From 74e967d16050472e1e30b2301b8460dad1bc91f0 Mon Sep 17 00:00:00 2001
From: Michaël Ball <michael.ball@gmail.com>
Date: Sun, 2 Aug 2015 10:46:53 +0100
Subject: Move to different sqlite driver. Performance improvements.

---
 db/db_manager.py | 116 +++++++++++++++++++++++++++++++++++++++++--------------
 1 file changed, 86 insertions(+), 30 deletions(-)

(limited to 'db')

diff --git a/db/db_manager.py b/db/db_manager.py
index 44d6b96..eb813ec 100644
--- a/db/db_manager.py
+++ b/db/db_manager.py
@@ -1,54 +1,110 @@
 import configparser
-import sqlite3
+import os
+
+import apsw
 
 
 class DbManager:
     class __DbManager:
+        config = configparser.ConfigParser()
+        config.read("mach2.ini")
+
         def __init__(self):
-            config = configparser.ConfigParser()
-            config.read("mach2.ini")
-            self.conn = sqlite3.connect(config["DEFAULT"]["database"])
+            new_db = False
+
+            if not os.path.isfile(self.config["DEFAULT"]["database"]):
+                new_db = True
+
+            self.conn = apsw.Connection(self.config["DEFAULT"]["database"],
+                                        apsw.SQLITE_OPEN_READWRITE |
+                                        apsw.SQLITE_OPEN_CREATE)
+
+            if new_db:
+                self.create_tables()
+            else:
+                library_info = os.stat(self.config["DEFAULT"]["database"])
+                cache_size_kb = round((library_info.st_size * 1.2) / 1024)
+
+                cursor = self.conn.cursor()
+                # Setting pragma with ? placeholder errors out
+                cursor.execute("pragma cache_size=%s" % cache_size_kb)
+                cursor.close()
 
         def __str__(self):
             return repr(self)
 
-        def execute(self, *args):
-            return self.conn.execute(*args)
-
         def cursor(self):
             return self.conn.cursor()
 
-        def commit(self):
-            return self.conn.commit()
-
         def close(self):
             return self.conn.close()
 
-        def rollback(self):
-            return self.conn.rollback()
-
-        def executemany(self, *args):
-            return self.conn.executemany(*args)
-
-        def executescript(self, *args):
-            return self.conn.executescript(*args)
-
-        def create_function(self, *args):
-            return self.conn.create_function(*args)
-
-        def create_aggregate(self, *args):
-            return self.conn.create_aggregate(*args)
-
-        def create_collation(self, *args):
-            return self.conn.create_collation(*args)
-
         def interrupt(self):
             return self.conn.interrupt()
 
+        def last_insert_rowid(self):
+            return self.conn.last_insert_rowid()
+
+        def create_tables(self):
+            cursor = self.conn.cursor()
+            cursor.execute("""CREATE TABLE IF NOT EXISTS album (id
+                           INTEGER PRIMARY KEY, name TEXT, date TEXT,
+                           musicbrainz_albumid TEXT)""")
+            cursor.execute("""CREATE TABLE IF NOT EXISTS album_artist (
+                    album_id INTEGER,
+                    artist_id INTEGER,
+                    CONSTRAINT ALBUM_ARTIST_PK PRIMARY KEY (album_id,
+                    artist_id),
+                    CONSTRAINT ALBUM_ARTIST_FK_ALBUM FOREIGN KEY (album_id)
+                        REFERENCES album(id),
+                    CONSTRAINT ALBUM_ARTIST_FK_ARTIST FOREIGN KEY
+                        (artist_id) REFERENCES artist(id)
+                )""")
+            cursor.execute("""CREATE TABLE IF NOT EXISTS album_track (
+                    album_id INTEGER,
+                    track_id INTEGER,
+                    CONSTRAINT ALBUM_TRACK_PK PRIMARY KEY (album_id,
+                        track_id),
+                    FOREIGN KEY(album_id) REFERENCES album(id),
+                    FOREIGN KEY(track_id) REFERENCES track(id)
+                )""")
+            cursor.execute("""CREATE TABLE IF NOT EXISTS artist (id
+                    INTEGER PRIMARY KEY, name
+                    TEXT(2000000000), sortname TEXT(2000000000),
+                    musicbrainz_artistid TEXT(2000000000))""")
+            cursor.execute("""CREATE TABLE IF NOT EXISTS artist_track (
+                    artist_id INTEGER,
+                    track_id INTEGER,
+                    CONSTRAINT ARTIST_TRACK_PK PRIMARY KEY (artist_id,
+                        track_id),
+                    FOREIGN KEY(artist_id) REFERENCES artist(id),
+                    FOREIGN KEY(track_id) REFERENCES track(id)
+                )""")
+            cursor.execute("""CREATE TABLE IF NOT EXISTS track (
+                    id INTEGER,
+                    tracknumber INTEGER,
+                    name TEXT(2000000000),
+                    grouping TEXT(2000000000),
+                    filename TEXT(2000000000),
+                    CONSTRAINT TRACK_PK PRIMARY KEY (id)
+                )""")
+            cursor.execute("""CREATE UNIQUE INDEX IF NOT EXISTS
+                           artist_musicbrainz_artistid ON
+                           artist(musicbrainz_artistid ASC)""")
+            cursor.execute("""CREATE INDEX IF NOT EXISTS track_filename_IDX
+                           ON track(filename)""")
+            cursor.execute("""CREATE INDEX IF NOT EXISTS track_grouping_IDX
+                           ON track(grouping)""")
+            cursor.execute("""CREATE INDEX IF NOT EXISTS track_name_IDX ON
+                           track(name)""")
+            cursor.execute("""CREATE INDEX IF NOT EXISTS
+                           track_tracknumber_IDX ON track(tracknumber)""")
+            cursor.close()
+
     instance = None
 
-    def __new__(cls):
+    def __new__(self):
         if not DbManager.instance:
             DbManager.instance = DbManager.__DbManager()
-
+        
         return DbManager.instance
-- 
cgit v1.2.3