]> kaliko git repositories - mpd-sima.git/blob - sima/lib/simadb.py
Add new database replacement code.
[mpd-sima.git] / sima / lib / simadb.py
1 # Copyright (c) 2009-2013, 2019-2021 kaliko <kaliko@azylum.org>
2 #
3 #  This file is part of sima
4 #
5 #  sima is free software: you can redistribute it and/or modify
6 #  it under the terms of the GNU General Public License as published by
7 #  the Free Software Foundation, either version 3 of the License, or
8 #  (at your option) any later version.
9 #
10 #  sima is distributed in the hope that it will be useful,
11 #  but WITHOUT ANY WARRANTY; without even the implied warranty of
12 #  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13 #  GNU General Public License for more details.
14 #
15 #  You should have received a copy of the GNU General Public License
16 #  along with sima.  If not, see <http://www.gnu.org/licenses/>.
17 #
18 #
19 """SQlite database library
20 """
21
22 __DB_VERSION__ = 4
23 __HIST_DURATION__ = int(30 * 24)  # in hours
24
25 import sqlite3
26
27 from collections import deque
28 from datetime import (datetime, timedelta)
29
30 from sima.lib.meta import Artist, Album
31 from sima.lib.track import Track
32
33
34 class SimaDBError(Exception):
35     """
36     Exceptions.
37     """
38
39
40 class SimaDB:
41     "SQLite management"
42
43     def __init__(self, db_path=None):
44         self._db_path = db_path
45
46     def get_database_connection(self):
47         """get database reference"""
48         connection = sqlite3.connect(
49             self._db_path, isolation_level=None)
50         return connection
51
52     def get_info(self):
53         connection = self.get_database_connection()
54         info = connection.execute("""SELECT * FROM db_info
55                     WHERE name = "DB Version" LIMIT 1;""").fetchone()
56         connection.close()
57         return info
58
59     def create_db(self):
60         """ Set up a database
61         """
62         connection = self.get_database_connection()
63         connection.execute(
64             'CREATE TABLE IF NOT EXISTS db_info'
65             ' (name CHAR(50), value CHAR(50))')
66         connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ?
67                            WHERE NOT EXISTS
68                            ( SELECT 1 FROM db_info WHERE name = ? )''',
69                            ('DB Version', __DB_VERSION__, 'DB Version'))
70         connection.execute(  # ARTISTS
71             'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, '
72             'name VARCHAR(100), mbid CHAR(36))')
73         connection.execute(  # ALBUMS
74             'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, '
75             'name VARCHAR(100), mbid CHAR(36))')
76         connection.execute(  # ALBUMARTISTS
77             'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, '
78             'name VARCHAR(100), mbid CHAR(36))')
79         connection.execute(  # TRACKS
80             'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, '
81             'title VARCHAR(100), artist INTEGER, '
82             'album INTEGER, albumartist INTEGER, '
83             'file VARCHAR(500), mbid CHAR(36), '
84             'FOREIGN KEY(artist)       REFERENCES artists(id), '
85             'FOREIGN KEY(album)        REFERENCES albums(id), '
86             'FOREIGN KEY(albumartist)  REFERENCES albumartists(id))')
87         connection.execute(  # HISTORY
88             'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, '
89             'last_play TIMESTAMP, track INTEGER, '
90             'FOREIGN KEY(track) REFERENCES tracks(id))')
91         connection.execute(  # BLOCKLIST
92             'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, '
93             'artist INTEGER, album INTEGER, track INTEGER, '
94             'FOREIGN KEY(artist) REFERENCES artists(id), '
95             'FOREIGN KEY(album)  REFERENCES albums(id), '
96             'FOREIGN KEY(track)  REFERENCES tracks(id))')
97         connection.execute(  # Genres (Many-to-many)
98             'CREATE TABLE IF NOT EXISTS genres '
99             '(id INTEGER PRIMARY KEY, name VARCHAR(100))')
100         connection.execute(  # Junction Genres Tracks
101                 """CREATE TABLE IF NOT EXISTS tracks_genres
102                 ( track INTEGER, genre INTEGER,
103                 FOREIGN KEY(track) REFERENCES tracks(id)
104                 FOREIGN KEY(genre) REFERENCES genres(id))""")
105         # Create cleanup triggers:
106         # DELETE history → Tracks / Tracks_genres tables
107         connection.execute('''
108             CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
109             AFTER DELETE ON history
110             WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
111                   (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
112             BEGIN
113              DELETE FROM tracks WHERE id = old.track;
114              DELETE FROM tracks_genres WHERE track = old.track;
115             END;
116             ''')
117         # DELETE Tracks_Genres → Genres table
118         connection.execute('''
119             CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres
120             AFTER DELETE ON tracks_genres
121             WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0)
122             BEGIN
123              DELETE FROM genres WHERE id = old.genre;
124             END;
125             ''')
126         # DELETE Tracks → Artists table
127         connection.execute('''
128             CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
129             AFTER DELETE ON tracks
130             WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
131                   (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
132             BEGIN
133              DELETE FROM artists WHERE id = old.artist;
134             END;
135             ''')
136         # DELETE Tracks → Albums table
137         connection.execute('''
138             CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
139             AFTER DELETE ON tracks
140             WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
141                   (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
142             BEGIN
143              DELETE FROM albums WHERE id = old.album;
144             END;
145             ''')
146         # DELETE Tracks → cleanup AlbumArtists table
147         connection.execute('''
148             CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
149             AFTER DELETE ON tracks
150             WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
151             BEGIN
152              DELETE FROM albumartists WHERE id = old.albumartist;
153             END;
154             ''')
155         # DELETE blocklist → Tracks table
156         connection.execute('''
157             CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
158             AFTER DELETE ON blocklist
159             WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
160                   (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
161             BEGIN
162              DELETE FROM tracks WHERE id = old.track;
163             END;
164             ''')
165         # DELETE blocklist → Artists table
166         # The "SELECT count(*) FROM blocklist" is useless,
167         # there can be only one blocklist.artist
168         connection.execute('''
169             CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_artists
170             AFTER DELETE ON blocklist
171             WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
172                   (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
173             BEGIN
174              DELETE FROM artists WHERE id = old.artist;
175             END;
176             ''')
177         # DELETE Tracks → Albums table
178         # The "SELECT count(*) FROM blocklist" is useless,
179         # there can be only one blocklist.album
180         connection.execute('''
181             CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_albums
182             AFTER DELETE ON blocklist
183             WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
184                   (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
185             BEGIN
186              DELETE FROM albums WHERE id = old.album;
187             END;
188             ''')
189         connection.close()
190
191     def drop_all(self):
192         connection = self.get_database_connection()
193         rows = connection.execute(
194                 "SELECT name FROM sqlite_master WHERE type='table'")
195         for r in rows.fetchall():
196             connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
197         connection.close()
198
199     def _remove_blocklist_id(self, blid, with_connection=None):
200         """Remove id"""
201         if with_connection:
202             connection = with_connection
203         else:
204             connection = self.get_database_connection()
205         connection = self.get_database_connection()
206         connection.execute('DELETE FROM blocklist'
207                            ' WHERE blocklist.id = ?', (blid,))
208         connection.commit()
209         if not with_connection:
210             connection.close()
211
212     def _get_album(self, album, connection):
213         if album.mbid:
214             return connection.execute(
215                 "SELECT id FROM albums WHERE mbid = ?",
216                 (album.mbid,))
217         else:
218             return connection.execute(
219                 "SELECT id FROM albums WHERE name = ? AND mbid IS NULL",
220                 (album.name,))
221
222     def get_album(self, album, with_connection=None, add=True):
223         """get album information from the database.
224         if not in database insert new entry.
225
226         :param sima.lib.meta.Album album: album objet
227         :param sqlite3.Connection with_connection: SQLite connection
228         """
229         if with_connection:
230             connection = with_connection
231         else:
232             connection = self.get_database_connection()
233         rows = self._get_album(album, connection)
234         for row in rows:
235             if not with_connection:
236                 connection.close()
237             return row[0]
238         if not add:
239             if not with_connection:
240                 connection.close()
241             return None
242         connection.execute(
243             "INSERT INTO albums (name, mbid) VALUES (?, ?)",
244             (album.name, album.mbid))
245         connection.commit()
246         rows = self._get_album(album, connection)
247         for row in rows:
248             if not with_connection:
249                 connection.close()
250             return row[0]
251         if not with_connection:
252             connection.close()
253         return None
254
255     def _get_albumartist(self, artist, connection):
256         if artist.mbid:
257             return connection.execute(
258                 "SELECT id FROM albumartists WHERE mbid = ?",
259                 (artist.mbid,))
260         else:
261             return connection.execute(
262                 "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL",
263                 (artist.name,))
264
265     def get_albumartist(self, artist, with_connection=None, add=True):
266         """get albumartist information from the database.
267         if not in database insert new entry.
268
269         :param sima.lib.meta.Artist artist: artist
270         :param sqlite3.Connection with_connection: SQLite connection
271         """
272         if with_connection:
273             connection = with_connection
274         else:
275             connection = self.get_database_connection()
276         rows = self._get_albumartist(artist, connection)
277         for row in rows:
278             if not with_connection:
279                 connection.close()
280             return row[0]
281         if not add:
282             if not with_connection:
283                 connection.close()
284             return None
285         connection.execute(
286             "INSERT INTO albumartists (name, mbid) VALUES (?, ?)",
287             (artist.name, artist.mbid))
288         connection.commit()
289         rows = self._get_albumartist(artist, connection)
290         for row in rows:
291             if not with_connection:
292                 connection.close()
293             return row[0]
294         if not with_connection:
295             connection.close()
296
297     def _get_artist(self, artist, connection):
298         if artist.mbid:
299             return connection.execute(
300                 "SELECT id FROM artists WHERE mbid = ?",
301                 (artist.mbid,))
302         else:
303             return connection.execute(
304                 "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,))
305
306     def get_artist(self, artist, with_connection=None, add=True):
307         """get artist information from the database.
308         if not in database insert new entry.
309
310         :param sima.lib.meta.Artist artist: artist
311         :param sqlite3.Connection with_connection: SQLite connection
312         """
313         if with_connection:
314             connection = with_connection
315         else:
316             connection = self.get_database_connection()
317         rows = self._get_artist(artist, connection)
318         for row in rows:
319             if not with_connection:
320                 connection.close()
321             return row[0]
322         if not add:
323             if not with_connection:
324                 connection.close()
325             return None
326         connection.execute(
327             "INSERT INTO artists (name, mbid) VALUES (?, ?)",
328             (artist.name, artist.mbid))
329         connection.commit()
330         rows = self._get_artist(artist, connection)
331         for row in rows:
332             if not with_connection:
333                 connection.close()
334             return row[0]
335         if not with_connection:
336             connection.close()
337
338     def get_genre(self, genre, with_connection=None, add=True):
339         """get genre from the database.
340         if not in database insert new entry.
341
342         :param str genre: genre as a string
343         :param sqlite3.Connection with_connection: SQLite connection
344         """
345         if with_connection:
346             connection = with_connection
347         else:
348             connection = self.get_database_connection()
349         rows = connection.execute(
350             "SELECT id FROM genres WHERE name = ?", (genre,))
351         for row in rows:
352             if not with_connection:
353                 connection.close()
354             return row[0]
355         if not add:
356             if not with_connection:
357                 connection.close()
358             return None
359         connection.execute(
360             "INSERT INTO genres (name) VALUES (?)", (genre,))
361         connection.commit()
362         rows = connection.execute(
363             "SELECT id FROM genres WHERE name = ?", (genre,))
364         for row in rows:
365             if not with_connection:
366                 connection.close()
367             return row[0]
368
369     def get_track(self, track, with_connection=None, add=True):
370         """Get a track id from Tracks table, add if not existing,
371         :param sima.lib.track.Track track: track to use
372         :param bool add: add non existing track to database"""
373         if not track.file:
374             raise SimaDBError('Got a track with no file attribute: %r' % track)
375         if with_connection:
376             connection = with_connection
377         else:
378             connection = self.get_database_connection()
379         rows = connection.execute(
380             "SELECT * FROM tracks WHERE file = ?", (track.file,))
381         for row in rows:
382             if not with_connection:
383                 connection.close()
384             return row[0]
385         if not add:  # Not adding non existing track
386             connection.close()
387             return None
388         # Get an artist record or None
389         if track.artist:
390             art = Artist(name=track.artist, mbid=track.musicbrainz_artistid)
391             art_id = self.get_artist(art, with_connection=connection)
392         else:
393             art_id = None
394         # Get an albumartist record or None
395         if track.albumartist:
396             albart = Artist(name=track.albumartist,
397                             mbid=track.musicbrainz_albumartistid)
398             albart_id = self.get_albumartist(albart, with_connection=connection)
399         else:
400             albart_id = None
401         # Get an album record or None
402         if track.album:
403             alb = Album(name=track.album, mbid=track.musicbrainz_albumid)
404             alb_id = self.get_album(alb, with_connection=connection)
405         else:
406             alb_id = None
407         connection.execute(
408             """INSERT INTO tracks (artist, albumartist, album, title, mbid, file)
409                 VALUES (?, ?, ?, ?, ?, ?)""",
410             (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid,
411                 track.file))
412         connection.commit()
413         # Add track id to junction tables
414         self._add_tracks_genres(track, connection)
415         rows = connection.execute(
416             "SELECT id FROM tracks WHERE file = ?", (track.file,))
417         for row in rows:
418             if not with_connection:
419                 connection.close()
420             return row[0]
421         if not with_connection:
422             connection.close()
423         return None
424
425     def _add_tracks_genres(self, track, connection):
426         if not track.genres:
427             return None
428         rows = connection.execute(
429             "SELECT id FROM tracks WHERE file = ?", (track.file,))
430         trk_id = rows.fetchone()[0]
431         for genre in track.genres:
432             # add genre
433             gen_id = self.get_genre(genre)
434             connection.execute("""INSERT INTO tracks_genres (track, genre)
435                     VALUES (?, ?)""", (trk_id, gen_id))
436
437     def add_history(self, track, date=None):
438         """Record last play date of track (ie. not a real exhautive play history).
439         :param track sima.lib.track.Track: track to add to history"""
440         if not date:
441             date = datetime.now()
442         connection = self.get_database_connection()
443         track_id = self.get_track(track, with_connection=connection)
444         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
445                                   (track_id,))
446         if not rows.fetchone():
447             connection.execute("INSERT INTO history (track) VALUES (?)",
448                                (track_id,))
449         connection.execute("UPDATE history SET last_play = ? "
450                            " WHERE track = ?", (date, track_id,))
451         connection.commit()
452         connection.close()
453
454     def purge_history(self, duration=__HIST_DURATION__):
455         """Remove old entries in history
456         :param duration int: Purge history record older than duration in hours
457                             (defaults to __HIST_DURATION__)"""
458         connection = self.get_database_connection()
459         connection.execute("DELETE FROM history WHERE last_play"
460                            " < datetime('now', '-%i hours')" % duration)
461         connection.execute('VACUUM')
462         connection.commit()
463         connection.close()
464
465     def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__):
466         """
467         :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
468         """
469         date = datetime.utcnow() - timedelta(hours=duration)
470         connection = self.get_database_connection()
471         connection.row_factory = sqlite3.Row
472         rows = connection.execute("""
473                 SELECT albums.name AS name,
474                        albums.mbid as mbid,
475                        artists.name as artist,
476                        artists.mbid as artist_mbib
477                 FROM history
478                 JOIN tracks ON history.track = tracks.id
479                 LEFT OUTER JOIN albums ON tracks.album = albums.id
480                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
481                 WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
482                 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
483         hist = list()
484         for row in rows:
485             vals = dict(row)
486             artist = Artist(name=vals.pop('artist'),
487                             mbid=vals.pop('artist_mbib'))
488             if needle:
489                 if needle != artist:
490                     continue
491             album = Album(**vals, artist=artist)
492             if hist and hist[-1] == album:
493                 # remove consecutive dupes
494                 continue
495             hist.append(album)
496         connection.close()
497         return hist
498
499     def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
500         """Returns a list of Artist objects
501         :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.
502         :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
503         """
504         date = datetime.utcnow() - timedelta(hours=duration)
505         connection = self.get_database_connection()
506         connection.row_factory = sqlite3.Row
507         rows = connection.execute("""
508                 SELECT artists.name AS name,
509                        artists.mbid as mbid
510                 FROM history
511                 JOIN tracks ON history.track = tracks.id
512                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
513                 WHERE history.last_play > ? AND artists.name NOT NULL
514                 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
515         last = deque(maxlen=1)
516         hist = list()
517         for row in rows:
518             artist = Artist(**row)
519             if last and last[0] == artist:  # remove consecutive dupes
520                 continue
521             last.append(artist)
522             if needle and isinstance(needle, (Artist, str)):
523                 if needle == artist:
524                     hist.append(artist)  # No need to go further
525                     break
526                 continue
527             elif needle and getattr(needle, '__contains__'):
528                 if artist in needle:
529                     hist.append(artist)  # No need to go further
530                 continue
531             hist.append(artist)
532         connection.close()
533         return hist
534
535     def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
536         date = datetime.utcnow() - timedelta(hours=duration)
537         connection = self.get_database_connection()
538         rows = connection.execute("""
539                 SELECT genres.name, artists.name
540                 FROM history
541                 JOIN tracks ON history.track = tracks.id
542                 LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id
543                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
544                 LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre
545                 WHERE history.last_play > ?
546                 ORDER BY history.last_play DESC
547                 """, (date.isoformat(' '),))
548         genres = list()
549         for row in rows:
550             genres.append(row)
551             if len({g[0] for g in genres}) >= limit:
552                 break
553         connection.close()
554         return genres
555
556     def fetch_history(self, artist=None, duration=__HIST_DURATION__):
557         """Fetches tracks history, more recent first
558         :param sima.lib.meta.Artist artist: limit history to this artist
559         :param int duration: How long ago to fetch history from
560         """
561         date = datetime.utcnow() - timedelta(hours=duration)
562         connection = self.get_database_connection()
563         connection.row_factory = sqlite3.Row
564         sql = """
565               SELECT tracks.title, tracks.file, artists.name AS artist,
566                      albumartists.name AS albumartist,
567                      artists.mbid as musicbrainz_artistid,
568                      albums.name AS album,
569                      albums.mbid AS musicbrainz_albumid,
570                      tracks.mbid as musicbrainz_trackid
571               FROM history
572               JOIN tracks ON history.track = tracks.id
573               LEFT OUTER JOIN artists ON tracks.artist = artists.id
574               LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
575               LEFT OUTER JOIN albums ON tracks.album = albums.id
576               WHERE history.last_play > ?
577               """
578         if artist:
579             if artist.mbid:
580                 rows = connection.execute(sql+"""
581                         AND artists.mbid = ?
582                         ORDER BY history.last_play DESC""",
583                                           (date.isoformat(' '), artist.mbid))
584             else:
585                 rows = connection.execute(sql+"""
586                         AND artists.name = ?
587                         ORDER BY history.last_play DESC""",
588                                           (date.isoformat(' '), artist.name))
589         else:
590             rows = connection.execute(sql+'ORDER BY history.last_play DESC',
591                                       (date.isoformat(' '),))
592         hist = list()
593         for row in rows:
594             hist.append(Track(**row))
595         connection.close()
596         return hist
597
598     def get_bl_track(self, track, with_connection=None, add=True):
599         """Add a track to blocklist
600         :param sima.lib.track.Track track: Track object to add to blocklist
601         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
602         :param bool add: Default is to add a new record, set to False to fetch associated record"""
603         if with_connection:
604             connection = with_connection
605         else:
606             connection = self.get_database_connection()
607         track_id = self.get_track(track, with_connection=connection, add=True)
608         rows = connection.execute(
609             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
610         if not rows.fetchone():
611             if not add:
612                 return None
613             connection.execute('INSERT INTO blocklist (track) VALUES (?)',
614                                (track_id,))
615             connection.commit()
616         rows = connection.execute(
617             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
618         bl = rows.fetchone()[0]
619         if not with_connection:
620             connection.close()
621         return bl
622
623     def get_bl_album(self, album, with_connection=None, add=True):
624         """Add an album to blocklist
625         :param sima.lib.meta.Album: Album object to add to blocklist
626         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
627         :param bool add: Default is to add a new record, set to False to fetch associated record"""
628         if with_connection:
629             connection = with_connection
630         else:
631             connection = self.get_database_connection()
632         album_id = self.get_album(album, with_connection=connection, add=True)
633         rows = connection.execute(
634             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
635         if not rows.fetchone():
636             if not add:
637                 return None
638             connection.execute('INSERT INTO blocklist (album) VALUES (?)',
639                                (album_id,))
640             connection.commit()
641         rows = connection.execute(
642             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
643         bl = rows.fetchone()[0]
644         if not with_connection:
645             connection.close()
646         return bl
647
648     def get_bl_artist(self, artist, with_connection=None, add=True):
649         """Add an artist to blocklist
650         :param sima.lib.meta.Artist: Artist object to add to blocklist
651         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
652         :param bool add: Default is to add a new record, set to False to fetch associated record"""
653         if with_connection:
654             connection = with_connection
655         else:
656             connection = self.get_database_connection()
657         artist_id = self.get_artist(artist, with_connection=connection, add=True)
658         rows = connection.execute(
659             "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
660         if not rows.fetchone():
661             if not add:
662                 return None
663             connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
664                                (artist_id,))
665             connection.commit()
666         rows = connection.execute(
667             "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
668         bl = rows.fetchone()[0]
669         if not with_connection:
670             connection.close()
671         return bl
672
673     def delete_bl(self, track=None, album=None, artist=None):
674         if not (track or album or artist):
675             return
676         connection = self.get_database_connection()
677         blid = None
678         if track:
679             blid = self.get_bl_track(track, with_connection=connection)
680         if album:
681             blid = self.get_bl_album(album, with_connection=connection)
682         if artist:
683             blid = self.get_bl_artist(artist, with_connection=connection)
684         if not blid:
685             return
686         self._remove_blocklist_id(blid, with_connection=connection)
687         connection.close()
688
689
690 # VIM MODLINE
691 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8