Move to different sqlite driver. Performance improvements.

4 files changed, 170 insertions, 105 deletions

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
diff --git a/models/album.py b/models/album.py
index 24af0b5..b2625db 100644
--- a/models/album.py
+++ b/models/album.py
@@ -6,8 +6,10 @@ class Album():
     def __init__(self, id=None, **kwargs):
         if id is not None:
             db = DbManager()
-            for row in db.execute("""SELECT * FROM album WHERE id = ?""",
-                                  (id,)):
+            cursor = db.cursor()
+
+            for row in cursor.execute("SELECT * FROM album WHERE id = ?",
+                                      (id,)):
                 setattr(self, "id", id)
                 setattr(self, "name", row[1])
                 setattr(self, "date", row[2])
@@ -17,20 +19,19 @@ class Album():
 
     def delete(self):
         db = DbManager()
+        cursor = db.cursor()
 
         for track in self.tracks:
             track.delete()
 
         delete_sql = "DELETE FROM album WHERE id = ?"
-        db.execute(delete_sql, (self.id,))
+        cursor.execute(delete_sql, (self.id,))
 
         delete_track_rel_sql = "DELETE FROM album_track WHERE album_id = ?"
-        db.execute(delete_track_rel_sql, (self.id,))
+        cursor.execute(delete_track_rel_sql, (self.id,))
 
         delete_artist_rel_sql = "DELETE FROM album_artist WHERE album_id = ?"
-        db.execute(delete_artist_rel_sql, (self.id,))
-
-        db.commit()
+        cursor.execute(delete_artist_rel_sql, (self.id,))
 
         return True
 
@@ -42,11 +43,12 @@ class Album():
             setattr(self, "_artists", [])
 
             db = DbManager()
+            cursor = db.cursor()
 
-            for row in db.execute("""SELECT artist.* FROM artist INNER JOIN
-                                  album_artist ON artist.id =
-                                  album_artist.artist_id WHERE album_id = ?
-                                  ORDER BY name ASC""", (self.id,)):
+            for row in cursor.execute("""SELECT artist.* FROM artist INNER JOIN
+                                      album_artist ON artist.id =
+                                      album_artist.artist_id WHERE album_id = ?
+                                      ORDER BY name ASC""", (self.id,)):
                 artist = Artist(id=row[0], name=row[1], sortname=row[2],
                                 musicbrainz_artistid=row[3])
                 self._artists.append(artist)
@@ -61,11 +63,12 @@ class Album():
             setattr(self, "_tracks", [])
 
             db = DbManager()
+            cursor = db.cursor()
 
-            for row in db.execute("""SELECT track.* FROM track
-                                 INNER JOIN album_track ON track.id =
-                                 album_track.track_id WHERE album_id = ?
-                                 ORDER BY tracknumber ASC""", (self.id,)):
+            for row in cursor.execute("""SELECT track.* FROM track
+                                      INNER JOIN album_track ON track.id =
+                                      album_track.track_id WHERE album_id = ?
+                                      ORDER BY tracknumber ASC""", (self.id,)):
 
                 track = Track(id=row[0], tracknumber=row[1], name=row[2],
                               grouping=row[3], filename=row[4])
@@ -83,14 +86,14 @@ class Album():
 
         if len(dirty_attributes) > 0:
             db = DbManager()
+            cursor = db.cursor()
 
             set_clause = utils.update_clause_from_dict(dirty_attributes)
 
             dirty_attributes[id] = self.id
 
             sql = " ".join(("UPDATE album"), set_clause, "WHERE id = :id")
