def played_cards(where: str = '1 = 1',
season_id: Optional[int] = None) -> List[Card]:
sql = """
SELECT
card AS name,
{all_select},
{season_select}, -- We use the season data on the homepage to calculate movement, even though we no longer use it on /cards/.
{week_select}
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{season_join}
{nwdl_join}
WHERE ({where}) AND ({season_query})
GROUP BY
dc.card
ORDER BY
all_num_decks DESC,
SUM(dsum.wins - dsum.losses) DESC,
name
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
week_select=deck.nwdl_week_select(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join(),
where=where,
season_query=query.season_query(season_id))
cs = [Container(r) for r in db().execute(sql)]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def leaderboards(where: str = "ct.name = 'Gatherling'",
season_id: Optional[int] = None) -> List[Dict[str, Any]]:
sql = """
SELECT
p.id AS person_id,
{person_query} AS person,
cs.name AS competition_series_name,
sp.name AS sponsor_name,
COUNT(DISTINCT d.id) AS tournaments,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
COUNT(DISTINCT d.id) + SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS points
FROM
competition AS c
INNER JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
sponsor AS sp ON sp.id = cs.sponsor_id
INNER JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
INNER JOIN
deck AS d ON d.competition_id = c.id
INNER JOIN
person AS p ON d.person_id = p.id
LEFT JOIN
deck_match AS dm ON dm.deck_id = d.id
LEFT JOIN
deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> d.id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
cs.id,
p.id
ORDER BY
cs.id,
points DESC,
wins DESC,
tournaments DESC,
person
""".format(person_query=query.person_query(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id))
results = []
current: Dict[str, Any] = {}
for row in db().select(sql):
k = row['competition_series_name']
if current.get('competition_series_name', None) != k:
if len(current) > 0:
results.append(current)
current = {
'competition_series_name': row['competition_series_name'],
'entries': [],
'sponsor_name': row['sponsor_name']
}
row.pop('competition_series_name')
current['entries'] = current['entries'] + [Container(row)]
if len(current) > 0:
results.append(current)
return results
def load_matches_count(where: str = 'TRUE',
season_id: Union[int, str, None] = None) -> int:
competition_join = query.competition_join()
season_join = query.season_join()
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
COUNT(DISTINCT m.id)
FROM
`match` AS m
INNER JOIN
deck_match AS dm ON m.id = dm.match_id
INNER JOIN
deck AS d ON dm.deck_id = d.id
INNER JOIN
deck_cache AS dc ON d.id = dc.deck_id
INNER JOIN
person AS p ON d.person_id = p.id
LEFT JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
LEFT JOIN
deck AS od ON odm.deck_id = od.id
LEFT JOIN
deck_cache AS odc ON od.id = odc.deck_id
LEFT JOIN
person AS o ON od.person_id = o.id
{competition_join}
{season_join}
WHERE
{where}
AND
{season_query}
"""
return int(db().value(sql))
def load_matchups(archetype_id, season_id=None):
sql = """
SELECT
oa.id,
oa.name,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS all_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS all_losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS all_draws,
IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS all_win_percent
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id)
{season_join}
WHERE
(a.id = %s) AND ({season_query})
GROUP BY
oa.id
ORDER BY
`all_wins` DESC, oa.name
""".format(season_join=query.season_join(),
season_query=query.season_query(season_id))
return [Container(m) for m in db().execute(sql, [archetype_id])]
def load_people_stats(
where: str,
season_id: Optional[int] = None) -> Dict[int, Dict[str, int]]:
season_join = query.season_join() if season_id else ''
sql = """
SELECT
p.id,
COUNT(d.id) AS num_decks,
SUM(dc.wins) AS wins,
SUM(dc.losses) AS losses,
SUM(dc.draws) AS draws,
SUM(CASE WHEN dc.wins >= 5 AND dc.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(dc.wins) / NULLIF(SUM(dc.wins + dc.losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
{where} AND {season_query}
GROUP BY
p.id
""".format(season_join=season_join,
where=where,
season_query=query.season_query(season_id, 'season.id'))
return {r['id']: r for r in db().select(sql)}
def only_played_by(person_id: int,
season_id: Optional[int] = None) -> List[Card]:
sql = """
SELECT
card AS name
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{season_join}
WHERE
deck_id
IN
(
SELECT
DISTINCT deck_id
FROM
deck_match
) -- Only include cards that actually got played competitively rather than just posted to Goldfish as "new cards this season" or similar.
AND
({season_query})
GROUP BY
card
HAVING
COUNT(DISTINCT d.person_id) = 1
AND
MAX(d.person_id) = {person_id} -- In MySQL 5.7+ this could/should be ANY_VALUE not MAX but this works with any version. The COUNT(DISTINCT p.id) ensures this only has one possible value but MySQL can't work that out.-- In MySQL 5.7+ this could/should be ANY_VALUE not MAX but this works with any version. The COUNT(DISTINCT p.id) ensures this only has one possible value but MySQL can't work that out.
""".format(season_join=query.season_join(),
season_query=query.season_query(season_id),
person_id=sqlescape(person_id))
cards = {c.name: c for c in oracle.load_cards()}
return [cards[r['name']] for r in db().execute(sql)]
def load_competitions(where: str = '1 = 1',
season_id: Optional[int] = None) -> List[Competition]:
sql = """
SELECT c.id, c.name, c.start_date, c.end_date, c.url,
COUNT(d.id) AS num_decks,
sp.name AS sponsor_name,
ct.name AS type
FROM competition AS c
LEFT JOIN deck AS d ON c.id = d.competition_id
LEFT JOIN competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN competition_type as ct ON ct.id = cs.competition_type_id
LEFT JOIN sponsor AS sp ON cs.sponsor_id = sp.id
{season_join}
WHERE ({where}) AND ({season_query})
GROUP BY c.id
ORDER BY c.start_date DESC, c.name
""".format(season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id))
competitions = [Competition(r) for r in db().execute(sql)]
for c in competitions:
c.start_date = dtutil.ts2dt(c.start_date)
c.end_date = dtutil.ts2dt(c.end_date)
set_decks(competitions)
return competitions
def matchup(hero: Dict[str, str],
enemy: Dict[str, str],
season_id: int = None) -> Dict[str, Union[str, int, List[int]]]:
where = 'TRUE'
prefix = None
args: List[Union[str, int]] = []
if season_id:
where += ' AND (season.id = %s)'
args.append(season_id)
for criteria in [hero, enemy]:
prefix = '' if prefix is None else 'o'
if criteria.get('person_id'):
where += f' AND ({prefix}d.person_id = %s)'
args.append(criteria['person_id'])
if criteria.get('archetype_id'):
where += f' AND ({prefix}d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = %s))'
args.append(criteria['archetype_id'])
if criteria.get('card'):
where += f' AND ({prefix}d.id IN (SELECT deck_id FROM deck_card WHERE card = %s))'
args.append(criteria['card'])
season_join = query.season_join()
sql = f"""
SELECT
GROUP_CONCAT(DISTINCT d.id) AS hero_deck_ids,
GROUP_CONCAT(DISTINCT od.id) AS enemy_deck_ids,
GROUP_CONCAT(DISTINCT m.id) AS match_ids,
IFNULL(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END), 0) AS wins,
IFNULL(SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END), 0) AS draws,
IFNULL(SUM(CASE WHEN odm.games > dm.games THEN 1 ELSE 0 END), 0) AS losses
FROM
deck AS d
LEFT JOIN
deck_match AS dm ON dm.deck_id = d.id
LEFT JOIN
`match` AS m ON dm.match_id = m.id
LEFT JOIN
deck_match AS odm ON m.id = odm.match_id AND odm.deck_id <> d.id
LEFT JOIN
deck AS od ON odm.deck_id = od.id
{season_join}
WHERE
{where}
"""
results = guarantee.exactly_one(db().select(sql, args))
results['hero_deck_ids'] = results['hero_deck_ids'].split(
',') if results['hero_deck_ids'] else []
results['hero_decks'] = deck.load_decks(
'd.id IN (' + ', '.join(results['hero_deck_ids']) +
')') if results['hero_deck_ids'] else []
results['enemy_deck_ids'] = results['enemy_deck_ids'].split(
',') if results['enemy_deck_ids'] else []
results['match_ids'] = results['match_ids'].split(
',') if results['match_ids'] else []
results['matches'] = match.load_matches(
where='m.id IN (' + ', '.join(results['match_ids']) +
')') if results['match_ids'] else []
return results
def preaggregate_archetype_person() -> None:
# This preaggregation fails if I use the obvious name _archetype_person_stats but works with any other name. It's confusing.
table = '_arch_person_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
archetype_id INT NOT NULL,
person_id INT NOT NULL,
season_id INT NOT NULL,
num_decks INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (person_id, season_id, archetype_id, deck_type),
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
d.person_id,
season.id AS season_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
LEFT JOIN
archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
LEFT JOIN
archetype_closure AS acd ON a.id = acd.ancestor
LEFT JOIN
deck AS d ON acd.descendant = d.archetype_id
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
a.id,
d.person_id,
season.id,
ct.name
HAVING
season.id IS NOT NULL
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
preaggregation.preaggregate(table, sql)
def preaggregate_matchups() -> None:
db().execute('DROP TABLE IF EXISTS _new_matchup_stats')
sql = """
CREATE TABLE IF NOT EXISTS _new_matchup_stats (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
wins_tournament INT NOT NULL,
losses_tournament INT NOT NULL,
draws_tournament INT NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
season.id AS season_id,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws,
SUM(CASE WHEN (dm.games > IFNULL(odm.games, 0)) AND (ct.name = 'Gatherling') THEN 1 ELSE 0 END) AS wins_tournament,
SUM(CASE WHEN (dm.games < IFNULL(odm.games, 0)) AND (ct.name = 'Gatherling') THEN 1 ELSE 0 END) AS losses_tournament,
SUM(CASE WHEN (dm.games = IFNULL(odm.games, 0)) AND (ct.name = 'Gatherling') THEN 1 ELSE 0 END) AS draws_tournament
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id)
{competition_join}
{season_join}
GROUP BY
a.id,
oa.id,
season.id
""".format(competition_join=query.competition_join(),
season_join=query.season_join())
db().execute(sql)
db().execute('DROP TABLE IF EXISTS _old_matchup_stats')
db().execute('CREATE TABLE IF NOT EXISTS _matchup_stats (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _matchup_stats TO _old_matchup_stats, _new_matchup_stats TO _matchup_stats'
)
db().execute('DROP TABLE IF EXISTS _old_matchup_stats')
def preaggregate_card_person() -> None:
db().execute('DROP TABLE IF EXISTS _new_card_person_stats')
db().execute("""
CREATE TABLE IF NOT EXISTS _new_card_person_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
person_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
wins_tournament INT NOT NULL,
losses_tournament INT NOT NULL,
draws_tournament INT NOT NULL,
PRIMARY KEY (season_id, person_id, name),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_person_id_name (person_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.id AS season_id,
d.person_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
SUM(CASE WHEN (d.id IS NOT NULL) AND (ct.name = 'Gatherling') THEN 1 ELSE 0 END) AS num_decks_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN wins ELSE 0 END), 0) AS wins_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN losses ELSE 0 END), 0) AS losses_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN draws ELSE 0 END), 0) AS draws_tournament
FROM
deck AS d
INNER JOIN
deck_card AS dc ON d.id = dc.deck_id
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
card,
d.person_id,
season.id
""".format(competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join()))
db().execute('DROP TABLE IF EXISTS _old_card_person_stats')
db().execute('CREATE TABLE IF NOT EXISTS _card_person_stats (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _card_person_stats TO _old_card_person_stats, _new_card_person_stats TO _card_person_stats'
)
db().execute('DROP TABLE IF EXISTS _old_card_person_stats')
def preaggregate_played_person() -> None:
table = '_played_card_person_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
person_id INT NOT NULL,
num_decks INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
PRIMARY KEY (season_id, person_id, name),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
name,
season_id AS season_id,
person_id,
COUNT(*) AS num_decks,
SUM(CASE WHEN my_games > your_games THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN my_games < your_games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN my_games = your_games THEN 1 ELSE 0 END) AS draws
FROM
(
SELECT
gcp.name,
season.id AS season_id,
p.id AS person_id,
MAX(CASE WHEN p.id = d.person_id THEN dm.games ELSE 0 END) AS my_games,
MAX(CASE WHEN p.id <> d.person_id THEN dm.games ELSE 0 END) AS your_games
FROM
{logsite_database}._game_card_person AS gcp
INNER JOIN
person AS p ON gcp.mtgo_username = p.mtgo_username
INNER JOIN
{logsite_database}.game AS g ON g.id = gcp.game_id
INNER JOIN
`match` AS m ON m.mtgo_id = g.match_id
INNER JOIN
deck_match AS dm ON dm.match_id = m.id
INNER JOIN
deck AS d ON dm.deck_id = d.id
{season_join}
GROUP BY
name, p.id, m.id
) AS base
GROUP BY
name, person_id, season_id
""".format(table=table,
logsite_database=configuration.get('logsite_database'),
season_join=query.season_join())
print(sql)
preaggregation.preaggregate(table, sql)
def with_sql(self) -> str:
return """knockouts AS
(
SELECT
dm1.deck_id AS winner_deck_id,
dm2.deck_id AS loser_deck_id,
p1.id AS winner_id,
p2.id AS loser_id,
season.id AS season_id,
`match`.date
FROM
deck AS d
LEFT JOIN
person AS p1
ON
d.person_id = p1.id
LEFT JOIN
deck_match AS dm1
ON
d.id = dm1.deck_id
LEFT JOIN
`match`
ON
dm1.match_id = `match`.id
LEFT JOIN
deck_match AS dm2
ON
`match`.id = dm2.match_id AND dm2.deck_id != dm1.deck_id
LEFT JOIN
deck AS d2
ON
dm2.deck_id = d2.id
LEFT JOIN
person AS p2
ON
d2.person_id = p2.id
{season_join}
WHERE
dm1.games > dm2.games AND elimination > 0
),
ancient_grudge_deck_ids AS
(
SELECT
k2.winner_deck_id AS id,
k1.winner_deck_id AS grudge_id,
CONCAT(k1.winner_deck_id, ",", k2.winner_deck_id) AS both_ids
FROM
knockouts AS k1
JOIN
knockouts AS k2
ON
k1.season_id = k2.season_id AND k1.winner_id = k2.loser_id AND k1.loser_id = k2.winner_id AND k2.date > k1.date
)""".format(season_join=query.season_join())
def preaggregate_matchups_person() -> None:
# Obvious name `_matchup_person_stats` fails with (1005, 'Can\'t create table `decksite`.`_new_matchup_person_stats` (errno: 121 "Duplicate key on write or update")'). Odd.
table = '_matchup_ps_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
archetype_id INT NOT NULL,
opponent_archetype_id INT NOT NULL,
person_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, opponent_archetype_id, person_id, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (opponent_archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
oa.id AS opponent_archetype_id,
d.person_id,
season.id AS season_id,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id)
{competition_join}
{season_join}
GROUP BY
a.id,
oa.id,
d.person_id,
season.id,
ct.name
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join())
preaggregation.preaggregate(table, sql)
def preaggregate_archetypes() -> None:
table = '_arch_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
archetype_id INT NOT NULL,
season_id INT NOT NULL,
num_decks INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
season.id AS season_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
archetype AS a
LEFT JOIN
archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
LEFT JOIN
archetype_closure AS acd ON a.id = acd.ancestor
LEFT JOIN
deck AS d ON acd.descendant = d.archetype_id
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
a.id,
aca.ancestor, -- aca.ancestor will be unique per a.id because of integrity constraints enforced elsewhere (each archetype has one ancestor) but we let the database know here.
season.id,
ct.name
HAVING
season.id IS NOT NULL
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
preaggregation.preaggregate(table, sql)
def load_competitions(
where: str = 'TRUE',
having: str = 'TRUE',
season_id: Optional[int] = None,
should_load_decks: Optional[bool] = False) -> List[Competition]:
sql = """
SELECT
c.id,
c.name,
c.start_date,
c.end_date,
c.url,
c.top_n,
COUNT(d.id) AS num_decks,
SUM(CASE WHEN d.reviewed THEN 1 ELSE 0 END) AS num_reviewed,
sp.name AS sponsor_name,
cs.name AS series_name,
ct.name AS type,
season.id AS season_id
FROM
competition AS c
LEFT JOIN
deck AS d ON c.id = d.competition_id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type as ct ON ct.id = cs.competition_type_id
LEFT JOIN
sponsor AS sp ON cs.sponsor_id = sp.id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
c.id
HAVING
{having}
ORDER BY
c.start_date DESC,
c.name
""".format(season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id, 'season.id'),
having=having)
competitions = [Competition(r) for r in db().select(sql)]
for c in competitions:
c.start_date = dtutil.ts2dt(c.start_date)
c.end_date = dtutil.ts2dt(c.end_date)
c.decks = []
if should_load_decks:
load_decks(competitions)
return competitions
def preaggregate_head_to_head() -> None:
db().execute('DROP TABLE IF EXISTS _new_head_to_head_stats')
sql = """
CREATE TABLE IF NOT EXISTS _new_head_to_head_stats (
person_id INT NOT NULL,
opponent_id INT NOT NULL,
season_id INT NOT NULL,
num_matches INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
PRIMARY KEY (season_id, person_id, opponent_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
p.id AS person_id,
opp.id AS opponent_id,
season.id AS season_id,
COUNT(p.id) AS num_matches,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
person AS p
INNER JOIN
deck AS d ON p.id = d.person_id
INNER JOIN
deck_match AS dm ON dm.deck_id = d.id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
INNER JOIN
deck AS od ON odm.deck_id = od.id
INNER JOIN
person AS opp ON od.person_id = opp.id
{season_join}
GROUP BY
p.id,
opp.id,
season.id
""".format(season_join=query.season_join())
db().execute(sql)
db().execute('DROP TABLE IF EXISTS _old_head_to_head_stats')
db().execute('CREATE TABLE IF NOT EXISTS _head_to_head_stats (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _head_to_head_stats TO _old_head_to_head_stats, _new_head_to_head_stats TO _head_to_head_stats'
)
db().execute('DROP TABLE IF EXISTS _old_head_to_head_stats')
def preaggregate_card_person() -> None:
table = '_card_person_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
person_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, person_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_person_id_name (person_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.id AS season_id,
d.person_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
card,
d.person_id,
season.id,
ct.name
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
preaggregation.preaggregate(table, sql)
def preaggregate_query() -> str:
# mypy doesn't understand our contract that a.create_columns etc. are only None if in_db is False
create_columns = ', '.join(
cast(str, a.create_columns) for a in Achievement.all_achievements
if a.in_db)
select_columns = ', '.join(
cast(str, a.select_columns) for a in Achievement.all_achievements
if a.in_db)
with_clauses = ', '.join(a.with_sql for a in Achievement.all_achievements
if a.with_sql is not None)
join_clauses = ' '.join(a.join_sql for a in Achievement.all_achievements
if a.join_sql is not None)
return """
CREATE TABLE IF NOT EXISTS _new_achievements (
person_id INT NOT NULL,
season_id INT NOT NULL,
{cc},
PRIMARY KEY (season_id, person_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
WITH
{with_clauses}
SELECT
p.id AS person_id,
season.id AS season_id,
{sc}
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
{season_join}
{competition_join}
{join_clauses}
GROUP BY
p.id,
season.id
HAVING
season.id IS NOT NULL
""".format(cc=create_columns,
sc=select_columns,
with_clauses=with_clauses,
join_clauses=join_clauses,
season_join=query.season_join(),
competition_join=query.competition_join())
def load_people(
where: str = 'TRUE',
order_by: str = 'num_decks DESC, p.name',
limit: str = '',
season_id: Optional[Union[str, int]] = None) -> Sequence[Person]:
person_query = query.person_query()
season_join = query.season_join() if season_id else ''
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
p.locale,
SUM(1) AS num_decks,
SUM(dc.wins) AS wins,
SUM(dc.losses) AS losses,
SUM(dc.draws) AS draws,
SUM(wins - losses) AS record,
SUM(CASE WHEN dc.wins >= 5 AND dc.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(dc.wins) / NULLIF(SUM(dc.wins + dc.losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
p.id
ORDER BY
{order_by}
{limit}
"""
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
return people
def set_head_to_head(people: List[Person], season_id: int = None) -> None:
people_by_id = {person.id: person for person in people}
sql = """
SELECT
p.id,
COUNT(p.id) AS num_matches,
LOWER(opp.mtgo_username) AS opp_mtgo_username,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws,
IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS win_percent
FROM
person AS p
INNER JOIN
deck AS d ON p.id = d.person_id
INNER JOIN
deck_match AS dm ON dm.deck_id = d.id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
INNER JOIN
deck AS od ON odm.deck_id = od.id
INNER JOIN
person AS opp ON od.person_id = opp.id
{season_join}
WHERE
p.id IN ({ids}) AND ({season_query})
GROUP BY
p.id, opp.id
ORDER BY
p.id,
num_matches DESC,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) - SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) DESC,
win_percent DESC,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) DESC,
opp_mtgo_username
""".format(ids=', '.join(str(k) for k in people_by_id.keys()),
season_join=query.season_join(),
season_query=query.season_query(season_id))
results = [Container(r) for r in db().select(sql)]
for result in results:
people_by_id[result.id].head_to_head = people_by_id[result.id].get(
'head_to_head', []) + [result]
for person in people:
if person.get('head_to_head') is None:
person.head_to_head = []
def load_people_count(where: str = 'TRUE',
season_id: Optional[Union[str, int]] = None) -> int:
season_join = query.season_join() if season_id else ''
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
COUNT(DISTINCT p.id)
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
"""
return db().value(sql) or 0
class VarietyPlayer(BooleanAchievement):
key = 'variety_player'
title = 'Variety Player'
season_text = 'Finished five-match league runs with three or more different archetypes this season'
description_safe = 'Finish five-match league runs with three or more different archetypes in a single season.'
sql = 'CASE WHEN COUNT(DISTINCT CASE WHEN falr.deck_id IS NOT NULL THEN d.archetype_id ELSE NULL END) >= 3 THEN True ELSE False END'
detail_sql = """
CASE WHEN
COUNT(DISTINCT CASE WHEN falr.deck_id IS NOT NULL THEN d.archetype_id ELSE NULL END) >= 3
THEN
GROUP_CONCAT(falr.deck_id)
ELSE
NULL
END
"""
with_sql = """
first_arch_league_runs AS
(
SELECT
MIN(d.id) as deck_id,
d.person_id AS person_id,
season.id AS season_id,
d.archetype_id AS archetype_id
FROM
deck AS d
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
{season_join}
{competition_join}
WHERE
ct.name = 'League' AND dc.wins + dc.losses >= 5
GROUP BY
person_id,
season_id,
archetype_id
)""".format(season_join=query.season_join(),
competition_join=query.competition_join())
join_sql = 'LEFT JOIN first_arch_league_runs AS falr ON d.id = falr.deck_id'
flags = ['hide_person', 'hide_source', 'hide_top8', 'show_archetype']
@staticmethod
def alltime_text(n: int) -> str:
what = ngettext('1 season', '%(num)d different seasons', n)
return f'Finished five-match league runs with three or more different archetypes in {what}'
def preaggregate_head_to_head() -> None:
table = '_head_to_head_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
person_id INT NOT NULL,
opponent_id INT NOT NULL,
season_id INT NOT NULL,
num_matches INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
PRIMARY KEY (season_id, person_id, opponent_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (opponent_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
p.id AS person_id,
opp.id AS opponent_id,
season.id AS season_id,
COUNT(p.id) AS num_matches,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
person AS p
INNER JOIN
deck AS d ON p.id = d.person_id
INNER JOIN
deck_match AS dm ON dm.deck_id = d.id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
INNER JOIN
deck AS od ON odm.deck_id = od.id
INNER JOIN
person AS opp ON od.person_id = opp.id
{season_join}
GROUP BY
p.id,
opp.id,
season.id
""".format(table=table, season_join=query.season_join())
preaggregation.preaggregate(table, sql)
def load_people(where: str = '1 = 1',
order_by: str = '`all_num_decks` DESC, name',
season_id: Optional[int] = None) -> Sequence[Person]:
sql = """
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
{all_select},
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL AND {season_query} THEN 1 ELSE 0 END) AS `all_num_competitions`
FROM
person AS p
LEFT JOIN
deck AS d ON p.id = d.person_id
{season_join}
{nwdl_join}
WHERE
({where})
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(),
all_select=deck.nwdl_select('all_',
query.season_query(season_id)),
nwdl_join=deck.nwdl_join(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id),
order_by=order_by)
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
if len(people) > 0:
set_achievements(people, season_id)
set_head_to_head(people, season_id)
return people
class Specialist(BooleanAchievement):
key = 'specialist'
title = 'Specialist'
season_text = 'Reached the elimination rounds of a tournament playing the same archetype three or more times this season'
description_safe = 'Reach the elimination rounds of a tournament playing the same archetype three or more times in a single season.'
sql = 'CASE WHEN EXISTS (SELECT * FROM arch_top_n_count1 AS atnc WHERE p.id = atnc.person_id AND season.id = atnc.season_id AND n >= 3) THEN TRUE ELSE FALSE END'
detail_sql = "GROUP_CONCAT(CASE WHEN d.finish <= c.top_n AND ct.name = 'Gatherling' AND d.archetype_id IN (SELECT archetype_id FROM arch_top_n_count2 AS atnc WHERE p.id = atnc.person_id AND season.id = atnc.season_id AND n >= 3) THEN d.id ELSE NULL END)"
with_sql = """
top_ns AS
(
SELECT
d.id as deck_id,
p.id AS person_id,
season.id AS season_id,
d.archetype_id AS archetype_id
FROM
person AS p
LEFT JOIN
deck AS d
ON
d.person_id = p.id
{season_join}
{competition_join}
WHERE
d.finish <= c.top_n AND ct.name = 'Gatherling'
),
arch_top_n_count1 AS
(
SELECT person_id, season_id, archetype_id, COUNT(DISTINCT deck_id) AS n FROM top_ns GROUP BY person_id, season_id, archetype_id
),
arch_top_n_count2 AS
(
SELECT person_id, season_id, archetype_id, COUNT(DISTINCT deck_id) AS n FROM top_ns GROUP BY person_id, season_id, archetype_id
)
""".format(season_join=query.season_join(),
competition_join=query.competition_join())
flags = ['hide_person', 'hide_source', 'show_archetype']
@staticmethod
def alltime_text(n: int) -> str:
what = ngettext('1 season', '%(num)d different seasons', n)
return f'Reached the elimination rounds of a tournament playing the same archetype three or more times in {what}'
def load_archetypes_deckless(
where: str = '1 = 1',
order_by: str = '`all_num_decks` DESC, `all_wins` DESC, name',
season_id: int = None) -> List[Archetype]:
sql = """
SELECT
a.id,
a.name,
aca.ancestor AS parent_id,
{all_select}
FROM
archetype AS a
LEFT JOIN
archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
LEFT JOIN
archetype_closure AS acd ON a.id = acd.ancestor
LEFT JOIN
deck AS d ON acd.descendant = d.archetype_id
{season_join}
{nwdl_join}
WHERE ({where}) AND ({season_query})
GROUP BY
a.id,
aca.ancestor -- aca.ancestor will be unique per a.id because of integrity constraints enforced elsewhere (each archetype has one ancestor) but we let the database know here.
ORDER BY
{order_by}
""".format(all_select=deck.nwdl_all_select(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join(),
where=where,
season_query=query.season_query(season_id),
order_by=order_by)
archetypes = [Archetype(a) for a in db().execute(sql)]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.parent = archetypes_by_id.get(a.parent_id, None)
return archetypes
def set_achievements(people: List[Person], season_id: int = None) -> None:
people_by_id = {person.id: person for person in people}
sql = """
SELECT
p.id,
COUNT(DISTINCT CASE WHEN ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_entries,
COUNT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_wins,
COUNT(DISTINCT CASE WHEN ct.name = 'League' THEN d.id ELSE NULL END) AS league_entries,
CASE WHEN COUNT(CASE WHEN d.retired = 1 THEN 1 ELSE NULL END) = 0 THEN True ELSE False END AS completionist,
SUM(CASE WHEN ct.name = 'League' AND dc.wins >= 5 AND dc.losses THEN 1 ELSE 0 END) AS perfect_runs,
SUM(
CASE WHEN d.id IN
(
SELECT
-- MAX here is just to fool MySQL to give us the id of the deck that crushed the perfect run from an aggregate function. There is only one value to MAX.
MAX(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN odm.deck_id ELSE NULL END) AS deck_id
FROM
deck AS d
INNER JOIN
deck_match AS dm
ON
dm.deck_id = d.id
INNER JOIN
deck_match AS odm
ON
dm.match_id = odm.match_id AND odm.deck_id <> d.id
WHERE
d.competition_id IN ({competition_ids_by_type_select})
GROUP BY d.id
HAVING
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >=4
AND
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 1
AND
SUM(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN 1 ELSE 0 END) = 1
)
THEN 1 ELSE 0 END
) AS perfect_run_crushes
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
{season_join}
{competition_join}
WHERE
p.id IN ({ids}) AND ({season_query})
GROUP BY
p.id
""".format(
competition_join=query.competition_join(),
competition_ids_by_type_select=query.competition_ids_by_type_select(
'League'),
ids=', '.join(str(k) for k in people_by_id.keys()),
season_join=query.season_join(),
season_query=query.season_query(season_id))
results = [Container(r) for r in db().select(sql)]
for result in results:
people_by_id[result['id']].num_achievements = len(
[k for k, v in result.items() if k != 'id' and v > 0])
people_by_id[result['id']].achievements = result
people_by_id[result['id']].achievements.pop('id')
def load_decks(where: str = '1 = 1',
having: str = '1 = 1',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[int] = None
) -> List[Deck]:
if not redis.enabled():
return load_decks_heavy(where, having, order_by, limit, season_id)
if order_by is None:
order_by = 'active_date DESC, d.finish IS NULL, d.finish'
sql = """
SELECT
d.id,
d.finish,
d.decklist_hash,
cache.active_date,
cache.wins,
cache.losses,
cache.draws,
ct.name AS competition_type_name
FROM
deck AS d
"""
if 'p.' in where or 'p.' in order_by:
sql += """
LEFT JOIN
person AS p ON d.person_id = p.id
"""
if 's.' in where or 's.' in order_by:
sql += """
LEFT JOIN
source AS s ON d.source_id = s.id
"""
if 'a.' in where or 'a.' in order_by:
sql += """
LEFT JOIN
archetype AS a ON d.archetype_id = a.id
"""
sql += """
{competition_join}
LEFT JOIN
deck_cache AS cache ON d.id = cache.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
d.id,
d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
HAVING
{having}
ORDER BY
{order_by}
{limit}
"""
sql = sql.format(person_query=query.person_query(), competition_join=query.competition_join(), season_query=query.season_query(season_id, 'season.id'), season_join=query.season_join(), where=where, having=having, order_by=order_by, limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().select(sql)
decks_by_id = {}
heavy = []
for row in rows:
d = redis.get_container('decksite:deck:{id}'.format(id=row['id']))
if d is None or d.name is None:
heavy.append(row['id'])
else:
decks_by_id[row['id']] = deserialize_deck(d)
if heavy:
where = 'd.id IN ({deck_ids})'.format(deck_ids=', '.join(map(sqlescape, map(str, heavy))))
loaded_decks = load_decks_heavy(where)
for d in loaded_decks:
decks_by_id[d.id] = d
decks = []
for row in rows:
decks.append(decks_by_id[row['id']])
return decks
def load_decks_heavy(where: str = '1 = 1',
having: str = '1 = 1',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[int] = None
) -> List[Deck]:
if order_by is None:
order_by = 'active_date DESC, d.finish IS NULL, d.finish'
sql = """
SELECT
d.id,
d.name AS original_name,
d.created_date,
d.updated_date,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws,
d.finish,
d.archetype_id,
d.url AS source_url,
d.competition_id,
c.name AS competition_name,
c.end_date AS competition_end_date,
c.top_n AS competition_top_n,
ct.name AS competition_type_name,
d.identifier,
{person_query} AS person,
p.id AS person_id,
p.banned,
p.discord_id,
d.decklist_hash,
d.retired,
d.reviewed,
s.name AS source_name,
IFNULL(a.name, '') AS archetype_name,
cache.normalized_name AS name,
cache.colors,
cache.colored_symbols,
cache.legal_formats,
ROUND(cache.omw * 100, 2) AS omw,
season.id AS season_id,
IFNULL(MAX(m.date), d.created_date) AS active_date
FROM
deck AS d
LEFT JOIN
person AS p ON d.person_id = p.id
LEFT JOIN
source AS s ON d.source_id = s.id
LEFT JOIN
archetype AS a ON d.archetype_id = a.id
{competition_join}
LEFT JOIN
deck_cache AS cache ON d.id = cache.deck_id
LEFT JOIN
deck_match AS dm ON d.id = dm.deck_id
LEFT JOIN
`match` AS m ON dm.match_id = m.id
LEFT JOIN
deck_match AS odm ON odm.deck_id <> d.id AND dm.match_id = odm.match_id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
d.id,
d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
HAVING
{having}
ORDER BY
{order_by}
{limit}
""".format(person_query=query.person_query(), competition_join=query.competition_join(), season_join=query.season_join(), where=where, season_query=query.season_query(season_id, 'season.id'), having=having, order_by=order_by, limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().select(sql)
decks = []
for row in rows:
d = Deck(row)
d.maindeck = []
d.sideboard = []
d.competition_top_n = Top(d.competition_top_n or 0)
d.colored_symbols = json.loads(d.colored_symbols or '[]')
d.colors = json.loads(d.colors or '[]')
d.legal_formats = set(json.loads(d.legal_formats or '[]'))
d.active_date = dtutil.ts2dt(d.active_date)
d.created_date = dtutil.ts2dt(d.created_date)
d.updated_date = dtutil.ts2dt(d.updated_date)
if d.competition_end_date:
d.competition_end_date = dtutil.ts2dt(d.competition_end_date)
d.can_draw = 'Divine Intervention' in [card.name for card in d.all_cards()]
d.wins = int(d.wins)
d.losses = int(d.losses)
d.draws = int(d.draws)
decks.append(d)
load_cards(decks)
load_competitive_stats(decks)
for d in decks:
expiry = 60 if d.is_in_current_run() else 3600
redis.store('decksite:deck:{id}'.format(id=d.id), d, ex=expiry)
return decks
