X-Git-Url: https://git.kaliko.me/?a=blobdiff_plain;f=sima%2Flib%2Fdb.py;h=91cf03721325d716d193727cbdaf2f690ea2cd97;hb=798ce9e58b7725118a05245ddb723f6e909ded95;hp=9ce90820d3e3d193c7a19cdbe98f48b75fa88839;hpb=9c754908e645834a53ce3906aac76a0aa92e98f4;p=mpd-sima.git

diff --git a/sima/lib/db.py b/sima/lib/db.py
index 9ce9082..91cf037 100644
--- a/sima/lib/db.py
+++ b/sima/lib/db.py
@@ -26,6 +26,7 @@ __HIST_DURATION__ = int(30 * 24)  # in hours
 
 import sqlite3
 
+from collections import deque
 from datetime import (datetime, timedelta)
 
 from sima.lib.meta import Artist, Album
@@ -95,53 +96,77 @@ class SimaDB:
         # Create cleanup triggers:
         # DELETE history → Tracks table
         connection.execute('''
-           CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
-           AFTER DELETE ON history
-           WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
-                 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
-           BEGIN
-            DELETE FROM tracks WHERE id = old.track;
-           END;
-           ''')
+            CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
+            AFTER DELETE ON history
+            WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
+                  (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
+            BEGIN
+             DELETE FROM tracks WHERE id = old.track;
+            END;
+            ''')
         # DELETE Tracks → Artists table
         connection.execute('''
-           CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
-           AFTER DELETE ON tracks
-           WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
-                 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
-           BEGIN
-            DELETE FROM artists WHERE id = old.artist;
-           END;
-           ''')
+            CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
+            AFTER DELETE ON tracks
+            WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
+                  (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
+            BEGIN
+             DELETE FROM artists WHERE id = old.artist;
+            END;
+            ''')
         # DELETE Tracks → Albums table
         connection.execute('''
-           CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
-           AFTER DELETE ON tracks
-           WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
-                 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
-           BEGIN
-            DELETE FROM albums WHERE id = old.album;
-           END;
-           ''')
+            CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
+            AFTER DELETE ON tracks
+            WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
+                  (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
+            BEGIN
+             DELETE FROM albums WHERE id = old.album;
+            END;
+            ''')
         # DELETE Tracks → cleanup AlbumArtists table
         connection.execute('''
-           CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
-           AFTER DELETE ON tracks
-           WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
-           BEGIN
-            DELETE FROM albumartists WHERE id = old.albumartist;
-           END;
-           ''')
+            CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
+            AFTER DELETE ON tracks
+            WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
+            BEGIN
+             DELETE FROM albumartists WHERE id = old.albumartist;
+            END;
+            ''')
         # DELETE blocklist → Tracks table
         connection.execute('''
-           CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
-           AFTER DELETE ON blocklist
-           WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
-                 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
-           BEGIN
-            DELETE FROM tracks WHERE id = old.track;
-           END;
-           ''')
+            CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
+            AFTER DELETE ON blocklist
+            WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
+                  (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
+            BEGIN
+             DELETE FROM tracks WHERE id = old.track;
+            END;
+            ''')
+        # DELETE blocklist → Artists table
+        # The "SELECT count(*) FROM blocklist" is useless,
+        # there can be only one blocklist.artist
+        connection.execute('''
+            CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_artists
+            AFTER DELETE ON blocklist
+            WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
+                  (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
+            BEGIN
+             DELETE FROM artists WHERE id = old.artist;
+            END;
+            ''')
+        # DELETE Tracks → Albums table
+        # The "SELECT count(*) FROM blocklist" is useless,
+        # there can be only one blocklist.album
+        connection.execute('''
+            CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_albums
+            AFTER DELETE ON blocklist
+            WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
+                  (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
+            BEGIN
+             DELETE FROM albums WHERE id = old.album;
+            END;
+            ''')
         self.close_database_connection(connection)
 
     def drop_all(self):
@@ -152,13 +177,18 @@ class SimaDB:
             connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
         connection.close()
 
-    def _remove_blocklist_id(self, blid):
+    def _remove_blocklist_id(self, blid, with_connection=None):
         """Remove id"""
+        if with_connection:
+            connection = with_connection
+        else:
+            connection = self.get_database_connection()
         connection = self.get_database_connection()
         connection.execute('DELETE FROM blocklist'
                            ' WHERE blocklist.id = ?', (blid,))
         connection.commit()
-        self.close_database_connection(connection)
+        if not with_connection:
+            self.close_database_connection(connection)
 
     def _get_album(self, album, connection):
         if album.mbid:
@@ -365,50 +395,115 @@ class SimaDB:
         connection = self.get_database_connection()
         connection.execute("DELETE FROM history WHERE last_play"
                            " < datetime('now', '-%i hours')" % duration)
+        connection.execute('VACUUM')
         connection.commit()
         self.close_database_connection(connection)
 
-    def fetch_artists_history(self, duration=__HIST_DURATION__):
+    def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__):
+        """
+        :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
+        """
         date = datetime.utcnow() - timedelta(hours=duration)
         connection = self.get_database_connection()
         connection.row_factory = sqlite3.Row
         rows = connection.execute("""
-                SELECT artists.name AS name,
-                       artists.mbid as mbid
+                SELECT albums.name AS name,
+                       albums.mbid as mbid,
+                       artists.name as artist,
+                       artists.mbid as artist_mbib
                 FROM history
                 JOIN tracks ON history.track = tracks.id
+                LEFT OUTER JOIN albums ON tracks.album = albums.id
                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
-                WHERE history.last_play > ?
+                WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
                 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
         hist = list()
         for row in rows:
-            if hist and hist[-1] == Album(**row):  # remove consecutive dupes
+            vals = dict(row)
+            artist = Artist(name=vals.pop('artist'),
+                            mbid=vals.pop('artist_mbib'))
+            if needle:
+                if needle != artist:
+                    continue
+            album = Album(**vals, artist=artist)
+            if hist and hist[-1] == album:
+                # remove consecutive dupes
                 continue
-            hist.append(Album(**row))
+            hist.append(album)
         connection.close()
         return hist
 
-    def fetch_history(self, duration=__HIST_DURATION__):
-        """Fetches tracks history, more recent first
-        :param int duration: How long ago to fetch history from
+    def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
+        """Returns a list of Artist objects
+        :param sima.lib.meta.Artist|sima.lib.meta.MetaContainer needle: When specified, returns history for this artist, it's actually testing the artist presence in history.
+        :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
         """
         date = datetime.utcnow() - timedelta(hours=duration)
         connection = self.get_database_connection()
         connection.row_factory = sqlite3.Row
         rows = connection.execute("""
-                SELECT tracks.title, tracks.file, artists.name AS artist,
-                       albumartists.name AS albumartist,
-                       artists.mbid as musicbrainz_artistid,
-                       albums.name AS album,
-                       albums.mbid AS musicbrainz_albumid,
-                       tracks.mbid as musicbrainz_trackid
+                SELECT artists.name AS name,
+                       artists.mbid as mbid
                 FROM history
                 JOIN tracks ON history.track = tracks.id
                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
-                LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
-                LEFT OUTER JOIN albums ON tracks.album = albums.id
-                WHERE history.last_play > ?
+                WHERE history.last_play > ? AND artists.name NOT NULL
                 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
+        last = deque(maxlen=1)
+        hist = list()
+        for row in rows:
+            artist = Artist(**row)
+            if last and last[0] == artist:  # remove consecutive dupes
+                continue
+            last.append(artist)
+            if needle and isinstance(needle, (Artist, str)):
+                if needle == artist:
+                    hist.append(artist)  # No need to go further
+                    break
+                continue
+            elif needle and getattr(needle, '__contains__'):
+                if artist in needle:
+                    hist.append(artist)  # No need to go further
+                continue
+            hist.append(artist)
+        return hist
+
+    def fetch_history(self, artist=None, duration=__HIST_DURATION__):
+        """Fetches tracks history, more recent first
+        :param sima.lib.meta.Artist artist: limit history to this artist
+        :param int duration: How long ago to fetch history from
+        """
+        date = datetime.utcnow() - timedelta(hours=duration)
+        connection = self.get_database_connection()
+        connection.row_factory = sqlite3.Row
+        sql = """
+              SELECT tracks.title, tracks.file, artists.name AS artist,
+                     albumartists.name AS albumartist,
+                     artists.mbid as musicbrainz_artistid,
+                     albums.name AS album,
+                     albums.mbid AS musicbrainz_albumid,
+                     tracks.mbid as musicbrainz_trackid
+              FROM history
+              JOIN tracks ON history.track = tracks.id
+              LEFT OUTER JOIN artists ON tracks.artist = artists.id
+              LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
+              LEFT OUTER JOIN albums ON tracks.album = albums.id
+              WHERE history.last_play > ?
+              """
+        if artist:
+            if artist.mbid:
+                rows = connection.execute(sql+"""
+                        AND artists.mbid = ?
+                        ORDER BY history.last_play DESC""",
+                                          (date.isoformat(' '), artist.mbid))
+            else:
+                rows = connection.execute(sql+"""
+                        AND artists.name = ?
+                        ORDER BY history.last_play DESC""",
+                                          (date.isoformat(' '), artist.name))
+        else:
+            rows = connection.execute(sql+'ORDER BY history.last_play DESC',
+                                      (date.isoformat(' '),))
         hist = list()
         for row in rows:
             hist.append(Track(**row))
@@ -426,7 +521,7 @@ class SimaDB:
             connection = self.get_database_connection()
         track_id = self.get_track(track, with_connection=connection, add=True)
         rows = connection.execute(
-            "SELECT * FROM blocklist WHERE track = ?", (track_id,))
+            "SELECT id FROM blocklist WHERE track = ?", (track_id,))
         if not rows.fetchone():
             if not add:
                 return None
@@ -434,13 +529,13 @@ class SimaDB:
                                (track_id,))
             connection.commit()
         rows = connection.execute(
-            "SELECT * FROM blocklist WHERE track = ?", (track_id,))
+            "SELECT id FROM blocklist WHERE track = ?", (track_id,))
         return rows.fetchone()[0]
 
     def get_bl_album(self, album, with_connection=None, add=True):
         """Add an album to blocklist
         :param sima.lib.meta.Album: Album object to add to blocklist
-        :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, ele create a new one
+        :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
         :param bool add: Default is to add a new record, set to False to fetch associated record"""
         if with_connection:
             connection = with_connection
@@ -448,7 +543,7 @@ class SimaDB:
             connection = self.get_database_connection()
         album_id = self.get_album(album, with_connection=connection, add=True)
         rows = connection.execute(
-            "SELECT * FROM blocklist WHERE album = ?", (album_id,))
+            "SELECT id FROM blocklist WHERE album = ?", (album_id,))
         if not rows.fetchone():
             if not add:
                 return None
@@ -456,13 +551,13 @@ class SimaDB:
                                (album_id,))
             connection.commit()
         rows = connection.execute(
-            "SELECT * FROM blocklist WHERE album = ?", (album_id,))
-        return rows.fetchone()
+            "SELECT id FROM blocklist WHERE album = ?", (album_id,))
+        return rows.fetchone()[0]
 
     def get_bl_artist(self, artist, with_connection=None, add=True):
         """Add an artist to blocklist
         :param sima.lib.meta.Artist: Artist object to add to blocklist
-        :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, ele create a new one
+        :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
         :param bool add: Default is to add a new record, set to False to fetch associated record"""
         if with_connection:
             connection = with_connection
@@ -470,7 +565,7 @@ class SimaDB:
             connection = self.get_database_connection()
         artist_id = self.get_artist(artist, with_connection=connection, add=True)
         rows = connection.execute(
-            "SELECT * FROM blocklist WHERE artist = ?", (artist_id,))
+            "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
         if not rows.fetchone():
             if not add:
                 return None
@@ -478,29 +573,24 @@ class SimaDB:
                                (artist_id,))
             connection.commit()
         rows = connection.execute(
-            "SELECT * FROM blocklist WHERE artist = ?", (artist_id,))
-        return rows.fetchone()
-
-
-def main():
-    DEVOLT = {
-        'album': 'Grey',
-        'albumartist': 'Devolt',
-        'artist': 'Devolt',
-        'date': '2011-12-01',
-        'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3',
-        'musicbrainz_albumartistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99',
-        'musicbrainz_albumid': 'ea2ef2cf-59e1-443a-817e-9066e3e0be4b',
-        'musicbrainz_artistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99',
-        'musicbrainz_trackid': 'fabf8fc9-2ae5-49c9-8214-a839c958d872',
-        'duration': '220.000',
-        'title': 'Crazy'}
-    db = SimaDB('/dev/shm/test.sqlite')
-    db.create_db()
-    db.add_history(Track(**DEVOLT))
-    DEVOLT['file'] = 'foo'
-    print(db.get_bl_track(Track(**DEVOLT)))
-    db.add_history(Track(**DEVOLT))
+            "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
+        return rows.fetchone()[0]
+
+    def delete_bl(self, track=None, album=None, artist=None):
+        if not (track or album or artist):
+            return
+        connection = self.get_database_connection()
+        blid = None
+        if track:
+            blid = self.get_bl_track(track, with_connection=connection)
+        if album:
+            blid = self.get_bl_album(album, with_connection=connection)
+        if artist:
+            blid = self.get_bl_artist(artist, with_connection=connection)
+        if not blid:
+            return
+        self._remove_blocklist_id(blid, with_connection=connection)
+
 
 # VIM MODLINE
 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8