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')