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))