-            db.execute(sql, dirty_attributes)
-            db.commit()
+            cursor.execute(sql, dirty_attributes)
 
     def search(**search_params):
         """Find an album with the given params
@@ -103,6 +106,7 @@ class Album():
         albums = []
 
         db = DbManager()
+        cursor = db.cursor()
 
         # unpack search params
         where_params = {}
@@ -116,9 +120,9 @@ class Album():
         result = None
         if where_clause:
             statement = " ".join(("SELECT * FROM album", where_clause))
-            result = db.execute(statement, value_params)
+            result = cursor.execute(statement, value_params)
         else:
-            result = db.execute("SELECT * FROM album")
+            result = cursor.execute("SELECT * FROM album")
 
         for row in result:
             albums.append(
diff --git a/models/artist.py b/models/artist.py
index 7d36457..1ac27d5 100644
--- a/models/artist.py
+++ b/models/artist.py
@@ -6,8 +6,10 @@ class Artist:
     def __init__(self, id=None, **kwargs):
         if id is not None:
             db = DbManager()
-            for row in db.execute("""SELECT * FROM artist WHERE id = ?""",
-                                  (id,)):
+            cursor = db.cursor()
+
+            for row in cursor.execute("SELECT * FROM artist WHERE id = ?",
+                                      (id,)):
                 setattr(self, "id", id)
                 setattr(self, "name", row[1])
                 setattr(self, "sortname", row[2])
@@ -18,20 +20,19 @@ class Artist:
 
     def delete(self):
         db = DbManager()
+        cursor = db.cursor()
 
         for album in self.albums:
             album.delete()
 
         delete_sql = "DELETE FROM artist WHERE id = ?"
-        db.execute(delete_sql, (self.id,))
+        cursor.execute(delete_sql, (self.id,))
 
         delete_track_rel_sql = "DELETE FROM artist_track WHERE artist_id = ?"
-        db.execute(delete_track_rel_sql, (self.id,))
+        cursor.execute(delete_track_rel_sql, (self.id,))
 
         delete_album_rel_sql = "DELETE FROM album_artist WHERE artist_id = ?"
-        db.execute(delete_album_rel_sql, (self.id,))
-
-        db.commit()
+        cursor.execute(delete_album_rel_sql, (self.id,))
 
         return True
 
@@ -43,11 +44,12 @@ class Artist:
             setattr(self, "_tracks", [])
 
             db = DbManager()
+            cursor = db.cursor()
 
-            for row in db.execute("""SELECT track.* FROM track
-                                  INNER JOIN artist_track ON track.id =
-                                  artist_track.track_id WHERE artist_id = ?
-                                  ORDER BY name ASC""", (self.id,)):
+            for row in cursor.execute("""SELECT track.* FROM track
+                                      INNER JOIN artist_track ON track.id =
+                                      artist_track.track_id WHERE artist_id = ?
+                                      ORDER BY name ASC""", (self.id,)):
 
                 track = Track(id=row[0], tracknumber=row[1], name=row[2],
                               grouping=row[3], filename=row[4])
@@ -63,11 +65,12 @@ class Artist:
             setattr(self, "_albums", [])
 
             db = DbManager()
+            cursor = db.cursor()
 
-            for row in db.execute("""SELECT album.* FROM album
-                                  INNER JOIN album_artist ON album.id =
-                                  album_artist.album_id WHERE artist_id = ?
-                                  ORDER BY date ASC""", (self.id,)):
+            for row in cursor.execute("""SELECT album.* FROM album
+                                      INNER JOIN album_artist ON album.id =
+                                      album_artist.album_id WHERE artist_id = ?
+                                      ORDER BY date ASC""", (self.id,)):
                 album = Album(id=row[0], name=row[1], date=row[2])
                 self._albums.append(album)
 
@@ -83,14 +86,14 @@ class Artist:
 
         if len(dirty_attributes) > 0:
             db = DbManager()
+            cursor = db.cursor()
 
             set_clause = utils.update_clause_from_dict(dirty_attributes)
 
             dirty_attributes[id] = self.id
 
             sql = " ".join(("UPDATE artist"), set_clause, "WHERE id = :id")
-            db.execute(sql, dirty_attributes)
-            db.commit()
+            cursor.execute(sql, dirty_attributes)
 
     def search(**search_params):
         """Find an artist with the given params
@@ -103,6 +106,7 @@ class Artist:
         artists = []
 
         db = DbManager()
+        cursor = db.cursor()
 
         # unpack search params
         where_params = {}
@@ -116,9 +120,9 @@ class Artist:
         result = []
         if where_clause:
             statement = " ".join(("SELECT * FROM artist", where_clause))
-            result = db.execute(statement, value_params)
+            result = cursor.execute(statement, value_params)
         else:
