4 from sqlobject import SQLObject, StringCol, UnicodeCol, IntCol, BoolCol, \
5 ForeignKey, DateCol, DatabaseIndex, RelatedJoin, \
6 connectionForURI, sqlhub, SQLObjectNotFound, dberrors
7 from .config import get_config
9 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
10 'AuthorBook', 'BookGenre', 'open_db', 'init_db',
11 'insert_name', 'insert_author', 'update_counters',
15 class Author(SQLObject):
16 surname = UnicodeCol(notNull=True)
17 name = UnicodeCol(notNull=True)
18 misc_name = UnicodeCol(notNull=True)
19 count = IntCol(notNull=True)
20 books = RelatedJoin('Book', otherColumn='book_id',
21 intermediateTable='author_book',
22 createRelatedTable=False)
26 fullnames = filter(None, (self.surname, self.name, self.misc_name))
27 return ' '.join(fullnames)
29 full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True)
30 count_idx = DatabaseIndex(count)
33 class AuthorBook(SQLObject):
36 author = ForeignKey('Author', notNull=True, cascade=True)
37 book = ForeignKey('Book', notNull=True, cascade=True)
38 author_idx = DatabaseIndex(author)
39 book_idx = DatabaseIndex(book)
40 main_idx = DatabaseIndex(author, book, unique=True)
43 class Book(SQLObject):
44 authors = RelatedJoin('Author',
45 intermediateTable='author_book',
46 createRelatedTable=False)
47 genres = RelatedJoin('Genre',
48 intermediateTable='book_genre',
49 createRelatedTable=False)
50 title = UnicodeCol(notNull=True)
51 series = UnicodeCol(notNull=True)
53 archive = StringCol(notNull=True)
54 file = StringCol(notNull=True)
55 size = IntCol(notNull=True)
56 lib_id = StringCol(notNull=True)
57 deleted = BoolCol(notNull=True)
58 extension = ForeignKey('Extension', notNull=True)
59 date = DateCol(notNull=True)
60 language = ForeignKey('Language')
61 title_idx = DatabaseIndex(title)
62 series_idx = DatabaseIndex(series)
63 ser_no_idx = DatabaseIndex(ser_no)
64 archive_idx = DatabaseIndex(archive)
65 archive_file_idx = DatabaseIndex(archive, file, unique=True)
66 file_idx = DatabaseIndex(file)
67 size_idx = DatabaseIndex(size)
68 deleted_idx = DatabaseIndex(deleted)
69 extension_idx = DatabaseIndex(extension)
70 date_idx = DatabaseIndex(date)
71 language_idx = DatabaseIndex(language)
74 class BookGenre(SQLObject):
77 book = ForeignKey('Book', notNull=True, cascade=True)
78 genre = ForeignKey('Genre', notNull=True, cascade=True)
79 book_idx = DatabaseIndex(book)
80 genre_idx = DatabaseIndex(genre)
81 main_idx = DatabaseIndex(book, genre, unique=True)
84 class Extension(SQLObject):
85 name = UnicodeCol(notNull=True, unique=True)
86 count = IntCol(notNull=True)
87 count_idx = DatabaseIndex(count)
90 class Genre(SQLObject):
91 name = UnicodeCol(notNull=True, unique=True)
92 title = UnicodeCol(notNull=True)
93 count = IntCol(notNull=True)
94 books = RelatedJoin('Book', otherColumn='book_id',
95 intermediateTable='book_genre',
96 createRelatedTable=False)
97 title_idx = DatabaseIndex(title)
98 count_idx = DatabaseIndex(count)
101 class Language(SQLObject):
102 name = UnicodeCol(notNull=True, unique=True)
103 count = IntCol(notNull=True)
104 count_idx = DatabaseIndex(count)
107 def _find_sqlite_db_dirs_posix():
109 if 'XDG_CACHE_HOME' in os.environ:
110 db_dirs.append(os.environ['XDG_CACHE_HOME'])
111 home_cache = os.path.expanduser('~/.cache')
112 if home_cache not in db_dirs:
113 db_dirs.append(home_cache)
117 def find_sqlite_db_dirs():
118 if os.name == 'posix':
119 return _find_sqlite_db_dirs_posix()
120 raise OSError("Unknow OS")
123 def find_sqlite_dburi(db_dirs=None):
125 db_dirs = find_sqlite_db_dirs()
127 db_file = os.path.join(d, 'm_librarian.sqlite')
128 if os.path.exists(db_file):
132 # make the database file/directory readable/writeable only by the user
137 except OSError: # Perhaps already exists
139 db_file = os.path.join(db_dir, 'm_librarian.sqlite')
141 return 'sqlite://%s' % db_file.replace(os.sep, '/')
144 def open_db(db_uri=None):
147 db_uri = get_config().get('database', 'URI')
149 db_uri = find_sqlite_dburi()
151 if '://' not in db_uri:
152 db_uri = 'sqlite://' + os.path.abspath(db_uri).replace(os.sep, '/')
154 sqlhub.processConnection = connection = connectionForURI(db_uri)
156 if connection.dbName == 'sqlite':
158 if isinstance(s, basestring):
162 sqlite = connection.module
164 class MLConnection(sqlite.Connection):
165 def __init__(self, *args, **kwargs):
166 super(MLConnection, self).__init__(*args, **kwargs)
167 self.create_function('lower', 1, lower)
169 # This hack must be done at the very beginning, before the first query
170 connection._connOptions['factory'] = MLConnection
172 # Speedup SQLite connection
173 connection.query("PRAGMA synchronous=OFF")
174 connection.query("PRAGMA count_changes=OFF")
175 connection.query("PRAGMA journal_mode=MEMORY")
176 connection.query("PRAGMA temp_store=MEMORY")
182 except IndexError: # Table exists but is empty
184 except dberrors.Error:
185 for table in Author, Extension, Genre, Language, Book, \
186 AuthorBook, BookGenre:
192 def insert_name(table, name, **kw):
194 return table.byName(name)
195 except SQLObjectNotFound:
196 return table(name=name, count=0, **kw)
199 def insert_author(surname, name, misc_name):
201 return Author.full_name_idx.get(
202 surname=surname, name=name, misc_name=misc_name)
203 except SQLObjectNotFound:
204 return Author(surname=surname, name=name, misc_name=misc_name, count=0)
207 def update_counters():
208 for author in Author.select():
209 author.count = AuthorBook.select(AuthorBook.q.author == author).count()
211 for ext in Extension.select():
212 ext.count = Book.select(Book.q.extension == ext.id).count()
214 for genre in Genre.select():
215 genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
217 for language in Language.select():
218 language.count = Book.select(Book.q.language == language.id).count()
222 db_dirs = find_sqlite_db_dirs()
223 print "DB dirs:", db_dirs
224 print "DB URI:", find_sqlite_dburi()
226 if __name__ == '__main__':