sima/lib/simadb.py

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