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/>.
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>
30 __HIST_DURATION__ = int(7 * 24) # in hours
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
40 class SimaDBError(Exception):
47 class SimaDBAccessError(SimaDBError):
48 """Error on accessing DB file"""
52 class SimaDBNoFile(SimaDBError):
53 """No DB file present"""
57 class SimaDBUpgradeError(SimaDBError):
58 """Error on upgrade"""
65 def __init__(self, db_path=None):
66 self._db_path = db_path
67 self.db_path_mod_control()
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"' %
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)
81 if not access(db_path, F_OK):
82 raise SimaDBNoFile('No DB file in "%s"' % db_path)
84 def close_database_connection(self, connection):
85 """Close the database connection."""
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
96 """upgrade DB from previous versions"""
97 connection = self.get_database_connection()
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')
108 raise SimaDBUpgradeError('Could not upgrade database: "%s"' %
110 self.close_database_connection(connection)
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."""
117 connection = with_connection
119 connection = self.get_database_connection()
120 rows = connection.execute(
121 "SELECT * FROM artists WHERE name = ?", (artist_name,))
123 if not with_connection:
124 self.close_database_connection(connection)
127 if not with_connection:
128 self.close_database_connection(connection)
131 "INSERT INTO artists (name, mbid) VALUES (?, ?)",
134 rows = connection.execute(
135 "SELECT * FROM artists WHERE name = ?", (artist_name,))
137 if not with_connection:
138 self.close_database_connection(connection)
140 if not with_connection:
141 self.close_database_connection(connection)
143 def get_track(self, track, with_connection=None, add_not=False):
145 Get a track from Tracks table, add if not existing,
146 Attention: use Track() object!!
147 if not in database insert new entry."""
152 connection = with_connection
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))
161 if not with_connection:
162 self.close_database_connection(connection)
167 "INSERT INTO tracks (artist, album, name, file) VALUES (?, ?, ?, ?)",
168 (art_id, alb_id, nam, fil))
170 rows = connection.execute(
171 "SELECT * FROM tracks WHERE name = ? AND"
172 " artist = ? AND album = ? AND file = ?",
173 (nam, art_id, alb_id, fil,))
175 if not with_connection:
176 self.close_database_connection(connection)
178 if not with_connection:
180 self.close_database_connection(connection)
182 def get_album(self, track, mbid=None,
183 with_connection=None, add_not=False):
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
191 connection = with_connection
193 connection = self.get_database_connection()
194 if track.albumartist:
195 artist = track.albumartist
197 artist = track.artist
198 art_id = self.get_artist(artist, with_connection=connection)[0]
200 rows = connection.execute(
201 "SELECT * FROM albums WHERE name = ? AND artist = ?",
204 if not with_connection:
205 self.close_database_connection(connection)
210 "INSERT INTO albums (name, artist, mbid) VALUES (?, ?, ?)",
211 (album, art_id, mbid))
213 rows = connection.execute(
214 "SELECT * FROM albums WHERE name = ? AND artist = ?",
217 if not with_connection:
218 self.close_database_connection(connection)
220 if not with_connection:
221 self.close_database_connection(connection)
223 def get_artists(self, with_connection=None):
224 """Returns all artists in DB"""
226 connection = with_connection
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:
236 def get_bl_artist(self, artist_name,
237 with_connection=None, add_not=None):
238 """get blacklisted artist information from the database."""
240 connection = with_connection
242 connection = self.get_database_connection()
243 art = self.get_artist(artist_name,
244 with_connection=connection, add_not=add_not)
248 rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
251 if not with_connection:
252 self.close_database_connection(connection)
255 if not with_connection:
256 self.close_database_connection(connection)
258 connection.execute("INSERT INTO black_list (artist) VALUES (?)",
260 connection.execute("UPDATE black_list SET updated = DATETIME('now')"
261 " WHERE artist = ?", (art_id,))
263 rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
266 if not with_connection:
267 self.close_database_connection(connection)
269 if not with_connection:
270 self.close_database_connection(connection)
273 def get_bl_album(self, track,
274 with_connection=None, add_not=None):
275 """get blacklisted album information from the database."""
277 connection = with_connection
279 connection = self.get_database_connection()
280 album = self.get_album(track,
281 with_connection=connection, add_not=add_not)
285 rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
288 if not with_connection:
289 self.close_database_connection(connection)
292 if not with_connection:
293 self.close_database_connection(connection)
295 connection.execute("INSERT INTO black_list (album) VALUES (?)",
297 connection.execute("UPDATE black_list SET updated = DATETIME('now')"
298 " WHERE album = ?", (alb_id,))
300 rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
303 if not with_connection:
304 self.close_database_connection(connection)
306 if not with_connection:
307 self.close_database_connection(connection)
310 def get_bl_track(self, track, with_connection=None, add_not=None):
311 """get blacklisted track information from the database."""
313 connection = with_connection
315 connection = self.get_database_connection()
316 track = self.get_track(track,
317 with_connection=connection, add_not=add_not)
321 rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
324 if not with_connection:
325 self.close_database_connection(connection)
328 if not with_connection:
329 self.close_database_connection(connection)
331 connection.execute("INSERT INTO black_list (track) VALUES (?)",
333 connection.execute("UPDATE black_list SET updated = DATETIME('now')"
334 " WHERE track = ?", (track_id,))
336 rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
339 if not with_connection:
340 self.close_database_connection(connection)
342 if not with_connection:
343 self.close_database_connection(connection)
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
351 date = datetime.utcnow() - timedelta(hours=duration)
352 connection = self.get_database_connection()
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,))
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(' '),))
367 if artists and row[0] not in artists:
370 self.close_database_connection(connection)
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',)
380 yield ('Row ID', 'Artist',)
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')
388 yield ('Row ID', 'Album', 'Artist name')
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')
396 yield ('Row ID', 'Title', 'Artist name')
399 self.close_database_connection(connection)
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."""
405 connection = with_connection
407 connection = self.get_database_connection()
408 connection.execute("UPDATE artists SET mbid = ? WHERE id = ?",
411 if not with_connection:
412 self.close_database_connection(connection)
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;",
421 self.close_database_connection(connection)
422 for score, artist in results:
423 yield {'score': score, 'artist': artist}
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 = ?;",
432 self.close_database_connection(connection)
433 for artist in results:
436 def get_similar_artists(self, artist_name):
437 """get similar artists from the database sorted by descending
439 artist_id = self.get_artist(artist_name)[0]
440 for result in self._get_similar_artists_from_db(artist_id):
443 def _get_artist_match(self, artist1, artist2, with_connection=None):
444 """get artist match score from database"""
446 connection = with_connection
448 connection = self.get_database_connection()
449 rows = connection.execute(
450 "SELECT match FROM usr_artist_2_artist WHERE artist1 = ?"
457 if not with_connection:
458 self.close_database_connection(connection)
461 def _remove_relation_between_2_artist(self, artist1, artist2):
462 """Remove a similarity relation"""
463 connection = self.get_database_connection()
465 'DELETE FROM usr_artist_2_artist'
466 ' WHERE artist1 = ? AND artist2 = ?;',
468 self.clean_database(with_connection=connection)
469 self._update_artist(artist_id=artist1, with_connection=connection)
471 self.close_database_connection(connection)
473 def _remove_artist(self, artist_id, deep=False, with_connection=None):
474 """Remove all artist1 reference"""
476 connection = with_connection
478 connection = self.get_database_connection()
481 'DELETE FROM usr_artist_2_artist'
482 ' WHERE artist1 = ? OR artist2 = ?;',
483 (artist_id, artist_id))
485 'DELETE FROM artists WHERE id = ?;',
489 'DELETE FROM usr_artist_2_artist WHERE artist1 = ?;',
491 self.clean_database(with_connection=connection)
492 self._update_artist(artist_id=artist_id, with_connection=connection)
493 if not with_connection:
495 self.close_database_connection(connection)
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,))
503 self.close_database_connection(connection)
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"""
510 connection = with_connection
512 connection = self.get_database_connection()
514 "INSERT INTO usr_artist_2_artist (artist1, artist2, match) VALUES"
516 (artist1, artist2, match))
517 if not with_connection:
519 self.close_database_connection(connection)
521 def add_history(self, track):
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,))
531 self.close_database_connection(connection)
533 def _update_artist(self, artist_id, with_connection=None):
534 """write artist information to the database"""
536 connection = with_connection
538 connection = self.get_database_connection()
540 "UPDATE artists SET usr_updated = DATETIME('now') WHERE id = ?",
542 if not with_connection:
544 self.close_database_connection(connection)
546 def _update_artist_match(
547 self, artist1, artist2, match, with_connection=None):
548 """write match score to the database"""
550 connection = with_connection
552 connection = self.get_database_connection()
554 "UPDATE usr_artist_2_artist SET match = ? WHERE artist1 = ? AND"
556 (match, artist1, artist2))
557 if not with_connection:
559 self.close_database_connection(connection)
561 def _update_similar_artists(self, artist, similar_artists):
562 """write user similar artist information to the database
564 # DOC: similar_artists = list([{'score': match, 'artist': name}])
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)
577 self._insert_artist_match(
578 artist_id, id2, artist['score'],
579 with_connection=connection)
580 self._update_artist(artist_id, with_connection=connection)
582 self.close_database_connection(connection)
584 def _clean_artists_table(self, with_connection=None):
585 """Clean orphan artists"""
587 connection = with_connection
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:
606 self.close_database_connection(connection)
608 def _clean_albums_table(self, with_connection=None):
609 """Clean orphan albums"""
611 connection = with_connection
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:
626 self.close_database_connection(connection)
628 def _clean_tracks_table(self, with_connection=None):
629 """Clean orphan tracks"""
631 connection = with_connection
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:
646 self.close_database_connection(connection)
648 def clean_database(self, with_connection=None):
649 """Wrapper around _clean_* methods"""
651 connection = with_connection
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:
660 self.close_database_connection(connection)
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)
668 self.close_database_connection(connection)
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'))
675 self.close_database_connection(connection)
678 """ Set up a database for the artist similarity scores
680 connection = self.get_database_connection()
682 'CREATE TABLE IF NOT EXISTS db_info'
683 ' (version INTEGER, name CHAR(36))')
685 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name'
686 ' VARCHAR(100), mbid CHAR(36), lfm_updated DATE, usr_updated DATE)')
688 'CREATE TABLE IF NOT EXISTS usr_artist_2_artist (artist1 INTEGER,'
689 ' artist2 INTEGER, match INTEGER)')
691 'CREATE TABLE IF NOT EXISTS lfm_artist_2_artist (artist1 INTEGER,'
692 ' artist2 INTEGER, match INTEGER)')
694 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,'
695 ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))')
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))')
701 'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,'
702 ' album INTEGER, track INTEGER, updated DATE)')
704 'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
707 "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)")
709 "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)")
711 "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)")
713 "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)")
715 self.close_database_connection(connection)
716 self._set_dbversion()
720 db = SimaDB(db_path='/tmp/sima.db')
721 db.purge_history(int(4))
726 if __name__ == '__main__':
730 # vim: ai ts=4 sw=4 sts=4 expandtab