def select_leagues_for_user(user_id, exclude_properties=None):
if exclude_properties is None:
exclude_properties = []
leagues = []
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_LEAGUES_FOR_USER, (user_id, user_id))
for league_tup in cur.fetchall():
league_id, created, name, owner_id, status = league_tup
league = League(id=league_id,
created=created,
name=name,
owner_id=owner_id,
status=status)
cur.execute(SELECT_USERS_FOR_LEAGUE, (league_id, ))
for user_tup in cur.fetchall():
user_id, email, image_url, is_admin, joined, name, profile_bg = user_tup
user = User(user_id, email, image_url, is_admin, joined,
name, profile_bg)
league.users.append(user)
if user_id == league.owner_id:
league.owner = user
# If owner is not participating in league, retrieve
league.owner = league.owner or select_user(owner_id)
leagues.append(league)
return leagues
def insert_round(round):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (round.id, round.created, round.description, round.league.id,
round.name, round.status, round.submission_due_date,
round.vote_due_date)
cur.execute(INSERT_ROUND, values)
def insert_submission(submission):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
# Determine if user previously submitted
values = (submission.submission_period.id, submission.user.id)
cur.execute(SELECT_SUBMISSIONS_FROM_USER, values)
# If user has previous submissions, remove them
if cur.rowcount > 0:
ranked_tracks = cur.fetchone()[2]
# Remove votes for previously submitted tracks
values = (submission.submission_period.id, ranked_tracks.keys())
cur.execute(DELETE_VOTES_FOR_URIS, values)
# Remove previously submitted tracks
values = (submission.submission_period.id, submission.user.id)
cur.execute(DELETE_SUBMISSIONS, values)
# Insert tracks for new submission
for uri in submission.tracks:
# TODO Insert comments
values = (submission.created, submission.submission_period.id,
uri, submission.user.id, submission.count)
cur.execute(INSERT_SUBMISSION, values)
def select_previous_submission(user_id, spotify_uri, exclude_league_id):
created, league = None, None
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_PREVIOUS_SUBMISSION, (user_id, spotify_uri, exclude_league_id))
if cur.rowcount > 0:
created, league = cur.fetchone()
return created, league
def select_submissions_count():
submissions_count = -1
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_SUBMISSIONS_COUNT)
if cur.rowcount > 0:
submissions_count = cur.fetchone()[0]
return submissions_count
def select_votes_count():
votes_count = -1
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_VOTES_COUNT)
if cur.rowcount > 0:
votes_count = cur.fetchone()[0]
return votes_count
def select_memberships_placed(user_id):
placed = defaultdict(int)
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_MEMBERSHIPS_PLACED_FOR_USER, (user_id, ))
for placed_tup in cur.fetchall():
rank, count = placed_tup
placed[rank] = count
return placed
def select_bot(bot_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (bot_id,)
cur.execute(SELECT_BOT, values)
if cur.rowcount < 1:
return None
access_token, refresh_token, expires_at = cur.fetchone()
bot = Bot(id=bot_id, access_token=access_token, refresh_token=refresh_token, expires_at=expires_at)
return bot
def upsert_league_preferences(league):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (league.id, league.preferences.track_count,
league.preferences.point_bank_size,
league.preferences.max_points_per_song,
league.preferences.downvote_bank_size,
league.preferences.max_downvotes_per_song,
league.preferences.submission_reminder_time,
league.preferences.vote_reminder_time)
cur.execute(UPSERT_LEAGUE_PREFERENCES, values)
def select_league_preferences(league_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_LEAGUE_PREFERENCES, (league_id,))
if cur.rowcount < 1:
return None
lp = LeaguePreferences()
(lp.track_count, lp.point_bank_size, lp.max_points_per_song,
lp.downvote_bank_size, lp.max_downvotes_per_song,
lp.submission_reminder_time, lp.vote_reminder_time) = cur.fetchone()
return lp
def autocomplete():
results = []
term = request.form.get('query')
stmt = 'SELECT name, id FROM users WHERE name ILIKE %s OR name ILIKE %s ORDER BY 1 LIMIT 10'
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(stmt, ('%' + term, '%' + term + '%'))
for user_tup in cur.fetchall():
name, id = user_tup
if id != g.user.id:
results.append({'label': name, 'id': id})
return json.dumps(results)
def select_user_by_email(user_email):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_USER_BY_EMAIL, (user_email,))
if cur.rowcount < 1:
return None
user_id, image_url, is_admin, joined, name, profile_bg = cur.fetchone()
u = User(user_id, user_email, image_url, is_admin, joined, name, profile_bg)
# TODO This could be done in one fetch with a join
u.preferences = select_user_preferences(user_id)
return u
def admin_users():
stmt = "SELECT id, email, image_url, is_admin, joined, name, profile_bg FROM users ORDER BY name;"
postgres_conn = get_postgres_conn()
with postgres_conn:
with postgres_conn.cursor() as cur:
users = []
cur.execute(stmt)
for user_tup in cur.fetchall():
user_id, email, image_url, is_admin, joined, name, profile_bg = user_tup
users.append(
User(user_id, email, image_url, is_admin, joined, name,
profile_bg))
return {'user': g.user, 'users': users}
def insert_league(league):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (league.id, league.created, league.name,
league.owner.id, LeagueStatus.CREATED)
cur.execute(INSERT_LEAGUE, values)
upsert_league_preferences(league)
for user in league.users:
insert_membership(league, user)
for u in league.invited_users:
insert_invited_user(u, league.id)
def delete_league(league):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_ROUNDS_IN_LEAGUE, (league.id, ))
for round_id_tup in cur.fetchall():
round_id = round_id_tup[0]
cur.execute(DELETE_VOTES_FOR_ROUND, (round_id, ))
cur.execute(DELETE_SUBMISSIONS_FOR_ROUND, (round_id, ))
cur.execute(DELETE_INVITED_USERS, (league.id, ))
cur.execute(DELETE_MEMBERSHIPS, (league.id, ))
cur.execute(DELETE_ROUNDS, (league.id, ))
cur.execute(DELETE_LEAGUE_PREFERENCES, (league.id, ))
cur.execute(DELETE_LEAGUE, (league.id, ))
def upsert_user_preferences(user):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (user.id,
user.preferences.owner_all_users_submitted_notifications,
user.preferences.owner_all_users_voted_notifications,
user.preferences.owner_user_left_notifications,
user.preferences.owner_user_submitted_notifications,
user.preferences.owner_user_voted_notifications,
user.preferences.user_added_to_league_notifications,
user.preferences.user_playlist_created_notifications,
user.preferences.user_removed_from_league_notifications,
user.preferences.user_submit_reminder_notifications,
user.preferences.user_vote_reminder_notifications)
cur.execute(UPSERT_USER_PREFERENCES, values)
def admin_leagues():
stmt = 'SELECT id, created, name, owner_id, status FROM leagues ORDER BY name;'
leagues = []
postgres_conn = get_postgres_conn()
with postgres_conn:
with postgres_conn.cursor() as cur:
cur.execute(stmt)
for league_tup in cur.fetchall():
leagues.append(
League(id=league_tup[0],
created=league_tup[1],
name=league_tup[2],
owner_id=league_tup[3],
status=league_tup[4]))
return {'user': g.user, 'leagues': leagues}
def insert_vote(vote):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
# Remove previously submitted votes
values = (vote.submission_period.id, vote.user.id)
cur.execute(DELETE_VOTES, values)
to_persist = set(vote.votes.keys() + vote.comments.keys())
# Insert new votes
for spotify_uri in to_persist:
weight = vote.votes.get(spotify_uri, 0)
comment = vote.comments.get(spotify_uri, '')
if not (weight or comment):
continue
values = (vote.created, vote.submission_period.id,
spotify_uri, vote.user.id, weight, comment)
cur.execute(INSERT_VOTE, values)
def select_round(round_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_ROUND, (round_id, ))
if cur.rowcount < 1:
return None
round_tup = cur.fetchone()
return Round(
id=round_id,
league_id=round_tup[0],
created=round_tup[1],
description=round_tup[2],
name=round_tup[3],
playlist_url=round_tup[4],
status=round_tup[5],
submissions_due=utc.localize(round_tup[6]),
votes_due=utc.localize(round_tup[7]),
)
def select_user_preferences(user_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_USER_PREFERENCES, (user_id, ))
up = UserPreferences()
if cur.rowcount < 1:
return up
(up.owner_all_users_submitted_notifications,
up.owner_all_users_voted_notifications,
up.owner_user_left_notifications,
up.owner_user_submitted_notifications,
up.owner_user_voted_notifications,
up.user_added_to_league_notifications,
up.user_playlist_created_notifications,
up.user_removed_from_league_notifications,
up.user_submit_reminder_notifications,
up.user_vote_reminder_notifications) = cur.fetchone()
return up
def select_memberships_count(user_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_MEMBERSHIPS_COUNT, (user_id, ))
return cur.fetchone()[0]
def insert_membership(league, user):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (league.id, user.id)
cur.execute(INSERT_MEMBERSHIP, values)
def select_rounds_count():
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_ROUNDS_COUNT)
return cur.fetchone()[0]
def delete_membership(league, user):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(DELETE_MEMBERSHIP, (league.id, user.id))
def select_rounds_incomplete_count(league_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_ROUNDS_IN_LEAGUE_WITH_STATUS,
(league_id, RoundStatus.CREATED))
return cur.rowcount
def insert_user(user):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (user.id, user.email, user.image_url, user.joined,
user.name, user.profile_background)
cur.execute(INSERT_USER, values)
def delete_round(round):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(DELETE_VOTES_FOR_ROUND, (round.id, ))
cur.execute(DELETE_SUBMISSIONS_FOR_ROUND, (round.id, ))
cur.execute(DELETE_ROUND, (round.id, ))
def insert_invited_user(user, league_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
values = (user.id, user.email, league_id)
cur.execute(INSERT_INVITED_USER, values)
def delete_invited_user(invite_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(DELETE_INVITED_USER, (invite_id, ))
def select_league_id_for_round(round_id):
with get_postgres_conn() as conn:
with conn.cursor() as cur:
cur.execute(SELECT_LEAGUE_ID_FOR_ROUND, (round_id, ))
return cur.fetchone()[0]
