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 preaggregate_playability() -> None:
sql = """
SELECT
card AS name,
COUNT(*) AS played
FROM
deck_card
GROUP BY
card
"""
rs = db().select(sql)
high = max([r['played'] for r in rs] + [0])
table = '_playability'
sql = f"""
CREATE TABLE IF NOT EXISTS _new{table} (
name VARCHAR(190) NOT NULL,\
playability DECIMAL(3,2),
PRIMARY KEY (name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
ROUND(COUNT(*) / {high}, 2) AS playability
FROM
deck_card
GROUP BY
card
""".format(table=table, high=high)
preaggregation.preaggregate(table, 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_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 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 cache_all_rules() -> None:
table = '_applied_rules'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
deck_id INT NOT NULL,
rule_id INT NOT NULL,
archetype_id INT NOT NULL,
archetype_name TEXT,
PRIMARY KEY (deck_id, rule_id),
FOREIGN KEY (deck_id) REFERENCES deck (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (rule_id) REFERENCES rule (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
{apply_rules_query}
""".format(table=table,
apply_rules_query=apply_rules_query(
deck_query=classified_decks_query()))
preaggregation.preaggregate(table, sql)
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 preaggregate_unique() -> None:
table = '_unique_cards'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
card VARCHAR(100) NOT NULL,
person_id INT NOT NULL,
PRIMARY KEY (card, person_id),
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE
)
SELECT
card, person_id
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
WHERE
d.id IN (SELECT deck_id FROM deck_match GROUP BY deck_id HAVING COUNT(*) >= 3)
GROUP BY
card,
person_id
HAVING
COUNT(DISTINCT person_id) = 1
""".format(table=table)
preaggregation.preaggregate(table, sql)
def preaggregate_achievements() -> None:
preaggregation.preaggregate('_achievements', preaggregate_query())
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)
