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