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