def load_person_by_mtggoldfish_name(username): return guarantee.at_most_one(load_people('p.mtggoldfish_username = {username}'.format(username=sqlescape(username))))
def load_person_by_discord_id(discord_id): return guarantee.at_most_one(load_people('p.discord_id = {discord_id}'.format(discord_id=sqlescape(discord_id))))
def load_person_by_tappedout_name(username): return guarantee.at_most_one(load_people('p.tappedout_username = {username}'.format(username=sqlescape(username))))
def card_where(name: str) -> str: return 'd.id IN (SELECT deck_id FROM deck_card WHERE card = {name})'.format(name=sqlescape(name))
def associate(d, discord_id): person = guarantee.exactly_one(load_people('d.id = {deck_id}'.format(deck_id=sqlescape(d.id)))) sql = 'UPDATE person SET discord_id = %s WHERE id = %s' return db().execute(sql, [discord_id, person.id])
def active_decks_by(mtgo_username): return active_decks('p.mtgo_username = {mtgo_username}'.format(mtgo_username=sqlescape(mtgo_username, force_string=True)))
def load_matches(where: str = '1 = 1', 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, 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 active_decks_by(mtgo_username: str) -> List[deck.Deck]: return active_decks('p.mtgo_username = {mtgo_username}'.format( mtgo_username=sqlescape(mtgo_username, force_string=True)))
def load_deck(deck_id: int) -> Deck: return guarantee.exactly_one( load_decks('d.id = {deck_id}'.format(deck_id=sqlescape(deck_id))))
def test_sqlescape(): assert sqlescape('Hello') == "'Hello'" assert sqlescape("O'Leary") == "'O''Leary'" assert sqlescape("100%") == "'100%%'"
def load_competition(competition_id): return guarantee.exactly_one( load_competitions('c.id = {competition_id}'.format( competition_id=sqlescape(competition_id))))
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
def set_where(name): return '(c.id IN (SELECT card_id FROM printing WHERE set_id IN (SELECT id FROM `set` WHERE name LIKE {name_fuzzy} OR code = {name} COLLATE NOCASE)))'.format( name_fuzzy=sqllikeescape(name), name=sqlescape(name))
def preaggregate_season_stats() -> None: sql = """ SELECT season.id AS season_id, season.start_date, season.end_date, COUNT(DISTINCT d.id) AS num_decks, COUNT(DISTINCT (CASE WHEN ct.name = 'League' THEN d.id ELSE NULL 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_person_by_mtgo_username(username: str, season_id: Optional[int] = None) -> Person: return load_person('p.mtgo_username = {username}'.format( username=sqlescape(username, force_string=True)), season_id=season_id)
def load_competition(competition_id: int) -> Competition: return guarantee.exactly_one( load_competitions('c.id = {competition_id}'.format( competition_id=sqlescape(competition_id)), should_load_decks=True))
def maybe_load_person_by_tappedout_name(username: str) -> Optional[Person]: return guarantee.at_most_one( load_people('p.tappedout_username = {username}'.format( username=sqlescape(username))))
def load_archetype(archetype, season_id=None): try: archetype_id = int(archetype) except ValueError: name = titlecase.titlecase(archetype) name_without_dashes = name.replace('-', ' ') archetype_id = db().value('SELECT id FROM archetype WHERE name IN (%s, %s)', [name, name_without_dashes]) if not archetype_id: raise DoesNotExistException('Did not find archetype with name of `{name}`'.format(name=name)) archetypes = load_archetypes(where='d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = {archetype_id})'.format(archetype_id=sqlescape(archetype_id)), merge=True, season_id=season_id) if len(archetypes) > 1: raise TooManyItemsException('Found {n} archetypes when expecting 1 at most'.format(n=len(archetypes))) archetype = archetypes[0] if len(archetypes) == 1 else Archetype() # Because load_archetypes loads the root archetype and all below merged the id and name might not be those of the root archetype. Overwrite. archetype.id = int(archetype_id) archetype.name = db().value('SELECT name FROM archetype WHERE id = %s', [archetype_id]) if len(archetypes) == 0: archetype.decks = [] return archetype