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