def set_album_fave(sid, album_id, user_id, fave): db.c.start_transaction() exists = db.c.fetch_row("SELECT * FROM r4_album_ratings WHERE album_id = %s AND user_id = %s AND sid = %s", (album_id, user_id, sid)) rating = None rating_complete = None if not exists: if db.c.update("INSERT INTO r4_album_ratings (album_id, user_id, album_fave, sid) VALUES (%s, %s, %s, %s)", (album_id, user_id, fave, sid)) == 0: log.debug("rating", "Failed to insert record for fave %s %s, fave is: %s." % ("album", album_id, fave)) return False else: rating = exists["album" + "_rating_user"] rating_complete = exists['album_rating_complete'] if db.c.update("UPDATE r4_album_ratings SET album_fave = %s WHERE album_id = %s AND user_id = %s AND sid = %s", (fave, album_id, user_id, sid)) == 0: log.debug("rating", "Failed to update record for fave %s %s, fave is: %s." % ("album", album_id, fave)) return False if (not exists and fave) or (not exists["album" + "_fave"] and fave): db.c.update("UPDATE r4_album_sid SET album_fave_count = album_fave_count + 1 WHERE album_id = %s AND sid = %s", (album_id, sid)) elif (exists and exists["album" + "_fave"] and not fave): db.c.update("UPDATE r4_album_sid SET album_fave_count = album_fave_count - 1 WHERE album_id = %s AND sid = %s", (album_id, sid)) if "album" == "album": cache.set_album_rating(sid, album_id, user_id, { "rating_user": rating, "fave": fave, "rating_complete": rating_complete }) elif "album" == "song": cache.set_song_rating(album_id, user_id, { "rating_user": rating, "fave": fave }) return True db.c.commit()
def update_album_ratings(target_sid, song_id, user_id): toret = None for row in db.c.fetch_all("SELECT r4_songs.album_id, sid, album_song_count FROM r4_songs JOIN r4_album_sid USING (album_id) WHERE r4_songs.song_id = %s AND album_exists = TRUE", (song_id,)): album_id = row['album_id'] sid = row['sid'] num_songs = row['album_song_count'] user_data = db.c.fetch_row( "SELECT ROUND(CAST(AVG(song_rating_user) AS NUMERIC), 1) AS rating_user, " "COUNT(song_rating_user) AS rating_user_count " "FROM r4_songs " "JOIN r4_song_sid USING (song_id) " "JOIN r4_song_ratings USING (song_id) " "WHERE album_id = %s AND sid = %s AND song_exists = TRUE AND user_id = %s", (album_id, sid, user_id)) rating_complete = False if user_data['rating_user_count'] >= num_songs: rating_complete = True album_rating = None if user_data['rating_user']: album_rating = float(user_data['rating_user']) album_fave = None existing_rating = db.c.fetch_row("SELECT album_rating_user, album_fave FROM r4_album_ratings WHERE album_id = %s AND user_id = %s AND sid = %s", (album_id, user_id, sid)) if existing_rating: album_fave = existing_rating['album_fave'] db.c.update("UPDATE r4_album_ratings SET album_rating_user = %s, album_fave = %s, album_rating_complete = %s WHERE user_id = %s AND album_id = %s AND sid = %s", (album_rating, album_fave, rating_complete, user_id, album_id, sid)) else: db.c.update("INSERT INTO r4_album_ratings (album_rating_user, album_fave, album_rating_complete, user_id, album_id, sid) VALUES (%s, %s, %s, %s, %s, %s)", (album_rating, album_fave, rating_complete, user_id, album_id, sid)) cache.set_album_rating(sid, album_id, user_id, { "rating_user": album_rating, "fave": album_fave, "rating_complete": rating_complete }) if target_sid == sid: toret = { "sid": sid, "id": album_id, "rating_user": album_rating, "rating_complete": rating_complete } return toret
def update_album_ratings(song_id, user_id): toreturn = [] for album_id in db.c.fetch_list("SELECT DISTINCT album_id FROM r4_song_sid WHERE song_id = %s", (song_id,)): user_data = db.c.fetch_row( "SELECT ROUND(CAST(AVG(song_rating_user) AS NUMERIC), 1) AS rating_user, " "COUNT(song_rating_user) AS rating_user_count " "FROM (SELECT DISTINCT song_id FROM r4_song_sid WHERE album_id = %s) AS temp " "JOIN r4_song_ratings USING (song_id) " "WHERE user_id = %s", (album_id, user_id)) num_songs = db.c.fetch_var("SELECT album_song_count FROM r4_albums WHERE album_id = %s", (album_id,)) rating_complete = False if user_data['rating_user_count'] >= num_songs: rating_complete = True album_rating = float(user_data['rating_user']) toreturn.append({ "id": album_id, "rating_user": album_rating, "rating_complete": rating_complete }) album_fave = None existing_rating = db.c.fetch_row("SELECT album_rating_user, album_fave FROM r4_album_ratings WHERE album_id = %s AND user_id = %s", (album_id, user_id)) if existing_rating: album_fave = existing_rating['album_fave'] db.c.update("UPDATE r4_album_ratings SET album_rating_user = %s, album_fave = %s, album_rating_complete = %s WHERE user_id = %s AND album_id = %s", (album_rating, album_fave, rating_complete, user_id, album_id)) else: db.c.update("INSERT INTO r4_album_ratings (album_rating_user, album_fave, album_rating_complete, user_id, album_id) VALUES (%s, %s, %s, %s, %s)", (album_rating, album_fave, rating_complete, user_id, album_id)) cache.set_album_rating(album_id, user_id, { "rating_user": album_rating, "fave": album_fave, "rating_complete": rating_complete }) return toreturn
def get_album_rating(sid, album_id, user_id): rating = cache.get_album_rating(sid, album_id, user_id) if not rating: rating = db.c.fetch_row("SELECT album_rating_user AS rating_user, album_fave AS fave FROM r4_album_ratings WHERE user_id = %s AND album_id = %s AND sid = %s", (user_id, album_id, sid)) if not rating: rating = { "rating_user": 0, "fave": None } cache.set_album_rating(sid, album_id, user_id, rating) return rating
def update_album_ratings(target_sid, song_id, user_id): toret = [] for row in db.c.fetch_all( "SELECT r4_songs.album_id, sid, album_song_count FROM r4_songs JOIN r4_album_sid USING (album_id) WHERE r4_songs.song_id = %s AND album_exists = TRUE", (song_id, ), ): album_id = row["album_id"] sid = row["sid"] num_songs = row["album_song_count"] user_data = db.c.fetch_row( "SELECT ROUND(CAST(AVG(song_rating_user) AS NUMERIC), 1) AS rating_user, " "COUNT(song_rating_user) AS rating_user_count " "FROM r4_songs " "JOIN r4_song_sid USING (song_id) " "JOIN r4_song_ratings USING (song_id) " "WHERE album_id = %s AND sid = %s AND song_exists = TRUE AND user_id = %s", (album_id, sid, user_id), ) rating_complete = False if user_data["rating_user_count"] >= num_songs: rating_complete = True album_rating = None if user_data["rating_user"]: album_rating = float(user_data["rating_user"]) existing_rating = db.c.fetch_row( "SELECT album_rating_user FROM r4_album_ratings WHERE album_id = %s AND user_id = %s AND sid = %s", (album_id, user_id, sid), ) if existing_rating: db.c.update( "UPDATE r4_album_ratings SET album_rating_user = %s, album_rating_complete = %s WHERE user_id = %s AND album_id = %s AND sid = %s", (album_rating, rating_complete, user_id, album_id, sid), ) else: db.c.update( "INSERT INTO r4_album_ratings (album_rating_user, album_rating_complete, user_id, album_id, sid) VALUES (%s, %s, %s, %s, %s)", (album_rating, rating_complete, user_id, album_id, sid), ) cache.set_album_rating( sid, album_id, user_id, { "rating_user": album_rating, "rating_complete": rating_complete }, ) if target_sid == sid: toret.append({ "sid": sid, "id": album_id, "rating_user": album_rating, "rating_complete": rating_complete, }) return toret
def get_album_rating(sid, album_id, user_id): rating = cache.get_album_rating(sid, album_id, user_id) if not rating: rating = db.c.fetch_row( "SELECT album_rating_user AS rating_user, album_fave AS fave, album_rating_complete AS rating_complete FROM r4_album_ratings WHERE user_id = %s AND album_id = %s AND sid = %s", (user_id, album_id, sid)) if not rating: rating = {"rating_user": 0, "fave": None, "rating_complete": False} cache.set_album_rating(sid, album_id, user_id, rating) return rating
def set_album_fave(sid, album_id, user_id, fave): db.c.start_transaction() exists = db.c.fetch_row( "SELECT * FROM r4_album_faves WHERE album_id = %s AND user_id = %s", (album_id, user_id), ) rating = None rating_complete = False if not exists: if ( db.c.update( "INSERT INTO r4_album_faves (album_id, user_id, album_fave) VALUES (%s, %s, %s)", (album_id, user_id, fave), ) == 0 ): log.debug( "rating", "Failed to insert record for fave %s %s, fave is: %s." % ("album", album_id, fave), ) return False else: if ( db.c.update( "UPDATE r4_album_faves SET album_fave = %s WHERE album_id = %s AND user_id = %s", (fave, album_id, user_id), ) == 0 ): log.debug( "rating", "Failed to update record for fave %s %s, fave is: %s." % ("album", album_id, fave), ) return False if (not exists and fave) or (not exists["album" + "_fave"] and fave): db.c.update( "UPDATE r4_album_sid SET album_fave_count = album_fave_count + 1 WHERE album_id = %s", (album_id,), ) elif exists and exists["album" + "_fave"] and not fave: db.c.update( "UPDATE r4_album_sid SET album_fave_count = album_fave_count - 1 WHERE album_id = %s", (album_id,), ) cache.set_album_rating( sid, album_id, user_id, {"rating_user": rating, "fave": fave, "rating_complete": rating_complete}, ) db.c.commit() return True
def update_album_ratings(target_sid, song_id, user_id): toret = None for row in db.c.fetch_all( "SELECT r4_songs.album_id, sid, album_song_count FROM r4_songs JOIN r4_album_sid USING (album_id) WHERE r4_songs.song_id = %s AND album_exists = TRUE", (song_id, )): album_id = row['album_id'] sid = row['sid'] num_songs = row['album_song_count'] user_data = db.c.fetch_row( "SELECT ROUND(CAST(AVG(song_rating_user) AS NUMERIC), 1) AS rating_user, " "COUNT(song_rating_user) AS rating_user_count " "FROM r4_songs " "JOIN r4_song_sid USING (song_id) " "JOIN r4_song_ratings USING (song_id) " "WHERE album_id = %s AND sid = %s AND song_exists = TRUE AND user_id = %s", (album_id, sid, user_id)) rating_complete = False if user_data['rating_user_count'] >= num_songs: rating_complete = True album_rating = None if user_data['rating_user']: album_rating = float(user_data['rating_user']) album_fave = None existing_rating = db.c.fetch_row( "SELECT album_rating_user, album_fave FROM r4_album_ratings WHERE album_id = %s AND user_id = %s AND sid = %s", (album_id, user_id, sid)) if existing_rating: album_fave = existing_rating['album_fave'] db.c.update( "UPDATE r4_album_ratings SET album_rating_user = %s, album_fave = %s, album_rating_complete = %s WHERE user_id = %s AND album_id = %s AND sid = %s", (album_rating, album_fave, rating_complete, user_id, album_id, sid)) else: db.c.update( "INSERT INTO r4_album_ratings (album_rating_user, album_fave, album_rating_complete, user_id, album_id, sid) VALUES (%s, %s, %s, %s, %s, %s)", (album_rating, album_fave, rating_complete, user_id, album_id, sid)) cache.set_album_rating( sid, album_id, user_id, { "rating_user": album_rating, "fave": album_fave, "rating_complete": rating_complete }) if target_sid == sid: toret = { "sid": sid, "id": album_id, "rating_user": album_rating, "rating_complete": rating_complete } return toret
def get_album_rating(sid, album_id, user_id): rating = cache.get_album_rating(sid, album_id, user_id) if not rating: rating = db.c.fetch_row( "SELECT album_rating_user AS rating_user, album_rating_complete AS rating_complete " "FROM r4_album_ratings " "WHERE user_id = %s AND album_id = %s AND sid = %s", (user_id, album_id, sid) ) if not rating: rating = {"rating_user": 0, "rating_complete": False} rating['fave'] = db.c.fetch_var("SELECT album_fave FROM r4_album_faves WHERE user_id = %s AND album_id = %s", (user_id, album_id)) or False cache.set_album_rating(sid, album_id, user_id, rating) return rating
def get_album_rating(sid, album_id, user_id): rating = cache.get_album_rating(sid, album_id, user_id) if not rating: rating = db.c.fetch_row( "SELECT album_rating_user AS rating_user, album_fave AS fave, album_rating_complete AS rating_complete " "FROM r4_album_ratings " "LEFT JOIN r4_album_faves USING (user_id, album_id) " "WHERE user_id = %s AND album_id = %s AND sid = %s", (user_id, album_id, sid) ) if not rating: rating = { "rating_user": 0, "fave": None, "rating_complete": False } cache.set_album_rating(sid, album_id, user_id, rating) return rating
def set_album_fave(sid, album_id, user_id, fave): db.c.start_transaction() exists = db.c.fetch_row( "SELECT * FROM r4_album_ratings WHERE album_id = %s AND user_id = %s AND sid = %s", (album_id, user_id, sid)) rating = None rating_complete = None if not exists: if db.c.update( "INSERT INTO r4_album_ratings (album_id, user_id, album_fave, sid) VALUES (%s, %s, %s, %s)", (album_id, user_id, fave, sid)) == 0: log.debug( "rating", "Failed to insert record for fave %s %s, fave is: %s." % ("album", album_id, fave)) return False else: rating = exists["album" + "_rating_user"] rating_complete = exists['album_rating_complete'] if db.c.update( "UPDATE r4_album_ratings SET album_fave = %s WHERE album_id = %s AND user_id = %s AND sid = %s", (fave, album_id, user_id, sid)) == 0: log.debug( "rating", "Failed to update record for fave %s %s, fave is: %s." % ("album", album_id, fave)) return False if (not exists and fave) or (not exists["album" + "_fave"] and fave): db.c.update( "UPDATE r4_album_sid SET album_fave_count = album_fave_count + 1 WHERE album_id = %s AND sid = %s", (album_id, sid)) elif (exists and exists["album" + "_fave"] and not fave): db.c.update( "UPDATE r4_album_sid SET album_fave_count = album_fave_count - 1 WHERE album_id = %s AND sid = %s", (album_id, sid)) if "album" == "album": cache.set_album_rating( sid, album_id, user_id, { "rating_user": rating, "fave": fave, "rating_complete": rating_complete }) elif "album" == "song": cache.set_song_rating(album_id, user_id, { "rating_user": rating, "fave": fave }) return True db.c.commit()
def get_album_rating(sid, album_id, user_id): rating = cache.get_album_rating(sid, album_id, user_id) if not rating: rating = db.c.fetch_row( "SELECT album_rating_user AS rating_user, album_rating_complete AS rating_complete " "FROM r4_album_ratings " "WHERE user_id = %s AND album_id = %s AND sid = %s", (user_id, album_id, sid)) if not rating: rating = {"rating_user": 0, "rating_complete": False} rating['fave'] = db.c.fetch_var( "SELECT album_fave FROM r4_album_faves WHERE user_id = %s AND album_id = %s", (user_id, album_id)) or False cache.set_album_rating(sid, album_id, user_id, rating) return rating
def update_album_ratings(song_id, user_id): toreturn = [] for album_id in db.c.fetch_list( "SELECT DISTINCT album_id FROM r4_song_sid WHERE song_id = %s", (song_id, )): user_data = db.c.fetch_row( "SELECT ROUND(CAST(AVG(song_rating_user) AS NUMERIC), 1) AS rating_user, " "COUNT(song_rating_user) AS rating_user_count " "FROM (SELECT DISTINCT song_id FROM r4_song_sid WHERE album_id = %s) AS temp " "JOIN r4_song_ratings USING (song_id) " "WHERE user_id = %s", (album_id, user_id)) num_songs = db.c.fetch_var( "SELECT album_song_count FROM r4_albums WHERE album_id = %s", (album_id, )) rating_complete = False if user_data['rating_user_count'] >= num_songs: rating_complete = True album_rating = float(user_data['rating_user']) toreturn.append({ "id": album_id, "rating_user": album_rating, "rating_complete": rating_complete }) album_fave = None existing_rating = db.c.fetch_row( "SELECT album_rating_user, album_fave FROM r4_album_ratings WHERE album_id = %s AND user_id = %s", (album_id, user_id)) if existing_rating: album_fave = existing_rating['album_fave'] db.c.update( "UPDATE r4_album_ratings SET album_rating_user = %s, album_fave = %s, album_rating_complete = %s WHERE user_id = %s AND album_id = %s", (album_rating, album_fave, rating_complete, user_id, album_id)) else: db.c.update( "INSERT INTO r4_album_ratings (album_rating_user, album_fave, album_rating_complete, user_id, album_id) VALUES (%s, %s, %s, %s, %s)", (album_rating, album_fave, rating_complete, user_id, album_id)) cache.set_album_rating( album_id, user_id, { "rating_user": album_rating, "fave": album_fave, "rating_complete": rating_complete }) return toreturn
def _set_fave(category, object_id, user_id, fave): if category != "album" and category != "song": raise Exception("Invalid favourite category.") exists = db.c.fetch_row("SELECT * FROM r4_" + category + "_ratings WHERE " + category + "_id = %s AND user_id = %s", (object_id, user_id)) rating = None rating_complete = None if not exists: if db.c.update("INSERT INTO r4_" + category + "_ratings (" + category + "_id, user_id, " + category + "_fave) VALUES (%s, %s, %s)", (object_id, user_id, fave)) == 0: return False else: rating = exists[category + "_rating_user"] if "album_rating_complete" in exists: rating_complete = exists['album_rating_complete'] if db.c.update("UPDATE r4_" + category + "_ratings SET " + category + "_fave = %s WHERE " + category + "_id = %s AND user_id = %s", (fave, object_id, user_id)) == 0: return False if category == "album": cache.set_album_rating(object_id, user_id, { "rating_user": rating, "fave": fave, "rating_complete": rating_complete }) elif category == "song": cache.set_song_rating(object_id, user_id, { "rating_user": rating, "fave": fave }) return True
def _set_fave(category, object_id, user_id, fave): if category != "album" and category != "song": raise Exception("Invalid favourite category.") exists = db.c.fetch_row( "SELECT * FROM r4_" + category + "_ratings WHERE " + category + "_id = %s AND user_id = %s", (object_id, user_id)) rating = None rating_complete = None if not exists: if db.c.update( "INSERT INTO r4_" + category + "_ratings (" + category + "_id, user_id, " + category + "_fave) VALUES (%s, %s, %s)", (object_id, user_id, fave)) == 0: return False else: rating = exists[category + "_rating_user"] if "album_rating_complete" in exists: rating_complete = exists['album_rating_complete'] if db.c.update( "UPDATE r4_" + category + "_ratings SET " + category + "_fave = %s WHERE " + category + "_id = %s AND user_id = %s", (fave, object_id, user_id)) == 0: return False if category == "album": cache.set_album_rating( object_id, user_id, { "rating_user": rating, "fave": fave, "rating_complete": rating_complete }) elif category == "song": cache.set_song_rating(object_id, user_id, { "rating_user": rating, "fave": fave }) return True