Add fetch history methods

[mpd-sima.git] / sima / lib / db.py

diff --git a/sima/lib/db.py b/sima/lib/db.py
index 6d7836a015b727a28f944a3e961abfadfa019d18..91cf03721325d716d193727cbdaf2f690ea2cd97 100644 (file)
--- a/sima/lib/db.py
+++ b/sima/lib/db.py
@@ -26,12 +26,19 @@ __HIST_DURATION__ = int(30 * 24)  # in hours
 
 import sqlite3
 
 
 import sqlite3
 

+from collections import deque

 from datetime import (datetime, timedelta)
 
 from sima.lib.meta import Artist, Album
 from sima.lib.track import Track
 
 
 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"
 
 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))')
             '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:
         # 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('''
         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('''
         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('''
         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('''
         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)
 
         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(
     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."""
         """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:
         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 = 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)
 
         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("""
         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
                 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
                 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(' '),))
                 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
 
         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
 
 # VIM MODLINE
 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8