def attempt(interval: int = 1) -> bool:
from shared import database, pd_exception
try:
database.get_database(configuration.get_str('magic_database'))
return True
except pd_exception.DatabaseConnectionRefusedException:
print(f'DB not accepting connections. Sleeping for {interval}.')
time.sleep(interval)
return False
def cache() -> None:
db = database.get_database(configuration.get_str('prices_database'))
now = round(time.time())
week = now - 60 * 60 * 24 * 7
month = now - 60 * 60 * 24 * 7 * 30
last_rotation = int(rotation.last_rotation().timestamp())
sql = 'SELECT MAX(`time`) FROM low_price'
latest = db.value(sql)
db.begin()
db.execute('DELETE FROM cache')
sql = """
INSERT INTO cache (`time`, name, price, low, high, week, month, season)
SELECT
MAX(`time`) AS `time`,
name,
MIN(CASE WHEN `time` = %s THEN price END) AS price,
MIN(CASE WHEN `time` > %s THEN price END) AS low,
MAX(CASE WHEN `time` > %s THEN price END) AS high,
AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS week,
AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS month,
AVG(CASE WHEN `time` > %s AND price = 1 THEN 1 WHEN `time` > %s THEN 0 END) AS season
FROM low_price
GROUP BY name;
"""
db.execute(sql, [latest, last_rotation, last_rotation, week, week, month, month, last_rotation, last_rotation])
db.commit()
def db():
if has_request_context():
ctx = request
else:
ctx = g
if not hasattr(ctx, 'database'):
ctx.database = get_database(configuration.get('decksite_database'))
return ctx.database
def setup():
db = getattr(setup, "db", None)
if db is not None:
db.execute("DROP TABLE IF EXISTS x")
else:
db = get_database(location())
db.execute('CREATE TABLE IF NOT EXISTS x (id INTEGER PRIMARY KEY, v TEXT)')
return db
def info_cached(card: Card = None, name: str = None) -> Optional[PriceDataType]:
if name is None and card is not None:
name = card.name
sql = 'SELECT `time`, low / 100.0 AS low, high / 100.0 AS high, price / 100.0 AS price, week, month, season FROM cache WHERE name = %s'
db = database.get_database(configuration.get_str('prices_database'))
try:
return db.select(sql, [name])[0] # type: ignore
except IndexError:
return None
def info_cached(card=None, name=None):
if name is None and card is not None:
name = card.name
sql = 'SELECT `time`, low / 100.0 AS low, high / 100.0 AS high, price / 100.0 AS price, week, month, season FROM cache WHERE name = %s'
db = database.get_database(configuration.get('prices_database'))
try:
return db.execute(sql, [name])[0]
except IndexError:
return None
def db() -> Database:
if flask.current_app:
context = flask.g
else:
context = DATABASE
if hasattr(context, 'magic_database'):
return context.get('magic_database')
context.magic_database = get_database(configuration.get_str('magic_database'))
init()
return context.get('magic_database')
def db() -> Database:
if has_request_context():
ctx = request
elif g:
ctx = g
else:
ctx = Container() # Fallback context for testing.
if not hasattr(ctx, 'database'):
ctx.database = get_database(configuration.get_str('decksite_database'))
return ctx.database
def db() -> Database:
if has_request_context(): # type: ignore
ctx = request
elif g:
ctx = g
else:
ctx = TEST_CONTEXT # Fallback context for testing.
if not hasattr(ctx, 'database'):
ctx.database = get_database(
configuration.get_str('decksite_database')) # type: ignore
return ctx.database # type: ignore
import datetime
import itertools
import sys
from typing import Dict, List, Optional
import ftfy
from magic import multiverse, oracle, rotation
from price_grabber import parser, price
from shared import configuration, dtutil, fetch_tools
from shared.database import get_database
from shared.pd_exception import DatabaseException, TooFewItemsException
DATABASE = get_database(configuration.get_str('prices_database'))
def run() -> None:
multiverse.init()
oracle.init()
fetch()
price.cache()
def fetch() -> None:
all_prices, timestamps = {}, []
ch_urls = configuration.get_list('cardhoarder_urls')
if ch_urls:
for _, url in enumerate(ch_urls):
s = fetch_tools.fetch(url)
s = ftfy.fix_encoding(s)
timestamps.append(
def db() -> Database:
if not DATABASE.get('logsite_database'):
DATABASE.logsite_database = get_database(
configuration.get_str('logsite_database'))
return DATABASE.logsite_database
default = 'DEFAULT {default}'.format(default=prop['default']) if prop['default'] is not None else ''
unique = 'UNIQUE' if prop['unique'] else ''
if prop['type'].startswith('VARCHAR') or prop['type'] == 'LONGTEXT':
prop['type'] = 'TEXT'
if prop['type'] == 'BOOLEAN':
prop['type'] = 'INTEGER'
return '`{name}` {type} {primary_key} {nullable} {unique} {default}'.format(name=name, type=prop['type'], primary_key=primary_key, nullable=nullable, unique=unique, default=default)
elif db().is_mysql():
nullable = 'NOT NULL' if not prop['nullable'] else ''
primary_key = 'PRIMARY KEY AUTO_INCREMENT' if prop['primary_key'] else ''
default = 'DEFAULT {default}'.format(default=prop['default']) if prop['default'] is not None else ''
unique = 'UNIQUE' if prop['unique'] else ''
return '`{name}` {type} {nullable} {primary_key} {unique} {default}'.format(name=name, type=prop['type'], primary_key=primary_key, nullable=nullable, unique=unique, default=default)
def foreign_key_def(name, fk):
return 'FOREIGN KEY(`{name}`) REFERENCES `{table}`(`{column}`)'.format(name=name, table=fk[0], column=fk[1])
def create_table_def(name, props):
sql = 'CREATE TABLE IF NOT EXISTS `{name}` ('
sql += ', '.join(column_def(name, prop) for name, prop in props.items())
fk = ', '.join(foreign_key_def(name, prop['foreign_key']) for name, prop in props.items() if prop['foreign_key'])
if fk:
sql += ', ' + fk
sql += ')'
if db().is_mysql():
sql += ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
return sql.format(name=name)
DATABASE = get_database(configuration.get('magic_database'))
init()
def setup():
db().execute(
'CREATE TABLE IF NOT EXISTS db_version (version INTEGER UNIQUE NOT NULL)'
)
version = db_version()
patches = os.listdir('decksite/sql')
patches.sort(key=lambda n: int(n.split('.')[0]))
for fn in patches:
path = os.path.join('decksite/sql', fn)
n = int(fn.split('.')[0])
if version < n:
print("Patching database to v{0}".format(n))
fh = open(path, 'r')
sql = fh.read()
for stmt in sql.split(';'):
if stmt.strip() != "":
db().execute(stmt)
fh.close()
db().execute(
"INSERT INTO db_version (version) VALUES ({n})".format(n=n))
def db_version() -> int:
return db().value(
'SELECT version FROM db_version ORDER BY version DESC LIMIT 1', [], 0)
DATABASE = get_database(configuration.get('decksite_database'))
setup()
