From 2363f972a796e8619e6fff1a9ed5ccf86a15f6bd Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Sun, 2 Aug 2015 10:45:58 +0100 Subject: Delete library.db from repo --- library.db | Bin 18432 -> 0 bytes 1 file changed, 0 insertions(+), 0 deletions(-) delete mode 100644 library.db diff --git a/library.db b/library.db deleted file mode 100644 index 8f4e338..0000000 Binary files a/library.db and /dev/null differ -- cgit v1.2.3 From 466ea0cdb8368e7286f1962bcf7693fa7e660a4c Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Sun, 2 Aug 2015 10:46:24 +0100 Subject: Ignore library.db --- .gitignore | 1 + 1 file changed, 1 insertion(+) diff --git a/.gitignore b/.gitignore index f18bf46..2feb85f 100644 --- a/.gitignore +++ b/.gitignore @@ -11,3 +11,4 @@ library.db-journal public tmp +library.db -- cgit v1.2.3 From 74e967d16050472e1e30b2301b8460dad1bc91f0 Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Sun, 2 Aug 2015 10:46:53 +0100 Subject: Move to different sqlite driver. Performance improvements. --- db/db_manager.py | 116 +++++++++++++++++++++++++++++++++++++++++-------------- models/album.py | 42 +++++++++++--------- models/artist.py | 42 +++++++++++--------- models/track.py | 75 +++++++++++++++++------------------ 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 -- cgit v1.2.3