sima/lib/simadb.py

   1 # -*- coding: utf-8 -*-
   2 #
   3 # Copyright (c) 2009-2013, 2019-2020 kaliko <kaliko@azylum.org>
   4 # Copyright (c) 2009, Eric Casteleijn <thisfred@gmail.com>
   5 # Copyright (c) 2008 Rick van Hattem
   6 #
   7 #  This file is part of sima
   8 #
   9 #  sima is free software: you can redistribute it and/or modify
  10 #  it under the terms of the GNU General Public License as published by
  11 #  the Free Software Foundation, either version 3 of the License, or
  12 #  (at your option) any later version.
  13 #
  14 #  sima is distributed in the hope that it will be useful,
  15 #  but WITHOUT ANY WARRANTY; without even the implied warranty of
  16 #  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17 #  GNU General Public License for more details.
  18 #
  19 #  You should have received a copy of the GNU General Public License
  20 #  along with sima.  If not, see <http://www.gnu.org/licenses/>.
  21 #
  22 #
  23 """SQlite database library
  24 """
  25
  26 #    DOC:
  27 #    MuscicBrainz ID: <http://musicbrainz.org/doc/MusicBrainzIdentifier>
  28 #    Artists: <http://musicbrainz.org/doc/Artist_Name>
  29 #             <http://musicbrainz.org/doc/Same_Artist_With_Different_Names>
  30
  31 __DB_VERSION__ = 3
  32 __HIST_DURATION__ = int(30 * 24)  # in hours
  33
  34 import sqlite3
  35
  36 from datetime import (datetime, timedelta)
  37 from os.path import dirname, isdir
  38 from os import (access, W_OK, F_OK)
  39
  40
  41 class SimaDBError(Exception):
  42     """
  43     Exceptions.
  44     """
  45
  46
  47 class SimaDBAccessError(SimaDBError):
  48     """Error on accessing DB file"""
  49
  50
  51 class SimaDBNoFile(SimaDBError):
  52     """No DB file present"""
  53
  54
  55 class SimaDB:
  56     "SQLite management"
  57
  58     def __init__(self, db_path=None):
  59         self._db_path = db_path
  60         self.db_path_mod_control()
  61
  62     def db_path_mod_control(self):
  63         """Controls DB path access & write permissions"""
  64         db_path = self._db_path
  65         # Controls directory access
  66         if not isdir(dirname(db_path)):
  67             raise SimaDBAccessError('Not a regular directory: "%s"' %
  68                                     dirname(db_path))
  69         if not access(dirname(db_path), W_OK):
  70             raise SimaDBAccessError('No write access to "%s"' % dirname(db_path))
  71         # Is a file but no write access
  72         if access(db_path, F_OK) and not access(db_path, W_OK | F_OK):
  73             raise SimaDBAccessError('No write access to "%s"' % db_path)
  74         # No file
  75         if not access(db_path, F_OK):
  76             raise SimaDBNoFile('No DB file in "%s"' % db_path)
  77
  78     def close_database_connection(self, connection):
  79         """Close the database connection."""
  80         connection.close()
  81
  82     def get_database_connection(self):
  83         """get database reference"""
  84         connection = sqlite3.connect(
  85             self._db_path, timeout=5.0, isolation_level=None)
  86         #connection.text_factory = str
  87         return connection
  88
  89     def get_artist(self, artist_name, mbid=None,
  90                    with_connection=None, add_not=False):
  91         """get artist information from the database.
  92         if not in database insert new entry."""
  93         if with_connection:
  94             connection = with_connection
  95         else:
  96             connection = self.get_database_connection()
  97         rows = connection.execute(
  98             "SELECT * FROM artists WHERE name = ?", (artist_name,))
  99         for row in rows:
 100             if not with_connection:
 101                 self.close_database_connection(connection)
 102             return row
 103         if add_not:
 104             if not with_connection:
 105                 self.close_database_connection(connection)
 106             return False
 107         connection.execute(
 108             "INSERT INTO artists (name, mbid) VALUES (?, ?)",
 109             (artist_name, mbid))
 110         connection.commit()
 111         rows = connection.execute(
 112             "SELECT * FROM artists WHERE name = ?", (artist_name,))
 113         for row in rows:
 114             if not with_connection:
 115                 self.close_database_connection(connection)
 116             return row
 117         if not with_connection:
 118             self.close_database_connection(connection)
 119
 120     def get_track(self, track, with_connection=None, add_not=False):
 121         """
 122         Get a track from Tracks table, add if not existing,
 123         Attention: use Track() object!!
 124         if not in database insert new entry."""
 125         art = track.artist
 126         nam = track.title
 127         fil = track.file
 128         if with_connection:
 129             connection = with_connection
 130         else:
 131             connection = self.get_database_connection()
 132         art_id = self.get_artist(art, with_connection=connection)[0]
 133         alb_id = self.get_album(track, with_connection=connection)[0]
 134         rows = connection.execute(
 135             "SELECT * FROM tracks WHERE name = ? AND"
 136             " artist = ? AND file = ?", (nam, art_id, fil))
 137         for row in rows:
 138             if not with_connection:
 139                 self.close_database_connection(connection)
 140             return row
 141         if add_not:
 142             return False
 143         connection.execute(
 144             "INSERT INTO tracks (artist, album, name, file) VALUES (?, ?, ?, ?)",
 145             (art_id, alb_id, nam, fil))
 146         connection.commit()
 147         rows = connection.execute(
 148             "SELECT * FROM tracks WHERE name = ? AND"
 149             " artist = ? AND album = ? AND file = ?",
 150             (nam, art_id, alb_id, fil,))
 151         for row in rows:
 152             if not with_connection:
 153                 self.close_database_connection(connection)
 154             return row
 155         if not with_connection:
 156             connection.commit()
 157             self.close_database_connection(connection)
 158
 159     def get_album(self, track, mbid=None,
 160                   with_connection=None, add_not=False):
 161         """
 162         get album information from the database.
 163         if not in database insert new entry.
 164         Attention: use Track|Album object!!
 165         Use AlbumArtist tag if provided, fallback to Album tag
 166         """
 167         if with_connection:
 168             connection = with_connection
 169         else:
 170             connection = self.get_database_connection()
 171         if track.albumartist:
 172             artist = track.albumartist
 173         else:
 174             artist = track.artist
 175         art_id = self.get_artist(artist, with_connection=connection)[0]
 176         album = track.album
 177         rows = connection.execute(
 178             "SELECT * FROM albums WHERE name = ? AND artist = ?",
 179             (album, art_id))
 180         for row in rows:
 181             if not with_connection:
 182                 self.close_database_connection(connection)
 183             return row
 184         if add_not:
 185             return False
 186         connection.execute(
 187             "INSERT INTO albums (name, artist, mbid) VALUES (?, ?, ?)",
 188             (album, art_id, mbid))
 189         connection.commit()
 190         rows = connection.execute(
 191             "SELECT * FROM albums WHERE name = ? AND artist = ?",
 192             (album, art_id))
 193         for row in rows:
 194             if not with_connection:
 195                 self.close_database_connection(connection)
 196             return row
 197         if not with_connection:
 198             self.close_database_connection(connection)
 199
 200     def get_artists(self, with_connection=None):
 201         """Returns all artists in DB"""
 202         if with_connection:
 203             connection = with_connection
 204         else:
 205             connection = self.get_database_connection()
 206         rows = connection.execute("SELECT name FROM artists ORDER BY name")
 207         results = [row for row in rows]
 208         if not with_connection:
 209             self.close_database_connection(connection)
 210         for artist in results:
 211             yield artist
 212
 213     def get_bl_artist(self, artist_name,
 214                       with_connection=None, add_not=None):
 215         """get blacklisted artist information from the database."""
 216         if with_connection:
 217             connection = with_connection
 218         else:
 219             connection = self.get_database_connection()
 220         art = self.get_artist(artist_name, with_connection=connection,
 221                               add_not=add_not)
 222         if not art:
 223             return False
 224         art_id = art[0]
 225         rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
 226                                   (art_id,))
 227         for row in rows:
 228             if not with_connection:
 229                 self.close_database_connection(connection)
 230             return row
 231         if add_not:
 232             if not with_connection:
 233                 self.close_database_connection(connection)
 234             return False
 235         connection.execute("INSERT INTO black_list (artist) VALUES (?)",
 236                            (art_id,))
 237         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
 238                            " WHERE artist = ?", (art_id,))
 239         connection.commit()
 240         rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
 241                                   (art_id,))
 242         for row in rows:
 243             if not with_connection:
 244                 self.close_database_connection(connection)
 245             return row
 246         if not with_connection:
 247             self.close_database_connection(connection)
 248         return False
 249
 250     def get_bl_album(self, track, with_connection=None, add_not=None):
 251         """get blacklisted album information from the database."""
 252         if with_connection:
 253             connection = with_connection
 254         else:
 255             connection = self.get_database_connection()
 256         album = self.get_album(track, with_connection=connection,
 257                                add_not=add_not)
 258         if not album:
 259             return False
 260         alb_id = album[0]
 261         rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
 262                                   (alb_id,))
 263         for row in rows:
 264             if not with_connection:
 265                 self.close_database_connection(connection)
 266             return row
 267         if add_not:
 268             if not with_connection:
 269                 self.close_database_connection(connection)
 270             return False
 271         connection.execute("INSERT INTO black_list (album) VALUES (?)",
 272                            (alb_id,))
 273         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
 274                            " WHERE album = ?", (alb_id,))
 275         connection.commit()
 276         rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
 277                                   (alb_id,))
 278         for row in rows:
 279             if not with_connection:
 280                 self.close_database_connection(connection)
 281             return row
 282         if not with_connection:
 283             self.close_database_connection(connection)
 284         return False
 285
 286     def get_bl_track(self, track, with_connection=None, add_not=None):
 287         """get blacklisted track information from the database."""
 288         if with_connection:
 289             connection = with_connection
 290         else:
 291             connection = self.get_database_connection()
 292         track = self.get_track(track, with_connection=connection,
 293                                add_not=add_not)
 294         if not track:
 295             return False
 296         track_id = track[0]
 297         rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
 298                                   (track_id,))
 299         for row in rows:
 300             if not with_connection:
 301                 self.close_database_connection(connection)
 302             return row
 303         if add_not:
 304             if not with_connection:
 305                 self.close_database_connection(connection)
 306             return False
 307         connection.execute("INSERT INTO black_list (track) VALUES (?)",
 308                            (track_id,))
 309         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
 310                            " WHERE track = ?", (track_id,))
 311         connection.commit()
 312         rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
 313                                   (track_id,))
 314         for row in rows:
 315             if not with_connection:
 316                 self.close_database_connection(connection)
 317             return row
 318         if not with_connection:
 319             self.close_database_connection(connection)
 320         return False
 321
 322     def get_artists_history(self, artists, duration=__HIST_DURATION__):
 323         """
 324         :param list artists: list of object that can evaluate equality with
 325                              artist name, iterable of str or Artist object
 326         """
 327         date = datetime.utcnow() - timedelta(hours=duration)
 328         connection = self.get_database_connection()
 329         rows = connection.execute(
 330             "SELECT arts.name, albs.name, trs.name, trs.file"
 331             " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
 332             " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
 333             " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
 334         for row in rows:
 335             if artists and row[0] not in artists:
 336                 continue
 337             for art in artists:
 338                 if row[0] == art:
 339                     yield art
 340         self.close_database_connection(connection)
 341
 342     def get_history(self, artist=None, artists=None, duration=__HIST_DURATION__):
 343         """Retrieve complete play history, most recent tracks first
 344         artist  : filter history for specific artist
 345         artists : filter history for specific artists list
 346         """ # pylint: disable=C0301
 347         date = datetime.utcnow() - timedelta(hours=duration)
 348         connection = self.get_database_connection()
 349         if artist:
 350             rows = connection.execute(
 351                 "SELECT arts.name, albs.name, trs.name, trs.file, hist.last_play"
 352                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
 353                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
 354                 " AND hist.last_play > ? AND arts.name = ?"
 355                 " ORDER BY hist.last_play DESC", (date.isoformat(' '), artist,))
 356         else:
 357             rows = connection.execute(
 358                 "SELECT arts.name, albs.name, trs.name, trs.file"
 359                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
 360                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
 361                 " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
 362         for row in rows:
 363             if artists and row[0] not in artists:
 364                 continue
 365             yield row
 366         self.close_database_connection(connection)
 367
 368     def get_black_list(self):
 369         """Retrieve complete black list."""
 370         connection = self.get_database_connection()
 371         rows = connection.execute('SELECT black_list.rowid, artists.name'
 372                                   ' FROM artists INNER JOIN black_list'
 373                                   ' ON artists.id = black_list.artist')
 374         yield ('Row ID', 'Actual black listed element', 'Extra information',)
 375         yield ('',)
 376         yield ('Row ID', 'Artist',)
 377         for row in rows:
 378             yield row
 379         rows = connection.execute(
 380                 'SELECT black_list.rowid, albums.name, artists.name'
 381                 ' FROM artists, albums INNER JOIN black_list'
 382                 ' ON albums.id = black_list.album'
 383                 ' WHERE artists.id = albums.artist')
 384         yield ('',)
 385         yield ('Row ID', 'Album', 'Artist name')
 386         for row in rows:
 387             yield row
 388         rows = connection.execute(
 389                 'SELECT black_list.rowid, tracks.name, artists.name'
 390                 ' FROM artists, tracks INNER JOIN black_list'
 391                 ' ON tracks.id = black_list.track'
 392                 ' WHERE tracks.artist = artists.id')
 393         yield ('',)
 394         yield ('Row ID', 'Title', 'Artist name')
 395         for row in rows:
 396             yield row
 397         self.close_database_connection(connection)
 398
 399     def _set_mbid(self, artist_id=None, mbid=None, with_connection=None):
 400         """"""
 401         if with_connection:
 402             connection = with_connection
 403         else:
 404             connection = self.get_database_connection()
 405         connection.execute("UPDATE artists SET mbid = ? WHERE id = ?",
 406                            (mbid, artist_id))
 407         connection.commit()
 408         if not with_connection:
 409             self.close_database_connection(connection)
 410
 411     def _remove_bl(self, rowid):
 412         """Remove bl row id"""
 413         connection = self.get_database_connection()
 414         connection.execute('DELETE FROM black_list'
 415                            ' WHERE black_list.rowid = ?', (rowid,))
 416         connection.commit()
 417         self.close_database_connection(connection)
 418
 419     def add_history(self, track):
 420         """Add to history"""
 421         connection = self.get_database_connection()
 422         track_id = self.get_track(track, with_connection=connection)[0]
 423         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
 424                                   (track_id,))
 425         if not rows.fetchone():
 426             connection.execute("INSERT INTO history (track) VALUES (?)",
 427                                (track_id,))
 428         connection.execute("UPDATE history SET last_play = DATETIME('now') "
 429                            " WHERE track = ?", (track_id,))
 430         connection.commit()
 431         self.close_database_connection(connection)
 432
 433     def _clean_artists_table(self, with_connection=None):
 434         """Clean orphan artists"""
 435         if with_connection:
 436             connection = with_connection
 437         else:
 438             connection = self.get_database_connection()
 439         artists_ids = {row[0] for row in connection.execute(
 440             "SELECT id FROM artists")}
 441         artist_2_artist_ids = {row[0] for row in connection.execute(
 442             "SELECT artist FROM black_list")} | {
 443                 row[0] for row in connection.execute(
 444                     "SELECT artist FROM albums")} | {
 445                         row[0] for row in connection.execute(
 446                             "SELECT artist FROM tracks")}
 447         orphans = [(orphan,) for orphan in artists_ids - artist_2_artist_ids]
 448         connection.executemany('DELETE FROM artists WHERE id = (?);', orphans)
 449         if not with_connection:
 450             connection.commit()
 451             self.close_database_connection(connection)
 452
 453     def _clean_albums_table(self, with_connection=None):
 454         """Clean orphan albums"""
 455         if with_connection:
 456             connection = with_connection
 457         else:
 458             connection = self.get_database_connection()
 459         orphan_black_ids = {row[0] for row in connection.execute(
 460             """SELECT albums.id FROM albums
 461             LEFT JOIN black_list ON albums.id = black_list.album
 462             WHERE ( black_list.album IS NULL )""")}
 463         orphan_tracks_ids = {row[0] for row in connection.execute(
 464             """SELECT albums.id FROM albums
 465             LEFT JOIN tracks ON albums.id = tracks.album
 466             WHERE tracks.album IS NULL""")}
 467         orphans = [(orphan,) for orphan in orphan_black_ids & orphan_tracks_ids]
 468         connection.executemany('DELETE FROM albums WHERE id = (?);', orphans)
 469         if not with_connection:
 470             connection.commit()
 471             self.close_database_connection(connection)
 472
 473     def _clean_tracks_table(self, with_connection=None):
 474         """Clean orphan tracks"""
 475         if with_connection:
 476             connection = with_connection
 477         else:
 478             connection = self.get_database_connection()
 479         hist_orphan_ids = {row[0] for row in connection.execute(
 480             """SELECT tracks.id FROM tracks
 481             LEFT JOIN history ON tracks.id = history.track
 482             WHERE history.track IS NULL""")}
 483         black_list_orphan_ids = {row[0] for row in connection.execute(
 484             """SELECT tracks.id FROM tracks
 485             LEFT JOIN black_list ON tracks.id = black_list.track
 486             WHERE black_list.track IS NULL""")}
 487         orphans = [(orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids]
 488         connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans)
 489         if not with_connection:
 490             connection.commit()
 491             self.close_database_connection(connection)
 492
 493     def clean_database(self, with_connection=None):
 494         """Wrapper around _clean_* methods"""
 495         if with_connection:
 496             connection = with_connection
 497         else:
 498             connection = self.get_database_connection()
 499         self._clean_tracks_table(with_connection=connection)
 500         self._clean_albums_table(with_connection=connection)
 501         self._clean_artists_table(with_connection=connection)
 502         connection.execute("VACUUM")
 503         if not with_connection:
 504             connection.commit()
 505             self.close_database_connection(connection)
 506
 507     def purge_history(self, duration=__HIST_DURATION__):
 508         """Remove old entries in history"""
 509         connection = self.get_database_connection()
 510         connection.execute("DELETE FROM history WHERE last_play"
 511                            " < datetime('now', '-%i hours')" % duration)
 512         connection.commit()
 513         self.close_database_connection(connection)
 514
 515     def _set_dbversion(self):
 516         """Add db version"""
 517         connection = self.get_database_connection()
 518         connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)',
 519                            (__DB_VERSION__, 'Sima DB'))
 520         connection.commit()
 521         self.close_database_connection(connection)
 522
 523     def create_db(self):
 524         """ Set up a database
 525         """
 526         connection = self.get_database_connection()
 527         connection.execute(
 528             'CREATE TABLE IF NOT EXISTS db_info'
 529             ' (version INTEGER, name CHAR(36))')
 530         connection.execute(
 531             'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name'
 532             ' VARCHAR(100), mbid CHAR(36))')
 533         connection.execute(
 534             'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,'
 535             ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))')
 536         connection.execute(
 537             'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY,'
 538             ' name VARCHAR(100), artist INTEGER, album INTEGER,'
 539             ' file VARCHAR(500), mbid CHAR(36))')
 540         connection.execute(
 541             'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,'
 542             ' album INTEGER, track INTEGER, updated DATE)')
 543         connection.execute(
 544             'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
 545             ' track integer)')
 546         connection.commit()
 547         self.close_database_connection(connection)
 548         self._set_dbversion()
 549
 550
 551 # VIM MODLINE
 552 # vim: ai ts=4 sw=4 sts=4 expandtab