def _on_collection_updated(self, scanner, item, scan_update):
"""
Handles changes in collection
@param scanner as CollectionScanner
@param item as CollectionItem
@param scan_update as ScanUpdate
"""
if scan_update == ScanUpdate.ADDED:
wanted = True
for genre_id in item.genre_ids:
genre_ids = remove_static(self._genre_ids)
if genre_ids and genre_id not in genre_ids:
wanted = False
for artist_id in item.artist_ids:
artist_ids = remove_static(self._artist_ids)
if artist_ids and artist_id not in artist_ids:
wanted = False
if wanted:
self.add_value(Album(item.album_id))
elif scan_update == ScanUpdate.MODIFIED:
for child in self.children:
if child.data.id == item.album_id:
child.data.reset_tracks()
break
elif scan_update == ScanUpdate.REMOVED:
for child in self.children:
if child.data.id == item.album_id:
child.destroy()
break
def get_duration(self, album_id, genre_ids):
"""
Album duration in seconds
@param album_id as int
@param genre_ids as [int]
@return album duration as int
"""
genre_ids = remove_static(genre_ids)
with SqlCursor(App().db) as sql:
if genre_ids and genre_ids[0] > 0:
filters = (album_id,)
filters += tuple(genre_ids)
request = "SELECT SUM(duration)\
FROM tracks, track_genres\
WHERE tracks.album_id=?\
AND track_genres.track_id = tracks.rowid AND ("
for genre_id in genre_ids:
request += "track_genres.genre_id=? OR "
request += "1=0)"
result = sql.execute(request, filters)
else:
result = sql.execute("SELECT SUM(duration) FROM tracks\
WHERE album_id=?", (album_id,))
v = result.fetchone()
if v and v[0] 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(genre_ids)
artist_ids = remove_static(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_compilation_ids(self, genre_ids, ignore=False):
"""
Get all compilations
@param genre_ids as [int]
@param ignore as bool => ignore albums with loved == 1
@return [int]
"""
genre_ids = remove_static(genre_ids)
with SqlCursor(App().db) as sql:
order = " ORDER BY albums.name, albums.timestamp"
result = []
# Get all compilations
if not genre_ids or genre_ids[0] == Type.ALL:
filters = (Type.COMPILATIONS,)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_artists\
WHERE album_artists.artist_id=?\
AND albums.mtime != 0\
AND album_artists.album_id=albums.rowid"
if ignore:
request += " AND albums.loved != -1"
if not get_network_available("YOUTUBE"):
request += " AND albums.mtime != -1"
request += order
result = sql.execute(request, filters)
# Get compilation for genre id
else:
filters = (Type.COMPILATIONS,)
filters += tuple(genre_ids)
request = "SELECT DISTINCT albums.rowid\
FROM albums, album_genres, album_artists\
WHERE album_genres.album_id=albums.rowid\
AND albums.mtime != 0\
AND albums.loved != -1\
AND album_artists.album_id=albums.rowid\
AND album_artists.artist_id=? AND ( "
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0)"
if ignore:
request += " AND albums.loved != -1"
if not get_network_available("YOUTUBE"):
request += " AND albums.mtime != -1"
request += order
result = sql.execute(request, filters)
return list(itertools.chain(*result))
def get(self, genre_ids, storage_type):
"""
Get all available artists
@param genre_ids as [int]
@param storage_type as StorageType
@return [int, str, str]
"""
genre_ids = remove_static(genre_ids)
if App().settings.get_value("show-artist-sort"):
select = "artists.rowid, artists.sortname, artists.sortname"
else:
select = "artists.rowid, artists.name, artists.sortname"
with SqlCursor(self.__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 %s FROM artists, albums, album_artists\
WHERE album_artists.artist_id=artists.rowid\
AND album_artists.album_id=albums.rowid\
AND albums.storage_type & ?\
ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED" % select,
(storage_type, ))
else:
filters = (storage_type, )
filters += tuple(genre_ids)
request = "SELECT DISTINCT %s\
FROM artists, albums, album_genres, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
AND albums.storage_type & ?\
AND album_genres.album_id=albums.rowid AND"
request += make_subrequest("album_genres.genre_id=?", "OR",
len(genre_ids))
request += " ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED"
result = sql.execute(request % select, filters)
return [(row[0], row[1], row[2]) for row in result]
def get(self, genre_ids=[]):
"""
Get all available album artists
@param genre_ids as [int]
@return [int, str, str]
"""
genre_ids = remove_static(genre_ids)
if App().settings.get_value("show-artist-sort"):
select = "artists.rowid, artists.sortname, artists.sortname"
else:
select = "artists.rowid, artists.name, artists.sortname"
with SqlCursor(App().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 %s FROM artists, albums, album_artists\
WHERE album_artists.artist_id=artists.rowid\
AND album_artists.album_id=albums.rowid\
AND albums.mtime!=0\
ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED" % select)
else:
genres = tuple(genre_ids)
request = "SELECT DISTINCT %s\
FROM artists, albums, album_genres, album_artists\
WHERE artists.rowid=album_artists.artist_id\
AND albums.rowid=album_artists.album_id\
AND albums.mtime!=0\
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 COLLATE LOCALIZED"
result = sql.execute(request % select, genres)
return [(row[0], row[1], row[2]) for row in result]
def get_discs(self, album_id, genre_ids):
"""
Get disc numbers
@param album_id as int
@param genre_ids as [int]
@return [disc as int]
"""
genre_ids = remove_static(genre_ids)
with SqlCursor(App().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, ignore=False):
"""
Get albums ids
@param artist_ids as [int]
@param genre_ids as [int]
@param ignore as bool => ignore albums with loved == 1
@return albums ids as [int]
"""
genre_ids = remove_static(genre_ids)
artist_ids = remove_static(artist_ids)
orderby = App().settings.get_enum("orderby")
if artist_ids or orderby == OrderBy.ARTIST:
order = " ORDER BY artists.sortname\
COLLATE NOCASE COLLATE LOCALIZED,\
albums.timestamp,\
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.timestamp DESC,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
else:
order = " ORDER BY albums.popularity DESC,\
albums.name\
COLLATE NOCASE COLLATE LOCALIZED"
with SqlCursor(App().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\
albums.mtime!=0 AND\
artists.rowid = album_artists.artist_id"
if ignore:
request += " AND albums.loved != -1"
if not get_network_available("YOUTUBE"):
request += " AND albums.mtime != -1"
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\
albums.mtime!=0 AND\
album_genres.album_id=albums.rowid AND ( "
for genre_id in genre_ids:
request += "album_genres.genre_id=? OR "
request += "1=0)"
if ignore:
request += " AND albums.loved != -1"
if not get_network_available("YOUTUBE"):
request += " AND albums.mtime != -1"
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\
albums.mtime!=0 AND\
artists.rowid = album_artists.artist_id AND ("
for artist_id in artist_ids:
request += "artists.rowid=? OR "
request += "1=0)"
if ignore:
request += " AND albums.loved != -1"
if not get_network_available("YOUTUBE"):
request += " AND albums.mtime != -1"
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\
albums.mtime!=0 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)"
if ignore:
request += " AND albums.loved != -1"
if not get_network_available("YOUTUBE"):
request += " AND albums.mtime != -1"
request += order
result = sql.execute(request, filters)
return list(itertools.chain(*result))