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