1 # Copyright (c) 2009-2013, 2019-2021 kaliko <kaliko@azylum.org>
3 # This file is part of sima
5 # sima is free software: you can redistribute it and/or modify
6 # it under the terms of the GNU General Public License as published by
7 # the Free Software Foundation, either version 3 of the License, or
8 # (at your option) any later version.
10 # sima is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with sima. If not, see <http://www.gnu.org/licenses/>.
19 """SQlite database library
23 __HIST_DURATION__ = int(30 * 24) # in hours
27 from collections import deque
28 from datetime import (datetime, timedelta)
30 from sima.lib.meta import Artist, Album
31 from sima.lib.track import Track
34 class SimaDBError(Exception):
43 def __init__(self, db_path=None):
44 self._db_path = db_path
46 def get_database_connection(self):
47 """get database reference"""
48 connection = sqlite3.connect(
49 self._db_path, isolation_level=None)
55 connection = self.get_database_connection()
57 'CREATE TABLE IF NOT EXISTS db_info'
58 ' (name CHAR(50), value CHAR(50))')
59 connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ?
61 ( SELECT 1 FROM db_info WHERE name = ? )''',
62 ('DB Version', __DB_VERSION__, 'DB Version'))
63 connection.execute( # ARTISTS
64 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, '
65 'name VARCHAR(100), mbid CHAR(36))')
66 connection.execute( # ALBUMS
67 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, '
68 'name VARCHAR(100), mbid CHAR(36))')
69 connection.execute( # ALBUMARTISTS
70 'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, '
71 'name VARCHAR(100), mbid CHAR(36))')
72 connection.execute( # TRACKS
73 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, '
74 'title VARCHAR(100), artist INTEGER, '
75 'album INTEGER, albumartist INTEGER, '
76 'file VARCHAR(500), mbid CHAR(36), '
77 'FOREIGN KEY(artist) REFERENCES artists(id), '
78 'FOREIGN KEY(album) REFERENCES albums(id), '
79 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))')
80 connection.execute( # HISTORY
81 'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, '
82 'last_play TIMESTAMP, track INTEGER, '
83 'FOREIGN KEY(track) REFERENCES tracks(id))')
84 connection.execute( # BLOCKLIST
85 'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, '
86 'artist INTEGER, album INTEGER, track INTEGER, '
87 'FOREIGN KEY(artist) REFERENCES artists(id), '
88 'FOREIGN KEY(album) REFERENCES albums(id), '
89 'FOREIGN KEY(track) REFERENCES tracks(id))')
90 connection.execute( # Genres (Many-to-many)
91 'CREATE TABLE IF NOT EXISTS genres '
92 '(id INTEGER PRIMARY KEY, name VARCHAR(100))')
93 connection.execute( # Junction Genres Tracks
94 """CREATE TABLE IF NOT EXISTS tracks_genres
95 ( track INTEGER, genre INTEGER,
96 FOREIGN KEY(track) REFERENCES tracks(id)
97 FOREIGN KEY(genre) REFERENCES genres(id))""")
98 # Create cleanup triggers:
99 # DELETE history → Tracks / Tracks_genres tables
100 connection.execute('''
101 CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
102 AFTER DELETE ON history
103 WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
104 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
106 DELETE FROM tracks WHERE id = old.track;
107 DELETE FROM tracks_genres WHERE track = old.track;
110 # DELETE Tracks_Genres → Genres table
111 connection.execute('''
112 CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres
113 AFTER DELETE ON tracks_genres
114 WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0)
116 DELETE FROM genres WHERE id = old.genre;
119 # DELETE Tracks → Artists table
120 connection.execute('''
121 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
122 AFTER DELETE ON tracks
123 WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
124 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
126 DELETE FROM artists WHERE id = old.artist;
129 # DELETE Tracks → Albums table
130 connection.execute('''
131 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
132 AFTER DELETE ON tracks
133 WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
134 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
136 DELETE FROM albums WHERE id = old.album;
139 # DELETE Tracks → cleanup AlbumArtists table
140 connection.execute('''
141 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
142 AFTER DELETE ON tracks
143 WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
145 DELETE FROM albumartists WHERE id = old.albumartist;
148 # DELETE blocklist → Tracks table
149 connection.execute('''
150 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
151 AFTER DELETE ON blocklist
152 WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
153 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
155 DELETE FROM tracks WHERE id = old.track;
158 # DELETE blocklist → Artists table
159 # The "SELECT count(*) FROM blocklist" is useless,
160 # there can be only one blocklist.artist
161 connection.execute('''
162 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_artists
163 AFTER DELETE ON blocklist
164 WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
165 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
167 DELETE FROM artists WHERE id = old.artist;
170 # DELETE Tracks → Albums table
171 # The "SELECT count(*) FROM blocklist" is useless,
172 # there can be only one blocklist.album
173 connection.execute('''
174 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_albums
175 AFTER DELETE ON blocklist
176 WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
177 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
179 DELETE FROM albums WHERE id = old.album;
185 connection = self.get_database_connection()
186 rows = connection.execute(
187 "SELECT name FROM sqlite_master WHERE type='table'")
188 for r in rows.fetchall():
189 connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
192 def _remove_blocklist_id(self, blid, with_connection=None):
195 connection = with_connection
197 connection = self.get_database_connection()
198 connection = self.get_database_connection()
199 connection.execute('DELETE FROM blocklist'
200 ' WHERE blocklist.id = ?', (blid,))
202 if not with_connection:
205 def _get_album(self, album, connection):
207 return connection.execute(
208 "SELECT id FROM albums WHERE mbid = ?",
211 return connection.execute(
212 "SELECT id FROM albums WHERE name = ? AND mbid IS NULL",
215 def get_album(self, album, with_connection=None, add=True):
216 """get album information from the database.
217 if not in database insert new entry.
219 :param sima.lib.meta.Album album: album objet
220 :param sqlite3.Connection with_connection: SQLite connection
223 connection = with_connection
225 connection = self.get_database_connection()
226 rows = self._get_album(album, connection)
228 if not with_connection:
232 if not with_connection:
236 "INSERT INTO albums (name, mbid) VALUES (?, ?)",
237 (album.name, album.mbid))
239 rows = self._get_album(album, connection)
241 if not with_connection:
244 if not with_connection:
248 def _get_albumartist(self, artist, connection):
250 return connection.execute(
251 "SELECT id FROM albumartists WHERE mbid = ?",
254 return connection.execute(
255 "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL",
258 def get_albumartist(self, artist, with_connection=None, add=True):
259 """get albumartist information from the database.
260 if not in database insert new entry.
262 :param sima.lib.meta.Artist artist: artist
263 :param sqlite3.Connection with_connection: SQLite connection
266 connection = with_connection
268 connection = self.get_database_connection()
269 rows = self._get_albumartist(artist, connection)
271 if not with_connection:
275 if not with_connection:
279 "INSERT INTO albumartists (name, mbid) VALUES (?, ?)",
280 (artist.name, artist.mbid))
282 rows = self._get_albumartist(artist, connection)
284 if not with_connection:
287 if not with_connection:
290 def _get_artist(self, artist, connection):
292 return connection.execute(
293 "SELECT id FROM artists WHERE mbid = ?",
296 return connection.execute(
297 "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,))
299 def get_artist(self, artist, with_connection=None, add=True):
300 """get artist information from the database.
301 if not in database insert new entry.
303 :param sima.lib.meta.Artist artist: artist
304 :param sqlite3.Connection with_connection: SQLite connection
307 connection = with_connection
309 connection = self.get_database_connection()
310 rows = self._get_artist(artist, connection)
312 if not with_connection:
316 if not with_connection:
320 "INSERT INTO artists (name, mbid) VALUES (?, ?)",
321 (artist.name, artist.mbid))
323 rows = self._get_artist(artist, connection)
325 if not with_connection:
328 if not with_connection:
331 def get_genre(self, genre, with_connection=None, add=True):
332 """get genre from the database.
333 if not in database insert new entry.
335 :param str genre: genre as a string
336 :param sqlite3.Connection with_connection: SQLite connection
339 connection = with_connection
341 connection = self.get_database_connection()
342 rows = connection.execute(
343 "SELECT id FROM genres WHERE name = ?", (genre,))
345 if not with_connection:
349 if not with_connection:
353 "INSERT INTO genres (name) VALUES (?)", (genre,))
355 rows = connection.execute(
356 "SELECT id FROM genres WHERE name = ?", (genre,))
358 if not with_connection:
362 def get_track(self, track, with_connection=None, add=True):
363 """Get a track id from Tracks table, add if not existing,
364 :param sima.lib.track.Track track: track to use
365 :param bool add: add non existing track to database"""
367 raise SimaDBError('Got a track with no file attribute: %r' % track)
369 connection = with_connection
371 connection = self.get_database_connection()
372 rows = connection.execute(
373 "SELECT * FROM tracks WHERE file = ?", (track.file,))
375 if not with_connection:
378 if not add: # Not adding non existing track
381 # Get an artist record or None
383 art = Artist(name=track.artist, mbid=track.musicbrainz_artistid)
384 art_id = self.get_artist(art, with_connection=connection)
387 # Get an albumartist record or None
388 if track.albumartist:
389 albart = Artist(name=track.albumartist,
390 mbid=track.musicbrainz_albumartistid)
391 albart_id = self.get_albumartist(albart, with_connection=connection)
394 # Get an album record or None
396 alb = Album(name=track.album, mbid=track.musicbrainz_albumid)
397 alb_id = self.get_album(alb, with_connection=connection)
401 """INSERT INTO tracks (artist, albumartist, album, title, mbid, file)
402 VALUES (?, ?, ?, ?, ?, ?)""",
403 (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid,
406 # Add track id to junction tables
407 self._add_tracks_genres(track, connection)
408 rows = connection.execute(
409 "SELECT id FROM tracks WHERE file = ?", (track.file,))
411 if not with_connection:
414 if not with_connection:
418 def _add_tracks_genres(self, track, connection):
421 rows = connection.execute(
422 "SELECT id FROM tracks WHERE file = ?", (track.file,))
423 trk_id = rows.fetchone()[0]
424 for genre in track.genres:
426 gen_id = self.get_genre(genre)
427 connection.execute("""INSERT INTO tracks_genres (track, genre)
428 VALUES (?, ?)""", (trk_id, gen_id))
430 def add_history(self, track, date=None):
431 """Record last play date of track (ie. not a real exhautive play history).
432 :param track sima.lib.track.Track: track to add to history"""
434 date = datetime.now()
435 connection = self.get_database_connection()
436 track_id = self.get_track(track, with_connection=connection)
437 rows = connection.execute("SELECT * FROM history WHERE track = ? ",
439 if not rows.fetchone():
440 connection.execute("INSERT INTO history (track) VALUES (?)",
442 connection.execute("UPDATE history SET last_play = ? "
443 " WHERE track = ?", (date, track_id,))
447 def purge_history(self, duration=__HIST_DURATION__):
448 """Remove old entries in history
449 :param duration int: Purge history record older than duration in hours
450 (defaults to __HIST_DURATION__)"""
451 connection = self.get_database_connection()
452 connection.execute("DELETE FROM history WHERE last_play"
453 " < datetime('now', '-%i hours')" % duration)
454 connection.execute('VACUUM')
458 def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__):
460 :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
462 date = datetime.utcnow() - timedelta(hours=duration)
463 connection = self.get_database_connection()
464 connection.row_factory = sqlite3.Row
465 rows = connection.execute("""
466 SELECT albums.name AS name,
468 artists.name as artist,
469 artists.mbid as artist_mbib
471 JOIN tracks ON history.track = tracks.id
472 LEFT OUTER JOIN albums ON tracks.album = albums.id
473 LEFT OUTER JOIN artists ON tracks.artist = artists.id
474 WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
475 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
479 artist = Artist(name=vals.pop('artist'),
480 mbid=vals.pop('artist_mbib'))
484 album = Album(**vals, artist=artist)
485 if hist and hist[-1] == album:
486 # remove consecutive dupes
492 def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
493 """Returns a list of Artist objects
494 :param sima.lib.meta.Artist|sima.lib.meta.MetaContainer needle: When specified, returns history for this artist, it's actually testing the artist presence in history.
495 :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
497 date = datetime.utcnow() - timedelta(hours=duration)
498 connection = self.get_database_connection()
499 connection.row_factory = sqlite3.Row
500 rows = connection.execute("""
501 SELECT artists.name AS name,
504 JOIN tracks ON history.track = tracks.id
505 LEFT OUTER JOIN artists ON tracks.artist = artists.id
506 WHERE history.last_play > ? AND artists.name NOT NULL
507 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
508 last = deque(maxlen=1)
511 artist = Artist(**row)
512 if last and last[0] == artist: # remove consecutive dupes
515 if needle and isinstance(needle, (Artist, str)):
517 hist.append(artist) # No need to go further
520 elif needle and getattr(needle, '__contains__'):
522 hist.append(artist) # No need to go further
528 def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
529 date = datetime.utcnow() - timedelta(hours=duration)
530 connection = self.get_database_connection()
531 rows = connection.execute("""
532 SELECT genres.name, artists.name
534 JOIN tracks ON history.track = tracks.id
535 LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id
536 LEFT OUTER JOIN artists ON tracks.artist = artists.id
537 LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre
538 WHERE history.last_play > ?
539 ORDER BY history.last_play DESC
540 """, (date.isoformat(' '),))
544 if len({g[0] for g in genres}) >= limit:
549 def fetch_history(self, artist=None, duration=__HIST_DURATION__):
550 """Fetches tracks history, more recent first
551 :param sima.lib.meta.Artist artist: limit history to this artist
552 :param int duration: How long ago to fetch history from
554 date = datetime.utcnow() - timedelta(hours=duration)
555 connection = self.get_database_connection()
556 connection.row_factory = sqlite3.Row
558 SELECT tracks.title, tracks.file, artists.name AS artist,
559 albumartists.name AS albumartist,
560 artists.mbid as musicbrainz_artistid,
561 albums.name AS album,
562 albums.mbid AS musicbrainz_albumid,
563 tracks.mbid as musicbrainz_trackid
565 JOIN tracks ON history.track = tracks.id
566 LEFT OUTER JOIN artists ON tracks.artist = artists.id
567 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
568 LEFT OUTER JOIN albums ON tracks.album = albums.id
569 WHERE history.last_play > ?
573 rows = connection.execute(sql+"""
575 ORDER BY history.last_play DESC""",
576 (date.isoformat(' '), artist.mbid))
578 rows = connection.execute(sql+"""
580 ORDER BY history.last_play DESC""",
581 (date.isoformat(' '), artist.name))
583 rows = connection.execute(sql+'ORDER BY history.last_play DESC',
584 (date.isoformat(' '),))
587 hist.append(Track(**row))
591 def get_bl_track(self, track, with_connection=None, add=True):
592 """Add a track to blocklist
593 :param sima.lib.track.Track track: Track object to add to blocklist
594 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
595 :param bool add: Default is to add a new record, set to False to fetch associated record"""
597 connection = with_connection
599 connection = self.get_database_connection()
600 track_id = self.get_track(track, with_connection=connection, add=True)
601 rows = connection.execute(
602 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
603 if not rows.fetchone():
606 connection.execute('INSERT INTO blocklist (track) VALUES (?)',
609 rows = connection.execute(
610 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
611 bl = rows.fetchone()[0]
612 if not with_connection:
616 def get_bl_album(self, album, with_connection=None, add=True):
617 """Add an album to blocklist
618 :param sima.lib.meta.Album: Album object to add to blocklist
619 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
620 :param bool add: Default is to add a new record, set to False to fetch associated record"""
622 connection = with_connection
624 connection = self.get_database_connection()
625 album_id = self.get_album(album, with_connection=connection, add=True)
626 rows = connection.execute(
627 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
628 if not rows.fetchone():
631 connection.execute('INSERT INTO blocklist (album) VALUES (?)',
634 rows = connection.execute(
635 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
636 bl = rows.fetchone()[0]
637 if not with_connection:
641 def get_bl_artist(self, artist, with_connection=None, add=True):
642 """Add an artist to blocklist
643 :param sima.lib.meta.Artist: Artist object to add to blocklist
644 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
645 :param bool add: Default is to add a new record, set to False to fetch associated record"""
647 connection = with_connection
649 connection = self.get_database_connection()
650 artist_id = self.get_artist(artist, with_connection=connection, add=True)
651 rows = connection.execute(
652 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
653 if not rows.fetchone():
656 connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
659 rows = connection.execute(
660 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
661 bl = rows.fetchone()[0]
662 if not with_connection:
666 def delete_bl(self, track=None, album=None, artist=None):
667 if not (track or album or artist):
669 connection = self.get_database_connection()
672 blid = self.get_bl_track(track, with_connection=connection)
674 blid = self.get_bl_album(album, with_connection=connection)
676 blid = self.get_bl_artist(artist, with_connection=connection)
679 self._remove_blocklist_id(blid, with_connection=connection)
684 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8