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