]> kaliko git repositories - mpd-sima.git/blob - sima/lib/simadb.py
Fixed crashes with untagged title (closes #40)
[mpd-sima.git] / sima / lib / simadb.py
1 # -*- coding: utf-8 -*-
2 #
3 # Copyright (c) 2009-2013, 2019-2020 kaliko <kaliko@azylum.org>
4 # Copyright (c) 2009, Eric Casteleijn <thisfred@gmail.com>
5 # Copyright (c) 2008 Rick van Hattem
6 #
7 #  This file is part of sima
8 #
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.
13 #
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.
18 #
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/>.
21 #
22 #
23 """SQlite database library
24 """
25
26 #    DOC:
27 #    MuscicBrainz ID: <http://musicbrainz.org/doc/MusicBrainzIdentifier>
28 #    Artists: <http://musicbrainz.org/doc/Artist_Name>
29 #             <http://musicbrainz.org/doc/Same_Artist_With_Different_Names>
30
31 __DB_VERSION__ = 3
32 __HIST_DURATION__ = int(30 * 24)  # in hours
33
34 import sqlite3
35
36 from datetime import (datetime, timedelta)
37 from os.path import dirname, isdir
38 from os import (access, W_OK, F_OK)
39
40
41 class SimaDBError(Exception):
42     """
43     Exceptions.
44     """
45
46
47 class SimaDBAccessError(SimaDBError):
48     """Error on accessing DB file"""
49
50
51 class SimaDBNoFile(SimaDBError):
52     """No DB file present"""
53
54
55 class SimaDB:
56     "SQLite management"
57
58     def __init__(self, db_path=None):
59         self._db_path = db_path
60         self.db_path_mod_control()
61
62     def db_path_mod_control(self):
63         """Controls DB path access & write permissions"""
64         db_path = self._db_path
65         # Controls directory access
66         if not isdir(dirname(db_path)):
67             raise SimaDBAccessError('Not a regular directory: "%s"' %
68                                     dirname(db_path))
69         if not access(dirname(db_path), W_OK):
70             raise SimaDBAccessError('No write access to "%s"' % dirname(db_path))
71         # Is a file but no write access
72         if access(db_path, F_OK) and not access(db_path, W_OK | F_OK):
73             raise SimaDBAccessError('No write access to "%s"' % db_path)
74         # No file
75         if not access(db_path, F_OK):
76             raise SimaDBNoFile('No DB file in "%s"' % db_path)
77
78     def close_database_connection(self, connection):
79         """Close the database connection."""
80         connection.close()
81
82     def get_database_connection(self):
83         """get database reference"""
84         connection = sqlite3.connect(
85             self._db_path, timeout=5.0, isolation_level=None)
86         #connection.text_factory = str
87         return connection
88
89     def get_artist(self, artist_name, mbid=None,
90                    with_connection=None, add_not=False):
91         """get artist information from the database.
92         if not in database insert new entry."""
93         if with_connection:
94             connection = with_connection
95         else:
96             connection = self.get_database_connection()
97         rows = connection.execute(
98             "SELECT * FROM artists WHERE name = ?", (artist_name,))
99         for row in rows:
100             if not with_connection:
101                 self.close_database_connection(connection)
102             return row
103         if add_not:
104             if not with_connection:
105                 self.close_database_connection(connection)
106             return False
107         connection.execute(
108             "INSERT INTO artists (name, mbid) VALUES (?, ?)",
109             (artist_name, mbid))
110         connection.commit()
111         rows = connection.execute(
112             "SELECT * FROM artists WHERE name = ?", (artist_name,))
113         for row in rows:
114             if not with_connection:
115                 self.close_database_connection(connection)
116             return row
117         if not with_connection:
118             self.close_database_connection(connection)
119
120     def get_track(self, track, with_connection=None, add_not=False):
121         """
122         Get a track from Tracks table, add if not existing,
123         Attention: use Track() object!!
124         if not in database insert new entry."""
125         art = track.artist
126         nam = track.title
127         fil = track.file
128         if with_connection:
129             connection = with_connection
130         else:
131             connection = self.get_database_connection()
132         art_id = self.get_artist(art, with_connection=connection)[0]
133         alb_id = self.get_album(track, with_connection=connection)[0]
134         rows = connection.execute(
135             "SELECT * FROM tracks WHERE name = ? AND"
136             " artist = ? AND file = ?", (nam, art_id, fil))
137         for row in rows:
138             if not with_connection:
139                 self.close_database_connection(connection)
140             return row
141         if add_not:
142             return False
143         connection.execute(
144             "INSERT INTO tracks (artist, album, name, file) VALUES (?, ?, ?, ?)",
145             (art_id, alb_id, nam, fil))
146         connection.commit()
147         rows = connection.execute(
148             "SELECT * FROM tracks WHERE name = ? AND"
149             " artist = ? AND album = ? AND file = ?",
150             (nam, art_id, alb_id, fil,))
151         for row in rows:
152             if not with_connection:
153                 self.close_database_connection(connection)
154             return row
155         if not with_connection:
156             connection.commit()
157             self.close_database_connection(connection)
158
159     def get_album(self, track, mbid=None,
160                   with_connection=None, add_not=False):
161         """
162         get album information from the database.
163         if not in database insert new entry.
164         Attention: use Track|Album object!!
165         Use AlbumArtist tag if provided, fallback to Album tag
166         """
167         if with_connection:
168             connection = with_connection
169         else:
170             connection = self.get_database_connection()
171         if track.albumartist:
172             artist = track.albumartist
173         else:
174             artist = track.artist
175         art_id = self.get_artist(artist, with_connection=connection)[0]
176         album = track.album
177         rows = connection.execute(
178             "SELECT * FROM albums WHERE name = ? AND artist = ?",
179             (album, art_id))
180         for row in rows:
181             if not with_connection:
182                 self.close_database_connection(connection)
183             return row
184         if add_not:
185             return False
186         connection.execute(
187             "INSERT INTO albums (name, artist, mbid) VALUES (?, ?, ?)",
188             (album, art_id, mbid))
189         connection.commit()
190         rows = connection.execute(
191             "SELECT * FROM albums WHERE name = ? AND artist = ?",
192             (album, art_id))
193         for row in rows:
194             if not with_connection:
195                 self.close_database_connection(connection)
196             return row
197         if not with_connection:
198             self.close_database_connection(connection)
199
200     def get_artists(self, with_connection=None):
201         """Returns all artists in DB"""
202         if with_connection:
203             connection = with_connection
204         else:
205             connection = self.get_database_connection()
206         rows = connection.execute("SELECT name FROM artists ORDER BY name")
207         results = [row for row in rows]
208         if not with_connection:
209             self.close_database_connection(connection)
210         for artist in results:
211             yield artist
212
213     def get_bl_artist(self, artist_name,
214                       with_connection=None, add_not=None):
215         """get blacklisted artist information from the database."""
216         if with_connection:
217             connection = with_connection
218         else:
219             connection = self.get_database_connection()
220         art = self.get_artist(artist_name, with_connection=connection,
221                               add_not=add_not)
222         if not art:
223             return False
224         art_id = art[0]
225         rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
226                                   (art_id,))
227         for row in rows:
228             if not with_connection:
229                 self.close_database_connection(connection)
230             return row
231         if add_not:
232             if not with_connection:
233                 self.close_database_connection(connection)
234             return False
235         connection.execute("INSERT INTO black_list (artist) VALUES (?)",
236                            (art_id,))
237         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
238                            " WHERE artist = ?", (art_id,))
239         connection.commit()
240         rows = connection.execute("SELECT * FROM black_list WHERE artist = ?",
241                                   (art_id,))
242         for row in rows:
243             if not with_connection:
244                 self.close_database_connection(connection)
245             return row
246         if not with_connection:
247             self.close_database_connection(connection)
248         return False
249
250     def get_bl_album(self, track, with_connection=None, add_not=None):
251         """get blacklisted album information from the database."""
252         if with_connection:
253             connection = with_connection
254         else:
255             connection = self.get_database_connection()
256         album = self.get_album(track, with_connection=connection,
257                                add_not=add_not)
258         if not album:
259             return False
260         alb_id = album[0]
261         rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
262                                   (alb_id,))
263         for row in rows:
264             if not with_connection:
265                 self.close_database_connection(connection)
266             return row
267         if add_not:
268             if not with_connection:
269                 self.close_database_connection(connection)
270             return False
271         connection.execute("INSERT INTO black_list (album) VALUES (?)",
272                            (alb_id,))
273         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
274                            " WHERE album = ?", (alb_id,))
275         connection.commit()
276         rows = connection.execute("SELECT * FROM black_list WHERE album = ?",
277                                   (alb_id,))
278         for row in rows:
279             if not with_connection:
280                 self.close_database_connection(connection)
281             return row
282         if not with_connection:
283             self.close_database_connection(connection)
284         return False
285
286     def get_bl_track(self, track, with_connection=None, add_not=None):
287         """get blacklisted track information from the database."""
288         if with_connection:
289             connection = with_connection
290         else:
291             connection = self.get_database_connection()
292         track = self.get_track(track, with_connection=connection,
293                                add_not=add_not)
294         if not track:
295             return False
296         track_id = track[0]
297         rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
298                                   (track_id,))
299         for row in rows:
300             if not with_connection:
301                 self.close_database_connection(connection)
302             return row
303         if add_not:
304             if not with_connection:
305                 self.close_database_connection(connection)
306             return False
307         connection.execute("INSERT INTO black_list (track) VALUES (?)",
308                            (track_id,))
309         connection.execute("UPDATE black_list SET updated = DATETIME('now')"
310                            " WHERE track = ?", (track_id,))
311         connection.commit()
312         rows = connection.execute("SELECT * FROM black_list WHERE track = ?",
313                                   (track_id,))
314         for row in rows:
315             if not with_connection:
316                 self.close_database_connection(connection)
317             return row
318         if not with_connection:
319             self.close_database_connection(connection)
320         return False
321
322     def get_artists_history(self, artists, duration=__HIST_DURATION__):
323         """
324         :param list artists: list of object that can evaluate equality with
325                              artist name, iterable of str or Artist object
326         """
327         date = datetime.utcnow() - timedelta(hours=duration)
328         connection = self.get_database_connection()
329         rows = connection.execute(
330             "SELECT arts.name, albs.name, trs.name, trs.file"
331             " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
332             " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
333             " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
334         for row in rows:
335             if artists and row[0] not in artists:
336                 continue
337             for art in artists:
338                 if row[0] == art:
339                     yield art
340         self.close_database_connection(connection)
341
342     def get_history(self, artist=None, artists=None, duration=__HIST_DURATION__):
343         """Retrieve complete play history, most recent tracks first
344         artist  : filter history for specific artist
345         artists : filter history for specific artists list
346         """ # pylint: disable=C0301
347         date = datetime.utcnow() - timedelta(hours=duration)
348         connection = self.get_database_connection()
349         if artist:
350             rows = connection.execute(
351                 "SELECT arts.name, albs.name, trs.name, trs.file, hist.last_play"
352                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
353                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
354                 " AND hist.last_play > ? AND arts.name = ?"
355                 " ORDER BY hist.last_play DESC", (date.isoformat(' '), artist,))
356         else:
357             rows = connection.execute(
358                 "SELECT arts.name, albs.name, trs.name, trs.file"
359                 " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
360                 " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
361                 " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
362         for row in rows:
363             if artists and row[0] not in artists:
364                 continue
365             yield row
366         self.close_database_connection(connection)
367
368     def get_black_list(self):
369         """Retrieve complete black list."""
370         connection = self.get_database_connection()
371         rows = connection.execute('SELECT black_list.rowid, artists.name'
372                                   ' FROM artists INNER JOIN black_list'
373                                   ' ON artists.id = black_list.artist')
374         yield ('Row ID', 'Actual black listed element', 'Extra information',)
375         yield ('',)
376         yield ('Row ID', 'Artist',)
377         for row in rows:
378             yield row
379         rows = connection.execute(
380                 'SELECT black_list.rowid, albums.name, artists.name'
381                 ' FROM artists, albums INNER JOIN black_list'
382                 ' ON albums.id = black_list.album'
383                 ' WHERE artists.id = albums.artist')
384         yield ('',)
385         yield ('Row ID', 'Album', 'Artist name')
386         for row in rows:
387             yield row
388         rows = connection.execute(
389                 'SELECT black_list.rowid, tracks.name, artists.name'
390                 ' FROM artists, tracks INNER JOIN black_list'
391                 ' ON tracks.id = black_list.track'
392                 ' WHERE tracks.artist = artists.id')
393         yield ('',)
394         yield ('Row ID', 'Title', 'Artist name')
395         for row in rows:
396             yield row
397         self.close_database_connection(connection)
398
399     def _set_mbid(self, artist_id=None, mbid=None, with_connection=None):
400         """"""
401         if with_connection:
402             connection = with_connection
403         else:
404             connection = self.get_database_connection()
405         connection.execute("UPDATE artists SET mbid = ? WHERE id = ?",
406                            (mbid, artist_id))
407         connection.commit()
408         if not with_connection:
409             self.close_database_connection(connection)
410
411     def _remove_bl(self, rowid):
412         """Remove bl row id"""
413         connection = self.get_database_connection()
414         connection.execute('DELETE FROM black_list'
415                            ' WHERE black_list.rowid = ?', (rowid,))
416         connection.commit()
417         self.close_database_connection(connection)
418
419     def add_history(self, track):
420         """Add to history"""
421         connection = self.get_database_connection()
422         track_id = self.get_track(track, with_connection=connection)[0]
423         rows = connection.execute("SELECT * FROM history WHERE track = ? ",
424                                   (track_id,))
425         if not rows.fetchone():
426             connection.execute("INSERT INTO history (track) VALUES (?)",
427                                (track_id,))
428         connection.execute("UPDATE history SET last_play = DATETIME('now') "
429                            " WHERE track = ?", (track_id,))
430         connection.commit()
431         self.close_database_connection(connection)
432
433     def _clean_artists_table(self, with_connection=None):
434         """Clean orphan artists"""
435         if with_connection:
436             connection = with_connection
437         else:
438             connection = self.get_database_connection()
439         artists_ids = {row[0] for row in connection.execute(
440             "SELECT id FROM artists")}
441         artist_2_artist_ids = {row[0] for row in connection.execute(
442             "SELECT artist FROM black_list")} | {
443                 row[0] for row in connection.execute(
444                     "SELECT artist FROM albums")} | {
445                         row[0] for row in connection.execute(
446                             "SELECT artist FROM tracks")}
447         orphans = [(orphan,) for orphan in artists_ids - artist_2_artist_ids]
448         connection.executemany('DELETE FROM artists WHERE id = (?);', orphans)
449         if not with_connection:
450             connection.commit()
451             self.close_database_connection(connection)
452
453     def _clean_albums_table(self, with_connection=None):
454         """Clean orphan albums"""
455         if with_connection:
456             connection = with_connection
457         else:
458             connection = self.get_database_connection()
459         orphan_black_ids = {row[0] for row in connection.execute(
460             """SELECT albums.id FROM albums
461             LEFT JOIN black_list ON albums.id = black_list.album
462             WHERE ( black_list.album IS NULL )""")}
463         orphan_tracks_ids = {row[0] for row in connection.execute(
464             """SELECT albums.id FROM albums
465             LEFT JOIN tracks ON albums.id = tracks.album
466             WHERE tracks.album IS NULL""")}
467         orphans = [(orphan,) for orphan in orphan_black_ids & orphan_tracks_ids]
468         connection.executemany('DELETE FROM albums WHERE id = (?);', orphans)
469         if not with_connection:
470             connection.commit()
471             self.close_database_connection(connection)
472
473     def _clean_tracks_table(self, with_connection=None):
474         """Clean orphan tracks"""
475         if with_connection:
476             connection = with_connection
477         else:
478             connection = self.get_database_connection()
479         hist_orphan_ids = {row[0] for row in connection.execute(
480             """SELECT tracks.id FROM tracks
481             LEFT JOIN history ON tracks.id = history.track
482             WHERE history.track IS NULL""")}
483         black_list_orphan_ids = {row[0] for row in connection.execute(
484             """SELECT tracks.id FROM tracks
485             LEFT JOIN black_list ON tracks.id = black_list.track
486             WHERE black_list.track IS NULL""")}
487         orphans = [(orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids]
488         connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans)
489         if not with_connection:
490             connection.commit()
491             self.close_database_connection(connection)
492
493     def clean_database(self, with_connection=None):
494         """Wrapper around _clean_* methods"""
495         if with_connection:
496             connection = with_connection
497         else:
498             connection = self.get_database_connection()
499         self._clean_tracks_table(with_connection=connection)
500         self._clean_albums_table(with_connection=connection)
501         self._clean_artists_table(with_connection=connection)
502         connection.execute("VACUUM")
503         if not with_connection:
504             connection.commit()
505             self.close_database_connection(connection)
506
507     def purge_history(self, duration=__HIST_DURATION__):
508         """Remove old entries in history"""
509         connection = self.get_database_connection()
510         connection.execute("DELETE FROM history WHERE last_play"
511                            " < datetime('now', '-%i hours')" % duration)
512         connection.commit()
513         self.close_database_connection(connection)
514
515     def _set_dbversion(self):
516         """Add db version"""
517         connection = self.get_database_connection()
518         connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)',
519                            (__DB_VERSION__, 'Sima DB'))
520         connection.commit()
521         self.close_database_connection(connection)
522
523     def create_db(self):
524         """ Set up a database
525         """
526         connection = self.get_database_connection()
527         connection.execute(
528             'CREATE TABLE IF NOT EXISTS db_info'
529             ' (version INTEGER, name CHAR(36))')
530         connection.execute(
531             'CREATE TABLE IF NOT EXISTS artists (id INTEGER PRIMARY KEY, name'
532             ' VARCHAR(100), mbid CHAR(36))')
533         connection.execute(
534             'CREATE TABLE IF NOT EXISTS albums (id INTEGER PRIMARY KEY,'
535             ' artist INTEGER, name VARCHAR(100), mbid CHAR(36))')
536         connection.execute(
537             'CREATE TABLE IF NOT EXISTS tracks (id INTEGER PRIMARY KEY,'
538             ' name VARCHAR(100), artist INTEGER, album INTEGER,'
539             ' file VARCHAR(500), mbid CHAR(36))')
540         connection.execute(
541             'CREATE TABLE IF NOT EXISTS black_list (artist INTEGER,'
542             ' album INTEGER, track INTEGER, updated DATE)')
543         connection.execute(
544             'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
545             ' track integer)')
546         connection.commit()
547         self.close_database_connection(connection)
548         self._set_dbversion()
549
550
551 # VIM MODLINE
552 # vim: ai ts=4 sw=4 sts=4 expandtab