sima/lib/simadb.py

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