1 # -*- coding: utf-8 -*-
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
7 # This file is part of sima
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.
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.
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/>.
23 """SQlite database library
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>
32 __HIST_DURATION__ = int(30 * 24) # in hours
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
42 class SimaDBError(Exception):
49 class SimaDBAccessError(SimaDBError):
50 """Error on accessing DB file"""
54 class SimaDBNoFile(SimaDBError):
55 """No DB file present"""
59 class SimaDBUpgradeError(SimaDBError):
60 """Error on upgrade"""
67 def __init__(self, db_path=None):
68 self._db_path = db_path
69 self.db_path_mod_control()
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"' %
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)
84 if not access(db_path, F_OK):
85 raise SimaDBNoFile('No DB file in "%s"' % db_path)
87 def close_database_connection(self, connection):
88 """Close the database connection."""
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
99 """upgrade DB from previous versions"""
100 connection = self.get_database_connection()
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')
111 raise SimaDBUpgradeError('Could not upgrade database: "%s"' %
113 self.close_database_connection(connection)
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."""
120 connection = with_connection
122 connection = self.get_database_connection()
123 rows = connection.execute(
124 "SELECT * FROM artists WHERE name = ?", (artist_name,))
126 if not with_connection:
127 self.close_database_connection(connection)
130 if not with_connection:
131 self.close_database_connection(connection)
134 "INSERT INTO artists (name, mbid) VALUES (?, ?)",
137 rows = connection.execute(
138 "SELECT * FROM artists WHERE name = ?", (artist_name,))
140 if not with_connection:
141 self.close_database_connection(connection)
143 if not with_connection:
144 self.close_database_connection(connection)
146 def get_track(self, track, with_connection=None, add_not=False):
148 Get a track from Tracks table, add if not existing,
149 Attention: use Track() object!!
150 if not in database insert new entry."""
155 connection = with_connection
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))
164 if not with_connection:
165 self.close_database_connection(connection)
170 "INSERT INTO tracks (artist, album, name, file) VALUES (?, ?, ?, ?)",
171 (art_id, alb_id, nam, fil))
173 rows = connection.execute(
174 "SELECT * FROM tracks WHERE name = ? AND"
175 " artist = ? AND album = ? AND file = ?",
176 (nam, art_id, alb_id, fil,))
178 if not with_connection:
179 self.close_database_connection(connection)
181 if not with_connection:
183 self.close_database_connection(connection)
185 def get_album(self, track, mbid=None,
186 with_connection=None, add_not=False):
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
194 connection = with_connection
196 connection = self.get_database_connection()
197 if track.albumartist:
198 artist = track.albumartist
200 artist = track.artist
201 art_id = self.get_artist(artist, with_connection=connection)[0]
203 rows = connection.execute(
204 "SELECT * FROM albums WHERE name = ? AND artist = ?",
207 if not with_connection:
208 self.close_database_connection(connection)
213 "INSERT INTO albums (name, artist, mbid) VALUES (?, ?, ?)",
214 (album, art_id, mbid))
216 rows = connection.execute(
217 "SELECT * FROM albums WHERE name = ? AND artist = ?",
220 if not with_connection:
221 self.close_database_connection(connection)
223 if not with_connection:
224 self.close_database_connection(connection)
226 def get_artists(self, with_connection=None):
227 """Returns all artists in DB"""
229 connection = with_connection
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:
239 def get_bl_artist(self, artist_name,
240 with_connection=None, add_not=None):
241 """get blacklisted artist information from the database."""
243 connection = with_connection
245 connection = self.get_database_connection()
246 art = self.get_artist(artist_name,
247 with_connection=connection, add_not=add_not)
251 rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
254 if not with_connection:
255 self.close_database_connection(connection)
258 if not with_connection:
259 self.close_database_connection(connection)
261 connection.execute("INSERT INTO black_list (artist) VALUES (?)",
263 connection.execute("UPDATE black_list SET updated = DATETIME('now')"
264 " WHERE artist = ?", (art_id,))
266 rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
269 if not with_connection:
270 self.close_database_connection(connection)
272 if not with_connection:
273 self.close_database_connection(connection)
276 def get_bl_album(self, track,
277 with_connection=None, add_not=None):
278 """get blacklisted album information from the database."""
280 connection = with_connection
282 connection = self.get_database_connection()
283 album = self.get_album(track,
284 with_connection=connection, add_not=add_not)
288 rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
291 if not with_connection:
292 self.close_database_connection(connection)
295 if not with_connection:
296 self.close_database_connection(connection)
298 connection.execute("INSERT INTO black_list (album) VALUES (?)",
300 connection.execute("UPDATE black_list SET updated = DATETIME('now')"
301 " WHERE album = ?", (alb_id,))
303 rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
306 if not with_connection:
307 self.close_database_connection(connection)
309 if not with_connection:
310 self.close_database_connection(connection)
313 def get_bl_track(self, track, with_connection=None, add_not=None):
314 """get blacklisted track information from the database."""
316 connection = with_connection
318 connection = self.get_database_connection()
319 track = self.get_track(track,
320 with_connection=connection, add_not=add_not)
324 rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
327 if not with_connection:
328 self.close_database_connection(connection)
331 if not with_connection:
332 self.close_database_connection(connection)
334 connection.execute("INSERT INTO black_list (track) VALUES (?)",
336 connection.execute("UPDATE black_list SET updated = DATETIME('now')"
337 " WHERE track = ?", (track_id,))
339 rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
342 if not with_connection:
343 self.close_database_connection(connection)
345 if not with_connection:
346 self.close_database_connection(connection)
349 def get_artists_history(self, artists, duration=__HIST_DURATION__):
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(' '),))
360 if artists and row[0] not in artists:
365 self.close_database_connection(connection)
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()
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,))
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(' '),))
388 if artists and row[0] not in artists:
391 self.close_database_connection(connection)
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',)
401 yield ('Row ID', 'Artist',)
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')
410 yield ('Row ID', 'Album', 'Artist name')
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')
419 yield ('Row ID', 'Title', 'Artist name')
422 self.close_database_connection(connection)
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."""
428 connection = with_connection
430 connection = self.get_database_connection()
431 connection.execute("UPDATE artists SET mbid = ? WHERE id = ?",
434 if not with_connection:
435 self.close_database_connection(connection)
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;",
444 self.close_database_connection(connection)
445 for score, artist in results:
446 yield {'score': score, 'artist': artist}
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 = ?;",
455 self.close_database_connection(connection)
456 for artist in results:
459 def get_similar_artists(self, artist_name):
460 """get similar artists from the database sorted by descending
462 artist_id = self.get_artist(artist_name)[0]
463 for result in self._get_similar_artists_from_db(artist_id):
466 def _get_artist_match(self, artist1, artist2, with_connection=None):
467 """get artist match score from database"""
469 connection = with_connection
471 connection = self.get_database_connection()
472 rows = connection.execute(
473 "SELECT match FROM usr_artist_2_artist WHERE artist1 = ?"
480 if not with_connection:
481 self.close_database_connection(connection)
484 def _remove_relation_between_2_artist(self, artist1, artist2):
485 """Remove a similarity relation"""
486 connection = self.get_database_connection()
488 'DELETE FROM usr_artist_2_artist'
489 ' WHERE artist1 = ? AND artist2 = ?;',
491 self.clean_database(with_connection=connection)
492 self._update_artist(artist_id=artist1, with_connection=connection)
494 self.close_database_connection(connection)
496 def _remove_artist(self, artist_id, deep=False, with_connection=None):
497 """Remove all artist1 reference"""
499 connection = with_connection
501 connection = self.get_database_connection()
504 'DELETE FROM usr_artist_2_artist'
505 ' WHERE artist1 = ? OR artist2 = ?;',
506 (artist_id, artist_id))
508 'DELETE FROM artists WHERE id = ?;',
512 'DELETE FROM usr_artist_2_artist WHERE artist1 = ?;',
514 self.clean_database(with_connection=connection)
515 self._update_artist(artist_id=artist_id, with_connection=connection)
516 if not with_connection:
518 self.close_database_connection(connection)
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,))
526 self.close_database_connection(connection)
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"""
533 connection = with_connection
535 connection = self.get_database_connection()
537 "INSERT INTO usr_artist_2_artist (artist1, artist2, match) VALUES"
539 (artist1, artist2, match))
540 if not with_connection:
542 self.close_database_connection(connection)
544 def add_history(self, track):
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 = ? ",
550 if not rows.fetchone():
551 connection.execute("INSERT INTO history (track) VALUES (?)",
553 connection.execute("UPDATE history SET last_play = DATETIME('now') "
554 " WHERE track = ?", (track_id,))
556 self.close_database_connection(connection)
558 def _update_artist(self, artist_id, with_connection=None):
559 """write artist information to the database"""
561 connection = with_connection
563 connection = self.get_database_connection()
565 "UPDATE artists SET usr_updated = DATETIME('now') WHERE id = ?",
567 if not with_connection:
569 self.close_database_connection(connection)
571 def _update_artist_match(
572 self, artist1, artist2, match, with_connection=None):
573 """write match score to the database"""
575 connection = with_connection
577 connection = self.get_database_connection()
579 "UPDATE usr_artist_2_artist SET match = ? WHERE artist1 = ? AND"
581 (match, artist1, artist2))
582 if not with_connection:
584 self.close_database_connection(connection)
586 def _update_similar_artists(self, artist, similar_artists):
587 """write user similar artist information to the database
589 # DOC: similar_artists = list([{'score': match, 'artist': name}])
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)
602 self._insert_artist_match(
603 artist_id, id2, artist['score'],
604 with_connection=connection)
605 self._update_artist(artist_id, with_connection=connection)
607 self.close_database_connection(connection)
609 def _clean_artists_table(self, with_connection=None):
610 """Clean orphan artists"""
612 connection = with_connection
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:
631 self.close_database_connection(connection)
633 def _clean_albums_table(self, with_connection=None):
634 """Clean orphan albums"""
636 connection = with_connection
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:
651 self.close_database_connection(connection)
653 def _clean_tracks_table(self, with_connection=None):
654 """Clean orphan tracks"""
656 connection = with_connection
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:
671 self.close_database_connection(connection)
673 def clean_database(self, with_connection=None):
674 """Wrapper around _clean_* methods"""
676 connection = with_connection
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:
685 self.close_database_connection(connection)
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)
693 self.close_database_connection(connection)
695 def _set_dbversion(self):
697 connection = self.get_database_connection()
698 connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)',
699 (__DB_VERSION__, 'Sima DB'))
701 self.close_database_connection(connection)
704 """ Set up a database for the artist similarity scores
706 connection = self.get_database_connection()
708 'CREATE TABLE IF NOT EXISTS db_info'
709 ' (version INTEGER, name CHAR(36))')
711 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name'
712 ' VARCHAR(100), mbid CHAR(36), lfm_updated DATE, usr_updated DATE)')
714 'CREATE TABLE IF NOT EXISTS usr_artist_2_artist (artist1 INTEGER,'
715 ' artist2 INTEGER, match INTEGER)')
717 'CREATE TABLE IF NOT EXISTS lfm_artist_2_artist (artist1 INTEGER,'
718 ' artist2 INTEGER, match INTEGER)')
720 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,'
721 ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))')
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))')
727 'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,'
728 ' album INTEGER, track INTEGER, updated DATE)')
730 'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
733 "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)")
735 "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)")
737 "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)")
739 "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)")
741 self.close_database_connection(connection)
742 self._set_dbversion()
746 db = SimaDB(db_path='/tmp/sima.db')
747 db.purge_history(int(4))
752 if __name__ == '__main__':
756 # vim: ai ts=4 sw=4 sts=4 expandtab