X-Git-Url: https://git.phdru.name/?a=blobdiff_plain;f=m_librarian%2Fdb.py;h=93631679139da91072146144debc2419950e5a1d;hb=755e7bd866100b38d222caf33fcb30d2f167bb91;hp=ef0e3bd350d52b0b96b8fdae3b5f3832d72708d2;hpb=e53f480b62166cc8028f1d6e4fda307ad7af2233;p=m_librarian.git diff --git a/m_librarian/db.py b/m_librarian/db.py index ef0e3bd..9363167 100755 --- a/m_librarian/db.py +++ b/m_librarian/db.py @@ -1,28 +1,122 @@ #! /usr/bin/env python __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language', - 'init_db', 'insert_name', 'update_counters', + 'AuthorBook', 'BookGenre', 'open_db', 'init_db', + 'insert_name', 'insert_author', 'update_counters', ] import os from sqlobject import SQLObject, StringCol, UnicodeCol, IntCol, BoolCol, \ ForeignKey, DateCol, DatabaseIndex, RelatedJoin, \ connectionForURI, sqlhub, SQLObjectNotFound, dberrors -from .config import ml_conf +from .config import get_config -try: - db_uri = ml_conf.get('database', 'URI') -except: - db_uri = None -db_dirs = [] -if not db_uri: +class Author(SQLObject): + surname = UnicodeCol(notNull=True) + name = UnicodeCol(notNull=True) + misc_name = UnicodeCol(notNull=True) + count = IntCol(notNull=True) + books = RelatedJoin('Book', otherColumn='book_id', + intermediateTable='author_book', + createRelatedTable=False) + full_name_idx = DatabaseIndex(surname, name, misc_name, unique=True) + count_idx = DatabaseIndex(count) + + +class AuthorBook(SQLObject): + class sqlmeta: + table = "author_book" + author = ForeignKey('Author', notNull=True, cascade=True) + book = ForeignKey('Book', notNull=True, cascade=True) + author_idx = DatabaseIndex(author) + book_idx = DatabaseIndex(book) + main_idx = DatabaseIndex(author, book, unique=True) + + +class Book(SQLObject): + authors = RelatedJoin('Author', + intermediateTable='author_book', + createRelatedTable=False) + genres = RelatedJoin('Genre', + intermediateTable='book_genre', + createRelatedTable=False) + title = UnicodeCol(notNull=True) + series = UnicodeCol(notNull=True) + ser_no = IntCol() + archive = StringCol(notNull=True) + file = StringCol(notNull=True) + size = IntCol(notNull=True) + lib_id = StringCol(notNull=True) + deleted = BoolCol(notNull=True) + extension = ForeignKey('Extension', notNull=True) + date = DateCol(notNull=True) + language = ForeignKey('Language') + title_idx = DatabaseIndex(title) + series_idx = DatabaseIndex(series) + ser_no_idx = DatabaseIndex(ser_no) + archive_idx = DatabaseIndex(archive) + archive_file_idx = DatabaseIndex(archive, file, unique=True) + file_idx = DatabaseIndex(file) + size_idx = DatabaseIndex(size) + deleted_idx = DatabaseIndex(deleted) + extension_idx = DatabaseIndex(extension) + date_idx = DatabaseIndex(date) + language_idx = DatabaseIndex(language) + + +class BookGenre(SQLObject): + class sqlmeta: + table = "book_genre" + book = ForeignKey('Book', notNull=True, cascade=True) + genre = ForeignKey('Genre', notNull=True, cascade=True) + book_idx = DatabaseIndex(book) + genre_idx = DatabaseIndex(genre) + main_idx = DatabaseIndex(book, genre, unique=True) + + +class Extension(SQLObject): + name = StringCol(notNull=True, unique=True) + count = IntCol(notNull=True) + count_idx = DatabaseIndex(count) + + +class Genre(SQLObject): + name = StringCol(notNull=True, unique=True) + title = UnicodeCol(notNull=True) + count = IntCol(notNull=True) + books = RelatedJoin('Book', otherColumn='book_id', + intermediateTable='book_genre', + createRelatedTable=False) + title_idx = DatabaseIndex(title) + count_idx = DatabaseIndex(count) + + +class Language(SQLObject): + name = StringCol(notNull=True, unique=True) + count = IntCol(notNull=True) + count_idx = DatabaseIndex(count) + + +def _find_sqlite_db_dirs_posix(): + db_dirs = [] if 'XDG_CACHE_HOME' in os.environ: db_dirs.append(os.environ['XDG_CACHE_HOME']) home_cache = os.path.expanduser('~/.cache') if home_cache not in db_dirs: db_dirs.append(home_cache) + return db_dirs + + +def find_sqlite_db_dirs(): + if os.name == 'posix': + return _find_sqlite_db_dirs_posix() + raise OSError("Unknow OS") + +def find_sqlite_dburi(db_dirs=None): + if db_dirs is None: + db_dirs = find_sqlite_db_dirs() for d in db_dirs: db_file = os.path.join(d, 'm_librarian.sqlite') if os.path.exists(db_file): @@ -38,61 +132,37 @@ if not db_uri: pass db_file = os.path.join(db_dir, 'm_librarian.sqlite') - db_uri = 'sqlite://%s' % db_file.replace(os.sep, '/') - - -sqlhub.processConnection = connection = connectionForURI(db_uri) + return 'sqlite://%s' % db_file.replace(os.sep, '/') -if connection.dbName == 'sqlite': - # Speedup SQLite connection - connection.query("PRAGMA synchronous=OFF") - connection.query("PRAGMA count_changes=OFF") - connection.query("PRAGMA journal_mode=MEMORY") - connection.query("PRAGMA temp_store=MEMORY") +def open_db(db_uri=None): + if db_uri is None: + try: + db_uri = get_config().get('database', 'URI') + except: + db_uri = find_sqlite_dburi() -class Author(SQLObject): - name = UnicodeCol(unique=True) - count = IntCol() - count_idx = DatabaseIndex('count') - books = RelatedJoin('Book', otherColumn='book_id') - - -class Book(SQLObject): - authors = RelatedJoin('Author') - genres = RelatedJoin('Genre') - title = UnicodeCol() - series = UnicodeCol() - ser_no = IntCol() - archive = StringCol() - file = StringCol() - size = IntCol() - lib_id = StringCol() - deleted = BoolCol() - extension = ForeignKey('Extension') - date = DateCol() - language = ForeignKey('Language') - archive_file_idx = DatabaseIndex('archive', 'file', unique=True) - + sqlhub.processConnection = connection = connectionForURI(db_uri) -class Extension(SQLObject): - name = StringCol(unique=True) - count = IntCol() - count_idx = DatabaseIndex('count') + if connection.dbName == 'sqlite': + def lower(s): + return s.lower() + sqlite = connection.module -class Genre(SQLObject): - name = StringCol(unique=True) - title = UnicodeCol() - count = IntCol() - count_idx = DatabaseIndex('count') - books = RelatedJoin('Book', otherColumn='book_id') + class MLConnection(sqlite.Connection): + def __init__(self, *args, **kwargs): + super(MLConnection, self).__init__(*args, **kwargs) + self.create_function('lower', 1, lower) + # This hack must be done at the very beginning, before the first query + connection._connOptions['factory'] = MLConnection -class Language(SQLObject): - name = StringCol(unique=True) - count = IntCol() - count_idx = DatabaseIndex('count') + # Speedup SQLite connection + connection.query("PRAGMA synchronous=OFF") + connection.query("PRAGMA count_changes=OFF") + connection.query("PRAGMA journal_mode=MEMORY") + connection.query("PRAGMA temp_store=MEMORY") def init_db(): @@ -101,7 +171,8 @@ def init_db(): except IndexError: # Table exists but is empty return except dberrors.Error: - for table in Author, Extension, Genre, Language, Book: + for table in Author, Extension, Genre, Language, Book, \ + AuthorBook, BookGenre: table.createTable() else: return @@ -114,24 +185,32 @@ def insert_name(table, name, **kw): return table(name=name, count=0, **kw) +def insert_author(surname, name, misc_name): + try: + return Author.full_name_idx.get( + surname=surname, name=name, misc_name=misc_name) + except SQLObjectNotFound: + return Author(surname=surname, name=name, misc_name=misc_name, count=0) + + def update_counters(): for author in Author.select(): - author.count = len(author.books) + author.count = AuthorBook.select(AuthorBook.q.author == author).count() for ext in Extension.select(): ext.count = Book.select(Book.q.extension == ext.name).count() for genre in Genre.select(): - genre.count = len(genre.books) + genre.count = BookGenre.select(BookGenre.q.genre == genre).count() for language in Language.select(): language.count = Book.select(Book.q.language == language.name).count() def test(): + db_dirs = find_sqlite_db_dirs() print "DB dirs:", db_dirs - if db_uri: - print "DB URI:", db_uri + print "DB URI:", find_sqlite_dburi() if __name__ == '__main__': test()