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 .config import get_config
   9
  10 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
  11            'AuthorBook', 'BookGenre', 'open_db', 'init_db',
  12            'insert_name', 'insert_author', 'update_counters',
  13            ]
  14
  15
  16 class Author(SQLObject):
  17     surname = UnicodeCol(notNull=True)
  18     name = UnicodeCol(notNull=True)
  19     misc_name = UnicodeCol(notNull=True)
  20     count = IntCol(notNull=True)
  21     books = RelatedJoin('Book', otherColumn='book_id',
  22                         intermediateTable='author_book',
  23                         createRelatedTable=False)
  24
  25     @property
  26     def fullname(self):
  27         fullnames = filter(None, (self.surname, self.name, self.misc_name))
  28         return ' '.join(fullnames)
  29
  30     full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True)
  31     count_idx = DatabaseIndex(count)
  32
  33
  34 class AuthorBook(SQLObject):
  35     class sqlmeta:
  36         table = "author_book"
  37     author = ForeignKey('Author', notNull=True, cascade=True)
  38     book = ForeignKey('Book', notNull=True, cascade=True)
  39     author_idx = DatabaseIndex(author)
  40     book_idx = DatabaseIndex(book)
  41     main_idx = DatabaseIndex(author, book, unique=True)
  42
  43
  44 class Book(SQLObject):
  45     authors = RelatedJoin('Author',
  46                           intermediateTable='author_book',
  47                           createRelatedTable=False)
  48     genres = RelatedJoin('Genre',
  49                          intermediateTable='book_genre',
  50                          createRelatedTable=False)
  51     title = UnicodeCol(notNull=True)
  52     series = UnicodeCol(notNull=True)
  53     ser_no = IntCol()
  54     archive = StringCol(notNull=True)
  55     file = StringCol(notNull=True)
  56     size = IntCol(notNull=True)
  57     lib_id = StringCol(notNull=True)
  58     deleted = BoolCol(notNull=True)
  59     extension = ForeignKey('Extension', notNull=True)
  60     date = DateCol(notNull=True)
  61     language = ForeignKey('Language')
  62     title_idx = DatabaseIndex(title)
  63     series_idx = DatabaseIndex(series)
  64     ser_no_idx = DatabaseIndex(ser_no)
  65     archive_idx = DatabaseIndex(archive)
  66     archive_file_idx = DatabaseIndex(archive, file, unique=True)
  67     file_idx = DatabaseIndex(file)
  68     size_idx = DatabaseIndex(size)
  69     deleted_idx = DatabaseIndex(deleted)
  70     extension_idx = DatabaseIndex(extension)
  71     date_idx = DatabaseIndex(date)
  72     language_idx = DatabaseIndex(language)
  73
  74
  75 class BookGenre(SQLObject):
  76     class sqlmeta:
  77         table = "book_genre"
  78     book = ForeignKey('Book', notNull=True, cascade=True)
  79     genre = ForeignKey('Genre', notNull=True, cascade=True)
  80     book_idx = DatabaseIndex(book)
  81     genre_idx = DatabaseIndex(genre)
  82     main_idx = DatabaseIndex(book, genre, unique=True)
  83
  84
  85 class Extension(SQLObject):
  86     name = UnicodeCol(notNull=True, unique=True)
  87     count = IntCol(notNull=True)
  88     count_idx = DatabaseIndex(count)
  89
  90
  91 class Genre(SQLObject):
  92     name = UnicodeCol(notNull=True, unique=True)
  93     title = UnicodeCol(notNull=True)
  94     count = IntCol(notNull=True)
  95     books = RelatedJoin('Book', otherColumn='book_id',
  96                         intermediateTable='book_genre',
  97                         createRelatedTable=False)
  98     title_idx = DatabaseIndex(title)
  99     count_idx = DatabaseIndex(count)
 100
 101
 102 class Language(SQLObject):
 103     name = UnicodeCol(notNull=True, unique=True)
 104     count = IntCol(notNull=True)
 105     count_idx = DatabaseIndex(count)
 106
 107
 108 def _find_sqlite_db_dirs_posix():
 109     db_dirs = []
 110     if 'XDG_CACHE_HOME' in os.environ:
 111         db_dirs.append(os.environ['XDG_CACHE_HOME'])
 112     home_cache = os.path.expanduser('~/.cache')
 113     if home_cache not in db_dirs:
 114         db_dirs.append(home_cache)
 115     return db_dirs
 116
 117
 118 def find_sqlite_db_dirs():
 119     if os.name == 'posix':
 120         return _find_sqlite_db_dirs_posix()
 121     raise OSError("Unknow OS")
 122
 123
 124 def find_sqlite_dburi(db_dirs=None):
 125     if db_dirs is None:
 126         db_dirs = find_sqlite_db_dirs()
 127     for d in db_dirs:
 128         db_file = os.path.join(d, 'm_librarian.sqlite')
 129         if os.path.exists(db_file):
 130             break
 131     else:
 132         # octal; -rw-------;
 133         # make the database file/directory readable/writeable only by the user
 134         os.umask(0o66)
 135         db_dir = db_dirs[0]
 136         try:
 137             os.makedirs(db_dir)
 138         except OSError:  # Perhaps already exists
 139             pass
 140         db_file = os.path.join(db_dir, 'm_librarian.sqlite')
 141
 142     return 'sqlite://%s' % db_file.replace(os.sep, '/')
 143
 144
 145 def open_db(db_uri=None):
 146     if db_uri is None:
 147         try:
 148             db_uri = get_config().get('database', 'URI')
 149         except:
 150             db_uri = find_sqlite_dburi()
 151
 152     if '://' not in db_uri:
 153         db_uri = 'sqlite://' + os.path.abspath(db_uri).replace(os.sep, '/')
 154
 155     sqlhub.processConnection = connection = connectionForURI(db_uri)
 156
 157     if connection.dbName == 'sqlite':
 158         def lower(s):
 159             if isinstance(s, basestring):
 160                 return s.lower()
 161             return s
 162
 163         sqlite = connection.module
 164
 165         class MLConnection(sqlite.Connection):
 166             def __init__(self, *args, **kwargs):
 167                 super(MLConnection, self).__init__(*args, **kwargs)
 168                 self.create_function('lower', 1, lower)
 169
 170         # This hack must be done at the very beginning, before the first query
 171         connection._connOptions['factory'] = MLConnection
 172
 173         # Speedup SQLite connection
 174         connection.query("PRAGMA synchronous=OFF")
 175         connection.query("PRAGMA count_changes=OFF")
 176         connection.query("PRAGMA journal_mode=MEMORY")
 177         connection.query("PRAGMA temp_store=MEMORY")
 178
 179
 180 def init_db():
 181     try:
 182         Book.select()[0]
 183     except IndexError:  # Table exists but is empty
 184         return
 185     except dberrors.Error:
 186         for table in Author, Extension, Genre, Language, Book, \
 187                 AuthorBook, BookGenre:
 188             table.createTable()
 189     else:
 190         return
 191
 192
 193 def insert_name(table, name, **kw):
 194     try:
 195         return table.byName(name)
 196     except SQLObjectNotFound:
 197         return table(name=name, count=0, **kw)
 198
 199
 200 def insert_author(surname, name, misc_name):
 201     try:
 202         return Author.full_name_idx.get(
 203             surname=surname, name=name, misc_name=misc_name)
 204     except SQLObjectNotFound:
 205         return Author(surname=surname, name=name, misc_name=misc_name, count=0)
 206
 207
 208 def update_counters():
 209     for author in Author.select():
 210         author.count = AuthorBook.select(AuthorBook.q.author == author).count()
 211
 212     for ext in Extension.select():
 213         ext.count = Book.select(Book.q.extension == ext.id).count()
 214
 215     for genre in Genre.select():
 216         genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
 217
 218     for language in Language.select():
 219         language.count = Book.select(Book.q.language == language.id).count()
 220
 221
 222 def test():
 223     db_dirs = find_sqlite_db_dirs()
 224     print("DB dirs:", db_dirs)
 225     print("DB URI:", find_sqlite_dburi())
 226
 227 if __name__ == '__main__':
 228     test()