]> kaliko git repositories - mpd-sima.git/blob - sima/lib/simadb.py
Aesthetics changes
[mpd-sima.git] / 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() object!!
191         Use AlbumArtist tag is 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_history(self, artist=None, artists=None, duration=__HIST_DURATION__):
350         """Retrieve complete play history, most recent tracks first
351         artist  : filter history for specific artist
352         artists : filter history for specific artists list
353         """ # pylint: disable=C0301
354         date = datetime.utcnow() - timedelta(hours=duration)
355         connection = self.get_database_connection()
356         if artist:
357             rows = connection.execute(
358                 "SELECT arts.name, albs.name, trs.name, trs.file, hist.last_play"
359                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
360                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
361                 " AND hist.last_play > ? AND arts.name = ?"
362                 " ORDER BY hist.last_play DESC", (date.isoformat(' '), artist,))
363         else:
364             rows = connection.execute(
365                 "SELECT arts.name, albs.name, trs.name, trs.file"
366                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
367                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
368                 " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
369         for row in rows:
370             if artists and row[0] not in artists:
371                 continue
372             yield row
373         self.close_database_connection(connection)
374
375     def get_black_list(self):
376         """Retrieve complete black list."""
377         connection = self.get_database_connection()
378         rows = connection.execute('SELECT black_list.rowid, artists.name'
379                 ' FROM artists INNER JOIN black_list'
380                 ' ON artists.id = black_list.artist')
381         yield ('Row ID', 'Actual black listed element', 'Extra information',)
382         yield ('',)
383         yield ('Row ID', 'Artist',)
384         for row in rows:
385             yield row
386         rows = connection.execute(
387                 'SELECT black_list.rowid, albums.name, artists.name'
388                 ' FROM artists, albums INNER JOIN black_list'
389                 ' ON albums.id = black_list.album'
390                 ' WHERE artists.id = albums.artist')
391         yield ('',)
392         yield ('Row ID', 'Album', 'Artist name')
393         for row in rows:
394             yield row
395         rows = connection.execute(
396                 'SELECT black_list.rowid, tracks.name, artists.name'
397                 ' FROM artists, tracks INNER JOIN black_list'
398                 ' ON tracks.id = black_list.track'
399                 ' WHERE tracks.artist = artists.id')
400         yield ('',)
401         yield ('Row ID', 'Title', 'Artist name')
402         for row in rows:
403             yield row
404         self.close_database_connection(connection)
405
406     def _set_mbid(self, artist_id=None, mbid=None, with_connection=None):
407         """get artist information from the database.
408         if not in database insert new entry."""
409         if with_connection:
410             connection = with_connection
411         else:
412             connection = self.get_database_connection()
413         connection.execute("UPDATE artists SET mbid = ? WHERE id = ?",
414             (mbid, artist_id))
415         connection.commit()
416         if not with_connection:
417             self.close_database_connection(connection)
418
419     def _get_similar_artists_from_db(self, artist_id):
420         connection = self.get_database_connection()
421         results = [row for row in connection.execute(
422             "SELECT match, name FROM usr_artist_2_artist INNER JOIN"
423             " artists ON usr_artist_2_artist.artist2 = artists.id WHERE"
424             " usr_artist_2_artist.artist1 = ? ORDER BY match DESC;",
425             (artist_id,))]
426         self.close_database_connection(connection)
427         for score, artist in results:
428             yield {'score': score, 'artist': artist}
429
430     def _get_reverse_similar_artists_from_db(self, artist_id):
431         connection = self.get_database_connection()
432         results = [row for row in connection.execute(
433             "SELECT name FROM usr_artist_2_artist INNER JOIN"
434             " artists ON usr_artist_2_artist.artist1 = artists.id WHERE"
435             " usr_artist_2_artist.artist2 = ?;",
436             (artist_id,))]
437         self.close_database_connection(connection)
438         for artist in results:
439             yield artist[0]
440
441     def get_similar_artists(self, artist_name):
442         """get similar artists from the database sorted by descending
443         match score"""
444         artist_id = self.get_artist(artist_name)[0]
445         for result in self._get_similar_artists_from_db(artist_id):
446             yield result
447
448     def _get_artist_match(self, artist1, artist2, with_connection=None):
449         """get artist match score from database"""
450         if with_connection:
451             connection = with_connection
452         else:
453             connection = self.get_database_connection()
454         rows = connection.execute(
455             "SELECT match FROM usr_artist_2_artist WHERE artist1 = ?"
456             " AND artist2 = ?",
457             (artist1, artist2))
458         result = 0
459         for row in rows:
460             result = row[0]
461             break
462         if not with_connection:
463             self.close_database_connection(connection)
464         return result
465
466     def _remove_relation_between_2_artist(self, artist1, artist2):
467         """Remove a similarity relation"""
468         connection = self.get_database_connection()
469         connection.execute(
470             'DELETE FROM usr_artist_2_artist'
471             ' WHERE artist1 = ? AND artist2 = ?;',
472             (artist1, artist2))
473         self.clean_database(with_connection=connection)
474         self._update_artist(artist_id=artist1, with_connection=connection)
475         connection.commit()
476         self.close_database_connection(connection)
477
478     def _remove_artist(self, artist_id, deep=False, with_connection=None):
479         """Remove all artist1 reference"""
480         if with_connection:
481             connection = with_connection
482         else:
483             connection = self.get_database_connection()
484         if deep:
485             connection.execute(
486                 'DELETE FROM usr_artist_2_artist'
487                 ' WHERE artist1 = ? OR artist2 = ?;',
488                 (artist_id, artist_id))
489             connection.execute(
490                 'DELETE FROM artists WHERE id = ?;',
491                 (artist_id,))
492         else:
493             connection.execute(
494                 'DELETE FROM usr_artist_2_artist WHERE artist1 = ?;',
495                 (artist_id,))
496         self.clean_database(with_connection=connection)
497         self._update_artist(artist_id=artist_id, with_connection=connection)
498         if not with_connection:
499             connection.commit()
500             self.close_database_connection(connection)
501
502     def _remove_bl(self, rowid):
503         """Remove bl row id"""
504         connection = self.get_database_connection()
505         connection.execute('DELETE FROM black_list'
506                 ' WHERE black_list.rowid = ?', (rowid,))
507         connection.commit()
508         self.close_database_connection(connection)
509
510     def _insert_artist_match(
511         self, artist1, artist2, match, with_connection=None):
512         """write match score to the database.
513         Does not update time stamp in table artist/*_updated"""
514         if with_connection:
515             connection = with_connection
516         else:
517             connection = self.get_database_connection()
518         connection.execute(
519             "INSERT INTO usr_artist_2_artist (artist1, artist2, match) VALUES"
520             " (?, ?, ?)",
521             (artist1, artist2, match))
522         if not with_connection:
523             connection.commit()
524             self.close_database_connection(connection)
525
526     def add_history(self, track):
527         """Add to history"""
528         connection = self.get_database_connection()
529         track_id = self.get_track(track, with_connection=connection)[0]
530         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
531                                   (track_id,))
532         if not rows.fetchone():
533             connection.execute("INSERT INTO history (track) VALUES (?)",
534                                (track_id,))
535         connection.execute("UPDATE history SET last_play = DATETIME('now') "
536                 " WHERE track = ?", (track_id,))
537         connection.commit()
538         self.close_database_connection(connection)
539
540     def _update_artist(self, artist_id, with_connection=None):
541         """write artist information to the database"""
542         if with_connection:
543             connection = with_connection
544         else:
545             connection = self.get_database_connection()
546         connection.execute(
547             "UPDATE artists SET usr_updated = DATETIME('now') WHERE id = ?",
548             (artist_id,))
549         if not with_connection:
550             connection.commit()
551             self.close_database_connection(connection)
552
553     def _update_artist_match(
554         self, artist1, artist2, match, with_connection=None):
555         """write match score to the database"""
556         if with_connection:
557             connection = with_connection
558         else:
559             connection = self.get_database_connection()
560         connection.execute(
561             "UPDATE usr_artist_2_artist SET match = ? WHERE artist1 = ? AND"
562             " artist2 = ?",
563             (match, artist1, artist2))
564         if not with_connection:
565             connection.commit()
566             self.close_database_connection(connection)
567
568     def _update_similar_artists(self, artist, similar_artists):
569         """write user similar artist information to the database
570         """
571         # DOC:   similar_artists = list([{'score': match, 'artist': name}])
572         #
573         connection = self.get_database_connection()
574         artist_id = self.get_artist(artist, with_connection=connection)[0]
575         for artist in similar_artists:
576             id2 = self.get_artist(
577                 artist['artist'], with_connection=connection)[0]
578             if self._get_artist_match(
579                 artist_id, id2, with_connection=connection):
580                 self._update_artist_match(
581                     artist_id, id2, artist['score'],
582                     with_connection=connection)
583                 continue
584             self._insert_artist_match(
585                 artist_id, id2, artist['score'],
586                 with_connection=connection)
587         self._update_artist(artist_id, with_connection=connection)
588         connection.commit()
589         self.close_database_connection(connection)
590
591     def _clean_artists_table(self, with_connection=None):
592         """Clean orphan artists"""
593         if with_connection:
594             connection = with_connection
595         else:
596             connection = self.get_database_connection()
597         artists_ids = set([row[0] for row in connection.execute(
598             "SELECT id FROM artists")])
599         artist_2_artist_ids = set([row[0] for row in connection.execute(
600             "SELECT artist1 FROM usr_artist_2_artist")] +
601             [row[0] for row in connection.execute(
602             "SELECT artist2 FROM usr_artist_2_artist")] +
603             [row[0] for row in connection.execute(
604             "SELECT artist FROM black_list")] +
605             [row[0] for row in connection.execute(
606             "SELECT artist FROM albums")] +
607             [row[0] for row in connection.execute(
608             "SELECT artist FROM tracks")])
609         orphans = [(orphan,) for orphan in artists_ids - artist_2_artist_ids]
610         connection.executemany('DELETE FROM artists WHERE id = (?);', orphans)
611         if not with_connection:
612             connection.commit()
613             self.close_database_connection(connection)
614
615     def _clean_albums_table(self, with_connection=None):
616         """Clean orphan albums"""
617         if with_connection:
618             connection = with_connection
619         else:
620             connection = self.get_database_connection()
621         orphan_black_ids = set([row[0] for row in connection.execute(
622             """SELECT albums.id FROM albums
623             LEFT JOIN black_list ON albums.id = black_list.album
624             WHERE ( black_list.album IS NULL )""")])
625         orphan_tracks_ids = set([row[0] for row in connection.execute(
626             """SELECT albums.id FROM albums
627             LEFT JOIN tracks ON albums.id = tracks.album
628             WHERE tracks.album IS NULL""")])
629         orphans = [(orphan,) for orphan in orphan_black_ids & orphan_tracks_ids]
630         connection.executemany('DELETE FROM albums WHERE id = (?);', orphans)
631         if not with_connection:
632             connection.commit()
633             self.close_database_connection(connection)
634
635     def _clean_tracks_table(self, with_connection=None):
636         """Clean orphan tracks"""
637         if with_connection:
638             connection = with_connection
639         else:
640             connection = self.get_database_connection()
641         hist_orphan_ids = set([row[0] for row in connection.execute(
642             """SELECT tracks.id FROM tracks
643             LEFT JOIN history ON tracks.id = history.track
644             WHERE history.track IS NULL""")])
645         black_list_orphan_ids = set([row[0] for row in connection.execute(
646             """SELECT tracks.id FROM tracks
647             LEFT JOIN black_list ON tracks.id = black_list.track
648             WHERE black_list.track IS NULL""")])
649         orphans = [(orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids]
650         connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans)
651         if not with_connection:
652             connection.commit()
653             self.close_database_connection(connection)
654
655     def clean_database(self, with_connection=None):
656         """Wrapper around _clean_* methods"""
657         if with_connection:
658             connection = with_connection
659         else:
660             connection = self.get_database_connection()
661         self._clean_tracks_table(with_connection=connection)
662         self._clean_albums_table(with_connection=connection)
663         self._clean_artists_table(with_connection=connection)
664         connection.execute("VACUUM")
665         if not with_connection:
666             connection.commit()
667             self.close_database_connection(connection)
668
669     def purge_history(self, duration=__HIST_DURATION__):
670         """Remove old entries in history"""
671         connection = self.get_database_connection()
672         connection.execute("DELETE FROM history WHERE last_play"
673                 " < datetime('now', '-%i hours')" % duration)
674         connection.commit()
675         self.close_database_connection(connection)
676
677     def _set_dbversion(self):
678         """Add db version"""
679         connection = self.get_database_connection()
680         connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)',
681                 (__DB_VERSION__, 'Sima DB'))
682         connection.commit()
683         self.close_database_connection(connection)
684
685     def create_db(self):
686         """ Set up a database for the artist similarity scores
687         """
688         connection = self.get_database_connection()
689         connection.execute(
690             'CREATE TABLE IF NOT EXISTS db_info'
691             ' (version INTEGER, name CHAR(36))')
692         connection.execute(
693             'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name'
694             ' VARCHAR(100), mbid CHAR(36), lfm_updated DATE, usr_updated DATE)')
695         connection.execute(
696             'CREATE TABLE IF NOT EXISTS usr_artist_2_artist (artist1 INTEGER,'
697             ' artist2 INTEGER, match INTEGER)')
698         connection.execute(
699             'CREATE TABLE IF NOT EXISTS lfm_artist_2_artist (artist1 INTEGER,'
700             ' artist2 INTEGER, match INTEGER)')
701         connection.execute(
702             'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,'
703             ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))')
704         connection.execute(
705             'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY,'
706             ' name VARCHAR(100), artist INTEGER, album INTEGER,'
707             ' file VARCHAR(500), mbid CHAR(36))')
708         connection.execute(
709             'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,'
710             ' album INTEGER, track INTEGER, updated DATE)')
711         connection.execute(
712             'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
713             ' track integer)')
714         connection.execute(
715           "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)")
716         connection.execute(
717           "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)")
718         connection.execute(
719         "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)")
720         connection.execute(
721         "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)")
722         connection.commit()
723         self.close_database_connection(connection)
724         self._set_dbversion()
725
726
727 def main():
728     db = SimaDB(db_path='/tmp/sima.db')
729     db.purge_history(int(4))
730     db.clean_database()
731
732
733 # Script starts here
734 if __name__ == '__main__':
735     main()
736
737 # VIM MODLINE
738 # vim: ai ts=4 sw=4 sts=4 expandtab