From 52791f111e9ee885389377a91cab44faa61c32b5 Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Fri, 27 Nov 2015 19:24:36 +0000 Subject: Better library management --- db/db_manager.py | 85 ++++++++++++++++++++++++++++++++++++++++++++++---------- library.py | 50 ++++++++++++++++++--------------- models/album.py | 23 +++++++++++++++ models/artist.py | 4 +-- models/track.py | 36 +++++++++++++----------- 5 files changed, 144 insertions(+), 54 deletions(-) diff --git a/db/db_manager.py b/db/db_manager.py index ca47ae4..82168d0 100644 --- a/db/db_manager.py +++ b/db/db_manager.py @@ -1,7 +1,7 @@ import configparser import os - -import apsw +import sqlite3 +import tempfile class DbManager: @@ -9,6 +9,57 @@ class DbManager: config = configparser.ConfigParser() config.read("mach2.ini") + def iterdump(connection): + cu = connection.cursor() + yield("BEGIN TRANSACTION;") + + q = """ + SELECT "name", "type", "sql" + FROM "sqlite_master" + WHERE "sql" NOT NULL AND + "type" == 'table' + ORDER BY "name"; + """ + schema_res = cu.execute(q).fetchall() + for table_name, type, sql in schema_res: + if table_name == "sqlite_sequence": + yield("DELETE FROM \"sqlite_sequence\";") + elif table_name == "sqlite_stat1": + yield("ANALYZE \"sqlite_master\";") + elif table_name.startswith("sqlite_"): + continue + else: + yield("{0};".format(sql)) + + table_name_ident = table_name.replace("\"", "\"\"") + res = cu.execute( + "PRAGMA table_info(\"{0}\")".format(table_name_ident)) + column_names = [ + str(table_info[1]) for table_info in res.fetchall()] + q = """ + SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}"; + """.format( + table_name_ident, + ",".join( + """'||quote("{0}")||'""".format( + col.replace( + "\"", "\"\"")) for col in column_names)) + query_res = cu.execute(q) + for row in query_res: + yield("{0};".format(row[0])) + + q = """ + SELECT "name", "type", "sql" + FROM "sqlite_master" + WHERE "sql" NOT NULL AND + "type" IN ('index', 'trigger', 'view') + """ + schema_res = cu.execute(q) + for name, type, sql in schema_res.fetchall(): + yield("{0};".format(sql)) + + yield("COMMIT;") + def __init__(self): new_db = False cache_size_kb = 9766 @@ -17,31 +68,40 @@ class DbManager: new_db = True if new_db: - self.conn = apsw.Connection(":memory:") + self.conn = sqlite3.connect(":memory:") self.create_tables() else: - self.conn = apsw.Connection(self.config["DEFAULT"]["database"]) + self.conn = sqlite3.connect(self.config["DEFAULT"]["database"]) 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 + # Setting pragma with ? placeholder produces an error cursor.execute("pragma cache_size=-%s" % cache_size_kb) cursor.close() + self.conn.row_factory = sqlite3.Row + def __del__(self): if not os.path.isfile(self.config["DEFAULT"]["database"]): - tempconn = apsw.Connection(self.config["DEFAULT"]["database"], - apsw.SQLITE_OPEN_READWRITE | - apsw.SQLITE_OPEN_CREATE) + script = "" + + for line in DbManager.__DbManager.iterdump(self.conn): + script = "\n".join((script, line)) - with tempconn.backup("main", - self.conn, "main") as backup: - backup.step() + tempconn = sqlite3.connect( + self.config["DEFAULT"]["database"]) + tempcur = tempconn.cursor() + + tempcur.executescript(script) + tempcur.close() def __str__(self): return repr(self) + def commit(self): + return self.conn.commit() + def cursor(self): return self.conn.cursor() @@ -51,9 +111,6 @@ class DbManager: 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 diff --git a/library.py b/library.py index 762b18b..6cdb732 100755 --- a/library.py +++ b/library.py @@ -1,14 +1,29 @@ #!/usr/bin/env python import configparser +import gevent +from gevent import queue +import logging import mutagen import os from models.track import Track -def run(path=None): - print("Scanning files") +file_store = queue.Queue() + +logging.basicConfig(format="%(asctime)s %(message)s", level=logging.DEBUG) + + +def store_track_task(): + while not file_store.empty(): + path = file_store.get() + m = mutagen.File(path, easy=True) + Track.store(path, m) + + gevent.sleep(0) + +def run(path=None): if path is not None: if os.path.isdir(path): store_dir(path) @@ -26,30 +41,21 @@ def store_file(path): def store_dir(path): - file_store = [] + logger = logging.getLogger("store_dir") + logger.info("Scanning files") + allowed_extensions = [".mp3", ".ogg", ".flac", ".wav", ".aac", ".ape"] for root, dirs, files in os.walk(path): for name in files: file_path = "".join([root, "/", name]) - file_store.append(file_path) - - file_store.sort() - j = 0 - media_files = 0 - print("Storing files") - for file_path in file_store: - j += 1 - m = mutagen.File(file_path, easy=True) - if m: - if not Track.store(file_path, m): - print("Problem saving %s" % (file_path,)) - - media_files += 1 - print( - "%d%% complete, (%d files)" % (((j / len(file_store)) * 100), - j) - ) - print("Stored %d tracks" % (media_files,)) + file, ext = os.path.splitext(file_path) + + if ext in allowed_extensions: + file_store.put(file_path) + + logger.info("Storing tracks") + gevent.joinall([gevent.spawn(store_track_task)] * 6) + logger.info("Done") def delete_file(path): diff --git a/models/album.py b/models/album.py index 216b615..9ca3798 100644 --- a/models/album.py +++ b/models/album.py @@ -134,3 +134,26 @@ class Album(): ) return albums + + def all(order="album.id", direction="ASC", limit=None, offset=None): + db = DbManager() + cursor = db.cursor() + albums = [] + + select_string = """SELECT * FROM album LEFT JOIN album_artist ON + album_artist.album_id = album.id LEFT JOIN artist ON + album_artist.artist_id = artist.id ORDER BY %s %s""" % (order, + direction) + + if limit is not None and offset is not None: + select_string = " ".join((select_string, + "LIMIT %s OFFSET %s" % (limit, offset))) + + result = cursor.execute(select_string) + + for row in result: + albums.append( + Album(id=row[0], name=row[1], date=row[2]) + ) + + return albums diff --git a/models/artist.py b/models/artist.py index 2e69354..fee081c 100644 --- a/models/artist.py +++ b/models/artist.py @@ -141,8 +141,8 @@ class Artist: cursor = db.cursor() artists = [] - select_string = """SELECT * FROM artist ORDER BY %s %s""" % (order, - direction) + select_string = "SELECT * FROM artist ORDER BY %s %s" % (order, + direction) if limit is not None and offset is not None: select_string = " ".join((select_string, diff --git a/models/track.py b/models/track.py index 8b16d26..dead1f8 100644 --- a/models/track.py +++ b/models/track.py @@ -1,4 +1,5 @@ -import apsw +import logging +import sqlite3 from common import utils from db.db_manager import DbManager @@ -6,6 +7,9 @@ from models.artist import Artist from models.album import Album +logging.basicConfig(format="%(asctime)s %(message)s", level=logging.DEBUG) + + class Track: def __init__(self, id=None, **kwargs): @@ -136,7 +140,7 @@ class Track: musicbrainz_artistid)) artist = Artist( - id=db.last_insert_rowid(), name=artist_name, + id=c.lastrowid, name=artist_name, sortname=artistsort, musicbrainz_artistid=musicbrainz_artistid ) @@ -180,7 +184,7 @@ class Track: musicbrainz_albumid) VALUES (?,?,?)""", (album_name, album_date, mb_albumid)) - album = Album(id=db.last_insert_rowid(), name=album_name, + album = Album(id=c.lastrowid, name=album_name, date=album_date, musicbrainz_albumid=mb_albumid) elif album_name: @@ -197,7 +201,7 @@ class Track: c.execute("""INSERT INTO album (name, `date`) VALUES (?,?)""", (album_name, album_date)) - album = Album(id=db.last_insert_rowid(), name=album_name, + album = Album(id=c.lastrowid, name=album_name, date=album_date) if album: @@ -219,7 +223,7 @@ class Track: album_id) VALUES(?,?)""", (artist.id, album.id) ) - except apsw.ConstraintError: + except sqlite3.IntegrityError: pass track_number = None @@ -248,7 +252,7 @@ class Track: c.execute("""INSERT INTO album_track (album_id, track_id) VALUES(?,?)""", (album.id, self.id)) - except apsw.ConstraintError: + except sqlite3.IntegrityError: pass for artist in artists: @@ -256,10 +260,10 @@ class Track: c.execute("""INSERT INTO artist_track (artist_id, track_id) VALUES(?,?)""", (artist.id, self.id)) - except apsw.ConstraintError: + except sqlite3.IntegrityError: pass - c.execute("COMMIT TRANSACTION") + db.commit() return True @@ -411,7 +415,7 @@ class Track: musicbrainz_artistid)) artist = Artist( - id=db.last_insert_rowid(), name=artist_name, + id=c.lastrowid, name=artist_name, sortname=artistsort, musicbrainz_artistid=musicbrainz_artistid ) @@ -455,7 +459,7 @@ class Track: musicbrainz_albumid) VALUES (?,?,?)""", (album_name, album_date, mb_albumid)) - album = Album(id=db.last_insert_rowid(), name=album_name, + album = Album(id=c.lastrowid, name=album_name, date=album_date, musicbrainz_albumid=mb_albumid) elif album_name: @@ -473,7 +477,7 @@ class Track: c.execute("""INSERT INTO album (name, `date`) VALUES (?,?)""", (album_name, album_date)) - album = Album(id=db.last_insert_rowid(), name=album_name, + album = Album(id=c.lastrowid, name=album_name, date=album_date) for artist in artists: @@ -484,7 +488,7 @@ class Track: album_id) VALUES(?,?)""", (artist.id, album.id) ) - except apsw.ConstraintError: + except sqlite3.IntegrityError: pass track_number = None @@ -516,7 +520,7 @@ class Track: (track_number, track_name, track_grouping, filename)) - track = Track(id=db.last_insert_rowid(), tracknumber=track_number, + track = Track(id=c.lastrowid, tracknumber=track_number, name=track_name, grouping=track_grouping, filename=filename) @@ -525,7 +529,7 @@ class Track: c.execute("""INSERT INTO album_track (album_id, track_id) VALUES(?,?)""", (album.id, track.id)) - except apsw.ConstraintError: + except sqlite3.IntegrityError: pass for artist in artists: @@ -533,9 +537,9 @@ class Track: c.execute("""INSERT INTO artist_track (artist_id, track_id) VALUES(?,?)""", (artist.id, track.id)) - except apsw.ConstraintError: + except sqlite3.IntegrityError: pass - c.execute("COMMIT TRANSACTION") + db.commit() return True -- cgit v1.2.3