sima/lib/simadb.py

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