summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorMichaël Ball <michael.ball@gmail.com>2015-08-02 10:47:25 +0100
committerMichaël Ball <michael.ball@gmail.com>2015-08-02 10:47:25 +0100
commit35a21c4d12ae85aed699d4117ebbdb4c510ad40f (patch)
treeda89869e7e26e4c0c6d9426cc544acf425e82f71 /db
parenteb962f7008660a6ce5e70927bf4486454519444d (diff)
parent74e967d16050472e1e30b2301b8460dad1bc91f0 (diff)
Merge branch 'feature/advanced-sqlite' into develop
Diffstat (limited to 'db')
-rw-r--r--db/db_manager.py116
1 files changed, 86 insertions, 30 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