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

diff --git a/sima/lib/db.py b/sima/lib/db.py
index 6d7836a..91cf037 100644
--- a/sima/lib/db.py
+++ b/sima/lib/db.py
@@ -26,12 +26,19 @@ __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
 from sima.lib.track import Track
 
 
+class SimaDBError(Exception):
+    """
+    Exceptions.
+    """
+
+
 class SimaDB:
     "SQLite management"
 
@@ -80,45 +87,109 @@ class SimaDB:
             'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, '
             'last_play TIMESTAMP, track integer, '
             'FOREIGN KEY(track) REFERENCES tracks(id))')
+        connection.execute(  # BLOCKLIST
+            'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, '
+            'artist INTEGER, album INTEGER, track INTEGER, '
+            'FOREIGN KEY(artist) REFERENCES artists(id), '
+            'FOREIGN KEY(album)  REFERENCES albums(id), '
+            'FOREIGN KEY(track)  REFERENCES tracks(id))')
         # Create cleanup triggers:
-        # Tracks table
+        # 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;
+            ''')
+        # 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;
+            ''')
+        # 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;
+            ''')
+        # DELETE Tracks → cleanup AlbumArtists table
         connection.execute('''
-                CREATE TRIGGER IF NOT EXISTS cleanup_tracks
-                AFTER DELETE ON history
-                WHEN ((SELECT count(*) FROM history WHERE track=old.id) = 0)
-                BEGIN
-                 DELETE FROM tracks WHERE id = old.id;
-                END;
-                ''')
-        # Artists table
+            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 cleanup_artists
-                AFTER DELETE ON tracks
-                WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0)
-                BEGIN
-                 DELETE FROM artists WHERE id = old.artist;
-                END;
-                ''')
-        # Albums table
+            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 cleanup_albums
-                AFTER DELETE ON tracks
-                WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0)
-                BEGIN
-                 DELETE FROM albums WHERE id = old.album;
-                END;
-                ''')
-        # AlbumArtists table
+            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 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_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):
+        connection = self.get_database_connection()
+        rows = connection.execute(
+                "SELECT name FROM sqlite_master WHERE type='table'")
+        for r in rows.fetchall():
+            connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
+        connection.close()
+
+    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()
+        if not with_connection:
+            self.close_database_connection(connection)
+
     def _get_album(self, album, connection):
         if album.mbid:
             return connection.execute(
@@ -250,6 +321,8 @@ class SimaDB:
         """Get a track from Tracks table, add if not existing,
         Attention: use Track() object!!
         if not in database insert new entry."""
+        if not track.file:
+            raise SimaDBError('Got a track with no file attribute: %r' % track)
         if with_connection:
             connection = with_connection
         else:
@@ -322,37 +395,202 @@ 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 get_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 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 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 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 > ?
+                LEFT OUTER JOIN artists ON tracks.artist = artists.id
+                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:
+            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)
+        connection.close()
+        return hist
+
+    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 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
+                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))
         connection.close()
         return hist
 
+    def get_bl_track(self, track, with_connection=None, add=True):
+        """Add a track to blocklist
+        :param sima.lib.track.Track track: Track object to add to blocklist
+        :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
+        else:
+            connection = self.get_database_connection()
+        track_id = self.get_track(track, with_connection=connection, add=True)
+        rows = connection.execute(
+            "SELECT id FROM blocklist WHERE track = ?", (track_id,))
+        if not rows.fetchone():
+            if not add:
+                return None
+            connection.execute('INSERT INTO blocklist (track) VALUES (?)',
+                               (track_id,))
+            connection.commit()
+        rows = connection.execute(
+            "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, 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
+        else:
+            connection = self.get_database_connection()
+        album_id = self.get_album(album, with_connection=connection, add=True)
+        rows = connection.execute(
+            "SELECT id FROM blocklist WHERE album = ?", (album_id,))
+        if not rows.fetchone():
+            if not add:
+                return None
+            connection.execute('INSERT INTO blocklist (album) VALUES (?)',
+                               (album_id,))
+            connection.commit()
+        rows = connection.execute(
+            "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, 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
+        else:
+            connection = self.get_database_connection()
+        artist_id = self.get_artist(artist, with_connection=connection, add=True)
+        rows = connection.execute(
+            "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
+        if not rows.fetchone():
+            if not add:
+                return None
+            connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
+                               (artist_id,))
+            connection.commit()
+        rows = connection.execute(
+            "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)
 
-def main():
-    db = SimaDB('/dev/shm/test.sqlite')
-    db.create_db()
 
 # VIM MODLINE
 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8