def get(user_id, revision_id):
"""Get vote cast by a user on a revision.
Args:
user_id (uuid): ID of a user.
revision_id (id): ID of a review revision that the vote is associated with.
Returns:
Dictionary with the following structure:
{
"user_id": (uuid),
"revision_id": (int),
"vote": (bool),
"rated_at": (datetime)
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT user_id, revision_id, vote, rated_at
FROM vote
WHERE user_id = :user_id AND revision_id = :revision_id
"""), {
"user_id": user_id,
"revision_id": revision_id,
})
row = result.fetchone()
if not row:
raise db_exceptions.NoDataFoundException(
"Cannot find specified vote.")
return dict(row)
def get_revision_number(review_id, revision_id):
"""Get revision number of the review from the revision_id.
Args:
review_id (uuid): ID of the review.
revision_id (int): ID of the revision whose number is to be fetched.
Returns:
rev_num(int): revision number of the revision.
"""
with db.engine.connect() as connection:
result = connection.execute(sqlalchemy.text("""
SELECT row_number
FROM (
SELECT row_number() over(order by timestamp),
id
FROM revision
WHERE review_id = :review_id
) AS indexed_revisions
WHERE id = :revision_id
"""), {
"review_id": review_id,
"revision_id": revision_id,
})
rev_num = result.fetchone()[0]
if not rev_num:
raise db_exceptions.NoDataFoundException("Can't find the revision with id={} for specified review.".
format(revision_id))
return rev_num
def get(review_id, limit=1, offset=0):
"""Get revisions on a review ordered by the timestamp
Args:
review_id (uuid): ID of review
limit (int): The number of revisions to return(default=1)
offset (int): The number of revisions to skip from top(default=0)
Returns:
List of dictionaries of revisions of the review
with the following structure:
{
"id": (int),
"review_id": (uuid),
"timestamp": (datetime),
"text": (string),
"rating": (int),
"votes_positive": (int),
"votes_negative": (int),
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT id,
review_id,
timestamp,
text,
rating,
SUM(
CASE WHEN vote='t' THEN 1 ELSE 0 END
) AS votes_positive,
SUM(
CASE WHEN vote='f' THEN 1 ELSE 0 END
) AS votes_negative
FROM revision
LEFT JOIN vote
ON vote.revision_id = revision.id
WHERE review_id = :review_id
GROUP BY revision.id
ORDER BY timestamp DESC
OFFSET :offset
LIMIT :limit
"""), {
"review_id": review_id,
"offset": offset,
"limit": limit
})
rows = result.fetchall()
if not rows:
raise db_exceptions.NoDataFoundException(
"Cannot find specified review.")
rows = [dict(row) for row in rows]
# Convert ratings to values on a scale 1-5
for row in rows:
row["rating"] = RATING_SCALE_1_5.get(row["rating"])
return rows
def get_users_with_comment_count(from_date=date(1970, 1, 1),
to_date=date.today() + timedelta(1)):
""" Gets list of users with number of comments they've submitted
Args:
from_date(datetime): Date from which contributions by users are to be considered.
to_date(datetime): Date upto which contributions by users are to be considered.
Returns:
List of dictionaries where each dictionary has the following structure:
{
"id": (uuid),
"display_name": (str),
"comment_count": (int),
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT id,
display_name,
COALESCE(cc, 0) AS comment_count
FROM "user"
LEFT JOIN (SELECT user_id,
count(*) AS cc
FROM comment
LEFT JOIN (SELECT comment_id,
min(timestamp) AS commented_at
FROM comment_revision
GROUP BY comment_id) AS comment_create
ON comment.id = comment_create.comment_id
WHERE commented_at >= :from_date AND commented_at <= :to_date
GROUP BY user_id) AS num_comment
ON "user".id = num_comment.user_id
"""), {
"from_date": from_date,
"to_date": to_date,
})
commenters = result.fetchall()
if not commenters:
raise db_exceptions.NoDataFoundException(
"Can't get users with comment count!")
commenters = [dict(commenter) for commenter in commenters]
return commenters
def get_users_with_review_count(from_date=date(1970, 1, 1),
to_date=date.today() + timedelta(1)):
""" Gets list of users with number of reviews they've submitted
Args:
from_date(datetime): Date from which contributions by users are to be considered.
to_date(datetime): Date upto which contributions by users are to be considered.
Returns:
List of dictionaries where each dictionary has the following structure:
{
"id": (uuid),
"display_name": (str),
"review_count": (int),
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT id,
display_name,
COALESCE(rc, 0) AS review_count
FROM "user"
LEFT JOIN (SELECT user_id,
count(*) AS rc
FROM review
WHERE published_on >= :from_date AND published_on <= :to_date
AND review.is_draft = 'f'
AND review.is_hidden = 'f'
GROUP BY user_id) AS num_review
ON "user".id = num_review.user_id
"""), {
"from_date": from_date,
"to_date": to_date,
})
reviewers = result.fetchall()
if not reviewers:
raise db_exceptions.NoDataFoundException(
"Can't get users with review count!")
reviewers = [dict(reviewer) for reviewer in reviewers]
return reviewers
def get_votes(review_id):
"""Get vote count for the all revisions of a review
Args:
review_id (uuid): ID of a review
Returns:
Dictionary of revisions of a review with a dictionary
of count of positive and negative votes.
{
"revision.id":"{"positive": (int), "negative": (int)}"
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT DISTINCT revision.id, user_id, vote, timestamp
FROM revision
LEFT JOIN vote
ON vote.revision_id = revision.id
WHERE review_id = :review_id
ORDER BY timestamp DESC
"""), {
"review_id": review_id,
})
rows = result.fetchall()
if not rows:
raise db_exceptions.NoDataFoundException(
"Cannot find votes for review(ID: {})".format(review_id))
votes = dict()
for row in rows:
revision = row.id
if revision not in votes:
votes[revision] = {'positive': 0, 'negative': 0}
if row.vote == False:
votes[revision]['negative'] += 1
elif row.vote == True:
votes[revision]['positive'] += 1
return votes
def get_users_with_vote_count(from_date=date(1970, 1, 1),
to_date=date.today() + timedelta(1)):
""" Gets list of users with number of votes they've submitted
Args:
from_date(datetime): Date from which contributions by users are to be considered.
to_date(datetime): Date upto which contributions by users are to be considered.
Returns:
List of dictionaries where each dictionary has the following structure:
{
"id": (uuid),
"display_name": (str),
"vote_count": (int),
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT id,
display_name,
COALESCE(vc, 0) AS vote_count
FROM "user"
LEFT JOIN (SELECT user_id,
count(*) AS vc
FROM vote
WHERE rated_at >= :from_date AND rated_at <= :to_date
GROUP BY user_id) AS num_votes
ON "user".id = num_votes.user_id
"""), {
"from_date": from_date,
"to_date": to_date,
})
voters = result.fetchall()
if not voters:
raise db_exceptions.NoDataFoundException(
"Can't get users with vote count!")
voters = [dict(voter) for voter in voters]
return voters
def get_scopes(token_id):
"""Returns the scopes of an application.
Args:
token_id (int): ID of the OAuth Token.
Returns:
scopes: (list).
"""
with db.engine.connect() as connection:
result = connection.execute(sqlalchemy.text("""
SELECT scopes
FROM oauth_token
WHERE id = :token_id
"""), {
"token_id": token_id,
})
scopes = result.fetchone()
if not scopes:
raise db_exceptions.NoDataFoundException("No token exists with ID: {}".format(token_id))
if scopes[0] is None:
return list()
return scopes[0].split()
def get(entity_id, entity_type):
"""Get average rating from entity_id
Args:
entity_id (uuid): ID of the entity
entity_type (str): Type of the entity
Returns:
Dictionary with the following structure
{
"entity_id": uuid,
"entity_type": str("release group", "event", "place"),
"rating": int,
"count": int,
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT entity_id,
entity_type,
rating,
count
FROM avg_rating
WHERE entity_id = :entity_id
AND entity_type = :entity_type
"""), {
"entity_id": entity_id,
"entity_type": entity_type
})
avg_rating = result.fetchone()
if not avg_rating:
raise db_exceptions.NoDataFoundException(
"""No rating for the entity with ID: {id} and Type: {type}""".
format(id=entity_id, type=entity_type))
avg_rating = dict(avg_rating)
avg_rating["rating"] = round(avg_rating["rating"] / 20, 1)
return avg_rating
def get(review_id, limit=1, offset=0):
"""Get revisions on a review ordered by the timestamp
Args:
review_id (uuid): ID of review
limit (int): The number of revisions to return(default=1)
offset (int): The number of revisions to skip from top(default=0)
Returns:
List of dictionaries of revisions of the review
with the following structure:
{
"id": (int),
"review_id": (uuid),
"timestamp": (datetime),
"text": (string)
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT id, review_id, timestamp, text
FROM revision
WHERE review_id = :review_id
ORDER BY timestamp DESC
OFFSET :offset
LIMIT :limit
"""), {
"review_id": review_id,
"offset": offset,
"limit": limit
})
rows = result.fetchall()
if not rows:
raise db_exceptions.NoDataFoundException(
"Cannot find specified review.")
rows = [dict(row) for row in rows]
return rows
def get_client(client_id):
"""Get info about an OAuth Client.
Args:
client_id(str): ID of the client.
Returns:
Dict with the following structure:
{
"client_id": str,
"client_secret": str,
"redirect_uri": str,
"user_id": uuid,
"name": str,
"desc": str,
"website": str,
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT client_id,
client_secret,
redirect_uri,
user_id,
name,
"desc",
website
FROM oauth_client
WHERE client_id = :client_id
"""), {
"client_id": client_id,
})
row = result.fetchone()
if not row:
raise db_exceptions.NoDataFoundException(
"Can't find OAuth client with ID: {id}".format(id=client_id))
return dict(row)
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(comment_id):
""" Get a comment by its ID.
Args:
comment_id (uuid): the ID of the comment
Returns:
dict representing the comment. The dict has the following structure:
{
'id',
'review_id',
'user_id',
'edits',
'is_hidden',
'last_revision': {
'id',
'timestamp',
'text'
},
'user',
}
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT c.id,
c.review_id,
c.user_id,
c.edits,
c.is_hidden,
cr.id as last_revision_id,
cr.timestamp,
cr.text,
"user".email,
"user".created as user_created,
"user".display_name,
"user".show_gravatar,
"user".musicbrainz_id,
"user".is_blocked
FROM comment c
JOIN comment_revision cr ON c.id = cr.comment_id
JOIN "user" ON c.user_id = "user".id
WHERE c.id = :comment_id
ORDER BY cr.timestamp DESC
LIMIT 1
"""), {
'comment_id': comment_id,
})
comment = result.fetchone()
if not comment:
raise db_exceptions.NoDataFoundException(
'Can\'t find comment with ID: {id}'.format(id=comment_id))
comment = dict(comment)
comment['last_revision'] = {
'id': comment.pop('last_revision_id'),
'timestamp': comment.pop('timestamp'),
'text': comment.pop('text'),
}
comment['user'] = User({
'id':
comment['user_id'],
'display_name':
comment.pop('display_name'),
'email':
comment.pop('email'),
'created':
comment.pop('user_created'),
'show_gravatar':
comment.pop('show_gravatar'),
'musicbrainz_username':
comment.pop('musicbrainz_id'),
'is_blocked':
comment.pop('is_blocked')
})
return comment
