def format_where(term: str) -> str:
if term == 'pd':
term = 'Penny Dreadful'
format_id = db().value('SELECT id FROM format WHERE name LIKE %s',
['{term}%%'.format(term=card.unaccent(term))])
if format_id is None:
raise InvalidValueException(
"Invalid format '{term}'".format(term=term))
return "(c.id IN (SELECT card_id FROM card_legality WHERE format_id = {format_id} AND legality <> 'Banned'))".format(
format_id=format_id)
def text_where(column: str, term: str) -> str:
q = term
if column.endswith('name'):
q = card.unaccent(q)
if column == 'text':
column = 'oracle_text'
escaped = sqllikeescape(q)
if column == 'oracle_text' and '~' in escaped:
parts = [
"'{text}'".format(text=text)
for text in escaped.strip("'").split('~')
]
escaped = concat(intersperse(parts, 'name'))
return '({column} LIKE {q})'.format(column=column, q=escaped)
def text_where(column, term):
q = term
if column.endswith('name'):
column = column.replace('name', 'name_ascii')
q = card.unaccent(q)
if column == 'text':
column = 'search_text'
escaped = sqllikeescape(q)
if column == 'search_text' and '~' in escaped:
parts = [
"'{text}'".format(text=text)
for text in escaped.strip("'").split('~')
]
escaped = db().concat(intersperse(parts, 'name'))
return '({column} LIKE {q})'.format(column=column, q=escaped)
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)