summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--.gitignore1
-rw-r--r--db/db_manager.py116
-rw-r--r--library.dbbin18432 -> 0 bytes
-rw-r--r--models/album.py42
-rw-r--r--models/artist.py42
-rw-r--r--models/track.py75
6 files changed, 171 insertions, 105 deletions
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
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/library.db b/library.db
deleted file mode 100644
index 8f4e338..0000000
--- a/library.db
+++ /dev/null
Binary files differ
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
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-23 20:51:59 +0000