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