]> kaliko git repositories - mpd-sima.git/blob - sima/lib/simadb.py
Do not limit valid UUID to version 4
[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(self._db_path, isolation_level=None)
49         return connection
50
51     def get_info(self):
52         connection = self.get_database_connection()
53         info = connection.execute("""SELECT * FROM db_info
54                     WHERE name = "DB Version" LIMIT 1;""").fetchone()
55         connection.close()
56         return info
57
58     def create_db(self):
59         """ Set up a database
60         """
61         connection = self.get_database_connection()
62         connection.execute(
63             'CREATE TABLE IF NOT EXISTS db_info'
64             ' (name CHAR(50), value CHAR(50))')
65         connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ?
66                            WHERE NOT EXISTS
67                            ( SELECT 1 FROM db_info WHERE name = ? )''',
68                            ('DB Version', __DB_VERSION__, 'DB Version'))
69         connection.execute(  # ARTISTS
70             'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, '
71             'name VARCHAR(100), mbid CHAR(36))')
72         connection.execute(  # ALBUMS
73             'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, '
74             'name VARCHAR(100), mbid CHAR(36))')
75         connection.execute(  # ALBUMARTISTS
76             'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, '
77             'name VARCHAR(100), mbid CHAR(36))')
78         connection.execute(  # TRACKS
79             'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, '
80             'title VARCHAR(100), artist INTEGER, '
81             'album INTEGER, albumartist INTEGER, '
82             'file VARCHAR(500), mbid CHAR(36), '
83             'FOREIGN KEY(artist)       REFERENCES artists(id), '
84             'FOREIGN KEY(album)        REFERENCES albums(id), '
85             'FOREIGN KEY(albumartist)  REFERENCES albumartists(id))')
86         connection.execute(  # HISTORY
87             'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, '
88             'last_play TIMESTAMP, track INTEGER, '
89             'FOREIGN KEY(track) REFERENCES tracks(id))')
90         connection.execute(  # BLOCKLIST
91             'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, '
92             'artist INTEGER, album INTEGER, track INTEGER, '
93             'FOREIGN KEY(artist) REFERENCES artists(id), '
94             'FOREIGN KEY(album)  REFERENCES albums(id), '
95             'FOREIGN KEY(track)  REFERENCES tracks(id))')
96         connection.execute(  # Genres (Many-to-many)
97             'CREATE TABLE IF NOT EXISTS genres '
98             '(id INTEGER PRIMARY KEY, name VARCHAR(100))')
99         connection.execute(  # Junction Genres Tracks
100                 """CREATE TABLE IF NOT EXISTS tracks_genres
101                 ( track INTEGER, genre INTEGER,
102                 FOREIGN KEY(track) REFERENCES tracks(id)
103                 FOREIGN KEY(genre) REFERENCES genres(id))""")
104         # Create cleanup triggers:
105         # DELETE history → Tracks / Tracks_genres tables
106         connection.execute('''
107             CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
108             AFTER DELETE ON history
109             WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
110                   (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
111             BEGIN
112              DELETE FROM tracks WHERE id = old.track;
113              DELETE FROM tracks_genres WHERE track = old.track;
114             END;
115             ''')
116         # DELETE Tracks_Genres → Genres table
117         connection.execute('''
118             CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres
119             AFTER DELETE ON tracks_genres
120             WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0)
121             BEGIN
122              DELETE FROM genres WHERE id = old.genre;
123             END;
124             ''')
125         # DELETE Tracks → Artists table
126         connection.execute('''
127             CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
128             AFTER DELETE ON tracks
129             WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
130                   (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
131             BEGIN
132              DELETE FROM artists WHERE id = old.artist;
133             END;
134             ''')
135         # DELETE Tracks → Albums table
136         connection.execute('''
137             CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
138             AFTER DELETE ON tracks
139             WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
140                   (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
141             BEGIN
142              DELETE FROM albums WHERE id = old.album;
143             END;
144             ''')
145         # DELETE Tracks → cleanup AlbumArtists table
146         connection.execute('''
147             CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
148             AFTER DELETE ON tracks
149             WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
150             BEGIN
151              DELETE FROM albumartists WHERE id = old.albumartist;
152             END;
153             ''')
154         # DELETE blocklist → Tracks table
155         connection.execute('''
156             CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
157             AFTER DELETE ON blocklist
158             WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
159                   (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
160             BEGIN
161              DELETE FROM tracks WHERE id = old.track;
162             END;
163             ''')
164         # DELETE blocklist → Artists table
165         # The "SELECT count(*) FROM blocklist" is useless,
166         # there can be only one blocklist.artist
167         connection.execute('''
168             CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_artists
169             AFTER DELETE ON blocklist
170             WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
171                   (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
172             BEGIN
173              DELETE FROM artists WHERE id = old.artist;
174             END;
175             ''')
176         # DELETE Tracks → Albums table
177         # The "SELECT count(*) FROM blocklist" is useless,
178         # there can be only one blocklist.album
179         connection.execute('''
180             CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_albums
181             AFTER DELETE ON blocklist
182             WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
183                   (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
184             BEGIN
185              DELETE FROM albums WHERE id = old.album;
186             END;
187             ''')
188         connection.close()
189
190     def drop_all(self):
191         connection = self.get_database_connection()
192         rows = connection.execute(
193                 "SELECT name FROM sqlite_master WHERE type='table'")
194         for r in rows.fetchall():
195             connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
196         connection.close()
197
198     def _remove_blocklist_id(self, blid, with_connection=None):
199         """Remove id"""
200         if with_connection:
201             connection = with_connection
202         else:
203             connection = self.get_database_connection()
204         connection = self.get_database_connection()
205         connection.execute('DELETE FROM blocklist'
206                            ' WHERE blocklist.id = ?', (blid,))
207         connection.commit()
208         if not with_connection:
209             connection.close()
210
211     def _get_album(self, album, connection):
212         if album.mbid:
213             return connection.execute(
214                 "SELECT id FROM albums WHERE mbid = ?",
215                 (album.mbid,))
216         else:
217             return connection.execute(
218                 "SELECT id FROM albums WHERE name = ? AND mbid IS NULL",
219                 (album.name,))
220
221     def get_album(self, album, with_connection=None, add=True):
222         """get album information from the database.
223         if not in database insert new entry.
224
225         :param sima.lib.meta.Album album: album objet
226         :param sqlite3.Connection with_connection: SQLite connection
227         """
228         if with_connection:
229             connection = with_connection
230         else:
231             connection = self.get_database_connection()
232         rows = self._get_album(album, connection)
233         for row in rows:
234             if not with_connection:
235                 connection.close()
236             return row[0]
237         if not add:
238             if not with_connection:
239                 connection.close()
240             return None
241         connection.execute(
242             "INSERT INTO albums (name, mbid) VALUES (?, ?)",
243             (album.name, album.mbid))
244         connection.commit()
245         rows = self._get_album(album, connection)
246         for row in rows:
247             if not with_connection:
248                 connection.close()
249             return row[0]
250         if not with_connection:
251             connection.close()
252         return None
253
254     def _get_albumartist(self, artist, connection):
255         if artist.mbid:
256             return connection.execute(
257                 "SELECT id FROM albumartists WHERE mbid = ?",
258                 (artist.mbid,))
259         else:
260             return connection.execute(
261                 "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL",
262                 (artist.name,))
263
264     def get_albumartist(self, artist, with_connection=None, add=True):
265         """get albumartist information from the database.
266         if not in database insert new entry.
267
268         :param sima.lib.meta.Artist artist: artist
269         :param sqlite3.Connection with_connection: SQLite connection
270         """
271         if with_connection:
272             connection = with_connection
273         else:
274             connection = self.get_database_connection()
275         rows = self._get_albumartist(artist, connection)
276         for row in rows:
277             if not with_connection:
278                 connection.close()
279             return row[0]
280         if not add:
281             if not with_connection:
282                 connection.close()
283             return None
284         connection.execute(
285             "INSERT INTO albumartists (name, mbid) VALUES (?, ?)",
286             (artist.name, artist.mbid))
287         connection.commit()
288         rows = self._get_albumartist(artist, connection)
289         for row in rows:
290             if not with_connection:
291                 connection.close()
292             return row[0]
293         if not with_connection:
294             connection.close()
295
296     def _get_artist(self, artist, connection):
297         if artist.mbid:
298             return connection.execute(
299                 "SELECT id FROM artists WHERE mbid = ?",
300                 (artist.mbid,))
301         else:
302             return connection.execute(
303                 "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,))
304
305     def get_artist(self, artist, with_connection=None, add=True):
306         """get artist information from the database.
307         if not in database insert new entry.
308
309         :param sima.lib.meta.Artist artist: artist
310         :param sqlite3.Connection with_connection: SQLite connection
311         """
312         if with_connection:
313             connection = with_connection
314         else:
315             connection = self.get_database_connection()
316         rows = self._get_artist(artist, connection)
317         for row in rows:
318             if not with_connection:
319                 connection.close()
320             return row[0]
321         if not add:
322             if not with_connection:
323                 connection.close()
324             return None
325         connection.execute(
326             "INSERT INTO artists (name, mbid) VALUES (?, ?)",
327             (artist.name, artist.mbid))
328         connection.commit()
329         rows = self._get_artist(artist, connection)
330         for row in rows:
331             if not with_connection:
332                 connection.close()
333             return row[0]
334         if not with_connection:
335             connection.close()
336
337     def get_genre(self, genre, with_connection=None, add=True):
338         """get genre from the database.
339         if not in database insert new entry.
340
341         :param str genre: genre as a string
342         :param sqlite3.Connection with_connection: SQLite connection
343         """
344         if with_connection:
345             connection = with_connection
346         else:
347             connection = self.get_database_connection()
348         rows = connection.execute(
349             "SELECT id FROM genres WHERE name = ?", (genre,))
350         for row in rows:
351             if not with_connection:
352                 connection.close()
353             return row[0]
354         if not add:
355             if not with_connection:
356                 connection.close()
357             return None
358         connection.execute(
359             "INSERT INTO genres (name) VALUES (?)", (genre,))
360         connection.commit()
361         rows = connection.execute(
362             "SELECT id FROM genres WHERE name = ?", (genre,))
363         for row in rows:
364             if not with_connection:
365                 connection.close()
366             return row[0]
367
368     def get_track(self, track, with_connection=None, add=True):
369         """Get a track id from Tracks table, add if not existing,
370         :param sima.lib.track.Track track: track to use
371         :param bool add: add non existing track to database"""
372         if not track.file:
373             raise SimaDBError('Got a track with no file attribute: %r' % track)
374         if with_connection:
375             connection = with_connection
376         else:
377             connection = self.get_database_connection()
378         rows = connection.execute(
379             "SELECT * FROM tracks WHERE file = ?", (track.file,))
380         for row in rows:
381             if not with_connection:
382                 connection.close()
383             return row[0]
384         if not add:  # Not adding non existing track
385             if not with_connection:
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 > ? AND genres.name NOT NULL
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=add)
608         rows = connection.execute(
609             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
610         if not rows.fetchone():
611             if not add:
612                 if not with_connection:
613                     connection.close()
614                 return None
615             connection.execute('INSERT INTO blocklist (track) VALUES (?)',
616                                (track_id,))
617             connection.commit()
618         rows = connection.execute(
619             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
620         bl = rows.fetchone()[0]
621         if not with_connection:
622             connection.close()
623         return bl
624
625     def get_bl_album(self, album, with_connection=None, add=True):
626         """Add an album to blocklist
627         :param sima.lib.meta.Album: Album object to add to blocklist
628         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
629         :param bool add: Default is to add a new record, set to False to fetch associated record"""
630         if with_connection:
631             connection = with_connection
632         else:
633             connection = self.get_database_connection()
634         album_id = self.get_album(album, with_connection=connection, add=add)
635         rows = connection.execute(
636             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
637         if not rows.fetchone():
638             if not add:
639                 if not with_connection:
640                     connection.close()
641                 return None
642             connection.execute('INSERT INTO blocklist (album) VALUES (?)',
643                                (album_id,))
644             connection.commit()
645         rows = connection.execute(
646             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
647         bl = rows.fetchone()[0]
648         if not with_connection:
649             connection.close()
650         return bl
651
652     def get_bl_artist(self, artist, with_connection=None, add=True):
653         """Add an artist to blocklist
654         :param sima.lib.meta.Artist: Artist object to add to blocklist
655         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
656         :param bool add: Default is to add a new record, set to False to fetch associated record"""
657         if with_connection:
658             connection = with_connection
659         else:
660             connection = self.get_database_connection()
661         artist_id = self.get_artist(artist, with_connection=connection, add=add)
662         rows = connection.execute(
663             "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
664         if not rows.fetchone():
665             if not add:
666                 return None
667             connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
668                                (artist_id,))
669             connection.commit()
670         rows = connection.execute(
671             "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
672         bl = rows.fetchone()[0]
673         if not with_connection:
674             connection.close()
675         return bl
676
677     def view_bl(self):
678         connection = self.get_database_connection()
679         connection.row_factory = sqlite3.Row
680         rows = connection.execute("""SELECT artists.name AS artist,
681                artists.mbid AS musicbrainz_artist,
682                albums.name AS album,
683                albums.mbid AS musicbrainz_album,
684                tracks.title AS title,
685                tracks.mbid AS musicbrainz_title,
686                blocklist.id
687                FROM blocklist
688                LEFT OUTER JOIN artists ON blocklist.artist = artists.id
689                LEFT OUTER JOIN albums ON blocklist.album = albums.id
690                LEFT OUTER JOIN tracks ON blocklist.track = tracks.id""")
691         res = [dict(row) for row in rows.fetchall()]
692         connection.close()
693         return res
694
695     def delete_bl(self, track=None, album=None, artist=None):
696         if not (track or album or artist):
697             return
698         connection = self.get_database_connection()
699         blid = None
700         if track:
701             blid = self.get_bl_track(track, with_connection=connection)
702         if album:
703             blid = self.get_bl_album(album, with_connection=connection)
704         if artist:
705             blid = self.get_bl_artist(artist, with_connection=connection)
706         if not blid:
707             return
708         self._remove_blocklist_id(blid, with_connection=connection)
709         connection.close()
710
711
712 # VIM MODLINE
713 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8