X-Git-Url: https://git.kaliko.me/?a=blobdiff_plain;f=sima%2Flib%2Fsimadb.py;h=dddf4eb430564019db2d8c41195d658554417c8b;hb=HEAD;hp=07794dc6d24f079ea6cc5b11e234e6e02e15f4b4;hpb=6e0cb104973681f2344de9c5ae4f04c62686401b;p=mpd-sima.git diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index 07794dc..dddf4eb 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -19,19 +19,24 @@ """SQlite database library """ +#: DB Version __DB_VERSION__ = 4 -__HIST_DURATION__ = int(30 * 24) # in hours +#: Default history duration for both request and purge in hours +__HIST_DURATION__ = int(30 * 24) import sqlite3 from collections import deque from datetime import (datetime, timedelta) +from datetime import timezone + from sima.lib.meta import Artist, Album from sima.lib.track import Track +from sima.utils.utils import MPDSimaException -class SimaDBError(Exception): +class SimaDBError(MPDSimaException): """ Exceptions. """ @@ -45,8 +50,7 @@ class SimaDB: def get_database_connection(self): """get database reference""" - connection = sqlite3.connect( - self._db_path, isolation_level=None) + connection = sqlite3.connect(self._db_path, isolation_level=None) return connection def get_info(self): @@ -192,12 +196,12 @@ class SimaDB: 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]}') + for row in rows.fetchall(): + connection.execute(f'DROP TABLE IF EXISTS {row[0]}') connection.close() def _remove_blocklist_id(self, blid, with_connection=None): - """Remove id""" + """Remove a blocklist id""" if with_connection: connection = with_connection else: @@ -214,10 +218,9 @@ class SimaDB: return connection.execute( "SELECT id FROM albums WHERE mbid = ?", (album.mbid,)) - else: - return connection.execute( - "SELECT id FROM albums WHERE name = ? AND mbid IS NULL", - (album.name,)) + return connection.execute( + "SELECT id FROM albums WHERE name = ? AND mbid IS NULL", + (album.name,)) def get_album(self, album, with_connection=None, add=True): """get album information from the database. @@ -257,10 +260,9 @@ class SimaDB: return connection.execute( "SELECT id FROM albumartists WHERE mbid = ?", (artist.mbid,)) - else: - return connection.execute( - "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL", - (artist.name,)) + return connection.execute( + "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL", + (artist.name,)) def get_albumartist(self, artist, with_connection=None, add=True): """get albumartist information from the database. @@ -299,9 +301,8 @@ class SimaDB: return connection.execute( "SELECT id FROM artists WHERE mbid = ?", (artist.mbid,)) - else: - return connection.execute( - "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,)) + return connection.execute( + "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,)) def get_artist(self, artist, with_connection=None, add=True): """get artist information from the database. @@ -368,10 +369,11 @@ class SimaDB: def get_track(self, track, with_connection=None, add=True): """Get a track id from Tracks table, add if not existing, + :param sima.lib.track.Track track: track to use :param bool add: add non existing track to database""" if not track.file: - raise SimaDBError('Got a track with no file attribute: %r' % track) + raise SimaDBError(f'Got a track with no file attribute: {track}') if with_connection: connection = with_connection else: @@ -383,7 +385,8 @@ class SimaDB: connection.close() return row[0] if not add: # Not adding non existing track - connection.close() + if not with_connection: + connection.close() return None # Get an artist record or None if track.artist: @@ -424,7 +427,7 @@ class SimaDB: def _add_tracks_genres(self, track, connection): if not track.genres: - return None + return rows = connection.execute( "SELECT id FROM tracks WHERE file = ?", (track.file,)) trk_id = rows.fetchone()[0] @@ -435,10 +438,12 @@ class SimaDB: VALUES (?, ?)""", (trk_id, gen_id)) def add_history(self, track, date=None): - """Record last play date of track (ie. not a real exhautive play history). - :param track sima.lib.track.Track: track to add to history""" + """Record last play date of track (ie. not a real play history). + + :param sima.lib.track.Track track: track to add to history + :param datetime.datetime date: UTC datetime object (use "datetime.now(timezone.utc)" is not set)""" if not date: - date = datetime.now() + date = datetime.now(timezone.utc) connection = self.get_database_connection() track_id = self.get_track(track, with_connection=connection) rows = connection.execute("SELECT * FROM history WHERE track = ? ", @@ -453,8 +458,8 @@ class SimaDB: def purge_history(self, duration=__HIST_DURATION__): """Remove old entries in history - :param duration int: Purge history record older than duration in hours - (defaults to __HIST_DURATION__)""" + + :param int duration: Purge history record older than duration in hours""" connection = self.get_database_connection() connection.execute("DELETE FROM history WHERE last_play" " < datetime('now', '-%i hours')" % duration) @@ -465,30 +470,40 @@ class SimaDB: def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__): """ :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist. + :param int duration: How long ago to fetch history from (in hours) """ - date = datetime.utcnow() - timedelta(hours=duration) + date = datetime.now(timezone.utc) - timedelta(hours=duration) connection = self.get_database_connection() connection.row_factory = sqlite3.Row rows = connection.execute(""" SELECT albums.name AS name, albums.mbid as mbid, artists.name as artist, - artists.mbid as artist_mbib + artists.mbid as artist_mbib, + albumartists.name as albumartist, + albumartists.mbid as albumartist_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 + LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.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() + hist = [] for row in rows: vals = dict(row) - artist = Artist(name=vals.pop('artist'), - mbid=vals.pop('artist_mbib')) - if needle: - if needle != artist: + if needle: # Here use artist instead of albumartist + if needle != Artist(name=vals.get('artist'), + mbid=vals.get('artist_mbib')): continue - album = Album(**vals, artist=artist) + # Use albumartist / MBIDs if possible to build album artist + if not vals.get('albumartist'): + vals['albumartist'] = vals.get('artist') + if not vals.get('albumartist_mbib'): + vals['albumartist_mbib'] = vals.get('artist_mbib') + artist = Artist(name=vals.get('albumartist'), + mbid=vals.pop('albumartist_mbib')) + album = Album(**vals, Artist=artist) if hist and hist[-1] == album: # remove consecutive dupes continue @@ -498,10 +513,12 @@ class SimaDB: 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 + :param int duration: How long ago to fetch history from (in hours) + :type needle: sima.lib.meta.Artist or sima.lib.meta.MetaContainer """ - date = datetime.utcnow() - timedelta(hours=duration) + date = datetime.now(timezone.utc) - timedelta(hours=duration) connection = self.get_database_connection() connection.row_factory = sqlite3.Row rows = connection.execute(""" @@ -513,7 +530,7 @@ class SimaDB: WHERE history.last_play > ? AND artists.name NOT NULL ORDER BY history.last_play DESC""", (date.isoformat(' '),)) last = deque(maxlen=1) - hist = list() + hist = [] for row in rows: artist = Artist(**row) if last and last[0] == artist: # remove consecutive dupes @@ -524,7 +541,7 @@ class SimaDB: hist.append(artist) # No need to go further break continue - elif needle and getattr(needle, '__contains__'): + if needle and getattr(needle, '__contains__'): if artist in needle: hist.append(artist) # No need to go further continue @@ -533,7 +550,12 @@ class SimaDB: return hist def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20): - date = datetime.utcnow() - timedelta(hours=duration) + """Returns genre history + + :param int duration: How long ago to fetch history from (in hours) + :param int limit: number of genre to fetch + """ + date = datetime.now(timezone.utc) - timedelta(hours=duration) connection = self.get_database_connection() rows = connection.execute(""" SELECT genres.name, artists.name @@ -545,7 +567,7 @@ class SimaDB: WHERE history.last_play > ? AND genres.name NOT NULL ORDER BY history.last_play DESC """, (date.isoformat(' '),)) - genres = list() + genres = [] for row in rows: genres.append(row) if len({g[0] for g in genres}) >= limit: @@ -555,10 +577,11 @@ class SimaDB: 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 + :param int duration: How long ago to fetch history from (in hours) """ - date = datetime.utcnow() - timedelta(hours=duration) + date = datetime.now(timezone.utc) - timedelta(hours=duration) connection = self.get_database_connection() connection.row_factory = sqlite3.Row sql = """ @@ -589,7 +612,7 @@ class SimaDB: else: rows = connection.execute(sql+'ORDER BY history.last_play DESC', (date.isoformat(' '),)) - hist = list() + hist = [] for row in rows: hist.append(Track(**row)) connection.close() @@ -597,6 +620,7 @@ class SimaDB: 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""" @@ -604,24 +628,27 @@ class SimaDB: connection = with_connection else: connection = self.get_database_connection() - track_id = self.get_track(track, with_connection=connection, add=True) + track_id = self.get_track(track, with_connection=connection, add=add) rows = connection.execute( "SELECT id FROM blocklist WHERE track = ?", (track_id,)) if not rows.fetchone(): if not add: + if not with_connection: + connection.close() return None connection.execute('INSERT INTO blocklist (track) VALUES (?)', (track_id,)) connection.commit() rows = connection.execute( "SELECT id FROM blocklist WHERE track = ?", (track_id,)) - bl = rows.fetchone()[0] + blt = rows.fetchone()[0] if not with_connection: connection.close() - return bl + return blt 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""" @@ -629,24 +656,27 @@ class SimaDB: connection = with_connection else: connection = self.get_database_connection() - album_id = self.get_album(album, with_connection=connection, add=True) + album_id = self.get_album(album, with_connection=connection, add=add) rows = connection.execute( "SELECT id FROM blocklist WHERE album = ?", (album_id,)) if not rows.fetchone(): if not add: + if not with_connection: + connection.close() return None connection.execute('INSERT INTO blocklist (album) VALUES (?)', (album_id,)) connection.commit() rows = connection.execute( "SELECT id FROM blocklist WHERE album = ?", (album_id,)) - bl = rows.fetchone()[0] + blitem = rows.fetchone()[0] if not with_connection: connection.close() - return bl + return blitem 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""" @@ -654,7 +684,7 @@ class SimaDB: connection = with_connection else: connection = self.get_database_connection() - artist_id = self.get_artist(artist, with_connection=connection, add=True) + artist_id = self.get_artist(artist, with_connection=connection, add=add) rows = connection.execute( "SELECT id FROM blocklist WHERE artist = ?", (artist_id,)) if not rows.fetchone(): @@ -665,10 +695,29 @@ class SimaDB: connection.commit() rows = connection.execute( "SELECT id FROM blocklist WHERE artist = ?", (artist_id,)) - bl = rows.fetchone()[0] + blitem = rows.fetchone()[0] if not with_connection: connection.close() - return bl + return blitem + + def view_bl(self): + connection = self.get_database_connection() + connection.row_factory = sqlite3.Row + rows = connection.execute("""SELECT artists.name AS artist, + artists.mbid AS musicbrainz_artist, + albums.name AS album, + albums.mbid AS musicbrainz_album, + tracks.title AS title, + tracks.mbid AS musicbrainz_title, + tracks.file AS file, + blocklist.id + FROM blocklist + LEFT OUTER JOIN artists ON blocklist.artist = artists.id + LEFT OUTER JOIN albums ON blocklist.album = albums.id + LEFT OUTER JOIN tracks ON blocklist.track = tracks.id""") + res = [dict(row) for row in rows.fetchall()] + connection.close() + return res def delete_bl(self, track=None, album=None, artist=None): if not (track or album or artist):