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', '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 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     def lower(s):
  49         return s.lower()
  50
  51     sqlite = connection.module
  52
  53     class MLConnection(sqlite.Connection):
  54         def __init__(self, *args, **kwargs):
  55             super(MLConnection, self).__init__(*args, **kwargs)
  56             self.create_function('lower', 1, lower)
  57
  58     connection._connOptions['factory'] = MLConnection
  59
  60     # Speedup SQLite connection
  61     connection.query("PRAGMA synchronous=OFF")
  62     connection.query("PRAGMA count_changes=OFF")
  63     connection.query("PRAGMA journal_mode=MEMORY")
  64     connection.query("PRAGMA temp_store=MEMORY")
  65
  66
  67 class Author(SQLObject):
  68     surname = UnicodeCol(notNull=True)
  69     name = UnicodeCol(notNull=True)
  70     misc_name = UnicodeCol(notNull=True)
  71     count = IntCol(notNull=True)
  72     books = RelatedJoin('Book', otherColumn='book_id',
  73                         intermediateTable='author_book',
  74                         createRelatedTable=False)
  75     full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True)
  76     count_idx = DatabaseIndex(count)
  77
  78
  79 class AuthorBook(SQLObject):
  80     class sqlmeta:
  81         table = "author_book"
  82     author = ForeignKey('Author', notNull=True, cascade=True)
  83     book = ForeignKey('Book', notNull=True, cascade=True)
  84     author_idx = DatabaseIndex(author)
  85     book_idx = DatabaseIndex(book)
  86     main_idx = DatabaseIndex(author, book, unique=True)
  87
  88
  89 class Book(SQLObject):
  90     authors = RelatedJoin('Author',
  91                           intermediateTable='author_book',
  92                           createRelatedTable=False)
  93     genres = RelatedJoin('Genre',
  94                          intermediateTable='book_genre',
  95                          createRelatedTable=False)
  96     title = UnicodeCol(notNull=True)
  97     series = UnicodeCol(notNull=True)
  98     ser_no = IntCol()
  99     archive = StringCol(notNull=True)
 100     file = StringCol(notNull=True)
 101     size = IntCol(notNull=True)
 102     lib_id = StringCol(notNull=True)
 103     deleted = BoolCol(notNull=True)
 104     extension = ForeignKey('Extension', notNull=True)
 105     date = DateCol(notNull=True)
 106     language = ForeignKey('Language')
 107     title_idx = DatabaseIndex(title)
 108     series_idx = DatabaseIndex(series)
 109     ser_no_idx = DatabaseIndex(ser_no)
 110     archive_idx = DatabaseIndex(archive)
 111     archive_file_idx = DatabaseIndex(archive, file, unique=True)
 112     file_idx = DatabaseIndex(file)
 113     size_idx = DatabaseIndex(size)
 114     deleted_idx = DatabaseIndex(deleted)
 115     extension_idx = DatabaseIndex(extension)
 116     date_idx = DatabaseIndex(date)
 117     language_idx = DatabaseIndex(language)
 118
 119
 120 class BookGenre(SQLObject):
 121     class sqlmeta:
 122         table = "book_genre"
 123     book = ForeignKey('Book', notNull=True, cascade=True)
 124     genre = ForeignKey('Genre', notNull=True, cascade=True)
 125     book_idx = DatabaseIndex(book)
 126     genre_idx = DatabaseIndex(genre)
 127     main_idx = DatabaseIndex(book, genre, unique=True)
 128
 129
 130 class Extension(SQLObject):
 131     name = StringCol(notNull=True, unique=True)
 132     count = IntCol(notNull=True)
 133     count_idx = DatabaseIndex(count)
 134
 135
 136 class Genre(SQLObject):
 137     name = StringCol(notNull=True, unique=True)
 138     title = UnicodeCol(notNull=True)
 139     count = IntCol(notNull=True)
 140     books = RelatedJoin('Book', otherColumn='book_id',
 141                         intermediateTable='book_genre',
 142                         createRelatedTable=False)
 143     title_idx = DatabaseIndex(title)
 144     count_idx = DatabaseIndex(count)
 145
 146
 147 class Language(SQLObject):
 148     name = StringCol(notNull=True, unique=True)
 149     count = IntCol(notNull=True)
 150     count_idx = DatabaseIndex(count)
 151
 152
 153 def init_db():
 154     try:
 155         Book.select()[0]
 156     except IndexError:  # Table exists but is empty
 157         return
 158     except dberrors.Error:
 159         for table in Author, Extension, Genre, Language, Book, \
 160                 AuthorBook, BookGenre:
 161             table.createTable()
 162     else:
 163         return
 164
 165
 166 def insert_name(table, name, **kw):
 167     try:
 168         return table.byName(name)
 169     except SQLObjectNotFound:
 170         return table(name=name, count=0, **kw)
 171
 172
 173 def insert_author(surname, name, misc_name):
 174     try:
 175         return Author.full_name_idx.get(
 176             surname=surname, name=name, misc_name=misc_name)
 177     except SQLObjectNotFound:
 178         return Author(surname=surname, name=name, misc_name=misc_name, count=0)
 179
 180
 181 def update_counters():
 182     for author in Author.select():
 183         author.count = AuthorBook.select(AuthorBook.q.author == author).count()
 184
 185     for ext in Extension.select():
 186         ext.count = Book.select(Book.q.extension == ext.name).count()
 187
 188     for genre in Genre.select():
 189         genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
 190
 191     for language in Language.select():
 192         language.count = Book.select(Book.q.language == language.name).count()
 193
 194
 195 def test():
 196     print "DB dirs:", db_dirs
 197     if db_uri:
 198         print "DB URI:", db_uri
 199
 200 if __name__ == '__main__':
 201     test()