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