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