def create_round(started_at: datetime) -> int:
QUERY = f'''
INSERT INTO {TABLE_NAME} (started_at)
VALUES (%s)
RETURNING id'''
result = get_db().query(QUERY, [started_at])
return list(result)[0][0]
def add_vote(round_id: int, username: str, vote: int) -> int:
QUERY = f'''
INSERT INTO {TABLE_NAME} (round_id, username, vote)
VALUES (%s, %s, %s)
RETURNING id'''
result = get_db().query(QUERY, [round_id, username, vote])
return list(result)[0][0]
def get_winner(round_id):
QUERY = f'''
SELECT
v.id,
v.round_id,
v.vote,
v.username
FROM {TABLE_NAME} v
WHERE
v.round_id = %s
AND
v.vote = (
-- select the minimum unique vote
SELECT
min(vote_counts.vote) min_vote
FROM (
-- count each vote
SELECT
vote,
count(vote) vote_count
FROM votes
WHERE
round_id = %s
GROUP BY
vote
) vote_counts
WHERE vote_counts.vote_count = 1
)
'''
results = get_db().query(QUERY, [round_id, round_id])
result_list = list(results)
if len(result_list) == 1:
return create_vote(*result_list[0])
else:
return None
def has_active_round() -> int:
QUERY = f'''
SELECT COUNT({TABLE_NAME})
FROM {TABLE_NAME}
WHERE finished_at is NULL
'''
result = get_db().query(QUERY)
return list(result)[0][0] != 0
def get_stats(round_id: int) -> Stat:
QUERY = f'''
SELECT
vote,
COUNT(vote) count
FROM {TABLE_NAME}
WHERE round_id=%s
GROUP BY vote
'''
result = get_db().query(QUERY, [round_id])
vote_stats = list(result)
return create_stat(round_id, vote_stats)
def is_voted(round_id: int, username: str) -> bool:
QUERY = f'''
SELECT
COUNT({TABLE_NAME})
FROM {TABLE_NAME}
WHERE round_id=%s AND username=%s
'''
results = get_db().query(QUERY, [round_id, username])
result_list = list(results)
if len(result_list) == 1:
return result_list[0][0] != 0
else:
return False
def get_one_round(id: int) -> Union[RoundItem, None]:
QUERY = f"""
SELECT
r.id round_id,
r.started_at started_at,
r.finished_at finished_at,
COUNT(v.round_id) participants
FROM {TABLE_NAME} r
LEFT JOIN {votes.TABLE_NAME} v ON r.id = v.round_id
WHERE r.id = %s
GROUP BY v.round_id, r.id
LIMIT 1
;
"""
results = get_db().query(QUERY, [id])
return fill_round_item(results)
def get_all_rounds() -> Iterable[RoundListItem]:
QUERY = f"""
SELECT
r.id round_id,
r.started_at started_at,
r.finished_at finished_at,
COUNT(v.round_id) participants
FROM {TABLE_NAME} r
LEFT JOIN {votes.TABLE_NAME} v ON r.id = v.round_id
GROUP BY v.round_id, r.id
ORDER BY r.id DESC
LIMIT %s;
"""
results = get_db().query(QUERY, [LIMIT_OF_LISTING])
for row in results:
yield create_round_list_item(*row)
def get_recent_round() -> Union[RoundItem, None]:
QUERY = f"""
SELECT
temp.round_id,
temp.started_at,
temp.finished_at,
temp.participants
FROM (
SELECT
r.id round_id,
r.started_at started_at,
r.finished_at finished_at,
COUNT(v.round_id) participants
FROM {TABLE_NAME} r
LEFT JOIN {votes.TABLE_NAME} v ON r.id = v.round_id
GROUP BY v.round_id, r.id
ORDER BY r.id DESC
) temp
LIMIT 1
"""
results = get_db().query(QUERY)
return fill_round_item(results)
def finish_round(round_id: int, finished_at: datetime) -> int:
QUERY = f"UPDATE {TABLE_NAME} SET finished_at=%s WHERE id=(%s) RETURNING id"
result = get_db().query(QUERY, (finished_at, round_id))
return list(result)