X-Git-Url: http://git.kaliko.me/?a=blobdiff_plain;f=sima%2Flib%2Fsimadb.py;h=a7ce7597d80050d00c006f493478c42b9410197d;hb=e9ed5c171c9251ef6ae7765b1406e2f5b2cb1c0d;hp=284f11562cbed58801eef6816d24d768e801c255;hpb=57621c64288a742232b379b53bfe5fce34959535;p=mpd-sima.git diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index 284f115..a7ce759 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -1,5 +1,5 @@ # -*- coding: utf-8 -*- - +# # Copyright (c) 2009-2013 Jack Kaliko # Copyright (c) 2009, Eric Casteleijn # Copyright (c) 2008 Rick van Hattem @@ -20,6 +20,8 @@ # along with sima. If not, see . # # +"""SQlite database library +""" # DOC: # MuscicBrainz ID: @@ -27,7 +29,7 @@ # __DB_VERSION__ = 2 -__HIST_DURATION__ = int(7 * 24) # in hours +__HIST_DURATION__ = int(30 * 24) # in hours import sqlite3 @@ -67,6 +69,7 @@ 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)): @@ -184,8 +187,8 @@ class SimaDB(object): """ 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 @@ -343,11 +346,29 @@ class SimaDB(object): self.close_database_connection(connection) return False + def get_artists_history(self, artists, duration=__HIST_DURATION__): + """ + """ + 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: @@ -380,7 +401,8 @@ class SimaDB(object): 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 +410,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') @@ -522,9 +545,11 @@ class SimaDB(object): """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() @@ -599,7 +624,7 @@ class SimaDB(object): "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 +644,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 +664,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() @@ -668,6 +693,7 @@ class SimaDB(object): 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')) @@ -704,13 +730,13 @@ class SimaDB(object): '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)") + "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)") + "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)") + "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)") + "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)") connection.commit() self.close_database_connection(connection) self._set_dbversion()