m_librarian/db.py

   1 #! /usr/bin/env python
   2
   3 from __future__ import print_function
   4 import os
   5
   6 from sqlobject import SQLObject, StringCol, UnicodeCol, IntCol, BoolCol, \
   7     ForeignKey, DateCol, DatabaseIndex, RelatedJoin, \
   8     connectionForURI, sqlhub, SQLObjectNotFound, dberrors
   9
  10 from .compat import string_type
  11 from .config import get_config
  12
  13 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
  14            'AuthorBook', 'BookGenre', 'open_db', 'init_db',
  15            'insert_name', 'insert_author', 'update_counters',
  16            ]
  17
  18
  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)
  27
  28     @property
  29     def fullname(self):
  30         fullnames = filter(None, (self.surname, self.name, self.misc_name))
  31         return ' '.join(fullnames)
  32
  33     full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True)
  34     count_idx = DatabaseIndex(count)
  35
  36
  37 class AuthorBook(SQLObject):
  38     class sqlmeta:
  39         table = "author_book"
  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)
  45
  46
  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)
  56     ser_no = IntCol()
  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)
  76
  77     @property
  78     def author1(self):
  79         return self.authors[0].fullname
  80
  81     @property
  82     def author_list(self):
  83         return u', '.join([a.fullname for a in self.authors])
  84
  85     @property
  86     def genre1name(self):
  87         return self.genres[0].name
  88
  89     @property
  90     def genre1title(self):
  91         return self.genres[0].title
  92
  93     @property
  94     def genre_name_list(self):
  95         return u', '.join([g.name for g in self.genres])
  96
  97     @property
  98     def genre_title_list(self):
  99         return u', '.join([g.title for g in self.genres])
 100
 101     @property
 102     def ext(self):
 103         return self.extension.name
 104
 105     @property
 106     def lang(self):
 107         return self.language.name
 108
 109
 110 class BookGenre(SQLObject):
 111     class sqlmeta:
 112         table = "book_genre"
 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)
 118
 119
 120 class Extension(SQLObject):
 121     name = UnicodeCol(notNull=True, unique=True)
 122     count = IntCol(notNull=True)
 123     count_idx = DatabaseIndex(count)
 124
 125
 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)
 135
 136
 137 class Language(SQLObject):
 138     name = UnicodeCol(notNull=True, unique=True)
 139     count = IntCol(notNull=True)
 140     count_idx = DatabaseIndex(count)
 141
 142
 143 def _find_sqlite_db_dirs_posix():
 144     db_dirs = []
 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)
 150     return db_dirs
 151
 152
 153 def find_sqlite_db_dirs():
 154     if os.name == 'posix':
 155         return _find_sqlite_db_dirs_posix()
 156     raise OSError("Unknow OS")
 157
 158
 159 def find_sqlite_dburi(db_dirs=None):
 160     if db_dirs is None:
 161         db_dirs = find_sqlite_db_dirs()
 162     for d in db_dirs:
 163         db_file = os.path.join(d, 'm_librarian.sqlite')
 164         if os.path.exists(db_file):
 165             break
 166     else:
 167         # octal; -rw-------;
 168         # make the database file/directory readable/writeable only by the user
 169         os.umask(0o66)
 170         db_dir = db_dirs[0]
 171         try:
 172             os.makedirs(db_dir)
 173         except OSError:  # Perhaps already exists
 174             pass
 175         db_file = os.path.join(db_dir, 'm_librarian.sqlite')
 176
 177     return 'sqlite://%s' % db_file.replace(os.sep, '/')
 178
 179
 180 def open_db(db_uri=None):
 181     if db_uri is None:
 182         db_uri = get_config().get('database', 'URI') or find_sqlite_dburi()
 183
 184     if '://' not in db_uri:
 185         db_uri = 'sqlite://' + os.path.abspath(db_uri).replace(os.sep, '/')
 186
 187     sqlhub.processConnection = connection = connectionForURI(db_uri)
 188
 189     if connection.dbName == 'sqlite':
 190         def lower(s):
 191             if isinstance(s, string_type):
 192                 return s.lower()
 193             return s
 194
 195         sqlite = connection.module
 196
 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)
 201
 202         # This hack must be done at the very beginning, before the first query
 203         connection._connOptions['factory'] = MLConnection
 204
 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")
 210
 211
 212 def init_db():
 213     try:
 214         Book.select()[0]
 215     except IndexError:  # Table exists but is empty
 216         return
 217     except dberrors.Error:
 218         for table in Author, Extension, Genre, Language, Book, \
 219                 AuthorBook, BookGenre:
 220             table.createTable()
 221     else:
 222         return
 223
 224
 225 def insert_name(table, name, **kw):
 226     try:
 227         return table.byName(name)
 228     except SQLObjectNotFound:
 229         return table(name=name, count=0, **kw)
 230
 231
 232 def insert_author(surname, name, misc_name):
 233     try:
 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)
 238
 239
 240 def update_counters(pbar_cb=None):
 241     if pbar_cb:
 242         count = 0
 243         for table in Author, Extension, Genre, Language:
 244             count += table.select().count()
 245         pbar_cb.set_max(count)
 246
 247     if pbar_cb:
 248         count = 0
 249
 250     for author in Author.select():
 251         author.count = AuthorBook.select(AuthorBook.q.author == author).count()
 252         if pbar_cb:
 253             count += 1
 254             pbar_cb.display(count)
 255
 256     for ext in Extension.select():
 257         ext.count = Book.select(Book.q.extension == ext.id).count()
 258         if pbar_cb:
 259             count += 1
 260             pbar_cb.display(count)
 261
 262     for genre in Genre.select():
 263         genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
 264         if pbar_cb:
 265             count += 1
 266             pbar_cb.display(count)
 267
 268     for language in Language.select():
 269         language.count = Book.select(Book.q.language == language.id).count()
 270         if pbar_cb:
 271             count += 1
 272             pbar_cb.display(count)
 273
 274     if pbar_cb:
 275         pbar_cb.close()
 276
 277
 278 def test():
 279     db_dirs = find_sqlite_db_dirs()
 280     print("DB dirs:", db_dirs)
 281     print("DB URI:", find_sqlite_dburi())
 282
 283
 284 if __name__ == '__main__':
 285     test()