Better library management

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