-            result = db.execute("SELECT * FROM artist")
+            result = cursor.execute("SELECT * FROM artist")
 
         for row in result:
             artists.append(
diff --git a/models/track.py b/models/track.py
index 4d06872..854b3ee 100644
--- a/models/track.py
+++ b/models/track.py
@@ -1,4 +1,4 @@
-import sqlite3
+import apsw
 
 from common import utils
 from db.db_manager import DbManager
@@ -14,8 +14,10 @@ class Track:
 
         if id is not None:
             db = DbManager()
-            for row in db.execute("""SELECT * FROM track WHERE id = ?""",
-                                  (id,)):
+            cursor = db.cursor()
+
+            for row in cursor.execute("""SELECT * FROM track WHERE id = ?""",
+                                      (id,)):
                 setattr(self, "id", row[0])
                 setattr(self, "tracknumber", row[1])
                 setattr(self, "name", row[2])
@@ -28,10 +30,10 @@ class Track:
 
     def delete(self):
         db = DbManager()
+        cursor = db.cursor()
 
         delete_sql = "DELETE FROM track WHERE id = ?"
-        db.execute(delete_sql, (self.id,))
-        db.commit()
+        cursor.execute(delete_sql, (self.id,))
 
         return True
 
@@ -41,11 +43,12 @@ class Track:
             setattr(self, "_album", None)
 
             db = DbManager()
+            cursor = db.cursor()
 
-            for row in db.execute("""SELECT album.* FROM album INNER JOIN
-                                  album_track ON album.id =
-                                  album_track.album_id WHERE track_id = ?
-                                  LIMIT 1""", (self.id,)):
+            for row in cursor.execute("""SELECT album.* FROM album INNER JOIN
+                                      album_track ON album.id =
+                                      album_track.album_id WHERE track_id = ?
+                                      LIMIT 1""", (self.id,)):
                 setattr(self, "_album", Album(row[0]))
 
         return self._album
@@ -54,12 +57,13 @@ class Track:
     def artists(self):
         if not hasattr(self, "_artists"):
             db = DbManager()
+            cursor = db.cursor()
 
             setattr(self, "_artists", [])
-            for row in db.execute("""SELECT artist.* FROM artist INNER JOIN
-                                  artist_track ON artist.id =
-                                  artist_track.artist_id WHERE
-                                  artist.id = ?""", (self.id,)):
+            for row in cursor.execute("""SELECT artist.* FROM artist INNER JOIN
+                                      artist_track ON artist.id =
+                                      artist_track.artist_id WHERE
+                                      artist.id = ?""", (self.id,)):
                 self._artists.append(Artist(row[0]))
 
         return self._artists
@@ -130,7 +134,7 @@ class Track:
                                 musicbrainz_artistid))
 
                 artist = Artist(
-                    id=c.lastrowid, name=artist_name,
+                    id=db.last_insert_rowid(), name=artist_name,
                     sortname=artistsort,
                     musicbrainz_artistid=musicbrainz_artistid
                 )
@@ -174,7 +178,7 @@ class Track:
                           musicbrainz_albumid) VALUES (?,?,?)""",
                           (album_name, album_date, mb_albumid))
 
-                album = Album(id=c.lastrowid, name=album_name,
+                album = Album(id=db.last_insert_rowid(), name=album_name,
                               date=album_date, musicbrainz_albumid=mb_albumid)
 
         elif album_name:
@@ -191,7 +195,7 @@ class Track:
                 c.execute("""INSERT INTO album (name, `date`) VALUES
                 (?,?)""", (album_name, album_date))
 
-                album = Album(id=c.lastrowid, name=album_name,
+                album = Album(id=db.last_insert_rowid(), name=album_name,
                               date=album_date)
 
         if album:
@@ -213,7 +217,7 @@ class Track:
                         album_id) VALUES(?,?)""",
                         (artist.id, album.id)
                     )
-                except sqlite3.IntegrityError:
+                except apsw.ConstraintError:
                     pass
 
         track_number = None
@@ -242,7 +246,7 @@ class Track:
                 c.execute("""INSERT INTO album_track (album_id,
                           track_id) VALUES(?,?)""",
                           (album.id, self.id))
-            except sqlite3.IntegrityError:
+            except apsw.ConstraintError:
                 pass
 
         for artist in artists:
@@ -250,11 +254,9 @@ class Track:
                 c.execute("""INSERT INTO artist_track
                           (artist_id, track_id) VALUES(?,?)""",
                           (artist.id, self.id))
-            except sqlite3.IntegrityError:
+            except apsw.ConstraintError:
                 pass
 
