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
481 JOIN tracks ON history.track = tracks.id
482 LEFT OUTER JOIN albums ON tracks.album = albums.id
483 LEFT OUTER JOIN artists ON tracks.artist = artists.id
484 WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
485 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
489 artist = Artist(name=vals.pop('artist'),
490 mbid=vals.pop('artist_mbib'))
494 album = Album(**vals, artist=artist)
495 if hist and hist[-1] == album:
496 # remove consecutive dupes
502 def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
503 """Returns a list of Artist objects
504 :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.
505 :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
507 date = datetime.now(timezone.utc) - timedelta(hours=duration)
508 connection = self.get_database_connection()
509 connection.row_factory = sqlite3.Row
510 rows = connection.execute("""
511 SELECT artists.name AS name,
514 JOIN tracks ON history.track = tracks.id
515 LEFT OUTER JOIN artists ON tracks.artist = artists.id
516 WHERE history.last_play > ? AND artists.name NOT NULL
517 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
518 last = deque(maxlen=1)
521 artist = Artist(**row)
522 if last and last[0] == artist: # remove consecutive dupes
525 if needle and isinstance(needle, (Artist, str)):
527 hist.append(artist) # No need to go further
530 elif needle and getattr(needle, '__contains__'):
532 hist.append(artist) # No need to go further
538 def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
539 date = datetime.now(timezone.utc) - timedelta(hours=duration)
540 connection = self.get_database_connection()
541 rows = connection.execute("""
542 SELECT genres.name, artists.name
544 JOIN tracks ON history.track = tracks.id
545 LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id
546 LEFT OUTER JOIN artists ON tracks.artist = artists.id
547 LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre
548 WHERE history.last_play > ? AND genres.name NOT NULL
549 ORDER BY history.last_play DESC
550 """, (date.isoformat(' '),))
554 if len({g[0] for g in genres}) >= limit:
559 def fetch_history(self, artist=None, duration=__HIST_DURATION__):
560 """Fetches tracks history, more recent first
561 :param sima.lib.meta.Artist artist: limit history to this artist
562 :param int duration: How long ago to fetch history from
564 date = datetime.now(timezone.utc) - timedelta(hours=duration)
565 connection = self.get_database_connection()
566 connection.row_factory = sqlite3.Row
568 SELECT tracks.title, tracks.file, artists.name AS artist,
569 albumartists.name AS albumartist,
570 artists.mbid as musicbrainz_artistid,
571 albums.name AS album,
572 albums.mbid AS musicbrainz_albumid,
573 tracks.mbid as musicbrainz_trackid
575 JOIN tracks ON history.track = tracks.id
576 LEFT OUTER JOIN artists ON tracks.artist = artists.id
577 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
578 LEFT OUTER JOIN albums ON tracks.album = albums.id
579 WHERE history.last_play > ?
583 rows = connection.execute(sql+"""
585 ORDER BY history.last_play DESC""",
586 (date.isoformat(' '), artist.mbid))
588 rows = connection.execute(sql+"""
590 ORDER BY history.last_play DESC""",
591 (date.isoformat(' '), artist.name))
593 rows = connection.execute(sql+'ORDER BY history.last_play DESC',
594 (date.isoformat(' '),))
597 hist.append(Track(**row))
601 def get_bl_track(self, track, with_connection=None, add=True):
602 """Add a track to blocklist
603 :param sima.lib.track.Track track: Track object to add to blocklist
604 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
605 :param bool add: Default is to add a new record, set to False to fetch associated record"""
607 connection = with_connection
609 connection = self.get_database_connection()
610 track_id = self.get_track(track, with_connection=connection, add=add)
611 rows = connection.execute(
612 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
613 if not rows.fetchone():
615 if not with_connection:
618 connection.execute('INSERT INTO blocklist (track) VALUES (?)',
621 rows = connection.execute(
622 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
623 bl = rows.fetchone()[0]
624 if not with_connection:
628 def get_bl_album(self, album, with_connection=None, add=True):
629 """Add an album to blocklist
630 :param sima.lib.meta.Album: Album object to add to blocklist
631 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
632 :param bool add: Default is to add a new record, set to False to fetch associated record"""
634 connection = with_connection
636 connection = self.get_database_connection()
637 album_id = self.get_album(album, with_connection=connection, add=add)
638 rows = connection.execute(
639 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
640 if not rows.fetchone():
642 if not with_connection:
645 connection.execute('INSERT INTO blocklist (album) VALUES (?)',
648 rows = connection.execute(
649 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
650 bl = rows.fetchone()[0]
651 if not with_connection:
655 def get_bl_artist(self, artist, with_connection=None, add=True):
656 """Add an artist to blocklist
657 :param sima.lib.meta.Artist: Artist object to add to blocklist
658 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
659 :param bool add: Default is to add a new record, set to False to fetch associated record"""
661 connection = with_connection
663 connection = self.get_database_connection()
664 artist_id = self.get_artist(artist, with_connection=connection, add=add)
665 rows = connection.execute(
666 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
667 if not rows.fetchone():
670 connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
673 rows = connection.execute(
674 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
675 bl = rows.fetchone()[0]
676 if not with_connection:
681 connection = self.get_database_connection()
682 connection.row_factory = sqlite3.Row
683 rows = connection.execute("""SELECT artists.name AS artist,
684 artists.mbid AS musicbrainz_artist,
685 albums.name AS album,
686 albums.mbid AS musicbrainz_album,
687 tracks.title AS title,
688 tracks.mbid AS musicbrainz_title,
692 LEFT OUTER JOIN artists ON blocklist.artist = artists.id
693 LEFT OUTER JOIN albums ON blocklist.album = albums.id
694 LEFT OUTER JOIN tracks ON blocklist.track = tracks.id""")
695 res = [dict(row) for row in rows.fetchall()]
699 def delete_bl(self, track=None, album=None, artist=None):
700 if not (track or album or artist):
702 connection = self.get_database_connection()
705 blid = self.get_bl_track(track, with_connection=connection)
707 blid = self.get_bl_album(album, with_connection=connection)
709 blid = self.get_bl_artist(artist, with_connection=connection)
712 self._remove_blocklist_id(blid, with_connection=connection)
717 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8