def init_value_lookup():
sql = """SELECT
id,
LOWER(name) AS name,
LOWER(SUBSTR(name, 1, 1)) AS initial,
LOWER(SUBSTR(TRIM(name), 1, INSTR({nameandspace}, ' ') - 1)) AS first_word,
LOWER(REPLACE(name, ' ', '')) AS spaceless,
LOWER({initials}) AS initials
FROM {table}"""
nameandspace = db().concat(['TRIM(name)', "' '"])
second_initial = """CASE WHEN INSTR(name, ' ') > 0 THEN
SUBSTR(name, INSTR(name, ' ') + 1, 1)
ELSE
''
END"""
initials = db().concat(['SUBSTR(name, 1, 1)', second_initial])
for table in ['color', 'rarity']:
rs = db().execute(
sql.format(nameandspace=nameandspace,
initials=initials,
table=table))
d = {}
for row in rs:
d[row['name']] = row['id']
d[row['first_word']] = row['id']
d[row['spaceless']] = row['id']
# Special case because 'b' is black and 'u' is blue in colors.
if table != 'color' or row['name'] != 'blue':
d[row['initial']] = row['id']
d[row['initials']] = row['id']
else:
d['u'] = row['id']
VALUE_LOOKUP[table] = d
if table == 'color':
VALUE_LOOKUP['color_identity'] = d
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 add_to_cache(ids: List[int]) -> None:
if not ids:
return
values = ', '.join([str(id) for id in ids])
query = base_query(f'c.id IN ({values})')
sql = f'INSERT INTO _cache_card {query}'
db().execute(sql)
def test_base_query_legalities() -> None:
sql = multiverse.base_query("f.name = 'Mother of Runes'")
db().execute('SET group_concat_max_len=100000')
rs = db().select(sql)
assert len(rs) == 1
legalities = rs[0]['legalities']
assert 'Penny Dreadful EMN:Legal' in legalities
assert 'Penny Dreadful AKH:Legal' not in legalities
def update_bugged_cards() -> None:
bugs = fetcher.bugged_cards()
if bugs is None:
return
db().begin('update_bugged_cards')
db().execute('DELETE FROM card_bug')
for bug in bugs:
last_confirmed_ts = dtutil.parse_to_ts(bug['last_updated'],
'%Y-%m-%d %H:%M:%S',
dtutil.UTC_TZ)
name = bug['card'].split(
' // '
)[0] # We need a face name from split cards - we don't have combined card names yet.
card_id = db().value('SELECT card_id FROM face WHERE name = %s',
[name])
if card_id is None:
print('UNKNOWN BUGGED CARD: {card}'.format(card=bug['card']))
continue
db().execute(
'INSERT INTO card_bug (card_id, description, classification, last_confirmed, url, from_bug_blog, bannable) VALUES (%s, %s, %s, %s, %s, %s, %s)',
[
card_id, bug['description'], bug['category'],
last_confirmed_ts, bug['url'], bug['bug_blog'], bug['bannable']
])
db().commit('update_bugged_cards')
def get_format_id(name, allow_create=False):
if len(FORMAT_IDS) == 0:
rs = db().execute('SELECT id, name FROM format')
for row in rs:
FORMAT_IDS[row['name']] = row['id']
if name not in FORMAT_IDS.keys() and allow_create:
db().execute('INSERT INTO format (name) VALUES (?)', [name])
FORMAT_IDS[name] = db().last_insert_rowid()
if name not in FORMAT_IDS.keys():
return None
return FORMAT_IDS[name]
def get_format_id(name: str, allow_create: bool = False) -> int:
if len(FORMAT_IDS) == 0:
rs = db().select('SELECT id, name FROM format')
for row in rs:
FORMAT_IDS[row['name']] = row['id']
if name not in FORMAT_IDS.keys() and allow_create:
db().execute('INSERT INTO format (name) VALUES (%s)', [name])
FORMAT_IDS[name] = db().last_insert_rowid()
if name not in FORMAT_IDS.keys():
raise InvalidArgumentException(
'Unknown format: {name}'.format(name=name))
return FORMAT_IDS[name]
def insert_set(s: Any) -> int:
sql = 'INSERT INTO `set` ('
sql += ', '.join(name for name, prop in card.set_properties().items()
if prop['scryfall'])
sql += ') VALUES ('
sql += ', '.join('%s' for name, prop in card.set_properties().items()
if prop['scryfall'])
sql += ')'
values = [
date2int(s.get(database2json(name)), name)
for name, prop in card.set_properties().items() if prop['scryfall']
]
db().execute(sql, values)
return db().last_insert_rowid()
def insert_many(table: str,
properties: TableDescription,
values: List[Dict[str, Any]],
additional_columns: Optional[List[str]] = None) -> None:
columns = additional_columns or []
columns += [k for k, v in properties.items() if v.get('foreign_key')]
columns += [name for name, prop in properties.items() if prop['scryfall']]
query = f'INSERT INTO `{table}` ('
query += ', '.join(columns)
query += ') VALUES ('
query += '), ('.join(', '.join(
str(sqlescape(entry[column])) for column in columns)
for entry in values)
query += ')'
db().execute(query)
def check_layouts():
rs = db().execute('SELECT DISTINCT layout FROM card')
if sorted([row['layout'] for row in rs]) != sorted(layouts()):
print(
'WARNING. There has been a change in layouts. The update to 0 CMC may no longer be valid. Comparing {old} with {new}.'
.format(old=sorted(layouts()),
new=sorted([row['layout'] for row in rs])))
def search(query: str) -> List[Card]:
where = parse(tokenize(query))
sql = """{base_query}
ORDER BY pd_legal DESC, name
""".format(base_query=multiverse.cached_base_query(where))
rs = db().select(sql)
return [Card(r) for r in rs]
def search(query):
where = parse(tokenize(query))
sql = """{base_query}
ORDER BY pd_legal DESC, name
""".format(base_query=multiverse.cached_base_query(where))
rs = db().execute(sql)
return [card.Card(r) for r in rs]
def if_todays_prices(out=True):
current_format = multiverse.get_format_id("Penny Dreadful")
if out:
not_clause = ''
compare = '<'
else:
not_clause = 'NOT'
compare = '>='
where = '''
c.id {not_clause} IN
(SELECT card_id FROM card_legality
WHERE format_id = {format})
AND c.name in (SELECT name from prices.cache where week {compare} 0.5)
AND c.layout IN ({layouts})
'''.format(not_clause=not_clause,
format=current_format,
compare=compare,
layouts=', '.join([
sqlescape(k) for k, v in multiverse.layouts().items() if v
]))
rs = db().execute(multiverse.cached_base_query(where=where))
out = [card.Card(r) for r in rs]
return sorted(out, key=lambda card: card['name'])
def if_todays_prices(out: bool = True) -> List[Card]:
current_format = multiverse.get_format_id('Penny Dreadful')
if out:
not_clause = ''
compare = '<'
else:
not_clause = 'NOT'
compare = '>='
where = """
c.id {not_clause} IN
(SELECT card_id FROM card_legality
WHERE format_id = {format})
AND c.name in (SELECT name FROM `{prices_database}`.cache WHERE week {compare} 0.5)
AND c.layout IN ({layouts})
""".format(not_clause=not_clause,
format=current_format,
prices_database=configuration.get('prices_database'),
compare=compare,
layouts=', '.join([
sqlescape(layout)
for layout in multiverse.playable_layouts()
]))
rs = db().select(multiverse.cached_base_query(where=where))
cards = [Card(r) for r in rs]
return sorted(cards, key=lambda card: card['name'])
def check_layouts() -> None:
rs = db().select('SELECT DISTINCT layout FROM card')
if sorted([row['layout'] for row in rs]) != sorted(layouts().keys()):
print(
'WARNING. There has been a change in layouts. The update to 0 CMC may no longer be valid. You may also want to add it to playable_layouts. Comparing {old} with {new}.'
.format(old=sorted(layouts().keys()),
new=sorted([row['layout'] for row in rs])))
def test_base_query_legalities() -> None:
sql = multiverse.base_query("f.name = 'Mother of Runes'")
rs = db().execute(sql)
assert len(rs) == 1
legalities = rs[0]['legalities']
assert 'Penny Dreadful EMN:Legal' in legalities
assert 'Penny Dreadful AKH:Legal' not in legalities
def test_uppercase():
pd_id = db().value('SELECT id FROM format WHERE name LIKE ?',
['{term}%%'.format(term='Penny Dreadful')])
do_test(
'F:pd',
"(c.id IN (SELECT card_id FROM card_legality WHERE format_id = {pd_id} AND legality <> 'Banned'))"
.format(pd_id=pd_id))
def init() -> None:
if FORMATS:
return
print('Updating Legalities…')
oracle.legal_cards()
FORMATS.clear()
for v in db().values('SELECT name FROM format'):
FORMATS.add(v)
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 search(query):
like_query = '%{query}%'.format(query=card.canonicalize(query))
sql = """
{base_query}
HAVING name_ascii LIKE %s OR names LIKE %s
ORDER BY pd_legal DESC, name
""".format(base_query=multiverse.base_query())
rs = db().execute(sql, [like_query, like_query])
return [card.Card(r) for r in rs]
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 search(query, fuzzy_threshold=260):
query = card.canonicalize(query)
like_query = '%{query}%'.format(query=query)
if db().is_mysql():
having = 'name_ascii LIKE ? OR names LIKE ?'
args = [like_query, like_query]
else:
having = """LOWER({name_query}) IN (SELECT word FROM fuzzy WHERE word MATCH ? AND distance <= {fuzzy_threshold})
OR {name_ascii_query} LIKE ?
OR SUM(CASE WHEN LOWER(face_name) IN (SELECT word FROM fuzzy WHERE word MATCH ? AND distance <= {fuzzy_threshold}) THEN 1 ELSE 0 END) > 0
""".format(name_query=card.name_query().format(table='u'), name_ascii_query=card.name_query('name_ascii').format(table='u'), fuzzy_threshold=fuzzy_threshold)
fuzzy_query = '{query}*'.format(query=query)
args = [fuzzy_query, like_query, fuzzy_query]
sql = """
{base_query}
HAVING {having}
ORDER BY pd_legal DESC, name
""".format(base_query=base_query(), having=having)
rs = db().execute(sql, args)
return [card.Card(r) for r in rs]
def set_legal_cards(force=False, season=None):
new_list = ['']
try:
new_list = fetcher.legal_cards(force, season)
except fetcher.FetchException:
pass
if season is None:
format_id = get_format_id('Penny Dreadful')
else:
format_id = get_format_id(
'Penny Dreadful {season}'.format(season=season), True)
if new_list == [''] or new_list is None:
return None
db().execute('DELETE FROM card_legality WHERE format_id = %s', [format_id])
sql = """INSERT INTO card_legality (format_id, card_id, legality)
SELECT {format_id}, bq.id, 'Legal'
FROM ({base_query}) AS bq
WHERE name IN ({names})
""".format(format_id=format_id,
base_query=base_query(),
names=', '.join(sqlescape(name) for name in new_list))
db().execute(sql)
# Check we got the right number of legal cards.
n = db().value('SELECT COUNT(*) FROM card_legality WHERE format_id = %s',
[format_id])
if n != len(new_list):
print(
"Found {n} pd legal cards in the database but the list was {len} long"
.format(n=n, len=len(new_list)))
sql = 'SELECT bq.name FROM ({base_query}) AS bq WHERE bq.id IN (SELECT card_id FROM card_legality WHERE format_id = {format_id})'.format(
base_query=base_query(), format_id=format_id)
db_legal_list = [row['name'] for row in db().execute(sql)]
print(set(new_list).symmetric_difference(set(db_legal_list)))
return new_list
def init() -> None:
if FORMATS:
return
print('Updating Legalities…')
assert len(oracle.legal_cards()) > 0
all_known = oracle.load_card('island').legalities
# assert 'Penny Dreadful EMN' in all_known
assert 'Penny Dreadful' in all_known
assert 'Vintage' in all_known
FORMATS.clear()
for v in db().values('SELECT name FROM format'):
FORMATS.add(v)