async def get_rating(discord_id: int) -> Rating:
async with DBConnect(commit=False) as cursor:
params = (discord_id, )
cursor.execute(
"""
SELECT trueskill_mu, trueskill_sigma
FROM ratings
WHERE discord_id=%s
""", params)
row = cursor.fetchone()
if row is not None:
return ratingutil.create_rating(mu=row[0], sigma=row[1])
# If here, there was no rating
async with DBConnect(commit=True) as cursor:
rating = ratingutil.create_rating()
params = (
discord_id,
rating.mu,
rating.sigma,
)
cursor.execute(
"""
INSERT INTO ratings
(discord_id, trueskill_mu, trueskill_sigma)
VALUES (%s, %s, %s)
""", params)
return rating
async def get_matchstats_raw(league_tag: str, user_id: int) -> list:
params = (
user_id,
league_tag,
)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
COUNT(*) AS wins,
MIN(winner_time) AS best_win,
AVG(winner_time) AS average_win
FROM {race_summary}
WHERE `winner_id` = %s AND `league_tag` = %s
LIMIT 1
""".format(race_summary=tn('race_summary')), params)
winner_data = cursor.fetchone()
if winner_data is None:
winner_data = [0, None, None]
cursor.execute(
"""
SELECT COUNT(*) AS losses
FROM {race_summary}
WHERE loser_id = %s AND `league_tag` = %s
LIMIT 1
""".format(race_summary=tn('race_summary')), params)
loser_data = cursor.fetchone()
if loser_data is None:
loser_data = [0]
return winner_data + loser_data
async def get_raw_match_data(match_id: int) -> list:
params = (match_id,)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id,
race_type_id,
racer_1_id,
racer_2_id,
suggested_time,
r1_confirmed,
r2_confirmed,
r1_unconfirmed,
r2_unconfirmed,
ranked,
is_best_of,
number_of_races,
cawmentator_id,
channel_id,
sheet_id,
sheet_row,
finish_time
FROM {matches}
WHERE match_id=%s
LIMIT 1
""".format(matches=tn('matches')),
params
)
return cursor.fetchone()
async def get_match_race_data(match_id: int) -> MatchRaceData:
params = (match_id,)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT canceled, winner
FROM {match_races}
WHERE match_id=%s
""".format(match_races=tn('match_races')),
params
)
finished = 0
canceled = 0
r1_wins = 0
r2_wins = 0
for row in cursor:
if bool(row[0]):
canceled += 1
else:
finished += 1
if int(row[1]) == 1:
r1_wins += 1
elif int(row[1]) == 2:
r2_wins += 1
return MatchRaceData(finished=finished, canceled=canceled, r1_wins=r1_wins, r2_wins=r2_wins)
async def get_all_users_with_any(names: Iterable[str]):
async with DBConnect(commit=False) as cursor:
if not names:
return []
params = tuple()
for name in names:
params += (name.lower(), )
format_strings = ','.join(['%s'] * len(params))
params = params + params + params
print(params)
cursor.execute(
"""
SELECT
discord_id,
discord_name,
twitch_name,
rtmp_name,
timezone,
user_info,
daily_alert,
race_alert,
user_id
FROM users
WHERE LOWER(discord_name) IN ({fm})
OR LOWER(twitch_name) IN ({fm})
OR LOWER(rtmp_name) IN ({fm})
""".format(fm=format_strings), params)
return cursor.fetchall()
async def submit(
necro_user: NecroUser,
category_race_info: RaceInfo,
category_score: int,
vod_url: str,
submission_time: datetime.datetime = None
) -> None:
category_type_id = await racedb.get_race_type_id(race_info=category_race_info, register=True)
params = (
necro_user.user_id,
category_type_id,
category_score,
vod_url,
submission_time
)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
INSERT INTO {speedruns}
(user_id, type_id, score, vod, submission_time)
VALUES (%s, %s, %s, %s, %s)
""".format(speedruns=tn('speedruns')),
params
)
async def is_condor_event(schema_name: str) -> bool:
"""
Parameters
----------
schema_name: str
The name of the schema for the event (and also the event's unique identifier).
Returns
-------
bool:
Whether the given schema refers to a CoNDOR event.
"""
params = (schema_name, )
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT `schema_name`
FROM `events`
WHERE `schema_name` = %s
LIMIT 1
""", params)
for _ in cursor:
return True
return False
async def get_event(schema_name: str) -> CondorEvent:
"""
Parameters
----------
schema_name: str
The name of the schema for the event (and also the event's unique identifier).
Returns
-------
str:
The string representing the deadline
"""
params = (schema_name, )
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT `event_name`, `deadline`, `gsheet_id`
FROM `events`
WHERE `schema_name` = %s
LIMIT 1
""", params)
for row in cursor:
return CondorEvent(schema_name=schema_name,
event_name=row[0],
deadline_str=row[1],
gsheet_id=row[2])
raise necrobot.exception.SchemaDoesNotExist()
async def get_most_races_leaderboard(category_name: str, limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = (category_name, limit,)
cursor.execute(
"""
SELECT
user_name,
num_races
FROM
(
SELECT
users.discord_name as user_name,
SUM(
IF(
race_types.category=%s
AND NOT {0}.private,
1, 0
)
) as num_races
FROM {1}
INNER JOIN users ON users.user_id = {1}.user_id
INNER JOIN {0} ON {0}.race_id = {1}.race_id
INNER JOIN race_types ON race_types.type_id = {0}.type_id
GROUP BY users.discord_name
) tbl1
ORDER BY num_races DESC
LIMIT %s
""".format(tn('races'), tn('race_runs')),
params)
return cursor.fetchall()
async def record_match_race(
match: Match,
race_number: int = None,
race_id: int = None,
winner: int = None,
canceled: bool = False,
contested: bool = False
) -> None:
if race_number is None:
race_number = await _get_new_race_number(match)
async with DBConnect(commit=True) as cursor:
params = (
match.match_id,
race_number,
race_id,
winner,
canceled,
contested
)
cursor.execute(
"""
INSERT INTO {match_races}
(match_id, race_number, race_id, winner, canceled, contested)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
race_id=VALUES(race_id),
winner=VALUES(winner),
canceled=VALUES(canceled),
contested=VALUES(contested)
""".format(match_races=tn('match_races')),
params
)
async def get_match_gsheet_duplication_number(match: Match) -> int:
"""
Parameters
----------
match: Match
A Match registered in the database.
Returns
-------
int
If this Match was created from a GSheet, the number of matches on the same worksheet and with
the same racers that appear in rows ahead of this match; otherwise, 0.
"""
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT COUNT(*)
FROM {matches}
WHERE
(racer_1_id = %(r1id)s OR racer_1_id = %(r2id)s)
AND (racer_2_id = %(r1id)s OR racer_2_id = %(r2id)s)
AND sheet_id = %(sheetid)s
AND sheet_row < %(sheetrow)s
""".format(matches=tn('matches')),
{
'r1id': match.racer_1.user_id,
'r2id': match.racer_2.user_id,
'sheetid': match.sheet_id,
'sheetrow': match.sheet_row,
}
)
return int(cursor.fetchone()[0])
async def get_league(league_tag: str) -> League:
"""
Parameters
----------
league_tag: str
The unique identifier for the league
Returns
-------
League
A League object for the event.
"""
params = (league_tag, )
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
{leagues}.`league_tag`,
{leagues}.`league_name`,
{leagues}.`number_of_races`,
{leagues}.`is_best_of`,
{leagues}.`worksheet_id`,
`race_types`.`character`,
`race_types`.`descriptor`,
`race_types`.`seeded`,
`race_types`.`amplified`,
`race_types`.`seed_fixed`
FROM {leagues}
LEFT JOIN `race_types` ON `leagues`.`race_type` = `race_types`.`type_id`
WHERE {leagues}.`league_tag` = %s
LIMIT 1
""".format(leagues=tn('leagues')), params)
for row in cursor:
race_info = RaceInfo()
if row[5] is not None:
race_info.set_char(row[5])
if row[6] is not None:
race_info.descriptor = row[6]
if row[7] is not None:
race_info.seeded = bool(row[7])
if row[8] is not None:
race_info.amplified = bool(row[8])
if row[9] is not None:
race_info.seed_fixed = bool(row[9])
match_info = MatchInfo(
max_races=int(row[2]) if row[2] is not None else None,
is_best_of=bool(row[3]) if row[3] is not None else None,
ranked=None,
race_info=race_info)
return League(commit_fn=write_league,
league_tag=row[0],
league_name=row[1],
match_info=match_info,
worksheet_id=row[4])
raise necrobot.exception.LeagueDoesNotExist()
async def get_all_matches_raw_data(
must_be_channeled: bool = False,
must_be_scheduled: bool = False,
order_by_time: bool = False,
racer_id: int = None,
limit: int = None
) -> list:
params = tuple()
where_query = 'TRUE'
if must_be_channeled:
where_query += " AND `channel_id` IS NOT NULL"
if must_be_scheduled:
where_query += " AND (`suggested_time` IS NOT NULL AND `r1_confirmed` AND `r2_confirmed`)"
if racer_id is not None:
where_query += " AND (`racer_1_id` = %s OR `racer_2_id` = %s)"
params += (racer_id, racer_id,)
order_query = ''
if order_by_time:
order_query = "ORDER BY `suggested_time` ASC"
limit_query = '' if limit is None else 'LIMIT {}'.format(limit)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id,
race_type_id,
racer_1_id,
racer_2_id,
suggested_time,
r1_confirmed,
r2_confirmed,
r1_unconfirmed,
r2_unconfirmed,
ranked,
is_best_of,
number_of_races,
cawmentator_id,
channel_id,
sheet_id,
sheet_row,
finish_time,
autogenned
FROM {matches}
WHERE {where_query} {order_query}
{limit_query}
""".format(
matches=tn('matches'),
where_query=where_query,
order_query=order_query,
limit_query=limit_query
), params)
return cursor.fetchall()
async def register_daily_message(daily_id, daily_type, message_id):
async with DBConnect(commit=True) as cursor:
params = (message_id, daily_id, daily_type,)
cursor.execute(
"""
UPDATE dailies
SET msg_id=%s
WHERE daily_id=%s AND type=%s
""",
params)
async def delete_from_daily(user_id, daily_id, daily_type):
async with DBConnect(commit=True) as cursor:
params = (user_id, daily_id, daily_type,)
cursor.execute(
"""
UPDATE daily_runs_uinfo
SET level=-1
WHERE user_id=%s AND daily_id=%s AND type=%s
""",
params)
async def create_daily(daily_id, daily_type, seed, message_id=0):
async with DBConnect(commit=True) as cursor:
params = (daily_id, daily_type, seed, message_id)
cursor.execute(
"""
INSERT INTO dailies
(daily_id, type, seed, msg_id)
VALUES (%s,%s,%s,%s)
""",
params)
async def _register_match(match: Match) -> None:
match_racetype_id = await racedb.get_race_type_id(race_info=match.race_info, register=True)
params = (
match_racetype_id,
match.racer_1.user_id,
match.racer_2.user_id,
match.suggested_time,
match.confirmed_by_r1,
match.confirmed_by_r2,
match.r1_wishes_to_unconfirm,
match.r2_wishes_to_unconfirm,
match.ranked,
match.is_best_of,
match.number_of_races,
match.cawmentator_id,
match.finish_time,
match.autogenned
)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
INSERT INTO {matches}
(
race_type_id,
racer_1_id,
racer_2_id,
suggested_time,
r1_confirmed,
r2_confirmed,
r1_unconfirmed,
r2_unconfirmed,
ranked,
is_best_of,
number_of_races,
cawmentator_id,
finish_time,
autogenned
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""".format(matches=tn('matches')),
params
)
cursor.execute("SELECT LAST_INSERT_ID()")
match.set_match_id(int(cursor.fetchone()[0]))
params = (match.racer_1.user_id, match.racer_2.user_id,)
cursor.execute(
"""
INSERT IGNORE INTO {entrants} (user_id)
VALUES (%s), (%s)
""".format(entrants=tn('entrants')),
params
)
async def _get_users_helpfn(discord_id, discord_name, twitch_name, rtmp_name,
timezone, user_id, case_sensitive, do_any):
async with DBConnect(commit=False) as cursor:
params = tuple()
if discord_id is not None:
params += (int(discord_id), )
if discord_name is not None:
params += (discord_name, ) if case_sensitive else (
discord_name.lower(), )
if twitch_name is not None:
params += (twitch_name, ) if case_sensitive else (
twitch_name.lower(), )
if rtmp_name is not None:
params += (rtmp_name, ) if case_sensitive else (
rtmp_name.lower(), )
if timezone is not None:
params += (timezone, )
if user_id is not None:
params += (user_id, )
connector = ' OR ' if do_any else ' AND '
where_query = ''
if discord_id is not None:
where_query += ' {0} discord_id=%s'.format(connector)
if discord_name is not None:
where_query += ' {0} discord_name=%s'.format(connector) if case_sensitive \
else ' {0} LOWER(discord_name)=%s'.format(connector)
if twitch_name is not None:
where_query += ' {0} twitch_name=%s'.format(connector) if case_sensitive \
else ' {0} LOWER(twitch_name)=%s'.format(connector)
if rtmp_name is not None:
where_query += ' {0} rtmp_name=%s'.format(connector) if case_sensitive \
else ' {0} LOWER(rtmp_name)=%s'.format(connector)
if timezone is not None:
where_query += ' {0} timezone=%s'.format(connector)
if user_id is not None:
where_query += ' {0} user_id=%s'.format(connector)
where_query = where_query[len(connector):] if where_query else 'TRUE'
cursor.execute(
"""
SELECT
discord_id,
discord_name,
twitch_name,
rtmp_name,
timezone,
user_info,
daily_alert,
race_alert,
user_id
FROM users
WHERE {0}
""".format(where_query), params)
return cursor.fetchall()
async def register_match_channel(match_id: int, channel_id: int or None) -> None:
params = (channel_id, match_id,)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
UPDATE {matches}
SET channel_id=%s
WHERE match_id=%s
""".format(matches=tn('matches')),
params
)
async def set_verified(run_id: int, verified: bool):
async with DBConnect(commit=True) as cursor:
params = (verified, run_id,)
cursor.execute(
"""
UPDATE {speedruns}
SET verified = %s
WHERE submission_id = %s
""".format(speedruns=tn('speedruns')),
params
)
async def get_daily_seed(daily_id, daily_type):
async with DBConnect(commit=False) as cursor:
params = (daily_id, daily_type,)
cursor.execute(
"""
SELECT seed
FROM dailies
WHERE daily_id=%s AND type=%s
""",
params)
return cursor.fetchall()
async def get_match_id(
racer_1_id: int,
racer_2_id: int,
scheduled_time: datetime.datetime = None
) -> int or None:
"""Attempt to find a match between the two racers
If multiple matches are found, prioritize as follows:
1. Prefer matches closer to scheduled_time, if scheduled_time is not None
2. Prefer channeled matches
3. Prefer the most recent scheduled match
4. Randomly
Parameters
----------
racer_1_id: int
The user ID of the first racer
racer_2_id: int
The user ID of the second racer
scheduled_time: datetime.datetime or None
The approximate time to search around, or None to skip this priority
Returns
-------
Optional[int]
The match ID, if one is found.
"""
param_dict = {
'racer1': racer_1_id,
'racer2': racer_2_id,
'time': scheduled_time
}
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id,
suggested_time,
channel_id,
ABS(`suggested_time` - '2017-23-04 12:00:00') AS abs_del
FROM {matches}
WHERE
(racer_1_id=%(racer1)s AND racer_2_id=%(racer2)s) OR (racer_1_id=%(racer2)s AND racer_2_id=%(racer1)s)
ORDER BY
IF(%(time)s IS NULL, 0, -ABS(`suggested_time` - %(time)s)) DESC,
`channel_id` IS NULL ASC,
`suggested_time` DESC
LIMIT 1
""".format(matches=tn('matches')),
param_dict
)
row = cursor.fetchone()
return int(row[0]) if row is not None else None
async def has_registered_daily(user_id, daily_id, daily_type):
async with DBConnect(commit=False) as cursor:
params = (user_id, daily_id, daily_type,)
cursor.execute(
"""
SELECT user_id
FROM daily_runs_uinfo
WHERE user_id=%s AND daily_id=%s AND type=%s
""",
params)
return cursor.fetchone() is not None
async def get_race_type_id(race_info: RaceInfo,
register: bool = False) -> int or None:
params = (
race_info.character_str,
race_info.descriptor,
race_info.seeded,
race_info.amplified,
race_info.seed_fixed,
)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT `type_id`
FROM `race_types`
WHERE `character`=%s
AND `descriptor`=%s
AND `seeded`=%s
AND `amplified`=%s
AND `seed_fixed`=%s
LIMIT 1
""", params)
row = cursor.fetchone()
if row is not None:
return int(row[0])
# If here, the race type was not found
if not register:
return None
# Create the new race type
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
INSERT INTO race_types
(`character`, descriptor, seeded, amplified, seed_fixed)
VALUES (%s, %s, %s, %s, %s)
""", params)
cursor.execute("SELECT LAST_INSERT_ID()")
return int(cursor.fetchone()[0])
async def write_match(match: Match):
if not match.is_registered:
await _register_match(match)
match_racetype_id = await racedb.get_race_type_id(race_info=match.race_info, register=True)
params = (
match_racetype_id,
match.racer_1.user_id,
match.racer_2.user_id,
match.suggested_time,
match.confirmed_by_r1,
match.confirmed_by_r2,
match.r1_wishes_to_unconfirm,
match.r2_wishes_to_unconfirm,
match.ranked,
match.is_best_of,
match.number_of_races,
match.cawmentator_id,
match.channel_id,
match.sheet_id,
match.sheet_row,
match.finish_time,
match.autogenned,
match.match_id,
)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
UPDATE {matches}
SET
race_type_id=%s,
racer_1_id=%s,
racer_2_id=%s,
suggested_time=%s,
r1_confirmed=%s,
r2_confirmed=%s,
r1_unconfirmed=%s,
r2_unconfirmed=%s,
ranked=%s,
is_best_of=%s,
number_of_races=%s,
cawmentator_id=%s,
channel_id=%s,
sheet_id=%s,
sheet_row=%s,
finish_time=%s,
autogenned=%s
WHERE match_id=%s
""".format(matches=tn('matches')),
params
)
async def get_daily_message_id(daily_id, daily_type):
async with DBConnect(commit=False) as cursor:
params = (daily_id, daily_type,)
cursor.execute(
"""
SELECT msg_id
FROM dailies
WHERE daily_id=%s AND type=%s
""",
params)
row = cursor.fetchone()
return int(row[0]) if row is not None else 0
async def get_league(schema_name: str) -> League:
"""
Parameters
----------
schema_name: str
The name of the schema for the event (and also the event's unique identifier).
Returns
-------
League
A League object for the event.
"""
params = (schema_name, )
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
`leagues`.`league_name`,
`leagues`.`number_of_races`,
`leagues`.`is_best_of`,
`leagues`.`ranked`,
`leagues`.`gsheet_id`,
`leagues`.`deadline`,
`race_types`.`category`,
`race_types`.`descriptor`,
`race_types`.`seeded`
FROM `leagues`
LEFT JOIN `race_types` ON `leagues`.`race_type` = `race_types`.`type_id`
WHERE `leagues`.`schema_name` = %s
LIMIT 1
""", params)
for row in cursor:
if all(i is not None for i in row[6:9]):
race_info = RaceInfo(Category.fromstr(row[6]), row[7],
bool(row[8]))
else:
race_info = RaceInfo()
match_info = MatchInfo(
max_races=int(row[1]) if row[1] is not None else None,
is_best_of=bool(row[2]) if row[2] is not None else None,
ranked=bool(row[3]) if row[3] is not None else None,
race_info=race_info)
return League(commit_fn=write_league,
schema_name=schema_name,
league_name=row[0],
match_info=match_info,
gsheet_id=row[4],
deadline=row[5])
raise necrobot.exception.LeagueDoesNotExist()
async def get_largest_race_number(user_id: int) -> int:
async with DBConnect(commit=False) as cursor:
params = (user_id, )
cursor.execute(
"""
SELECT race_id
FROM {0}
WHERE user_id = %s
ORDER BY race_id DESC
LIMIT 1
""".format(tn('race_runs')), params)
row = cursor.fetchone()
return int(row[0]) if row is not None else 0
async def get_daily_times(daily_id, daily_type):
async with DBConnect(commit=False) as cursor:
params = (daily_id, daily_type,)
cursor.execute(
"""
SELECT users.discord_name,daily_runs.level,daily_runs.time
FROM daily_runs
INNER JOIN users ON daily_runs.user_id=users.user_id
WHERE daily_runs.daily_id=%s AND daily_runs.type=%s
ORDER BY daily_runs.level DESC, daily_runs.time ASC
""",
params)
return cursor.fetchall()
async def get_fastest_times_leaderboard(character_name: str, amplified: bool,
limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = {
'character': character_name,
'limit': limit,
}
cursor.execute(
"""
SELECT
users.`discord_name`,
mintimes.`min_time`,
{races}.`seed`,
{races}.`timestamp`
FROM
(
SELECT user_id, MIN(time) AS `min_time`
FROM {race_runs}
INNER JOIN {races} ON {races}.`race_id` = {race_runs}.`race_id`
INNER JOIN race_types ON race_types.`type_id` = {races}.`type_id`
WHERE
{race_runs}.`time` > 0
AND {race_runs}.`level` = -2
AND ({races}.`timestamp` > '2017-07-12' OR NOT race_types.`amplified`)
AND race_types.`character` = %(character)s
AND race_types.`descriptor` = 'All-zones'
AND race_types.`seeded`
AND {not_amplified}race_types.`amplified`
AND NOT {races}.`private`
GROUP BY user_id
) mintimes
INNER JOIN {race_runs}
ON ({race_runs}.`user_id` = mintimes.`user_id` AND {race_runs}.`time` = mintimes.`min_time`)
INNER JOIN {races} ON {races}.`race_id` = {race_runs}.`race_id`
INNER JOIN race_types ON race_types.`type_id` = {races}.`type_id`
INNER JOIN users ON users.`user_id` = mintimes.`user_id`
WHERE
{race_runs}.`level` = -2
AND ({races}.`timestamp` > '2017-07-12' OR NOT race_types.`amplified`)
AND race_types.`character` = %(character)s
AND race_types.`descriptor` = 'All-zones'
AND race_types.`seeded`
AND {not_amplified}race_types.`amplified`
AND NOT {races}.`private`
GROUP BY {race_runs}.`user_id`
ORDER BY mintimes.min_time ASC
LIMIT %(limit)s
""".format(races=tn('races'),
race_runs=tn('race_runs'),
not_amplified=('' if amplified else 'NOT ')), params)
return cursor.fetchall()
