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)
29 from datetime import timezone
32 from sima.lib.meta import Artist, Album
33 from sima.lib.track import Track
36 class SimaDBError(Exception):
45 def __init__(self, db_path=None):
46 self._db_path = db_path
48 def get_database_connection(self):
49 """get database reference"""
50 connection = sqlite3.connect(self._db_path, isolation_level=None)
54 connection = self.get_database_connection()
55 info = connection.execute("""SELECT * FROM db_info
56 WHERE name = "DB Version" LIMIT 1;""").fetchone()
63 connection = self.get_database_connection()
65 'CREATE TABLE IF NOT EXISTS db_info'
66 ' (name CHAR(50), value CHAR(50))')
67 connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ?
69 ( SELECT 1 FROM db_info WHERE name = ? )''',
70 ('DB Version', __DB_VERSION__, 'DB Version'))
71 connection.execute( # ARTISTS
72 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, '
73 'name VARCHAR(100), mbid CHAR(36))')
74 connection.execute( # ALBUMS
75 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, '
76 'name VARCHAR(100), mbid CHAR(36))')
77 connection.execute( # ALBUMARTISTS
78 'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, '
79 'name VARCHAR(100), mbid CHAR(36))')
80 connection.execute( # TRACKS
81 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, '
82 'title VARCHAR(100), artist INTEGER, '
83 'album INTEGER, albumartist INTEGER, '
84 'file VARCHAR(500), mbid CHAR(36), '
85 'FOREIGN KEY(artist) REFERENCES artists(id), '
86 'FOREIGN KEY(album) REFERENCES albums(id), '
87 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))')
88 connection.execute( # HISTORY
89 'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, '
90 'last_play TIMESTAMP, track INTEGER, '
91 'FOREIGN KEY(track) REFERENCES tracks(id))')
92 connection.execute( # BLOCKLIST
93 'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, '
94 'artist INTEGER, album INTEGER, track INTEGER, '
95 'FOREIGN KEY(artist) REFERENCES artists(id), '
96 'FOREIGN KEY(album) REFERENCES albums(id), '
97 'FOREIGN KEY(track) REFERENCES tracks(id))')
98 connection.execute( # Genres (Many-to-many)
99 'CREATE TABLE IF NOT EXISTS genres '
100 '(id INTEGER PRIMARY KEY, name VARCHAR(100))')
101 connection.execute( # Junction Genres Tracks
102 """CREATE TABLE IF NOT EXISTS tracks_genres
103 ( track INTEGER, genre INTEGER,
104 FOREIGN KEY(track) REFERENCES tracks(id)
105 FOREIGN KEY(genre) REFERENCES genres(id))""")
106 # Create cleanup triggers:
107 # DELETE history → Tracks / Tracks_genres tables
108 connection.execute('''
109 CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
110 AFTER DELETE ON history
111 WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
112 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
114 DELETE FROM tracks WHERE id = old.track;
115 DELETE FROM tracks_genres WHERE track = old.track;
118 # DELETE Tracks_Genres → Genres table
119 connection.execute('''
120 CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres
121 AFTER DELETE ON tracks_genres
122 WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0)
124 DELETE FROM genres WHERE id = old.genre;
127 # DELETE Tracks → Artists table
128 connection.execute('''
129 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
130 AFTER DELETE ON tracks
131 WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
132 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
134 DELETE FROM artists WHERE id = old.artist;
137 # DELETE Tracks → Albums table
138 connection.execute('''
139 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
140 AFTER DELETE ON tracks
141 WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
142 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
144 DELETE FROM albums WHERE id = old.album;
147 # DELETE Tracks → cleanup AlbumArtists table
148 connection.execute('''
149 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
150 AFTER DELETE ON tracks
151 WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
153 DELETE FROM albumartists WHERE id = old.albumartist;
156 # DELETE blocklist → Tracks table
157 connection.execute('''
158 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
159 AFTER DELETE ON blocklist
160 WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
161 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
163 DELETE FROM tracks WHERE id = old.track;
166 # DELETE blocklist → Artists table
167 # The "SELECT count(*) FROM blocklist" is useless,
168 # there can be only one blocklist.artist
169 connection.execute('''
170 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_artists
171 AFTER DELETE ON blocklist
172 WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
173 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
175 DELETE FROM artists WHERE id = old.artist;
178 # DELETE Tracks → Albums table
179 # The "SELECT count(*) FROM blocklist" is useless,
180 # there can be only one blocklist.album
181 connection.execute('''
182 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_albums
183 AFTER DELETE ON blocklist
184 WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
185 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
187 DELETE FROM albums WHERE id = old.album;
193 connection = self.get_database_connection()
194 rows = connection.execute(
195 "SELECT name FROM sqlite_master WHERE type='table'")
196 for r in rows.fetchall():
197 connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
200 def _remove_blocklist_id(self, blid, with_connection=None):
203 connection = with_connection
205 connection = self.get_database_connection()
206 connection = self.get_database_connection()
207 connection.execute('DELETE FROM blocklist'
208 ' WHERE blocklist.id = ?', (blid,))
210 if not with_connection:
213 def _get_album(self, album, connection):
215 return connection.execute(
216 "SELECT id FROM albums WHERE mbid = ?",
219 return connection.execute(
220 "SELECT id FROM albums WHERE name = ? AND mbid IS NULL",
223 def get_album(self, album, with_connection=None, add=True):
224 """get album information from the database.
225 if not in database insert new entry.
227 :param sima.lib.meta.Album album: album objet
228 :param sqlite3.Connection with_connection: SQLite connection
231 connection = with_connection
233 connection = self.get_database_connection()
234 rows = self._get_album(album, connection)
236 if not with_connection:
240 if not with_connection:
244 "INSERT INTO albums (name, mbid) VALUES (?, ?)",
245 (album.name, album.mbid))
247 rows = self._get_album(album, connection)
249 if not with_connection:
252 if not with_connection:
256 def _get_albumartist(self, artist, connection):
258 return connection.execute(
259 "SELECT id FROM albumartists WHERE mbid = ?",
262 return connection.execute(
263 "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL",
266 def get_albumartist(self, artist, with_connection=None, add=True):
267 """get albumartist information from the database.
268 if not in database insert new entry.
270 :param sima.lib.meta.Artist artist: artist
271 :param sqlite3.Connection with_connection: SQLite connection
274 connection = with_connection
276 connection = self.get_database_connection()
277 rows = self._get_albumartist(artist, connection)
279 if not with_connection:
283 if not with_connection:
287 "INSERT INTO albumartists (name, mbid) VALUES (?, ?)",
288 (artist.name, artist.mbid))
290 rows = self._get_albumartist(artist, connection)
292 if not with_connection:
295 if not with_connection:
298 def _get_artist(self, artist, connection):
300 return connection.execute(
301 "SELECT id FROM artists WHERE mbid = ?",
304 return connection.execute(
305 "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,))
307 def get_artist(self, artist, with_connection=None, add=True):
308 """get artist information from the database.
309 if not in database insert new entry.
311 :param sima.lib.meta.Artist artist: artist
312 :param sqlite3.Connection with_connection: SQLite connection
315 connection = with_connection
317 connection = self.get_database_connection()
318 rows = self._get_artist(artist, connection)
320 if not with_connection:
324 if not with_connection:
328 "INSERT INTO artists (name, mbid) VALUES (?, ?)",
329 (artist.name, artist.mbid))
331 rows = self._get_artist(artist, connection)
333 if not with_connection:
336 if not with_connection:
339 def get_genre(self, genre, with_connection=None, add=True):
340 """get genre from the database.
341 if not in database insert new entry.
343 :param str genre: genre as a string
344 :param sqlite3.Connection with_connection: SQLite connection
347 connection = with_connection
349 connection = self.get_database_connection()
350 rows = connection.execute(
351 "SELECT id FROM genres WHERE name = ?", (genre,))
353 if not with_connection:
357 if not with_connection:
361 "INSERT INTO genres (name) VALUES (?)", (genre,))
363 rows = connection.execute(
364 "SELECT id FROM genres WHERE name = ?", (genre,))
366 if not with_connection:
370 def get_track(self, track, with_connection=None, add=True):
371 """Get a track id from Tracks table, add if not existing,
372 :param sima.lib.track.Track track: track to use
373 :param bool add: add non existing track to database"""
375 raise SimaDBError('Got a track with no file attribute: %r' % track)
377 connection = with_connection
379 connection = self.get_database_connection()
380 rows = connection.execute(
381 "SELECT * FROM tracks WHERE file = ?", (track.file,))
383 if not with_connection:
386 if not add: # Not adding non existing track
387 if not with_connection:
390 # Get an artist record or None
392 art = Artist(name=track.artist, mbid=track.musicbrainz_artistid)
393 art_id = self.get_artist(art, with_connection=connection)
396 # Get an albumartist record or None
397 if track.albumartist:
398 albart = Artist(name=track.albumartist,
399 mbid=track.musicbrainz_albumartistid)
400 albart_id = self.get_albumartist(albart, with_connection=connection)
403 # Get an album record or None
405 alb = Album(name=track.album, mbid=track.musicbrainz_albumid)
406 alb_id = self.get_album(alb, with_connection=connection)
410 """INSERT INTO tracks (artist, albumartist, album, title, mbid, file)
411 VALUES (?, ?, ?, ?, ?, ?)""",
412 (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid,
415 # Add track id to junction tables
416 self._add_tracks_genres(track, connection)
417 rows = connection.execute(
418 "SELECT id FROM tracks WHERE file = ?", (track.file,))
420 if not with_connection:
423 if not with_connection:
427 def _add_tracks_genres(self, track, connection):
430 rows = connection.execute(
431 "SELECT id FROM tracks WHERE file = ?", (track.file,))
432 trk_id = rows.fetchone()[0]
433 for genre in track.genres:
435 gen_id = self.get_genre(genre)
436 connection.execute("""INSERT INTO tracks_genres (track, genre)
437 VALUES (?, ?)""", (trk_id, gen_id))
439 def add_history(self, track, date=None):
440 """Record last play date of track (ie. not a real play history).
441 :param track sima.lib.track.Track: track to add to history
442 :param date datetime.datetime: UTC datetime object (use "datetime.now(timezone.utc)" is not set)"""
444 date = datetime.now(timezone.utc)
445 connection = self.get_database_connection()
446 track_id = self.get_track(track, with_connection=connection)
447 rows = connection.execute("SELECT * FROM history WHERE track = ? ",
449 if not rows.fetchone():
450 connection.execute("INSERT INTO history (track) VALUES (?)",
452 connection.execute("UPDATE history SET last_play = ? "
453 " WHERE track = ?", (date, track_id,))
457 def purge_history(self, duration=__HIST_DURATION__):
458 """Remove old entries in history
459 :param duration int: Purge history record older than duration in hours
460 (defaults to __HIST_DURATION__)"""
461 connection = self.get_database_connection()
462 connection.execute("DELETE FROM history WHERE last_play"
463 " < datetime('now', '-%i hours')" % duration)
464 connection.execute('VACUUM')
468 def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__):
470 :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
472 date = datetime.now(timezone.utc) - timedelta(hours=duration)
473 connection = self.get_database_connection()
474 connection.row_factory = sqlite3.Row
475 rows = connection.execute("""
476 SELECT albums.name AS name,
478 artists.name as artist,
479 artists.mbid as artist_mbib,
480 albumartists.name as albumartist,
481 albumartists.mbid as albumartist_mbib
483 JOIN tracks ON history.track = tracks.id
484 LEFT OUTER JOIN albums ON tracks.album = albums.id
485 LEFT OUTER JOIN artists ON tracks.artist = artists.id
486 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
487 WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
488 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
492 if needle: # Here use artist instead of albumartist
493 if needle != Artist(name=vals.get('artist'),
494 mbid=vals.get('artist_mbib')):
496 # Use albumartist / MBIDs if possible to build album artist
497 if not vals.get('albumartist'):
498 vals['albumartist'] = vals.get('artist')
499 if not vals.get('albumartist_mbib'):
500 vals['albumartist_mbib'] = vals.get('artist_mbib')
501 artist = Artist(name=vals.get('albumartist'),
502 mbid=vals.pop('albumartist_mbib'))
503 album = Album(**vals, Artist=artist)
504 if hist and hist[-1] == album:
505 # remove consecutive dupes
511 def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
512 """Returns a list of Artist objects
513 :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.
514 :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
516 date = datetime.now(timezone.utc) - timedelta(hours=duration)
517 connection = self.get_database_connection()
518 connection.row_factory = sqlite3.Row
519 rows = connection.execute("""
520 SELECT artists.name AS name,
523 JOIN tracks ON history.track = tracks.id
524 LEFT OUTER JOIN artists ON tracks.artist = artists.id
525 WHERE history.last_play > ? AND artists.name NOT NULL
526 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
527 last = deque(maxlen=1)
530 artist = Artist(**row)
531 if last and last[0] == artist: # remove consecutive dupes
534 if needle and isinstance(needle, (Artist, str)):
536 hist.append(artist) # No need to go further
539 elif needle and getattr(needle, '__contains__'):
541 hist.append(artist) # No need to go further
547 def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
548 date = datetime.now(timezone.utc) - timedelta(hours=duration)
549 connection = self.get_database_connection()
550 rows = connection.execute("""
551 SELECT genres.name, artists.name
553 JOIN tracks ON history.track = tracks.id
554 LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id
555 LEFT OUTER JOIN artists ON tracks.artist = artists.id
556 LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre
557 WHERE history.last_play > ? AND genres.name NOT NULL
558 ORDER BY history.last_play DESC
559 """, (date.isoformat(' '),))
563 if len({g[0] for g in genres}) >= limit:
568 def fetch_history(self, artist=None, duration=__HIST_DURATION__):
569 """Fetches tracks history, more recent first
570 :param sima.lib.meta.Artist artist: limit history to this artist
571 :param int duration: How long ago to fetch history from
573 date = datetime.now(timezone.utc) - timedelta(hours=duration)
574 connection = self.get_database_connection()
575 connection.row_factory = sqlite3.Row
577 SELECT tracks.title, tracks.file, artists.name AS artist,
578 albumartists.name AS albumartist,
579 artists.mbid as musicbrainz_artistid,
580 albums.name AS album,
581 albums.mbid AS musicbrainz_albumid,
582 tracks.mbid as musicbrainz_trackid
584 JOIN tracks ON history.track = tracks.id
585 LEFT OUTER JOIN artists ON tracks.artist = artists.id
586 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
587 LEFT OUTER JOIN albums ON tracks.album = albums.id
588 WHERE history.last_play > ?
592 rows = connection.execute(sql+"""
594 ORDER BY history.last_play DESC""",
595 (date.isoformat(' '), artist.mbid))
597 rows = connection.execute(sql+"""
599 ORDER BY history.last_play DESC""",
600 (date.isoformat(' '), artist.name))
602 rows = connection.execute(sql+'ORDER BY history.last_play DESC',
603 (date.isoformat(' '),))
606 hist.append(Track(**row))
610 def get_bl_track(self, track, with_connection=None, add=True):
611 """Add a track to blocklist
612 :param sima.lib.track.Track track: Track object to add to blocklist
613 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
614 :param bool add: Default is to add a new record, set to False to fetch associated record"""
616 connection = with_connection
618 connection = self.get_database_connection()
619 track_id = self.get_track(track, with_connection=connection, add=add)
620 rows = connection.execute(
621 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
622 if not rows.fetchone():
624 if not with_connection:
627 connection.execute('INSERT INTO blocklist (track) VALUES (?)',
630 rows = connection.execute(
631 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
632 bl = rows.fetchone()[0]
633 if not with_connection:
637 def get_bl_album(self, album, with_connection=None, add=True):
638 """Add an album to blocklist
639 :param sima.lib.meta.Album: Album object to add to blocklist
640 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
641 :param bool add: Default is to add a new record, set to False to fetch associated record"""
643 connection = with_connection
645 connection = self.get_database_connection()
646 album_id = self.get_album(album, with_connection=connection, add=add)
647 rows = connection.execute(
648 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
649 if not rows.fetchone():
651 if not with_connection:
654 connection.execute('INSERT INTO blocklist (album) VALUES (?)',
657 rows = connection.execute(
658 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
659 bl = rows.fetchone()[0]
660 if not with_connection:
664 def get_bl_artist(self, artist, with_connection=None, add=True):
665 """Add an artist to blocklist
666 :param sima.lib.meta.Artist: Artist object to add to blocklist
667 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
668 :param bool add: Default is to add a new record, set to False to fetch associated record"""
670 connection = with_connection
672 connection = self.get_database_connection()
673 artist_id = self.get_artist(artist, with_connection=connection, add=add)
674 rows = connection.execute(
675 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
676 if not rows.fetchone():
679 connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
682 rows = connection.execute(
683 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
684 bl = rows.fetchone()[0]
685 if not with_connection:
690 connection = self.get_database_connection()
691 connection.row_factory = sqlite3.Row
692 rows = connection.execute("""SELECT artists.name AS artist,
693 artists.mbid AS musicbrainz_artist,
694 albums.name AS album,
695 albums.mbid AS musicbrainz_album,
696 tracks.title AS title,
697 tracks.mbid AS musicbrainz_title,
701 LEFT OUTER JOIN artists ON blocklist.artist = artists.id
702 LEFT OUTER JOIN albums ON blocklist.album = albums.id
703 LEFT OUTER JOIN tracks ON blocklist.track = tracks.id""")
704 res = [dict(row) for row in rows.fetchall()]
708 def delete_bl(self, track=None, album=None, artist=None):
709 if not (track or album or artist):
711 connection = self.get_database_connection()
714 blid = self.get_bl_track(track, with_connection=connection)
716 blid = self.get_bl_album(album, with_connection=connection)
718 blid = self.get_bl_artist(artist, with_connection=connection)
721 self._remove_blocklist_id(blid, with_connection=connection)
726 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8