def load_svt_extra_db(
engine: Engine,
svtExtras: list[MstSvtExtra]) -> None: # pragma: no cover
svtExtra_db_data = [svtExtra.dict() for svtExtra in svtExtras]
with engine.begin() as conn:
recreate_table(conn, mstSvtExtra)
conn.execute(mstSvtExtra.insert(), svtExtra_db_data)
def migrate_db(engine: Engine) -> None:
config_path = Path(__file__).resolve().parent.parent / "alembic.ini"
alembic_cfg = Config(config_path)
with engine.begin() as connection:
# check if database has not been migrated yet with alembic
if has_tables(connection) and not has_revision(connection):
# stamp database with the initial revision id, representing pre-alembic database schema
command.stamp(alembic_cfg, "c54a916ec6c8")
command.upgrade(alembic_cfg, "head")
def load_skill_td_lv(engine: Engine,
master_folder: DirectoryPath) -> None: # pragma: no cover
with open(master_folder / "mstBuff.json", "rb") as fp:
mstBuff = {buff["id"]: buff for buff in orjson.loads(fp.read())}
with open(master_folder / "mstFunc.json", "rb") as fp:
mstFunc = {func["id"]: func for func in orjson.loads(fp.read())}
mstFuncGroup = defaultdict(list)
with open(master_folder / "mstFuncGroup.json", "rb") as fp:
for funcGroup in orjson.loads(fp.read()):
mstFuncGroup[funcGroup["funcId"]].append(funcGroup)
with open(master_folder / "mstSkillLv.json", "rb") as fp:
mstSkillLv_data = orjson.loads(fp.read())
with open(master_folder / "mstTreasureDeviceLv.json", "rb") as fp:
mstTreasureDeviceLv_data = orjson.loads(fp.read())
def get_func_entity(func_id: int) -> dict[Any, Any]:
func_entity = {
"mstFunc": mstFunc[func_id],
"mstFuncGroup": mstFuncGroup.get(func_id, []),
}
if (func_entity["mstFunc"]["funcType"] not in FUNC_VALS_NOT_BUFF
and func_entity["mstFunc"]["vals"]
and func_entity["mstFunc"]["vals"][0] in mstBuff):
func_entity["mstFunc"]["expandedVals"] = [{
"mstBuff":
mstBuff[func_entity["mstFunc"]["vals"][0]]
}]
else:
func_entity["mstFunc"]["expandedVals"] = []
return func_entity
for skillLv in mstSkillLv_data:
skillLv["expandedFuncId"] = [
get_func_entity(func_id) for func_id in skillLv["funcId"]
if func_id in mstFunc
]
for treasureDeviceLv in mstTreasureDeviceLv_data:
treasureDeviceLv["expandedFuncId"] = [
get_func_entity(func_id) for func_id in treasureDeviceLv["funcId"]
if func_id in mstFunc
]
with engine.begin() as conn:
recreate_table(conn, mstSkillLv)
conn.execute(mstSkillLv.insert(), mstSkillLv_data)
recreate_table(conn, mstTreasureDeviceLv)
conn.execute(mstTreasureDeviceLv.insert(), mstTreasureDeviceLv_data)
def run_alembic_command(engine: Engine, command: str, command_kwargs: Dict[str, Any]) -> str:
command_func = ALEMBIC_COMMAND_MAP[command]
stdout = StringIO()
alembic_cfg = build_alembic_config(engine)
with engine.begin() as connection:
alembic_cfg.attributes["connection"] = connection
with contextlib.redirect_stdout(stdout):
command_func(alembic_cfg, **command_kwargs)
return stdout.getvalue()
def change_functions_ownership(engine: Engine, database: str,
target_role: str):
stmt = f"SHOW USER FUNCTIONS IN DATABASE {database}"
with engine.begin() as tx:
for object in [dict(x) for x in tx.execute(stmt).fetchall()]:
func_handle = object["arguments"].split(" RETURN ")[0]
schema = object["schema_name"]
tx.execute(
f"GRANT OWNERSHIP ON FUNCTION {database}.{schema}.{func_handle} "
f"TO ROLE {target_role} REVOKE CURRENT GRANTS").fetchall()
def load_script_list(engine: Engine,
repo_folder: DirectoryPath) -> None: # pragma: no cover
script_list_file = (repo_folder / "ScriptActionEncrypt" /
ScriptFileList.name / f"{ScriptFileList.name}.txt")
db_data: list[dict[str, Union[int, str, None]]] = []
if script_list_file.exists():
with open(script_list_file, encoding="utf-8") as fp:
script_list = [line.strip() for line in fp.readlines()]
with open(repo_folder / "master" / "mstQuest.json", "rb") as bfp:
mstQuest = orjson.loads(bfp.read())
questId = {quest["id"] for quest in mstQuest}
scriptQuestId = {
quest["scriptQuestId"]: quest["id"]
for quest in mstQuest if quest["scriptQuestId"] != 0
}
for script in script_list:
script_name = script.removesuffix(".txt")
quest_ids: list[Optional[int]] = []
phase: Optional[int] = None
sceneType: Optional[int] = None
if len(script) == 14 and script[0] in ("0", "9"):
script_int = int(script_name[:-2])
sceneType = int(script_name[-1])
phase = int(script_name[-2])
if script_int in scriptQuestId:
quest_ids.append(scriptQuestId[script_int])
if script_int in questId:
quest_ids.append(script_int)
if not quest_ids:
quest_ids.append(None)
for quest_id in quest_ids:
db_data.append({
"scriptFileName": script_name,
"questId": quest_id,
"phase": phase,
"sceneType": sceneType,
})
with engine.begin() as conn:
recreate_table(conn, ScriptFileList)
conn.execute(ScriptFileList.insert(), db_data)
def aggregate_report(bookings_table: MappedTable, report_table: MappedTable,
engine: Engine):
"""
Aggregate bookings table into a monthly report, and replace report table content with it.
Args:
bookings_table: source bookings table mapper
report_table: destination report table mapper
engine: sqlalchemy engine
Notes:
Make the operation transactional.
"""
with engine.begin() as connection:
# Truncate already-existing report table
_run_truncate_query(report_table, connection)
# Recreate report
_run_aggregate_report_query(bookings_table, report_table, connection)
def change_objects_ownership(engine: Engine, database: str,
target_role: str) -> None:
stmt = Select(
[
literal_column("table_type"),
literal_column("table_schema"),
literal_column("table_name"),
],
from_obj=text(f"{database}.INFORMATION_SCHEMA.TABLES"),
whereclause=literal_column("table_owner") == "DBT_PRODUCTION",
)
with engine.begin() as tx:
rp = tx.execute(stmt)
objects = [(
"TABLE" if object_type == "BASE TABLE" else object_type,
schema,
object_name,
) for object_type, schema, object_name in rp.fetchall()]
for object_type, schema, object_name in objects:
tx.execute(
f"GRANT OWNERSHIP ON {object_type} {database}.{schema}.{object_name} TO ROLE {target_role} REVOKE CURRENT GRANTS"
).fetchall()
def get_current_head(connectable: engine.Engine) -> set:
with connectable.begin() as connection:
context = migration.MigrationContext.configure(connection)
current_head = set(context.get_current_heads())
return current_head
def fetch_user_stickers(db_pool: engine.Engine) -> List[str]:
with db_pool.begin() as conn:
old_image_ids = conn.execute(user_image_query).fetchall()
return [str(row).split("'")[1] for row in old_image_ids]
