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
24 #: Default history duration for both request and purge in hours
25 __HIST_DURATION__ = int(30 * 24)
29 from collections import deque
30 from datetime import (datetime, timedelta)
31 from datetime import timezone
34 from sima.lib.meta import Artist, Album
35 from sima.lib.track import Track
36 from sima.utils.utils import MPDSimaException
39 class SimaDBError(MPDSimaException):
48 def __init__(self, db_path=None):
49 self._db_path = db_path
51 def get_database_connection(self):
52 """get database reference"""
53 connection = sqlite3.connect(self._db_path, isolation_level=None)
57 connection = self.get_database_connection()
58 info = connection.execute("""SELECT * FROM db_info
59 WHERE name = "DB Version" LIMIT 1;""").fetchone()
66 connection = self.get_database_connection()
68 'CREATE TABLE IF NOT EXISTS db_info'
69 ' (name CHAR(50), value CHAR(50))')
70 connection.execute('''INSERT INTO db_info (name, value) SELECT ?, ?
72 ( SELECT 1 FROM db_info WHERE name = ? )''',
73 ('DB Version', __DB_VERSION__, 'DB Version'))
74 connection.execute( # ARTISTS
75 'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, '
76 'name VARCHAR(100), mbid CHAR(36))')
77 connection.execute( # ALBUMS
78 'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY, '
79 'name VARCHAR(100), mbid CHAR(36))')
80 connection.execute( # ALBUMARTISTS
81 'CREATE TABLE IF NOT EXISTS albumartists (id INTEGER PRIMARY KEY, '
82 'name VARCHAR(100), mbid CHAR(36))')
83 connection.execute( # TRACKS
84 'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY, '
85 'title VARCHAR(100), artist INTEGER, '
86 'album INTEGER, albumartist INTEGER, '
87 'file VARCHAR(500), mbid CHAR(36), '
88 'FOREIGN KEY(artist) REFERENCES artists(id), '
89 'FOREIGN KEY(album) REFERENCES albums(id), '
90 'FOREIGN KEY(albumartist) REFERENCES albumartists(id))')
91 connection.execute( # HISTORY
92 'CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY, '
93 'last_play TIMESTAMP, track INTEGER, '
94 'FOREIGN KEY(track) REFERENCES tracks(id))')
95 connection.execute( # BLOCKLIST
96 'CREATE TABLE IF NOT EXISTS blocklist (id INTEGER PRIMARY KEY, '
97 'artist INTEGER, album INTEGER, track INTEGER, '
98 'FOREIGN KEY(artist) REFERENCES artists(id), '
99 'FOREIGN KEY(album) REFERENCES albums(id), '
100 'FOREIGN KEY(track) REFERENCES tracks(id))')
101 connection.execute( # Genres (Many-to-many)
102 'CREATE TABLE IF NOT EXISTS genres '
103 '(id INTEGER PRIMARY KEY, name VARCHAR(100))')
104 connection.execute( # Junction Genres Tracks
105 """CREATE TABLE IF NOT EXISTS tracks_genres
106 ( track INTEGER, genre INTEGER,
107 FOREIGN KEY(track) REFERENCES tracks(id)
108 FOREIGN KEY(genre) REFERENCES genres(id))""")
109 # Create cleanup triggers:
110 # DELETE history → Tracks / Tracks_genres tables
111 connection.execute('''
112 CREATE TRIGGER IF NOT EXISTS del_history_cleanup_tracks
113 AFTER DELETE ON history
114 WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
115 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
117 DELETE FROM tracks WHERE id = old.track;
118 DELETE FROM tracks_genres WHERE track = old.track;
121 # DELETE Tracks_Genres → Genres table
122 connection.execute('''
123 CREATE TRIGGER IF NOT EXISTS del_tracks_genres_cleanup_genres
124 AFTER DELETE ON tracks_genres
125 WHEN ((SELECT count(*) FROM tracks_genres WHERE genre=old.genre) = 0)
127 DELETE FROM genres WHERE id = old.genre;
130 # DELETE Tracks → Artists table
131 connection.execute('''
132 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_artists
133 AFTER DELETE ON tracks
134 WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
135 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
137 DELETE FROM artists WHERE id = old.artist;
140 # DELETE Tracks → Albums table
141 connection.execute('''
142 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albums
143 AFTER DELETE ON tracks
144 WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
145 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
147 DELETE FROM albums WHERE id = old.album;
150 # DELETE Tracks → cleanup AlbumArtists table
151 connection.execute('''
152 CREATE TRIGGER IF NOT EXISTS del_tracks_cleanup_albumartists
153 AFTER DELETE ON tracks
154 WHEN ((SELECT count(*) FROM tracks WHERE albumartist=old.albumartist) = 0)
156 DELETE FROM albumartists WHERE id = old.albumartist;
159 # DELETE blocklist → Tracks table
160 connection.execute('''
161 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_tracks
162 AFTER DELETE ON blocklist
163 WHEN ((SELECT count(*) FROM history WHERE track=old.track) = 0 AND
164 (SELECT count(*) FROM blocklist WHERE track=old.track) = 0)
166 DELETE FROM tracks WHERE id = old.track;
169 # DELETE blocklist → Artists table
170 # The "SELECT count(*) FROM blocklist" is useless,
171 # there can be only one blocklist.artist
172 connection.execute('''
173 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_artists
174 AFTER DELETE ON blocklist
175 WHEN ((SELECT count(*) FROM tracks WHERE artist=old.artist) = 0 AND
176 (SELECT count(*) FROM blocklist WHERE artist=old.artist) = 0)
178 DELETE FROM artists WHERE id = old.artist;
181 # DELETE Tracks → Albums table
182 # The "SELECT count(*) FROM blocklist" is useless,
183 # there can be only one blocklist.album
184 connection.execute('''
185 CREATE TRIGGER IF NOT EXISTS del_blocklist_cleanup_albums
186 AFTER DELETE ON blocklist
187 WHEN ((SELECT count(*) FROM tracks WHERE album=old.album) = 0 AND
188 (SELECT count(*) FROM blocklist WHERE album=old.album) = 0)
190 DELETE FROM albums WHERE id = old.album;
196 connection = self.get_database_connection()
197 rows = connection.execute(
198 "SELECT name FROM sqlite_master WHERE type='table'")
199 for r in rows.fetchall():
200 connection.execute(f'DROP TABLE IF EXISTS {r[0]}')
203 def _remove_blocklist_id(self, blid, with_connection=None):
204 """Remove a blocklist id"""
206 connection = with_connection
208 connection = self.get_database_connection()
209 connection = self.get_database_connection()
210 connection.execute('DELETE FROM blocklist'
211 ' WHERE blocklist.id = ?', (blid,))
213 if not with_connection:
216 def _get_album(self, album, connection):
218 return connection.execute(
219 "SELECT id FROM albums WHERE mbid = ?",
222 return connection.execute(
223 "SELECT id FROM albums WHERE name = ? AND mbid IS NULL",
226 def get_album(self, album, with_connection=None, add=True):
227 """get album information from the database.
228 if not in database insert new entry.
230 :param sima.lib.meta.Album album: album objet
231 :param sqlite3.Connection with_connection: SQLite connection
234 connection = with_connection
236 connection = self.get_database_connection()
237 rows = self._get_album(album, connection)
239 if not with_connection:
243 if not with_connection:
247 "INSERT INTO albums (name, mbid) VALUES (?, ?)",
248 (album.name, album.mbid))
250 rows = self._get_album(album, connection)
252 if not with_connection:
255 if not with_connection:
259 def _get_albumartist(self, artist, connection):
261 return connection.execute(
262 "SELECT id FROM albumartists WHERE mbid = ?",
265 return connection.execute(
266 "SELECT id FROM albumartists WHERE name = ? AND mbid IS NULL",
269 def get_albumartist(self, artist, with_connection=None, add=True):
270 """get albumartist information from the database.
271 if not in database insert new entry.
273 :param sima.lib.meta.Artist artist: artist
274 :param sqlite3.Connection with_connection: SQLite connection
277 connection = with_connection
279 connection = self.get_database_connection()
280 rows = self._get_albumartist(artist, connection)
282 if not with_connection:
286 if not with_connection:
290 "INSERT INTO albumartists (name, mbid) VALUES (?, ?)",
291 (artist.name, artist.mbid))
293 rows = self._get_albumartist(artist, connection)
295 if not with_connection:
298 if not with_connection:
301 def _get_artist(self, artist, connection):
303 return connection.execute(
304 "SELECT id FROM artists WHERE mbid = ?",
307 return connection.execute(
308 "SELECT id FROM artists WHERE name = ? AND mbid IS NULL", (artist.name,))
310 def get_artist(self, artist, with_connection=None, add=True):
311 """get artist information from the database.
312 if not in database insert new entry.
314 :param sima.lib.meta.Artist artist: artist
315 :param sqlite3.Connection with_connection: SQLite connection
318 connection = with_connection
320 connection = self.get_database_connection()
321 rows = self._get_artist(artist, connection)
323 if not with_connection:
327 if not with_connection:
331 "INSERT INTO artists (name, mbid) VALUES (?, ?)",
332 (artist.name, artist.mbid))
334 rows = self._get_artist(artist, connection)
336 if not with_connection:
339 if not with_connection:
342 def get_genre(self, genre, with_connection=None, add=True):
343 """get genre from the database.
344 if not in database insert new entry.
346 :param str genre: genre as a string
347 :param sqlite3.Connection with_connection: SQLite connection
350 connection = with_connection
352 connection = self.get_database_connection()
353 rows = connection.execute(
354 "SELECT id FROM genres WHERE name = ?", (genre,))
356 if not with_connection:
360 if not with_connection:
364 "INSERT INTO genres (name) VALUES (?)", (genre,))
366 rows = connection.execute(
367 "SELECT id FROM genres WHERE name = ?", (genre,))
369 if not with_connection:
373 def get_track(self, track, with_connection=None, add=True):
374 """Get a track id from Tracks table, add if not existing,
376 :param sima.lib.track.Track track: track to use
377 :param bool add: add non existing track to database"""
379 raise SimaDBError('Got a track with no file attribute: %r' % track)
381 connection = with_connection
383 connection = self.get_database_connection()
384 rows = connection.execute(
385 "SELECT * FROM tracks WHERE file = ?", (track.file,))
387 if not with_connection:
390 if not add: # Not adding non existing track
391 if not with_connection:
394 # Get an artist record or None
396 art = Artist(name=track.artist, mbid=track.musicbrainz_artistid)
397 art_id = self.get_artist(art, with_connection=connection)
400 # Get an albumartist record or None
401 if track.albumartist:
402 albart = Artist(name=track.albumartist,
403 mbid=track.musicbrainz_albumartistid)
404 albart_id = self.get_albumartist(albart, with_connection=connection)
407 # Get an album record or None
409 alb = Album(name=track.album, mbid=track.musicbrainz_albumid)
410 alb_id = self.get_album(alb, with_connection=connection)
414 """INSERT INTO tracks (artist, albumartist, album, title, mbid, file)
415 VALUES (?, ?, ?, ?, ?, ?)""",
416 (art_id, albart_id, alb_id, track.title, track.musicbrainz_trackid,
419 # Add track id to junction tables
420 self._add_tracks_genres(track, connection)
421 rows = connection.execute(
422 "SELECT id FROM tracks WHERE file = ?", (track.file,))
424 if not with_connection:
427 if not with_connection:
431 def _add_tracks_genres(self, track, connection):
434 rows = connection.execute(
435 "SELECT id FROM tracks WHERE file = ?", (track.file,))
436 trk_id = rows.fetchone()[0]
437 for genre in track.genres:
439 gen_id = self.get_genre(genre)
440 connection.execute("""INSERT INTO tracks_genres (track, genre)
441 VALUES (?, ?)""", (trk_id, gen_id))
443 def add_history(self, track, date=None):
444 """Record last play date of track (ie. not a real play history).
446 :param sima.lib.track.Track track: track to add to history
447 :param datetime.datetime date: UTC datetime object (use "datetime.now(timezone.utc)" is not set)"""
449 date = datetime.now(timezone.utc)
450 connection = self.get_database_connection()
451 track_id = self.get_track(track, with_connection=connection)
452 rows = connection.execute("SELECT * FROM history WHERE track = ? ",
454 if not rows.fetchone():
455 connection.execute("INSERT INTO history (track) VALUES (?)",
457 connection.execute("UPDATE history SET last_play = ? "
458 " WHERE track = ?", (date, track_id,))
462 def purge_history(self, duration=__HIST_DURATION__):
463 """Remove old entries in history
465 :param int duration: Purge history record older than duration in hours"""
466 connection = self.get_database_connection()
467 connection.execute("DELETE FROM history WHERE last_play"
468 " < datetime('now', '-%i hours')" % duration)
469 connection.execute('VACUUM')
473 def fetch_albums_history(self, needle=None, duration=__HIST_DURATION__):
475 :param sima.lib.meta.Artist needle: When specified, returns albums history for this artist.
476 :param int duration: How long ago to fetch history from (in hours)
478 date = datetime.now(timezone.utc) - timedelta(hours=duration)
479 connection = self.get_database_connection()
480 connection.row_factory = sqlite3.Row
481 rows = connection.execute("""
482 SELECT albums.name AS name,
484 artists.name as artist,
485 artists.mbid as artist_mbib,
486 albumartists.name as albumartist,
487 albumartists.mbid as albumartist_mbib
489 JOIN tracks ON history.track = tracks.id
490 LEFT OUTER JOIN albums ON tracks.album = albums.id
491 LEFT OUTER JOIN artists ON tracks.artist = artists.id
492 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
493 WHERE history.last_play > ? AND albums.name NOT NULL AND artists.name NOT NULL
494 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
498 if needle: # Here use artist instead of albumartist
499 if needle != Artist(name=vals.get('artist'),
500 mbid=vals.get('artist_mbib')):
502 # Use albumartist / MBIDs if possible to build album artist
503 if not vals.get('albumartist'):
504 vals['albumartist'] = vals.get('artist')
505 if not vals.get('albumartist_mbib'):
506 vals['albumartist_mbib'] = vals.get('artist_mbib')
507 artist = Artist(name=vals.get('albumartist'),
508 mbid=vals.pop('albumartist_mbib'))
509 album = Album(**vals, Artist=artist)
510 if hist and hist[-1] == album:
511 # remove consecutive dupes
517 def fetch_artists_history(self, needle=None, duration=__HIST_DURATION__):
518 """Returns a list of Artist objects
520 :param sima.lib.meta.MetaContainer needle: When specified, returns history for these artists only
521 :param int duration: How long ago to fetch history from (in hours)
522 :type needle: sima.lib.meta.Artist or sima.lib.meta.MetaContainer
524 date = datetime.now(timezone.utc) - timedelta(hours=duration)
525 connection = self.get_database_connection()
526 connection.row_factory = sqlite3.Row
527 rows = connection.execute("""
528 SELECT artists.name AS name,
531 JOIN tracks ON history.track = tracks.id
532 LEFT OUTER JOIN artists ON tracks.artist = artists.id
533 WHERE history.last_play > ? AND artists.name NOT NULL
534 ORDER BY history.last_play DESC""", (date.isoformat(' '),))
535 last = deque(maxlen=1)
538 artist = Artist(**row)
539 if last and last[0] == artist: # remove consecutive dupes
542 if needle and isinstance(needle, (Artist, str)):
544 hist.append(artist) # No need to go further
547 elif needle and getattr(needle, '__contains__'):
549 hist.append(artist) # No need to go further
555 def fetch_genres_history(self, duration=__HIST_DURATION__, limit=20):
556 """Returns genre history
558 :param int duration: How long ago to fetch history from (in hours)
559 :param int limit: number of genre to fetch
561 date = datetime.now(timezone.utc) - timedelta(hours=duration)
562 connection = self.get_database_connection()
563 rows = connection.execute("""
564 SELECT genres.name, artists.name
566 JOIN tracks ON history.track = tracks.id
567 LEFT OUTER JOIN tracks_genres ON tracks_genres.track = tracks.id
568 LEFT OUTER JOIN artists ON tracks.artist = artists.id
569 LEFT OUTER JOIN genres ON genres.id = tracks_genres.genre
570 WHERE history.last_play > ? AND genres.name NOT NULL
571 ORDER BY history.last_play DESC
572 """, (date.isoformat(' '),))
576 if len({g[0] for g in genres}) >= limit:
581 def fetch_history(self, artist=None, duration=__HIST_DURATION__):
582 """Fetches tracks history, more recent first
584 :param sima.lib.meta.Artist artist: limit history to this artist
585 :param int duration: How long ago to fetch history from (in hours)
587 date = datetime.now(timezone.utc) - timedelta(hours=duration)
588 connection = self.get_database_connection()
589 connection.row_factory = sqlite3.Row
591 SELECT tracks.title, tracks.file, artists.name AS artist,
592 albumartists.name AS albumartist,
593 artists.mbid as musicbrainz_artistid,
594 albums.name AS album,
595 albums.mbid AS musicbrainz_albumid,
596 tracks.mbid as musicbrainz_trackid
598 JOIN tracks ON history.track = tracks.id
599 LEFT OUTER JOIN artists ON tracks.artist = artists.id
600 LEFT OUTER JOIN albumartists ON tracks.albumartist = albumartists.id
601 LEFT OUTER JOIN albums ON tracks.album = albums.id
602 WHERE history.last_play > ?
606 rows = connection.execute(sql+"""
608 ORDER BY history.last_play DESC""",
609 (date.isoformat(' '), artist.mbid))
611 rows = connection.execute(sql+"""
613 ORDER BY history.last_play DESC""",
614 (date.isoformat(' '), artist.name))
616 rows = connection.execute(sql+'ORDER BY history.last_play DESC',
617 (date.isoformat(' '),))
620 hist.append(Track(**row))
624 def get_bl_track(self, track, with_connection=None, add=True):
625 """Add a track to blocklist
627 :param sima.lib.track.Track track: Track object to add to blocklist
628 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
629 :param bool add: Default is to add a new record, set to False to fetch associated record"""
631 connection = with_connection
633 connection = self.get_database_connection()
634 track_id = self.get_track(track, with_connection=connection, add=add)
635 rows = connection.execute(
636 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
637 if not rows.fetchone():
639 if not with_connection:
642 connection.execute('INSERT INTO blocklist (track) VALUES (?)',
645 rows = connection.execute(
646 "SELECT id FROM blocklist WHERE track = ?", (track_id,))
647 bl = rows.fetchone()[0]
648 if not with_connection:
652 def get_bl_album(self, album, with_connection=None, add=True):
653 """Add an album to blocklist
655 :param sima.lib.meta.Album: Album object to add to blocklist
656 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
657 :param bool add: Default is to add a new record, set to False to fetch associated record"""
659 connection = with_connection
661 connection = self.get_database_connection()
662 album_id = self.get_album(album, with_connection=connection, add=add)
663 rows = connection.execute(
664 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
665 if not rows.fetchone():
667 if not with_connection:
670 connection.execute('INSERT INTO blocklist (album) VALUES (?)',
673 rows = connection.execute(
674 "SELECT id FROM blocklist WHERE album = ?", (album_id,))
675 bl = rows.fetchone()[0]
676 if not with_connection:
680 def get_bl_artist(self, artist, with_connection=None, add=True):
681 """Add an artist to blocklist
683 :param sima.lib.meta.Artist: Artist object to add to blocklist
684 :param sqlite3.Connection with_connection: sqlite3.Connection to reuse, else create a new one
685 :param bool add: Default is to add a new record, set to False to fetch associated record"""
687 connection = with_connection
689 connection = self.get_database_connection()
690 artist_id = self.get_artist(artist, with_connection=connection, add=add)
691 rows = connection.execute(
692 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
693 if not rows.fetchone():
696 connection.execute('INSERT INTO blocklist (artist) VALUES (?)',
699 rows = connection.execute(
700 "SELECT id FROM blocklist WHERE artist = ?", (artist_id,))
701 bl = rows.fetchone()[0]
702 if not with_connection:
707 connection = self.get_database_connection()
708 connection.row_factory = sqlite3.Row
709 rows = connection.execute("""SELECT artists.name AS artist,
710 artists.mbid AS musicbrainz_artist,
711 albums.name AS album,
712 albums.mbid AS musicbrainz_album,
713 tracks.title AS title,
714 tracks.mbid AS musicbrainz_title,
718 LEFT OUTER JOIN artists ON blocklist.artist = artists.id
719 LEFT OUTER JOIN albums ON blocklist.album = albums.id
720 LEFT OUTER JOIN tracks ON blocklist.track = tracks.id""")
721 res = [dict(row) for row in rows.fetchall()]
725 def delete_bl(self, track=None, album=None, artist=None):
726 if not (track or album or artist):
728 connection = self.get_database_connection()
731 blid = self.get_bl_track(track, with_connection=connection)
733 blid = self.get_bl_album(album, with_connection=connection)
735 blid = self.get_bl_artist(artist, with_connection=connection)
738 self._remove_blocklist_id(blid, with_connection=connection)
743 # vim: ai ts=4 sw=4 sts=4 expandtab fileencoding=utf8