def get_local(self):
"""
Get all available local artists
@return Array of (artist id as int, artist name as string)
"""
with SqlCursor(Lp().db) as sql:
result = []
result = sql.execute(
"SELECT DISTINCT artists.rowid,\
artists.name, artists.sortname\
FROM artists, albums, album_artists\
WHERE album_artists.artist_id=artists.rowid\
AND album_artists.album_id=albums.rowid\
AND albums.synced!=?\
ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED", (Type.NONE, ))
return [(row[0], row[1], row[2]) for row in result]
def get_uris(self, exclude=[]):
"""
Get all tracks uri
@param exclude as [str]
@return Array of uri as string
"""
with SqlCursor(Lp().db) as sql:
filters = (DbPersistent.INTERNAL, )
for e in exclude:
filters += ("%" + e + "%", )
request = "SELECT uri FROM tracks\
WHERE persistent=?"
for e in exclude:
request += " AND uri not like ?"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def execute(self, request):
"""
Execute SQL request (only smart one)
@param request as str
@return list
"""
with SqlCursor(App().db) as sql:
result = sql.execute(request)
# Special case for OR request
if request.find("ORDER BY random()") == -1 and\
request.find("UNION") != -1:
ids = []
for (id, other) in list(result):
ids.append(id)
return ids
else:
return list(itertools.chain(*result))
def set_more_popular(self, track_id):
"""
Increment popularity field
@param track_id as int
@raise sqlite3.OperationalError on db update
"""
with SqlCursor(App().db, True) as sql:
result = sql.execute("SELECT popularity from tracks WHERE rowid=?",
(track_id,))
pop = result.fetchone()
if pop:
current = pop[0]
else:
current = 0
current += 1
sql.execute("UPDATE tracks set popularity=? WHERE rowid=?",
(current, track_id))
def get_year_from_tracks(self, album_id):
"""
Get album year based on tracks
Use most used year by tracks
@param album id as int
"""
with SqlCursor(Lp().db) as sql:
result = sql.execute("SELECT year, COUNT(year) AS occurrence\
FROM tracks\
WHERE tracks.album_id=?\
GROUP BY year\
ORDER BY occurrence DESC\
LIMIT 1", (album_id,))
v = result.fetchone()
if v is not None:
return v[0]
return None
def get_uris(self, exclude=[]):
"""
Get all tracks uri
@param exclude as [str]
@return Array of uri as string
"""
with SqlCursor(Lp().db) as sql:
filters = ()
for e in exclude:
filters += ('%' + e + '%', )
request = "SELECT uri FROM tracks"
if exclude:
request += " WHERE uri not like ?"
for e in exclude[1:]:
request += " AND uri not like ?"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def search(self, searched, storage_type):
"""
Search for artists looking like searched
@param searched as str without accents
@param storage_type as StorageType
@return artist ids as [int]
"""
with SqlCursor(self.__db) as sql:
filters = ("%" + searched + "%", storage_type)
request = "SELECT DISTINCT artists.rowid, artists.name\
FROM albums, album_artists, artists\
WHERE album_artists.artist_id=artists.rowid AND\
album_artists.album_id=albums.rowid AND\
noaccents(artists.name) LIKE ? AND\
albums.storage_type & ? LIMIT 25"
result = sql.execute(request, filters)
return list(result)
def add(self, name, sortname, mb_artist_id):
"""
Add a new artist to database
@param name as string
@param sortname as string
@param mb_artist_id as str
@return inserted rowid as int
@warning: commit needed
"""
if sortname == "":
sortname = format_artist_name(name)
with SqlCursor(self.__db, True) as sql:
result = sql.execute(
"INSERT INTO artists (name, sortname,\
mb_artist_id)\
VALUES (?, ?, ?)",
(name, sortname, mb_artist_id))
return result.lastrowid
def update_featuring(self):
"""
Calculate featuring for current DB
"""
with SqlCursor(self.__db, True) as sql:
sql.execute("DELETE FROM featuring")
result = sql.execute(
"SELECT track_artists.artist_id, tracks.album_id\
FROM tracks, track_artists\
WHERE track_artists.track_id = tracks.rowid\
AND NOT EXISTS (\
SELECT * FROM album_artists WHERE\
album_artists.album_id = tracks.album_id AND\
album_artists.artist_id = track_artists.artist_id)")
for (artist_id, album_id) in result:
sql.execute(
"INSERT INTO featuring (artist_id, album_id)\
VALUES (?, ?)", (artist_id, album_id))
def search(self, searched):
"""
Search for albums looking like string
@param searched as str
@return album ids as [int]
"""
no_accents = noaccents(searched)
with SqlCursor(App().db) as sql:
items = []
for filter in [(no_accents + "%",),
("%" + no_accents,),
("%" + no_accents + "%",)]:
request = "SELECT albums.rowid FROM albums\
WHERE noaccents(name) LIKE ?\
AND albums.mtime!=0 LIMIT 25"
result = sql.execute(request, filter)
items += list(itertools.chain(*result))
return items
def get_stats(self, path, duration):
"""
Get stats for track with filename and duration
@param path as str
@param duration as int
@return (popularity, mtime) as (int, int)
"""
with SqlCursor(Lp().db) as sql:
name = GLib.path_get_basename(path)
result = sql.execute("SELECT popularity, ltime\
FROM tracks\
WHERE filepath LIKE ?\
AND duration=?",
('%' + name + '%', duration))
v = result.fetchone()
if v is not None:
return v
return None
def add_uri(self, playlist_id, uri, signal=False):
"""
Add uri to playlist
@param playlist_id as int
@param uri as str
@param signal as bool
"""
if self.exists_track(playlist_id, uri):
return
if signal:
position = len(self.get_track_uris(playlist_id))
self.emit("playlist-track-added", playlist_id, uri, position)
with SqlCursor(self, True) as sql:
sql.execute("INSERT INTO tracks VALUES (?, ?)", (playlist_id, uri))
sql.execute(
"UPDATE playlists SET mtime=?\
WHERE rowid=?",
(datetime.now().strftime("%s"), playlist_id))
def get_randoms(self):
"""
Return random albums
@return array of albums ids as int
"""
with SqlCursor(Lp().db) as sql:
albums = []
result = sql.execute(
"SELECT DISTINCT albums.rowid\
FROM albums, album_genres\
WHERE album_genres.genre_id!=?\
AND album_genres.album_id=albums.rowid\
ORDER BY random() LIMIT 100",
(Type.CHARTS, ))
albums = list(itertools.chain(*result))
self._cached_randoms = list(albums)
return albums
def get_ids(self):
"""
Get all availables genres ids
@return [id as int]
"""
with SqlCursor(Lp().db) as sql:
result = sql.execute(
"SELECT DISTINCT genres.rowid\
FROM genres,album_genres AS AG\
WHERE AG.genre_id=genres.rowid\
AND ? NOT IN (\
SELECT album_genres.genre_id\
FROM album_genres\
WHERE AG.album_id=album_genres.album_id)\
ORDER BY genres.name\
COLLATE NOCASE COLLATE LOCALIZED",
(Type.CHARTS, ))
return list(itertools.chain(*result))
def get_ids_by_performer(self, artist_id):
"""
Return track id for performer
@param artist_id as int
@return [int]
"""
with SqlCursor(App().db) as sql:
filters = (artist_id, artist_id)
request = "SELECT tracks.rowid\
FROM tracks, track_artists\
WHERE track_artists.artist_id=? AND\
tracks.rowid = track_artists.track_id AND NOT EXISTS (\
SELECT albums.rowid FROM albums, album_artists\
WHERE albums.rowid=tracks.album_id AND\
album_artists.artist_id = ? AND\
album_artists.album_id = albums.rowid)"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_loves(self):
"""
Get albums ids with popularity
@return array of album ids as int
"""
with SqlCursor(Lp().db) as sql:
filters = (Type.CHARTS, )
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres\
WHERE album_genres.genre_id!=?\
AND loved=1\
AND album_genres.album_id=albums.rowid"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += " ORDER BY popularity DESC"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def exists(self, name, duration):
"""
Return True if entry exists
@param name as str
@parma duration as int
@return bool
"""
with SqlCursor(self) as sql:
result = sql.execute("SELECT rowid\
FROM history\
WHERE name=?\
AND duration=?",
(name, duration))
v = result.fetchone()
if v is not None:
return True
else:
return False
def del_non_persistent(self):
"""
Delete non persistent tracks from db
"""
for track_id in Lp().tracks.get_non_persistent():
album_id = Lp().tracks.get_album_id(track_id)
genre_ids = Lp().tracks.get_genre_ids(track_id)
album_artist_ids = Lp().albums.get_artist_ids(album_id)
artist_ids = Lp().tracks.get_artist_ids(track_id)
Lp().tracks.remove(track_id)
Lp().tracks.clean(track_id)
Lp().albums.clean(album_id)
for artist_id in album_artist_ids + artist_ids:
Lp().artists.clean(artist_id)
for genre_id in genre_ids:
Lp().genres.clean(genre_id)
with SqlCursor(Lp().db) as sql:
sql.commit()
def add(self, name, url):
"""
Add a radio, update url if radio already exists in db
@param radio name as str
@param url as str
@thread safe
"""
with SqlCursor(self) as sql:
if self.exists(name):
sql.execute("UPDATE radios\
SET url=?\
WHERE name=?", (url, name))
else:
sql.execute("INSERT INTO radios (name, url, popularity)\
VALUES (?, ?, ?)",
(name, url, 0))
sql.commit()
GLib.idle_add(self.emit, "radios-changed")
def set_artist_ids(self, album_id, artist_ids):
"""
Set artist id
@param album id as int
@param artist_ids as [int]
@warning: commit needed
"""
with SqlCursor(Lp().db) as sql:
currents = self.get_artist_ids(album_id)
if not currents or set(currents) - set(artist_ids):
sql.execute(
"DELETE FROM album_artists\
WHERE album_id=?", (album_id, ))
for artist_id in artist_ids:
sql.execute(
"INSERT INTO album_artists\
(album_id, artist_id)\
VALUES (?, ?)", (album_id, artist_id))
def get_compilations(self, artist_ids):
"""
Get all availables compilations for artist
@return Array of id as int
"""
with SqlCursor(Lp().db) as sql:
request = "SELECT DISTINCT albums.rowid FROM albums,\
tracks, track_artists, album_artists\
WHERE track_artists.track_id=tracks.rowid\
AND album_artists.artists_id=%s\
AND album_artists.album_id=albums.rowid\
AND albums.rowid=tracks.album_id AND (1=0 " %\
Type.COMPILATIONS
for artist_id in artist_ids:
request += "OR track_artists.artist_id=%s " % artist_id
request += ") ORDER BY albums.year"
result = sql.execute(request)
return list(itertools.chain(*result))
def search(self, string):
"""
Search for artists looking like string
@param string
@return Array of id as int
"""
with SqlCursor(Lp().db) as sql:
result = sql.execute(
"SELECT artists.rowid FROM artists, albums,\
album_genres, album_artists\
WHERE noaccents(artists.name) LIKE ?\
AND album_artists.artist_id=artists.rowid\
AND album_artists.album_id=albums.rowid\
AND album_genres.album_id=albums.rowid\
AND album_genres.genre_id!=?\
LIMIT 25",
('%' + noaccents(string) + '%', Type.CHARTS))
return list(itertools.chain(*result))
def set_more_popular(self, album_id):
"""
Increment popularity field for album id
@param int
@raise sqlite3.OperationalError on db update
"""
with SqlCursor(Lp().db) as sql:
result = sql.execute("SELECT popularity from albums WHERE rowid=?",
(album_id, ))
pop = result.fetchone()
if pop:
current = pop[0]
else:
current = 0
current += 1
sql.execute("UPDATE albums set popularity=? WHERE rowid=?",
(current, album_id))
sql.commit()
def get_mtimes(self):
"""
Get mtime for tracks
WARNING: Should be called before anything is shown on screen
@param None
@return dict of {uri as string: mtime as int}
"""
with SqlCursor(Lp().db) as sql:
mtimes = {}
result = sql.execute(
"SELECT DISTINCT tracks.uri, TG.mtime\
FROM tracks, track_genres AS TG\
WHERE tracks.rowid=TG.track_id\
AND tracks.persistent=?",
(DbPersistent.INTERNAL, ))
for row in result:
mtimes.update((row, ))
return mtimes
def count(self):
"""
Count artists
@return int
"""
with SqlCursor(Lp().db) as sql:
result = sql.execute("SELECT COUNT(DISTINCT artists.rowid)\
FROM artists, album_artists,\
album_genres, albums\
WHERE album_artists.album_id=albums.rowid\
AND artists.rowid=album_artists.artist_id\
AND album_genres.album_id=albums.rowid\
AND album_genres.genre_id!=?",
(Type.CHARTS,))
v = result.fetchone()
if v is not None:
return v[0]
return 0
def get_name(self, playlist_id):
"""
Get playlist name
@param playlist id as int
@return playlist name as str
"""
if playlist_id == Type.LOVED:
return self.LOVED
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT name\
FROM playlists\
WHERE rowid=?", (playlist_id, ))
v = result.fetchone()
if v is not None:
return v[0]
return ''
def get_id(self, playlist_name):
"""
Get playlist id
@param playlist name as str
@return playlst id as int
"""
if playlist_name == self.LOVED:
return Type.LOVED
with SqlCursor(self) as sql:
result = sql.execute(
"SELECT rowid\
FROM playlists\
WHERE name=?", (playlist_name, ))
v = result.fetchone()
if v is not None:
return v[0]
return Type.NONE
def get_ids(self, artist_id=None, genre_id=None):
"""
Get albums ids
@param Artist id as int/None, genre id as int/None
@return Array of album ids as int
"""
with SqlCursor(Lp().db) as sql:
result = []
# Get albums for all artists
if artist_id is None and genre_id is None:
result = sql.execute("SELECT albums.rowid FROM albums, artists\
WHERE artists.rowid=albums.artist_id\
ORDER BY artists.sortname COLLATE NOCASE,\
albums.year,\
albums.name COLLATE NOCASE")
# Get albums for genre
elif artist_id is None:
result = sql.execute(
"SELECT albums.rowid FROM albums,\
album_genres, artists\
WHERE album_genres.genre_id=?\
AND artists.rowid=artist_id\
AND album_genres.album_id=albums.rowid\
ORDER BY artists.sortname COLLATE NOCASE,\
albums.year,\
albums.name COLLATE NOCASE", (genre_id, ))
# Get albums for artist
elif genre_id is None:
result = sql.execute(
"SELECT rowid FROM albums\
WHERE artist_id=?\
ORDER BY year, name COLLATE NOCASE",
(artist_id, ))
# Get albums for artist id and genre id
else:
result = sql.execute(
"SELECT albums.rowid\
FROM albums, album_genres\
WHERE artist_id=?\
AND album_genres.genre_id=?\
AND album_genres.album_id=albums.rowid\
ORDER BY year, name COLLATE NOCASE",
(artist_id, genre_id))
return list(itertools.chain(*result))
def get(self, genre_ids=[]):
"""
Get all available album artists
@param genre ids as [int]
@return Array of (artist id as int, artist name as string)
"""
with SqlCursor(Lp().db) as sql:
result = []
if not genre_ids or genre_ids[0] == Type.ALL:
# Only artist that really have an album
result = sql.execute(
"SELECT DISTINCT artists.rowid,\
artists.name, artists.sortname\
FROM artists, albums,\
album_genres AS AG, album_artists\
WHERE album_artists.artist_id=artists.rowid\
AND album_artists.album_id=albums.rowid\
AND AG.album_id=albums.rowid\
AND ? NOT IN (\
SELECT album_genres.genre_id\
FROM album_genres\
WHERE AG.album_id=album_genres.album_id)\
ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED",
(Type.CHARTS,))
else:
genres = (Type.CHARTS,) + tuple(genre_ids)
request = "SELECT DISTINCT artists.rowid,\
artists.name, artists.sortname\
FROM artists, albums, album_genres AS AG,\
album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND ? NOT IN (\
SELECT album_genres.genre_id\
FROM album_genres\
WHERE AG.album_id=album_genres.album_id)\
AND albums.rowid=album_artists.album_id\
AND AG.album_id=albums.rowid AND ("
for genre_id in genre_ids:
request += "AG.genre_id=? OR "
request += "1=0) ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED"
result = sql.execute(request, genres)
return [(row[0], row[1], row[2]) for row in result]
def get_disc_tracks(self, album_id, genre_ids, artist_ids, disc):
"""
Get tracks ids for album id disc
@param album id as int
@param genre ids as [int]
@param artist ids as [int]
@param disc as int
@return [int]
"""
genre_ids = remove_static_genres(genre_ids)
# Reset filters if not needed
if not self.__has_genres(album_id):
genre_ids = []
if not self.__has_artists(album_id):
artist_ids = []
with SqlCursor(Lp().db) as sql:
filters = (album_id, disc)
request = "SELECT DISTINCT tracks.rowid\
FROM tracks"
if genre_ids:
request += ", track_genres"
filters += tuple(genre_ids)
if artist_ids:
request += ", track_artists"
filters += tuple(artist_ids)
request += " WHERE album_id=?\
AND discnumber=?"
if genre_ids:
request += " AND track_genres.track_id = tracks.rowid AND ("
for genre_id in genre_ids:
request += "track_genres.genre_id=? OR "
request += "1=0)"
if artist_ids:
request += " AND track_artists.track_id=tracks.rowid AND ("
for artist_id in artist_ids:
request += "track_artists.artist_id=? OR "
request += "1=0)"
request += " ORDER BY discnumber, tracknumber, tracks.name"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
