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 insert_card(p: Any, update_index: bool = True) -> Optional[int]:
# Preprocess card partly for sanity but partly just to match what we used to get from mtgjson to make migration easier.
sql = 'INSERT INTO card ('
sql += ', '.join(name for name, prop in card.card_properties().items()
if prop['scryfall'])
sql += ') VALUES ('
sql += ', '.join('%s' for name, prop in card.card_properties().items()
if prop['scryfall'])
sql += ')'
values = [
p.get(database2json(name))
for name, prop in card.card_properties().items() if prop['scryfall']
]
db().execute(sql, values)
card_id = db().last_insert_rowid()
# 'meld' is in the list of normal cards here but is handled differently at a higher level. See above.
if p['layout'] in [
'augment', 'emblem', 'host', 'leveler', 'meld', 'normal', 'planar',
'saga', 'scheme', 'token', 'vanguard'
]:
insert_face(p, card_id)
elif p['layout'] in ['double_faced_token', 'flip', 'split', 'transform']:
insert_card_faces(p, card_id)
else:
raise InvalidDataException(f"Unknown layout {p['layout']}")
for color in p.get('colors', []):
color_id = db().value('SELECT id FROM color WHERE symbol = %s',
[color.capitalize()])
# 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 p.get('color_identity', []):
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 f, 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.
name = f.capitalize()
format_id = get_format_id(name, True)
# INSERT IGNORE INTO because some cards have multiple faces with the same legality. See DFCs and What // When // Where // Who // Why.
db().execute(
'INSERT IGNORE INTO card_legality (card_id, format_id, legality) VALUES (%s, %s, %s)',
[card_id, format_id, status.capitalize()])
if update_index:
p.id = card_id
writer = WhooshWriter()
writer.update_card(p)
return card_id
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 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 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)
