async def save_import(import_obj: Import,
database: Database) -> Union[int, None]:
"""Create import and corresponding citizens and relations."""
async with database.transaction():
insert_import_query = imports.insert().values().returning(
imports.c.import_id)
import_id = await database.fetch_val(insert_import_query)
if import_obj:
max_citizens_per_insert = MAX_QUERY_ARGS // len(citizens.columns)
citizens_rows = make_citizens_rows(import_obj, import_id)
chunked_citizens = chunk_list(citizens_rows,
max_citizens_per_insert)
insert_citizens_query = citizens.insert()
for chunk in chunked_citizens:
await database.execute(
insert_citizens_query.values(list(chunk)))
max_relations_per_insert = MAX_QUERY_ARGS // len(relations.columns)
relations_rows = make_relations_rows(import_obj, import_id)
chunked_relations = chunk_list(relations_rows,
max_relations_per_insert)
insert_relations_query = relations.insert()
for chunk in chunked_relations:
await database.execute(
insert_relations_query.values(list(chunk)))
return import_id
async def transfer_money(database: Database, redis_pool: ConnectionPool,
user: dict, target_user: dict, value: Decimal) -> int:
context = getcontext()
context.prec = 5
async with database.transaction():
currencies_data = await get_currencies(database, redis_pool)
currencies = {
data['id']: curr
for curr, data in currencies_data.items()
}
currency_form = currencies[user['currency_id']]
currency_to = currencies[target_user['currency_id']]
query = users.update().where(users.c.id == user['id'])
balance = Decimal(user['_balance'])
sender_new_balance = balance - value
await database.execute(query, {'_balance': sender_new_balance})
value_in_target_currency = await convert(value, currency_form,
currency_to, database)
query = users.update().where(users.c.id == target_user['id'])
target_balance = Decimal(target_user['_balance'])
target_new_balance = target_balance + value_in_target_currency
await database.execute(query, {'_balance': target_new_balance})
transactions.insert().values(sender_id=user['id'],
sender_new_balance=sender_new_balance,
target_id=target_user['id'],
target_new_balance=target_new_balance,
currency_id=user['currency_id'])
return await database.execute(query, {'_balance': target_new_balance})
async def patch_citizen(import_id: int, citizen_id: int,
citizen_patch: CitizenPatch,
database: Database) -> dict:
"""Update citizen."""
async with database.transaction():
await _update_citizen(import_id, citizen_id, citizen_patch, database)
if isinstance(citizen_patch.relatives, list):
curr_relatives_query = select([relations]).where(
and_(relations.c.import_id == import_id,
relations.c.citizen == citizen_id))
current_relatives = await database.fetch_all(curr_relatives_query)
current_relatives = [r["relative"] for r in current_relatives]
relatives_to_add = [
r for r in citizen_patch.relatives
if r not in current_relatives
]
if relatives_to_add:
await _add_relatives(import_id, citizen_id, relatives_to_add,
database)
relatives_to_remove = [
r for r in current_relatives
if r not in citizen_patch.relatives
]
if relatives_to_remove:
await _remove_relatives(import_id, citizen_id,
relatives_to_remove, database)
citizen = await _get_citizen(import_id=import_id,
citizen_id=citizen_id,
database=database)
return citizen
class DbImport:
IMPORT_CLASS = {
"PrihlaskaPohledavky": PrihlaskaImporter,
"PrehledovyList": PrehledovyListImporter,
"ZpravaProOddluzeni": ZpravaProOddluzeniImporter,
"ZpravaPlneniOddluzeni": ZpravaPlneniOddluzeniImporter,
"ZpravaSplneniOddluzeni": ZpravaSplneniOddluzeniImporter,
}
def __init__(self, config, db=None):
self.config = config
if db is None:
self.db = Database(self.config['db.dsn'])
else:
self.db = db
self.metadata = {}
async def importDocument(self, doc):
try:
typ = doc["Metadata"]["Typ"]
importerCls = self.IMPORT_CLASS[typ]
except KeyError:
raise UnknownDocument()
importer = importerCls(self.db, doc)
if "isir_record" in self.metadata:
importer.addIsirRecord(self.metadata["isir_record"])
if "pdf_file_size" in self.metadata:
importer.pdf_file_size = round(self.metadata["pdf_file_size"], 3)
if "db_conn" in self.metadata:
importer.db_conn = self.metadata["db_conn"]
await importer.startImport()
async def run(self, filename):
with open(filename, 'r') as f:
fileContent = f.read()
obj = json.loads(fileContent)
# json muze obsahovat jeden nebo vice dokumentu
documents = []
if isinstance(obj, list):
documents = obj
else:
documents.append(obj)
await self.db.connect()
async with self.db.transaction():
for document in documents:
await self.importDocument(document)
await self.db.disconnect()
async def test_initial_migrations_deleted(create_command):
with cleaner("alembic_env"):
make_migrations = create_command.make_migrations()
make_migrations()
config = create_command.config
migration_assertions(config)
migrate = Commands(["app_one.conf", True, {}]).migrate()
migrate()
check = await check_table_exists(create_command.default_connection,
"orders")
assert check is True
connection = create_command.default_connection
if "mysql+pymysql" in connection:
connection = connection.replace("mysql+pymysql", "mysql")
database = Database(connection)
if "sqlite" not in connection:
query = ("DROP TABLE IF EXISTS orders, customer_a, customer_b,"
" vendor, customer_discount, vendor_discount;")
await database.connect()
await database.execute(query=query)
await database.disconnect()
else:
async with database.transaction():
await database.execute(query="DROP TABLE IF EXISTS orders;")
await database.execute(
query="DROP TABLE IF EXISTS customer_a; ")
await database.execute(
query="DROP TABLE IF EXISTS customer_b; ")
await database.execute(query="DROP TABLE IF EXISTS vendor; ")
await database.execute(
query="DROP TABLE IF EXISTS customer_discount; ")
await database.execute(
query="DROP TABLE IF EXISTS vendor_discount; ")
shutil.rmtree("app_one/migrations")
shutil.rmtree("app_two/migrations")
shutil.rmtree("app_three/migrations")
if os.path.exists("../crax/auth/migrations"):
shutil.rmtree("../crax/auth/migrations")
assert not os.path.exists("app_one/migrations")
assert not os.path.exists("app_two/migrations")
assert not os.path.exists("app_three/migrations")
make_migrations = Commands(["app_one.conf", True,
{}]).make_migrations()
make_migrations()
assert os.path.exists("app_one/migrations")
assert os.path.exists("app_two/migrations")
assert os.path.exists("app_three/migrations")
async def create_resource(db: Database, resource: "DomainResource",
fhir_version: FHIR_VERSION):
"""
https://github.com/MagicStack/asyncpg/issues/526
:param db:
:param resource:
:param fhir_version:
:return:
"""
es_id = "{0}_{1}_{2}".format(
fhir_version.name.lower(),
resource.resource_type.lower(),
resource.id.replace("-", ""),
)
resource_id = resource.id
status = "created"
resource_type = resource.resource_type
resource_version = resource.meta.versionId
resource = resource.json()
fhir_version = fhir_version.value
timestamp = datetime.utcnow()
query1 = (
f"INSERT INTO {ResourceTransactionModel.__tablename__}(es_id, resource_id, status, resource_type, "
"resource_version, resource, fhir_version, timestamp) VALUES (:es_id, :resource_id, "
":status, :resource_type, :resource_version, :resource, :fhir_version, :timestamp) RETURNING id"
)
data1 = {
"es_id": es_id,
"resource_id": resource_id,
"status": status,
"resource_type": resource_type,
"resource_version": resource_version,
"resource": resource,
"fhir_version": fhir_version,
"timestamp": timestamp,
}
async with db.transaction():
primary_key = await db.execute(query1, values=data1)
data1["id"] = primary_key
return data1
async def reassign_figmentator(
suggestion: Suggestion,
figmentator: Figmentator,
*,
db: Database,
session: ClientSession,
) -> Optional[Figmentator]:
""" Reassign the story to a new figmentator for the given suggestion """
story = await Story.select(db, where={"hash": suggestion.story_hash})
if not story:
logger.error("Story %s not found in database!", suggestion.story_hash)
return None
figmentators = [
f for f in await select_figmentators(db=db)
if f.type == suggestion.type
]
if not figmentators:
raise InsufficientCapacityError("No preprocessors available")
story.status = StoryStatus.ready
context = {"story_id": story.hash, "story": story.story}
completed, new_figmentator = await preprocess(context,
figmentators.pop(),
session=session)
async with db.transaction():
if completed:
where = {"model_id": figmentator.id, "story_hash": story.hash}
await FigmentatorForStory(**where).delete(db, where=where)
await FigmentatorForStory(model_id=new_figmentator.id,
story_hash=story.hash).insert(db)
else:
story.status = StoryStatus.failed
await story.update(db)
logger.info("Reprocessed story=%s, status=%s", story.hash, story.status)
return new_figmentator
async def add_resource_history(
db: Database, record: typing.Dict[str, typing.Any], first: bool = True
):
""" """
data = record.copy()
data["txid"] = data.pop("id")
data["prev_id"] = None
del data["es_id"]
if first is False:
previous_pk = await db.fetch_val(
(
f"SELECT id FROM {ResourceHistoryModel.__tablename__} "
"WHERE txid=:txid ORDER BY timestamp DESC LIMIT 1"
),
values={"txid": data["txid"]},
column="id",
)
if previous_pk:
data["prev_id"] = previous_pk
async with db.transaction():
query1 = (
f"INSERT INTO {ResourceHistoryModel.__tablename__}"
"(txid, resource_id, prev_id, status, resource_type, "
"resource_version, resource, fhir_version, timestamp) "
"VALUES (:txid, :resource_id, :prev_id, :status, :resource_type, "
":resource_version, :resource, :fhir_version, :timestamp) RETURNING id"
)
pk = await db.execute(query1, values=data)
if data["prev_id"]:
query2 = (
f"UPDATE {ResourceHistoryModel.__tablename__} "
"SET next_id=:next_id "
"WHERE id=:id"
)
await db.execute(query2, values={"next_id": pk, "id": data["prev_id"]})
async def migrate(db: Database,
src_dir: Path = Path(__file__).absolute().parent /
'migrations',
namespace: str = 'default'):
"""
Apply forward migration on database.
:param db: async database connection
:param src_dir: source directory with *.sql files, ordered by name (ex: 0001_abc.sql, 0002_def.sql)
:param namespace: migration namespace (useful if several projects are using same db)
"""
logger = getLogger("db-migration")
await db.execute('''
CREATE TABLE IF NOT EXISTS _migration (
name TEXT NOT NULL,
namespace TEXT NOT NULL,
PRIMARY KEY(name,namespace)
)''')
row = await db.fetch_one(
'SELECT name FROM _migration WHERE namespace = :namespace ORDER BY name DESC LIMIT 1',
values={"namespace": namespace})
for file in sorted(src_dir.glob("*.sql")):
if row is not None and file.name <= row[0]:
logger.info("skipping %s", file.name)
continue
async with db.transaction():
logger.info("applying migration from %s", file.name)
for statement in file.read_text().split(';'):
logger.info("applying: %s", statement)
await db.execute(statement)
await db.execute(
'INSERT INTO _migration(name, namespace) VALUES(:name, :namespace)',
values={
'name': file.name,
'namespace': namespace
})
logger.info("migration complete, namespace = %s", namespace)
class SQLiteDatasource(object):
create_tbl_sub_tasks = """
CREATE TABLE IF NOT EXISTS Tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
uuid VARCHAR(100) NOT NULL UNIQUE,
type VARCHAR(100) NOT NULL,
url VARCHAR(1000),
status INTEGER,
profile VARCHAR(1000),
submitted_ts VARCHAR(100)
)
"""
def __init__(self, db_path: str):
self.database = Database(db_path)
@staticmethod
def _row_to_download_task(
row: Optional[Mapping]) -> Optional[DownloadTask]:
if row is not None:
uuid = row["uuid"]
url = row["url"]
task_type = row["type"]
profile = row["profile"]
submitted_ts = row["submitted_ts"]
status = TaskStatus(row["status"])
submitted_task = SubmittedTaskModel.create(url, uuid, submitted_ts,
status, profile)
if task_type == repr(TaskType.YTDL):
return YTDLDownloadTask(submitted_task)
else:
return None
else:
return None
async def connect(self):
await self.database.connect()
async def initialize(self):
await self.database.execute(query=self.create_tbl_sub_tasks)
async def disconnect(self):
await self.database.disconnect()
async def put(self, task: DownloadTask):
task_type = repr(task.type)
query = """
INSERT INTO Tasks(uuid, type, url, profile, submitted_ts, status)
VALUES (:uuid, :type, :url, :profile, :submitted_ts, :status)
"""
params = {
"uuid": task.submitted_task.uuid,
"type": task_type,
"url": task.submitted_task.url,
"submitted_ts": task.submitted_task.submitted_ts,
"profile": task.submitted_task.profile,
"status": task.submitted_task.status
}
await self.database.execute(query, params)
async def get(self) -> Optional[DownloadTask]:
select_query = """
SELECT * FROM Tasks WHERE status = {} ORDER BY id ASC LIMIT 1
""".format(TaskStatus.Created)
update_query = """
UPDATE Tasks SET status = {} WHERE id = :id
""".format(TaskStatus.Processing)
async with self.database.transaction():
row = await self.database.fetch_one(select_query, None)
if row is not None:
param = {"id": row["id"]}
await self.database.execute(update_query, param)
task = self._row_to_download_task(row)
if task is not None:
task.submitted_task.status = TaskStatus.Processing
return task
else:
return None
async def retry_processed(self):
update_query = """
UPDATE Tasks SET status = {} WHERE status = {}
""".format(TaskStatus.Created, TaskStatus.Processing)
await self.database.execute(update_query, None)
async def retry(self, _uuid: str):
update_query = """
UPDATE Tasks SET status = {} WHERE uuid = :uuid
""".format(TaskStatus.Created)
params = {"uuid": _uuid}
await self.database.execute(update_query, params)
async def cleanup(self):
delete_query = """
DELETE FROM Tasks WHERE status IN ({}, {}, {})
""".format(TaskStatus.Done, TaskStatus.Cancelled, TaskStatus.Failed)
await self.database.execute(delete_query, None)
async def set_status(self, uuid: str, status: TaskStatus):
update_query = """
UPDATE Tasks SET status = :status WHERE uuid = :uuid
"""
params = {"status": status, "uuid": uuid}
await self.database.execute(update_query, params)
async def cancel(self, uuid: str):
update_query = """
UPDATE Tasks SET status = {} WHERE uuid = :uuid
""".format(TaskStatus.Cancelled)
params = {"uuid": uuid}
await self.database.execute(update_query, params)
async def get_by_uuid(self, uuid: str) -> Optional[DownloadTask]:
param = {"uuid": uuid}
query = """
SELECT * FROM Tasks WHERE uuid = :uuid
"""
row = await self.database.fetch_one(query, param)
return self._row_to_download_task(row)
async def _list_by(self, query) -> List[DownloadTask]:
list_rows = await self.database.fetch_all(query, None)
maybe_tasks: Iterator[Optional[DownloadTask]] = map(
self._row_to_download_task, list_rows)
return [task for task in maybe_tasks if task is not None]
async def list_all(self) -> List[DownloadTask]:
query = "SELECT * FROM Tasks"
return await self._list_by(query)
async def list_running_or_queued(self) -> List[DownloadTask]:
query = "SELECT * FROM Tasks WHERE status IN ({}, {})".format(
TaskStatus.Processing, TaskStatus.Created)
return await self._list_by(query)
async def list_running(self) -> List[DownloadTask]:
query = "SELECT * FROM Tasks WHERE status = {}".format(
TaskStatus.Processing)
return await self._list_by(query)
async def list_queued(self) -> List[DownloadTask]:
query = "SELECT * FROM Tasks WHERE status = {}".format(
TaskStatus.Created)
return await self._list_by(query)
class DB:
"""
`databases` wrapper that implements actual DB operations.
"""
def __init__(self, db_uri: DatabaseURL, *args, **kwargs):
self._db = Database(db_uri, *args, **kwargs)
def __call__(self) -> "DB":
# Hook for FastAPI dependency injection
return self
@property
def dbapi(self):
# Shortcut for accessing dialect module, e.g. for handling errors.
# DBAPI errors can be caught using `db.dbapi.Error` (or one of the more specific errors)
dbapi = type(self._db._backend._dialect).dbapi()
if hasattr(dbapi, "err"):
return dbapi.err
if hasattr(dbapi, "error"):
return dbapi.error
return dbapi
async def get_account(
self, id_or_username: Union[int, str]) -> Optional[models.Account]:
if isinstance(id_or_username, int):
query = account.select().where(account.c.id == id_or_username)
else:
query = account.select().where(
account.c.username == id_or_username)
res = await self._db.fetch_one(query)
if res is not None:
return models.Account.parse_obj(res)
return None
async def create_account(self, username: str, password: str):
async with self._db.transaction():
await self._db.execute(
account.insert(),
values={
"username": username,
"password": password
},
)
async def create_transaction(
self,
id_: str,
account_from: int,
account_to: int,
amount: int,
type_: models.TransactionType,
):
async with self._db.transaction():
if type_ is models.TransactionType.transfer:
await self._db.execute(account.update().where(
account.c.id == account_from).values(
balance=account.c.balance - amount))
await self._db.execute(account.update().where(
account.c.id == account_to).values(balance=account.c.balance +
amount))
await self._db.execute(
transaction.insert(),
values={
"id": id_,
"account_from": account_from,
"account_to": account_to,
"amount": amount,
"type": type_,
"created": datetime.utcnow().replace(microsecond=0),
},
)
async def get_transaction(
self,
transaction_id: str,
account_id: Optional[int] = None) -> Optional[models.Transaction]:
query = self._transaction_query(for_account=account_id).where(
transaction.c.id == transaction_id)
res = await self._db.fetch_one(query)
if res is not None:
return self._build_transaction_model(res)
return None
async def get_transactions(
self,
account_id: int,
dt_from: Optional[datetime] = None,
dt_to: Optional[datetime] = None,
limit: Optional[int] = None,
order: Optional[models.Ordering] = None,
) -> list[models.Transaction]:
query = self._transaction_query(for_account=account_id)
if dt_from is not None:
query = query.where(transaction.c.created >= dt_from)
if dt_to is not None:
query = query.where(transaction.c.created <= dt_to)
if limit is not None:
query = query.limit(limit)
if order is not None:
query = query.order_by(transaction.c.created.asc(
) if order is models.Ordering.asc else transaction.c.created.desc(
))
res = await self._db.fetch_all(query)
return [self._build_transaction_model(row) for row in res]
# Helpers for selecting transactions
@staticmethod
def _transaction_query(
for_account: Optional[int] = None,
) -> sqlalchemy.sql.expression.Select:
account_from_alias = account.alias()
account_to_alias = account.alias()
query = (sqlalchemy.sql.select([
transaction,
account_from_alias.c.username.label("account_from_username"),
account_to_alias.c.username.label("account_to_username"),
]).where(
transaction.c.account_from == account_from_alias.c.id, ).where(
transaction.c.account_to == account_to_alias.c.id, ))
if for_account is not None:
query = query.where((transaction.c.account_from == for_account)
| (transaction.c.account_to == for_account))
return query
@staticmethod
def _build_transaction_model(
row: sqlalchemy.engine.RowProxy, ) -> models.Transaction:
row = dict(row)
row["account_from"] = {
"id": row.pop("account_from"),
"username": row.pop("account_from_username"),
}
row["account_to"] = {
"id": row.pop("account_to"),
"username": row.pop("account_to_username"),
}
return models.Transaction.construct(**row)
