# -*- coding: utf-8 -*-
-
+#
# Copyright (c) 2009-2013 Jack Kaliko <jack@azylum.org>
# Copyright (c) 2009, Eric Casteleijn <thisfred@gmail.com>
# Copyright (c) 2008 Rick van Hattem
# along with sima. If not, see <http://www.gnu.org/licenses/>.
#
#
+"""SQlite database library
+"""
# DOC:
# MuscicBrainz ID: <http://musicbrainz.org/doc/MusicBrainzIdentifier>
# <http://musicbrainz.org/doc/Same_Artist_With_Different_Names>
__DB_VERSION__ = 2
-__HIST_DURATION__ = int(7 * 24) # in hours
+__HIST_DURATION__ = int(30 * 24) # in hours
import sqlite3
self.db_path_mod_control()
def db_path_mod_control(self):
+ """Controls DB path access & write permissions"""
db_path = self._db_path
# Controls directory access
if not isdir(dirname(db_path)):
if not in database insert new entry."""
art = track.artist
nam = track.title
- fil = track.get_filename()
+ fil = track.file
if with_connection:
connection = with_connection
else:
"""
get album information from the database.
if not in database insert new entry.
- Attention: use Track() object!!
- Use AlbumArtist tag is provided, fallback to Album tag
+ Attention: use Track|Album object!!
+ Use AlbumArtist tag if provided, fallback to Album tag
"""
if with_connection:
connection = with_connection
def get_bl_album(self, track,
with_connection=None, add_not=None):
- """get blacklisted track information from the database."""
+ """get blacklisted album information from the database."""
if with_connection:
connection = with_connection
else:
self.close_database_connection(connection)
return False
+ def get_artists_history(self, artists, duration=__HIST_DURATION__):
+ """
+ """
+ date = datetime.utcnow() - timedelta(hours=duration)
+ connection = self.get_database_connection()
+ rows = connection.execute(
+ "SELECT arts.name, albs.name, trs.name, trs.file"
+ " FROM artists AS arts, tracks AS trs, history AS hist, albums AS albs"
+ " WHERE trs.id = hist.track AND trs.artist = arts.id AND trs.album = albs.id"
+ " AND hist.last_play > ? ORDER BY hist.last_play DESC", (date.isoformat(' '),))
+ for row in rows:
+ if artists and row[0] not in artists:
+ continue
+ for art in artists:
+ if row[0] == art:
+ yield art
+ self.close_database_connection(connection)
+
def get_history(self, artist=None, artists=None, duration=__HIST_DURATION__):
"""Retrieve complete play history, most recent tracks first
artist : filter history for specific artist
artists : filter history for specific artists list
- """
+ """ # pylint: disable=C0301
date = datetime.utcnow() - timedelta(hours=duration)
connection = self.get_database_connection()
if artist:
yield ('Row ID', 'Artist',)
for row in rows:
yield row
- rows = connection.execute('SELECT black_list.rowid, albums.name, artists.name'
+ rows = connection.execute(
+ 'SELECT black_list.rowid, albums.name, artists.name'
' FROM artists, albums INNER JOIN black_list'
' ON albums.id = black_list.album'
' WHERE artists.id = albums.artist')
yield ('Row ID', 'Album', 'Artist name')
for row in rows:
yield row
- rows = connection.execute('SELECT black_list.rowid, tracks.name, artists.name'
+ rows = connection.execute(
+ 'SELECT black_list.rowid, tracks.name, artists.name'
' FROM artists, tracks INNER JOIN black_list'
' ON tracks.id = black_list.track'
' WHERE tracks.artist = artists.id')
"""Add to history"""
connection = self.get_database_connection()
track_id = self.get_track(track, with_connection=connection)[0]
- rows = connection.execute("SELECT * FROM history WHERE track = ? ", (track_id,))
+ rows = connection.execute("SELECT * FROM history WHERE track = ? ",
+ (track_id,))
if not rows.fetchone():
- connection.execute("INSERT INTO history (track) VALUES (?)", (track_id,))
+ connection.execute("INSERT INTO history (track) VALUES (?)",
+ (track_id,))
connection.execute("UPDATE history SET last_play = DATETIME('now') "
" WHERE track = ?", (track_id,))
connection.commit()
"SELECT artist FROM albums")] +
[row[0] for row in connection.execute(
"SELECT artist FROM tracks")])
- orphans = [ (orphan,) for orphan in artists_ids - artist_2_artist_ids ]
+ orphans = [(orphan,) for orphan in artists_ids - artist_2_artist_ids]
connection.executemany('DELETE FROM artists WHERE id = (?);', orphans)
if not with_connection:
connection.commit()
"""SELECT albums.id FROM albums
LEFT JOIN tracks ON albums.id = tracks.album
WHERE tracks.album IS NULL""")])
- orphans = [ (orphan,) for orphan in orphan_black_ids & orphan_tracks_ids ]
+ orphans = [(orphan,) for orphan in orphan_black_ids & orphan_tracks_ids]
connection.executemany('DELETE FROM albums WHERE id = (?);', orphans)
if not with_connection:
connection.commit()
"""SELECT tracks.id FROM tracks
LEFT JOIN black_list ON tracks.id = black_list.track
WHERE black_list.track IS NULL""")])
- orphans = [ (orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids ]
+ orphans = [(orphan,) for orphan in hist_orphan_ids & black_list_orphan_ids]
connection.executemany('DELETE FROM tracks WHERE id = (?);', orphans)
if not with_connection:
connection.commit()
self.close_database_connection(connection)
def _set_dbversion(self):
+ """Add db version"""
connection = self.get_database_connection()
connection.execute('INSERT INTO db_info (version, name) VALUES (?, ?)',
(__DB_VERSION__, 'Sima DB'))
'CREATE TABLE IF NOT EXISTS history (last_play DATE,'
' track integer)')
connection.execute(
- "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)")
+ "CREATE INDEX IF NOT EXISTS a2aa1x ON usr_artist_2_artist (artist1)")
connection.execute(
- "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)")
+ "CREATE INDEX IF NOT EXISTS a2aa2x ON usr_artist_2_artist (artist2)")
connection.execute(
- "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)")
+ "CREATE INDEX IF NOT EXISTS lfma2aa1x ON lfm_artist_2_artist (artist1)")
connection.execute(
- "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)")
+ "CREATE INDEX IF NOT EXISTS lfma2aa2x ON lfm_artist_2_artist (artist2)")
connection.commit()
self.close_database_connection(connection)
self._set_dbversion()