def get_count(self, album_id, genre_ids):
"""
Get number of tracks for album_id
@param album id as int
@param genre ids as [int]
@return count as int
"""
genre_ids = remove_static_genres(genre_ids)
with SqlCursor(Lp().db) as sql:
filters = (album_id,)
filters += tuple(genre_ids)
request = "SELECT COUNT(1)\
FROM tracks, track_genres\
WHERE tracks.album_id=?\
AND track_genres.track_id = tracks.rowid"
if genre_ids:
request += " AND ("
for genre_id in genre_ids:
request += "track_genres.genre_id=? OR "
request += "1=0)"
result = sql.execute(request, filters)
v = result.fetchone()
if v is not None:
return v[0]
return 0
def get_disc_track_ids(self, album_id, genre_ids, artist_ids, disc,
disallow_ignored_tracks):
"""
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
@param disallow_ignored_tracks as bool
@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(App().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)"
if disallow_ignored_tracks:
request += " AND tracks.loved != -1"
request += " ORDER BY discnumber, tracknumber, tracks.name"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_track_uris(self, album_id, genre_ids, artist_ids):
"""
Get track uris for album id/disc
Will search track from albums from same artist
with same name and different genre
@param album id as int
@param genre ids as [int]
@param artist_ids as [int]
@return Arrays of tracks id as 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, )
request = "SELECT DISTINCT tracks.uri\
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=? "
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))
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 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"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_tracks_path(self, album_id, genre_ids, artist_ids):
"""
Get tracks path for album id/disc
Will search track from albums from same artist
with same name and different genre
@param album id as int
@param genre ids as [int]
@param artist_ids as [int]
@return Arrays of tracks id as 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,)
request = "SELECT DISTINCT tracks.filepath\
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=? "
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"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_track_ids(self, album_id, genre_ids=[], artist_ids=[]):
"""
Get tracks for album id
@param album id as int
@param genre ids as [int]
@param artist_ids as [int]
@return track ids as [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, )
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=? "
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"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_discs(self, album_id, genre_ids):
"""
Get disc numbers
@param album id as int
@param genre id as int
@return [disc as int]
"""
genre_ids = remove_static_genres(genre_ids)
with SqlCursor(Lp().db) as sql:
filters = (album_id,)
filters += tuple(genre_ids)
request = "SELECT DISTINCT discnumber\
FROM tracks, track_genres\
WHERE tracks.album_id=?\
AND track_genres.track_id = tracks.rowid"
if genre_ids:
request += " AND ("
for genre_id in genre_ids:
request += "track_genres.genre_id=? OR "
request += "1=0)"
request += " ORDER BY discnumber"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_ids(self, artist_ids=[], genre_ids=[]):
"""
Get albums ids
@param artist ids as [int]
@param genre ids as [int]
@return albums ids as [int]
"""
genre_ids = remove_static_genres(genre_ids)
orderby = Lp().settings.get_enum("orderby")
if orderby == OrderBy.ARTIST:
order = " ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED,\
albums.year,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
elif orderby == OrderBy.NAME:
order = " ORDER BY albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
elif orderby == OrderBy.YEAR:
order = " ORDER BY albums.year,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
else:
order = " ORDER BY albums.popularity DESC,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
with SqlCursor(Lp().db) as sql:
result = []
# Get albums for all artists
if not artist_ids and not genre_ids:
filters = (Type.CHARTS, )
request = "SELECT DISTINCT albums.rowid\
FROM albums, artists,\
album_artists, album_genres as AG\
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 AG.album_id=albums.rowid\
AND albums.rowid=album_artists.album_id"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
# Get albums for genre
elif not artist_ids:
# Only show charts if wanted
if Type.CHARTS in genre_ids:
filters = tuple(genre_ids)
else:
filters = (Type.CHARTS, ) + tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid FROM albums,\
album_genres as AG, artists, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id "
if Type.CHARTS not in genre_ids:
request += "AND ? NOT IN (\
SELECT album_genres.genre_id\
FROM album_genres\
WHERE AG.album_id=album_genres.album_id)"
request += " AND AG.album_id=albums.rowid AND ( "
for genre_id in genre_ids:
request += "AG.genre_id=? OR "
request += "1=0)"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
# Get albums for artist
elif not genre_ids:
filters = (Type.CHARTS, )
filters += tuple(artist_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, artists,\
album_artists, album_genres as AG\
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 AG.album_id=albums.rowid\
AND album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0)"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
# Get albums for artist id and genre id
else:
filters = (Type.CHARTS, )
filters += tuple(artist_ids)
filters += tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres as AG,\
artists, album_artists\
WHERE AG.album_id=albums.rowid\
AND 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 album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0) AND ("
for genre_id in genre_ids:
request += "AG.genre_id=? OR "
request += "1=0)"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_ids(self, artist_ids=[], genre_ids=[]):
"""
Get albums ids
@param artist ids as [int]
@param genre ids as [int]
@return Array of album ids as int
"""
genre_ids = remove_static_genres(genre_ids)
with SqlCursor(Lp().db) as sql:
result = []
# Get albums for all artists
if not artist_ids and not genre_ids:
result = sql.execute(
"SELECT DISTINCT albums.rowid\
FROM albums, artists, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
ORDER BY artists.sortname COLLATE NOCASE,\
albums.year,\
albums.name COLLATE NOCASE")
# Get albums for genre
elif not artist_ids:
genres = tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid FROM albums,\
album_genres, artists, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
AND album_genres.album_id=albums.rowid AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0) ORDER BY artists.sortname COLLATE NOCASE,\
albums.year,\
albums.name COLLATE NOCASE"
result = sql.execute(request, genres)
# Get albums for artist
elif not genre_ids:
artists = tuple(artist_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, artists, album_artists WHERE\
album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0) ORDER BY artists.name COLLATE NOCASE,\
year, albums.name COLLATE NOCASE"
result = sql.execute(request, artists)
# Get albums for artist id and genre id
else:
filters = tuple(artist_ids)
filters += tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres, artists, album_artists\
WHERE album_genres.album_id=albums.rowid AND\
album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0) AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0) ORDER BY artists.name COLLATE NOCASE,\
year, albums.name COLLATE NOCASE"
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_ids(self, artist_ids=[], genre_ids=[]):
"""
Get albums ids
@param artist ids as [int]
@param genre ids as [int]
@return Array of album ids as int
"""
genre_ids = remove_static_genres(genre_ids)
orderby = Lp().settings.get_enum('orderby')
if orderby == OrderBy.ARTIST:
order = "ORDER BY artists.sortname\
COLLATE NOCASE COLLATE NOCASE,\
albums.year,\
albums.name\
COLLATE NOCASE COLLATE NOCASE"
elif orderby == OrderBy.NAME:
order = "ORDER BY albums.name\
COLLATE NOCASE COLLATE NOCASE"
elif orderby == OrderBy.YEAR:
order = "ORDER BY albums.year,\
albums.name\
COLLATE NOCASE COLLATE NOCASE"
else:
order = "ORDER BY albums.popularity DESC,\
albums.name\
COLLATE NOCASE COLLATE NOCASE"
with SqlCursor(Lp().db) as sql:
result = []
# Get albums for all artists
if not artist_ids and not genre_ids:
result = sql.execute(
"SELECT DISTINCT albums.rowid\
FROM albums, artists, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
%s" % order)
# Get albums for genre
elif not artist_ids:
genres = tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid FROM albums,\
album_genres, artists, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
AND album_genres.album_id=albums.rowid AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0) %s" % order
result = sql.execute(request, genres)
# Get albums for artist
elif not genre_ids:
artists = tuple(artist_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, artists, album_artists WHERE\
artists.rowid=album_artists.artist_id AND\
album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0) %s" % order
result = sql.execute(request, artists)
# Get albums for artist id and genre id
else:
filters = tuple(artist_ids)
filters += tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres, artists, album_artists\
WHERE album_genres.album_id=albums.rowid AND\
artists.rowid=album_artists.artist_id AND\
album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0) AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0) %s" % order
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_ids(self, artist_ids=[], genre_ids=[]):
"""
Get albums ids
@param artist ids as [int]
@param genre ids as [int]
@return albums ids as [int]
"""
genre_ids = remove_static_genres(genre_ids)
orderby = Lp().settings.get_enum("orderby")
if orderby == OrderBy.ARTIST:
order = " ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED,\
albums.year,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
elif orderby == OrderBy.NAME:
order = " ORDER BY albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
elif orderby == OrderBy.YEAR:
order = " ORDER BY albums.year,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
else:
order = " ORDER BY albums.popularity DESC,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
with SqlCursor(Lp().db) as sql:
result = []
# Get albums for all artists
if not artist_ids and not genre_ids:
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_artists, artists\
WHERE albums.rowid = album_artists.album_id AND\
artists.rowid = album_artists.artist_id"
request += order
result = sql.execute(request)
# Get albums for genres
elif not artist_ids:
filters = tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid FROM albums,\
album_genres, album_artists, artists\
WHERE albums.rowid = album_artists.album_id AND\
artists.rowid = album_artists.artist_id AND\
album_genres.album_id=albums.rowid AND ( "
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0)"
request += order
result = sql.execute(request, filters)
# Get albums for artist
elif not genre_ids:
filters = tuple(artist_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_artists, artists\
WHERE album_artists.album_id=albums.rowid AND\
artists.rowid = album_artists.artist_id AND ("
for artist_id in artist_ids:
request += "artists.rowid=? OR "
request += "1=0)"
request += order
result = sql.execute(request, filters)
# Get albums for artist id and genre id
else:
filters = tuple(artist_ids)
filters += tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres, album_artists, artists\
WHERE album_genres.album_id=albums.rowid AND\
artists.rowid = album_artists.artist_id AND\
album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "artists.rowid=? OR "
request += "1=0) AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0)"
request += order
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get_ids(self, artist_ids=[], genre_ids=[]):
"""
Get albums ids
@param artist ids as [int]
@param genre ids as [int]
@return Array of album ids as int
"""
genre_ids = remove_static_genres(genre_ids)
orderby = Lp().settings.get_enum('orderby')
if genre_ids and genre_ids[0] == Type.CHARTS:
order = " ORDER BY albums.id DESC"
elif orderby == OrderBy.ARTIST:
order = " ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED,\
albums.year,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
elif orderby == OrderBy.NAME:
order = " ORDER BY albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
elif orderby == OrderBy.YEAR:
order = " ORDER BY albums.year,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
else:
order = " ORDER BY albums.popularity DESC,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
with SqlCursor(Lp().db) as sql:
result = []
# Get albums for all artists
if not artist_ids and not genre_ids:
filters = (Type.CHARTS,)
request = "SELECT DISTINCT albums.rowid\
FROM albums, artists, album_artists, album_genres\
WHERE artists.rowid=album_artists.artist_id\
AND album_genres.genre_id!=?\
AND album_genres.album_id=albums.rowid\
AND albums.rowid=album_artists.album_id"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
# Get albums for genre
elif not artist_ids:
filters = tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid FROM albums,\
album_genres, artists, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
AND album_genres.album_id=albums.rowid AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0)"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
# Get albums for artist
elif not genre_ids:
filters = (Type.CHARTS,)
filters += tuple(artist_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, artists, album_artists, album_genres\
WHERE artists.rowid=album_artists.artist_id\
AND album_genres.genre_id!=?\
AND album_genres.album_id=albums.rowid\
AND album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0)"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
# Get albums for artist id and genre id
else:
filters = tuple(artist_ids)
filters += tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres, artists, album_artists\
WHERE album_genres.album_id=albums.rowid AND\
artists.rowid=album_artists.artist_id AND\
album_artists.album_id=albums.rowid AND ("
for artist_id in artist_ids:
request += "album_artists.artist_id=? OR "
request += "1=0) AND ("
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0)"
if not get_network_available():
request += " AND albums.synced!=%s" % Type.NONE
request += order
result = sql.execute(request, filters)
return list(itertools.chain(*result))