-        db.commit()
-        c.close()
         return True
 
     def save(self):
@@ -267,14 +269,14 @@ class Track:
 
         if len(dirty_attributes) > 0:
             db = DbManager()
+            cursor = db.cursor()
 
             set_clause = utils.update_clause_from_dict(dirty_attributes)
 
             dirty_attributes[id] = self.id
 
             sql = " ".join(("UPDATE track"), set_clause, "WHERE id = :id")
-            db.execute(sql, dirty_attributes)
-            db.commit()
+            cursor.execute(sql, dirty_attributes)
 
     def search(**search_params):
         """Find a track with the given params
@@ -287,6 +289,7 @@ class Track:
         """
 
         db = DbManager()
+        cursor = db.cursor()
         tracks = []
 
         # unpack search params
@@ -301,9 +304,9 @@ class Track:
         result = None
         if where_clause:
             statement = " ".join(("SELECT * FROM track", where_clause))
-            result = db.execute(statement, value_params)
+            result = cursor.execute(statement, value_params)
         else:
-            result = db.execute("SELECT * FROM track")
+            result = cursor.execute("SELECT * FROM track")
 
         for row in result:
             tracks.append(
@@ -315,10 +318,11 @@ class Track:
 
     def find_by_path(path):
         db = DbManager()
+        cursor = db.cursor()
         track = None
 
-        for row in db.execute("SELECT * FROM track WHERE filename = ? LIMIT 1",
-                              (path,)):
+        for row in cursor.execute("""SELECT * FROM track WHERE filename = ?
+                                  LIMIT 1""", (path,)):
             track = Track(row[0])
 
         return track
@@ -393,7 +397,6 @@ class Track:
                     c.execute("""UPDATE artist SET
                             sortname = ? WHERE id = ?""",
                               (artistsort, artist.id))
-                    db.commit()
 
             else:
                 c.execute("""INSERT INTO artist
@@ -402,7 +405,7 @@ class Track:
                                 musicbrainz_artistid))
 
                 artist = Artist(
-                    id=c.lastrowid, name=artist_name,
+                    id=db.last_insert_rowid(), name=artist_name,
                     sortname=artistsort,
                     musicbrainz_artistid=musicbrainz_artistid
                 )
@@ -446,7 +449,7 @@ class Track:
                           musicbrainz_albumid) VALUES (?,?,?)""",
                           (album_name, album_date, mb_albumid))
 
-                album = Album(id=c.lastrowid, name=album_name,
+                album = Album(id=db.last_insert_rowid(), name=album_name,
                               date=album_date, musicbrainz_albumid=mb_albumid)
 
         elif album_name:
@@ -464,7 +467,7 @@ class Track:
                     c.execute("""INSERT INTO album (name, `date`) VALUES
                     (?,?)""", (album_name, album_date))
 
-                    album = Album(id=c.lastrowid, name=album_name,
+                    album = Album(id=db.last_insert_rowid(), name=album_name,
                                   date=album_date)
 
         for artist in artists:
@@ -475,7 +478,7 @@ class Track:
                         album_id) VALUES(?,?)""",
                         (artist.id, album.id)
                     )
-                except sqlite3.IntegrityError:
+                except apsw.ConstraintError:
                     pass
 
         track_number = None
@@ -507,7 +510,7 @@ class Track:
                       (track_number, track_name, track_grouping,
                        filename))
 
-            track = Track(id=c.lastrowid, tracknumber=track_number,
+            track = Track(id=db.last_insert_rowid(), tracknumber=track_number,
                           name=track_name, grouping=track_grouping,
                           filename=filename)
 
@@ -516,7 +519,7 @@ class Track:
                 c.execute("""INSERT INTO album_track (album_id,
                           track_id) VALUES(?,?)""",
                           (album.id, track.id))
-            except sqlite3.IntegrityError:
+            except apsw.ConstraintError:
                 pass
 
         for artist in artists:
@@ -524,9 +527,7 @@ class Track:
                 c.execute("""INSERT INTO artist_track
                           (artist_id, track_id) VALUES(?,?)""",
                           (artist.id, track.id))
-            except sqlite3.IntegrityError:
+            except apsw.ConstraintError:
                 pass
 
-        db.commit()
-        c.close()
         return True