def _insert_into_db(self): self.id = db.c.get_next_id("r4_albums", "album_id") success = db.c.update( "INSERT INTO r4_albums (album_id, album_name, album_name_searchable) VALUES (%s, %s, %s)", (self.id, self.data['name'], make_searchable_string(self.data['name']))) return success
def _insert_into_db(self): self.id = db.c.get_next_id("r4_groups", "group_id") return db.c.update( "INSERT INTO r4_groups (group_id, group_name, group_name_searchable) VALUES (%s, %s, %s)", (self.id, self.data["name"], make_searchable_string(self.data["name"])), )
def _insert_into_db(self): self.id = db.c.get_next_id("r4_albums", "album_id") success = db.c.update( "INSERT INTO r4_albums (album_id, album_name, album_name_searchable) VALUES (%s, %s, %s)", (self.id, self.data["name"], make_searchable_string(self.data["name"])), ) return success
def _update_db(self): global updated_album_ids success = db.c.update( "UPDATE r4_albums " "SET album_name = %s, album_name_searchable = %s, album_rating = %s " "WHERE album_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.data['rating'], self.id)) updated_album_ids[self.sid][self.id] = True return success
def _update_db(self): global updated_album_ids success = db.c.update( "UPDATE r4_albums " "SET album_name = %s, album_name_searchable = %s " "WHERE album_id = %s", (self.data["name"], make_searchable_string(self.data["name"]), self.id), ) if self.sid != 0: updated_album_ids[self.sid][self.id] = True return success
def _update_db(self): global updated_album_ids success = db.c.update( "UPDATE r4_albums " "SET album_name = %s, album_name_searchable = %s " "WHERE album_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.id)) if self.sid != 0: updated_album_ids[self.sid][self.id] = True return success
def _update_db(self): return db.c.update("UPDATE r4_groups SET group_name = %s, group_name_searchable = %s WHERE group_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.id))
def _insert_into_db(self): self.id = db.c.get_next_id("r4_groups", "group_id") return db.c.update("INSERT INTO r4_groups (group_id, group_name, group_name_searchable) VALUES (%s, %s, %s)", (self.id, self.data['name'], make_searchable_string(self.data['name'])))
def _update_db(self): return db.c.update( "UPDATE r4_artists SET artist_name = %s, artist_name_searchable = %s WHERE artist_id = %s", (self.data['name'], make_searchable_string( self.data['name']), self.id))
def _insert_into_db(self): self.id = db.c.get_next_id("r4_artists", "artist_id") return db.c.update( "INSERT INTO r4_artists (artist_id, artist_name, artist_name_searchable) VALUES (%s, %s, %s)", (self.id, self.data['name'], make_searchable_string(self.data['name'])))
def _update_db(self): return db.c.update( "UPDATE r4_groups SET group_name = %s, group_name_searchable = %s WHERE group_id = %s", (self.data["name"], make_searchable_string( self.data["name"]), self.id), )
def save(self, sids_override = False): """ Save song to the database. Does NOT associate metadata. """ log.debug("playlist", "saving song to database; manual sids? {}".format(sids_override)) update = False if self.id: update = True else: potential_id = None # To check for moved/duplicate songs we try to find if it exists in the db if self.artist_tag: potential_id = db.c.fetch_var("SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s AND song_artist_tag = %s", (self.data['title'], self.data['length'], self.artist_tag)) else: potential_id = db.c.fetch_var("SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s", (self.data['title'], self.data['length'])) if not config.get("allow_duplicate_song") and potential_id: self.id = potential_id update = True if sids_override: self.data['sids'] = sids_override elif len(self.data['sids']) == 0: raise SongHasNoSIDsException self.data['origin_sid'] = self.data['sids'][0] file_mtime = 0 if not self.fake: file_mtime = os.stat(self.filename)[8] if update: log.debug("playlist", "updating existing song_id {}".format(self.id)) db.c.update("UPDATE r4_songs \ SET song_filename = %s, \ song_title = %s, \ song_title_searchable = %s, \ song_url = %s, \ song_link_text = %s, \ song_length = %s, \ song_track_number = %s, \ song_disc_number = %s, \ song_year = %s, \ song_scanned = TRUE, \ song_verified = TRUE, \ song_file_mtime = %s, \ song_replay_gain = %s, \ song_origin_sid = %s \ WHERE song_id = %s", (self.filename, self.data['title'], make_searchable_string(self.data['title']), self.data['url'], self.data['link_text'], self.data['length'], self.data['track_number'], self.data['disc_number'], self.data['year'], file_mtime, self.replay_gain, self.data['origin_sid'], self.id)) if self.artist_tag: db.c.update("UPDATE r4_songs SET song_artist_tag = %s WHERE song_id = %s", (self.artist_tag, self.id)) else: self.id = db.c.get_next_id("r4_songs", "song_id") log.debug("playlist", "inserting a new song with id {}".format(self.id)) db.c.update("INSERT INTO r4_songs \ (song_id, song_filename, song_title, song_title_searchable, song_url, song_link_text, song_length, song_track_number, song_disc_number, song_year, song_origin_sid, song_file_mtime, song_verified, song_scanned, song_replay_gain, song_artist_tag) \ VALUES \ (%s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s)", (self.id, self.filename, self.data['title'], make_searchable_string(self.data['title']), self.data['url'], self.data['link_text'], self.data['length'], self.data['track_number'], self.data['disc_number'], self.data['year'], self.data['origin_sid'], file_mtime, True, True, self.replay_gain, self.artist_tag)) self.verified = True self.data['added_on'] = int(timestamp()) current_sids = db.c.fetch_list("SELECT sid FROM r4_song_sid WHERE song_id = %s", (self.id,)) log.debug("playlist", "database sids: {}, actual sids: {}".format(current_sids, self.data['sids'])) for sid in current_sids: if not self.data['sids'].count(sid): db.c.update("UPDATE r4_song_sid SET song_exists = FALSE WHERE song_id = %s AND sid = %s", (self.id, sid)) for sid in self.data['sids']: if current_sids.count(sid): db.c.update("UPDATE r4_song_sid SET song_exists = TRUE WHERE song_id = %s AND sid = %s", (self.id, sid)) else: db.c.update("INSERT INTO r4_song_sid (song_id, sid) VALUES (%s, %s)", (self.id, sid))
def _update_db(self): return db.c.update("UPDATE r4_artists SET artist_name = %s, artist_name_searchable = %s WHERE artist_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.id))
def save(self, sids_override=False): """ Save song to the database. Does NOT associate metadata. """ log.debug( "playlist", "saving song to database; manual sids? {}".format(sids_override)) update = False if self.id: update = True else: potential_id = None # To check for moved/duplicate songs we try to find if it exists in the db if self.artist_tag: potential_id = db.c.fetch_var( "SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s AND song_artist_tag = %s", (self.data['title'], self.data['length'], self.artist_tag)) else: potential_id = db.c.fetch_var( "SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s", (self.data['title'], self.data['length'])) if not config.get("allow_duplicate_song") and potential_id: self.id = potential_id update = True if sids_override: self.data['sids'] = sids_override elif len(self.data['sids']) == 0: raise SongHasNoSIDsException self.data['origin_sid'] = self.data['sids'][0] file_mtime = 0 if not self.fake: file_mtime = os.stat(self.filename)[8] if update: log.debug("playlist", "updating existing song_id {}".format(self.id)) db.c.update( "UPDATE r4_songs \ SET song_filename = %s, \ song_title = %s, \ song_title_searchable = %s, \ song_url = %s, \ song_link_text = %s, \ song_length = %s, \ song_track_number = %s, \ song_disc_number = %s, \ song_year = %s, \ song_scanned = TRUE, \ song_verified = TRUE, \ song_file_mtime = %s, \ song_replay_gain = %s, \ song_origin_sid = %s \ WHERE song_id = %s", (self.filename, self.data['title'], make_searchable_string(self.data['title']), self.data['url'], self.data['link_text'], self.data['length'], self.data['track_number'], self.data['disc_number'], self.data['year'], file_mtime, self.replay_gain, self.data['origin_sid'], self.id)) if self.artist_tag: db.c.update( "UPDATE r4_songs SET song_artist_tag = %s WHERE song_id = %s", (self.artist_tag, self.id)) else: self.id = db.c.get_next_id("r4_songs", "song_id") log.debug("playlist", "inserting a new song with id {}".format(self.id)) db.c.update( "INSERT INTO r4_songs \ (song_id, song_filename, song_title, song_title_searchable, song_url, song_link_text, song_length, song_track_number, song_disc_number, song_year, song_origin_sid, song_file_mtime, song_verified, song_scanned, song_replay_gain, song_artist_tag) \ VALUES \ (%s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s)", (self.id, self.filename, self.data['title'], make_searchable_string(self.data['title']), self.data['url'], self.data['link_text'], self.data['length'], self.data['track_number'], self.data['disc_number'], self.data['year'], self.data['origin_sid'], file_mtime, True, True, self.replay_gain, self.artist_tag)) self.verified = True self.data['added_on'] = int(timestamp()) current_sids = db.c.fetch_list( "SELECT sid FROM r4_song_sid WHERE song_id = %s", (self.id, )) log.debug( "playlist", "database sids: {}, actual sids: {}".format( current_sids, self.data['sids'])) for sid in current_sids: if not self.data['sids'].count(sid): db.c.update( "UPDATE r4_song_sid SET song_exists = FALSE WHERE song_id = %s AND sid = %s", (self.id, sid)) for sid in self.data['sids']: if current_sids.count(sid): db.c.update( "UPDATE r4_song_sid SET song_exists = TRUE WHERE song_id = %s AND sid = %s", (self.id, sid)) else: db.c.update( "INSERT INTO r4_song_sid (song_id, sid) VALUES (%s, %s)", (self.id, sid))
def post(self): s = make_searchable_string(self.get_argument("search")) if len(s) < 3: raise APIException("search_string_too_short") s = "%%%s%%" % s artists = db.c.fetch_all( "SELECT DISTINCT artist_id AS id, artist_name AS name, artist_name_searchable " "FROM r4_song_sid " "JOIN r4_song_artist USING (song_id) " "JOIN r4_artists USING (artist_id) " "WHERE sid = %s AND song_exists = TRUE AND artist_name_searchable LIKE %s " "ORDER BY artist_name_searchable " "LIMIT 50", (self.sid, s)) if self.user.is_anonymous(): albums = db.c.fetch_all( "SELECT DISTINCT album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, " "FALSE AS fave, 0 AS rating_user, FALSE as rating_complete " "FROM r4_album_sid " "JOIN r4_albums USING (album_id) " "WHERE sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s " "ORDER BY album_name_searchable " "LIMIT 50", (self.sid, s)) else: albums = db.c.fetch_all( "SELECT DISTINCT r4_albums.album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, " "COALESCE(album_fave, FALSE) AS fave, COALESCE(album_rating_user, 0) AS rating_user, COALESCE(album_rating_complete, FALSE) AS rating_complete " "FROM r4_album_sid " "JOIN r4_albums USING (album_id) " "LEFT JOIN r4_album_ratings ON (r4_albums.album_id = r4_album_ratings.album_id AND r4_album_ratings.user_id = %s AND r4_album_ratings.sid = %s) " "LEFT JOIN r4_album_faves ON (r4_albums.album_id = r4_album_faves.album_id AND r4_album_faves.user_id = %s) " "WHERE r4_album_sid.sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s " "ORDER BY album_name_searchable " "LIMIT 50", (self.user.id, self.sid, self.user.id, self.sid, s)) # use bigger query below # songs = db.c.fetch_all("SELECT DISTINCT song_id, song_title, album_name, song_origin_sid, song_cool, song_cool_end, song_url, song_length, FROM r4_song_sid JOIN r4_songs USING (song_id) JOIN r4_albums USING (album_id) WHERE sid = %s AND song_title_searchable LIKE %s", (self.sid, s)) # base SQL here copy pasted from /rainwave/playlist_objects/album.py if self.user.is_anonymous(): songs = db.c.fetch_all( "SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, " "song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, " "song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, " "FALSE AS requestable, song_cool AS cool, song_cool_end AS cool_end, " "song_artist_parseable AS artist_parseable, " "0 AS rating_user, FALSE AS fave, " "r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable " "FROM r4_song_sid " "JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) " "JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) " "WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s " "ORDER BY album_name_searchable, song_title_searchable " "LIMIT 100", (s, self.sid)) else: songs = db.c.fetch_all( "SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, " "song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, " "song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, " "TRUE AS requestable, song_cool AS cool, song_cool_end AS cool_end, " "song_artist_parseable AS artist_parseable, " "COALESCE(song_rating_user, 0) AS rating_user, COALESCE(song_fave, FALSE) AS fave, " "r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable " "FROM r4_song_sid " "JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) " "LEFT JOIN r4_song_ratings ON (r4_song_sid.song_id = r4_song_ratings.song_id AND user_id = %s) " "JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) " "WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s " "ORDER BY album_name_searchable, song_title_searchable " "LIMIT 100", (s, self.user.id, self.sid)) self.append("artists", artists) self.append("albums", albums) self.append("songs", songs)
def post(self): s = make_searchable_string(self.get_argument("search")) if len(s) < 3: raise APIException("search_string_too_short") s = "%%%s%%" % s artists = db.c.fetch_all( "SELECT DISTINCT artist_id AS id, artist_name AS name, artist_name_searchable " "FROM r4_song_sid " "JOIN r4_song_artist USING (song_id) " "JOIN r4_artists USING (artist_id) " "WHERE sid = %s AND song_exists = TRUE AND artist_name_searchable LIKE %s " "ORDER BY artist_name_searchable " "LIMIT 50", (self.sid, s) ) if self.user.is_anonymous(): albums = db.c.fetch_all( "SELECT DISTINCT album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, " "FALSE AS fave, 0 AS rating_user, FALSE as rating_complete " "FROM r4_album_sid " "JOIN r4_albums USING (album_id) " "WHERE sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s " "ORDER BY album_name_searchable " "LIMIT 50", (self.sid, s) ) else: albums = db.c.fetch_all( "SELECT DISTINCT r4_albums.album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, " "COALESCE(album_fave, FALSE) AS fave, COALESCE(album_rating_user, 0) AS rating_user, COALESCE(album_rating_complete, FALSE) AS rating_complete " "FROM r4_album_sid " "JOIN r4_albums USING (album_id) " "LEFT JOIN r4_album_ratings ON (r4_albums.album_id = r4_album_ratings.album_id AND r4_album_ratings.user_id = %s AND r4_album_ratings.sid = %s) " "LEFT JOIN r4_album_faves ON (r4_albums.album_id = r4_album_faves.album_id AND r4_album_faves.user_id = %s) " "WHERE r4_album_sid.sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s " "ORDER BY album_name_searchable " "LIMIT 50", (self.user.id, self.sid, self.user.id, self.sid, s) ) # use bigger query below # songs = db.c.fetch_all("SELECT DISTINCT song_id, song_title, album_name, song_origin_sid, song_cool, song_cool_end, song_url, song_length, FROM r4_song_sid JOIN r4_songs USING (song_id) JOIN r4_albums USING (album_id) WHERE sid = %s AND song_title_searchable LIKE %s", (self.sid, s)) # base SQL here copy pasted from /rainwave/playlist_objects/album.py if self.user.is_anonymous(): songs = db.c.fetch_all( "SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, " "song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, " "song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, " "FALSE AS requestable, song_cool AS cool, song_cool_end AS cool_end, " "song_artist_parseable AS artist_parseable, " "0 AS rating_user, FALSE AS fave, " "r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable " "FROM r4_song_sid " "JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) " "JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) " "WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s " "ORDER BY album_name_searchable, song_title_searchable " "LIMIT 100", (s, self.sid)) else: songs = db.c.fetch_all( "SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, " "song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, " "song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, " "TRUE AS requestable, song_cool AS cool, song_cool_end AS cool_end, " "song_artist_parseable AS artist_parseable, " "COALESCE(song_rating_user, 0) AS rating_user, COALESCE(song_fave, FALSE) AS fave, " "r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable " "FROM r4_song_sid " "JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) " "LEFT JOIN r4_song_ratings ON (r4_song_sid.song_id = r4_song_ratings.song_id AND user_id = %s) " "JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) " "WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s " "ORDER BY album_name_searchable, song_title_searchable " "LIMIT 100", (s, self.user.id, self.sid)) self.append("artists", artists) self.append("albums", albums) self.append("songs", songs)