m_librarian/db.py

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