From 9be53ff0c7f6dfeaa62bf0964f7d3a477c10499e Mon Sep 17 00:00:00 2001 From: kaliko Date: Wed, 28 Apr 2021 13:06:36 +0200 Subject: [PATCH 01/16] Rewrote simadb --- sima/lib/db.py | 358 +++++++++++++++++++++++++++++++++++++++++++++++ tests/test_db.py | 125 +++++++++++++++++ 2 files changed, 483 insertions(+) create mode 100644 sima/lib/db.py create mode 100644 tests/test_db.py diff --git a/sima/lib/db.py b/sima/lib/db.py new file mode 100644 index 0000000..6d7836a --- /dev/null +++ b/sima/lib/db.py @@ -0,0 +1,358 @@ +# Copyright (c) 2009-2013, 2019-2021 kaliko +# +# This file is part of sima +# +# sima is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# sima is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with sima. If not, see . +# +# +"""SQlite database library + +https://stackoverflow.com/questions/62818662/sqlite-foreign-key-reverse-cascade-delete +""" + +__DB_VERSION__ = 4 +__HIST_DURATION__ = int(30 * 24) # in hours + +import sqlite3 + +from datetime import (datetime, timedelta) + +from sima.lib.meta import Artist, Album +from sima.lib.track import Track + + +class SimaDB: + "SQLite management" + + def __init__(self, db_path=None): + self._db_path = db_path + + def get_database_connection(self): + """get database reference""" + connection = sqlite3.connect( + self._db_path, isolation_level=None) + return connection + + def close_database_connection(self, connection): + """Close the database connection.""" + connection.close() + + def create_db(self): + """ Set up a database + """ + connection = self.get_database_connection() + connection.execute( + 'CREATE TABLE IF NOT EXISTS db_info' + ' (name CHAR(50), value CHAR(50))') + connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ? + WHERE NOT EXISTS + ( SELECT 1 FROM db_info WHERE name = ? )''', + ('DB Version', __DB_VERSION__, 'DB Version')) + connection.execute( # ARTISTS + 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, ' + 'name VARCHAR(100), mbid CHAR(36))') + connection.execute( # ALBUMS + 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, ' + 'name VARCHAR(100), mbid CHAR(36))') + connection.execute( # ALBUMARTISTS + 'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, ' + 'name VARCHAR(100), mbid CHAR(36))') + connection.execute( # TRACKS + 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, ' + 'title VARCHAR(100), artist INTEGER, ' + 'album INTEGER, albumartist INTEGER, ' + 'file VARCHAR(500), mbid CHAR(36), ' + 'FOREIGN KEY(artist) REFERENCES artists(id), ' + 'FOREIGN KEY(album) REFERENCES albums(id), ' + 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))') + connection.execute( # HISTORY + 'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, ' + 'last_play TIMESTAMP, track integer, ' + 'FOREIGN KEY(track) REFERENCES tracks(id))') + # Create cleanup triggers: + # Tracks table + 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 + 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 + 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 + 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; + ''') + self.close_database_connection(connection) + + def _get_album(self, album, connection): + if album.mbid: + 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,)) + + def get_album(self, album, with_connection=None, add=True): + """get album information from the database. + if not in database insert new entry. + + :param sima.lib.meta.Album album: album objet + :param sqlite3.Connection with_connection: SQLite connection + """ + if with_connection: + connection = with_connection + else: + connection = self.get_database_connection() + rows = self._get_album(album, connection) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not add: + if not with_connection: + self.close_database_connection(connection) + return None + connection.execute( + "INSERT INTO albums (name, mbid) VALUES (?, ?)", + (album.name, album.mbid)) + connection.commit() + rows = self._get_album(album, connection) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + print('damned: %s' % album.mbid) + if not with_connection: + self.close_database_connection(connection) + return None + + def _get_albumartist(self, artist, connection): + if artist.mbid: + 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,)) + + def get_albumartist(self, artist, with_connection=None, add=True): + """get albumartist information from the database. + if not in database insert new entry. + + :param sima.lib.meta.Artist artist: artist + :param sqlite3.Connection with_connection: SQLite connection + """ + if with_connection: + connection = with_connection + else: + connection = self.get_database_connection() + rows = self._get_albumartist(artist, connection) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not add: + if not with_connection: + self.close_database_connection(connection) + return None + connection.execute( + "INSERT INTO albumartists (name, mbid) VALUES (?, ?)", + (artist.name, artist.mbid)) + connection.commit() + rows = self._get_albumartist(artist, connection) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not with_connection: + self.close_database_connection(connection) + + def _get_artist(self, artist, connection): + if artist.mbid: + 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,)) + + def get_artist(self, artist, with_connection=None, add=True): + """get artist information from the database. + if not in database insert new entry. + + :param sima.lib.meta.Artist artist: artist + :param sqlite3.Connection with_connection: SQLite connection + """ + if with_connection: + connection = with_connection + else: + connection = self.get_database_connection() + rows = self._get_artist(artist, connection) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not add: + if not with_connection: + self.close_database_connection(connection) + return None + connection.execute( + "INSERT INTO artists (name, mbid) VALUES (?, ?)", + (artist.name, artist.mbid)) + connection.commit() + rows = self._get_artist(artist, connection) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not with_connection: + self.close_database_connection(connection) + + def get_track(self, track, with_connection=None, add=True): + """Get a track from Tracks table, add if not existing, + Attention: use Track() object!! + if not in database insert new entry.""" + if with_connection: + connection = with_connection + else: + connection = self.get_database_connection() + rows = connection.execute( + "SELECT * FROM tracks WHERE file = ?", (track.file,)) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not add: # Not adding non existing track + return None + # Get an artist record or None + if track.artist: + art = Artist(name=track.artist, mbid=track.musicbrainz_artistid) + art_id = self.get_artist(art, with_connection=connection) + else: + art_id = None + # Get an albumartist record or None + if track.albumartist: + albart = Artist(name=track.albumartist, + mbid=track.musicbrainz_albumartistid) + albart_id = self.get_albumartist(albart, with_connection=connection) + else: + albart_id = None + # Get an album record or None + if track.album: + alb = Album(name=track.album, mbid=track.musicbrainz_albumid) + alb_id = self.get_album(alb, with_connection=connection) + else: + alb_id = None + connection.execute( + """INSERT INTO tracks (artist, albumartist, album, title, mbid, file) + VALUES (?, ?, ?, ?, ?, ?)""", + (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid, + track.file)) + connection.commit() + rows = connection.execute( + "SELECT id FROM tracks WHERE file = ?", (track.file,)) + for row in rows: + if not with_connection: + self.close_database_connection(connection) + return row[0] + if not with_connection: + connection.commit() + self.close_database_connection(connection) + return None + + 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""" + if not date: + date = datetime.now() + connection = self.get_database_connection() + track_id = self.get_track(track, with_connection=connection) + 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("UPDATE history SET last_play = ? " + " WHERE track = ?", (date, track_id,)) + connection.commit() + self.close_database_connection(connection) + + 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__)""" + connection = self.get_database_connection() + connection.execute("DELETE FROM history WHERE last_play" + " < datetime('now', '-%i hours')" % duration) + connection.commit() + self.close_database_connection(connection) + + def get_history(self, duration=__HIST_DURATION__): + 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 + 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 > ? + ORDER BY history.last_play DESC""", (date.isoformat(' '),)) + hist = list() + for row in rows: + hist.append(Track(**row)) + connection.close() + return hist + + +def main(): + db = SimaDB('/dev/shm/test.sqlite') + db.create_db() + +# VIM MODLINE +# vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 diff --git a/tests/test_db.py b/tests/test_db.py new file mode 100644 index 0000000..2419d44 --- /dev/null +++ b/tests/test_db.py @@ -0,0 +1,125 @@ +# coding: utf-8 + +import unittest +import os +import datetime + +from sima.lib.db import SimaDB +from sima.lib.track import Track + + +DEVOLT = { + 'album': 'Grey', + 'albumartist': 'Devolt', + 'albumartistsort': 'Devolt', + 'artist': 'Devolt', + 'date': '2011-12-01', + 'disc': '1/1', + 'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3', + 'last-modified': '2012-04-02T20:48:59Z', + 'musicbrainz_albumartistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', + 'musicbrainz_albumid': 'ea2ef2cf-59e1-443a-817e-9066e3e0be4b', + 'musicbrainz_artistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', + 'musicbrainz_trackid': 'fabf8fc9-2ae5-49c9-8214-a839c958d872', + 'time': '220', + 'duration': '220.000', + 'title': 'Crazy', + 'track': '3/6'} + + +class Main_TestDB(unittest.TestCase): + db_file = 'file::memory:?cache=shared' + #db_file = '/dev/shm/unittest.sqlite' + + @classmethod + def setUpClass(self): + self.db = SimaDB(db_path=self.db_file) + # Maintain a connection to keep the database between test cases + self.conn = self.db.get_database_connection() + + @classmethod + def tearDownClass(self): + self.conn.close() + + +class TestDB(Main_TestDB): + + def test_00_recreation(self): + self.db.create_db() + + def test_01_add_track(self): + trk = Track(**DEVOLT) + trk_id = self.db.get_track(trk) + self.assertEqual(trk_id, self.db.get_track(trk), + 'Same track, same record') + + def test_02_history(self): + curr = datetime.datetime.utcnow() + # set records in the past to ease purging then + last = curr - datetime.timedelta(hours=1) + trk = Track(**DEVOLT) + self.db.add_history(trk, date=last) + self.db.add_history(trk, date=last) + hist = self.db.get_history() + self.assertEqual(len(hist), 1, 'same track results in a single record') + + trk_foo = Track(file="/foo/bar/baz.flac") + self.db.add_history(trk_foo, date=last) + hist = self.db.get_history() + self.assertEqual(len(hist), 2) + + self.db.add_history(trk, date=last) + hist = self.db.get_history() + self.assertEqual(len(hist), 2) + self.db.purge_history(duration=0) + hist = self.db.get_history() + self.assertEqual(len(hist), 0) + + # Controls we got history in the right order + # recent first, oldest last + hist = list() + for i in range(1, 5): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/bar.{i}', name='{i}-baz', album='foolbum') + hist.append(trk) + last = curr - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) + hist_records = self.db.get_history() + self.assertEqual(hist, hist_records) + self.db.purge_history(duration=0) + + def test_04_triggers(self): + self.db.purge_history(duration=0) + curr = datetime.datetime.utcnow() + tracks_ids = list() + # Set 4 records, same album + for i in range(1, 6): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/{i}', name=f'{i}', artist='fooart', + albumartist='fooalbart', album='foolbum',) + tracks_ids.append(self.db.get_track(trk)) # Add track, save its DB id + # set records in the past to ease purging then + last = curr - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) # Add to history + conn = self.db.get_database_connection() + # Add another track not related (not same album) + track = Track(file='/baz/bar.baz', name='baz', artist='fooart', + albumartist='not-same', album='not-same',) + self.db.get_track(track) + # for tid in tracks_ids: + for tid in tracks_ids[:-1]: + # Delete lastest record + conn.execute('DELETE FROM history WHERE history.track = ?', (tid,)) + c = conn.execute('SELECT albums.name FROM albums;') + # There are still albums records (still a history using it) + self.assertIn((trk.album,), c.fetchall()) + # purging last entry in history or album == trk.album + c.execute('DELETE FROM history WHERE history.track = ?', + (tracks_ids[-1],)) + # triggers purge other tables if possible + c.execute('SELECT albums.name FROM albums;') + albums = c.fetchall() + self.assertNotIn(('foolbum',), albums) + conn.close() + + +# VIM MODLINE +# vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 -- 2.39.2 From 9c754908e645834a53ce3906aac76a0aa92e98f4 Mon Sep 17 00:00:00 2001 From: kaliko Date: Thu, 29 Apr 2021 16:03:27 +0200 Subject: [PATCH 02/16] Add drop_all, fetch_artists*, get_bl_* methods --- sima/lib/db.py | 214 +++++++++++++++++++++++++++++------ tests/test_db.py | 125 --------------------- tests/test_simadb.py | 258 +++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 439 insertions(+), 158 deletions(-) delete mode 100644 tests/test_db.py create mode 100644 tests/test_simadb.py diff --git a/sima/lib/db.py b/sima/lib/db.py index 6d7836a..9ce9082 100644 --- a/sima/lib/db.py +++ b/sima/lib/db.py @@ -32,6 +32,12 @@ from sima.lib.meta import Artist, Album from sima.lib.track import Track +class SimaDBError(Exception): + """ + Exceptions. + """ + + class SimaDB: "SQLite management" @@ -80,43 +86,78 @@ class SimaDB: '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: - # Tracks table + # DELETE history → Tracks table 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_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 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_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 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_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(''' - 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_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(''' + 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; + ''') + 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): + """Remove id""" + connection = self.get_database_connection() + connection.execute('DELETE FROM blocklist' + ' WHERE blocklist.id = ?', (blid,)) + connection.commit() self.close_database_connection(connection) def _get_album(self, album, connection): @@ -250,6 +291,8 @@ class SimaDB: """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: @@ -325,7 +368,30 @@ class SimaDB: connection.commit() self.close_database_connection(connection) - def get_history(self, duration=__HIST_DURATION__): + def fetch_artists_history(self, duration=__HIST_DURATION__): + 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 > ? + ORDER BY history.last_play DESC""", (date.isoformat(' '),)) + hist = list() + for row in rows: + if hist and hist[-1] == Album(**row): # remove consecutive dupes + continue + hist.append(Album(**row)) + connection.close() + return hist + + def fetch_history(self, duration=__HIST_DURATION__): + """Fetches tracks history, more recent first + :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 @@ -349,10 +415,92 @@ class SimaDB: 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 * 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 * 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, ele 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 * 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 * FROM blocklist WHERE album = ?", (album_id,)) + return rows.fetchone() + + 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, ele 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 * 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 * FROM blocklist WHERE artist = ?", (artist_id,)) + return rows.fetchone() + def main(): + DEVOLT = { + 'album': 'Grey', + 'albumartist': 'Devolt', + 'artist': 'Devolt', + 'date': '2011-12-01', + 'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3', + 'musicbrainz_albumartistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', + 'musicbrainz_albumid': 'ea2ef2cf-59e1-443a-817e-9066e3e0be4b', + 'musicbrainz_artistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', + 'musicbrainz_trackid': 'fabf8fc9-2ae5-49c9-8214-a839c958d872', + 'duration': '220.000', + 'title': 'Crazy'} db = SimaDB('/dev/shm/test.sqlite') db.create_db() + db.add_history(Track(**DEVOLT)) + DEVOLT['file'] = 'foo' + print(db.get_bl_track(Track(**DEVOLT))) + db.add_history(Track(**DEVOLT)) # VIM MODLINE # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 diff --git a/tests/test_db.py b/tests/test_db.py deleted file mode 100644 index 2419d44..0000000 --- a/tests/test_db.py +++ /dev/null @@ -1,125 +0,0 @@ -# coding: utf-8 - -import unittest -import os -import datetime - -from sima.lib.db import SimaDB -from sima.lib.track import Track - - -DEVOLT = { - 'album': 'Grey', - 'albumartist': 'Devolt', - 'albumartistsort': 'Devolt', - 'artist': 'Devolt', - 'date': '2011-12-01', - 'disc': '1/1', - 'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3', - 'last-modified': '2012-04-02T20:48:59Z', - 'musicbrainz_albumartistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', - 'musicbrainz_albumid': 'ea2ef2cf-59e1-443a-817e-9066e3e0be4b', - 'musicbrainz_artistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', - 'musicbrainz_trackid': 'fabf8fc9-2ae5-49c9-8214-a839c958d872', - 'time': '220', - 'duration': '220.000', - 'title': 'Crazy', - 'track': '3/6'} - - -class Main_TestDB(unittest.TestCase): - db_file = 'file::memory:?cache=shared' - #db_file = '/dev/shm/unittest.sqlite' - - @classmethod - def setUpClass(self): - self.db = SimaDB(db_path=self.db_file) - # Maintain a connection to keep the database between test cases - self.conn = self.db.get_database_connection() - - @classmethod - def tearDownClass(self): - self.conn.close() - - -class TestDB(Main_TestDB): - - def test_00_recreation(self): - self.db.create_db() - - def test_01_add_track(self): - trk = Track(**DEVOLT) - trk_id = self.db.get_track(trk) - self.assertEqual(trk_id, self.db.get_track(trk), - 'Same track, same record') - - def test_02_history(self): - curr = datetime.datetime.utcnow() - # set records in the past to ease purging then - last = curr - datetime.timedelta(hours=1) - trk = Track(**DEVOLT) - self.db.add_history(trk, date=last) - self.db.add_history(trk, date=last) - hist = self.db.get_history() - self.assertEqual(len(hist), 1, 'same track results in a single record') - - trk_foo = Track(file="/foo/bar/baz.flac") - self.db.add_history(trk_foo, date=last) - hist = self.db.get_history() - self.assertEqual(len(hist), 2) - - self.db.add_history(trk, date=last) - hist = self.db.get_history() - self.assertEqual(len(hist), 2) - self.db.purge_history(duration=0) - hist = self.db.get_history() - self.assertEqual(len(hist), 0) - - # Controls we got history in the right order - # recent first, oldest last - hist = list() - for i in range(1, 5): # starts at 1 to ensure records are in the past - trk = Track(file=f'/foo/bar.{i}', name='{i}-baz', album='foolbum') - hist.append(trk) - last = curr - datetime.timedelta(minutes=i) - self.db.add_history(trk, date=last) - hist_records = self.db.get_history() - self.assertEqual(hist, hist_records) - self.db.purge_history(duration=0) - - def test_04_triggers(self): - self.db.purge_history(duration=0) - curr = datetime.datetime.utcnow() - tracks_ids = list() - # Set 4 records, same album - for i in range(1, 6): # starts at 1 to ensure records are in the past - trk = Track(file=f'/foo/{i}', name=f'{i}', artist='fooart', - albumartist='fooalbart', album='foolbum',) - tracks_ids.append(self.db.get_track(trk)) # Add track, save its DB id - # set records in the past to ease purging then - last = curr - datetime.timedelta(minutes=i) - self.db.add_history(trk, date=last) # Add to history - conn = self.db.get_database_connection() - # Add another track not related (not same album) - track = Track(file='/baz/bar.baz', name='baz', artist='fooart', - albumartist='not-same', album='not-same',) - self.db.get_track(track) - # for tid in tracks_ids: - for tid in tracks_ids[:-1]: - # Delete lastest record - conn.execute('DELETE FROM history WHERE history.track = ?', (tid,)) - c = conn.execute('SELECT albums.name FROM albums;') - # There are still albums records (still a history using it) - self.assertIn((trk.album,), c.fetchall()) - # purging last entry in history or album == trk.album - c.execute('DELETE FROM history WHERE history.track = ?', - (tracks_ids[-1],)) - # triggers purge other tables if possible - c.execute('SELECT albums.name FROM albums;') - albums = c.fetchall() - self.assertNotIn(('foolbum',), albums) - conn.close() - - -# VIM MODLINE -# vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 diff --git a/tests/test_simadb.py b/tests/test_simadb.py new file mode 100644 index 0000000..b71fa1b --- /dev/null +++ b/tests/test_simadb.py @@ -0,0 +1,258 @@ +# coding: utf-8 + +import datetime +import unittest +import os + +from sima.lib.db import SimaDB +from sima.lib.track import Track +from sima.lib.meta import Album + + +DEVOLT = { + 'album': 'Grey', + 'albumartist': 'Devolt', + 'albumartistsort': 'Devolt', + 'artist': 'Devolt', + 'date': '2011-12-01', + 'disc': '1/1', + 'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3', + 'last-modified': '2012-04-02T20:48:59Z', + 'musicbrainz_albumartistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', + 'musicbrainz_albumid': 'ea2ef2cf-59e1-443a-817e-9066e3e0be4b', + 'musicbrainz_artistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', + 'musicbrainz_trackid': 'fabf8fc9-2ae5-49c9-8214-a839c958d872', + 'time': '220', + 'duration': '220.000', + 'title': 'Crazy', + 'track': '3/6'} + +DB_FILE = 'file::memory:?cache=shared' +KEEP_FILE = False # File db in file to ease debug +if KEEP_FILE: + DB_FILE = '/dev/shm/unittest.sqlite' +CURRENT = datetime.datetime.utcnow() +IN_THE_PAST = CURRENT - datetime.timedelta(hours=1) + + +class Main(unittest.TestCase): + """Deal with database creation and purge between tests""" + + @classmethod + def setUpClass(self): + self.db = SimaDB(db_path=DB_FILE) + + def setUp(self): + # Maintain a connection to keep the database (when stored in memory) + self.conn = self.db.get_database_connection() + self.db.drop_all() + self.db.create_db() + + def tearDown(self): + if not KEEP_FILE: + self.db.drop_all() + self.conn.close() + + @classmethod + def tearDownClass(self): + if KEEP_FILE: + return + if os.path.isfile(DB_FILE): + os.unlink(DB_FILE) + + +class Test_00DB(Main): + + def test_00_recreation(self): + self.db.create_db() + + def test_01_add_track(self): + trk = Track(**DEVOLT) + trk_id = self.db.get_track(trk) + self.assertEqual(trk_id, self.db.get_track(trk), + 'Same track, same record') + + def test_02_history(self): + # set records in the past to ease purging then + last = CURRENT - datetime.timedelta(hours=1) + trk = Track(**DEVOLT) + self.db.add_history(trk, date=last) + self.db.add_history(trk, date=last) + hist = self.db.fetch_history() + self.assertEqual(len(hist), 1, 'same track results in a single record') + + trk_foo = Track(file="/foo/bar/baz.flac") + self.db.add_history(trk_foo, date=last) + hist = self.db.fetch_history() + self.assertEqual(len(hist), 2) + + self.db.add_history(trk, date=last) + hist = self.db.fetch_history() + self.assertEqual(len(hist), 2) + self.db.purge_history(duration=0) + hist = self.db.fetch_history() + self.assertEqual(len(hist), 0) + + # Controls we got history in the right order + # recent first, oldest last + hist = list() + for i in range(1, 5): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/bar.{i}', name='{i}-baz', album='foolbum') + hist.append(trk) + last = CURRENT - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) + hist_records = self.db.fetch_history() + self.assertEqual(hist, hist_records) + self.db.purge_history(duration=0) + + def test_history_to_tracks(self): + tr = dict(**DEVOLT) + tr.pop('file') + trk01 = Track(file='01', **tr) + self.db.add_history(trk01, CURRENT-datetime.timedelta(minutes=1)) + # + tr.pop('musicbrainz_artistid') + trk02 = Track(file='02', **tr) + self.db.add_history(trk02, CURRENT-datetime.timedelta(minutes=2)) + # + tr.pop('musicbrainz_albumid') + trk03 = Track(file='03', **tr) + self.db.add_history(trk03, CURRENT-datetime.timedelta(minutes=3)) + # + tr.pop('musicbrainz_albumartistid') + trk04 = Track(file='04', **tr) + self.db.add_history(trk04, CURRENT-datetime.timedelta(minutes=4)) + # + tr.pop('musicbrainz_trackid') + trk05 = Track(file='05', **tr) + self.db.add_history(trk05, CURRENT-datetime.timedelta(minutes=5)) + history = self.db.fetch_history() + self.assertEqual(len(history), 5) + # Controls history ordering, recent first + self.assertEqual(history, [trk01, trk02, trk03, trk04, trk05]) + + def test_history_to_artists(self): + tr = dict(**DEVOLT) + tr.pop('file') + tr.pop('musicbrainz_artistid') + # + trk01 = Track(file='01', **tr) + self.db.add_history(trk01, CURRENT-datetime.timedelta(hours=1)) + # + trk02 = Track(file='02', **tr) + self.db.add_history(trk02, CURRENT-datetime.timedelta(hours=1)) + self.db.add_history(trk02, CURRENT-datetime.timedelta(hours=1)) + # + trk03 = Track(file='03', **tr) + self.db.add_history(trk03, CURRENT-datetime.timedelta(hours=1)) + # got multiple tracks, same artist, got artist history len == 1 + art_history = self.db.fetch_artists_history() + self.assertEqual(len(art_history), 1) + self.assertEqual(art_history, [trk01.Artist]) + + # Now add new artist to history + trk04 = Track(file='04', artist='New Art') + trk05 = Track(file='05', artist='New² Art') + self.db.add_history(trk04, CURRENT-datetime.timedelta(minutes=3)) + self.db.add_history(trk03, CURRENT-datetime.timedelta(minutes=2)) + self.db.add_history(trk05, CURRENT-datetime.timedelta(minutes=1)) + art_history = self.db.fetch_artists_history() + # Now we should have 4 artists in history + self.assertEqual(len(art_history), 4) + # Controling order, recent first + self.assertEqual([trk05.artist, trk03.artist, + trk04.artist, trk03.artist], + art_history) + + def test_04_triggers(self): + self.db.purge_history(duration=0) + tracks_ids = list() + # Add a first track + track = Track(file='/baz/bar.baz', name='baz', artist='fooart', + albumartist='not-same', album='not-same',) + self.db.get_track(track) + # Set 6 more records from same artist but not same album + for i in range(1, 6): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/{i}', name=f'{i}', artist='fooart', + albumartist='fooalbart', album='foolbum',) + # Add track, save its DB id + tracks_ids.append(self.db.get_track(trk)) + # set records in the past to ease purging then + last = CURRENT - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) # Add to history + conn = self.db.get_database_connection() + # for tid in tracks_ids: + for tid in tracks_ids[:-1]: + # Delete lastest record + conn.execute('DELETE FROM history WHERE history.track = ?', + (tid,)) + c = conn.execute('SELECT albums.name FROM albums;') + # There are still albums records (still a history using it) + self.assertIn((trk.album,), c.fetchall()) + # purging last entry in history for album == trk.album + conn.execute('DELETE FROM history WHERE history.track = ?', + (tracks_ids[-1],)) + # triggers purge other tables if possible + conn.execute('SELECT albums.name FROM albums;') + albums = c.fetchall() + # No more "foolbum" in the table albums + self.assertNotIn(('foolbum',), albums) + # There is still "fooart" though + c = conn.execute('SELECT artists.name FROM artists;') + artists = c.fetchall() + # No more "foolbum" in the table albums + self.assertIn(('fooart',), artists) + conn.close() + + +class Test_01BlockList(Main): + + def test_blocklist_addition(self): + tracks_ids = list() + # Set 6 records, same album + for i in range(1, 6): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/{i}', name=f'{i}', artist='fooart', + albumartist='fooalbart', album='foolbum',) + # Add track, save its DB id + tracks_ids.append(self.db.get_track(trk)) + # set records in the past to ease purging then + last = CURRENT - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) # Add to history + if i == 1: + self.db.get_bl_track(trk) + if i == 2: + self.db.get_bl_track(trk) + self.db.get_bl_album(Album(name=trk.album)) + if i == 3: + self.db.get_bl_artist(trk.Artist) + + def test_blocklist_triggers(self): + trk01 = Track(file='01', name='01', artist='artist A', album='album A') + trk02 = Track(file='02', name='01', artist='artist A', album='album B') + trk01_id = self.db.get_bl_track(trk01) + trk02_id = self.db.get_bl_track(trk02) + self.db.add_history(trk01, IN_THE_PAST) + self.db._remove_blocklist_id(trk01_id) + # bl trk01 removed: + # albums/artists table not affected since trk01_id still in history + conn = self.db.get_database_connection() + albums = conn.execute('SELECT albums.name FROM albums;').fetchall() + artists = conn.execute('SELECT artists.name FROM artists;').fetchall() + self.assertIn(('album A',), albums) + self.assertIn(('artist A',), artists) + self.db.purge_history(0) + # remove last reference to trk01 + albums = conn.execute('SELECT albums.name FROM albums;').fetchall() + self.assertNotIn(('album A',), albums) + self.assertIn(('artist A',), artists) + # remove trk02 + self.db._remove_blocklist_id(trk02_id) + albums = conn.execute('SELECT albums.name FROM albums;').fetchall() + artists = conn.execute('SELECT artists.name FROM artists;').fetchall() + self.assertNotIn(('album B',), albums) + self.assertNotIn(('artist A'), artists) + conn.close() + + +# VIM MODLINE +# vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 -- 2.39.2 From ff70af5118413adb9e34fa50b4d95be3cddc82f0 Mon Sep 17 00:00:00 2001 From: kaliko Date: Fri, 30 Apr 2021 10:02:30 +0200 Subject: [PATCH 03/16] Fixed indentation --- sima/lib/db.py | 78 +++++++++++++++++++++++++------------------------- 1 file changed, 39 insertions(+), 39 deletions(-) diff --git a/sima/lib/db.py b/sima/lib/db.py index 9ce9082..229a233 100644 --- a/sima/lib/db.py +++ b/sima/lib/db.py @@ -95,53 +95,53 @@ class SimaDB: # Create cleanup triggers: # 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; - ''') + 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; - ''') + 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; - ''') + 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(''' - 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; - ''') + 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(''' - 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; - ''') + 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; + ''') self.close_database_connection(connection) def drop_all(self): -- 2.39.2 From 68e49506aaaec305e8ba5ac1f529df9eb9d68f72 Mon Sep 17 00:00:00 2001 From: kaliko Date: Fri, 30 Apr 2021 11:35:59 +0200 Subject: [PATCH 04/16] =?utf8?q?Add=20Triggers=20for=20blocklist=E2=86=92a?= =?utf8?q?lbums|artists?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- sima/lib/db.py | 67 ++++++++++++++++++++++++++++++++++++-------- tests/test_simadb.py | 17 +++++++++-- 2 files changed, 70 insertions(+), 14 deletions(-) diff --git a/sima/lib/db.py b/sima/lib/db.py index 229a233..a2687b8 100644 --- a/sima/lib/db.py +++ b/sima/lib/db.py @@ -142,6 +142,30 @@ class SimaDB: 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(''' + 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(''' + 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) def drop_all(self): @@ -152,13 +176,18 @@ class SimaDB: connection.execute(f'DROP TABLE IF EXISTS {r[0]}') connection.close() - def _remove_blocklist_id(self, blid): + 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() - self.close_database_connection(connection) + if not with_connection: + self.close_database_connection(connection) def _get_album(self, album, connection): if album.mbid: @@ -426,7 +455,7 @@ class SimaDB: connection = self.get_database_connection() track_id = self.get_track(track, with_connection=connection, add=True) rows = connection.execute( - "SELECT * FROM blocklist WHERE track = ?", (track_id,)) + "SELECT id FROM blocklist WHERE track = ?", (track_id,)) if not rows.fetchone(): if not add: return None @@ -434,13 +463,13 @@ class SimaDB: (track_id,)) connection.commit() rows = connection.execute( - "SELECT * FROM blocklist WHERE track = ?", (track_id,)) + "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, ele create a new one + :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 @@ -448,7 +477,7 @@ class SimaDB: connection = self.get_database_connection() album_id = self.get_album(album, with_connection=connection, add=True) rows = connection.execute( - "SELECT * FROM blocklist WHERE album = ?", (album_id,)) + "SELECT id FROM blocklist WHERE album = ?", (album_id,)) if not rows.fetchone(): if not add: return None @@ -456,13 +485,13 @@ class SimaDB: (album_id,)) connection.commit() rows = connection.execute( - "SELECT * FROM blocklist WHERE album = ?", (album_id,)) - return rows.fetchone() + "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, ele create a new one + :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 @@ -470,7 +499,7 @@ class SimaDB: connection = self.get_database_connection() artist_id = self.get_artist(artist, with_connection=connection, add=True) rows = connection.execute( - "SELECT * FROM blocklist WHERE artist = ?", (artist_id,)) + "SELECT id FROM blocklist WHERE artist = ?", (artist_id,)) if not rows.fetchone(): if not add: return None @@ -478,9 +507,23 @@ class SimaDB: (artist_id,)) connection.commit() rows = connection.execute( - "SELECT * FROM blocklist WHERE artist = ?", (artist_id,)) - return rows.fetchone() + "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(): DEVOLT = { diff --git a/tests/test_simadb.py b/tests/test_simadb.py index b71fa1b..8e0d49f 100644 --- a/tests/test_simadb.py +++ b/tests/test_simadb.py @@ -28,7 +28,7 @@ DEVOLT = { 'track': '3/6'} DB_FILE = 'file::memory:?cache=shared' -KEEP_FILE = False # File db in file to ease debug +KEEP_FILE = True # File db in file to ease debug if KEEP_FILE: DB_FILE = '/dev/shm/unittest.sqlite' CURRENT = datetime.datetime.utcnow() @@ -226,7 +226,20 @@ class Test_01BlockList(Main): if i == 3: self.db.get_bl_artist(trk.Artist) - def test_blocklist_triggers(self): + def test_blocklist_triggers_00(self): + trk01 = Track(file='01', name='01', artist='artist A', album='album A') + blart01_id = self.db.get_bl_artist(trk01.Artist) + blalb01_id = self.db.get_bl_album(Album(name=trk01.album, mbid=trk01.musicbrainz_albumid)) + conn = self.db.get_database_connection() + self.db._remove_blocklist_id(blart01_id, with_connection=conn) + self.db._remove_blocklist_id(blalb01_id, with_connection=conn) + albums = conn.execute('SELECT albums.name FROM albums;').fetchall() + artists = conn.execute('SELECT artists.name FROM artists;').fetchall() + conn.close() + self.assertNotIn((trk01.album,), albums) + self.assertNotIn((trk01.artist,), artists) + + def test_blocklist_triggers_01(self): trk01 = Track(file='01', name='01', artist='artist A', album='album A') trk02 = Track(file='02', name='01', artist='artist A', album='album B') trk01_id = self.db.get_bl_track(trk01) -- 2.39.2 From 798ce9e58b7725118a05245ddb723f6e909ded95 Mon Sep 17 00:00:00 2001 From: kaliko Date: Sat, 1 May 2021 15:28:37 +0200 Subject: [PATCH 05/16] Add fetch history methods --- sima/lib/db.py | 121 ++++++++++++++++++++++++++++++------------- tests/test_simadb.py | 44 ++++++++++++++-- 2 files changed, 125 insertions(+), 40 deletions(-) diff --git a/sima/lib/db.py b/sima/lib/db.py index a2687b8..91cf037 100644 --- a/sima/lib/db.py +++ b/sima/lib/db.py @@ -26,6 +26,7 @@ __HIST_DURATION__ = int(30 * 24) # in hours import sqlite3 +from collections import deque from datetime import (datetime, timedelta) from sima.lib.meta import Artist, Album @@ -394,50 +395,115 @@ class SimaDB: 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) - def fetch_artists_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(""" - SELECT artists.name AS name, - artists.mbid as mbid + 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 + LEFT OUTER JOIN albums ON tracks.album = albums.id LEFT OUTER JOIN artists ON tracks.artist = artists.id - WHERE history.last_play > ? + 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: - if hist and hist[-1] == Album(**row): # remove consecutive dupes + 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(**row)) + hist.append(album) connection.close() return hist - def fetch_history(self, duration=__HIST_DURATION__): - """Fetches tracks history, more recent first - :param int duration: How long ago to fetch history from + 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 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 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 - LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id - LEFT OUTER JOIN albums ON tracks.album = albums.id - WHERE history.last_play > ? + WHERE history.last_play > ? AND artists.name NOT NULL 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)) @@ -525,25 +591,6 @@ class SimaDB: return self._remove_blocklist_id(blid, with_connection=connection) -def main(): - DEVOLT = { - 'album': 'Grey', - 'albumartist': 'Devolt', - 'artist': 'Devolt', - 'date': '2011-12-01', - 'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3', - 'musicbrainz_albumartistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', - 'musicbrainz_albumid': 'ea2ef2cf-59e1-443a-817e-9066e3e0be4b', - 'musicbrainz_artistid': 'd8e7e3e2-49ab-4f7c-b148-fc946d521f99', - 'musicbrainz_trackid': 'fabf8fc9-2ae5-49c9-8214-a839c958d872', - 'duration': '220.000', - 'title': 'Crazy'} - db = SimaDB('/dev/shm/test.sqlite') - db.create_db() - db.add_history(Track(**DEVOLT)) - DEVOLT['file'] = 'foo' - print(db.get_bl_track(Track(**DEVOLT))) - db.add_history(Track(**DEVOLT)) # VIM MODLINE # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 diff --git a/tests/test_simadb.py b/tests/test_simadb.py index 8e0d49f..52e3e44 100644 --- a/tests/test_simadb.py +++ b/tests/test_simadb.py @@ -6,7 +6,7 @@ import os from sima.lib.db import SimaDB from sima.lib.track import Track -from sima.lib.meta import Album +from sima.lib.meta import Album, Artist, MetaContainer DEVOLT = { @@ -145,9 +145,11 @@ class Test_00DB(Main): # trk03 = Track(file='03', **tr) self.db.add_history(trk03, CURRENT-datetime.timedelta(hours=1)) - # got multiple tracks, same artist, got artist history len == 1 + # got multiple tracks, same artist/album, got artist/album history len = 1 art_history = self.db.fetch_artists_history() + alb_history = self.db.fetch_albums_history() self.assertEqual(len(art_history), 1) + self.assertEqual(len(alb_history), 1) self.assertEqual(art_history, [trk01.Artist]) # Now add new artist to history @@ -164,7 +166,43 @@ class Test_00DB(Main): trk04.artist, trk03.artist], art_history) - def test_04_triggers(self): + def test_04_filtering_history(self): + # Controls artist history filtering + for i in range(0, 5): + trk = Track(file=f'/foo/bar.{i}', name=f'{i}-baz', + artist=f'{i}-art', album=f'{i}-lbum') + last = CURRENT - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) + if i == 5: # bounce latest record + self.db.add_history(trk, date=last) + art_history = self.db.fetch_artists_history() + # Already checked but to be sure, we should have 5 artists in history + self.assertEqual(len(art_history), 5) + for needle in ['4-art', Artist(name='4-art')]: + art_history = self.db.fetch_artists_history(needle=needle) + self.assertEqual(art_history, [needle]) + needle = Artist(name='not-art') + art_history = self.db.fetch_artists_history(needle=needle) + self.assertEqual(art_history, []) + # Controls artists history filtering + # for a list of Artist objects + needle_list = [Artist(name='3-art'), Artist(name='4-art')] + art_history = self.db.fetch_artists_history(needle=needle_list) + self.assertEqual(art_history, needle_list) + # for a MetaContainer + needle_meta = MetaContainer(needle_list) + art_history = self.db.fetch_artists_history(needle=needle_meta) + self.assertEqual(art_history, needle_list) + # for a list of string (Meta object handles Artist/str comparison) + art_history = self.db.fetch_artists_history(['3-art', '4-art']) + self.assertEqual(art_history, needle_list) + + # Controls album history filtering + needle = Artist(name='4-art') + alb_history = self.db.fetch_albums_history(needle=needle) + self.assertEqual(alb_history, [Album(name='4-lbum')]) + + def test_05_triggers(self): self.db.purge_history(duration=0) tracks_ids = list() # Add a first track -- 2.39.2 From c23e4560ba184403e94d41cbf0816ed9847406fc Mon Sep 17 00:00:00 2001 From: kaliko Date: Sat, 1 May 2021 19:13:53 +0200 Subject: [PATCH 06/16] Update simadb API --- sima/lib/plugin.py | 28 +++++++--------------------- sima/mpdclient.py | 4 ++-- sima/plugins/core/history.py | 16 +++++++++++----- 3 files changed, 20 insertions(+), 28 deletions(-) diff --git a/sima/lib/plugin.py b/sima/lib/plugin.py index 49cd7f1..1afafe5 100644 --- a/sima/lib/plugin.py +++ b/sima/lib/plugin.py @@ -126,29 +126,15 @@ class AdvancedPlugin(Plugin): """ # Query History - def get_history(self, artist=False): - """Constructs Track list of already played artists. - - :param Artist artist: Artist object to look history for - """ + def get_history(self): + """Returns a Track list of already played artists.""" duration = self.main_conf.getint('sima', 'history_duration') - name = None - if artist: - name = artist.name - from_db = self.sdb.get_history(duration=duration, artist=name) - hist = [Track(artist=tr[0], album=tr[1], title=tr[2], - file=tr[3]) for tr in from_db] - return hist + return self.sdb.fetch_history(duration=duration) def get_album_history(self, artist): """Retrieve album history""" - hist = [] - tracks_from_db = self.get_history(artist=artist) - for trk in tracks_from_db: - if trk.album and trk.album in hist: - continue - hist.append(Album(name=trk.album, artist=Artist(trk.artist))) - return hist + duration = self.main_conf.getint('sima', 'history_duration') + return self.sdb.fetch_albums_history(needle=artist, duration=duration) def get_reorg_artists_list(self, alist): """ @@ -162,7 +148,7 @@ class AdvancedPlugin(Plugin): not_queued_artist = alist - queued_artist duration = self.main_conf.getint('sima', 'history_duration') hist = [] - for art in self.sdb.get_artists_history(alist, duration=duration): + for art in self.sdb.fetch_artists_history(alist, duration=duration): if art not in hist: if art not in queued_artist: hist.insert(0, art) @@ -230,7 +216,7 @@ class AdvancedPlugin(Plugin): deny_list = self.player.playlist else: deny_list = self.player.queue - not_in_hist = list(set(tracks) - set(self.get_history(artist=artist))) + not_in_hist = list(set(tracks) - set(self.sdb.fetch_history(artist=artist))) if not not_in_hist: self.log.debug('All tracks already played for "%s"', artist) if unplayed: diff --git a/sima/mpdclient.py b/sima/mpdclient.py index 7eeed94..96767b0 100644 --- a/sima/mpdclient.py +++ b/sima/mpdclient.py @@ -92,10 +92,10 @@ def blacklist(artist=False, album=False, track=False): #cls.log.debug('using {0} as bl filter'.format(bl_getter.__name__)) results = list() for elem in func(*args, **kwargs): - if bl_getter(elem, add_not=True): + if bl_getter(elem, add=False): #cls.log.debug('Blacklisted "{0}"'.format(elem)) continue - if track and cls.database.get_bl_album(elem, add_not=True): + if track and cls.database.get_bl_album(elem, add=False): # filter album as well in track mode # (artist have already been) cls.log.debug('Blacklisted alb. "%s"', elem) diff --git a/sima/plugins/core/history.py b/sima/plugins/core/history.py index 6c3ad90..3380870 100644 --- a/sima/plugins/core/history.py +++ b/sima/plugins/core/history.py @@ -39,15 +39,21 @@ class History(Plugin): def shutdown(self): self.log.info('Cleaning database') self.sdb.purge_history() - self.sdb.clean_database() + + def _h_tip(self): + hist = self.sdb.fetch_history() + if hist: + return hist[0] + return None def callback_player(self): current = self.player.current - last_hist = next(self.sdb.get_history(), None) - if last_hist and last_hist[3] == current.file: - return if not current: - self.log.debug('Cannot add "%s" to history (empty or missing file)', current) + if self.player.state == 'play': + self.log.debug('Cannot add "%s" to history (empty or missing file)', current) + return + last_hist = self._h_tip() + if last_hist and last_hist == current: return self.log.debug('add history: "%s"', current) self.sdb.add_history(current) -- 2.39.2 From 5cbf72d42c713c5a719fa407e6969f0a84fdfe21 Mon Sep 17 00:00:00 2001 From: kaliko Date: Mon, 3 May 2021 09:41:14 +0200 Subject: [PATCH 07/16] Add genres tables to the database, update Genre plugin (related to #48) --- sima/lib/db.py | 158 +++++++++++++++++++++++++-------- sima/plugins/internal/genre.py | 17 ++-- tests/test_simadb.py | 19 +++- 3 files changed, 149 insertions(+), 45 deletions(-) diff --git a/sima/lib/db.py b/sima/lib/db.py index 91cf037..ca2277d 100644 --- a/sima/lib/db.py +++ b/sima/lib/db.py @@ -17,8 +17,6 @@ # # """SQlite database library - -https://stackoverflow.com/questions/62818662/sqlite-foreign-key-reverse-cascade-delete """ __DB_VERSION__ = 4 @@ -51,10 +49,6 @@ class SimaDB: self._db_path, isolation_level=None) return connection - def close_database_connection(self, connection): - """Close the database connection.""" - connection.close() - def create_db(self): """ Set up a database """ @@ -85,7 +79,7 @@ class SimaDB: 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))') connection.execute( # HISTORY 'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, ' - 'last_play TIMESTAMP, track integer, ' + 'last_play TIMESTAMP, track INTEGER, ' 'FOREIGN KEY(track) REFERENCES tracks(id))') connection.execute( # BLOCKLIST 'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, ' @@ -93,8 +87,16 @@ class SimaDB: 'FOREIGN KEY(artist) REFERENCES artists(id), ' 'FOREIGN KEY(album) REFERENCES albums(id), ' 'FOREIGN KEY(track) REFERENCES tracks(id))') + connection.execute( # Genres (Many-to-many) + 'CREATE TABLE IF NOT EXISTS genres ' + '(id INTEGER PRIMARY KEY, name VARCHAR(100))') + connection.execute( # Junction Genres Tracks + """CREATE TABLE IF NOT EXISTS tracks_genres + ( track INTEGER, genre INTEGER, + FOREIGN KEY(track) REFERENCES tracks(id) + FOREIGN KEY(genre) REFERENCES genres(id))""") # Create cleanup triggers: - # DELETE history → Tracks table + # DELETE history → Tracks / Tracks_genres tables connection.execute(''' CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks AFTER DELETE ON history @@ -102,6 +104,16 @@ class SimaDB: (SELECT count(*) FROM blocklist WHERE track=old.track) = 0) BEGIN DELETE FROM tracks WHERE id = old.track; + DELETE FROM tracks_genres WHERE track = old.track; + END; + ''') + # DELETE Tracks_Genres → Genres table + connection.execute(''' + CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres + AFTER DELETE ON tracks_genres + WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0) + BEGIN + DELETE FROM genres WHERE id = old.genre; END; ''') # DELETE Tracks → Artists table @@ -167,7 +179,7 @@ class SimaDB: DELETE FROM albums WHERE id = old.album; END; ''') - self.close_database_connection(connection) + connection.close() def drop_all(self): connection = self.get_database_connection() @@ -188,7 +200,7 @@ class SimaDB: ' WHERE blocklist.id = ?', (blid,)) connection.commit() if not with_connection: - self.close_database_connection(connection) + connection.close() def _get_album(self, album, connection): if album.mbid: @@ -214,11 +226,11 @@ class SimaDB: rows = self._get_album(album, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not add: if not with_connection: - self.close_database_connection(connection) + connection.close() return None connection.execute( "INSERT INTO albums (name, mbid) VALUES (?, ?)", @@ -227,11 +239,10 @@ class SimaDB: rows = self._get_album(album, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] - print('damned: %s' % album.mbid) if not with_connection: - self.close_database_connection(connection) + connection.close() return None def _get_albumartist(self, artist, connection): @@ -258,11 +269,11 @@ class SimaDB: rows = self._get_albumartist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not add: if not with_connection: - self.close_database_connection(connection) + connection.close() return None connection.execute( "INSERT INTO albumartists (name, mbid) VALUES (?, ?)", @@ -271,10 +282,10 @@ class SimaDB: rows = self._get_albumartist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not with_connection: - self.close_database_connection(connection) + connection.close() def _get_artist(self, artist, connection): if artist.mbid: @@ -299,11 +310,11 @@ class SimaDB: rows = self._get_artist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not add: if not with_connection: - self.close_database_connection(connection) + connection.close() return None connection.execute( "INSERT INTO artists (name, mbid) VALUES (?, ?)", @@ -312,15 +323,46 @@ class SimaDB: rows = self._get_artist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not with_connection: - self.close_database_connection(connection) + connection.close() + + def get_genre(self, genre, with_connection=None, add=True): + """get genre from the database. + if not in database insert new entry. + + :param str genre: genre as a string + :param sqlite3.Connection with_connection: SQLite connection + """ + if with_connection: + connection = with_connection + else: + connection = self.get_database_connection() + rows = connection.execute( + "SELECT id FROM genres WHERE name = ?", (genre,)) + for row in rows: + if not with_connection: + connection.close() + return row[0] + if not add: + if not with_connection: + connection.close() + return None + connection.execute( + "INSERT INTO genres (name) VALUES (?)", (genre,)) + connection.commit() + rows = connection.execute( + "SELECT id FROM genres WHERE name = ?", (genre,)) + for row in rows: + if not with_connection: + connection.close() + return row[0] def get_track(self, track, with_connection=None, add=True): - """Get a track from Tracks table, add if not existing, - Attention: use Track() object!! - if not in database insert new entry.""" + """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) if with_connection: @@ -331,9 +373,10 @@ class SimaDB: "SELECT * FROM tracks WHERE file = ?", (track.file,)) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not add: # Not adding non existing track + connection.close() return None # Get an artist record or None if track.artist: @@ -360,17 +403,30 @@ class SimaDB: (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid, track.file)) connection.commit() + # Add track id to junction tables + self._add_tracks_genres(track, connection) rows = connection.execute( "SELECT id FROM tracks WHERE file = ?", (track.file,)) for row in rows: if not with_connection: - self.close_database_connection(connection) + connection.close() return row[0] if not with_connection: - connection.commit() - self.close_database_connection(connection) + connection.close() return None + def _add_tracks_genres(self, track, connection): + if not track.genres: + return None + rows = connection.execute( + "SELECT id FROM tracks WHERE file = ?", (track.file,)) + trk_id = rows.fetchone()[0] + for genre in track.genres: + # add genre + gen_id = self.get_genre(genre) + connection.execute("""INSERT INTO tracks_genres (track, genre) + 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""" @@ -386,7 +442,7 @@ class SimaDB: connection.execute("UPDATE history SET last_play = ? " " WHERE track = ?", (date, track_id,)) connection.commit() - self.close_database_connection(connection) + connection.close() def purge_history(self, duration=__HIST_DURATION__): """Remove old entries in history @@ -397,7 +453,7 @@ class SimaDB: " < datetime('now', '-%i hours')" % duration) connection.execute('VACUUM') connection.commit() - self.close_database_connection(connection) + connection.close() def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__): """ @@ -466,8 +522,30 @@ class SimaDB: hist.append(artist) # No need to go further continue hist.append(artist) + connection.close() return hist + def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20): + date = datetime.utcnow() - timedelta(hours=duration) + connection = self.get_database_connection() + rows = connection.execute(""" + SELECT genres.name, artists.name + FROM history + JOIN tracks ON history.track = tracks.id + 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 > ? + ORDER BY history.last_play DESC + """, (date.isoformat(' '),)) + genres = list() + for row in rows: + genres.append(row) + if len({g[0] for g in genres}) >= limit: + break + connection.close() + return genres + 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 @@ -530,7 +608,10 @@ class SimaDB: connection.commit() rows = connection.execute( "SELECT id FROM blocklist WHERE track = ?", (track_id,)) - return rows.fetchone()[0] + bl = rows.fetchone()[0] + if not with_connection: + connection.close() + return bl def get_bl_album(self, album, with_connection=None, add=True): """Add an album to blocklist @@ -552,7 +633,10 @@ class SimaDB: connection.commit() rows = connection.execute( "SELECT id FROM blocklist WHERE album = ?", (album_id,)) - return rows.fetchone()[0] + bl = rows.fetchone()[0] + if not with_connection: + connection.close() + return bl def get_bl_artist(self, artist, with_connection=None, add=True): """Add an artist to blocklist @@ -574,7 +658,10 @@ class SimaDB: connection.commit() rows = connection.execute( "SELECT id FROM blocklist WHERE artist = ?", (artist_id,)) - return rows.fetchone()[0] + bl = rows.fetchone()[0] + if not with_connection: + connection.close() + return bl def delete_bl(self, track=None, album=None, artist=None): if not (track or album or artist): @@ -590,6 +677,7 @@ class SimaDB: if not blid: return self._remove_blocklist_id(blid, with_connection=connection) + connection.close() # VIM MODLINE diff --git a/sima/plugins/internal/genre.py b/sima/plugins/internal/genre.py index 808d96d..43aabf7 100644 --- a/sima/plugins/internal/genre.py +++ b/sima/plugins/internal/genre.py @@ -63,15 +63,14 @@ class Genre(AdvancedPlugin): raise PluginException('MPD >= 0.21 required') def fetch_genres(self): - pldepth = 4 - nbgenres = 2 - current_titles = self.player.playlist[-pldepth:] - genres = [] - for track in current_titles: - if not track.genres: - self.log.debug('No genre found in %s', track) - continue - genres.extend(track.genres) + """Fetches ,at most, nb-depth genre from history, + and returns the nbgenres most present""" + depth = 10 # nb of genre to fetch from history for analysis + nbgenres = 2 # nb of genre to return + genres = [g[0] for g in self.sdb.fetch_genres_history(limit=depth)] + if not genres: + self.log.debug('No genre found in current track history') + return [] genres_analysis = Counter(genres) if genres_analysis.most_common(): self.log.debug('Most common genres: %s', genres_analysis.most_common()) diff --git a/tests/test_simadb.py b/tests/test_simadb.py index 52e3e44..3be8758 100644 --- a/tests/test_simadb.py +++ b/tests/test_simadb.py @@ -14,6 +14,7 @@ DEVOLT = { 'albumartist': 'Devolt', 'albumartistsort': 'Devolt', 'artist': 'Devolt', + 'genre': ['Rock'], 'date': '2011-12-01', 'disc': '1/1', 'file': 'music/Devolt/2011-Grey/03-Devolt - Crazy.mp3', @@ -97,7 +98,8 @@ class Test_00DB(Main): # recent first, oldest last hist = list() for i in range(1, 5): # starts at 1 to ensure records are in the past - trk = Track(file=f'/foo/bar.{i}', name='{i}-baz', album='foolbum') + trk = Track(file=f'/foo/bar.{i}', name=f'{i}-baz', + album='foolbum', genre=f'{i}') hist.append(trk) last = CURRENT - datetime.timedelta(minutes=i) self.db.add_history(trk, date=last) @@ -305,5 +307,20 @@ class Test_01BlockList(Main): conn.close() +class Test_02Genre(Main): + + def test_genre(self): + conn = self.db.get_database_connection() + self.db.get_genre('Post-Rock', with_connection=conn) + genres = list() + for i in range(1, 15): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/bar.{i}', name=f'{i}-baz', + album='foolbum', artist=f'{i}-art', genre=f'{i}') + genres.append(f'{i}') + last = CURRENT - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) + genre_hist = self.db.fetch_genres_history(limit=10) + self.assertEqual([g[0] for g in genre_hist], genres[:10]) + # VIM MODLINE # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 -- 2.39.2 From e8b1bae0a5ff4fcbe9a2d206f6c22b33f3ab7740 Mon Sep 17 00:00:00 2001 From: kaliko Date: Mon, 3 May 2021 13:40:49 +0200 Subject: [PATCH 08/16] Use new sqlite module --- sima/lib/db.py | 684 ------------------------------- sima/lib/simadb.py | 934 ++++++++++++++++++++++++------------------- tests/test_simadb.py | 2 +- 3 files changed, 534 insertions(+), 1086 deletions(-) delete mode 100644 sima/lib/db.py diff --git a/sima/lib/db.py b/sima/lib/db.py deleted file mode 100644 index ca2277d..0000000 --- a/sima/lib/db.py +++ /dev/null @@ -1,684 +0,0 @@ -# Copyright (c) 2009-2013, 2019-2021 kaliko -# -# This file is part of sima -# -# sima is free software: you can redistribute it and/or modify -# it under the terms of the GNU General Public License as published by -# the Free Software Foundation, either version 3 of the License, or -# (at your option) any later version. -# -# sima is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with sima. If not, see . -# -# -"""SQlite database library -""" - -__DB_VERSION__ = 4 -__HIST_DURATION__ = int(30 * 24) # in hours - -import sqlite3 - -from collections import deque -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" - - def __init__(self, db_path=None): - self._db_path = db_path - - def get_database_connection(self): - """get database reference""" - connection = sqlite3.connect( - self._db_path, isolation_level=None) - return connection - - def create_db(self): - """ Set up a database - """ - connection = self.get_database_connection() - connection.execute( - 'CREATE TABLE IF NOT EXISTS db_info' - ' (name CHAR(50), value CHAR(50))') - connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ? - WHERE NOT EXISTS - ( SELECT 1 FROM db_info WHERE name = ? )''', - ('DB Version', __DB_VERSION__, 'DB Version')) - connection.execute( # ARTISTS - 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, ' - 'name VARCHAR(100), mbid CHAR(36))') - connection.execute( # ALBUMS - 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, ' - 'name VARCHAR(100), mbid CHAR(36))') - connection.execute( # ALBUMARTISTS - 'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, ' - 'name VARCHAR(100), mbid CHAR(36))') - connection.execute( # TRACKS - 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, ' - 'title VARCHAR(100), artist INTEGER, ' - 'album INTEGER, albumartist INTEGER, ' - 'file VARCHAR(500), mbid CHAR(36), ' - 'FOREIGN KEY(artist) REFERENCES artists(id), ' - 'FOREIGN KEY(album) REFERENCES albums(id), ' - 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))') - connection.execute( # HISTORY - '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))') - connection.execute( # Genres (Many-to-many) - 'CREATE TABLE IF NOT EXISTS genres ' - '(id INTEGER PRIMARY KEY, name VARCHAR(100))') - connection.execute( # Junction Genres Tracks - """CREATE TABLE IF NOT EXISTS tracks_genres - ( track INTEGER, genre INTEGER, - FOREIGN KEY(track) REFERENCES tracks(id) - FOREIGN KEY(genre) REFERENCES genres(id))""") - # Create cleanup triggers: - # DELETE history → Tracks / Tracks_genres tables - 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; - DELETE FROM tracks_genres WHERE track = old.track; - END; - ''') - # DELETE Tracks_Genres → Genres table - connection.execute(''' - CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres - AFTER DELETE ON tracks_genres - WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0) - BEGIN - DELETE FROM genres WHERE id = old.genre; - 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(''' - 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(''' - 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(''' - 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(''' - 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; - ''') - connection.close() - - 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: - connection.close() - - def _get_album(self, album, connection): - if album.mbid: - 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,)) - - def get_album(self, album, with_connection=None, add=True): - """get album information from the database. - if not in database insert new entry. - - :param sima.lib.meta.Album album: album objet - :param sqlite3.Connection with_connection: SQLite connection - """ - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - rows = self._get_album(album, connection) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not add: - if not with_connection: - connection.close() - return None - connection.execute( - "INSERT INTO albums (name, mbid) VALUES (?, ?)", - (album.name, album.mbid)) - connection.commit() - rows = self._get_album(album, connection) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not with_connection: - connection.close() - return None - - def _get_albumartist(self, artist, connection): - if artist.mbid: - 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,)) - - def get_albumartist(self, artist, with_connection=None, add=True): - """get albumartist information from the database. - if not in database insert new entry. - - :param sima.lib.meta.Artist artist: artist - :param sqlite3.Connection with_connection: SQLite connection - """ - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - rows = self._get_albumartist(artist, connection) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not add: - if not with_connection: - connection.close() - return None - connection.execute( - "INSERT INTO albumartists (name, mbid) VALUES (?, ?)", - (artist.name, artist.mbid)) - connection.commit() - rows = self._get_albumartist(artist, connection) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not with_connection: - connection.close() - - def _get_artist(self, artist, connection): - if artist.mbid: - 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,)) - - def get_artist(self, artist, with_connection=None, add=True): - """get artist information from the database. - if not in database insert new entry. - - :param sima.lib.meta.Artist artist: artist - :param sqlite3.Connection with_connection: SQLite connection - """ - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - rows = self._get_artist(artist, connection) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not add: - if not with_connection: - connection.close() - return None - connection.execute( - "INSERT INTO artists (name, mbid) VALUES (?, ?)", - (artist.name, artist.mbid)) - connection.commit() - rows = self._get_artist(artist, connection) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not with_connection: - connection.close() - - def get_genre(self, genre, with_connection=None, add=True): - """get genre from the database. - if not in database insert new entry. - - :param str genre: genre as a string - :param sqlite3.Connection with_connection: SQLite connection - """ - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - rows = connection.execute( - "SELECT id FROM genres WHERE name = ?", (genre,)) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not add: - if not with_connection: - connection.close() - return None - connection.execute( - "INSERT INTO genres (name) VALUES (?)", (genre,)) - connection.commit() - rows = connection.execute( - "SELECT id FROM genres WHERE name = ?", (genre,)) - for row in rows: - if not with_connection: - connection.close() - return row[0] - - 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) - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - rows = connection.execute( - "SELECT * FROM tracks WHERE file = ?", (track.file,)) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not add: # Not adding non existing track - connection.close() - return None - # Get an artist record or None - if track.artist: - art = Artist(name=track.artist, mbid=track.musicbrainz_artistid) - art_id = self.get_artist(art, with_connection=connection) - else: - art_id = None - # Get an albumartist record or None - if track.albumartist: - albart = Artist(name=track.albumartist, - mbid=track.musicbrainz_albumartistid) - albart_id = self.get_albumartist(albart, with_connection=connection) - else: - albart_id = None - # Get an album record or None - if track.album: - alb = Album(name=track.album, mbid=track.musicbrainz_albumid) - alb_id = self.get_album(alb, with_connection=connection) - else: - alb_id = None - connection.execute( - """INSERT INTO tracks (artist, albumartist, album, title, mbid, file) - VALUES (?, ?, ?, ?, ?, ?)""", - (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid, - track.file)) - connection.commit() - # Add track id to junction tables - self._add_tracks_genres(track, connection) - rows = connection.execute( - "SELECT id FROM tracks WHERE file = ?", (track.file,)) - for row in rows: - if not with_connection: - connection.close() - return row[0] - if not with_connection: - connection.close() - return None - - def _add_tracks_genres(self, track, connection): - if not track.genres: - return None - rows = connection.execute( - "SELECT id FROM tracks WHERE file = ?", (track.file,)) - trk_id = rows.fetchone()[0] - for genre in track.genres: - # add genre - gen_id = self.get_genre(genre) - connection.execute("""INSERT INTO tracks_genres (track, genre) - 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""" - if not date: - date = datetime.now() - connection = self.get_database_connection() - track_id = self.get_track(track, with_connection=connection) - 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("UPDATE history SET last_play = ? " - " WHERE track = ?", (date, track_id,)) - connection.commit() - connection.close() - - 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__)""" - connection = self.get_database_connection() - connection.execute("DELETE FROM history WHERE last_play" - " < datetime('now', '-%i hours')" % duration) - connection.execute('VACUUM') - connection.commit() - connection.close() - - 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(""" - 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 - LEFT OUTER JOIN albums ON tracks.album = albums.id - 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(' '),)) - 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) - connection.close() - return hist - - def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20): - date = datetime.utcnow() - timedelta(hours=duration) - connection = self.get_database_connection() - rows = connection.execute(""" - SELECT genres.name, artists.name - FROM history - JOIN tracks ON history.track = tracks.id - 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 > ? - ORDER BY history.last_play DESC - """, (date.isoformat(' '),)) - genres = list() - for row in rows: - genres.append(row) - if len({g[0] for g in genres}) >= limit: - break - connection.close() - return genres - - 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 - - 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,)) - bl = rows.fetchone()[0] - if not with_connection: - connection.close() - return bl - - 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,)) - bl = rows.fetchone()[0] - if not with_connection: - connection.close() - return bl - - 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,)) - bl = rows.fetchone()[0] - if not with_connection: - connection.close() - return bl - - 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) - connection.close() - - -# VIM MODLINE -# vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index d060da3..ca2277d 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -1,8 +1,4 @@ -# -*- coding: utf-8 -*- -# -# Copyright (c) 2009-2013, 2019-2020 kaliko -# Copyright (c) 2009, Eric Casteleijn -# Copyright (c) 2008 Rick van Hattem +# Copyright (c) 2009-2013, 2019-2021 kaliko # # This file is part of sima # @@ -23,19 +19,16 @@ """SQlite database library """ -# DOC: -# MuscicBrainz ID: -# Artists: -# - -__DB_VERSION__ = 3 +__DB_VERSION__ = 4 __HIST_DURATION__ = int(30 * 24) # in hours import sqlite3 +from collections import deque from datetime import (datetime, timedelta) -from os.path import dirname, isdir -from os import (access, W_OK, F_OK) + +from sima.lib.meta import Artist, Album +from sima.lib.track import Track class SimaDBError(Exception): @@ -44,509 +37,648 @@ class SimaDBError(Exception): """ -class SimaDBAccessError(SimaDBError): - """Error on accessing DB file""" - - -class SimaDBNoFile(SimaDBError): - """No DB file present""" - - class SimaDB: "SQLite management" def __init__(self, db_path=None): self._db_path = db_path - 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)) - 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 - if access(db_path, F_OK) and not access(db_path, W_OK | F_OK): - raise SimaDBAccessError('No write access to "%s"' % db_path) - # No file - if not access(db_path, F_OK): - raise SimaDBNoFile('No DB file in "%s"' % db_path) - - def close_database_connection(self, connection): - """Close the database connection.""" - connection.close() def get_database_connection(self): """get database reference""" connection = sqlite3.connect( - self._db_path, timeout=5.0, isolation_level=None) - #connection.text_factory = str + self._db_path, isolation_level=None) return connection - def get_artist(self, artist_name, mbid=None, - with_connection=None, add_not=False): - """get artist information from the database. - if not in database insert new entry.""" + def create_db(self): + """ Set up a database + """ + connection = self.get_database_connection() + connection.execute( + 'CREATE TABLE IF NOT EXISTS db_info' + ' (name CHAR(50), value CHAR(50))') + connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ? + WHERE NOT EXISTS + ( SELECT 1 FROM db_info WHERE name = ? )''', + ('DB Version', __DB_VERSION__, 'DB Version')) + connection.execute( # ARTISTS + 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, ' + 'name VARCHAR(100), mbid CHAR(36))') + connection.execute( # ALBUMS + 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, ' + 'name VARCHAR(100), mbid CHAR(36))') + connection.execute( # ALBUMARTISTS + 'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, ' + 'name VARCHAR(100), mbid CHAR(36))') + connection.execute( # TRACKS + 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, ' + 'title VARCHAR(100), artist INTEGER, ' + 'album INTEGER, albumartist INTEGER, ' + 'file VARCHAR(500), mbid CHAR(36), ' + 'FOREIGN KEY(artist) REFERENCES artists(id), ' + 'FOREIGN KEY(album) REFERENCES albums(id), ' + 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))') + connection.execute( # HISTORY + '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))') + connection.execute( # Genres (Many-to-many) + 'CREATE TABLE IF NOT EXISTS genres ' + '(id INTEGER PRIMARY KEY, name VARCHAR(100))') + connection.execute( # Junction Genres Tracks + """CREATE TABLE IF NOT EXISTS tracks_genres + ( track INTEGER, genre INTEGER, + FOREIGN KEY(track) REFERENCES tracks(id) + FOREIGN KEY(genre) REFERENCES genres(id))""") + # Create cleanup triggers: + # DELETE history → Tracks / Tracks_genres tables + 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; + DELETE FROM tracks_genres WHERE track = old.track; + END; + ''') + # DELETE Tracks_Genres → Genres table + connection.execute(''' + CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres + AFTER DELETE ON tracks_genres + WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0) + BEGIN + DELETE FROM genres WHERE id = old.genre; + 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(''' + 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(''' + 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(''' + 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(''' + 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; + ''') + connection.close() + + 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() - rows = connection.execute( - "SELECT * FROM artists WHERE name = ?", (artist_name,)) - for row in rows: - if not with_connection: - self.close_database_connection(connection) - return row - if add_not: - if not with_connection: - self.close_database_connection(connection) - return False - connection.execute( - "INSERT INTO artists (name, mbid) VALUES (?, ?)", - (artist_name, mbid)) + connection = self.get_database_connection() + connection.execute('DELETE FROM blocklist' + ' WHERE blocklist.id = ?', (blid,)) connection.commit() - rows = connection.execute( - "SELECT * FROM artists WHERE name = ?", (artist_name,)) - for row in rows: - if not with_connection: - self.close_database_connection(connection) - return row if not with_connection: - self.close_database_connection(connection) + connection.close() + + def _get_album(self, album, connection): + if album.mbid: + 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,)) - def get_track(self, track, with_connection=None, add_not=False): + def get_album(self, album, with_connection=None, add=True): + """get album information from the database. + if not in database insert new entry. + + :param sima.lib.meta.Album album: album objet + :param sqlite3.Connection with_connection: SQLite connection """ - Get a track from Tracks table, add if not existing, - Attention: use Track() object!! - if not in database insert new entry.""" - art = track.artist - nam = track.title - fil = track.file if with_connection: connection = with_connection else: connection = self.get_database_connection() - art_id = self.get_artist(art, with_connection=connection)[0] - alb_id = self.get_album(track, with_connection=connection)[0] - rows = connection.execute( - "SELECT * FROM tracks WHERE name = ? AND" - " artist = ? AND file = ?", (nam, art_id, fil)) + rows = self._get_album(album, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row - if add_not: - return False + connection.close() + return row[0] + if not add: + if not with_connection: + connection.close() + return None connection.execute( - "INSERT INTO tracks (artist, album, name, file) VALUES (?, ?, ?, ?)", - (art_id, alb_id, nam, fil)) + "INSERT INTO albums (name, mbid) VALUES (?, ?)", + (album.name, album.mbid)) connection.commit() - rows = connection.execute( - "SELECT * FROM tracks WHERE name = ? AND" - " artist = ? AND album = ? AND file = ?", - (nam, art_id, alb_id, fil,)) + rows = self._get_album(album, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row + connection.close() + return row[0] if not with_connection: - connection.commit() - self.close_database_connection(connection) + connection.close() + return None + + def _get_albumartist(self, artist, connection): + if artist.mbid: + 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,)) - def get_album(self, track, mbid=None, - with_connection=None, add_not=False): - """ - get album information from the database. + def get_albumartist(self, artist, with_connection=None, add=True): + """get albumartist information from the database. if not in database insert new entry. - Attention: use Track|Album object!! - Use AlbumArtist tag if provided, fallback to Album tag + + :param sima.lib.meta.Artist artist: artist + :param sqlite3.Connection with_connection: SQLite connection """ if with_connection: connection = with_connection else: connection = self.get_database_connection() - if track.albumartist: - artist = track.albumartist - else: - artist = track.artist - art_id = self.get_artist(artist, with_connection=connection)[0] - album = track.album - rows = connection.execute( - "SELECT * FROM albums WHERE name = ? AND artist = ?", - (album, art_id)) + rows = self._get_albumartist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row - if add_not: - return False + connection.close() + return row[0] + if not add: + if not with_connection: + connection.close() + return None connection.execute( - "INSERT INTO albums (name, artist, mbid) VALUES (?, ?, ?)", - (album, art_id, mbid)) + "INSERT INTO albumartists (name, mbid) VALUES (?, ?)", + (artist.name, artist.mbid)) connection.commit() - rows = connection.execute( - "SELECT * FROM albums WHERE name = ? AND artist = ?", - (album, art_id)) + rows = self._get_albumartist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row + connection.close() + return row[0] if not with_connection: - self.close_database_connection(connection) + connection.close() - def get_artists(self, with_connection=None): - """Returns all artists in DB""" - if with_connection: - connection = with_connection + def _get_artist(self, artist, connection): + if artist.mbid: + return connection.execute( + "SELECT id FROM artists WHERE mbid = ?", + (artist.mbid,)) else: - connection = self.get_database_connection() - rows = connection.execute("SELECT name FROM artists ORDER BY name") - results = [row for row in rows] - if not with_connection: - self.close_database_connection(connection) - for artist in results: - yield artist + 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. + if not in database insert new entry. - def get_bl_artist(self, artist_name, - with_connection=None, add_not=None): - """get blacklisted artist information from the database.""" + :param sima.lib.meta.Artist artist: artist + :param sqlite3.Connection with_connection: SQLite connection + """ 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) - if not art: - return False - art_id = art[0] - rows = connection.execute("SELECT * FROM black_list WHERE artist = ?", - (art_id,)) + rows = self._get_artist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row - if add_not: + connection.close() + return row[0] + if not add: if not with_connection: - self.close_database_connection(connection) - return False - connection.execute("INSERT INTO black_list (artist) VALUES (?)", - (art_id,)) - connection.execute("UPDATE black_list SET updated = DATETIME('now')" - " WHERE artist = ?", (art_id,)) + connection.close() + return None + connection.execute( + "INSERT INTO artists (name, mbid) VALUES (?, ?)", + (artist.name, artist.mbid)) connection.commit() - rows = connection.execute("SELECT * FROM black_list WHERE artist = ?", - (art_id,)) + rows = self._get_artist(artist, connection) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row + connection.close() + return row[0] if not with_connection: - self.close_database_connection(connection) - return False + connection.close() - def get_bl_album(self, track, with_connection=None, add_not=None): - """get blacklisted album information from the database.""" + def get_genre(self, genre, with_connection=None, add=True): + """get genre from the database. + if not in database insert new entry. + + :param str genre: genre as a string + :param sqlite3.Connection with_connection: SQLite connection + """ if with_connection: connection = with_connection else: connection = self.get_database_connection() - 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,)) + rows = connection.execute( + "SELECT id FROM genres WHERE name = ?", (genre,)) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row - if add_not: + connection.close() + return row[0] + if not add: if not with_connection: - self.close_database_connection(connection) - return False - connection.execute("INSERT INTO black_list (album) VALUES (?)", - (alb_id,)) - connection.execute("UPDATE black_list SET updated = DATETIME('now')" - " WHERE album = ?", (alb_id,)) + connection.close() + return None + connection.execute( + "INSERT INTO genres (name) VALUES (?)", (genre,)) connection.commit() - rows = connection.execute("SELECT * FROM black_list WHERE album = ?", - (alb_id,)) + rows = connection.execute( + "SELECT id FROM genres WHERE name = ?", (genre,)) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row - if not with_connection: - self.close_database_connection(connection) - return False - - def get_bl_track(self, track, with_connection=None, add_not=None): - """get blacklisted track information from the database.""" + connection.close() + return row[0] + + 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) if with_connection: connection = with_connection else: connection = self.get_database_connection() - 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,)) + rows = connection.execute( + "SELECT * FROM tracks WHERE file = ?", (track.file,)) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row - if add_not: - if not with_connection: - self.close_database_connection(connection) - return False - connection.execute("INSERT INTO black_list (track) VALUES (?)", - (track_id,)) - connection.execute("UPDATE black_list SET updated = DATETIME('now')" - " WHERE track = ?", (track_id,)) + connection.close() + return row[0] + if not add: # Not adding non existing track + connection.close() + return None + # Get an artist record or None + if track.artist: + art = Artist(name=track.artist, mbid=track.musicbrainz_artistid) + art_id = self.get_artist(art, with_connection=connection) + else: + art_id = None + # Get an albumartist record or None + if track.albumartist: + albart = Artist(name=track.albumartist, + mbid=track.musicbrainz_albumartistid) + albart_id = self.get_albumartist(albart, with_connection=connection) + else: + albart_id = None + # Get an album record or None + if track.album: + alb = Album(name=track.album, mbid=track.musicbrainz_albumid) + alb_id = self.get_album(alb, with_connection=connection) + else: + alb_id = None + connection.execute( + """INSERT INTO tracks (artist, albumartist, album, title, mbid, file) + VALUES (?, ?, ?, ?, ?, ?)""", + (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid, + track.file)) connection.commit() - rows = connection.execute("SELECT * FROM black_list WHERE track = ?", - (track_id,)) + # Add track id to junction tables + self._add_tracks_genres(track, connection) + rows = connection.execute( + "SELECT id FROM tracks WHERE file = ?", (track.file,)) for row in rows: if not with_connection: - self.close_database_connection(connection) - return row + connection.close() + return row[0] if not with_connection: - self.close_database_connection(connection) - return False + connection.close() + return None + + def _add_tracks_genres(self, track, connection): + if not track.genres: + return None + rows = connection.execute( + "SELECT id FROM tracks WHERE file = ?", (track.file,)) + trk_id = rows.fetchone()[0] + for genre in track.genres: + # add genre + gen_id = self.get_genre(genre) + connection.execute("""INSERT INTO tracks_genres (track, genre) + 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""" + if not date: + date = datetime.now() + connection = self.get_database_connection() + track_id = self.get_track(track, with_connection=connection) + 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("UPDATE history SET last_play = ? " + " WHERE track = ?", (date, track_id,)) + connection.commit() + connection.close() + + 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__)""" + connection = self.get_database_connection() + connection.execute("DELETE FROM history WHERE last_play" + " < datetime('now', '-%i hours')" % duration) + connection.execute('VACUUM') + connection.commit() + connection.close() - def get_artists_history(self, artists, duration=__HIST_DURATION__): + def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__): """ - :param list artists: list of object that can evaluate equality with - artist name, iterable of str or Artist object + :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() - 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(' '),)) + 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 + 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 + 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: - if artists and row[0] not in artists: + 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 - 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 + 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() - if artist: - rows = connection.execute( - "SELECT arts.name, albs.name, trs.name, trs.file, hist.last_play" - " 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 > ? AND arts.name = ?" - " ORDER BY hist.last_play DESC", (date.isoformat(' '), artist,)) - else: - 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(' '),)) + 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(' '),)) + last = deque(maxlen=1) + hist = list() for row in rows: - if artists and row[0] not in artists: + 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 - yield row - self.close_database_connection(connection) + hist.append(artist) + connection.close() + return hist - def get_black_list(self): - """Retrieve complete black list.""" + def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20): + date = datetime.utcnow() - timedelta(hours=duration) 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') - yield ('Row ID', 'Actual black listed element', 'Extra information',) - yield ('',) - yield ('Row ID', 'Artist',) + rows = connection.execute(""" + SELECT genres.name, artists.name + FROM history + JOIN tracks ON history.track = tracks.id + 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 > ? + ORDER BY history.last_play DESC + """, (date.isoformat(' '),)) + genres = list() for row in rows: - yield row - 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') - yield ('',) - yield ('Row ID', 'Album', 'Artist name') - for row in rows: - yield row - 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') - yield ('',) - yield ('Row ID', 'Title', 'Artist name') + genres.append(row) + if len({g[0] for g in genres}) >= limit: + break + connection.close() + return genres + + 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: - yield row - self.close_database_connection(connection) + hist.append(Track(**row)) + connection.close() + return hist - def _set_mbid(self, artist_id=None, mbid=None, with_connection=None): - """""" + 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() - connection.execute("UPDATE artists SET mbid = ? WHERE id = ?", - (mbid, artist_id)) - connection.commit() - if not with_connection: - 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,)) - 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,)) + 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(): - connection.execute("INSERT INTO history (track) VALUES (?)", + if not add: + return None + connection.execute('INSERT INTO blocklist (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 _clean_artists_table(self, with_connection=None): - """Clean orphan artists""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - artists_ids = {row[0] for row in connection.execute( - "SELECT id FROM artists")} - artist_2_artist_ids = {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] - connection.executemany('DELETE FROM artists WHERE id = (?);', orphans) - if not with_connection: connection.commit() - self.close_database_connection(connection) - - def _clean_albums_table(self, with_connection=None): - """Clean orphan albums""" - if with_connection: - connection = with_connection - else: - connection = self.get_database_connection() - orphan_black_ids = {row[0] for row in connection.execute( - """SELECT albums.id FROM albums - LEFT JOIN black_list ON albums.id = black_list.album - WHERE ( black_list.album IS NULL )""")} - orphan_tracks_ids = {row[0] for row in connection.execute( - """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] - connection.executemany('DELETE FROM albums WHERE id = (?);', orphans) + rows = connection.execute( + "SELECT id FROM blocklist WHERE track = ?", (track_id,)) + bl = rows.fetchone()[0] if not with_connection: - connection.commit() - self.close_database_connection(connection) - - def _clean_tracks_table(self, with_connection=None): - """Clean orphan tracks""" + connection.close() + return bl + + 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() - hist_orphan_ids = {row[0] for row in connection.execute( - """SELECT tracks.id FROM tracks - LEFT JOIN history ON tracks.id = history.track - WHERE history.track IS NULL""")} - black_list_orphan_ids = {row[0] for row in connection.execute( - """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] - connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans) - if not with_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() - self.close_database_connection(connection) - - def clean_database(self, with_connection=None): - """Wrapper around _clean_* methods""" + rows = connection.execute( + "SELECT id FROM blocklist WHERE album = ?", (album_id,)) + bl = rows.fetchone()[0] + if not with_connection: + connection.close() + return bl + + 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() - self._clean_tracks_table(with_connection=connection) - self._clean_albums_table(with_connection=connection) - self._clean_artists_table(with_connection=connection) - connection.execute("VACUUM") - if not with_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() - self.close_database_connection(connection) - - def purge_history(self, duration=__HIST_DURATION__): - """Remove old entries in history""" - connection = self.get_database_connection() - connection.execute("DELETE FROM history WHERE last_play" - " < 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')) - connection.commit() - self.close_database_connection(connection) + rows = connection.execute( + "SELECT id FROM blocklist WHERE artist = ?", (artist_id,)) + bl = rows.fetchone()[0] + if not with_connection: + connection.close() + return bl - def create_db(self): - """ Set up a database - """ + def delete_bl(self, track=None, album=None, artist=None): + if not (track or album or artist): + return connection = self.get_database_connection() - connection.execute( - 'CREATE TABLE IF NOT EXISTS db_info' - ' (version INTEGER, name CHAR(36))') - connection.execute( - 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name' - ' 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))') - connection.execute( - 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY,' - ' name VARCHAR(100), artist INTEGER, album INTEGER,' - ' file VARCHAR(500), mbid CHAR(36))') - connection.execute( - 'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,' - ' album INTEGER, track INTEGER, updated DATE)') - connection.execute( - 'CREATE TABLE IF NOT EXISTS history (last_play DATE,' - ' track integer)') - connection.commit() - self.close_database_connection(connection) - self._set_dbversion() + 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) + connection.close() # VIM MODLINE -# vim: ai ts=4 sw=4 sts=4 expandtab +# vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 diff --git a/tests/test_simadb.py b/tests/test_simadb.py index 3be8758..e69c4bd 100644 --- a/tests/test_simadb.py +++ b/tests/test_simadb.py @@ -4,7 +4,7 @@ import datetime import unittest import os -from sima.lib.db import SimaDB +from sima.lib.simadb import SimaDB from sima.lib.track import Track from sima.lib.meta import Album, Artist, MetaContainer -- 2.39.2 From 568365b3b2ec343ffa3e1b2159c38abbe88011eb Mon Sep 17 00:00:00 2001 From: kaliko Date: Tue, 4 May 2021 15:15:53 +0200 Subject: [PATCH 09/16] Update file access controls --- sima/launch.py | 1 - sima/utils/config.py | 11 +++++------ 2 files changed, 5 insertions(+), 7 deletions(-) diff --git a/sima/launch.py b/sima/launch.py index 0ff7da3..27e9d6b 100644 --- a/sima/launch.py +++ b/sima/launch.py @@ -109,7 +109,6 @@ def start(sopt, restart=False): if cmd == "create-db": if not isfile(db_file): logger.info('Creating database in "%s"', db_file) - open(db_file, 'a').close() # TODO: to remove with new simadb in v0.18 SimaDB(db_path=db_file).create_db() else: logger.info('Database already there, not overwriting %s', db_file) diff --git a/sima/utils/config.py b/sima/utils/config.py index 299c7c3..854d6df 100644 --- a/sima/utils/config.py +++ b/sima/utils/config.py @@ -142,10 +142,10 @@ class ConfMan: # CONFIG MANAGER CLASS ## INIT CALLS self.init_config() self.supersedes_config_with_cmd_line_options() - # Controls files access - self.control_facc() # set dbfile self.config['sima']['db_file'] = join(self.config['sima']['var_dir'], 'sima.db') + # Controls files access + self.control_facc() # Create directories data_dir = self.config['sima']['var_dir'] @@ -158,11 +158,12 @@ class ConfMan: # CONFIG MANAGER CLASS """Controls file access. This is relevant only for file provided through the configuration file since files provided on the command line are already checked with - argparse. + argparse. Also add config['sima']['db_file'] contructed here in init """ ok = True for op, ftochk in [('logfile', self.config.get('log', 'logfile')), - ('pidfile', self.config.get('daemon', 'pidfile')),]: + ('pidfile', self.config.get('daemon', 'pidfile')), + ('db file', self.config.get('sima', 'db_file'))]: if not ftochk: continue if isdir(ftochk): @@ -179,8 +180,6 @@ class ConfMan: # CONFIG MANAGER CLASS self.log.critical('no write access to "%s" (%s)', ftochk, op) ok = False if not ok: - if exists(self.conf_file): - self.log.warning('Try to check the configuration file: %s', self.conf_file) sys.exit(2) def control_mod(self): -- 2.39.2 From 45cf8271f3d364c33dd3c6fa2bcceca494176c0d Mon Sep 17 00:00:00 2001 From: kaliko Date: Tue, 4 May 2021 15:37:44 +0200 Subject: [PATCH 10/16] Update simadb API --- sima/mpdclient.py | 13 +++++++------ sima/plugins/internal/random.py | 12 +++++------- 2 files changed, 12 insertions(+), 13 deletions(-) diff --git a/sima/mpdclient.py b/sima/mpdclient.py index 96767b0..bccdb97 100644 --- a/sima/mpdclient.py +++ b/sima/mpdclient.py @@ -48,8 +48,9 @@ def bl_artist(func): return None names = list() for art in result.names: - if cls.database.get_bl_artist(art, add_not=True): - cls.log.debug('Blacklisted "%s"', art) + artist = Artist(name=art, mbid=result.mbid) + if cls.database.get_bl_artist(artist, add=False): + cls.log.debug('Artist "%s" in blocklist!', artist) continue names.append(art) if not names: @@ -75,7 +76,7 @@ def tracks_wrapper(func): # / decorators -def blacklist(artist=False, album=False, track=False): +def blocklist(album=False, track=False): # pylint: disable=C0111,W0212 field = (album, track) @@ -98,7 +99,7 @@ def blacklist(artist=False, album=False, track=False): if track and cls.database.get_bl_album(elem, add=False): # filter album as well in track mode # (artist have already been) - cls.log.debug('Blacklisted alb. "%s"', elem) + cls.log.debug('Album "%s" in blocklist', elem) continue results.append(elem) return results @@ -437,7 +438,7 @@ class MPD(MPDClient): return artist return None - @blacklist(track=True) + @blocklist(track=True) def search_track(self, artist, title): """Fuzzy search of title by an artist """ @@ -467,7 +468,7 @@ class MPD(MPDClient): mtitle, title, leven) return tracks - @blacklist(album=True) + @blocklist(album=True) def search_albums(self, artist): """Find potential albums for "artist" diff --git a/sima/plugins/internal/random.py b/sima/plugins/internal/random.py index 99e0599..5af0ba1 100644 --- a/sima/plugins/internal/random.py +++ b/sima/plugins/internal/random.py @@ -46,11 +46,9 @@ class Random(Plugin): self.candidates = [] def get_played_artist(self,): - """Constructs list of already played artists. - """ + """Constructs list of already played artists.""" duration = self.main_conf.getint('sima', 'history_duration') - tracks_from_db = self.sdb.get_history(duration=duration) - artists = {tr[0] for tr in tracks_from_db} + artists = self.sdb.fetch_artists_history(duration=duration) return artists def filtered_artist(self, artist): @@ -59,10 +57,10 @@ class Random(Plugin): If sensible random is set: * not in recent history - * not blacklisted + * not in blocklist """ if self.mode == 'sensible': - if self.sdb.get_bl_artist(artist, add_not=True): + if self.sdb.get_bl_artist(Artist(artist), add=False): self.log.debug('Random plugin: Blacklisted "%s"', artist) return True if artist in self.get_played_artist(): @@ -79,7 +77,7 @@ class Random(Plugin): target = self.plugin_conf.getint('track_to_add') artists = self.player.list('artist', '( artist != "")') random.shuffle(artists) - for art in artists: + for art in artists: # artists is a list of strings here if self.filtered_artist(art): continue self.log.debug('Random art: %s', art) -- 2.39.2 From ea1864a3e4e5b879a9ae84d3d97c23eebb0dc77a Mon Sep 17 00:00:00 2001 From: kaliko Date: Tue, 4 May 2021 20:21:13 +0200 Subject: [PATCH 11/16] Add new database replacement code. In case a previous database is loaded, it is renamed with a prefix "-old-version-backup" in the same directory. No migration managed. --- sima/launch.py | 9 ++++++++- sima/lib/simadb.py | 7 +++++++ 2 files changed, 15 insertions(+), 1 deletion(-) diff --git a/sima/launch.py b/sima/launch.py index 27e9d6b..890f96b 100644 --- a/sima/launch.py +++ b/sima/launch.py @@ -26,6 +26,7 @@ import sys from importlib import __import__ as sima_import from os.path import isfile +from os import rename ## # third parties components @@ -92,7 +93,13 @@ def start(sopt, restart=False): db_file = config.get('sima', 'db_file') if not isfile(db_file): logger.debug('Creating database in "%s"', db_file) - open(db_file, 'a').close() # TODO: to remove with new simadb in v0.18 + SimaDB(db_path=db_file).create_db() + # Migration from v0.17.0 + dbinfo = SimaDB(db_path=db_file).get_info() + if not dbinfo: # v0.17.0 → v0.18+ migration + logger.warning('Backing up database!') + rename(db_file, db_file + '-old-version-backup') + logger.info('Creating an new database in "%s"', db_file) SimaDB(db_path=db_file).create_db() if sopt.options.get('command'): diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index ca2277d..60844a2 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -49,6 +49,13 @@ class SimaDB: 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 """ -- 2.39.2 From 6e0cb104973681f2344de9c5ae4f04c62686401b Mon Sep 17 00:00:00 2001 From: kaliko Date: Tue, 4 May 2021 20:37:34 +0200 Subject: [PATCH 12/16] simadb: do not fetch NULL genre --- sima/lib/simadb.py | 2 +- tests/test_simadb.py | 11 +++++++++++ 2 files changed, 12 insertions(+), 1 deletion(-) diff --git a/sima/lib/simadb.py b/sima/lib/simadb.py index 60844a2..07794dc 100644 --- a/sima/lib/simadb.py +++ b/sima/lib/simadb.py @@ -542,7 +542,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() diff --git a/tests/test_simadb.py b/tests/test_simadb.py index e69c4bd..a577916 100644 --- a/tests/test_simadb.py +++ b/tests/test_simadb.py @@ -322,5 +322,16 @@ class Test_02Genre(Main): genre_hist = self.db.fetch_genres_history(limit=10) self.assertEqual([g[0] for g in genre_hist], genres[:10]) + def test_null_genres(self): + conn = self.db.get_database_connection() + genres = list() + for i in range(1, 2): # starts at 1 to ensure records are in the past + trk = Track(file=f'/foo/bar.{i}', name=f'{i}-baz', + album='foolbum', artist=f'{i}-art') + last = CURRENT - datetime.timedelta(minutes=i) + self.db.add_history(trk, date=last) + genre_hist = self.db.fetch_genres_history(limit=10) + self.assertEqual(genre_hist, []) + # VIM MODLINE # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 -- 2.39.2 From 24bfc0a6fceb390f4b967be0b83d412dcad44ca5 Mon Sep 17 00:00:00 2001 From: kaliko Date: Wed, 5 May 2021 17:58:12 +0200 Subject: [PATCH 13/16] Fixed test_config 568365b introduced a file acces control on db_file --- tests/test_config.py | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/tests/test_config.py b/tests/test_config.py index 99a89f8..36ba2ac 100644 --- a/tests/test_config.py +++ b/tests/test_config.py @@ -15,7 +15,8 @@ class TestConfMan(unittest.TestCase): @patch('sima.utils.config.makedirs') @patch('sima.utils.config.chmod') - def test_XDG_var(self, mock_makedirs, mock_chmod): + @patch('sima.utils.config.ConfMan.control_facc') + def test_XDG_var(self, *args): config_home = '/foo/bar' os.environ['XDG_CONFIG_HOME'] = config_home conf_file = os.path.join(config_home, DIRNAME, CONF_FILE) @@ -28,7 +29,8 @@ class TestConfMan(unittest.TestCase): self.assertEqual(conf.config['sima']['var_dir'], var_dir) @patch('sima.utils.config.isdir') - def test_default_locations(self, mock_isdir): + @patch('sima.utils.config.ConfMan.control_facc') + def test_default_locations(self, mock_isdir, *args): home = '/foo' mock_isdir.return_value = True os.environ.pop('XDG_CONFIG_HOME', None) -- 2.39.2 From d67a78c3f86751a551b370c32aaf3934e772825f Mon Sep 17 00:00:00 2001 From: kaliko Date: Wed, 5 May 2021 18:30:45 +0200 Subject: [PATCH 14/16] Add abstract unix socket support for MPD connection Add unittests for env var parsing --- doc/Changelog | 1 + sima/utils/config.py | 10 ++++--- sima/utils/utils.py | 33 +++++++++++++++-------- tests/test_config.py | 63 ++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 92 insertions(+), 15 deletions(-) diff --git a/doc/Changelog b/doc/Changelog index c487b0e..0e390f1 100644 --- a/doc/Changelog +++ b/doc/Changelog @@ -2,6 +2,7 @@ MPD_sima v0.18.0.dev0 * Removed obsolete --create-db and --generate-config options * Fixed crash when setting XDG_CONFIG_HOME (closes #50) + * Add abstract unix socket support for MPD connection -- kaliko Wed, 28 Apr 2021 17:21:39 +0200 diff --git a/sima/utils/config.py b/sima/utils/config.py index 854d6df..a46c0c6 100644 --- a/sima/utils/config.py +++ b/sima/utils/config.py @@ -139,7 +139,7 @@ class ConfMan: # CONFIG MANAGER CLASS self.use_envar() self.startopt = options - ## INIT CALLS + # INIT CALLS self.init_config() self.supersedes_config_with_cmd_line_options() # set dbfile @@ -204,9 +204,11 @@ class ConfMan: # CONFIG MANAGER CLASS # honor MPD_HOST format as in mpc(1) for command line option --host if self.startopt.get('host'): if '@' in self.startopt.get('host'): - passwd, host = self.startopt.get('host').split('@') - self.config.set('MPD', 'password', passwd) - self.config.set('MPD', 'host', host) + host, passwd = utils.parse_mpd_host(self.startopt.get('host')) + if passwd: + self.config.set('MPD', 'password', passwd) + if host: + self.config.set('MPD', 'host', host) def use_envar(self): """Use MPD en.var. to set defaults""" diff --git a/sima/utils/utils.py b/sima/utils/utils.py index 8e07e7a..b9dc980 100644 --- a/sima/utils/utils.py +++ b/sima/utils/utils.py @@ -30,7 +30,7 @@ from argparse import ArgumentError, Action from base64 import b64decode as push from codecs import getencoder from datetime import datetime -from os import environ, access, getcwd, W_OK, R_OK +from os import getenv, access, getcwd, W_OK, R_OK from os.path import dirname, isabs, join, normpath, exists, isdir, isfile from time import sleep @@ -45,22 +45,33 @@ def getws(dic): aka = getencoder('rot-13')(str((aka), 'utf-8'))[0] dic.update({'apikey': aka}) +def parse_mpd_host(value): + passwd = host = None + # If password is set: MPD_HOST=pass@host + if '@' in value: + mpd_host_env = value.split('@', 1) + if mpd_host_env[0]: + # A password is actually set + passwd = mpd_host_env[0] + if mpd_host_env[1]: + host = mpd_host_env[1] + elif mpd_host_env[1]: + # No password set but leading @ is an abstract socket + host = '@'+mpd_host_env[1] + else: + # MPD_HOST is a plain host + host = value + return host, passwd + def get_mpd_environ(): """ Retrieve MPD env. var. """ passwd = host = None - mpd_host_env = environ.get('MPD_HOST') - if mpd_host_env: - # If password is set: - # mpd_host_env = ['pass', 'host'] because MPD_HOST=pass@host - mpd_host_env = mpd_host_env.split('@') - mpd_host_env.reverse() - host = mpd_host_env[0] - if len(mpd_host_env) > 1 and mpd_host_env[1]: - passwd = mpd_host_env[1] - return (host, environ.get('MPD_PORT', None), passwd) + if getenv('MPD_HOST'): + host, passwd = parse_mpd_host(getenv('MPD_HOST')) + return (host, getenv('MPD_PORT', None), passwd) def normalize_path(path): diff --git a/tests/test_config.py b/tests/test_config.py index 36ba2ac..eee93bd 100644 --- a/tests/test_config.py +++ b/tests/test_config.py @@ -12,6 +12,11 @@ from sima.lib.logger import set_logger class TestConfMan(unittest.TestCase): + """For some tests we don't care about file access check, then to ensure + checks are properly mocked run test forcing non existent locations: + + XDG_DATA_HOME=/non/existent/ XDG_HOME_CONFIG=/non/existent/ python3 -m unittest -vf tests/test_config.py + """ @patch('sima.utils.config.makedirs') @patch('sima.utils.config.chmod') @@ -46,5 +51,63 @@ class TestConfMan(unittest.TestCase): expected_config = os.path.join(home, '.config', DIRNAME, CONF_FILE) self.assertEqual(constructed_config_location, expected_config) + @patch('sima.utils.config.makedirs') + @patch('sima.utils.config.chmod') + @patch('sima.utils.config.ConfMan.control_facc') + def test_MPD_env_var(self, *args): + host = 'example.org' + passwd = 's2cr34!' + port = '6601' + os.environ.pop('MPD_HOST', None) + os.environ.pop('MPD_PORT', None) + # Test defaults + conf = ConfMan({}) + self.assertEqual(dict(conf.config['MPD']), + {'host': 'localhost', 'port': '6600'}) + # Test provided env. var. + os.environ['MPD_HOST'] = host + conf = ConfMan({}) + self.assertEqual(dict(conf.config['MPD']), + {'host': host, 'port': '6600'}) + os.environ['MPD_HOST'] = f'{passwd}@{host}' + conf = ConfMan({}) + self.assertEqual(dict(conf.config['MPD']), + {'host': host, + 'password': passwd, + 'port': '6600'}) + # Test abstract unix socket support with password + os.environ['MPD_HOST'] = f'{passwd}@@/{host}' + conf = ConfMan({}) + self.assertEqual(dict(conf.config['MPD']), + {'host': f'@/{host}', + 'password': passwd, + 'port': '6600'}) + # Test abstract unix socket support only + os.environ['MPD_HOST'] = f'@/{host}' + conf = ConfMan({}) + self.assertEqual(dict(conf.config['MPD']), + {'host': f'@/{host}', + 'port': '6600'}) + # Test port + os.environ['MPD_PORT'] = f'{port}' + conf = ConfMan({}) + self.assertEqual(conf.config['MPD']['port'], port) + + @patch('sima.utils.config.makedirs') + @patch('sima.utils.config.chmod') + @patch('sima.utils.config.ConfMan.control_facc') + def test_config_origin_priority(self, *args): + # cli provided host overrides env. var. + os.environ['MPD_HOST'] = 'baz.foo' + conf = ConfMan({'host': 'cli.host'}) + self.assertEqual(conf.config['MPD']['host'], 'cli.host') + # cli provided abstract socket overrides env. var. + conf = ConfMan({'host': '@/abstract'}) + self.assertEqual(conf.config['MPD']['host'], '@/abstract') + # cli provided passord and abstract socket overrides env. var. + conf = ConfMan({'host': 'pass!@@/abstract'}) + self.assertEqual(conf.config['MPD']['host'], '@/abstract') + self.assertEqual(conf.config['MPD']['password'], 'pass!') + # VIM MODLINE # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8 -- 2.39.2 From b374f36f34ce368fbfc2b8b56b784aa45b27ec7e Mon Sep 17 00:00:00 2001 From: kaliko Date: Wed, 5 May 2021 20:20:01 +0200 Subject: [PATCH 15/16] Use os.getenv instead of os.environ.get --- sima/lib/logger.py | 4 ++-- sima/utils/config.py | 12 ++++++------ 2 files changed, 8 insertions(+), 8 deletions(-) diff --git a/sima/lib/logger.py b/sima/lib/logger.py index 50785f1..b9b8b40 100644 --- a/sima/lib/logger.py +++ b/sima/lib/logger.py @@ -26,7 +26,7 @@ Logging facility for sima. import logging import sys -from os import environ +from os import getenv DEBUG = logging.DEBUG INFO = logging.INFO @@ -56,7 +56,7 @@ def set_logger(level='info', logfile=None): logfile: file to log to """ name = 'sima' - if environ.get('TRACE', False): + if getenv('TRACE', False): user_log_level = TRACE_LEVEL_NUM else: user_log_level = getattr(logging, level.upper()) diff --git a/sima/utils/config.py b/sima/utils/config.py index a46c0c6..fd5cab6 100644 --- a/sima/utils/config.py +++ b/sima/utils/config.py @@ -30,7 +30,7 @@ import logging import sys from configparser import Error -from os import (access, makedirs, environ, stat, chmod, W_OK) +from os import (access, makedirs, getenv, stat, chmod, W_OK) from os.path import (join, isdir, isfile, dirname, exists) from stat import (S_IMODE, ST_MODE, S_IRWXO, S_IRWXG) @@ -230,10 +230,10 @@ class ConfMan: # CONFIG MANAGER CLASS http://standards.freedesktop.org/basedir-spec/basedir-spec-0.6.html """ - homedir = environ.get('HOME') + homedir = getenv('HOME') - if environ.get('XDG_DATA_HOME'): - data_dir = join(environ.get('XDG_DATA_HOME'), DIRNAME) + if getenv('XDG_DATA_HOME'): + data_dir = join(getenv('XDG_DATA_HOME'), DIRNAME) elif homedir and isdir(homedir) and homedir not in ['/']: data_dir = join(homedir, '.local', 'share', DIRNAME) else: @@ -244,8 +244,8 @@ class ConfMan: # CONFIG MANAGER CLASS if self.startopt.get('conf_file'): # No need to handle conf file location pass - elif environ.get('XDG_CONFIG_HOME'): - conf_dir = join(environ.get('XDG_CONFIG_HOME'), DIRNAME) + elif getenv('XDG_CONFIG_HOME'): + conf_dir = join(getenv('XDG_CONFIG_HOME'), DIRNAME) self.conf_file = join(conf_dir, CONF_FILE) elif homedir and isdir(homedir) and homedir not in ['/']: conf_dir = join(homedir, '.config', DIRNAME) -- 2.39.2 From 5de446a3d7e67a95afa7cff1c9c12642daeaf8ce Mon Sep 17 00:00:00 2001 From: kaliko Date: Thu, 6 May 2021 11:32:29 +0200 Subject: [PATCH 16/16] MPD client: Remove useless check on use_mbid --- sima/mpdclient.py | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/sima/mpdclient.py b/sima/mpdclient.py index bccdb97..1ad5100 100644 --- a/sima/mpdclient.py +++ b/sima/mpdclient.py @@ -353,11 +353,11 @@ class MPD(MPDClient): if not hasattr(album, 'artist'): raise PlayerError('Album object have no artist attribute') albums = [] - if self.use_mbid and album.mbid: + if album.mbid: filt = f"(MUSICBRAINZ_ALBUMID == '{album.mbid}')" albums = self.find(filt) # Now look for album with no MusicBrainzIdentifier - if not albums and album.artist.mbid and self.use_mbid: # Use album artist MBID if possible + if not albums and album.artist.mbid: # Use album artist MBID if possible filt = f"((MUSICBRAINZ_ALBUMARTISTID == '{album.artist.mbid}') AND (album == '{album.name_sz}'))" albums = self.find(filt) if not albums: # Falls back to (album)?artist/album name @@ -382,7 +382,7 @@ class MPD(MPDClient): Returns an Artist object """ found = False - if self.use_mbid and artist.mbid: + if artist.mbid: # look for exact search w/ musicbrainz_artistid library = self.list('artist', f"(MUSICBRAINZ_ARTISTID == '{artist.mbid}')") if library: -- 2.39.2