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