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