def run() -> None:
run_elo = False
# pylint: disable=consider-using-enumerate
for i in range(0, len(USERNAME_COLUMNS)):
# pylint: disable=consider-using-enumerate
for j in range(i + 1, len(USERNAME_COLUMNS)):
sql = """
SELECT p1.id AS p1_id, p2.id AS p2_id, '{col1}' AS col1, '{col2}' AS col2
FROM person AS p1
LEFT JOIN person AS p2
ON p1.{col1} = p2.{col2} AND p1.id <> p2.id
WHERE p1.id IS NOT NULL AND p2.id IS NOT NULL
""".format(col1=USERNAME_COLUMNS[i], col2=USERNAME_COLUMNS[j])
pairs = [Container(row) for row in db().select(sql)]
if len(pairs) > 0:
run_elo = True
for pair in pairs:
person.squash(pair.p1_id, pair.p2_id, pair.col1, pair.col2)
if run_elo:
logger.warning('Running maintenance task to correct all Elo ratings.')
elo.run()
def apply_rules_to_decks(decks: List[Deck]) -> None:
if len(decks) == 0:
return
decks_by_id = {}
for d in decks:
decks_by_id[d.id] = d
id_list = ', '.join(str(d.id) for d in decks)
sql = """
SELECT
deck_id,
archetype_name
FROM
({apply_rules_query}) AS applied_rules
GROUP BY
deck_id
HAVING
COUNT(DISTINCT archetype_id) = 1
""".format(
apply_rules_query=apply_rules_query(f'deck_id IN ({id_list})'))
for r in (Container(row) for row in db().select(sql)):
decks_by_id[r.deck_id].rule_archetype_name = r.archetype_name
def prime_cache(d: Deck) -> None:
set_colors(d)
colors_s = json.dumps(d.colors)
colored_symbols_s = json.dumps(d.colored_symbols)
set_legality(d)
legal_formats_s = json.dumps(list(d.legal_formats))
normalized_name = deck_name.normalize(d)
db().begin()
db().execute('DELETE FROM deck_cache WHERE deck_id = %s', [d.id])
sql = """
INSERT INTO
deck_cache (deck_id, normalized_name, colors, colored_symbols, legal_formats, wins, draws, losses, active_date)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
db().execute(sql, [
d.id, normalized_name, colors_s, colored_symbols_s, legal_formats_s, 0,
0, 0,
dtutil.dt2ts(d.created_date)
])
db().commit()
# If it was worth priming the in-db cache it's worth busting the in-memory cache to pick up the changes.
redis.clear(f'decksite:deck:{d.id}')
def load_people(where: str = '1 = 1',
order_by: str = '`all_num_decks` DESC, name',
season_id: Optional[int] = None) -> Sequence[Person]:
sql = """
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
{all_select},
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL AND {season_query} THEN 1 ELSE 0 END) AS `all_num_competitions`
FROM
person AS p
LEFT JOIN
deck AS d ON p.id = d.person_id
{season_join}
{nwdl_join}
WHERE
({where})
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(),
all_select=deck.nwdl_select('all_',
query.season_query(season_id)),
nwdl_join=deck.nwdl_join(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id),
order_by=order_by)
people = [Person(r) for r in db().execute(sql)]
if len(people) > 0:
set_decks(people, season_id)
set_achievements(people, season_id)
set_head_to_head(people, season_id)
return people
def load_opponent_stats(decks):
if len(decks) == 0:
return
decks_by_id = {d.id: d for d in decks}
sql = """
SELECT
d.id,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) AS opp_wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS opp_losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS opp_draws,
ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) * 100 AS omw,
IFNULL(MIN(CASE WHEN m.elimination > 0 THEN m.elimination END), 0) AS stage_reached,
GROUP_CONCAT(m.elimination) AS elim
FROM
deck AS d
INNER JOIN
deck_match AS my_dm ON my_dm.deck_id = d.id
LEFT JOIN
deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = my_odm.deck_id
INNER JOIN
deck_match AS dm ON dm.deck_id = od.id
LEFT JOIN
deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
INNER JOIN
`match` AS m ON m.id = dm.match_id
WHERE
d.id IN ({deck_ids})
GROUP BY
d.id
""".format(
deck_ids=', '.join(map(sqlescape, map(str, decks_by_id.keys()))))
for row in db().execute(sql):
decks_by_id[row['id']].opp_wins = row['opp_wins']
decks_by_id[row['id']].opp_losses = row['opp_losses']
decks_by_id[row['id']].omw = row['omw']
decks_by_id[row['id']].stage_reached = row['stage_reached']
decks_by_id[row['id']].elim = row[
'elim'] # This property is never used? and is always a bunch of zeroes?
def load_person_statless(where: str = 'TRUE',
season_id: Optional[int] = None) -> Person:
person_query = query.person_query()
sql = f"""
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
p.locale
FROM
person AS p
WHERE
{where}
"""
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
return guarantee.exactly_one(people)
def load_cards(person_id: Optional[int] = None, season_id: Optional[int] = None, tournament_only: bool = False) -> List[Card]:
if person_id:
table = '_card_person_stats'
where = 'person_id = {person_id}'.format(person_id=sqlescape(person_id))
group_by = 'person_id, name'
else:
table = '_card_stats'
where = 'TRUE'
group_by = 'name'
if tournament_only:
where = f"({where}) AND deck_type = 'tournament'"
sql = """
SELECT
name,
SUM(num_decks) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins - losses) AS record,
SUM(perfect_runs) AS perfect_runs,
SUM(tournament_wins) AS tournament_wins,
SUM(tournament_top8s) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
{table} AS cs
WHERE
({where}) AND ({season_query})
GROUP BY
{group_by}
ORDER BY
num_decks DESC,
record,
name
""".format(table=table, season_query=query.season_query(season_id), where=where, group_by=group_by)
cs = [Container(r) for r in db().select(sql)]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def load_matchups(archetype_id):
sql = """
SELECT
oa.id,
oa.name,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS all_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS all_losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS all_draws,
IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS all_win_percent,
SUM(CASE WHEN d.created_date > %s AND dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS season_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN d.created_date > %s AND dm.games < odm.games THEN 1 ELSE 0 END) AS season_losses,
SUM(CASE WHEN d.created_date > %s AND dm.games = odm.games THEN 1 ELSE 0 END) AS season_draws,
IFNULL(ROUND((SUM(CASE WHEN d.created_date > %s AND dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN d.created_date > %s AND dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS season_win_percent
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)
WHERE
a.id = %s
GROUP BY
oa.id
ORDER BY
`season_wins` DESC, `all_wins` DESC
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
nwdl_join=deck.nwdl_join())
return [
Container(m) for m in
db().execute(sql, [int(rotation.last_rotation().timestamp())] * 5 +
[archetype_id])
]
def do_validation(self):
if len(self.mtgo_username) == 0:
self.errors['mtgo_username'] = "******"
elif len(self.mtgo_username) > card.MAX_LEN_VARCHAR:
self.errors['mtgo_username'] = "******".format(n=card.MAX_LEN_VARCHAR)
elif active_decks_by(self.mtgo_username):
self.errors['mtgo_username'] = "******"
if len(self.name) == 0:
self.errors['name'] = 'Deck Name is required'
elif len(self.name) > card.MAX_LEN_TEXT:
self.errors['name'] = 'Deck Name is too long (max {n})'.format(n=card.MAX_LEN_TEXT)
else:
self.source = 'League'
self.competition_id = db().value(active_competition_id_query())
self.identifier = identifier(self)
self.url = url_for('competitions', competition_id=self.competition_id)
self.decklist = self.decklist.strip()
if len(self.decklist) == 0:
self.errors['decklist'] = 'Decklist is required'
else:
self.cards = None
if self.decklist.startswith('<?xml'):
try:
self.cards = decklist.parse_xml(self.decklist)
except InvalidDataException as e:
self.errors['decklist'] = 'Unable to read .dek decklist. Try exporting from Magic Online as Text and pasting the result.'.format(specific=str(e))
else:
try:
self.cards = decklist.parse(self.decklist)
except InvalidDataException as e:
self.errors['decklist'] = '{specific}. Try exporting from Magic Online as Text and pasting the result.'.format(specific=str(e))
if self.cards is not None:
try:
vivified = decklist.vivify(self.cards)
errors = {}
if 'Penny Dreadful' not in legality.legal_formats(vivified, None, errors):
self.errors['decklist'] = 'Deck is not legal in Penny Dreadful - {error}'.format(error=errors.get('Penny Dreadful'))
except InvalidDataException as e:
self.errors['decklist'] = str(e)
def load_archetypes_deckless(
where='1 = 1',
order_by='`season.num_decks` DESC, `all.num_decks` DESC, `season.wins` DESC, `all.wins` DESC'
):
sql = """
SELECT
a.id,
a.name,
aca.ancestor AS parent_id,
COUNT(DISTINCT d.id) AS `all.num_decks`,
SUM(d.wins) AS `all.wins`,
SUM(d.losses) AS `all.losses`,
SUM(d.draws) AS `all.draws`,
IFNULL(ROUND((SUM(wins) / SUM(wins + losses)) * 100, 1), '') AS `all.win_percent`,
SUM(CASE WHEN d.created_date >= %s THEN 1 ELSE 0 END) AS `season.num_decks`,
SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) AS `season.wins`,
SUM(CASE WHEN d.created_date >= %s THEN losses ELSE 0 END) AS `season.losses`,
SUM(CASE WHEN d.created_date >= %s THEN draws ELSE 0 END) AS `season.draws`,
IFNULL(ROUND((SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) / SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END + CASE WHEN d.created_date >= %s THEN losses ELSE 0 END)) * 100, 1), '') AS `season.win_percent`
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
WHERE {where}
GROUP BY a.id
ORDER BY {order_by}
""".format(where=where, order_by=order_by)
archetypes = [
Archetype(a)
for a in db().execute(sql, [rotation.last_rotation().timestamp()] * 7)
]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.parent = archetypes_by_id.get(a.parent_id, None)
return archetypes
def load_archetypes_deckless(
where: str = '1 = 1',
order_by: str = '`all_num_decks` DESC, `all_wins` DESC, name',
season_id: int = None) -> List[Archetype]:
sql = """
SELECT
a.id,
a.name,
aca.ancestor AS parent_id,
{all_select}
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
{season_join}
{nwdl_join}
WHERE ({where}) AND ({season_query})
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.
ORDER BY
{order_by}
""".format(all_select=deck.nwdl_all_select(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join(),
where=where,
season_query=query.season_query(season_id),
order_by=order_by)
archetypes = [Archetype(a) for a in db().execute(sql)]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.parent = archetypes_by_id.get(a.parent_id, None)
return archetypes
def season_stats() -> Dict[int, Dict[str, Union[int, datetime.datetime]]]:
sql = """
SELECT
season_id,
start_date,
end_date,
DATEDIFF(IFNULL(FROM_UNIXTIME(end_date), NOW()), FROM_UNIXTIME(start_date)) AS length_in_days,
num_decks,
num_league_decks,
num_people,
num_competitions,
num_archetypes,
num_matches,
num_cards
FROM
_season_stats
"""
stats = {r['season_id']: r for r in db().select(sql)}
for season in stats.values():
season['start_date'] = dtutil.ts2dt(season['start_date'])
season['end_date'] = dtutil.ts2dt(
season['end_date']) if season['end_date'] else None
return stats
def load_notes():
sql = """
SELECT
pn.created_date,
pn.creator_id,
{creator_query} AS creator,
pn.subject_id,
{subject_query} AS subject,
note
FROM
person_note AS pn
INNER JOIN
person AS c ON pn.creator_id = c.id
INNER JOIN
person AS s ON pn.subject_id = s.id
ORDER BY
s.id,
pn.created_date DESC
""".format(creator_query=query.person_query('c'), subject_query=query.person_query('s'))
notes = [Container(r) for r in db().execute(sql)]
for n in notes:
n.created_date = dtutil.ts2dt(n.created_date)
return notes
def get_matches(d: deck.Deck,
should_load_decks: bool = False) -> List[Container]:
sql = """
SELECT
m.`date`, m.id, m.round, m.elimination,
dm1.games AS game_wins,
dm2.deck_id AS opponent_deck_id, IFNULL(dm2.games, 0) AS game_losses,
d2.name AS opponent_deck_name,
{person_query} AS opponent
FROM `match` AS m
INNER JOIN deck_match AS dm1 ON m.id = dm1.match_id AND dm1.deck_id = %s
LEFT JOIN deck_match AS dm2 ON m.id = dm2.match_id AND dm2.deck_id <> %s
INNER JOIN deck AS d1 ON dm1.deck_id = d1.id
LEFT JOIN deck AS d2 ON dm2.deck_id = d2.id
LEFT JOIN person AS p ON p.id = d2.person_id
ORDER BY m.date, round
""".format(person_query=query.person_query())
matches = [Container(m) for m in db().execute(sql, [d.id, d.id])]
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)
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_all_matchups(where: str = 'TRUE', season_id: Optional[int] = None, retry: bool = False) -> List[Container]:
sql = """
SELECT
archetype_id,
a.name AS archetype_name,
opponent_archetype_id AS id,
oa.name AS name,
SUM(wins) AS all_wins,
SUM(losses) AS all_losses,
SUM(draws) AS all_draws,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS all_win_percent
FROM
_matchup_stats AS ms
INNER JOIN
archetype AS a ON archetype_id = a.id
INNER JOIN
archetype AS oa ON opponent_archetype_id = oa.id
LEFT JOIN
({season_table}) AS season ON season.start_date <= ms.day AND (season.end_date IS NULL OR season.end_date > ms.day)
WHERE
({where}) AND ({season_query})
GROUP BY
archetype_id,
opponent_archetype_id
ORDER BY
all_wins DESC,
oa.name
""".format(season_table=query.season_table(), where=where, season_query=query.season_query(season_id))
try:
return [Container(m) for m in db().select(sql)]
except DatabaseException as e:
if not retry:
print(f"Got {e} trying to load_all_matchups so trying to preaggregate. If this is happening on user time that's undesirable.")
preaggregate_matchups()
return load_all_matchups(where=where, season_id=season_id, retry=True)
print(f'Failed to preaggregate. Giving up.')
raise e
def delete_match(match_id: int) -> None:
db().begin('delete_match')
rs = db().select(
'SELECT deck_id, games FROM deck_match WHERE match_id = %s',
[match_id])
if not rs:
raise TooFewItemsException(
'No deck_match entries found for match_id `{match_id}`')
left_id = rs[0]['deck_id']
left_games = rs[0]['games']
if len(rs) > 1:
right_id = rs[1]['deck_id']
right_games = rs[1]['games']
else:
right_id, right_games = 0, 0
update_cache(left_id, left_games, right_games, delete=True)
update_cache(right_id, right_games, left_games, delete=True)
sql = 'DELETE FROM `match` WHERE id = %s'
db().execute(sql, [match_id])
db().commit('delete_match')
if rs:
redis.clear(f'decksite:deck:{left_id}', f'decksite:deck:{right_id}')
def detail(self,
p: Person,
season_id: Optional[int] = None) -> Optional[List[Deck]]:
if self.detail_sql is None:
return None
sql = """
SELECT
GROUP_CONCAT({k}_detail)
FROM
_achievements
WHERE
person_id={id} AND {season_query}
GROUP BY
person_id
""".format(k=self.key,
id=p.id,
season_query=query.season_query(season_id))
ids = db().value(sql)
result = Container()
result.decks = deck.load_decks(
where=f'd.id IN ({ids})') if ids is not None else []
for f in self.flags:
result[f] = True
return result
def load_competitive_stats(decks: List[Deck]) -> None:
decks_by_id = {d.id: d for d in decks if d.get('omw') is None}
if len(decks_by_id) == 0:
return
if len(decks_by_id) < 1000:
where = 'd.id IN ({deck_ids})'.format(
deck_ids=', '.join(map(sqlescape, map(str, decks_by_id.keys()))))
else:
where = 'TRUE' # MySQL doesn't like to be asked to do IN queries for very long argument lists. Just load everything. (MariaDB doesn't care, interestingly.)
sql = """
SELECT
d.id,
ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) * 100 AS omw
FROM
deck AS d
INNER JOIN
deck_match AS my_dm ON my_dm.deck_id = d.id
LEFT JOIN
deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = my_odm.deck_id
INNER JOIN
deck_match AS dm ON dm.deck_id = od.id
LEFT JOIN
deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id
INNER JOIN
`match` AS m ON m.id = dm.match_id
WHERE
{where}
GROUP BY
d.id
""".format(where=where)
rs = db().select(sql)
for row in rs:
if decks_by_id.get(row['id']):
decks_by_id[row['id']].omw = row['omw']
def overlooked_decks() -> List[Deck]:
sql = """
SELECT
deck.id as deck_id
FROM
deck
LEFT JOIN
_applied_rules
ON
deck.id = _applied_rules.deck_id
WHERE
_applied_rules.rule_id IS NULL AND deck.archetype_id IN
(
SELECT
DISTINCT archetype_id
FROM
rule
)
"""
deck_ids = [str(row['deck_id']) for row in db().select(sql)]
if not deck_ids:
return []
ids_list = ', '.join(deck_ids)
return deck.load_decks(where=f'd.id IN ({ids_list})')
def set_achievements(people: List[Person], season_id: int = None) -> None:
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,
CASE WHEN COUNT(CASE WHEN d.retired = 1 THEN 1 ELSE NULL END) = 0 THEN True ELSE False END AS completionist,
SUM(CASE WHEN ct.name = 'League' AND dc.wins >= 5 AND dc.losses 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
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
{season_join}
{competition_join}
WHERE
p.id IN ({ids}) AND ({season_query})
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()),
season_join=query.season_join(),
season_query=query.season_query(season_id))
results = [Container(r) for r in db().select(sql)]
for result in results:
people_by_id[result['id']].num_achievements = len(
[k for k, v in result.items() if k != 'id' and v > 0])
people_by_id[result['id']].achievements = result
people_by_id[result['id']].achievements.pop('id')
def assign(deck_id, archetype_id):
return db().execute(
'UPDATE deck SET reviewed = TRUE, archetype_id = ? WHERE id = ?',
[archetype_id, deck_id])
def set_locale(person_id: int, locale: str) -> None:
db().execute('UPDATE person SET locale = %s WHERE id = %s',
[locale, person_id])
def squash(p1id: int, p2id: int, col1: str, col2: str) -> None:
logger.warning('Squashing {p1id} and {p2id} on {col1} and {col2}'.format(
p1id=p1id, p2id=p2id, col1=col1, col2=col2))
db().begin('squash')
new_value = db().value(
'SELECT {col2} FROM person WHERE id = %s'.format(col2=col2), [p2id])
db().execute('UPDATE deck SET person_id = %s WHERE person_id = %s',
[p1id, p2id])
db().execute('DELETE FROM person WHERE id = %s', [p2id])
db().execute(
'UPDATE person SET {col2} = %s WHERE id = %s'.format(col2=col2),
[new_value, p1id])
db().commit('squash')
def is_banned(mtgo_username: str) -> bool:
return db().value('SELECT banned FROM person WHERE mtgo_username = %s',
[mtgo_username]) == 1
def remove_discord_link(discord_id: int) -> int:
sql = 'UPDATE person SET discord_id = NULL WHERE discord_id = %s'
return db().execute(sql, [discord_id])
def unlink_discord(person_id: int) -> int:
sql = 'UPDATE person SET discord_id = NULL WHERE id = %s'
return db().execute(sql, [person_id])
def add_note(creator_id: int, subject_id: int, note: str) -> None:
sql = 'INSERT INTO person_note (created_date, creator_id, subject_id, note) VALUES (UNIX_TIMESTAMP(NOW()), %s, %s, %s)'
db().execute(sql, [creator_id, subject_id, note])
def associate(d: deck.Deck, discord_id: int) -> int:
person_id = db().value('SELECT person_id FROM deck WHERE id = %s', [d.id],
fail_on_missing=True)
sql = 'UPDATE person SET discord_id = %s WHERE id = %s'
return db().execute(sql, [discord_id, person_id])
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 percent(self, season_id: Optional[int] = None) -> float: # pylint: disable=unused-argument
sql = 'SELECT COUNT(*) AS mnum FROM _achievements'
r = db().select(sql)[0]
return len(self.hosts) * 100.0 / int(r['mnum'])
