async def make_match_from_raw_db_data(row: list) -> Match:
match_id = int(row[0])
if match_id in match_library:
return match_library[match_id]
match_info = MatchInfo(race_info=await racedb.get_race_info_from_type_id(
int(row[1])) if row[1] is not None else RaceInfo(),
ranked=bool(row[9]),
is_best_of=bool(row[10]),
max_races=int(row[11]))
sheet_info = MatchGSheetInfo()
sheet_info.wks_id = row[14]
sheet_info.row = row[15]
new_match = Match(commit_fn=matchdb.write_match,
match_id=match_id,
match_info=match_info,
racer_1_id=int(row[2]),
racer_2_id=int(row[3]),
suggested_time=row[4],
finish_time=row[16],
r1_confirmed=bool(row[5]),
r2_confirmed=bool(row[6]),
r1_unconfirmed=bool(row[7]),
r2_unconfirmed=bool(row[8]),
cawmentator_id=row[12],
channel_id=int(row[13]) if row[13] is not None else None,
gsheet_info=sheet_info,
autogenned=bool(row[17]))
await new_match.initialize()
match_library[new_match.match_id] = new_match
return new_match
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 _do_execute(self, cmd):
if len(cmd.args[0]) != 1:
await cmd.channel.send(
'Wrong number of arguments for `{}`. (Enclose racer names with spaces inside quotes.)'
.format(self.mention))
return
await cmd_matchmake.make_match_from_cmd(
cmd=cmd,
racer_members=[cmd.author],
racer_names=[cmd.args[0]],
match_info=MatchInfo(ranked=True))
async def _do_execute(self, cmd):
# Parse arguments
if len(cmd.args) != 2:
await cmd.channel.send(
'Error: Wrong number of arguments for `{}`.'.format(
self.mention))
return
await cmd_matchmake.make_match_from_cmd(
cmd=cmd,
racer_names=[cmd.args[0], cmd.args[1]],
match_info=MatchInfo(ranked=True))
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 _do_execute(self, cmd):
if len(cmd.args[0]) != 1:
await self.client.send_message(
cmd.channel, 'Wrong number of arguments for `{0}`. '
'(Enclose racer names with spaces inside quotes.)'.format(
self.mention))
return
await cmd_match.make_match_from_cmd(cmd=cmd,
cmd_type=self,
racer_members=[cmd.author],
racer_names=[cmd.args[0]],
match_info=MatchInfo(ranked=False))
async def _get_match(r1_name: str, r2_name: str, time: datetime.datetime
or None, cawmentator_name: str or None,
gsheet_info: MatchGSheetInfo) -> Match:
racer_1 = await userlib.get_user(any_name=r1_name, register=False)
racer_2 = await userlib.get_user(any_name=r2_name, register=False)
cawmentator = await userlib.get_user(rtmp_name=cawmentator_name)
cawmentator_id = cawmentator.discord_id if cawmentator is not None else None
match_info = MatchInfo(ranked=True)
return await matchutil.make_match(racer_1_id=racer_1.user_id,
racer_2_id=racer_2.user_id,
match_info=match_info,
suggested_time=time,
cawmentator_id=cawmentator_id,
register=False,
gsheet_info=gsheet_info)
async def get_all_leagues() -> List[League]:
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`
""".format(leagues=tn('leagues')))
all_leagues = []
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)
all_leagues.append(
League(commit_fn=write_league,
league_tag=row[0],
league_name=row[1],
match_info=match_info,
worksheet_id=row[4]))
return all_leagues
async def make_league(self,
league_tag: str,
league_name: str,
match_info: Optional[MatchInfo] = None,
gsheet_id: str = None) -> League:
# noinspection PyIncorrectDocstring
"""Registers a new league
Parameters
----------
league_tag: str
A unique tag for the league, used for commands (e.g. "coh")
league_name: str
The name of the league (e.g. "Cadence of Hyrule Story Mode"
match_info: MatchInfo
The default MatchInfo for a match in this league
gsheet_id: str
The GSheet ID of the standings sheet for this league
Raises
------
LeagueAlreadyExists: If the league tag is already registered to a league
"""
if league_tag in self._league_lib:
raise necrobot.exception.LeagueAlreadyExists()
if match_info is None:
match_info = MatchInfo()
league = League(commit_fn=leaguedb.write_league,
league_tag=league_tag,
league_name=league_name,
match_info=match_info,
worksheet_id=gsheet_id)
league.commit()
self._league_lib[league_tag] = league
return league
def __init__(self,
commit_fn,
racer_1_id,
racer_2_id,
match_id=None,
suggested_time=None,
r1_confirmed=False,
r2_confirmed=False,
r1_unconfirmed=False,
r2_unconfirmed=False,
match_info=MatchInfo(),
cawmentator_id=None,
channel_id=None,
gsheet_info=None,
finish_time=None,
autogenned=False):
"""Create a `Match` object. There should be no need to call this directly; use `matchutil.make_match` instead,
since this needs to interact with the database.
Parameters
----------
commit_fn: Callable
Function for commiting to the database.
racer_1_id: int
The DB user ID of the first racer.
racer_2_id: int
The DB user ID of the second racer.
match_id: int
The DB unique ID of this match.
suggested_time: datetime.datetime
The time the match is suggested for. If no tzinfo, UTC is assumed.
r1_confirmed: bool
Whether the first racer has confirmed the match time.
r2_confirmed: bool
Whether the second racer has confirmed the match time.
r1_unconfirmed: bool
Whether the first racer wishes to unconfirm the match time.
r2_unconfirmed: bool
Whether the second racer wishes to unconfirm the match time.
match_info: MatchInfo
The type of match.
cawmentator_id: int
The DB unique ID of the cawmentator for this match.
channel_id: int
The discord.ID of the channel for this match, if any.
gsheet_info: MatchGSheetInfo
If this match was created from a GSheet, the worksheet and row it was created from.
finish_time: datetime.datetime
The time the match finished at. If no tzinfo, UTC is assumed.
"""
self._match_id = match_id
# Racers in the match
self._racer_1_id = racer_1_id # type: int
self._racer_1 = None # type: Optional[NecroUser]
self._racer_2_id = racer_2_id # type: int
self._racer_2 = None # type: Optional[NecroUser]
# Scheduling data
self._suggested_time = None # type: Optional[datetime.datetime]
self._finish_time = None # type: Optional[datetime.datetime]
self._set_suggested_time(suggested_time)
self._set_finish_time(finish_time)
self._confirmed_by_r1 = r1_confirmed # type: bool
self._confirmed_by_r2 = r2_confirmed # type: bool
self._r1_wishes_to_unconfirm = r1_unconfirmed # type: bool
self._r2_wishes_to_unconfirm = r2_unconfirmed # type: bool
# Format and race data
self._match_info = match_info # type: MatchInfo
# Other
self._cawmentator_id = int(
cawmentator_id
) if cawmentator_id is not None else None # type: int
self._channel_id = channel_id # type: int
self._gsheet_info = gsheet_info # type: MatchGSheetInfo
self._autogenned = autogenned # type: bool
# Commit function
self._commit = commit_fn # type: Callable[[], None]
async def create_league(schema_name: str) -> League:
"""Creates a new CoNDOR event with the given schema_name as its database.
Parameters
----------
schema_name: str
The name of the database schema for this event, and also the unique identifier for this event.
Raises
------
LeagueAlreadyExists
When the schema_name already exists.
"""
table_name_validator = re.compile(r'^[0-9a-zA-Z_$]+$')
if not table_name_validator.match(schema_name):
raise necrobot.exception.InvalidSchemaName()
params = (schema_name, )
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
SELECT `league_name`
FROM `leagues`
WHERE `schema_name`=%s
""", params)
for row in cursor:
raise necrobot.exception.LeagueAlreadyExists(row[0])
cursor.execute(
"""
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = %s
""", params)
for _ in cursor:
raise necrobot.exception.LeagueAlreadyExists(
'Schema exists, but is not a event.')
cursor.execute("""
CREATE SCHEMA `{schema_name}`
DEFAULT CHARACTER SET = utf8
DEFAULT COLLATE = utf8_general_ci
""".format(schema_name=schema_name))
cursor.execute(
"""
INSERT INTO `leagues`
(`schema_name`)
VALUES (%s)
""", params)
cursor.execute("""
CREATE TABLE `{schema_name}`.`entrants` (
`user_id` smallint unsigned NOT NULL,
PRIMARY KEY (`user_id`)
) DEFAULT CHARSET=utf8
""".format(schema_name=schema_name))
for tablename in ['matches', 'match_races', 'races', 'race_runs']:
cursor.execute(
"CREATE TABLE `{league_schema}`.`{table}` LIKE `{necrobot_schema}`.`{table}`"
.format(league_schema=schema_name,
necrobot_schema=Config.MYSQL_DB_NAME,
table=tablename))
def tablename(table):
return '`{league_schema}`.`{table}`'.format(
league_schema=schema_name, table=table)
cursor.execute("""
CREATE VIEW {race_summary} AS
SELECT
{matches}.`match_id` AS `match_id`,
{match_races}.`race_number` AS `race_number`,
`users_winner`.`user_id` AS `winner_id`,
`users_loser`.`user_id` AS `loser_id`,
`race_runs_winner`.`time` AS `winner_time`,
`race_runs_loser`.`time` AS `loser_time`
FROM
{matches}
JOIN {match_races} ON {matches}.`match_id` = {match_races}.`match_id`
JOIN `users` `users_winner` ON
IF( {match_races}.`winner` = 1,
`users_winner`.`user_id` = {matches}.`racer_1_id`,
`users_winner`.`user_id` = {matches}.`racer_2_id`
)
JOIN `users` `users_loser` ON
IF( {match_races}.`winner` = 1,
`users_loser`.`user_id` = {matches}.`racer_2_id`,
`users_loser`.`user_id` = {matches}.`racer_1_id`
)
LEFT JOIN {race_runs} `race_runs_winner` ON
`race_runs_winner`.`user_id` = `users_winner`.`user_id`
AND `race_runs_winner`.`race_id` = {match_races}.`race_id`
LEFT JOIN {race_runs} `race_runs_loser` ON
`race_runs_loser`.`user_id` = `users_loser`.`user_id`
AND `race_runs_loser`.`race_id` = {match_races}.`race_id`
WHERE NOT {match_races}.`canceled`
""".format(matches=tablename('matches'),
match_races=tablename('match_races'),
race_runs=tablename('race_runs'),
race_summary=tablename('race_summary')))
cursor.execute("""
CREATE VIEW {match_info} AS
SELECT
{matches}.`match_id` AS `match_id`,
`ud1`.`rtmp_name` AS `racer_1_name`,
`ud2`.`rtmp_name` AS `racer_2_name`,
{matches}.`is_best_of` AS `is_best_of`,
{matches}.`number_of_races` AS `number_of_races`,
COUNT(0) AS `num_finished`,
SUM((CASE
WHEN ({match_races}.`winner` = 1) THEN 1
ELSE 0
END)) AS `racer_1_wins`,
SUM((CASE
WHEN ({match_races}.`winner` = 2) THEN 1
ELSE 0
END)) AS `racer_2_wins`,
(CASE
WHEN
{matches}.`is_best_of`
THEN
GREATEST(
SUM(CASE WHEN ({match_races}.`winner` = 1) THEN 1 ELSE 0 END),
SUM(CASE WHEN ({match_races}.`winner` = 2) THEN 1 ELSE 0 END)
) >= ({matches}.`number_of_races` DIV 2) + 1
ELSE
COUNT(0) >= {matches}.`number_of_races`
END) AS `completed`
FROM
{match_races}
JOIN {matches} ON {matches}.`match_id` = {match_races}.`match_id`
JOIN `necrobot`.`users` `ud1` ON {matches}.`racer_1_id` = `ud1`.`user_id`
JOIN `necrobot`.`users` `ud2` ON {matches}.`racer_2_id` = `ud2`.`user_id`
WHERE
{match_races}.`canceled` = 0
GROUP BY
{match_races}.`match_id`
""".format(match_info=tablename('match_info'),
matches=tablename('matches'),
match_races=tablename('match_races')))
cursor.execute(
"""
CREATE VIEW {league_info} AS
SELECT *
FROM `leagues`
WHERE (`leagues`.`schema_name` = %s)
""".format(league_info=tablename('league_info')), params)
return League(commit_fn=write_league,
schema_name=schema_name,
league_name='<unnamed league>',
match_info=MatchInfo())