def whatsinstandard() -> Dict[str, Union[bool, List[Dict[str, str]]]]:
cached = redis.get_container('magic:fetcher:whatisinstandard_6')
if cached is not None:
return cached
try:
info = fetch_tools.fetch_json('http://whatsinstandard.com/api/v6/standard.json')
except FetchException:
cached = redis.get_container('magic:fetcher:whatisinstandard_noex')
if cached is not None:
return cached
raise
redis.store('magic:fetcher:whatisinstandard_6', info, ex=86400)
redis.store('magic:fetcher:whatisinstandard_noex', info)
return info
def prepare(self) -> None:
for p in self.people:
key = f'logsite:people:{p.id}'
data = redis.get_container(key, ex=3600)
if data:
p.fav_format = data.fav_format
p.num_matches = data.num_matches
else:
p.num_matches = match.get_recent_matches_by_player(
p.name).count()
stmt = text("""
SELECT f.name, COUNT(*) AS num_matches
FROM match_players AS mp
INNER JOIN `match` AS m ON mp.match_id = m.id
INNER JOIN format AS f ON m.format_id = f.id
WHERE mp.user_id = :pid
GROUP BY f.id;
""")
p.formats = db.DB.session.query(
'name',
'num_matches').from_statement(stmt).params(pid=p.id).all()
if p.formats:
p.fav_format = '{0} ({1} matches)'.format(
p.formats[0][0], p.formats[0][1])
else:
p.fav_format = '⸺'
redis.store(key, {
'fav_format': p.fav_format,
'num_matches': p.num_matches
},
ex=3600)
def whatsinstandard() -> Dict[str, Union[bool, List[Dict[str, str]]]]:
cached = redis.get_container('magic:fetcher:whatisinstandard')
if cached is not None:
return cached
info = internal.fetch_json('http://whatsinstandard.com/api/v5/sets.json')
redis.store('magic:fetcher:whatisinstandard', info, ex=86400)
return info
def load_decks(where: str = 'TRUE',
having: str = 'TRUE',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[Union[str, int]] = None) -> List[Deck]:
if not redis.enabled():
return load_decks_heavy(where, having, order_by, limit, season_id)
columns = """
d.id,
d.finish,
d.decklist_hash,
cache.active_date,
cache.wins,
cache.losses,
cache.draws,
cache.color_sort,
ct.name AS competition_type_name
"""
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.
"""
sql = load_decks_query(columns,
where=where,
group_by=group_by,
having=having,
order_by=order_by,
limit=limit,
season_id=season_id)
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(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(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