3 from __future__ import print_function
6 from sqlobject import SQLObject, StringCol, UnicodeCol, IntCol, BoolCol, \
7 ForeignKey, DateCol, DatabaseIndex, RelatedJoin, \
8 connectionForURI, sqlhub, SQLObjectNotFound, dberrors
10 from .compat import string_type
11 from .config import get_config
13 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
14 'AuthorBook', 'BookGenre', 'open_db', 'init_db',
15 'insert_name', 'insert_author', 'update_counters',
19 class Author(SQLObject):
20 surname = UnicodeCol(notNull=True)
21 name = UnicodeCol(notNull=True)
22 misc_name = UnicodeCol(notNull=True)
23 count = IntCol(notNull=True)
24 books = RelatedJoin('Book', otherColumn='book_id',
25 intermediateTable='author_book',
26 createRelatedTable=False)
30 fullnames = filter(None, (self.surname, self.name, self.misc_name))
31 return ' '.join(fullnames)
33 full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True)
34 count_idx = DatabaseIndex(count)
37 class AuthorBook(SQLObject):
40 author = ForeignKey('Author', notNull=True, cascade=True)
41 book = ForeignKey('Book', notNull=True, cascade=True)
42 author_idx = DatabaseIndex(author)
43 book_idx = DatabaseIndex(book)
44 main_idx = DatabaseIndex(author, book, unique=True)
47 class Book(SQLObject):
48 authors = RelatedJoin('Author',
49 intermediateTable='author_book',
50 createRelatedTable=False)
51 genres = RelatedJoin('Genre',
52 intermediateTable='book_genre',
53 createRelatedTable=False)
54 title = UnicodeCol(notNull=True)
55 series = UnicodeCol(notNull=True)
57 archive = StringCol(notNull=True)
58 file = UnicodeCol(notNull=True)
59 size = IntCol(notNull=True)
60 lib_id = StringCol(notNull=True)
61 deleted = BoolCol(notNull=True)
62 extension = ForeignKey('Extension', notNull=True)
63 date = DateCol(notNull=True)
64 language = ForeignKey('Language')
65 title_idx = DatabaseIndex(title)
66 series_idx = DatabaseIndex(series)
67 ser_no_idx = DatabaseIndex(ser_no)
68 archive_idx = DatabaseIndex(archive)
69 archive_file_idx = DatabaseIndex(archive, file, unique=True)
70 file_idx = DatabaseIndex(file)
71 size_idx = DatabaseIndex(size)
72 deleted_idx = DatabaseIndex(deleted)
73 extension_idx = DatabaseIndex(extension)
74 date_idx = DatabaseIndex(date)
75 language_idx = DatabaseIndex(language)
79 return self.authors[0].fullname
82 def author_list(self):
83 return u', '.join([a.fullname for a in self.authors])
87 return self.genres[0].name
90 def genre1title(self):
91 return self.genres[0].title
94 def genre_name_list(self):
95 return u', '.join([g.name for g in self.genres])
98 def genre_title_list(self):
99 return u', '.join([g.title for g in self.genres])
103 return self.extension.name
107 return self.language.name
110 class BookGenre(SQLObject):
113 book = ForeignKey('Book', notNull=True, cascade=True)
114 genre = ForeignKey('Genre', notNull=True, cascade=True)
115 book_idx = DatabaseIndex(book)
116 genre_idx = DatabaseIndex(genre)
117 main_idx = DatabaseIndex(book, genre, unique=True)
120 class Extension(SQLObject):
121 name = UnicodeCol(notNull=True, unique=True)
122 count = IntCol(notNull=True)
123 count_idx = DatabaseIndex(count)
126 class Genre(SQLObject):
127 name = UnicodeCol(notNull=True, unique=True)
128 title = UnicodeCol(notNull=True)
129 count = IntCol(notNull=True)
130 books = RelatedJoin('Book', otherColumn='book_id',
131 intermediateTable='book_genre',
132 createRelatedTable=False)
133 title_idx = DatabaseIndex(title)
134 count_idx = DatabaseIndex(count)
137 class Language(SQLObject):
138 name = UnicodeCol(notNull=True, unique=True)
139 count = IntCol(notNull=True)
140 count_idx = DatabaseIndex(count)
143 def _find_sqlite_db_dirs_posix():
145 if 'XDG_CACHE_HOME' in os.environ:
146 db_dirs.append(os.environ['XDG_CACHE_HOME'])
147 home_cache = os.path.expanduser('~/.cache')
148 if home_cache not in db_dirs:
149 db_dirs.append(home_cache)
153 def find_sqlite_db_dirs():
154 if os.name == 'posix':
155 return _find_sqlite_db_dirs_posix()
156 raise OSError("Unknow OS")
159 def find_sqlite_dburi(db_dirs=None):
161 db_dirs = find_sqlite_db_dirs()
163 db_file = os.path.join(d, 'm_librarian.sqlite')
164 if os.path.exists(db_file):
168 # make the database file/directory readable/writeable only by the user
173 except OSError: # Perhaps already exists
175 db_file = os.path.join(db_dir, 'm_librarian.sqlite')
177 return 'sqlite://%s' % db_file.replace(os.sep, '/')
180 def open_db(db_uri=None):
182 db_uri = get_config().get('database', 'URI') or find_sqlite_dburi()
184 if '://' not in db_uri:
185 db_uri = 'sqlite://' + os.path.abspath(db_uri).replace(os.sep, '/')
187 sqlhub.processConnection = connection = connectionForURI(db_uri)
189 if connection.dbName == 'sqlite':
191 if isinstance(s, string_type):
195 sqlite = connection.module
197 class MLConnection(sqlite.Connection):
198 def __init__(self, *args, **kwargs):
199 super(MLConnection, self).__init__(*args, **kwargs)
200 self.create_function('lower', 1, lower)
202 # This hack must be done at the very beginning, before the first query
203 connection._connOptions['factory'] = MLConnection
205 # Speedup SQLite connection
206 connection.query("PRAGMA synchronous=OFF")
207 connection.query("PRAGMA count_changes=OFF")
208 connection.query("PRAGMA journal_mode=MEMORY")
209 connection.query("PRAGMA temp_store=MEMORY")
215 except IndexError: # Table exists but is empty
217 except dberrors.Error:
218 for table in Author, Extension, Genre, Language, Book, \
219 AuthorBook, BookGenre:
225 def insert_name(table, name, **kw):
227 return table.byName(name)
228 except SQLObjectNotFound:
229 return table(name=name, count=0, **kw)
232 def insert_author(surname, name, misc_name):
234 return Author.full_name_idx.get(
235 surname=surname, name=name, misc_name=misc_name)
236 except SQLObjectNotFound:
237 return Author(surname=surname, name=name, misc_name=misc_name, count=0)
240 def update_counters(pbar_cb=None):
243 for table in Author, Extension, Genre, Language:
244 count += table.select().count()
245 pbar_cb.set_max(count)
250 for author in Author.select():
251 author.count = AuthorBook.select(AuthorBook.q.author == author).count()
254 pbar_cb.display(count)
256 for ext in Extension.select():
257 ext.count = Book.select(Book.q.extension == ext.id).count()
260 pbar_cb.display(count)
262 for genre in Genre.select():
263 genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
266 pbar_cb.display(count)
268 for language in Language.select():
269 language.count = Book.select(Book.q.language == language.id).count()
272 pbar_cb.display(count)
279 db_dirs = find_sqlite_db_dirs()
280 print("DB dirs:", db_dirs)
281 print("DB URI:", find_sqlite_dburi())
284 if __name__ == '__main__':