Search genres by name and title

[m_librarian.git] / m_librarian / db.py

diff --git a/m_librarian/db.py b/m_librarian/db.py
index 99a2843aa42f3ad5f347a86efaecde0b30b6a1d5..771fd57270a18a33b1a95bddb76d37cbc75cce7a 100755 (executable)
--- a/m_librarian/db.py
+++ b/m_librarian/db.py
@@ -1,55 +1,15 @@
 #! /usr/bin/env python
 
 __all__ = ['Author', 'Book', 'Extension', 'Genre', 'Language',
-           'AuthorBook', 'BookGenre',
-           'init_db', 'insert_name', 'insert_author', '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
-
-try:
-    db_uri = ml_conf.get('database', 'URI')
-except:
-    db_uri = None
-
-db_dirs = []
-if not db_uri:
-    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)
-
-    for d in db_dirs:
-        db_file = os.path.join(d, 'm_librarian.sqlite')
-        if os.path.exists(db_file):
-            break
-    else:
-        # octal; -rw-------;
-        # make the database file/directory readable/writeable only by the user
-        os.umask(0066)
-        db_dir = db_dirs[0]
-        try:
-            os.makedirs(db_dir)
-        except OSError:  # Perhaps already exists
-            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)
-
-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")
+from .config import get_config
 
 
 class Author(SQLObject):
@@ -122,7 +82,7 @@ class Extension(SQLObject):
 
 
 class Genre(SQLObject):
-    name = StringCol(notNull=True, unique=True)
+    name = UnicodeCol(notNull=True, unique=True)
     title = UnicodeCol(notNull=True)
     count = IntCol(notNull=True)
     books = RelatedJoin('Book', otherColumn='book_id',
@@ -138,6 +98,73 @@ class Language(SQLObject):
     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):
+            break
+    else:
+        # octal; -rw-------;
+        # make the database file/directory readable/writeable only by the user
+        os.umask(0066)
+        db_dir = db_dirs[0]
+        try:
+            os.makedirs(db_dir)
+        except OSError:  # Perhaps already exists
+            pass
+        db_file = os.path.join(db_dir, 'm_librarian.sqlite')
+
+    return 'sqlite://%s' % db_file.replace(os.sep, '/')
+
+
+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()
+
+    sqlhub.processConnection = connection = connectionForURI(db_uri)
+
+    if connection.dbName == 'sqlite':
+        def lower(s):
+            return s.lower()
+
+        sqlite = connection.module
+
+        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
+
+        # 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():
     try:
         Book.select()[0]
@@ -171,19 +198,19 @@ def update_counters():
         author.count = AuthorBook.select(AuthorBook.q.author == author).count()
 
     for ext in Extension.select():
-        ext.count = Book.select(Book.q.extension == ext.name).count()
+        ext.count = Book.select(Book.q.extension == ext.id).count()
 
     for genre in Genre.select():
         genre.count = BookGenre.select(BookGenre.q.genre == genre).count()
 
     for language in Language.select():
-        language.count = Book.select(Book.q.language == language.name).count()
+        language.count = Book.select(Book.q.language == language.id).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()