Ejemplo n.º 1
0
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)
Ejemplo n.º 2
0
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")
Ejemplo n.º 3
0
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)
Ejemplo n.º 4
0
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()
Ejemplo n.º 5
0
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()
Ejemplo n.º 6
0
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)
Ejemplo n.º 7
0
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)
Ejemplo n.º 8
0
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()
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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]