m_librarian/db.py

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