def load_notes(person_id: int = None) -> List[Container]:
where = f'subject_id = {person_id}' if person_id else 'TRUE'
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
WHERE
{where}
ORDER BY
s.id,
pn.created_date DESC
""".format(creator_query=query.person_query('c'),
subject_query=query.person_query('s'),
where=where)
notes = [Container(r) for r in db().select(sql)]
for n in notes:
n.created_date = dtutil.ts2dt(n.created_date)
n.display_date = dtutil.display_date(n.created_date)
return notes
def matches_api() -> Response:
"""
Grab a slice of results from a 0-indexed resultset of matches.
Input:
{
'competitionId': <int?>,
'page': <int>,
'pageSize': <int>,
'q': <str>,
'sortBy': <str>,
'sortOrder': <'ASC'|'DESC'>,
'seasonId': <int|'all'?>
}
Output:
{
'page': <int>,
'objects': [<entry>],
'total': <int>
}
"""
order_by = query.matches_order_by(request.args.get('sortBy'),
request.args.get('sortOrder'))
page_size = int(request.args.get('pageSize', DEFAULT_LIVE_TABLE_PAGE_SIZE))
page = int(request.args.get('page', 0))
start = page * page_size
limit = f'LIMIT {start}, {page_size}'
q = request.args.get('q', '').strip()
person_where = query.text_match_where(query.person_query(),
q) if q else 'TRUE'
opponent_where = query.text_match_where(query.person_query('o'),
q) if q else 'TRUE'
where = f'({person_where} OR {opponent_where})'
try:
competition_id = int(request.args.get('competitionId', ''))
where += f' AND (c.id = {competition_id})'
season_id = None
except ValueError:
season_id = seasons.season_id(str(request.args.get('seasonId')), None)
entries = match.load_matches(where=where,
order_by=order_by,
limit=limit,
season_id=season_id,
show_active_deck_names=session.get(
'admin', False))
prepare_matches(entries)
total = match.load_matches_count(where=where, season_id=season_id)
r = {'page': page, 'total': total, 'objects': entries}
resp = return_json(r, camelize=True)
resp.set_cookie('page_size', str(page_size))
return resp
def load_people(where='1 = 1', order_by='`season_num_decks` DESC, `all_num_decks` DESC, name'):
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 THEN 1 ELSE 0 END) AS `all_num_competitions`,
{season_select},
SUM(DISTINCT CASE WHEN d.created_date >= %s AND d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `season_num_competitions`
FROM
person AS p
LEFT JOIN
deck AS d ON p.id = d.person_id
{nwdl_join}
WHERE
{where}
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(), all_select=deck.nwdl_all_select(), season_select=deck.nwdl_season_select(), nwdl_join=deck.nwdl_join(), where=where, order_by=order_by)
people = [Person(r) for r in db().execute(sql, [int(rotation.last_rotation().timestamp())])]
if len(people) > 0:
set_decks(people)
set_achievements(people)
set_head_to_head(people)
return people
def load_people(where: str = 'TRUE',
order_by_name: bool = False,
season_id: Optional[int] = None) -> Sequence[Person]:
# First we retrieve the people because we want to return a Person for each person that exists even if they have no decks.
# This is two separate queries for performance reasons. See #5564.
sql = """
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}
""".format(person_query=query.person_query(), where=where)
people = [Person(r) for r in db().select(sql)]
stats = load_people_stats(where, season_id)
for p in people:
p.update(stats.get(p.id, {}))
p.season_id = season_id
if order_by_name:
people.sort(key=lambda p: p.get('name') or 'ZZZZZZZZZZ')
else:
people.sort(key=lambda p:
(-p.get('num_decks', 0), 1)) # (, p.get('name')))
return people
def load_people(where='1 = 1'):
sql = """
SELECT p.id, {person_query} AS name,
COUNT(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(d.wins) / SUM(d.wins + d.losses)) * 100, 1), '') AS `all.win_percent`,
SUM(CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `all.num_competitions`,
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`,
SUM(CASE WHEN d.created_date >= %s AND d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `season.num_competitions`
FROM person AS p
LEFT JOIN deck AS d ON p.id = d.person_id
WHERE {where}
GROUP BY p.id
ORDER BY `season.num_decks` DESC, `all.num_decks` DESC, name
""".format(person_query=query.person_query(), where=where)
people = [Person(r) for r in db().execute(sql, [rotation.last_rotation().timestamp()] * 8)]
if len(people) > 0:
set_decks(people)
return people
def get_matches(d, should_load_decks=False):
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 round
""".format(person_query=query.person_query())
matches = [Container(m) for m in db().execute(sql, [d.id, d.id])]
if should_load_decks and len(matches) > 0:
decks = load_decks('d.id IN ({ids})'.format(ids=', '.join([
sqlescape(str(m.opponent_deck_id)) for m in matches
if m.opponent_deck_id is not None
])))
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:
m.opponent_deck = decks_by_id[m.opponent_deck_id]
elif should_load_decks:
m.opponent_deck = None
return matches
def leaderboards(where: str = "ct.name = 'Gatherling'",
season_id: Optional[int] = None) -> List[Dict[str, Any]]:
sql = """
SELECT
p.id AS person_id,
{person_query} AS person,
cs.name AS competition_series_name,
sp.name AS sponsor_name,
COUNT(DISTINCT d.id) AS tournaments,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
COUNT(DISTINCT d.id) + SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS points
FROM
competition AS c
INNER JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
sponsor AS sp ON sp.id = cs.sponsor_id
INNER JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
INNER JOIN
deck AS d ON d.competition_id = c.id
INNER JOIN
person AS p ON d.person_id = p.id
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
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
cs.id,
p.id
ORDER BY
cs.id,
points DESC,
wins DESC,
tournaments DESC,
person
""".format(person_query=query.person_query(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id))
results = []
current: Dict[str, Any] = {}
for row in db().select(sql):
k = row['competition_series_name']
if current.get('competition_series_name', None) != k:
if len(current) > 0:
results.append(current)
current = {
'competition_series_name': row['competition_series_name'],
'entries': [],
'sponsor_name': row['sponsor_name']
}
row.pop('competition_series_name')
current['entries'] = current['entries'] + [Container(row)]
if len(current) > 0:
results.append(current)
return results
def leaderboard(
self,
season_id: Optional[int] = None) -> Optional[List[Container]]:
season_condition = query.season_query(season_id)
person_query = query.person_query()
sql = f"""
SELECT
{person_query} AS person,
SUM({self.key}) AS points,
p.id AS person_id
FROM
person AS p
JOIN
_achievements
ON
p.id = _achievements.person_id
WHERE
{season_condition}
GROUP BY
p.id
HAVING
points >=
( -- Work out the minimum score to make top N, counting ties
SELECT
MIN(s)
FROM
(
SELECT
SUM({self.key}) AS s
FROM
_achievements
WHERE
{season_condition}
GROUP BY
person_id
HAVING
s > 0
ORDER BY
s DESC
LIMIT
{LEADERBOARD_TOP_N}
) AS _
)
ORDER BY
points DESC,
name
LIMIT {LEADERBOARD_LIMIT}
"""
leaderboard = []
for row in db().select(sql):
c = Container(row)
c.score = c.points
leaderboard.append(c)
return leaderboard if len(leaderboard) > 0 else None
def leaderboards_api() -> Response:
"""
Grab a slice of results from a 0-indexed resultset of leaderboard entries.
Input:
{
'competitionId': <int?>,
'competitionSeriesId': <int?>
'page': <int>,
'pageSize': <int>,
'sortBy': <str>,
'sortOrder': <'ASC'|'DESC'>,
'seasonId': <int|'all'?>,
'q': <str>
}
Output:
{
'page': <int>,
'objects': [<entry>],
'total': <int>
}
"""
order_by = query.leaderboard_order_by(request.args.get('sortBy'),
request.args.get('sortOrder'))
page_size = int(request.args.get('pageSize', DEFAULT_LIVE_TABLE_PAGE_SIZE))
page = int(request.args.get('page', 0))
start = page * page_size
limit = f'LIMIT {start}, {page_size}'
q = request.args.get('q', '').strip()
where = query.text_match_where(query.person_query(), q) if q else 'TRUE'
try:
competition_id = int(request.args.get('competitionId', ''))
where += f' AND (c.id = {competition_id})'
season_id = None
except ValueError:
season_id = seasons.season_id(str(request.args.get('seasonId')), None)
try:
competition_series_id = int(request.args.get('competitionSeriesId',
''))
where += f' AND (cs.id = {competition_series_id})'
except ValueError:
pass
entries = comp.load_leaderboard(where=where,
group_by='p.id',
order_by=order_by,
limit=limit,
season_id=season_id)
prepare_leaderboard(entries)
total = comp.load_leaderboard_count(where=where, season_id=season_id)
r = {'page': page, 'total': total, 'objects': entries}
resp = return_json(r, camelize=True)
resp.set_cookie('page_size', str(page_size))
return resp
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 load_people(
where: str = 'TRUE',
order_by: str = 'num_decks DESC, p.name',
limit: str = '',
season_id: Optional[Union[str, int]] = None) -> Sequence[Person]:
person_query = query.person_query()
season_join = query.season_join() if season_id else ''
season_query = query.season_query(season_id, 'season.id')
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,
SUM(1) AS num_decks,
SUM(dc.wins) AS wins,
SUM(dc.losses) AS losses,
SUM(dc.draws) AS draws,
SUM(wins - losses) AS record,
SUM(CASE WHEN dc.wins >= 5 AND dc.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 d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(dc.wins) / NULLIF(SUM(dc.wins + dc.losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
p.id
ORDER BY
{order_by}
{limit}
"""
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
return people
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().select(sql)]
for p in people:
p.season_id = season_id
if len(people) > 0:
set_achievements(people, season_id)
set_head_to_head(people, season_id)
return people
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 people_api() -> Response:
"""
Grab a slice of results from a 0-indexed resultset of people.
Input:
{
'page': <int>,
'pageSize': <int>,
'sortBy': <str>,
'sortOrder': <'ASC'|'DESC'>,
'seasonId': <int|'all'>,
'q': <str>
}
Output:
{
'page': <int>,
'objects': [<person>],
'total': <int>
}
"""
order_by = query.people_order_by(request.args.get('sortBy'),
request.args.get('sortOrder'))
page_size = int(request.args.get('pageSize', DEFAULT_LIVE_TABLE_PAGE_SIZE))
page = int(request.args.get('page', 0))
start = page * page_size
limit = f'LIMIT {start}, {page_size}'
season_id = seasons.season_id(str(request.args.get('seasonId')), None)
q = request.args.get('q', '').strip()
where = query.text_match_where(query.person_query(), q) if q else 'TRUE'
people = ps.load_people(where=where,
order_by=order_by,
limit=limit,
season_id=season_id)
prepare_people(people)
total = ps.load_people_count(where=where, season_id=season_id)
r = {'page': page, 'total': total, 'objects': people}
resp = return_json(r, camelize=True)
resp.set_cookie('page_size', str(page_size))
return resp
def load_decks(where: str = '1 = 1',
having: str = '1 = 1',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[int] = None
) -> List[Deck]:
if not redis.enabled():
return load_decks_heavy(where, having, 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,
d.decklist_hash,
cache.active_date,
cache.wins,
cache.losses,
cache.draws,
ct.name AS competition_type_name
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
d.id,
d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
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.
HAVING
{having}
ORDER BY
{order_by}
{limit}
"""
sql = sql.format(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, having=having, order_by=order_by, limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().select(sql)
decks_by_id = {}
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'])
else:
decks_by_id[row['id']] = deserialize_deck(d)
if heavy:
where = 'd.id IN ({deck_ids})'.format(deck_ids=', '.join(map(sqlescape, map(str, heavy))))
loaded_decks = load_decks_heavy(where)
for d in loaded_decks:
decks_by_id[d.id] = d
decks = []
for row in rows:
decks.append(decks_by_id[row['id']])
return decks
def load_decks_heavy(where: str = '1 = 1',
having: str = '1 = 1',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[int] = 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,
d.reviewed,
s.name AS source_name,
IFNULL(a.name, '') AS archetype_name,
cache.normalized_name AS name,
cache.colors,
cache.colored_symbols,
cache.legal_formats,
ROUND(cache.omw * 100, 2) AS omw,
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,
d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
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.
HAVING
{having}
ORDER BY
{order_by}
{limit}
""".format(person_query=query.person_query(), competition_join=query.competition_join(), season_join=query.season_join(), where=where, season_query=query.season_query(season_id, 'season.id'), having=having, order_by=order_by, limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().select(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()]
d.wins = int(d.wins)
d.losses = int(d.losses)
d.draws = int(d.draws)
decks.append(d)
load_cards(decks)
load_competitive_stats(decks)
for d in decks:
expiry = 60 if d.is_in_current_run() else 3600
redis.store('decksite:deck:{id}'.format(id=d.id), d, ex=expiry)
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 leaderboards(where="ct.name = 'Gatherling' AND season.id = (SELECT id FROM season WHERE start_date = (SELECT MAX(start_date) FROM season WHERE start_date < UNIX_TIMESTAMP(NOW())))"):
sql = """
SELECT
p.id AS person_id,
season.code AS season_code,
{person_query} AS person,
cs.name AS competition_series_name,
COUNT(DISTINCT d.id) AS tournaments,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
COUNT(DISTINCT d.id) + SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS points
FROM
competition AS c
INNER JOIN
competition_series AS cs ON cs.id = c.competition_series_id
INNER JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
INNER JOIN
deck AS d ON d.competition_id = c.id
INNER JOIN
person AS p ON d.person_id = p.id
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
INNER JOIN
(
SELECT
s.id,
s.code,
s.start_date AS start_date,
s2.start_date AS end_date
FROM
season AS s
LEFT JOIN
season AS s2
ON
s2.start_date = (SELECT MIN(start_date) FROM season WHERE start_date > s.start_date)
) AS season ON c.start_date >= season.start_date AND (c.start_date < season.end_date OR season.end_date IS NULL)
WHERE
{where}
GROUP BY
cs.id,
p.id,
season.id
ORDER BY
cs.id,
points DESC,
wins DESC,
tournaments DESC,
person
""".format(person_query=query.person_query(), where=where)
results = []
current = {}
for row in db().execute(sql):
k = (row['competition_series_name'], row['season_code'])
if (current.get('competition_series_name', None), current.get('season_code', None)) != k:
if len(current) > 0:
results.append(current)
current = {
'competition_series_name': row['competition_series_name'],
'season_code': row['season_code'],
'entries': []
}
row.pop('competition_series_name')
row.pop('season_code')
current['entries'] = current['entries'] + [Container(row)]
if len(current) > 0:
results.append(current)
return results
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, IFNULL(d.finish, 9999999999)'
sql = """
SELECT d.id, d.name, d.created_date, d.updated_date, d.wins, d.losses, d.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,
d.created_date AS `date`, d.decklist_hash, d.retired,
s.name AS source_name, IFNULL(a.name, '') AS archetype_name,
SUM(opp.wins) AS opp_wins, SUM(opp.losses) AS opp_losses, ROUND(SUM(opp.wins) / (SUM(opp.wins) + SUM(opp.losses)), 2) * 100 AS omw,
GROUP_CONCAT(DISTINCT CONCAT(dc.card, '|', dc.n, '|', dc.sideboard) SEPARATOR '█') AS cards,
cache.colors, cache.colored_symbols, cache.legal_formats,
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 person AS p ON d.person_id = p.id
LEFT JOIN competition AS c ON d.competition_id = c.id
INNER JOIN source AS s ON d.source_id = s.id
LEFT JOIN archetype AS a ON d.archetype_id = a.id
LEFT JOIN deck AS opp ON opp.id IN (SELECT deck_id FROM deck_match WHERE deck_id <> d.id AND match_id IN (SELECT match_id FROM deck_match WHERE deck_id = d.id))
LEFT JOIN competition_type AS ct ON ct.id = c.competition_type_id
LEFT JOIN deck_card AS dc ON d.id = dc.deck_id
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 m.id = dm.match_id
WHERE {where}
GROUP BY d.id
ORDER BY {order_by}
{limit}
""".format(person_query=query.person_query(),
where=where,
order_by=order_by,
limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().execute(sql)
cards = oracle.cards_by_name()
decks = []
for row in rows:
d = Deck(row)
d.maindeck = []
d.sideboard = []
cards_s = (row['cards'] or '')
for entry in filter(None, cards_s.split('█')):
name, n, is_sideboard = entry.split('|')
location = 'sideboard' if bool(int(is_sideboard)) else 'maindeck'
d[location].append({
'n': int(n),
'name': name,
'card': cards[name]
})
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)
return decks
def load_people(where: str = '1 = 1',
order_by: str = '`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,
num_decks,
wins,
losses,
draws,
perfect_runs,
tournament_wins,
tournament_top8s,
win_percent,
num_competitions
FROM
person AS p
LEFT JOIN
(
SELECT
d.person_id,
COUNT(d.id) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) 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 d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
deck AS d
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
{season_query}
GROUP BY
d.person_id
) AS stats ON p.id = stats.person_id
WHERE
{where}
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id, 'season.id'),
order_by=order_by)
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
return people
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_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_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 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 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
