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)