3 from __future__ import print_function
5 from sqlobject import SQLObject, StringCol, UnicodeCol, IntCol, BoolCol, \
6 ForeignKey, DateCol, DatabaseIndex, RelatedJoin, \
7 connectionForURI, sqlhub, SQLObjectNotFound, dberrors
8 from .compat import string_type
9 from .config import get_config
11 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
12 'AuthorBook', 'BookGenre', 'open_db', 'init_db',
13 'insert_name', 'insert_author', 'update_counters',
17 class Author(SQLObject):
18 surname = UnicodeCol(notNull=True)
19 name = UnicodeCol(notNull=True)
20 misc_name = UnicodeCol(notNull=True)
21 count = IntCol(notNull=True)
22 books = RelatedJoin('Book', otherColumn='book_id',
23 intermediateTable='author_book',
24 createRelatedTable=False)
28 fullnames = filter(None, (self.surname, self.name, self.misc_name))
29 return ' '.join(fullnames)
31 full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True)
32 count_idx = DatabaseIndex(count)
35 class AuthorBook(SQLObject):
38 author = ForeignKey('Author', notNull=True, cascade=True)
39 book = ForeignKey('Book', notNull=True, cascade=True)
40 author_idx = DatabaseIndex(author)
41 book_idx = DatabaseIndex(book)
42 main_idx = DatabaseIndex(author, book, unique=True)
45 class Book(SQLObject):
46 authors = RelatedJoin('Author',
47 intermediateTable='author_book',
48 createRelatedTable=False)
49 genres = RelatedJoin('Genre',
50 intermediateTable='book_genre',
51 createRelatedTable=False)
52 title = UnicodeCol(notNull=True)
53 series = UnicodeCol(notNull=True)
55 archive = StringCol(notNull=True)
56 file = UnicodeCol(notNull=True)
57 size = IntCol(notNull=True)
58 lib_id = StringCol(notNull=True)
59 deleted = BoolCol(notNull=True)
60 extension = ForeignKey('Extension', notNull=True)
61 date = DateCol(notNull=True)
62 language = ForeignKey('Language')
63 title_idx = DatabaseIndex(title)
64 series_idx = DatabaseIndex(series)
65 ser_no_idx = DatabaseIndex(ser_no)
66 archive_idx = DatabaseIndex(archive)
67 archive_file_idx = DatabaseIndex(archive, file, unique=True)
68 file_idx = DatabaseIndex(file)
69 size_idx = DatabaseIndex(size)
70 deleted_idx = DatabaseIndex(deleted)
71 extension_idx = DatabaseIndex(extension)
72 date_idx = DatabaseIndex(date)
73 language_idx = DatabaseIndex(language)
77 return self.authors[0].fullname
80 def author_list(self):
81 return u', '.join([a.fullname for a in self.authors])
85 return self.genres[0].name
88 def genre1title(self):
89 return self.genres[0].title
92 def genre_name_list(self):
93 return u', '.join([g.name for g in self.genres])
96 def genre_title_list(self):
97 return u', '.join([g.title for g in self.genres])
101 return self.extension.name
105 return self.language.name
108 class BookGenre(SQLObject):
111 book = ForeignKey('Book', notNull=True, cascade=True)
112 genre = ForeignKey('Genre', notNull=True, cascade=True)
113 book_idx = DatabaseIndex(book)
114 genre_idx = DatabaseIndex(genre)
115 main_idx = DatabaseIndex(book, genre, unique=True)
118 class Extension(SQLObject):
119 name = UnicodeCol(notNull=True, unique=True)
120 count = IntCol(notNull=True)
121 count_idx = DatabaseIndex(count)
124 class Genre(SQLObject):
125 name = UnicodeCol(notNull=True, unique=True)
126 title = UnicodeCol(notNull=True)
127 count = IntCol(notNull=True)
128 books = RelatedJoin('Book', otherColumn='book_id',
129 intermediateTable='book_genre',
130 createRelatedTable=False)
131 title_idx = DatabaseIndex(title)
132 count_idx = DatabaseIndex(count)
135 class Language(SQLObject):
136 name = UnicodeCol(notNull=True, unique=True)
137 count = IntCol(notNull=True)
138 count_idx = DatabaseIndex(count)
141 def _find_sqlite_db_dirs_posix():
143 if 'XDG_CACHE_HOME' in os.environ:
144 db_dirs.append(os.environ['XDG_CACHE_HOME'])
145 home_cache = os.path.expanduser('~/.cache')
146 if home_cache not in db_dirs:
147 db_dirs.append(home_cache)
151 def find_sqlite_db_dirs():
152 if os.name == 'posix':
153 return _find_sqlite_db_dirs_posix()
154 raise OSError("Unknow OS")
157 def find_sqlite_dburi(db_dirs=None):
159 db_dirs = find_sqlite_db_dirs()
161 db_file = os.path.join(d, 'm_librarian.sqlite')
162 if os.path.exists(db_file):
166 # make the database file/directory readable/writeable only by the user
171 except OSError: # Perhaps already exists
173 db_file = os.path.join(db_dir, 'm_librarian.sqlite')
175 return 'sqlite://%s' % db_file.replace(os.sep, '/')
178 def open_db(db_uri=None):
180 db_uri = get_config().get('database', 'URI') or find_sqlite_dburi()
182 if '://' not in db_uri:
183 db_uri = 'sqlite://' + os.path.abspath(db_uri).replace(os.sep, '/')
185 sqlhub.processConnection = connection = connectionForURI(db_uri)
187 if connection.dbName == 'sqlite':
189 if isinstance(s, string_type):
193 sqlite = connection.module
195 class MLConnection(sqlite.Connection):
196 def __init__(self, *args, **kwargs):
197 super(MLConnection, self).__init__(*args, **kwargs)
198 self.create_function('lower', 1, lower)
200 # This hack must be done at the very beginning, before the first query
201 connection._connOptions['factory'] = MLConnection
203 # Speedup SQLite connection
204 connection.query("PRAGMA synchronous=OFF")
205 connection.query("PRAGMA count_changes=OFF")
206 connection.query("PRAGMA journal_mode=MEMORY")
207 connection.query("PRAGMA temp_store=MEMORY")
213 except IndexError: # Table exists but is empty
215 except dberrors.Error:
216 for table in Author, Extension, Genre, Language, Book, \
217 AuthorBook, BookGenre:
223 def insert_name(table, name, **kw):
225 return table.byName(name)
226 except SQLObjectNotFound:
227 return table(name=name, count=0, **kw)
230 def insert_author(surname, name, misc_name):
232 return Author.full_name_idx.get(
233 surname=surname, name=name, misc_name=misc_name)
234 except SQLObjectNotFound:
235 return Author(surname=surname, name=name, misc_name=misc_name, count=0)
238 def update_counters(pbar_cb=None):
241 for table in Author, Extension, Genre, Language:
242 count += table.select().count()
243 pbar_cb.set_max(count)
248 for author in Author.select():
249 author.count = AuthorBook.select(AuthorBook.q.author == author).count()
252 pbar_cb.display(count)
254 for ext in Extension.select():
255 ext.count = Book.select(Book.q.extension == ext.id).count()
258 pbar_cb.display(count)
260 for genre in Genre.select():
261 genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
264 pbar_cb.display(count)
266 for language in Language.select():
267 language.count = Book.select(Book.q.language == language.id).count()
270 pbar_cb.display(count)
277 db_dirs = find_sqlite_db_dirs()
278 print("DB dirs:", db_dirs)
279 print("DB URI:", find_sqlite_dburi())
282 if __name__ == '__main__':