X-Git-Url: https://git.kaliko.me/?a=blobdiff_plain;f=sima%2Flib%2Fsimadb.py;h=75119aafbd94c0d473531db32f936b26be6cff05;hb=28caa80fbddaca9c7bb83a9d72dfba8130f16e44;hp=ca2277d601d4c24c61e407fa02ee996f86beab92;hpb=e8b1bae0a5ff4fcbe9a2d206f6c22b33f3ab7740;p=mpd-sima.git diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index ca2277d..75119aa 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -19,13 +19,17 @@ """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 @@ -45,10 +49,16 @@ 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): + connection = self.get_database_connection() + info = connection.execute("""SELECT * FROM db_info + WHERE name = "DB Version" LIMIT 1;""").fetchone() + connection.close() + return info + def create_db(self): """ Set up a database """ @@ -190,7 +200,7 @@ class SimaDB: connection.close() def _remove_blocklist_id(self, blid, with_connection=None): - """Remove id""" + """Remove a blocklist id""" if with_connection: connection = with_connection else: @@ -361,6 +371,7 @@ 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: @@ -376,7 +387,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: @@ -428,10 +440,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 = ? ", @@ -446,8 +460,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) @@ -458,30 +472,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() 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 @@ -491,10 +515,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(""" @@ -526,7 +552,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 @@ -535,7 +566,7 @@ class SimaDB: LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id LEFT OUTER JOIN artists ON tracks.artist = artists.id LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre - WHERE history.last_play > ? + WHERE history.last_play > ? AND genres.name NOT NULL ORDER BY history.last_play DESC """, (date.isoformat(' '),)) genres = list() @@ -548,10 +579,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 = """ @@ -590,6 +622,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""" @@ -597,11 +630,13 @@ 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,)) @@ -615,6 +650,7 @@ class SimaDB: 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""" @@ -622,11 +658,13 @@ 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,)) @@ -640,6 +678,7 @@ class SimaDB: 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""" @@ -647,7 +686,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(): @@ -663,6 +702,25 @@ class SimaDB: connection.close() return bl + 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): return