m_librarian/db.py

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