def base_query(where: str = '(1 = 1)') -> str:
return """
SELECT
{base_query_props}
FROM (
SELECT {card_props}, {face_props}, f.name AS face_name,
pd_legal,
legalities
FROM
card AS c
INNER JOIN
face AS f ON c.id = f.card_id
LEFT JOIN (
SELECT
cl.card_id,
SUM(CASE WHEN cl.format_id = {format_id} THEN 1 ELSE 0 END) > 0 AS pd_legal,
GROUP_CONCAT(CONCAT(fo.name, ':', cl.legality)) AS legalities
FROM
card_legality AS cl
LEFT JOIN
format AS fo ON cl.format_id = fo.id
GROUP BY
cl.card_id
) AS cl ON cl.card_id = c.id
GROUP BY
f.id
ORDER BY
f.card_id, f.position
) AS u
LEFT JOIN (
SELECT
cb.card_id,
GROUP_CONCAT(CONCAT(cb.description, '|', cb.classification, '|', cb.last_confirmed, '|', cb.url, '|', cb.from_bug_blog, '|', cb.bannable) SEPARATOR '_SEPARATOR_') AS bugs
FROM
card_bug AS cb
GROUP BY
cb.card_id
) AS bugs ON u.id = bugs.card_id
WHERE u.id IN (SELECT c.id FROM card AS c INNER JOIN face AS f ON c.id = f.card_id WHERE {where})
GROUP BY u.id
""".format(base_query_props=', '.join(
prop['query'].format(table='u', column=name)
for name, prop in card.base_query_properties().items()),
format_id=get_format_id('Penny Dreadful'),
card_props=', '.join('c.{name}'.format(name=name)
for name in card.card_properties()),
face_props=', '.join('f.{name}'.format(name=name)
for name in card.face_properties()
if name not in ['id', 'name']),
where=where)
def update_cache() -> None:
db().execute('DROP TABLE IF EXISTS _new_cache_card')
db().execute('SET group_concat_max_len=100000')
db().execute(
create_table_def('_new_cache_card', card.base_query_properties(),
base_query()))
db().execute(
'CREATE UNIQUE INDEX idx_u_card_id ON _new_cache_card (card_id)')
db().execute(
'CREATE UNIQUE INDEX idx_u_name ON _new_cache_card (name(142))')
db().execute(
'CREATE UNIQUE INDEX idx_u_name_ascii ON _new_cache_card (name_ascii(142))'
)
db().execute(
'CREATE UNIQUE INDEX idx_u_names ON _new_cache_card (names(142))')
db().execute('DROP TABLE IF EXISTS _cache_card')
db().execute('RENAME TABLE _new_cache_card TO _cache_card')
def update_cache() -> None:
db().execute('DROP TABLE IF EXISTS _new_cache_card')
db().execute('SET group_concat_max_len=100000')
db().execute(
create_table_def('_new_cache_card', card.base_query_properties(),
base_query()))
db().execute(
'CREATE UNIQUE INDEX idx_u_card_id ON _new_cache_card (card_id)')
db().execute(
'CREATE UNIQUE INDEX idx_u_name ON _new_cache_card (name(142))')
db().execute(
'CREATE UNIQUE INDEX idx_u_names ON _new_cache_card (names(142))')
db().execute('DROP TABLE IF EXISTS _old_cache_card')
db().execute('CREATE TABLE IF NOT EXISTS _cache_card (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _cache_card TO _old_cache_card, _new_cache_card TO _cache_card'
)
db().execute('DROP TABLE IF EXISTS _old_cache_card')