def preaggregate_trailblazer() -> None:
table = '_trailblazer_cards'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
card VARCHAR(100) NOT NULL,
deck_id INT NOT NULL,
PRIMARY KEY (card, deck_id),
FOREIGN KEY (deck_id) REFERENCES deck (id) ON UPDATE CASCADE ON DELETE CASCADE
)
SELECT
d.id AS deck_id,
card,
MIN(d.created_date) AS `date`
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
INNER JOIN
deck_match AS dm ON dm.deck_id = d.id
{competition_join}
WHERE
d.id IN (SELECT deck_id FROM deck_match GROUP BY deck_id HAVING COUNT(*) >= 3)
GROUP BY
card,
deck_id
""".format(table=table, competition_join=query.competition_join())
preaggregation.preaggregate(table, sql)
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 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_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_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_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 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 set_achievements(people):
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,
SUM(
CASE WHEN d.id IN
(
SELECT
d.id
FROM
deck AS d
{competition_join}
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
WHERE
ct.name = 'League'
GROUP BY
d.id
HAVING
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >= 5
AND
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 0
)
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
{competition_join}
WHERE
p.id IN ({ids})
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()))
results = [Container(r) for r in db().execute(sql)]
for result in results:
people_by_id[result['id']].update(result)
people_by_id[result['id']].achievements = len([k for k, v in result.items() if k != 'id' and v > 0])
def load_decks_query(
columns: str,
where: str = 'TRUE',
group_by: Optional[str] = None,
having: str = 'TRUE',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[Union[str, int]] = None,
) -> str:
if order_by is None:
order_by = 'active_date DESC, d.finish IS NULL, d.finish'
if group_by is None:
group_by = ''
else:
group_by = f'GROUP BY {group_by}'
sql = """
SELECT
{columns}
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}
HAVING
{having}
ORDER BY
{order_by}
{limit}
"""
sql = sql.format(columns=columns,
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,
group_by=group_by,
having=having,
order_by=order_by,
limit=limit)
return sql
def load_decks(where: str = '1 = 1',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[int] = None) -> List[Deck]:
if redis.REDIS is None:
return load_decks_heavy(where, 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,
IFNULL(MAX(m.date), d.created_date) AS active_date
FROM
deck AS d
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
"""
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}
"""
if 'cache.' in where or 'cache.' in order_by:
sql += """
LEFT JOIN
deck_cache AS cache ON d.id = cache.deck_id
"""
sql += """
{season_join}
WHERE ({where}) AND ({season_query})
GROUP BY
d.id,
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.
ORDER BY
{order_by}
{limit}
"""
sql = sql.format(person_query=query.person_query(),
competition_join=query.competition_join(),
where=where,
order_by=order_by,
limit=limit,
season_query=query.season_query(season_id),
season_join=query.season_join())
db().execute('SET group_concat_max_len=100000')
rows = db().execute(sql)
decks = []
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'])
# decks.append(guarantee.exactly_one(load_decks_heavy('d.id = {id}'.format(id=row['id']))))
else:
decks.append(deserialize_deck(d))
if heavy:
# This currently messes up the order.
where = 'd.id IN ({deck_ids})'.format(
deck_ids=', '.join(map(sqlescape, map(str, heavy))))
decks.extend(load_decks_heavy(where))
return decks
def preaggregate_archetypes() -> None:
db().execute('DROP TABLE IF EXISTS _new_archetype_stats')
sql = """
CREATE TABLE IF NOT EXISTS _new_archetype_stats (
archetype_id INT NOT NULL,
season_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, 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
) 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,
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
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
HAVING
season.id IS NOT NULL
""".format(competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
db().execute(sql)
db().execute('DROP TABLE IF EXISTS _old_archetype_stats')
db().execute('CREATE TABLE IF NOT EXISTS _archetype_stats (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _archetype_stats TO _old_archetype_stats, _new_archetype_stats TO _archetype_stats'
)
db().execute('DROP TABLE IF EXISTS _old_archetype_stats')
def load_matches(where: str = 'TRUE',
season_id: Optional[int] = None,
should_load_decks: bool = False) -> List[Container]:
person_query = query.person_query(table='o')
competition_join = query.competition_join()
season_join = query.season_join()
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
m.`date`,
m.id,
m.`round`,
m.elimination,
m.mtgo_id,
d.id AS deck_id,
dc.normalized_name AS deck_name,
od.id AS opponent_deck_id,
odc.normalized_name AS opponent_deck_name,
dm.games AS game_wins,
IFNULL(odm.games, 0) AS game_losses,
c.id AS competition_id,
ct.name AS competition_type_name,
c.end_date AS competition_end_date,
{person_query} AS opponent,
odc.wins,
odc.draws,
odc.losses,
od.retired
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
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}
ORDER BY
m.`date`,
m.`round`
"""
matches = [Container(r) for r in db().select(sql)]
if should_load_decks:
opponents = [
m.opponent_deck_id for m in matches
if m.opponent_deck_id is not None
]
if len(opponents) > 0:
decks = deck.load_decks('d.id IN ({ids})'.format(ids=', '.join(
[sqlescape(str(deck_id)) for deck_id in opponents])))
else:
decks = []
decks_by_id = {d.id: d for d in decks}
for m in matches:
m.date = dtutil.ts2dt(m.date)
m.competition_end_date = dtutil.ts2dt(m.competition_end_date)
m.competition_url = url_for('competition',
competition_id=m.competition_id)
if Deck(m).is_in_current_run():
m.opponent_deck_name = '(Active League Run)'
if should_load_decks and m.opponent_deck_id is not None and decks_by_id.get(
m.opponent_deck_id):
m.opponent_deck = decks_by_id[m.opponent_deck_id]
elif should_load_decks:
m.opponent_deck = None
return matches
def load_matches(where: str = 'TRUE',
order_by: str = 'm.`date`, m.`round`',
limit: str = '',
season_id: Union[int, str, None] = None,
should_load_decks: bool = False,
show_active_deck_names: bool = False) -> List[Container]:
person_query = query.person_query()
opponent_person_query = query.person_query(table='o')
competition_join = query.competition_join()
season_join = query.season_join()
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
m.`date`,
m.id,
m.`round`,
m.elimination,
m.mtgo_id,
d.id AS deck_id,
{person_query} AS person,
dc.normalized_name AS deck_name,
od.id AS opponent_deck_id,
odc.normalized_name AS opponent_deck_name,
dm.games AS game_wins,
IFNULL(odm.games, 0) AS game_losses,
c.id AS competition_id,
ct.name AS competition_type_name,
c.end_date AS competition_end_date,
{opponent_person_query} AS opponent,
odc.wins,
odc.draws,
odc.losses,
od.retired
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}
GROUP BY
m.id -- We don't want an row for each deck in a match (when the WHERE doesn't include a person)
ORDER BY
{order_by}
{limit}
"""
matches = [Container(r) for r in db().select(sql)]
decks = []
if should_load_decks:
opponents = [
m.opponent_deck_id for m in matches
if m.opponent_deck_id is not None
]
if len(opponents) > 0:
decks = deck.load_decks('d.id IN ({ids})'.format(ids=', '.join(
[sqlescape(str(deck_id)) for deck_id in opponents])))
decks_by_id = {d.id: d for d in decks}
setup_matches(should_load_decks, show_active_deck_names, decks_by_id,
matches)
return matches
def preaggregate_season_stats() -> None:
sql = """
SELECT
season.id AS season_id,
season.start_date,
season.end_date,
COUNT(*) AS num_decks,
SUM(CASE WHEN ct.name = 'League' THEN 1 ELSE 0 END) AS num_league_decks,
COUNT(DISTINCT d.person_id) AS num_people,
COUNT(DISTINCT c.id) AS num_competitions,
COUNT(DISTINCT d.archetype_id) AS num_archetypes
FROM
deck AS d
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
{competition_join}
{season_join}
GROUP BY
season.id;
""".format(competition_join=query.competition_join(),
season_join=query.season_join())
rs = db().select(sql)
stats = {r['season_id']: r for r in rs}
sql = """
SELECT
season.id AS season_id,
COUNT(DISTINCT dm.match_id) AS num_matches
FROM
deck_match AS dm
INNER JOIN
deck AS d ON dm.deck_id = d.id
{season_join}
GROUP BY
season.id
""".format(season_join=query.season_join())
rs = db().select(sql)
for r in rs:
stats.get(r['season_id'], {}).update(r)
sql = """
SELECT
season.id AS season_id,
COUNT(DISTINCT dc.card) AS num_cards
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{season_join}
GROUP BY
season.id
""".format(season_join=query.season_join())
rs = db().select(sql)
for r in rs:
stats.get(r['season_id'], {}).update(r)
table = '_season_stats'
columns = [
'season_id', 'start_date', 'end_date', 'num_decks', 'num_league_decks',
'num_people', 'num_competitions', 'num_archetypes', 'num_matches',
'num_cards'
]
values = []
for season in stats.values():
values.append('(' +
', '.join(str(sqlescape(season[k]))
for k in columns) + ')')
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
season_id INT NOT NULL,
start_date INT NOT NULL,
end_date INT,
num_decks INT NOT NULL,
num_league_decks INT NOT NULL,
num_people INT NOT NULL,
num_competitions INT NOT NULL,
num_archetypes INT NOT NULL,
num_matches INT NOT NULL,
num_cards INT NOT NULL,
PRIMARY KEY (season_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO _new{table} VALUES {values};
""".format(table=table, values=', '.join(values))
preaggregation.preaggregate(table, sql)
def load_decks(where='1 = 1', order_by=None, limit='', season_id=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,
s.name AS source_name,
IFNULL(a.name, '') AS archetype_name,
cache.normalized_name AS name,
cache.colors,
cache.colored_symbols,
cache.legal_formats,
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,
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.
ORDER BY
{order_by}
{limit}
""".format(person_query=query.person_query(), competition_join=query.competition_join(), where=where, order_by=order_by, limit=limit, season_query=query.season_query(season_id), season_join=query.season_join())
db().execute('SET group_concat_max_len=100000')
rows = db().execute(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()]
decks.append(d)
load_cards(decks)
load_competitive_stats(decks)
return decks
def load_decks(where='1 = 1', order_by=None, limit=''):
if order_by is None:
order_by = 'd.created_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,
ct.name AS competition_type_name,
d.identifier,
{person_query} AS person,
p.id AS person_id,
p.banned,
p.discord_id,
d.created_date AS `date`,
d.decklist_hash,
d.retired,
s.name AS source_name,
IFNULL(a.name, '') AS archetype_name,
cache.normalized_name AS name,
cache.colors,
cache.colored_symbols,
cache.legal_formats
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
deck_match AS odm ON odm.deck_id <> d.id AND dm.match_id = odm.match_id
WHERE
{where}
GROUP BY
d.id
ORDER BY
{order_by}
{limit}
""".format(person_query=query.person_query(),
competition_join=query.competition_join(),
where=where,
order_by=order_by,
limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().execute(sql)
decks = []
for row in rows:
d = Deck(row)
d.maindeck = []
d.sideboard = []
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.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.date = dtutil.ts2dt(d.date)
d.can_draw = 'Divine Intervention' in [
card.name for card in d.all_cards()
]
decks.append(d)
load_cards(decks)
load_opponent_stats(decks)
return decks
def load_matches(where: str = 'TRUE',
order_by: str = 'm.`date`, m.`round`',
limit: str = '',
season_id: Union[int, str, None] = None,
should_load_decks: bool = False,
show_active_deck_names: bool = False) -> List[Container]:
person_query = query.person_query()
opponent_person_query = query.person_query(table='o')
competition_join = query.competition_join()
season_join = query.season_join()
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
m.`date`,
m.id,
m.`round`,
m.elimination,
m.mtgo_id,
d.id AS deck_id,
{person_query} AS person,
dc.normalized_name AS deck_name,
od.id AS opponent_deck_id,
odc.normalized_name AS opponent_deck_name,
dm.games AS game_wins,
IFNULL(odm.games, 0) AS game_losses,
c.id AS competition_id,
ct.name AS competition_type_name,
c.end_date AS competition_end_date,
{opponent_person_query} AS opponent,
odc.wins,
odc.draws,
odc.losses,
od.retired
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}
GROUP BY
m.id -- We don't want an row for each deck in a match (when the WHERE doesn't include a person)
ORDER BY
{order_by}
{limit}
"""
matches = [Container(r) for r in db().select(sql)]
if should_load_decks:
opponents = [
m.opponent_deck_id for m in matches
if m.opponent_deck_id is not None
]
if len(opponents) > 0:
decks = deck.load_decks('d.id IN ({ids})'.format(ids=', '.join(
[sqlescape(str(deck_id)) for deck_id in opponents])))
else:
decks = []
decks_by_id = {d.id: d for d in decks}
for m in matches:
m.date = dtutil.ts2dt(m.date)
m.competition_end_date = dtutil.ts2dt(m.competition_end_date)
if g: # https://github.com/PennyDreadfulMTG/Penny-Dreadful-Tools/issues/8435
m.competition_url = url_for('competition',
competition_id=m.competition_id)
if Deck(m).is_in_current_run() and not show_active_deck_names:
m.opponent_deck_name = '(Active League Run)'
if should_load_decks and m.opponent_deck_id is not None and decks_by_id.get(
m.opponent_deck_id):
m.opponent_deck = decks_by_id[m.opponent_deck_id]
elif should_load_decks:
m.opponent_deck = None
return matches