Ejemplo n.º 1
0
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()
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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
Ejemplo n.º 7
0
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
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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
Ejemplo n.º 11
0
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()
Ejemplo n.º 12
0
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
Ejemplo n.º 13
0
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
Ejemplo n.º 14
0
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
Ejemplo n.º 15
0
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