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()