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