X-Git-Url: https://git.kaliko.me/?a=blobdiff_plain;f=sima%2Flib%2Fsimadb.py;h=a3e55703641b480d1c237ac39b5137f70d2e46dd;hb=c806cb3dbeee61c9c4d023f75a862a33d32ef026;hp=4db498dad354b870357674c15ff2f6c2888da41c;hpb=5fe20b6caffe162afe5be18e77fe40004d00c95e;p=mpd-sima.git diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index 4db498d..a3e5570 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -1,6 +1,6 @@ # -*- coding: utf-8 -*- - -# Copyright (c) 2009-2013 Jack Kaliko +# +# Copyright (c) 2009-2013, 2019-2020 kaliko # Copyright (c) 2009, Eric Casteleijn # Copyright (c) 2008 Rick van Hattem # @@ -20,21 +20,22 @@ # along with sima. If not, see . # # +"""SQlite database library +""" # DOC: # MuscicBrainz ID: # Artists: # -__DB_VERSION__ = 2 -__HIST_DURATION__ = int(7 * 24) # in hours +__DB_VERSION__ = 3 +__HIST_DURATION__ = int(30 * 24) # in hours import sqlite3 from datetime import (datetime, timedelta) from os.path import dirname, isdir from os import (access, W_OK, F_OK) -from shutil import copyfile class SimaDBError(Exception): @@ -54,11 +55,6 @@ class SimaDBNoFile(SimaDBError): pass -class SimaDBUpgradeError(SimaDBError): - """Error on upgrade""" - pass - - class SimaDB(object): "SQLite management" @@ -67,11 +63,12 @@ class SimaDB(object): self.db_path_mod_control() def db_path_mod_control(self): + """Controls DB path access & write permissions""" db_path = self._db_path # Controls directory access if not isdir(dirname(db_path)): raise SimaDBAccessError('Not a regular directory: "%s"' % - dirname(db_path)) + dirname(db_path)) if not access(dirname(db_path), W_OK): raise SimaDBAccessError('No write access to "%s"' % dirname(db_path)) # Is a file but no write access @@ -88,29 +85,12 @@ class SimaDB(object): def get_database_connection(self): """get database reference""" connection = sqlite3.connect( - self._db_path, timeout=5.0, isolation_level="immediate") + self._db_path, timeout=5.0, isolation_level=None) #connection.text_factory = str return connection - def upgrade(self): - """upgrade DB from previous versions""" - connection = self.get_database_connection() - try: - connection.execute('SELECT version FROM db_info') - except Exception as err: - if err.__str__() == "no such table: db_info": - # db version < 2 (MPD_sima 0.6) - copyfile(self._db_path, self._db_path + '.0.6') - connection.execute('DROP TABLE tracks') - connection.commit() - self.create_db() - else: - raise SimaDBUpgradeError('Could not upgrade database: "%s"' % - err) - self.close_database_connection(connection) - def get_artist(self, artist_name, mbid=None, - with_connection=None, add_not=False): + with_connection=None, add_not=False): """get artist information from the database. if not in database insert new entry.""" if with_connection: @@ -147,7 +127,7 @@ class SimaDB(object): if not in database insert new entry.""" art = track.artist nam = track.title - fil = track.get_filename() + fil = track.file if with_connection: connection = with_connection else: @@ -180,12 +160,12 @@ class SimaDB(object): self.close_database_connection(connection) def get_album(self, track, mbid=None, - with_connection=None, add_not=False): + with_connection=None, add_not=False): """ get album information from the database. if not in database insert new entry. - Attention: use Track() object!! - Use AlbumArtist tag is provided, fallback to Album tag + Attention: use Track|Album object!! + Use AlbumArtist tag if provided, fallback to Album tag """ if with_connection: connection = with_connection @@ -234,19 +214,19 @@ class SimaDB(object): yield artist def get_bl_artist(self, artist_name, - with_connection=None, add_not=None): + with_connection=None, add_not=None): """get blacklisted artist information from the database.""" if with_connection: connection = with_connection else: connection = self.get_database_connection() - art = self.get_artist(artist_name, - with_connection=connection, add_not=add_not) + art = self.get_artist(artist_name, with_connection=connection, + add_not=add_not) if not art: return False art_id = art[0] rows = connection.execute("SELECT * FROM black_list WHERE artist = ?", - (art_id,)) + (art_id,)) for row in rows: if not with_connection: self.close_database_connection(connection) @@ -256,12 +236,12 @@ class SimaDB(object): self.close_database_connection(connection) return False connection.execute("INSERT INTO black_list (artist) VALUES (?)", - (art_id,)) + (art_id,)) connection.execute("UPDATE black_list SET updated = DATETIME('now')" - " WHERE artist = ?", (art_id,)) + " WHERE artist = ?", (art_id,)) connection.commit() rows = connection.execute("SELECT * FROM black_list WHERE artist = ?", - (art_id,)) + (art_id,)) for row in rows: if not with_connection: self.close_database_connection(connection) @@ -270,20 +250,19 @@ class SimaDB(object): self.close_database_connection(connection) return False - def get_bl_album(self, track, - with_connection=None, add_not=None): - """get blacklisted track information from the database.""" + def get_bl_album(self, track, with_connection=None, add_not=None): + """get blacklisted album information from the database.""" if with_connection: connection = with_connection else: connection = self.get_database_connection() - album = self.get_album(track, - with_connection=connection, add_not=add_not) + album = self.get_album(track, with_connection=connection, + add_not=add_not) if not album: return False alb_id = album[0] rows = connection.execute("SELECT * FROM black_list WHERE album = ?", - (alb_id,)) + (alb_id,)) for row in rows: if not with_connection: self.close_database_connection(connection) @@ -293,12 +272,12 @@ class SimaDB(object): self.close_database_connection(connection) return False connection.execute("INSERT INTO black_list (album) VALUES (?)", - (alb_id,)) + (alb_id,)) connection.execute("UPDATE black_list SET updated = DATETIME('now')" - " WHERE album = ?", (alb_id,)) + " WHERE album = ?", (alb_id,)) connection.commit() rows = connection.execute("SELECT * FROM black_list WHERE album = ?", - (alb_id,)) + (alb_id,)) for row in rows: if not with_connection: self.close_database_connection(connection) @@ -313,13 +292,13 @@ class SimaDB(object): connection = with_connection else: connection = self.get_database_connection() - track = self.get_track(track, - with_connection=connection, add_not=add_not) + track = self.get_track(track, with_connection=connection, + add_not=add_not) if not track: return False track_id = track[0] rows = connection.execute("SELECT * FROM black_list WHERE track = ?", - (track_id,)) + (track_id,)) for row in rows: if not with_connection: self.close_database_connection(connection) @@ -329,12 +308,12 @@ class SimaDB(object): self.close_database_connection(connection) return False connection.execute("INSERT INTO black_list (track) VALUES (?)", - (track_id,)) + (track_id,)) connection.execute("UPDATE black_list SET updated = DATETIME('now')" - " WHERE track = ?", (track_id,)) + " WHERE track = ?", (track_id,)) connection.commit() rows = connection.execute("SELECT * FROM black_list WHERE track = ?", - (track_id,)) + (track_id,)) for row in rows: if not with_connection: self.close_database_connection(connection) @@ -343,11 +322,31 @@ class SimaDB(object): self.close_database_connection(connection) return False + def get_artists_history(self, artists, duration=__HIST_DURATION__): + """ + :param list artists: list of object that can evaluate equality with + artist name, iterable of str or Artist object + """ + date = datetime.utcnow() - timedelta(hours=duration) + connection = self.get_database_connection() + rows = connection.execute( + "SELECT arts.name, albs.name, trs.name, trs.file" + " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs" + " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id" + " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),)) + for row in rows: + if artists and row[0] not in artists: + continue + for art in artists: + if row[0] == art: + yield art + self.close_database_connection(connection) + def get_history(self, artist=None, artists=None, duration=__HIST_DURATION__): """Retrieve complete play history, most recent tracks first artist : filter history for specific artist artists : filter history for specific artists list - """ + """ # pylint: disable=C0301 date = datetime.utcnow() - timedelta(hours=duration) connection = self.get_database_connection() if artist: @@ -373,14 +372,15 @@ class SimaDB(object): """Retrieve complete black list.""" connection = self.get_database_connection() rows = connection.execute('SELECT black_list.rowid, artists.name' - ' FROM artists INNER JOIN black_list' - ' ON artists.id = black_list.artist') + ' FROM artists INNER JOIN black_list' + ' ON artists.id = black_list.artist') yield ('Row ID', 'Actual black listed element', 'Extra information',) yield ('',) yield ('Row ID', 'Artist',) for row in rows: yield row - rows = connection.execute('SELECT black_list.rowid, albums.name, artists.name' + rows = connection.execute( + 'SELECT black_list.rowid, albums.name, artists.name' ' FROM artists, albums INNER JOIN black_list' ' ON albums.id = black_list.album' ' WHERE artists.id = albums.artist') @@ -388,7 +388,8 @@ class SimaDB(object): yield ('Row ID', 'Album', 'Artist name') for row in rows: yield row - rows = connection.execute('SELECT black_list.rowid, tracks.name, artists.name' + rows = connection.execute( + 'SELECT black_list.rowid, tracks.name, artists.name' ' FROM artists, tracks INNER JOIN black_list' ' ON tracks.id = black_list.track' ' WHERE tracks.artist = artists.id') @@ -399,185 +400,36 @@ class SimaDB(object): self.close_database_connection(connection) def _set_mbid(self, artist_id=None, mbid=None, with_connection=None): - """get artist information from the database. - if not in database insert new entry.""" + """""" if with_connection: connection = with_connection else: connection = self.get_database_connection() connection.execute("UPDATE artists SET mbid = ? WHERE id = ?", - (mbid, artist_id)) + (mbid, artist_id)) connection.commit() if not with_connection: self.close_database_connection(connection) - def _get_similar_artists_from_db(self, artist_id): - connection = self.get_database_connection() - results = [row for row in connection.execute( - "SELECT match, name FROM usr_artist_2_artist INNER JOIN" - " artists ON usr_artist_2_artist.artist2 = artists.id WHERE" - " usr_artist_2_artist.artist1 = ? ORDER BY match DESC;", - (artist_id,))] - self.close_database_connection(connection) - for score, artist in results: - yield {'score': score, 'artist': artist} - - def _get_reverse_similar_artists_from_db(self, artist_id): - connection = self.get_database_connection() - results = [row for row in connection.execute( - "SELECT name FROM usr_artist_2_artist INNER JOIN" - " artists ON usr_artist_2_artist.artist1 = artists.id WHERE" - " usr_artist_2_artist.artist2 = ?;", - (artist_id,))] - self.close_database_connection(connection) - for artist in results: - yield artist[0] - - def get_similar_artists(self, artist_name): - """get similar artists from the database sorted by descending - match score""" - artist_id = self.get_artist(artist_name)[0] - for result in self._get_similar_artists_from_db(artist_id): - yield result - - def _get_artist_match(self, artist1, artist2, with_connection=None): - """get artist match score from database""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - rows = connection.execute( - "SELECT match FROM usr_artist_2_artist WHERE artist1 = ?" - " AND artist2 = ?", - (artist1, artist2)) - result = 0 - for row in rows: - result = row[0] - break - if not with_connection: - self.close_database_connection(connection) - return result - - def _remove_relation_between_2_artist(self, artist1, artist2): - """Remove a similarity relation""" - connection = self.get_database_connection() - connection.execute( - 'DELETE FROM usr_artist_2_artist' - ' WHERE artist1 = ? AND artist2 = ?;', - (artist1, artist2)) - self.clean_database(with_connection=connection) - self._update_artist(artist_id=artist1, with_connection=connection) - connection.commit() - self.close_database_connection(connection) - - def _remove_artist(self, artist_id, deep=False, with_connection=None): - """Remove all artist1 reference""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - if deep: - connection.execute( - 'DELETE FROM usr_artist_2_artist' - ' WHERE artist1 = ? OR artist2 = ?;', - (artist_id, artist_id)) - connection.execute( - 'DELETE FROM artists WHERE id = ?;', - (artist_id,)) - else: - connection.execute( - 'DELETE FROM usr_artist_2_artist WHERE artist1 = ?;', - (artist_id,)) - self.clean_database(with_connection=connection) - self._update_artist(artist_id=artist_id, with_connection=connection) - if not with_connection: - connection.commit() - self.close_database_connection(connection) - def _remove_bl(self, rowid): """Remove bl row id""" connection = self.get_database_connection() connection.execute('DELETE FROM black_list' - ' WHERE black_list.rowid = ?', (rowid,)) + ' WHERE black_list.rowid = ?', (rowid,)) connection.commit() self.close_database_connection(connection) - def _insert_artist_match( - self, artist1, artist2, match, with_connection=None): - """write match score to the database. - Does not update time stamp in table artist/*_updated""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - connection.execute( - "INSERT INTO usr_artist_2_artist (artist1, artist2, match) VALUES" - " (?, ?, ?)", - (artist1, artist2, match)) - if not with_connection: - connection.commit() - self.close_database_connection(connection) - def add_history(self, track): """Add to history""" connection = self.get_database_connection() track_id = self.get_track(track, with_connection=connection)[0] - rows = connection.execute("SELECT * FROM history WHERE track = ? ", (track_id,)) + rows = connection.execute("SELECT * FROM history WHERE track = ? ", + (track_id,)) if not rows.fetchone(): - connection.execute("INSERT INTO history (track) VALUES (?)", (track_id,)) + connection.execute("INSERT INTO history (track) VALUES (?)", + (track_id,)) connection.execute("UPDATE history SET last_play = DATETIME('now') " - " WHERE track = ?", (track_id,)) - connection.commit() - self.close_database_connection(connection) - - def _update_artist(self, artist_id, with_connection=None): - """write artist information to the database""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - connection.execute( - "UPDATE artists SET usr_updated = DATETIME('now') WHERE id = ?", - (artist_id,)) - if not with_connection: - connection.commit() - self.close_database_connection(connection) - - def _update_artist_match( - self, artist1, artist2, match, with_connection=None): - """write match score to the database""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - connection.execute( - "UPDATE usr_artist_2_artist SET match = ? WHERE artist1 = ? AND" - " artist2 = ?", - (match, artist1, artist2)) - if not with_connection: - connection.commit() - self.close_database_connection(connection) - - def _update_similar_artists(self, artist, similar_artists): - """write user similar artist information to the database - """ - # DOC: similar_artists = list([{'score': match, 'artist': name}]) - # - connection = self.get_database_connection() - artist_id = self.get_artist(artist, with_connection=connection)[0] - for artist in similar_artists: - id2 = self.get_artist( - artist['artist'], with_connection=connection)[0] - if self._get_artist_match( - artist_id, id2, with_connection=connection): - self._update_artist_match( - artist_id, id2, artist['score'], - with_connection=connection) - continue - self._insert_artist_match( - artist_id, id2, artist['score'], - with_connection=connection) - self._update_artist(artist_id, with_connection=connection) + " WHERE track = ?", (track_id,)) connection.commit() self.close_database_connection(connection) @@ -590,16 +442,12 @@ class SimaDB(object): artists_ids = set([row[0] for row in connection.execute( "SELECT id FROM artists")]) artist_2_artist_ids = set([row[0] for row in connection.execute( - "SELECT artist1 FROM usr_artist_2_artist")] + - [row[0] for row in connection.execute( - "SELECT artist2 FROM usr_artist_2_artist")] + - [row[0] for row in connection.execute( "SELECT artist FROM black_list")] + [row[0] for row in connection.execute( "SELECT artist FROM albums")] + [row[0] for row in connection.execute( "SELECT artist FROM tracks")]) - orphans = [ (orphan,) for orphan in artists_ids - artist_2_artist_ids ] + orphans = [(orphan,) for orphan in artists_ids - artist_2_artist_ids] connection.executemany('DELETE FROM artists WHERE id = (?);', orphans) if not with_connection: connection.commit() @@ -619,7 +467,7 @@ class SimaDB(object): """SELECT albums.id FROM albums LEFT JOIN tracks ON albums.id = tracks.album WHERE tracks.album IS NULL""")]) - orphans = [ (orphan,) for orphan in orphan_black_ids & orphan_tracks_ids ] + orphans = [(orphan,) for orphan in orphan_black_ids & orphan_tracks_ids] connection.executemany('DELETE FROM albums WHERE id = (?);', orphans) if not with_connection: connection.commit() @@ -639,7 +487,7 @@ class SimaDB(object): """SELECT tracks.id FROM tracks LEFT JOIN black_list ON tracks.id = black_list.track WHERE black_list.track IS NULL""")]) - orphans = [ (orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids ] + orphans = [(orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids] connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans) if not with_connection: connection.commit() @@ -663,19 +511,20 @@ class SimaDB(object): """Remove old entries in history""" connection = self.get_database_connection() connection.execute("DELETE FROM history WHERE last_play" - " < datetime('now', '-%i hours')" % duration) + " < datetime('now', '-%i hours')" % duration) connection.commit() self.close_database_connection(connection) def _set_dbversion(self): + """Add db version""" connection = self.get_database_connection() connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)', - (__DB_VERSION__, 'Sima DB')) + (__DB_VERSION__, 'Sima DB')) connection.commit() self.close_database_connection(connection) def create_db(self): - """ Set up a database for the artist similarity scores + """ Set up a database """ connection = self.get_database_connection() connection.execute( @@ -683,13 +532,7 @@ class SimaDB(object): ' (version INTEGER, name CHAR(36))') connection.execute( 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name' - ' VARCHAR(100), mbid CHAR(36), lfm_updated DATE, usr_updated DATE)') - connection.execute( - 'CREATE TABLE IF NOT EXISTS usr_artist_2_artist (artist1 INTEGER,' - ' artist2 INTEGER, match INTEGER)') - connection.execute( - 'CREATE TABLE IF NOT EXISTS lfm_artist_2_artist (artist1 INTEGER,' - ' artist2 INTEGER, match INTEGER)') + ' VARCHAR(100), mbid CHAR(36))') connection.execute( 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,' ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))') @@ -703,28 +546,10 @@ class SimaDB(object): connection.execute( 'CREATE TABLE IF NOT EXISTS history (last_play DATE,' ' track integer)') - connection.execute( - "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)") - connection.execute( - "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)") - connection.execute( - "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)") - connection.execute( - "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)") connection.commit() self.close_database_connection(connection) self._set_dbversion() -def main(): - db = SimaDB(db_path='/tmp/sima.db') - db.purge_history(int(4)) - db.clean_database() - - -# Script starts here -if __name__ == '__main__': - main() - # VIM MODLINE # vim: ai ts=4 sw=4 sts=4 expandtab