def get_by_id(review_id):
"""Get a review by its ID.
Args:
review_id (uuid): ID of the review.
Returns:
Dictionary with the following structure
{
"id": uuid,
"entity_id": uuid,
"entity_type": str,
"user_id": uuid,
"user": dict,
"edits": int,
"is_draft": bool,
"is_hidden": bool,
"language": str,
"license_id": str,
"source": str,
"source_url": str,
"last_revision: dict,
"votes": dict,
"popularity": int,
"rating": int,
"text": str,
"created": datetime,
"license": dict,
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT review.id AS id,
review.entity_id,
review.entity_type,
review.user_id,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
revision.id AS last_revision_id,
revision.timestamp,
revision.text,
revision.rating,
"user".email,
"user".created as user_created,
"user".display_name,
"user".show_gravatar,
"user".musicbrainz_id,
"user".is_blocked,
created_time.created,
license.full_name,
license.info_url
FROM review
JOIN revision ON revision.review_id = review.id
JOIN "user" ON "user".id = review.user_id
JOIN license ON license.id = license_id
JOIN (
SELECT review.id,
timestamp AS created
FROM review
JOIN revision ON review.id = revision.review_id
WHERE review.id = :review_id
ORDER BY revision.timestamp ASC
LIMIT 1
) AS created_time
ON created_time.id = review.id
ORDER BY timestamp DESC
"""), {
"review_id": review_id,
})
review = result.fetchone()
if not review:
raise db_exceptions.NoDataFoundException(
"Can't find review with ID: {id}".format(id=review_id))
review = dict(review)
review["rating"] = RATING_SCALE_1_5.get(review["rating"])
review["last_revision"] = {
"id": review.pop("last_revision_id"),
"timestamp": review.pop("timestamp"),
"text": review.get("text"),
"rating": review.get("rating"),
"review_id": review.get("id"),
}
review["user"] = User({
"id":
review["user_id"],
"display_name":
review.pop("display_name", None),
"is_blocked":
review.pop("is_blocked", False),
"show_gravatar":
review.pop("show_gravatar", False),
"musicbrainz_username":
review.pop("musicbrainz_id"),
"email":
review.pop("email"),
"created":
review.pop("user_created"),
})
review["license"] = {
"id": review["license_id"],
"info_url": review["info_url"],
"full_name": review["full_name"],
}
votes = db_revision.votes(review["last_revision"]["id"])
review["votes"] = {
"positive": votes["positive"],
"negative": votes["negative"],
}
review["popularity"] = review["votes"]["positive"] - review["votes"][
"negative"]
return review
def get_by_id(review_id):
"""Get a review by its ID.
Args:
review_id (uuid): ID of the review.
Returns:
Dictionary with the following structure
{
"id": uuid,
"entity_id": uuid,
"entity_type": str,
"user_id": uuid,
"user": dict,
"edits": int,
"is_draft": bool,
"is_hidden": bool,
"language": str,
"license_id": str,
"source": str,
"source_url": str,
"last_revision: dict,
"votes": dict,
"popularity": int,
"rating": int,
"text": str,
"license": dict,
"published_on": datetime,
}
"""
with db.engine.connect() as connection:
result = connection.execute(sqlalchemy.text("""
SELECT review.id AS id,
review.entity_id,
review.entity_type,
review.user_id,
review.edits,
review.is_draft,
review.is_hidden,
review.license_id,
review.language,
review.source,
review.source_url,
review.published_on,
revision.id AS last_revision_id,
revision.timestamp,
revision.text,
revision.rating,
"user".email,
"user".created as user_created,
"user".display_name,
"user".show_gravatar,
"user".musicbrainz_id,
"user".is_blocked,
license.full_name,
license.info_url
FROM review
JOIN revision ON revision.review_id = review.id
JOIN "user" ON "user".id = review.user_id
JOIN license ON license.id = license_id
WHERE review.id = :review_id
ORDER BY timestamp DESC
"""), {
"review_id": review_id,
})
review = result.fetchone()
if not review:
raise db_exceptions.NoDataFoundException("Can't find review with ID: {id}".format(id=review_id))
review = dict(review)
review["rating"] = RATING_SCALE_1_5.get(review["rating"])
review["last_revision"] = {
"id": review.pop("last_revision_id"),
"timestamp": review.pop("timestamp"),
"text": review.get("text"),
"rating": review.get("rating"),
"review_id": review.get("id"),
}
review["user"] = User({
"id": review["user_id"],
"display_name": review.pop("display_name", None),
"is_blocked": review.pop("is_blocked", False),
"show_gravatar": review.pop("show_gravatar", False),
"musicbrainz_username": review.pop("musicbrainz_id"),
"email": review.pop("email"),
"created": review.pop("user_created"),
})
review["license"] = {
"id": review["license_id"],
"info_url": review["info_url"],
"full_name": review["full_name"],
}
votes = db_revision.votes(review["last_revision"]["id"])
review["votes"] = {
"positive": votes["positive"],
"negative": votes["negative"],
}
review["popularity"] = review["votes"]["positive"] - review["votes"]["negative"]
return review