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
