m_librarian/db.py

   1 #! /usr/bin/env python
   2
   3 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
   4            'AuthorBook', 'BookGenre',
   5            'init_db', 'insert_name', '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 ml_conf
  13
  14 try:
  15     db_uri = ml_conf.get('database', 'URI')
  16 except:
  17     db_uri = None
  18
  19 db_dirs = []
  20 if not db_uri:
  21     if 'XDG_CACHE_HOME' in os.environ:
  22         db_dirs.append(os.environ['XDG_CACHE_HOME'])
  23     home_cache = os.path.expanduser('~/.cache')
  24     if home_cache not in db_dirs:
  25         db_dirs.append(home_cache)
  26
  27     for d in db_dirs:
  28         db_file = os.path.join(d, 'm_librarian.sqlite')
  29         if os.path.exists(db_file):
  30             break
  31     else:
  32         # octal; -rw-------;
  33         # make the database file/directory readable/writeable only by the user
  34         os.umask(0066)
  35         db_dir = db_dirs[0]
  36         try:
  37             os.makedirs(db_dir)
  38         except OSError:  # Perhaps already exists
  39             pass
  40         db_file = os.path.join(db_dir, 'm_librarian.sqlite')
  41
  42     db_uri = 'sqlite://%s' % db_file.replace(os.sep, '/')
  43
  44
  45 sqlhub.processConnection = connection = connectionForURI(db_uri)
  46
  47 if connection.dbName == 'sqlite':
  48     # Speedup SQLite connection
  49     connection.query("PRAGMA synchronous=OFF")
  50     connection.query("PRAGMA count_changes=OFF")
  51     connection.query("PRAGMA journal_mode=MEMORY")
  52     connection.query("PRAGMA temp_store=MEMORY")
  53
  54
  55 class Author(SQLObject):
  56     name = UnicodeCol(unique=True)
  57     count = IntCol()
  58     books = RelatedJoin('Book', otherColumn='book_id',
  59                         intermediateTable='author_book',
  60                         createRelatedTable=False)
  61     count_idx = DatabaseIndex(count)
  62
  63
  64 class AuthorBook(SQLObject):
  65     class sqlmeta:
  66         table = "author_book"
  67     author = ForeignKey('Author', notNull=True, cascade=True)
  68     book = ForeignKey('Book', notNull=True, cascade=True)
  69     author_idx = DatabaseIndex(author)
  70     book_idx = DatabaseIndex(book)
  71     main_idx = DatabaseIndex(author, book, unique=True)
  72
  73
  74 class Book(SQLObject):
  75     authors = RelatedJoin('Author',
  76                           intermediateTable='author_book',
  77                           createRelatedTable=False)
  78     genres = RelatedJoin('Genre',
  79                          intermediateTable='book_genre',
  80                          createRelatedTable=False)
  81     title = UnicodeCol()
  82     series = UnicodeCol()
  83     ser_no = IntCol()
  84     archive = StringCol()
  85     file = StringCol()
  86     size = IntCol()
  87     lib_id = StringCol()
  88     deleted = BoolCol()
  89     extension = ForeignKey('Extension')
  90     date = DateCol()
  91     language = ForeignKey('Language')
  92     title_idx = DatabaseIndex(title)
  93     series_idx = DatabaseIndex(series)
  94     ser_no_idx = DatabaseIndex(ser_no)
  95     archive_file_idx = DatabaseIndex(archive, file, unique=True)
  96     file_idx = DatabaseIndex(file)
  97     size_idx = DatabaseIndex(size)
  98     deleted_idx = DatabaseIndex(deleted)
  99     extension_idx = DatabaseIndex(extension)
 100     date_idx = DatabaseIndex(date)
 101     language_idx = DatabaseIndex(language)
 102
 103
 104 class BookGenre(SQLObject):
 105     class sqlmeta:
 106         table = "book_genre"
 107     book = ForeignKey('Book', notNull=True, cascade=True)
 108     genre = ForeignKey('Genre', notNull=True, cascade=True)
 109     book_idx = DatabaseIndex(book)
 110     genre_idx = DatabaseIndex(genre)
 111     main_idx = DatabaseIndex(book, genre, unique=True)
 112
 113
 114 class Extension(SQLObject):
 115     name = StringCol(unique=True)
 116     count = IntCol()
 117     count_idx = DatabaseIndex(count)
 118
 119
 120 class Genre(SQLObject):
 121     name = StringCol(unique=True)
 122     title = UnicodeCol()
 123     count = IntCol()
 124     books = RelatedJoin('Book', otherColumn='book_id',
 125                         intermediateTable='book_genre',
 126                         createRelatedTable=False)
 127     title_idx = DatabaseIndex(title)
 128     count_idx = DatabaseIndex(count)
 129
 130
 131 class Language(SQLObject):
 132     name = StringCol(unique=True)
 133     count = IntCol()
 134     count_idx = DatabaseIndex(count)
 135
 136
 137 def init_db():
 138     try:
 139         Book.select()[0]
 140     except IndexError:  # Table exists but is empty
 141         return
 142     except dberrors.Error:
 143         for table in Author, Extension, Genre, Language, Book, \
 144                 AuthorBook, BookGenre:
 145             table.createTable()
 146     else:
 147         return
 148
 149
 150 def insert_name(table, name, **kw):
 151     try:
 152         return table.byName(name)
 153     except SQLObjectNotFound:
 154         return table(name=name, count=0, **kw)
 155
 156
 157 def update_counters():
 158     for author in Author.select():
 159         author.count = len(author.books)
 160
 161     for ext in Extension.select():
 162         ext.count = Book.select(Book.q.extension == ext.name).count()
 163
 164     for genre in Genre.select():
 165         genre.count = len(genre.books)
 166
 167     for language in Language.select():
 168         language.count = Book.select(Book.q.language == language.name).count()
 169
 170
 171 def test():
 172     print "DB dirs:", db_dirs
 173     if db_uri:
 174         print "DB URI:", db_uri
 175
 176 if __name__ == '__main__':
 177     test()