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