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__ = 2
  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 SimaDBUpgradeError(SimaDBError):
  60     """Error on upgrade"""
  61     pass
  62
  63
  64 class SimaDB(object):
  65     "SQLite management"
  66
  67     def __init__(self, db_path=None):
  68         self._db_path = db_path
  69         self.db_path_mod_control()
  70
  71     def db_path_mod_control(self):
  72         """Controls DB path access & write permissions"""
  73         db_path = self._db_path
  74         # Controls directory access
  75         if not isdir(dirname(db_path)):
  76             raise SimaDBAccessError('Not a regular directory: "%s"' %
  77                     dirname(db_path))
  78         if not access(dirname(db_path), W_OK):
  79             raise SimaDBAccessError('No write access to "%s"' % dirname(db_path))
  80         # Is a file but no write access
  81         if access(db_path, F_OK) and not access(db_path, W_OK | F_OK):
  82             raise SimaDBAccessError('No write access to "%s"' % db_path)
  83         # No file
  84         if not access(db_path, F_OK):
  85             raise SimaDBNoFile('No DB file in "%s"' % db_path)
  86
  87     def close_database_connection(self, connection):
  88         """Close the database connection."""
  89         connection.close()
  90
  91     def get_database_connection(self):
  92         """get database reference"""
  93         connection = sqlite3.connect(
  94             self._db_path, timeout=5.0, isolation_level="immediate")
  95         #connection.text_factory = str
  96         return connection
  97
  98     def upgrade(self):
  99         """upgrade DB from previous versions"""
 100         connection = self.get_database_connection()
 101         try:
 102             connection.execute('SELECT version FROM db_info')
 103         except Exception as err:
 104             if err.__str__() == "no such table: db_info":
 105                 # db version < 2 (MPD_sima 0.6)
 106                 copyfile(self._db_path, self._db_path + '.0.6')
 107                 connection.execute('DROP TABLE tracks')
 108                 connection.commit()
 109                 self.create_db()
 110             else:
 111                 raise SimaDBUpgradeError('Could not upgrade database: "%s"' %
 112                         err)
 113         self.close_database_connection(connection)
 114
 115     def get_artist(self, artist_name, mbid=None,
 116             with_connection=None, add_not=False):
 117         """get artist information from the database.
 118         if not in database insert new entry."""
 119         if with_connection:
 120             connection = with_connection
 121         else:
 122             connection = self.get_database_connection()
 123         rows = connection.execute(
 124             "SELECT * FROM artists WHERE name = ?", (artist_name,))
 125         for row in rows:
 126             if not with_connection:
 127                 self.close_database_connection(connection)
 128             return row
 129         if add_not:
 130             if not with_connection:
 131                 self.close_database_connection(connection)
 132             return False
 133         connection.execute(
 134             "INSERT INTO artists (name, mbid) VALUES (?, ?)",
 135             (artist_name, mbid))
 136         connection.commit()
 137         rows = connection.execute(
 138             "SELECT * FROM artists WHERE name = ?", (artist_name,))
 139         for row in rows:
 140             if not with_connection:
 141                 self.close_database_connection(connection)
 142             return row
 143         if not with_connection:
 144             self.close_database_connection(connection)
 145
 146     def get_track(self, track, with_connection=None, add_not=False):
 147         """
 148         Get a track from Tracks table, add if not existing,
 149         Attention: use Track() object!!
 150         if not in database insert new entry."""
 151         art = track.artist
 152         nam = track.title
 153         fil = track.file
 154         if with_connection:
 155             connection = with_connection
 156         else:
 157             connection = self.get_database_connection()
 158         art_id = self.get_artist(art, with_connection=connection)[0]
 159         alb_id = self.get_album(track, with_connection=connection)[0]
 160         rows = connection.execute(
 161             "SELECT * FROM tracks WHERE name = ? AND"
 162             " artist = ? AND file = ?", (nam, art_id, fil))
 163         for row in rows:
 164             if not with_connection:
 165                 self.close_database_connection(connection)
 166             return row
 167         if add_not:
 168             return False
 169         connection.execute(
 170             "INSERT INTO tracks (artist, album, name, file) VALUES (?, ?, ?, ?)",
 171             (art_id, alb_id, nam, fil))
 172         connection.commit()
 173         rows = connection.execute(
 174             "SELECT * FROM tracks WHERE name = ? AND"
 175             " artist = ? AND album = ? AND file = ?",
 176             (nam, art_id, alb_id, fil,))
 177         for row in rows:
 178             if not with_connection:
 179                 self.close_database_connection(connection)
 180             return row
 181         if not with_connection:
 182             connection.commit()
 183             self.close_database_connection(connection)
 184
 185     def get_album(self, track, mbid=None,
 186             with_connection=None, add_not=False):
 187         """
 188         get album information from the database.
 189         if not in database insert new entry.
 190         Attention: use Track|Album object!!
 191         Use AlbumArtist tag if provided, fallback to Album tag
 192         """
 193         if with_connection:
 194             connection = with_connection
 195         else:
 196             connection = self.get_database_connection()
 197         if track.albumartist:
 198             artist = track.albumartist
 199         else:
 200             artist = track.artist
 201         art_id = self.get_artist(artist, with_connection=connection)[0]
 202         album = track.album
 203         rows = connection.execute(
 204             "SELECT * FROM albums WHERE name = ? AND artist = ?",
 205             (album, art_id))
 206         for row in rows:
 207             if not with_connection:
 208                 self.close_database_connection(connection)
 209             return row
 210         if add_not:
 211             return False
 212         connection.execute(
 213             "INSERT INTO albums (name, artist, mbid) VALUES (?, ?, ?)",
 214             (album, art_id, mbid))
 215         connection.commit()
 216         rows = connection.execute(
 217             "SELECT * FROM albums WHERE name = ? AND artist = ?",
 218             (album, art_id))
 219         for row in rows:
 220             if not with_connection:
 221                 self.close_database_connection(connection)
 222             return row
 223         if not with_connection:
 224             self.close_database_connection(connection)
 225
 226     def get_artists(self, with_connection=None):
 227         """Returns all artists in DB"""
 228         if with_connection:
 229             connection = with_connection
 230         else:
 231             connection = self.get_database_connection()
 232         rows = connection.execute("SELECT name FROM artists ORDER BY name")
 233         results = [row for row in rows]
 234         if not with_connection:
 235             self.close_database_connection(connection)
 236         for artist in results:
 237             yield artist
 238
 239     def get_bl_artist(self, artist_name,
 240             with_connection=None, add_not=None):
 241         """get blacklisted artist information from the database."""
 242         if with_connection:
 243             connection = with_connection
 244         else:
 245             connection = self.get_database_connection()
 246         art = self.get_artist(artist_name,
 247                 with_connection=connection, add_not=add_not)
 248         if not art:
 249             return False
 250         art_id = art[0]
 251         rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
 252                 (art_id,))
 253         for row in rows:
 254             if not with_connection:
 255                 self.close_database_connection(connection)
 256             return row
 257         if add_not:
 258             if not with_connection:
 259                 self.close_database_connection(connection)
 260             return False
 261         connection.execute("INSERT INTO black_list (artist) VALUES (?)",
 262                 (art_id,))
 263         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
 264                 " WHERE artist = ?", (art_id,))
 265         connection.commit()
 266         rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
 267                 (art_id,))
 268         for row in rows:
 269             if not with_connection:
 270                 self.close_database_connection(connection)
 271             return row
 272         if not with_connection:
 273             self.close_database_connection(connection)
 274         return False
 275
 276     def get_bl_album(self, track,
 277             with_connection=None, add_not=None):
 278         """get blacklisted album information from the database."""
 279         if with_connection:
 280             connection = with_connection
 281         else:
 282             connection = self.get_database_connection()
 283         album = self.get_album(track,
 284                 with_connection=connection, add_not=add_not)
 285         if not album:
 286             return False
 287         alb_id = album[0]
 288         rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
 289                 (alb_id,))
 290         for row in rows:
 291             if not with_connection:
 292                 self.close_database_connection(connection)
 293             return row
 294         if add_not:
 295             if not with_connection:
 296                 self.close_database_connection(connection)
 297             return False
 298         connection.execute("INSERT INTO black_list (album) VALUES (?)",
 299                 (alb_id,))
 300         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
 301                 " WHERE album = ?", (alb_id,))
 302         connection.commit()
 303         rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
 304                 (alb_id,))
 305         for row in rows:
 306             if not with_connection:
 307                 self.close_database_connection(connection)
 308             return row
 309         if not with_connection:
 310             self.close_database_connection(connection)
 311         return False
 312
 313     def get_bl_track(self, track, with_connection=None, add_not=None):
 314         """get blacklisted track information from the database."""
 315         if with_connection:
 316             connection = with_connection
 317         else:
 318             connection = self.get_database_connection()
 319         track = self.get_track(track,
 320                 with_connection=connection, add_not=add_not)
 321         if not track:
 322             return False
 323         track_id = track[0]
 324         rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
 325                 (track_id,))
 326         for row in rows:
 327             if not with_connection:
 328                 self.close_database_connection(connection)
 329             return row
 330         if add_not:
 331             if not with_connection:
 332                 self.close_database_connection(connection)
 333             return False
 334         connection.execute("INSERT INTO black_list (track) VALUES (?)",
 335                 (track_id,))
 336         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
 337                 " WHERE track = ?", (track_id,))
 338         connection.commit()
 339         rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
 340                 (track_id,))
 341         for row in rows:
 342             if not with_connection:
 343                 self.close_database_connection(connection)
 344             return row
 345         if not with_connection:
 346             self.close_database_connection(connection)
 347         return False
 348
 349     def get_artists_history(self, artists, duration=__HIST_DURATION__):
 350         """
 351         """
 352         date = datetime.utcnow() - timedelta(hours=duration)
 353         connection = self.get_database_connection()
 354         rows = connection.execute(
 355             "SELECT arts.name, albs.name, trs.name, trs.file"
 356             " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
 357             " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
 358             " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
 359         for row in rows:
 360             if artists and row[0] not in artists:
 361                 continue
 362             for art in artists:
 363                 if row[0] == art:
 364                     yield art
 365         self.close_database_connection(connection)
 366
 367     def get_history(self, artist=None, artists=None, duration=__HIST_DURATION__):
 368         """Retrieve complete play history, most recent tracks first
 369         artist  : filter history for specific artist
 370         artists : filter history for specific artists list
 371         """ # pylint: disable=C0301
 372         date = datetime.utcnow() - timedelta(hours=duration)
 373         connection = self.get_database_connection()
 374         if artist:
 375             rows = connection.execute(
 376                 "SELECT arts.name, albs.name, trs.name, trs.file, hist.last_play"
 377                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
 378                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
 379                 " AND hist.last_play > ? AND arts.name = ?"
 380                 " ORDER BY hist.last_play DESC", (date.isoformat(' '), artist,))
 381         else:
 382             rows = connection.execute(
 383                 "SELECT arts.name, albs.name, trs.name, trs.file"
 384                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
 385                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
 386                 " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
 387         for row in rows:
 388             if artists and row[0] not in artists:
 389                 continue
 390             yield row
 391         self.close_database_connection(connection)
 392
 393     def get_black_list(self):
 394         """Retrieve complete black list."""
 395         connection = self.get_database_connection()
 396         rows = connection.execute('SELECT black_list.rowid, artists.name'
 397                 ' FROM artists INNER JOIN black_list'
 398                 ' ON artists.id = black_list.artist')
 399         yield ('Row ID', 'Actual black listed element', 'Extra information',)
 400         yield ('',)
 401         yield ('Row ID', 'Artist',)
 402         for row in rows:
 403             yield row
 404         rows = connection.execute(
 405                 'SELECT black_list.rowid, albums.name, artists.name'
 406                 ' FROM artists, albums INNER JOIN black_list'
 407                 ' ON albums.id = black_list.album'
 408                 ' WHERE artists.id = albums.artist')
 409         yield ('',)
 410         yield ('Row ID', 'Album', 'Artist name')
 411         for row in rows:
 412             yield row
 413         rows = connection.execute(
 414                 'SELECT black_list.rowid, tracks.name, artists.name'
 415                 ' FROM artists, tracks INNER JOIN black_list'
 416                 ' ON tracks.id = black_list.track'
 417                 ' WHERE tracks.artist = artists.id')
 418         yield ('',)
 419         yield ('Row ID', 'Title', 'Artist name')
 420         for row in rows:
 421             yield row
 422         self.close_database_connection(connection)
 423
 424     def _set_mbid(self, artist_id=None, mbid=None, with_connection=None):
 425         """get artist information from the database.
 426         if not in database insert new entry."""
 427         if with_connection:
 428             connection = with_connection
 429         else:
 430             connection = self.get_database_connection()
 431         connection.execute("UPDATE artists SET mbid = ? WHERE id = ?",
 432             (mbid, artist_id))
 433         connection.commit()
 434         if not with_connection:
 435             self.close_database_connection(connection)
 436
 437     def _get_similar_artists_from_db(self, artist_id):
 438         connection = self.get_database_connection()
 439         results = [row for row in connection.execute(
 440             "SELECT match, name FROM usr_artist_2_artist INNER JOIN"
 441             " artists ON usr_artist_2_artist.artist2 = artists.id WHERE"
 442             " usr_artist_2_artist.artist1 = ? ORDER BY match DESC;",
 443             (artist_id,))]
 444         self.close_database_connection(connection)
 445         for score, artist in results:
 446             yield {'score': score, 'artist': artist}
 447
 448     def _get_reverse_similar_artists_from_db(self, artist_id):
 449         connection = self.get_database_connection()
 450         results = [row for row in connection.execute(
 451             "SELECT name FROM usr_artist_2_artist INNER JOIN"
 452             " artists ON usr_artist_2_artist.artist1 = artists.id WHERE"
 453             " usr_artist_2_artist.artist2 = ?;",
 454             (artist_id,))]
 455         self.close_database_connection(connection)
 456         for artist in results:
 457             yield artist[0]
 458
 459     def get_similar_artists(self, artist_name):
 460         """get similar artists from the database sorted by descending
 461         match score"""
 462         artist_id = self.get_artist(artist_name)[0]
 463         for result in self._get_similar_artists_from_db(artist_id):
 464             yield result
 465
 466     def _get_artist_match(self, artist1, artist2, with_connection=None):
 467         """get artist match score from database"""
 468         if with_connection:
 469             connection = with_connection
 470         else:
 471             connection = self.get_database_connection()
 472         rows = connection.execute(
 473             "SELECT match FROM usr_artist_2_artist WHERE artist1 = ?"
 474             " AND artist2 = ?",
 475             (artist1, artist2))
 476         result = 0
 477         for row in rows:
 478             result = row[0]
 479             break
 480         if not with_connection:
 481             self.close_database_connection(connection)
 482         return result
 483
 484     def _remove_relation_between_2_artist(self, artist1, artist2):
 485         """Remove a similarity relation"""
 486         connection = self.get_database_connection()
 487         connection.execute(
 488             'DELETE FROM usr_artist_2_artist'
 489             ' WHERE artist1 = ? AND artist2 = ?;',
 490             (artist1, artist2))
 491         self.clean_database(with_connection=connection)
 492         self._update_artist(artist_id=artist1, with_connection=connection)
 493         connection.commit()
 494         self.close_database_connection(connection)
 495
 496     def _remove_artist(self, artist_id, deep=False, with_connection=None):
 497         """Remove all artist1 reference"""
 498         if with_connection:
 499             connection = with_connection
 500         else:
 501             connection = self.get_database_connection()
 502         if deep:
 503             connection.execute(
 504                 'DELETE FROM usr_artist_2_artist'
 505                 ' WHERE artist1 = ? OR artist2 = ?;',
 506                 (artist_id, artist_id))
 507             connection.execute(
 508                 'DELETE FROM artists WHERE id = ?;',
 509                 (artist_id,))
 510         else:
 511             connection.execute(
 512                 'DELETE FROM usr_artist_2_artist WHERE artist1 = ?;',
 513                 (artist_id,))
 514         self.clean_database(with_connection=connection)
 515         self._update_artist(artist_id=artist_id, with_connection=connection)
 516         if not with_connection:
 517             connection.commit()
 518             self.close_database_connection(connection)
 519
 520     def _remove_bl(self, rowid):
 521         """Remove bl row id"""
 522         connection = self.get_database_connection()
 523         connection.execute('DELETE FROM black_list'
 524                 ' WHERE black_list.rowid = ?', (rowid,))
 525         connection.commit()
 526         self.close_database_connection(connection)
 527
 528     def _insert_artist_match(
 529         self, artist1, artist2, match, with_connection=None):
 530         """write match score to the database.
 531         Does not update time stamp in table artist/*_updated"""
 532         if with_connection:
 533             connection = with_connection
 534         else:
 535             connection = self.get_database_connection()
 536         connection.execute(
 537             "INSERT INTO usr_artist_2_artist (artist1, artist2, match) VALUES"
 538             " (?, ?, ?)",
 539             (artist1, artist2, match))
 540         if not with_connection:
 541             connection.commit()
 542             self.close_database_connection(connection)
 543
 544     def add_history(self, track):
 545         """Add to history"""
 546         connection = self.get_database_connection()
 547         track_id = self.get_track(track, with_connection=connection)[0]
 548         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
 549                                   (track_id,))
 550         if not rows.fetchone():
 551             connection.execute("INSERT INTO history (track) VALUES (?)",
 552                                (track_id,))
 553         connection.execute("UPDATE history SET last_play = DATETIME('now') "
 554                 " WHERE track = ?", (track_id,))
 555         connection.commit()
 556         self.close_database_connection(connection)
 557
 558     def _update_artist(self, artist_id, with_connection=None):
 559         """write artist information to the database"""
 560         if with_connection:
 561             connection = with_connection
 562         else:
 563             connection = self.get_database_connection()
 564         connection.execute(
 565             "UPDATE artists SET usr_updated = DATETIME('now') WHERE id = ?",
 566             (artist_id,))
 567         if not with_connection:
 568             connection.commit()
 569             self.close_database_connection(connection)
 570
 571     def _update_artist_match(
 572         self, artist1, artist2, match, with_connection=None):
 573         """write match score to the database"""
 574         if with_connection:
 575             connection = with_connection
 576         else:
 577             connection = self.get_database_connection()
 578         connection.execute(
 579             "UPDATE usr_artist_2_artist SET match = ? WHERE artist1 = ? AND"
 580             " artist2 = ?",
 581             (match, artist1, artist2))
 582         if not with_connection:
 583             connection.commit()
 584             self.close_database_connection(connection)
 585
 586     def _update_similar_artists(self, artist, similar_artists):
 587         """write user similar artist information to the database
 588         """
 589         # DOC:   similar_artists = list([{'score': match, 'artist': name}])
 590         #
 591         connection = self.get_database_connection()
 592         artist_id = self.get_artist(artist, with_connection=connection)[0]
 593         for artist in similar_artists:
 594             id2 = self.get_artist(
 595                 artist['artist'], with_connection=connection)[0]
 596             if self._get_artist_match(
 597                 artist_id, id2, with_connection=connection):
 598                 self._update_artist_match(
 599                     artist_id, id2, artist['score'],
 600                     with_connection=connection)
 601                 continue
 602             self._insert_artist_match(
 603                 artist_id, id2, artist['score'],
 604                 with_connection=connection)
 605         self._update_artist(artist_id, with_connection=connection)
 606         connection.commit()
 607         self.close_database_connection(connection)
 608
 609     def _clean_artists_table(self, with_connection=None):
 610         """Clean orphan artists"""
 611         if with_connection:
 612             connection = with_connection
 613         else:
 614             connection = self.get_database_connection()
 615         artists_ids = set([row[0] for row in connection.execute(
 616             "SELECT id FROM artists")])
 617         artist_2_artist_ids = set([row[0] for row in connection.execute(
 618             "SELECT artist1 FROM usr_artist_2_artist")] +
 619             [row[0] for row in connection.execute(
 620             "SELECT artist2 FROM usr_artist_2_artist")] +
 621             [row[0] for row in connection.execute(
 622             "SELECT artist FROM black_list")] +
 623             [row[0] for row in connection.execute(
 624             "SELECT artist FROM albums")] +
 625             [row[0] for row in connection.execute(
 626             "SELECT artist FROM tracks")])
 627         orphans = [(orphan,) for orphan in artists_ids - artist_2_artist_ids]
 628         connection.executemany('DELETE FROM artists WHERE id = (?);', orphans)
 629         if not with_connection:
 630             connection.commit()
 631             self.close_database_connection(connection)
 632
 633     def _clean_albums_table(self, with_connection=None):
 634         """Clean orphan albums"""
 635         if with_connection:
 636             connection = with_connection
 637         else:
 638             connection = self.get_database_connection()
 639         orphan_black_ids = set([row[0] for row in connection.execute(
 640             """SELECT albums.id FROM albums
 641             LEFT JOIN black_list ON albums.id = black_list.album
 642             WHERE ( black_list.album IS NULL )""")])
 643         orphan_tracks_ids = set([row[0] for row in connection.execute(
 644             """SELECT albums.id FROM albums
 645             LEFT JOIN tracks ON albums.id = tracks.album
 646             WHERE tracks.album IS NULL""")])
 647         orphans = [(orphan,) for orphan in orphan_black_ids & orphan_tracks_ids]
 648         connection.executemany('DELETE FROM albums WHERE id = (?);', orphans)
 649         if not with_connection:
 650             connection.commit()
 651             self.close_database_connection(connection)
 652
 653     def _clean_tracks_table(self, with_connection=None):
 654         """Clean orphan tracks"""
 655         if with_connection:
 656             connection = with_connection
 657         else:
 658             connection = self.get_database_connection()
 659         hist_orphan_ids = set([row[0] for row in connection.execute(
 660             """SELECT tracks.id FROM tracks
 661             LEFT JOIN history ON tracks.id = history.track
 662             WHERE history.track IS NULL""")])
 663         black_list_orphan_ids = set([row[0] for row in connection.execute(
 664             """SELECT tracks.id FROM tracks
 665             LEFT JOIN black_list ON tracks.id = black_list.track
 666             WHERE black_list.track IS NULL""")])
 667         orphans = [(orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids]
 668         connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans)
 669         if not with_connection:
 670             connection.commit()
 671             self.close_database_connection(connection)
 672
 673     def clean_database(self, with_connection=None):
 674         """Wrapper around _clean_* methods"""
 675         if with_connection:
 676             connection = with_connection
 677         else:
 678             connection = self.get_database_connection()
 679         self._clean_tracks_table(with_connection=connection)
 680         self._clean_albums_table(with_connection=connection)
 681         self._clean_artists_table(with_connection=connection)
 682         connection.execute("VACUUM")
 683         if not with_connection:
 684             connection.commit()
 685             self.close_database_connection(connection)
 686
 687     def purge_history(self, duration=__HIST_DURATION__):
 688         """Remove old entries in history"""
 689         connection = self.get_database_connection()
 690         connection.execute("DELETE FROM history WHERE last_play"
 691                 " < datetime('now', '-%i hours')" % duration)
 692         connection.commit()
 693         self.close_database_connection(connection)
 694
 695     def _set_dbversion(self):
 696         """Add db version"""
 697         connection = self.get_database_connection()
 698         connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)',
 699                 (__DB_VERSION__, 'Sima DB'))
 700         connection.commit()
 701         self.close_database_connection(connection)
 702
 703     def create_db(self):
 704         """ Set up a database for the artist similarity scores
 705         """
 706         connection = self.get_database_connection()
 707         connection.execute(
 708             'CREATE TABLE IF NOT EXISTS db_info'
 709             ' (version INTEGER, name CHAR(36))')
 710         connection.execute(
 711             'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name'
 712             ' VARCHAR(100), mbid CHAR(36), lfm_updated DATE, usr_updated DATE)')
 713         connection.execute(
 714             'CREATE TABLE IF NOT EXISTS usr_artist_2_artist (artist1 INTEGER,'
 715             ' artist2 INTEGER, match INTEGER)')
 716         connection.execute(
 717             'CREATE TABLE IF NOT EXISTS lfm_artist_2_artist (artist1 INTEGER,'
 718             ' artist2 INTEGER, match INTEGER)')
 719         connection.execute(
 720             'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,'
 721             ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))')
 722         connection.execute(
 723             'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY,'
 724             ' name VARCHAR(100), artist INTEGER, album INTEGER,'
 725             ' file VARCHAR(500), mbid CHAR(36))')
 726         connection.execute(
 727             'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,'
 728             ' album INTEGER, track INTEGER, updated DATE)')
 729         connection.execute(
 730             'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
 731             ' track integer)')
 732         connection.execute(
 733           "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)")
 734         connection.execute(
 735           "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)")
 736         connection.execute(
 737         "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)")
 738         connection.execute(
 739         "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)")
 740         connection.commit()
 741         self.close_database_connection(connection)
 742         self._set_dbversion()
 743
 744
 745 def main():
 746     db = SimaDB(db_path='/tmp/sima.db')
 747     db.purge_history(int(4))
 748     db.clean_database()
 749
 750
 751 # Script starts here
 752 if __name__ == '__main__':
 753     main()
 754
 755 # VIM MODLINE
 756 # vim: ai ts=4 sw=4 sts=4 expandtab