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 row in rows.fetchall():
 200             connection.execute(f'DROP TABLE IF EXISTS {row[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         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         return connection.execute(
 264             "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL",
 265             (artist.name,))
 266
 267     def get_albumartist(self, artist, with_connection=None, add=True):
 268         """get albumartist information from the database.
 269         if not in database insert new entry.
 270
 271         :param sima.lib.meta.Artist artist: artist
 272         :param sqlite3.Connection with_connection: SQLite connection
 273         """
 274         if with_connection:
 275             connection = with_connection
 276         else:
 277             connection = self.get_database_connection()
 278         rows = self._get_albumartist(artist, connection)
 279         for row in rows:
 280             if not with_connection:
 281                 connection.close()
 282             return row[0]
 283         if not add:
 284             if not with_connection:
 285                 connection.close()
 286             return None
 287         connection.execute(
 288             "INSERT INTO albumartists (name, mbid) VALUES (?, ?)",
 289             (artist.name, artist.mbid))
 290         connection.commit()
 291         rows = self._get_albumartist(artist, connection)
 292         for row in rows:
 293             if not with_connection:
 294                 connection.close()
 295             return row[0]
 296         if not with_connection:
 297             connection.close()
 298
 299     def _get_artist(self, artist, connection):
 300         if artist.mbid:
 301             return connection.execute(
 302                 "SELECT id FROM artists WHERE mbid = ?",
 303                 (artist.mbid,))
 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
 373         :param sima.lib.track.Track track: track to use
 374         :param bool add: add non existing track to database"""
 375         if not track.file:
 376             raise SimaDBError(f'Got a track with no file attribute: {track}')
 377         if with_connection:
 378             connection = with_connection
 379         else:
 380             connection = self.get_database_connection()
 381         rows = connection.execute(
 382             "SELECT * FROM tracks WHERE file = ?", (track.file,))
 383         for row in rows:
 384             if not with_connection:
 385                 connection.close()
 386             return row[0]
 387         if not add:  # Not adding non existing track
 388             if not with_connection:
 389                 connection.close()
 390             return None
 391         # Get an artist record or None
 392         if track.artist:
 393             art = Artist(name=track.artist, mbid=track.musicbrainz_artistid)
 394             art_id = self.get_artist(art, with_connection=connection)
 395         else:
 396             art_id = None
 397         # Get an albumartist record or None
 398         if track.albumartist:
 399             albart = Artist(name=track.albumartist,
 400                             mbid=track.musicbrainz_albumartistid)
 401             albart_id = self.get_albumartist(albart, with_connection=connection)
 402         else:
 403             albart_id = None
 404         # Get an album record or None
 405         if track.album:
 406             alb = Album(name=track.album, mbid=track.musicbrainz_albumid)
 407             alb_id = self.get_album(alb, with_connection=connection)
 408         else:
 409             alb_id = None
 410         connection.execute(
 411             """INSERT INTO tracks (artist, albumartist, album, title, mbid, file)
 412                 VALUES (?, ?, ?, ?, ?, ?)""",
 413             (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid,
 414                 track.file))
 415         connection.commit()
 416         # Add track id to junction tables
 417         self._add_tracks_genres(track, connection)
 418         rows = connection.execute(
 419             "SELECT id FROM tracks WHERE file = ?", (track.file,))
 420         for row in rows:
 421             if not with_connection:
 422                 connection.close()
 423             return row[0]
 424         if not with_connection:
 425             connection.close()
 426         return None
 427
 428     def _add_tracks_genres(self, track, connection):
 429         if not track.genres:
 430             return
 431         rows = connection.execute(
 432             "SELECT id FROM tracks WHERE file = ?", (track.file,))
 433         trk_id = rows.fetchone()[0]
 434         for genre in track.genres:
 435             # add genre
 436             gen_id = self.get_genre(genre)
 437             connection.execute("""INSERT INTO tracks_genres (track, genre)
 438                     VALUES (?, ?)""", (trk_id, gen_id))
 439
 440     def add_history(self, track, date=None):
 441         """Record last play date of track (ie. not a real play history).
 442
 443         :param sima.lib.track.Track track: track to add to history
 444         :param datetime.datetime date: UTC datetime object (use "datetime.now(timezone.utc)" is not set)"""
 445         if not date:
 446             date = datetime.now(timezone.utc)
 447         connection = self.get_database_connection()
 448         track_id = self.get_track(track, with_connection=connection)
 449         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
 450                                   (track_id,))
 451         if not rows.fetchone():
 452             connection.execute("INSERT INTO history (track) VALUES (?)",
 453                                (track_id,))
 454         connection.execute("UPDATE history SET last_play = ? "
 455                            " WHERE track = ?", (date, track_id,))
 456         connection.commit()
 457         connection.close()
 458
 459     def purge_history(self, duration=__HIST_DURATION__):
 460         """Remove old entries in history
 461
 462         :param int duration: Purge history record older than duration in hours"""
 463         connection = self.get_database_connection()
 464         connection.execute("DELETE FROM history WHERE last_play"
 465                            " < datetime('now', '-%i hours')" % duration)
 466         connection.execute('VACUUM')
 467         connection.commit()
 468         connection.close()
 469
 470     def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__):
 471         """
 472         :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
 473         :param int duration: How long ago to fetch history from (in hours)
 474         """
 475         date = datetime.now(timezone.utc) - timedelta(hours=duration)
 476         connection = self.get_database_connection()
 477         connection.row_factory = sqlite3.Row
 478         rows = connection.execute("""
 479                 SELECT albums.name AS name,
 480                        albums.mbid as mbid,
 481                        artists.name as artist,
 482                        artists.mbid as artist_mbib,
 483                        albumartists.name as albumartist,
 484                        albumartists.mbid as albumartist_mbib
 485                 FROM history
 486                 JOIN tracks ON history.track = tracks.id
 487                 LEFT OUTER JOIN albums ON tracks.album = albums.id
 488                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
 489                 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
 490                 WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
 491                 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
 492         hist = []
 493         for row in rows:
 494             vals = dict(row)
 495             if needle:  # Here use artist instead of albumartist
 496                 if needle != Artist(name=vals.get('artist'),
 497                                     mbid=vals.get('artist_mbib')):
 498                     continue
 499             # Use albumartist / MBIDs if possible to build album artist
 500             if not vals.get('albumartist'):
 501                 vals['albumartist'] = vals.get('artist')
 502             if not vals.get('albumartist_mbib'):
 503                 vals['albumartist_mbib'] = vals.get('artist_mbib')
 504             artist = Artist(name=vals.get('albumartist'),
 505                             mbid=vals.pop('albumartist_mbib'))
 506             album = Album(**vals, Artist=artist)
 507             if hist and hist[-1] == album:
 508                 # remove consecutive dupes
 509                 continue
 510             hist.append(album)
 511         connection.close()
 512         return hist
 513
 514     def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
 515         """Returns a list of Artist objects
 516
 517         :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
 518         :param int duration: How long ago to fetch history from (in hours)
 519         :type needle: sima.lib.meta.Artist or sima.lib.meta.MetaContainer
 520         """
 521         date = datetime.now(timezone.utc) - timedelta(hours=duration)
 522         connection = self.get_database_connection()
 523         connection.row_factory = sqlite3.Row
 524         rows = connection.execute("""
 525                 SELECT artists.name AS name,
 526                        artists.mbid as mbid
 527                 FROM history
 528                 JOIN tracks ON history.track = tracks.id
 529                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
 530                 WHERE history.last_play > ? AND artists.name NOT NULL
 531                 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
 532         last = deque(maxlen=1)
 533         hist = []
 534         for row in rows:
 535             artist = Artist(**row)
 536             if last and last[0] == artist:  # remove consecutive dupes
 537                 continue
 538             last.append(artist)
 539             if needle and isinstance(needle, (Artist, str)):
 540                 if needle == artist:
 541                     hist.append(artist)  # No need to go further
 542                     break
 543                 continue
 544             if needle and getattr(needle, '__contains__'):
 545                 if artist in needle:
 546                     hist.append(artist)  # No need to go further
 547                 continue
 548             hist.append(artist)
 549         connection.close()
 550         return hist
 551
 552     def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
 553         """Returns genre history
 554
 555         :param int duration: How long ago to fetch history from (in hours)
 556         :param int limit: number of genre to fetch
 557         """
 558         date = datetime.now(timezone.utc) - timedelta(hours=duration)
 559         connection = self.get_database_connection()
 560         rows = connection.execute("""
 561                 SELECT genres.name, artists.name
 562                 FROM history
 563                 JOIN tracks ON history.track = tracks.id
 564                 LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id
 565                 LEFT OUTER JOIN artists ON tracks.artist = artists.id
 566                 LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre
 567                 WHERE history.last_play > ? AND genres.name NOT NULL
 568                 ORDER BY history.last_play DESC
 569                 """, (date.isoformat(' '),))
 570         genres = []
 571         for row in rows:
 572             genres.append(row)
 573             if len({g[0] for g in genres}) >= limit:
 574                 break
 575         connection.close()
 576         return genres
 577
 578     def fetch_history(self, artist=None, duration=__HIST_DURATION__):
 579         """Fetches tracks history, more recent first
 580
 581         :param sima.lib.meta.Artist artist: limit history to this artist
 582         :param int duration: How long ago to fetch history from (in hours)
 583         """
 584         date = datetime.now(timezone.utc) - timedelta(hours=duration)
 585         connection = self.get_database_connection()
 586         connection.row_factory = sqlite3.Row
 587         sql = """
 588               SELECT tracks.title, tracks.file, artists.name AS artist,
 589                      albumartists.name AS albumartist,
 590                      artists.mbid as musicbrainz_artistid,
 591                      albums.name AS album,
 592                      albums.mbid AS musicbrainz_albumid,
 593                      tracks.mbid as musicbrainz_trackid
 594               FROM history
 595               JOIN tracks ON history.track = tracks.id
 596               LEFT OUTER JOIN artists ON tracks.artist = artists.id
 597               LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
 598               LEFT OUTER JOIN albums ON tracks.album = albums.id
 599               WHERE history.last_play > ?
 600               """
 601         if artist:
 602             if artist.mbid:
 603                 rows = connection.execute(sql+"""
 604                         AND artists.mbid = ?
 605                         ORDER BY history.last_play DESC""",
 606                                           (date.isoformat(' '), artist.mbid))
 607             else:
 608                 rows = connection.execute(sql+"""
 609                         AND artists.name = ?
 610                         ORDER BY history.last_play DESC""",
 611                                           (date.isoformat(' '), artist.name))
 612         else:
 613             rows = connection.execute(sql+'ORDER BY history.last_play DESC',
 614                                       (date.isoformat(' '),))
 615         hist = []
 616         for row in rows:
 617             hist.append(Track(**row))
 618         connection.close()
 619         return hist
 620
 621     def get_bl_track(self, track, with_connection=None, add=True):
 622         """Add a track to blocklist
 623
 624         :param sima.lib.track.Track track: Track object to add to blocklist
 625         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
 626         :param bool add: Default is to add a new record, set to False to fetch associated record"""
 627         if with_connection:
 628             connection = with_connection
 629         else:
 630             connection = self.get_database_connection()
 631         track_id = self.get_track(track, with_connection=connection, add=add)
 632         rows = connection.execute(
 633             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
 634         if not rows.fetchone():
 635             if not add:
 636                 if not with_connection:
 637                     connection.close()
 638                 return None
 639             connection.execute('INSERT INTO blocklist (track) VALUES (?)',
 640                                (track_id,))
 641             connection.commit()
 642         rows = connection.execute(
 643             "SELECT id FROM blocklist WHERE track = ?", (track_id,))
 644         blt = rows.fetchone()[0]
 645         if not with_connection:
 646             connection.close()
 647         return blt
 648
 649     def get_bl_album(self, album, with_connection=None, add=True):
 650         """Add an album to blocklist
 651
 652         :param sima.lib.meta.Album: Album object to add to blocklist
 653         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
 654         :param bool add: Default is to add a new record, set to False to fetch associated record"""
 655         if with_connection:
 656             connection = with_connection
 657         else:
 658             connection = self.get_database_connection()
 659         album_id = self.get_album(album, with_connection=connection, add=add)
 660         rows = connection.execute(
 661             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
 662         if not rows.fetchone():
 663             if not add:
 664                 if not with_connection:
 665                     connection.close()
 666                 return None
 667             connection.execute('INSERT INTO blocklist (album) VALUES (?)',
 668                                (album_id,))
 669             connection.commit()
 670         rows = connection.execute(
 671             "SELECT id FROM blocklist WHERE album = ?", (album_id,))
 672         blitem = rows.fetchone()[0]
 673         if not with_connection:
 674             connection.close()
 675         return blitem
 676
 677     def get_bl_artist(self, artist, with_connection=None, add=True):
 678         """Add an artist to blocklist
 679
 680         :param sima.lib.meta.Artist: Artist object to add to blocklist
 681         :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
 682         :param bool add: Default is to add a new record, set to False to fetch associated record"""
 683         if with_connection:
 684             connection = with_connection
 685         else:
 686             connection = self.get_database_connection()
 687         artist_id = self.get_artist(artist, with_connection=connection, add=add)
 688         rows = connection.execute(
 689             "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
 690         if not rows.fetchone():
 691             if not add:
 692                 return None
 693             connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
 694                                (artist_id,))
 695             connection.commit()
 696         rows = connection.execute(
 697             "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
 698         blitem = rows.fetchone()[0]
 699         if not with_connection:
 700             connection.close()
 701         return blitem
 702
 703     def view_bl(self):
 704         connection = self.get_database_connection()
 705         connection.row_factory = sqlite3.Row
 706         rows = connection.execute("""SELECT artists.name AS artist,
 707                artists.mbid AS musicbrainz_artist,
 708                albums.name AS album,
 709                albums.mbid AS musicbrainz_album,
 710                tracks.title AS title,
 711                tracks.mbid AS musicbrainz_title,
 712                tracks.file AS file,
 713                blocklist.id
 714                FROM blocklist
 715                LEFT OUTER JOIN artists ON blocklist.artist = artists.id
 716                LEFT OUTER JOIN albums ON blocklist.album = albums.id
 717                LEFT OUTER JOIN tracks ON blocklist.track = tracks.id""")
 718         res = [dict(row) for row in rows.fetchall()]
 719         connection.close()
 720         return res
 721
 722     def delete_bl(self, track=None, album=None, artist=None):
 723         if not (track or album or artist):
 724             return
 725         connection = self.get_database_connection()
 726         blid = None
 727         if track:
 728             blid = self.get_bl_track(track, with_connection=connection)
 729         if album:
 730             blid = self.get_bl_album(album, with_connection=connection)
 731         if artist:
 732             blid = self.get_bl_artist(artist, with_connection=connection)
 733         if not blid:
 734             return
 735         self._remove_blocklist_id(blid, with_connection=connection)
 736         connection.close()
 737
 738
 739 # VIM MODLINE
 740 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8