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