def insert_set(s) -> None:
sql = 'INSERT INTO `set` ('
sql += ', '.join(name for name, prop in card.set_properties().items()
if prop['mtgjson'])
sql += ') VALUES ('
sql += ', '.join('?' for name, prop in card.set_properties().items()
if prop['mtgjson'])
sql += ')'
values = [
date2int(s.get(database2json(name)), name)
for name, prop in card.set_properties().items() if prop['mtgjson']
]
# database.execute commits after each statement, which we want to
# avoid while inserting sets
db().execute(sql, values)
set_id = db().last_insert_rowid()
for c in s.get('cards', []):
card_id = CARD_IDS[card_name(c)]
sql = 'INSERT INTO printing (card_id, set_id, '
sql += ', '.join(name
for name, prop in card.printing_properties().items()
if prop['mtgjson'])
sql += ') VALUES (?, ?, '
sql += ', '.join('?'
for name, prop in card.printing_properties().items()
if prop['mtgjson'])
sql += ')'
values = [card_id, set_id] + [
c.get(database2json(name))
for name, prop in card.printing_properties().items()
if prop['mtgjson']
]
db().execute(sql, values)
def get_printings(generalized_card: Card) -> List[card.Printing]:
sql = 'SELECT ' + (', '.join('p.' + property for property in card.printing_properties())) + ', s.code AS set_code' \
+ ' FROM printing AS p' \
+ ' LEFT OUTER JOIN `set` AS s ON p.set_id = s.id' \
+ ' WHERE card_id = %s '
rs = db().select(sql, [generalized_card.id])
return [card.Printing(r) for r in rs]
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 get_printing(generalized_card: Card, setcode: str) -> Optional[Printing]:
if setcode is None:
return None
sql = 'SELECT ' + (', '.join('p.' + property for property in card.printing_properties())) + ', s.code AS set_code' \
+ ' FROM printing AS p' \
+ ' LEFT OUTER JOIN `set` AS s ON p.set_id = s.id' \
+ ' WHERE card_id = %s AND s.code = %s'
rs = db().select(sql, [generalized_card.id, setcode])
if rs:
return [Printing(r) for r in rs][0]
return None
def insert_set(s) -> None:
sql = 'INSERT INTO `set` ('
sql += ', '.join(name for name, prop in card.set_properties().items()
if prop['mtgjson'])
sql += ') VALUES ('
sql += ', '.join('%s' for name, prop in card.set_properties().items()
if prop['mtgjson'])
sql += ')'
values = [
date2int(s.get(database2json(name)), name)
for name, prop in card.set_properties().items() if prop['mtgjson']
]
# database.execute commits after each statement, which we want to
# avoid while inserting sets
db().execute(sql, values)
set_id = db().last_insert_rowid()
set_cards = s.get('cards', [])
fix_mtgjson_melded_cards_array(set_cards)
fix_double_faced_bug_array(set_cards)
for c in set_cards:
_, card_id = try_find_card_id(c)
if card_id is None:
raise InvalidDataException("Can't find id for: '{n}': {ns}".format(
n=c['name'], ns=c['names']))
sql = 'INSERT INTO printing (card_id, set_id, '
sql += ', '.join(name
for name, prop in card.printing_properties().items()
if prop['mtgjson'])
sql += ') VALUES (%s, %s, '
sql += ', '.join('%s'
for name, prop in card.printing_properties().items()
if prop['mtgjson'])
sql += ')'
values = [card_id, set_id] + [
c.get(database2json(name))
for name, prop in card.printing_properties().items()
if prop['mtgjson']
]
db().execute(sql, values)
def insert_set(s) -> None:
sql = 'INSERT INTO `set` ('
sql += ', '.join(name for name, prop in card.set_properties().items()
if prop['mtgjson'])
sql += ') VALUES ('
sql += ', '.join('%s' for name, prop in card.set_properties().items()
if prop['mtgjson'])
sql += ')'
values = [
date2int(s.get(database2json(name)), name)
for name, prop in card.set_properties().items() if prop['mtgjson']
]
db().execute(sql, values)
set_id = db().last_insert_rowid()
set_cards = s.get('cards', [])
fix_bad_mtgjson_set_cards_data(set_cards)
fix_mtgjson_melded_cards_array(set_cards)
for c in set_cards:
_, card_id = try_find_card_id(c)
if card_id is None:
raise InvalidDataException("Can't find id for: '{n}': {ns}".format(
n=c['name'], ns='; '.join(c.get('names', []))))
sql = 'INSERT INTO printing (card_id, set_id, '
sql += ', '.join(name
for name, prop in card.printing_properties().items()
if prop['mtgjson'])
sql += ') VALUES (%s, %s, '
sql += ', '.join('%s'
for name, prop in card.printing_properties().items()
if prop['mtgjson'])
sql += ')'
cards_values = [card_id, set_id] + [
c.get(database2json(name))
for name, prop in card.printing_properties().items()
if prop['mtgjson']
]
db().execute(sql, cards_values)
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']]
