def base_query(where: str = '(1 = 1)') -> str:
return """
SELECT
{card_queries},
{face_queries},
GROUP_CONCAT(face_name SEPARATOR '|') AS names,
legalities,
pd_legal,
bugs
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) 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(card_queries=', '.join(
prop['query'].format(table='u', column=name)
for name, prop in card.card_properties().items()),
face_queries=', '.join(
prop['query'].format(table='u', column=name)
for name, prop in card.face_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 base_query_lite() -> str:
return """
SELECT
{base_query_props}
FROM (
SELECT {card_props}, {face_props}, f.name AS face_name
FROM
card AS c
INNER JOIN
face AS f ON c.id = f.card_id
GROUP BY
f.id
ORDER BY
f.card_id, f.position
) AS u
GROUP BY u.id
""".format(
base_query_props=', '.join(
prop['query'].format(table='u', column=name)
for name, prop in card.base_query_lite_properties().items()),
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']),
)
def setup():
db().begin()
db().execute('CREATE TABLE IF NOT EXISTS db_version (version INTEGER)')
db().execute('CREATE TABLE IF NOT EXISTS version (version TEXT)')
sql = create_table_def('card', card.card_properties())
db().execute(sql)
sql = create_table_def('face', card.face_properties())
db().execute(sql)
sql = create_table_def('set', card.set_properties())
db().execute(sql)
sql = create_table_def('color', card.color_properties())
db().execute(sql)
sql = create_table_def('card_color', card.card_color_properties())
db().execute(sql)
sql = create_table_def('card_color_identity', card.card_color_properties())
db().execute(sql)
sql = create_table_def('card_supertype',
card.card_type_properties('supertype'))
db().execute(sql)
sql = create_table_def('card_type', card.card_type_properties('type'))
db().execute(sql)
sql = create_table_def('card_subtype',
card.card_type_properties('subtype'))
db().execute(sql)
sql = create_table_def('format', card.format_properties())
db().execute(sql)
sql = create_table_def('card_legality', card.card_legality_properties())
db().execute(sql)
sql = create_table_def('card_alias', card.card_alias_properties())
db().execute(sql)
sql = create_table_def('card_bug', card.card_bug_properties())
db().execute(sql)
sql = create_table_def('rarity', card.format_properties(
)) # This has the same profile as `format` (`id`, `name`)
db().execute(sql)
db().execute("""INSERT INTO color (name, symbol) VALUES
('White', 'W'),
('Blue', 'U'),
('Black', 'B'),
('Red', 'R'),
('Green', 'G')
""")
db().execute("""INSERT INTO rarity (name) VALUES
('Basic Land'),
('Common'),
('Uncommon'),
('Rare'),
('Mythic Rare')
""")
sql = create_table_def('printing', card.printing_properties())
db().execute(sql)
# Speed up innermost subselect in base_query.
db().execute(
'CREATE INDEX idx_card_id_format_id ON card_legality (card_id, format_id, legality)'
)
db().execute(
'INSERT INTO db_version (version) VALUES ({0})'.format(SCHEMA_VERSION))
db().commit()
def insert_face(p: CardDescription, card_id: int, position: int = 1) -> None:
if not card_id:
raise InvalidDataException(
f'Cannot insert a face without a card_id: {p}')
p['oracle_text'] = p.get('oracle_text', '')
sql = 'INSERT INTO face (card_id, position, '
sql += ', '.join(name for name, prop in card.face_properties().items()
if prop['scryfall'])
sql += ') VALUES (%s, %s, '
sql += ', '.join('%s' for name, prop in card.face_properties().items()
if prop['scryfall'])
sql += ')'
values: List[Any] = [card_id, position]
values += [
p.get(database2json(name))
for name, prop in card.face_properties().items() if prop['scryfall']
]
db().execute(sql, values)
def base_query(where='(1 = 1)'):
return """
SELECT
{card_queries},
{face_queries},
GROUP_CONCAT(face_name SEPARATOR '|') AS names,
legalities,
pd_legal,
bug_desc,
bug_class,
bug_last_confirmed
FROM
(SELECT {card_props}, {face_props}, f.name AS face_name,
SUM(CASE WHEN cl.format_id = {format_id} THEN 1 ELSE 0 END) > 0 AS pd_legal,
GROUP_CONCAT({legality_code}) AS legalities,
bugs.description AS bug_desc,
bugs.classification AS bug_class,
bugs.last_confirmed AS bug_last_confirmed
FROM card AS c
INNER JOIN face AS f ON c.id = f.card_id
LEFT OUTER JOIN card_legality AS cl ON c.id = cl.card_id
LEFT OUTER JOIN format AS fo ON cl.format_id = fo.id
LEFT OUTER JOIN card_bugs AS bugs ON c.id = bugs.card_id
GROUP BY f.id
ORDER BY f.card_id, f.position)
AS u
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(card_queries=', '.join(
prop['query'].format(table='u', column=name)
for name, prop in card.card_properties().items()),
face_queries=', '.join(
prop['query'].format(table='u', column=name)
for name, prop in card.face_properties().items()),
format_id=get_format_id('Penny Dreadful'),
legality_code=db().concat(['fo.name', "':'", 'cl.legality']),
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)
async def insert_cards_async(printings: List[CardDescription]) -> List[int]:
next_card_id = (db().value('SELECT MAX(id) FROM card') or 0) + 1
values = await determine_values_async(printings, next_card_id)
insert_many('card', card.card_properties(), values['card'], ['id'])
if values[
'card_color']: # We should not issue this query if we are only inserting colorless cards as they don't have an entry in this table.
insert_many('card_color', card.card_color_properties(),
values['card_color'])
insert_many('card_color_identity', card.card_color_properties(),
values['card_color_identity'])
insert_many('printing', card.printing_properties(), values['printing'])
insert_many('face', card.face_properties(), values['face'], ['position'])
if values['card_legality']:
insert_many('card_legality', card.card_legality_properties(),
values['card_legality'], ['legality'])
# Create the current Penny Dreadful format if necessary.
get_format_id('Penny Dreadful', True)
await update_bugged_cards_async()
return [c['id'] for c in values['card']]
def update_database(new_date: datetime.datetime) -> None:
# pylint: disable=too-many-locals
db().begin('update_database')
db().execute('DELETE FROM scryfall_version')
# In order to rebuild the card table, we must delete (and rebuild) all tables with a FK to it
db().execute('DROP TABLE IF EXISTS _cache_card')
db().execute("""
DELETE FROM card_color;
DELETE FROM card_color_identity;
DELETE FROM card_legality;
DELETE FROM card_bug;
DELETE FROM face;
DELETE FROM printing;
DELETE FROM card;
DELETE FROM `set`;
""")
sets = {}
for s in fetcher.all_sets():
sets[s['code']] = insert_set(s)
every_card_printing = fetcher.all_cards()
rarity_ids = {
x['name']: x['id']
for x in db().select('SELECT id, name FROM rarity;')
}
scryfall_to_internal_rarity = {
'common': ('Common', rarity_ids['Common']),
'uncommon': ('Uncommon', rarity_ids['Uncommon']),
'rare': ('Rare', rarity_ids['Rare']),
'mythic': ('Mythic Rare', rarity_ids['Mythic Rare'])
}
# Strategy:
# Iterate through all printings of each cards, building several queries to be executed at the end.
# If we hit a new card, add it to the queries the several tables tracking cards:
# card, face, card_color, card_color_identity, printing
# If it's a printing of a card we already have, just add to the printing query
# We need to special case the result (melded) side of meld cards, due to their general weirdness.
cards: Dict[str, int] = {}
meld_result_printings = []
card_query = 'INSERT INTO `card` (id, layout) VALUES '
card_values = []
card_color_query = 'INSERT IGNORE INTO `card_color` (card_id, color_id) VALUES '
card_color_values = []
card_color_identity_query = 'INSERT IGNORE INTO `card_color_identity` (card_id, color_id) VALUES '
card_color_identity_values = []
face_query = 'INSERT INTO `face` (card_id, position, '
face_query += ', '.join(name
for name, prop in card.face_properties().items()
if prop['scryfall'])
face_query += ') VALUES '
face_values = []
printing_query = 'INSERT INTO `printing` (card_id, set_id, '
printing_query += 'system_id, rarity, flavor, artist, number, multiverseid, watermark, border, timeshifted, reserved, mci_number, rarity_id'
printing_query += ') VALUES'
printing_values = []
colors_raw = db().select(
'SELECT id, symbol FROM color GROUP BY name ORDER BY id;')
colors = {c['symbol'].upper(): c['id'] for c in colors_raw}
next_card_id = 1
card_legality_query = 'INSERT IGNORE INTO `card_legality` (card_id, format_id, legality) VALUES '
card_legality_values = []
for p in every_card_printing:
# Exclude little girl because {hw} mana is a problem rn.
if p['name'] == 'Little Girl':
continue
if is_meld_result(p):
meld_result_printings.append(p)
rarity, rarity_id = scryfall_to_internal_rarity[p['rarity']]
try:
set_id = sets[p['set']]
except KeyError:
raise InvalidDataException(
f"We think we should have set {p['set']} but it's not in {sets} (from {p})"
)
# If we already have the card, all we need is to record the next printing of it
if p['name'] in cards:
card_id = cards[p['name']]
printing_values.append(
printing_value(p, card_id, set_id, rarity_id, rarity))
continue
card_id = next_card_id
next_card_id += 1
cards[p['name']] = card_id
card_values.append("({i},'{l}')".format(i=card_id, l=p['layout']))
if p['layout'] in [
'augment', 'emblem', 'host', 'leveler', 'meld', 'normal',
'planar', 'saga', 'scheme', 'token', 'vanguard'
]:
face_values.append(single_face_value(p, card_id))
elif p['layout'] in [
'double_faced_token', 'flip', 'split', 'transform'
]:
face_values += multiple_faces_values(p, card_id)
for color in p.get('colors', []):
color_id = colors[color]
card_color_values.append(f'({card_id}, {color_id})')
for color in p.get('color_identity', []):
color_id = colors[color]
card_color_identity_values.append(f'({card_id}, {color_id})')
for format_, status in p.get('legalities', {}).items():
if status == 'not_legal':
continue
# Strictly speaking we could drop all this capitalizing and use what Scryfall sends us as the canonical name as it's just a holdover from mtgjson.
format_id = get_format_id(format_.capitalize(), True)
internal_status = status.capitalize()
card_legality_values.append(
f"({card_id}, {format_id}, '{internal_status}')")
cards[p['name']] = card_id
printing_values.append(
printing_value(p, card_id, set_id, rarity_id, rarity))
card_query += ',\n'.join(card_values)
card_query += ';'
db().execute(card_query)
card_color_query += ',\n'.join(card_color_values) + ';'
db().execute(card_color_query)
card_color_identity_query += ',\n'.join(card_color_identity_values) + ';'
db().execute(card_color_identity_query)
for p in meld_result_printings:
insert_meld_result_faces(p, cards)
printing_query += ',\n'.join(printing_values)
printing_query += ';'
db().execute(printing_query)
face_query += ',\n'.join(face_values)
face_query += ';'
db().execute(face_query)
card_legality_query += ',\n'.join(card_legality_values)
card_legality_query += ';'
db().execute(card_legality_query)
# Create the current Penny Dreadful format.
get_format_id('Penny Dreadful', True)
update_bugged_cards()
update_pd_legality()
db().execute('INSERT INTO scryfall_version (last_updated) VALUES (%s)',
[dtutil.dt2ts(new_date)])
db().commit('update_database')
def insert_card(c):
name = card_name(c)
try:
card_id = CARD_IDS[name]
except KeyError:
sql = 'INSERT INTO card ('
sql += ', '.join(name for name, prop in card.card_properties().items()
if prop['mtgjson'])
sql += ') VALUES ('
sql += ', '.join('?' for name, prop in card.card_properties().items()
if prop['mtgjson'])
sql += ')'
values = [
c.get(database2json(name))
for name, prop in card.card_properties().items() if prop['mtgjson']
]
db().execute(sql, values)
card_id = db().last_insert_rowid()
CARD_IDS[name] = card_id
# mtgjson thinks the text of Jhessian Lookout is NULL not '' but that is clearly wrong.
if c.get('text', None) is None and c['layout'] in [
'normal', 'token', 'double-faced', 'split'
]:
c['text'] = ''
c['nameAscii'] = card.unaccent(c.get('name'))
c['searchText'] = re.sub(r'\([^\)]+\)', '', c['text'])
c['cardId'] = card_id
c['position'] = 1 if not c.get('names') else c.get(
'names', [c.get('name')]).index(c.get('name')) + 1
sql = 'INSERT INTO face ('
sql += ', '.join(name for name, prop in card.face_properties().items()
if not prop['primary_key'])
sql += ') VALUES ('
sql += ', '.join('?' for name, prop in card.face_properties().items()
if not prop['primary_key'])
sql += ')'
values = [
c.get(database2json(name))
for name, prop in card.face_properties().items()
if not prop['primary_key']
]
try:
db().execute(sql, values)
except database.DatabaseException:
print(c)
raise
for color in c.get('colors', []):
color_id = db().value('SELECT id FROM color WHERE name = ?', [color])
db().execute(
'INSERT INTO card_color (card_id, color_id) VALUES (?, ?)',
[card_id, color_id])
for symbol in c.get('colorIdentity', []):
color_id = db().value('SELECT id FROM color WHERE symbol = ?',
[symbol])
db().execute(
'INSERT INTO card_color_identity (card_id, color_id) VALUES (?, ?)',
[card_id, color_id])
for supertype in c.get('supertypes', []):
db().execute(
'INSERT INTO card_supertype (card_id, supertype) VALUES (?, ?)',
[card_id, supertype])
for subtype in c.get('subtypes', []):
db().execute(
'INSERT INTO card_subtype (card_id, subtype) VALUES (?, ?)',
[card_id, subtype])
for info in c.get('legalities', []):
format_id = get_format_id(info['format'], True)
db().execute(
'INSERT INTO card_legality (card_id, format_id, legality) VALUES (?, ?, ?)',
[card_id, format_id, info['legality']])
def insert_card(c, update_index: bool = True) -> None:
name, card_id = try_find_card_id(c)
if card_id is None:
sql = 'INSERT INTO card ('
sql += ', '.join(name for name, prop in card.card_properties().items()
if prop['mtgjson'])
sql += ') VALUES ('
sql += ', '.join('%s' for name, prop in card.card_properties().items()
if prop['mtgjson'])
sql += ')'
values = [
c.get(database2json(name))
for name, prop in card.card_properties().items() if prop['mtgjson']
]
db().execute(sql, values)
card_id = db().last_insert_rowid()
CARD_IDS[name] = card_id
# mtgjson thinks the text of Jhessian Lookout is NULL not '' but that is clearly wrong.
if c.get('text', None) is None and c['layout'] in playable_layouts():
c['text'] = ''
c['nameAscii'] = card.unaccent(c.get('name'))
c['searchText'] = re.sub(r'\([^\)]+\)', '', c['text'])
c['cardId'] = card_id
c['position'] = 1 if not c.get('names') else c.get(
'names', [c.get('name')]).index(c.get('name')) + 1
sql = 'INSERT INTO face ('
sql += ', '.join(name for name, prop in card.face_properties().items()
if not prop['primary_key'])
sql += ') VALUES ('
sql += ', '.join('%s' for name, prop in card.face_properties().items()
if not prop['primary_key'])
sql += ')'
values = [
c.get(database2json(name))
for name, prop in card.face_properties().items()
if not prop['primary_key']
]
try:
db().execute(sql, values)
except database.DatabaseException:
print(c)
raise
for color in c.get('colors', []):
color_id = db().value('SELECT id FROM color WHERE name = %s', [color])
# INSERT IGNORE INTO because some cards have multiple faces with the same color. See DFCs and What // When // Where // Who // Why.
db().execute(
'INSERT IGNORE INTO card_color (card_id, color_id) VALUES (%s, %s)',
[card_id, color_id])
for symbol in c.get('colorIdentity', []):
color_id = db().value('SELECT id FROM color WHERE symbol = %s',
[symbol])
# INSERT IGNORE INTO because some cards have multiple faces with the same color identity. See DFCs and What // When // Where // Who // Why.
db().execute(
'INSERT IGNORE INTO card_color_identity (card_id, color_id) VALUES (%s, %s)',
[card_id, color_id])
for supertype in c.get('supertypes', []):
db().execute(
'INSERT INTO card_supertype (card_id, supertype) VALUES (%s, %s)',
[card_id, supertype])
for subtype in c.get('subtypes', []):
db().execute(
'INSERT INTO card_subtype (card_id, subtype) VALUES (%s, %s)',
[card_id, subtype])
for info in c.get('legalities', []):
format_id = get_format_id(info['format'], True)
db().execute(
'INSERT INTO card_legality (card_id, format_id, legality) VALUES (%s, %s, %s)',
[card_id, format_id, info['legality']])
if update_index:
writer = WhooshWriter()
c['id'] = c['cardId']
writer.update_card(c)
