sima/lib/simadb.py

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