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