unis-oa/backend/scripts/migrate_design.py

1042 lines
50 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

#!/usr/bin/env python3
"""把某个档位(默认生产)的表单与流程设计,原样搬到另一个档位(默认本机测试库)。
连哪两个库由 ``config.<档位>.toml`` 的 ``[database]`` 说了算,跟后端启动读的是同一批文件,
所以"生产""测试"在这里不用再抄一遍地址:``--from prod --to dev`` 就是默认档。
**源库只读是硬保证**,不靠自觉:连上就先 ``SET SESSION TRANSACTION READ ONLY``,
这条会话上任何写语句都会被 MySQL 顶回来(错误 1792),脚本本身也只发 SELECT。
目标库一个事务写完,任何一步报错整体回滚,写之前先把要动的行整包备份成 gzip JSON。
搬的是设计数据,分两层:
* 核心:流程 ``oa_process``、环节、路由、发布历史 ``oa_process_version``、流程脚本、
表单 ``oa_form``、分类 ``oa_category``;
* 依赖(``--no-deps`` 关掉):表单与环节按业务键点名的东西 —— 数据字典
(``oa_dict_type`` / ``oa_dict_item``)、外部取数 ``oa_data_source``、回写模板
``oa_integration``。只搬被引用到的那几项,不整库倒;``--all-deps`` 换成整表搬,
源库这三类有什么就搬什么,不看有没有被设计点名(只想补齐依赖时用它的干跑最直观)。
按什么对齐:跨环境的业务键。流程 / 表单 / 分类认主键 id(同一套设计长出来的两个库,
id 本来就一样),字典、取数、回写认 ``code``。目标库已有同一行的整行覆盖(覆盖前备份),
没有的新增;**目标库多出来的一律不删** —— 这是搬迁不是清理,测试环境自己加的流程照留。
环节 / 路由 / 版本 / 脚本按流程整组替换:生产删掉的环节,测试这边也跟着没有。
组织与人不搬:审批人存的是角色名 / 部门名 / 登录账号(见 :mod:`app.service.personref`),
两边各自从统一身份同步。写完把这套引用逐个点名对一遍,缺谁如实报出来(比如测试库少一个
角色),不悄悄留一个配了却没人能审的环节。
python3 scripts/migrate_design.py # 干跑:看要搬什么、会盖什么
python3 scripts/migrate_design.py --apply # 真搬(生产 → 测试)
python3 scripts/migrate_design.py --process 印章 费用 # 只搬名字带这些词的流程
python3 scripts/migrate_design.py --new-only # 测试库已有的不动,只补缺
python3 scripts/migrate_design.py --no-deps # 不带字典 / 取数 / 回写
python3 scripts/migrate_design.py --all-deps # 字典 / 取数 / 回写整表搬
python3 scripts/migrate_design.py --restore ../backups/design-migrate-XXXX.json.gz
"""
from __future__ import annotations
import argparse
import gzip
import json
import re
import sys
import tomllib
from datetime import date, datetime
from pathlib import Path
from typing import Any
from urllib.parse import quote
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from sqlalchemy import create_engine, delete, event, insert, select, text # noqa: E402
from sqlalchemy.engine import Engine # noqa: E402
from sqlalchemy.orm import sessionmaker # noqa: E402
from app import models # noqa: E402
BACKEND = Path(__file__).resolve().parents[1]
BACKUP_DIR = BACKEND.parent / "backups"
TABLES = models.Base.metadata.tables
#: 32 位十六进制:本平台所有设计主键的形状(:func:`app.models.new_id`)
ID_RE = re.compile(r"\b[0-9a-f]{32}\b")
#: 按流程整组替换的子表(生产没有的环节 / 路由,测试这边也不该留)
CHILD_TABLES = ("oa_activity", "oa_route", "oa_process_version", "oa_script")
#: 哪些列里藏着别的主键,id 对不上时要跟着改写
REF_COLUMNS: dict[str, tuple[str, ...]] = {
"oa_category": (),
"oa_form": ("content",),
"oa_process": ("latest_form", "category", "quick_forms", "data_template", "canvas",
"approval_config", "title_config", "serial_config", "open_approve",
"terminate_config"),
"oa_activity": ("form", "config"),
"oa_route": (),
"oa_process_version": ("snapshot",),
"oa_script": (),
}
#: 覆盖时值得单独提一句的列:这些不一样,说明测试库有自己的改动,别默默盖掉
NOTABLE = ("name", "alias", "version", "enabled", "published", "latest_form", "order_number")
#: 只认业务键、**已存在就一律不盖**的表。审批角色是本部署自己配的"谁顶这个岗",
#: 测试环境往往故意把它指给 localadmin 这类测试账号,拿生产的真员工盖下去,
#: 测试环境的审批链当场就派不到人了。缺的角色补上,成员名单各环境自己说了算。
INSERT_ONLY = frozenset({"oa_role"})
#: 跟着本部署走、不从源库搬的列:同步型字典的这两列记的是"这台机器上一次同步的结果"。
#: 新增行时按源库那份写(选项正是从那儿抄来的,如实),已存在的行保留本机那份,
#: 否则生产每同步一次,这边就永远有个搬不掉的假差异。
KEEP_LOCAL_ON_UPDATE: dict[str, tuple[str, ...]] = {
"oa_dict_type": ("synced_at", "synced_count", "sync_status", "sync_message"),
}
# ---------------------------------------------------------------------------
# 连库与读行
# ---------------------------------------------------------------------------
def db_conf(profile: str) -> dict[str, Any]:
"""读 ``config.<档位>.toml`` 的 ``[database]``——这一节只可能写在分档文件里。"""
path = BACKEND / f"config.{profile}.toml"
if not path.exists():
raise SystemExit(f"找不到 {path},--from / --to 只能写有配置文件的档位名")
conf = tomllib.loads(path.read_text()).get("database") or {}
missing = [key for key in ("host", "port", "user", "password", "name") if key not in conf]
if missing:
raise SystemExit(f"{path.name} 的 [database] 少了 {'、'.join(missing)}")
return conf
def dsn(conf: dict[str, Any]) -> str:
"""口令带 ``@`` 必须转义,否则 SQLAlchemy 把主机名切成 ``123@10.100.51.227``。"""
return (
f"mysql+pymysql://{quote(str(conf['user']))}:{quote(str(conf['password']))}"
f"@{conf['host']}:{conf['port']}/{conf['name']}?charset=utf8mb4"
)
def label(conf: dict[str, Any]) -> str:
return f"{conf['name']}@{conf['host']}:{conf['port']}"
def connect(conf: dict[str, Any], read_only: bool = False) -> Engine:
"""连一条库。``read_only`` 的那条会话上任何写语句都会被服务端顶回来。"""
engine = create_engine(dsn(conf), pool_pre_ping=True, pool_recycle=600)
if read_only:
@event.listens_for(engine, "connect")
def _readonly(dbapi_conn: Any, _record: Any) -> None: # noqa: ANN001 - DBAPI 裸连接
with dbapi_conn.cursor() as cursor:
cursor.execute("SET SESSION TRANSACTION READ ONLY")
return engine
def probe(engine: Engine, conf: dict[str, Any]) -> None:
"""连不上就当场说,别等写了一半才炸。"""
try:
with engine.connect() as conn:
conn.execute(text("select 1"))
except Exception as exc: # noqa: BLE001 - 原样给运维看
raise SystemExit(f"连不上 {label(conf)}:{exc}") from exc
def fetch(engine: Engine, table_name: str, column: str = "",
values: list[Any] | None = None) -> list[dict]:
"""读一张表,可按某一列的值筛。分片 IN,不把 SQL 拼得比数据还长。"""
table = TABLES[table_name]
order = [table.c[name] for name in table.primary_key.columns.keys()]
out: list[dict] = []
with engine.connect() as conn:
if column:
for start in range(0, len(values or []), 500):
part = (values or [])[start:start + 500]
stmt = select(table).where(table.c[column].in_(part)).order_by(*order)
out.extend(dict(row._mapping) for row in conn.execute(stmt))
return out
out.extend(dict(row._mapping) for row in conn.execute(select(table).order_by(*order)))
return out
def by_key(rows: list[dict], column: str) -> dict[Any, dict]:
"""``{某列的值: 行}``。同值多行留第一行(业务键本来就该唯一)。"""
out: dict[Any, dict] = {}
for row in rows:
out.setdefault(row.get(column), row)
return out
def blob(row: dict, columns: tuple[str, ...]) -> str:
"""把一行里几列的内容拼成一段文本,捞引用用。"""
parts: list[str] = []
for column in columns:
value = row.get(column)
if value in (None, "", {}, []):
continue
parts.append(value if isinstance(value, str) else json.dumps(value, ensure_ascii=False))
return "\n".join(parts)
def walk(value: Any):
"""深度遍历 JSON 里每一个 dict 节点。"""
if isinstance(value, dict):
yield value
for item in value.values():
yield from walk(item)
elif isinstance(value, list):
for item in value:
yield from walk(item)
# ---------------------------------------------------------------------------
# 备份与还原
# ---------------------------------------------------------------------------
def jsonable(value: object) -> object:
return value.isoformat() if isinstance(value, (datetime, date)) else value
def unjsonable(column: Any, value: Any) -> Any:
"""备份里时间是 ISO 串,插回去之前按列类型换回来。"""
if value is None:
return None
if column.type.__class__.__name__ in ("DateTime", "Timestamp") and isinstance(value, str):
return datetime.fromisoformat(value)
return value
def encode_rows(rows: list[dict]) -> list[dict]:
return [{key: jsonable(value) for key, value in row.items()} for row in rows]
def decode_rows(table_name: str, rows: list[dict]) -> list[dict]:
table = TABLES[table_name]
return [{key: unjsonable(table.c[key], value) for key, value in row.items()} for row in rows]
def write_backup(path: Path, payload: dict[str, Any]) -> None:
path.parent.mkdir(parents=True, exist_ok=True)
with gzip.open(path, "wt", encoding="utf-8") as handle:
json.dump(payload, handle, ensure_ascii=False)
def inserted_keys(table_name: str, rows: list[dict]) -> list[dict]:
"""备份里记一笔"这次插了哪些行":主键,外加名字与所属流程,供局部还原认人。"""
out: list[dict] = []
for row in rows:
item = {"id": str(row["id"])}
for column in ("name", "process"):
if row.get(column):
item[column] = str(row[column])
out.append(item)
return out
def belongs(table_name: str, item: dict, pids: set[str], keywords: list[str]) -> bool:
"""这一行属不属于"要局部还原的那几条流程"。环节 / 路由看归属,流程与表单看名字。"""
if item.get("process") and item["process"] in pids:
return True
if table_name == "oa_process" and item.get("id") in pids:
return True
if table_name == "oa_form":
return any(word in str(item.get("name") or "") for word in keywords)
return False
def restore(path: Path, profile: str, keywords: list[str] | None = None) -> int:
"""撤销**这一次**搬迁:这趟新插的行删掉,被这趟改掉 / 删掉的行按备份塞回去。
只认手上这一个备份,认不了"搬了好几趟":连着搬三次要全退回去,就得从最新那个
备份往回一个个还原。
给了 ``--process`` 就只还原名字命中这些词的流程(连带它的环节 / 路由 / 版本 / 脚本
与同名表单),其余保持搬迁后的样子 —— 只想捞回某一条自己改过的流程时,
不用把整批一起退掉。
"""
with gzip.open(path, "rt", encoding="utf-8") as handle:
payload = json.load(handle)
before = payload.get("before", {})
inserted = payload.get("inserted", {})
if keywords:
pids = {row["id"] for row in before.get("oa_process", [])
if any(word in str(row.get("name") or "") for word in keywords)}
pids |= {item["id"] for item in inserted.get("oa_process", [])
if any(word in str(item.get("name") or "") for word in keywords)}
if not pids:
raise SystemExit(f"备份里没有名字命中 {'、'.join(keywords)} 的流程,什么都没动")
before = {name: [row for row in rows if belongs(name, row, pids, keywords)]
for name, rows in before.items()}
inserted = {name: [item for item in items if belongs(name, item, pids, keywords)]
for name, items in inserted.items()}
before = {name: rows for name, rows in before.items() if rows}
inserted = {name: items for name, items in inserted.items() if items}
print(f"局部还原:{len(pids)} 条流程,涉及 "
f"{', '.join(sorted(set(before) | set(inserted))) or '没有要动的行'}")
conf = db_conf(profile)
engine = connect(conf)
probe(engine, conf)
session = sessionmaker(bind=engine)()
try:
victims: dict[str, set[str]] = {}
for table_name, keys in inserted.items():
victims.setdefault(table_name, set()).update(str(item["id"]) for item in keys)
for table_name, rows in before.items():
pk = TABLES[table_name].primary_key.columns.keys()[0]
victims.setdefault(table_name, set()).update(str(row[pk]) for row in rows)
# 删按子表在前、插按主表在前:备份 JSON 里的表序是"先删后插"攒出来的,
# 照着它插会拿环节去认一个还没塞回来的流程,撞上外键(ON DELETE CASCADE 同理)。
sequence = [*ORDER, *(name for name in before if name not in ORDER)]
for table_name in reversed(sequence):
keys = victims.get(table_name)
if not keys:
continue
table = TABLES[table_name]
pk = table.primary_key.columns.keys()[0]
session.execute(delete(table).where(table.c[pk].in_(list(keys))))
session.flush()
for table_name in sequence:
rows = before.get(table_name)
if rows:
session.execute(insert(TABLES[table_name]), decode_rows(table_name, rows))
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()
total = sum(len(rows) for rows in before.values())
print(f"已按 {path.name} 还原 {label(conf)}:塞回原行 {total} 行")
return total
# ---------------------------------------------------------------------------
# 圈定范围
# ---------------------------------------------------------------------------
class Scope:
"""这一趟要搬什么:流程、表单、分类的行,加上依赖的业务键。"""
def __init__(self) -> None:
self.processes: list[dict] = []
self.forms: list[dict] = []
self.categories: list[dict] = []
self.activities: list[dict] = []
self.routes: list[dict] = []
self.dict_codes: list[str] = []
self.data_source_codes: list[str] = []
self.integration_codes: list[str] = []
self.role_names: list[str] = []
@property
def process_ids(self) -> list[str]:
return [row["id"] for row in self.processes]
def design_refs(scope: Scope) -> dict[str, set[str]]:
"""从设计正文里把**按名字点名**的东西挑出来。
审批人、抄送、外部审批与回写认的都是名字(角色名 / 部门名 / 登录账号 / 应用码 /
回写模板码,见 :mod:`app.service.personref`),不是主键 —— 这既是跨环境能搬的原因,
也是搬过去可能点名点不到的原因,所以圈范围和落地后点名都要靠这一份。
"""
out: dict[str, set[str]] = {kind: set() for kind in
("roles", "units", "persons", "apps", "integrations")}
buckets = {"role": "roles", "person": "persons", "unitManager": "units", "unit": "units"}
for row in scope.activities + scope.processes:
for config in (row.get("config"), row.get("terminate_config"), row.get("open_approve")):
for node in walk(config or {}):
kind = buckets.get(str(node.get("type")))
if kind and isinstance(node.get("data"), list):
out[kind].update(str(item) for item in node["data"] if item)
for key in ("apps", "integrations", "notifyRoles", "notifyUnits"):
value = node.get(key)
if not isinstance(value, list):
continue
bucket = {"apps": "apps", "integrations": "integrations",
"notifyRoles": "roles", "notifyUnits": "units"}[key]
out[bucket].update(str(item) for item in value if item)
return out
def collect_scope(src: Engine, keywords: list[str], follow: bool, with_deps: bool,
all_deps: bool = False) -> tuple[Scope, list[str]]:
"""从流程表出发顺着引用把范围圈满。
``all_deps`` 只影响依赖那一层:字典 / 取数 / 回写改成整表搬,不再要求被设计点名。
一条流程带出:它绑的表单、各环节绑的表单、所属分类,以及**设计正文里点到的别的流程**
(``relatedWork`` 这类字段按流程 id 过滤可选单据,不跟过去就是一条断链)。
"""
process_index = by_key(fetch(src, "oa_process"), "id")
form_index = by_key(fetch(src, "oa_form"), "id")
category_index = by_key(fetch(src, "oa_category"), "id")
activities = fetch(src, "oa_activity")
routes = fetch(src, "oa_route")
notes: list[str] = []
for word in keywords:
if not any(word in str(row["name"]) for row in process_index.values()):
notes.append(f"--process「{word}」在源库里没圈到任何流程")
queue = [row["id"] for row in process_index.values()
if any(word in str(row["name"]) for word in keywords)] if keywords else list(process_index)
seen: set[str] = set()
picked: list[str] = []
cursor = 0
while cursor < len(queue):
pid = queue[cursor]
cursor += 1
if pid in seen or pid not in process_index:
continue
seen.add(pid)
picked.append(pid)
if not follow:
continue
text = blob(process_index[pid], ("data_template", "quick_forms", "title_config",
"serial_config"))
text += "\n" + "\n".join(
blob(row, ("config",)) for row in activities if row["process"] == pid
)
for hit in ID_RE.findall(text):
if hit in process_index and hit not in seen:
queue.append(hit)
notes.append(f"顺引用带进来:{process_index[pid]['name']} → {process_index[hit]['name']}")
scope = Scope()
scope.processes = [process_index[pid] for pid in picked]
in_scope = set(picked)
scope.activities = [row for row in activities if row["process"] in in_scope]
scope.routes = [row for row in routes if row["process"] in in_scope]
form_ids: list[str] = []
def want_form(form_id: Any) -> None:
if form_id and form_id in form_index and form_id not in form_ids:
form_ids.append(form_id)
for pid in picked:
want_form(process_index[pid].get("latest_form"))
for form_id in process_index[pid].get("quick_forms") or []:
want_form(form_id)
for row in scope.activities:
want_form(row.get("form"))
cursor = 0
while cursor < len(form_ids): # 表单正文里还可能引用别的表单,顺着捞一遍
for hit in ID_RE.findall(blob(form_index[form_ids[cursor]], ("content",))):
want_form(hit)
cursor += 1
scope.forms = [form_index[fid] for fid in form_ids]
wanted = {process_index[pid].get("category") for pid in picked} - {None, ""}
scope.categories = [category_index[cid] for cid in wanted if cid in category_index]
for cid in wanted - set(category_index):
notes.append(f"有流程挂在一个已经不存在的分类上:{cid}")
if with_deps:
text = "\n".join(
[blob(row, ("content",)) for row in scope.forms]
+ [blob(row, ("config",)) for row in scope.activities]
+ [blob(row, ("data_template", "title_config", "serial_config", "terminate_config",
"open_approve", "approval_config")) for row in scope.processes]
+ [blob(row, ("expression",)) for row in scope.routes]
)
scope.dict_codes = sorted(
row["code"] for row in fetch(src, "oa_dict_type")
if row["code"] and f'"{row["code"]}"' in text
)
# 同步型字典的选项来自一条取数,挂在 dict_type.source_code 上,一起带过去
remote = {row["source_code"] for row in fetch(src, "oa_dict_type")
if row.get("source_code") and row["code"] in set(scope.dict_codes)}
scope.data_source_codes = sorted(
{row["code"] for row in fetch(src, "oa_data_source")
if row["code"] and f'"{row["code"]}"' in text} | remote
)
scope.integration_codes = sorted(
row["code"] for row in fetch(src, "oa_integration")
if row["code"] and f'"{row["code"]}"' in text
)
if all_deps:
# 整表搬:设计点没点名都不重要,源库有就带上
scope.dict_codes = sorted(
row["code"] for row in fetch(src, "oa_dict_type") if row["code"])
scope.data_source_codes = sorted(
row["code"] for row in fetch(src, "oa_data_source") if row["code"])
scope.integration_codes = sorted(
row["code"] for row in fetch(src, "oa_integration") if row["code"])
notes.append(
f"--all-deps:依赖整表搬 —— 字典 {len(scope.dict_codes)} 类 / 取数 "
f"{len(scope.data_source_codes)} 个 / 回写 {len(scope.integration_codes)} 个,"
f"不限于被设计点名的那些"
)
# 审批角色是本部署自己在界面上维护的(Hub 不管这张表),环节用了就得一起搬,
# 否则抄送与审批落到一个不存在的角色上,测试环境里这条链子等于没配。
scope.role_names = sorted(design_refs(scope)["roles"])
return scope, notes
# ---------------------------------------------------------------------------
# 算差异
# ---------------------------------------------------------------------------
#: 写入顺序:外键先有爹再有儿(环节 / 路由挂在流程上),依赖表放最后
ORDER = ("oa_category", "oa_form", "oa_process", "oa_activity", "oa_route",
"oa_process_version", "oa_script", "oa_dict_type", "oa_dict_item",
"oa_data_source", "oa_integration", "oa_role")
def diff_columns(before: dict, after: dict) -> list[str]:
"""两边不一样、且值得看一眼的列(创建 / 更新时间不比,纯噪音)。"""
return [
column for column in after
if column not in ("create_time", "update_time") and before.get(column) != after[column]
]
def apply_refs(table_name: str, row: dict, ref_map: dict[str, str]) -> dict:
"""把行内容里出现的源主键换成目标主键(映射是恒等时原样返回)。"""
if not ref_map or not any(key != value for key, value in ref_map.items()):
return dict(row)
out = dict(row)
for column in REF_COLUMNS.get(table_name, ()):
value = out.get(column)
if value in (None, "", {}, []):
continue
text = value if isinstance(value, str) else json.dumps(value, ensure_ascii=False)
hit = ID_RE.sub(lambda match: ref_map.get(match.group(0), match.group(0)), text)
if hit == text:
continue
out[column] = hit if isinstance(value, str) else json.loads(hit)
return out
class Plan:
"""目标库这一趟要落地的三件事:删哪些、盖哪些、插哪些。"""
def __init__(self) -> None:
self.inserts: dict[str, list[dict]] = {}
self.updates: dict[str, list[tuple[dict, dict]]] = {}
self.deletes: dict[str, list[dict]] = {}
#: 表名 -> {源主键: 目标主键},只有改用目标已有行时才非恒等
self.remap: dict[str, dict[str, str]] = {}
def counts(self) -> dict[str, tuple[int, int, int]]:
names = set(self.inserts) | set(self.updates) | set(self.deletes)
return {
name: (len(self.inserts.get(name, [])), len(self.updates.get(name, [])),
len(self.deletes.get(name, [])))
for name in names
if any((len(self.inserts.get(name, [])), len(self.updates.get(name, [])),
len(self.deletes.get(name, []))))
}
@property
def total(self) -> int:
return sum(sum(row) for row in self.counts().values())
def compute_map(src_rows: list[dict], tgt_rows: list[dict], notes: list[str], table_name: str) -> dict[str, str]:
"""源行该落到目标哪一行上:主键对得上就用主键,对不上再看名字,都没有就新增。
按名字兜这一层是为了同一个东西两边各存一个 id —— 那样界面上会出现两张同名表单,
设计器里改这张那张不动,是最难查的一类脏数据。
"""
by_id = by_key(tgt_rows, "id")
by_name: dict[str, str] = {}
for row in tgt_rows:
by_name.setdefault(str(row.get("name") or ""), row["id"])
mapping: dict[str, str] = {}
for row in src_rows:
target_id = row["id"]
if target_id not in by_id and str(row.get("name") or "") in by_name:
target_id = by_name[str(row["name"])]
notes.append(f"{table_name}「{row['name']}」两边 id 不同,沿用目标库那一行")
mapping[row["id"]] = target_id
return mapping
def plan_upsert(plan: Plan, table_name: str, src_rows: list[dict], tgt_rows: list[dict],
mapping: dict[str, str], ref_map: dict[str, str], new_only: bool) -> None:
"""按主键整行覆盖 / 新增。目标库有但源库没有的行不动。"""
by_id = by_key(tgt_rows, "id")
for row in src_rows:
target_id = mapping.get(row["id"], row["id"])
new_row = apply_refs(table_name, row, ref_map)
new_row["id"] = target_id
current = by_id.get(target_id)
if current is None:
plan.inserts.setdefault(table_name, []).append(new_row)
elif not new_only and diff_columns(current, new_row):
plan.updates.setdefault(table_name, []).append((new_row, current))
def plan_upsert_by_key(plan: Plan, table_name: str, src_rows: list[dict],
tgt_rows: list[dict], key_column: str, new_only: bool) -> None:
"""按业务键对齐的那几张:字典 / 取数 / 回写认 ``code``,审批角色认 ``name``。
主键两边各随机,目标库已有同一业务键的行就沿用它那个主键,免得同一样东西存两份。
"""
by_code = by_key(tgt_rows, key_column)
for row in src_rows:
current = by_code.get(row.get(key_column))
new_row = dict(row)
if current is None:
plan.inserts.setdefault(table_name, []).append(new_row)
elif table_name in INSERT_ONLY:
continue # 已存在就留着目标库自己那一套,见 INSERT_ONLY 的说明
else:
new_row["id"] = current["id"]
for column in KEEP_LOCAL_ON_UPDATE.get(table_name, ()):
new_row[column] = current[column]
if not new_only and diff_columns(current, new_row):
plan.updates.setdefault(table_name, []).append((new_row, current))
def plan_replace_group(plan: Plan, table_name: str, src_rows: list[dict],
tgt_rows: list[dict]) -> None:
"""整组替换:组内先删干净再按源库插。源库删掉的环节 / 路由,目标库不留残骸。"""
if not src_rows:
# 源库里这一组本来就是空的,别把目标库那一组白删了(多半是 --process 圈窄了)
return
if _same_group(src_rows, tgt_rows):
return # 一模一样就别动了:重写 3000 行字典项不是"搬迁",是白折腾
plan.deletes.setdefault(table_name, []).extend(tgt_rows)
plan.inserts.setdefault(table_name, []).extend(src_rows)
def _same_group(src_rows: list[dict], tgt_rows: list[dict]) -> bool:
"""两组行是否逐列相同。时间戳是原样搬的,所以相同判得到位。"""
if len(src_rows) != len(tgt_rows):
return False
def key(row: dict) -> str:
return json.dumps(row, ensure_ascii=False, sort_keys=True, default=str)
return sorted(key(row) for row in src_rows) == sorted(key(row) for row in tgt_rows)
def build_plan(src: Engine, tgt: Engine, scope: Scope, with_deps: bool, new_only: bool,
notes: list[str]) -> Plan:
plan = Plan()
tgt_categories = fetch(tgt, "oa_category")
tgt_forms = fetch(tgt, "oa_form")
tgt_processes = fetch(tgt, "oa_process")
plan.remap["oa_category"] = compute_map(scope.categories, tgt_categories, notes, "oa_category")
plan.remap["oa_form"] = compute_map(scope.forms, tgt_forms, notes, "oa_form")
ref_map = dict(plan.remap["oa_form"])
ref_map.update(plan.remap["oa_category"])
plan_upsert(plan, "oa_category", scope.categories, tgt_categories,
plan.remap["oa_category"], ref_map, new_only)
plan_upsert(plan, "oa_form", scope.forms, tgt_forms, plan.remap["oa_form"], ref_map, new_only)
plan_upsert(plan, "oa_process", scope.processes, tgt_processes,
{row["id"]: row["id"] for row in scope.processes}, ref_map, new_only)
process_ids = scope.process_ids
for table_name in CHILD_TABLES:
src_rows = fetch(src, table_name, "process", process_ids)
tgt_rows = fetch(tgt, table_name, "process", process_ids)
if new_only and tgt_rows:
continue
src_rows = [apply_refs(table_name, row, ref_map) for row in src_rows]
plan_replace_group(plan, table_name, src_rows, tgt_rows)
if not with_deps:
return plan
for table_name in ("oa_dict_type", "oa_data_source", "oa_integration"):
codes = {"oa_dict_type": scope.dict_codes, "oa_data_source": scope.data_source_codes,
"oa_integration": scope.integration_codes}[table_name]
if not codes:
continue
plan_upsert_by_key(plan, table_name, fetch(src, table_name, "code", codes),
[row for row in fetch(tgt, table_name) if row["code"] in set(codes)],
"code", new_only)
if scope.role_names:
tgt_roles = [row for row in fetch(tgt, "oa_role")]
plan_upsert_by_key(
plan, "oa_role", fetch(src, "oa_role", "name", scope.role_names),
[row for row in tgt_roles if row["name"] in set(scope.role_names)],
"name", new_only,
)
absent = set(scope.role_names) - {row["name"] for row in tgt_roles}
if absent:
notes.append(
f"环节点名、但目标库没有的审批角色,这一趟照生产补上:{'、'.join(sorted(absent))}"
f"(连成员名单一起,名单里的人在这边有没有账号见下面的点名);"
f"已存在的角色一律不盖,成员名单还是测试环境自己那套"
)
if scope.dict_codes:
plan_replace_group(
plan, "oa_dict_item",
fetch(src, "oa_dict_item", "type", scope.dict_codes),
fetch(tgt, "oa_dict_item", "type", scope.dict_codes),
)
return plan
# ---------------------------------------------------------------------------
# 报告
# ---------------------------------------------------------------------------
def show(row: dict) -> str:
for column in ("name", "code", "id"):
if row.get(column):
return str(row[column])
return "?"
def brief(value: Any, limit: int = 72) -> str:
"""diff 里的一格取值:表单正文那种 JSON 动辄几万字符,全打出来没法看。"""
text = value if isinstance(value, str) else json.dumps(value, ensure_ascii=False, default=str)
text = " ".join(text.split())
return text if len(text) <= limit else f"{text[:limit]}…(共 {len(text)} 字)"
def report(scope: Scope, plan: Plan, notes: list[str], src_conf: dict, tgt_conf: dict,
new_only: bool) -> None:
print(f"源(只读) {label(src_conf)}")
print(f"目标(写) {label(tgt_conf)}")
print(f"模式 {'只补缺(已有的不动)' if new_only else '整行覆盖'}\n")
print(f"圈到的流程:{len(scope.processes)}")
for row in sorted(scope.processes, key=lambda item: str(item.get("name"))):
state = "启用" if row.get("enabled") else "停用"
print(f" - {row['name']}(v{row['version']},{state})")
print(f"\n表单:{len(scope.forms)} 张")
for row in sorted(scope.forms, key=lambda item: str(item.get("name"))):
print(f" - {row['name']}(v{row['version']})")
print(f"分类:{len(scope.categories)} 个:"
f"{'、'.join(str(row['name']) for row in scope.categories) or '无'}")
print(f"环节 {len(scope.activities)} 个 / 路由 {len(scope.routes)} 条")
print(f"依赖:字典 {len(scope.dict_codes)} 个({'、'.join(scope.dict_codes) or '无'})")
print(f" 取数 {len(scope.data_source_codes)} 个({'、'.join(scope.data_source_codes) or '无'})")
print(f" 回写 {len(scope.integration_codes)} 个({'、'.join(scope.integration_codes) or '无'})")
counts = plan.counts()
print("\n这一趟要动目标库:")
if not counts:
print(" 两边已经一致,一行都不用写。")
return
print(f" {'表':<20}{'新增':>6}{'覆盖':>6}{'删掉':>6}")
for table_name in sorted(counts, key=lambda name: (ORDER.index(name) if name in ORDER else 99)):
ins, upd, dele = counts[table_name]
print(f" {table_name:<20}{ins:>6}{upd:>6}{dele:>6}")
for table_name in ("oa_category", "oa_form", "oa_process", "oa_dict_type",
"oa_data_source", "oa_integration"):
pairs = plan.updates.get(table_name, [])
if not pairs:
continue
print(f"\n要覆盖的 {table_name}(目标库现值 → 改成):")
for new_row, old_row in pairs[:20]:
changed = diff_columns(old_row, new_row)
picked = [column for column in changed if column in NOTABLE]
if not picked:
print(f" - {show(new_row)}:{'、'.join(changed[:5])}"
f"{f' 等 {len(changed)} 列' if len(changed) > 5 else ''}有变化")
continue
bits = [f"{column}={brief(old_row.get(column))}→{brief(new_row.get(column))}"
for column in picked[:4]]
extra = [column for column in changed if column not in NOTABLE]
print(f" - {show(new_row)}:" + ";".join(bits)
+ (f"(另有 {len(extra)} 列变化)" if extra else ""))
if len(pairs) > 20:
print(f" …… 另外 {len(pairs) - 20} 行")
# 测试库版本号更高:覆盖等于把测试环境自己的改动退回去,这一条必须单独喊出来
behind: list[tuple[str, dict, dict]] = []
both_edited = 0
for table_name in ("oa_process", "oa_form", "oa_category"):
for new_row, old_row in plan.updates.get(table_name, []):
if int(old_row.get("version") or 0) > int(new_row.get("version") or 0):
behind.append((table_name, old_row, new_row))
elif old_row.get("update_time") and new_row.get("update_time") \
and old_row["update_time"] > new_row["update_time"]:
both_edited += 1
if behind:
print("\n⚠️ 下面这些测试库的版本号比生产还高,整行覆盖会把它退回生产的水平:")
for table_name, old_row, new_row in behind:
print(f" - {table_name}「{show(new_row)}」生产 v{new_row.get('version')}"
f" / 测试 v{old_row.get('version')}(测试库最后改动 {old_row['update_time']})")
print(" 想连这几条一起保住测试库自己的版本:加 --new-only 重跑(已有的一行都不盖)")
if both_edited:
print(f"\n(另有 {both_edited} 行两边都各自改过,按「以生产为准」整行覆盖)")
for note in notes:
print(f"\n注意:{note}")
# ---------------------------------------------------------------------------
# 引用点名
# ---------------------------------------------------------------------------
def audit(tgt: Engine, scope: Scope, plan: Plan) -> list[str]:
"""把设计点名的人 / 部门 / 角色 / 字典 / 取数 / 回写 / 开放应用,在目标库查一遍。
算的是**落地之后**的目标库:这一趟自己会补上的东西不算缺。
"""
refs = design_refs(scope)
roles, units = refs["roles"], refs["units"]
persons, apps, integrations = refs["persons"], refs["apps"], refs["integrations"]
# 补进来的角色自带成员名单,那几个人在这边存不存在得一起认
for row in plan.inserts.get("oa_role", []):
persons.update(str(item) for item in (row.get("list_auth") or []) if item)
form_text = "\n".join(blob(row, ("content",)) for row in scope.forms)
dicts = set(re.findall(r'"dictCode"\s*:\s*"([^"]+)"', form_text))
sources = set(scope.data_source_codes)
sources.update(re.findall(r'"source"\s*:\s*"([^"]+)"', form_text))
process_ids = set(scope.process_ids)
with tgt.connect() as conn:
def column_values(sql: str) -> set[str]:
return {str(row[0]) for row in conn.execute(text(sql))}
known = {
"角色": column_values("select name from oa_role"),
"部门": column_values("select name from oa_unit"),
"人员账号": column_values("select account from users"),
"数据字典": column_values("select code from oa_dict_type"),
"外部取数": column_values("select code from oa_data_source"),
"回写模板": column_values("select code from oa_integration"),
"开放应用": column_values("select code from oa_open_app"),
}
target_processes = column_values("select id from oa_process")
# 这一趟自己会补上的东西不算缺,按落地之后的样子算
for table_name, bucket in (("oa_dict_type", "数据字典"), ("oa_data_source", "外部取数"),
("oa_integration", "回写模板")):
known[bucket].update(row["code"] for row in plan.inserts.get(table_name, []))
known["角色"].update(row["name"] for row in plan.inserts.get("oa_role", []))
for row in plan.inserts.get("oa_process", []):
target_processes.add(row["id"])
problems: list[str] = []
for kind, wanted in (("角色", roles), ("部门", units), ("人员账号", persons),
("数据字典", dicts), ("外部取数", sources),
("回写模板", integrations), ("开放应用", apps)):
missing = sorted(wanted - known[kind])
if missing:
shown = "、".join(missing[:8]) + ("…" if len(missing) > 8 else "")
problems.append(f"{kind}目标库里没有:{shown}")
dangling = sorted(pid for pid in ID_RE.findall(form_text) if pid not in target_processes
and pid not in process_ids and pid not in {row["id"] for row in scope.forms}
and pid not in {row["id"] for row in scope.categories})
if dangling:
problems.append(f"表单正文里点到的流程目标库里没有:{'、'.join(dangling[:6])}")
return problems
# ---------------------------------------------------------------------------
# 落地
# ---------------------------------------------------------------------------
def prove_read_only(engine: Engine, conf: dict[str, Any]) -> str:
"""往源库发一条写语句,确认它真的写不进去。写进去了才是大问题。"""
table = TABLES["oa_process"]
with engine.connect() as conn:
probe_id = conn.execute(select(table.c.id).limit(1)).scalar()
if probe_id is None:
return "源库里一个流程都没有,只读保证没机会验证"
try:
conn.execute(table.update().where(table.c.id == probe_id).values(
update_time=conn.execute(text("select now()")).scalar()))
except Exception as exc: # noqa: BLE001 - 被顶回来才是对的
conn.rollback()
message = str(getattr(exc, "orig", exc))
if "1792" in message or "read only" in message.lower():
return f"源库只读已验证:写语句被 MySQL 顶回({label(conf)})"
return f"源库写语句被挡住了,但不是因为只读:{message}"
conn.rollback()
return "警告:源库接受了写语句(已回滚),只读事务没生效"
def apply_plan(tgt: Engine, plan: Plan, conf: dict[str, Any]) -> Path:
"""先备份目标库要动的行,再一个事务写下去。"""
stamp = datetime.now().strftime("%Y%m%d-%H%M%S")
path = BACKUP_DIR / f"design-migrate-{stamp}.json.gz"
before = {name: list(rows) for name, rows in plan.deletes.items()}
for table_name, pairs in plan.updates.items():
before.setdefault(table_name, []).extend(old for _, old in pairs)
inserted = {name: inserted_keys(name, rows) for name, rows in plan.inserts.items()}
write_backup(path, {
"written_at": stamp,
"target": label(conf),
"note": "migrate_design.py 落地前目标库的原行;--restore 按这个还原",
"before": {name: encode_rows(rows) for name, rows in before.items()},
"inserted": inserted,
})
session = sessionmaker(bind=tgt)()
try:
for table_name in ORDER:
rows = plan.deletes.get(table_name, [])
if not rows:
continue
table = TABLES[table_name]
pk = table.primary_key.columns.keys()[0]
ids = [row[pk] for row in rows]
for start in range(0, len(ids), 500):
session.execute(delete(table).where(table.c[pk].in_(ids[start:start + 500])))
for table_name in ORDER:
table = TABLES[table_name]
pk = table.primary_key.columns.keys()
for new_row, _ in plan.updates.get(table_name, []):
session.execute(
table.update()
.where(*[table.c[column] == new_row[column] for column in pk])
.values({column: value for column, value in new_row.items()
if column not in pk})
)
# 删一半插一半会撞主键,所以插放在所有删之后;表之间按外键顺序来
for table_name in ORDER:
rows = plan.inserts.get(table_name, [])
if rows:
session.execute(insert(TABLES[table_name]), rows)
session.commit()
except Exception:
session.rollback()
raise
finally:
session.close()
return path
def invalidate_cache(tgt: Engine, profile: str, process_ids: list[str]) -> str:
"""清目标环境的设计缓存。没配 Redis 就一句话带过——那一层自己 10 分钟也会过期。"""
conf = tomllib.loads((BACKEND / f"config.{profile}.toml").read_text()).get("redis") or {}
if not conf.get("enabled") or not conf.get("host"):
return "目标环境没配 Redis,跳过清缓存(设计缓存最多 10 分钟自己过期)"
try:
import redis # noqa: PLC0415 - 只在真要清缓存时才用得上
except ImportError:
return "这个 Python 没装 redis,跳过清缓存"
# 键前缀在运行配置里也可能被改过,以目标库存的为准
prefix = str(conf.get("key_prefix") or "unisoa").strip(":")
try:
with tgt.connect() as conn:
raw = conn.execute(text("select value from oa_system_setting where `key`='config:redis'")).scalar()
prefix = str((json.loads(raw) if raw else {}).get("redis_key_prefix") or prefix).strip(":")
except Exception: # noqa: BLE001 - 读不到就退回配置文件与前缀默认值
pass
client = redis.Redis(
host=str(conf["host"]), port=int(conf.get("port") or 6379), db=int(conf.get("db") or 0),
username=str(conf.get("username") or "") or None,
password=str(conf.get("password") or "") or None, socket_timeout=3,
)
try:
removed = 0
for pid in process_ids:
removed += int(client.delete(f"{prefix}:design:{pid}") or 0)
client.set(f"{prefix}:design-stale:{pid}", "1", ex=30)
client.set(f"{prefix}:design-stale:all", "1", ex=30) # 挡住别的工作进程把旧设计回填
except Exception as exc: # noqa: BLE001 - 清不掉缓存不该算迁移失败
return f"清设计缓存失败(数据不受影响,最多 10 分钟后自己过期):{exc}"
finally:
client.close()
return f"已清掉 {removed} 个设计缓存键({prefix}:design:*,Redis db={conf.get('db')})"
def verify(tgt: Engine, process_ids: list[str]) -> list[str]:
"""拿设计器自己的口径过一遍:搬过来的图必须还是能发布的图。"""
from app.engine import definition as definition_module # noqa: PLC0415
from app.engine import engine, expressions # noqa: PLC0415
from app.service import mapping, starters # noqa: PLC0415
session = sessionmaker(bind=tgt)()
problems: list[str] = []
try:
for pid in process_ids:
try:
definition = definition_module.load(session, pid)
name = definition.name
except Exception as exc: # noqa: BLE001
problems.append(f"{pid} 读不出流程定义:{exc}")
continue
problems.extend(f"{name}:{message}" for message in definition_module.validate(definition))
# 发布前设计器还要过的几关,一起过:条件脚本、回写挂载、发起范围
for route in definition.routes:
error = expressions.validate_script(route.expression)
if error:
problems.append(f"{name}:路由 {route.name} 条件脚本错误 {error}")
for activity in definition.activities.values():
for key, value in (activity.config or {}).items():
if isinstance(value, str) and key.lower().endswith("script"):
error = expressions.validate_script(value)
if error:
problems.append(f"{name}:环节 {activity.name} 脚本错误 {error}")
try:
anchors = [value for route in definition.routes for value in (route.id, route.name)]
problems.extend(f"{name}:{message}" for message in mapping.mount_problems(
session, definition.activities.values(), anchors,
engine.goback_universe(definition)))
row = session.get(models.Process, pid)
problems.extend(f"{name}:{message}"
for message in starters.scope_problems(session, row))
except Exception as exc: # noqa: BLE001 - 这两项读不到配置算提醒不算失败
problems.append(f"{name}:回写挂载与发起范围没校验成({exc})")
finally:
session.close()
return problems
# ---------------------------------------------------------------------------
def main(argv: list[str]) -> int:
parser = argparse.ArgumentParser(description="跨档位搬迁表单与流程设计(源库只读)")
parser.add_argument("--from", dest="src", default="prod", help="源档位,默认 prod")
parser.add_argument("--to", dest="dst", default="dev", help="目标档位,默认 dev")
parser.add_argument("--process", nargs="*", default=[], help="只搬名字里带这些词的流程")
parser.add_argument("--no-follow", action="store_true", help="不顺设计里的引用追别的流程")
parser.add_argument("--no-deps", action="store_true", help="不搬字典 / 取数 / 回写依赖")
parser.add_argument("--all-deps", action="store_true",
help="字典 / 取数 / 回写整表搬,不限被设计点名的那几项")
parser.add_argument("--new-only", action="store_true", help="目标库已有的原样不动,只补缺的")
parser.add_argument("--restore", metavar="备份文件",
help="撤销那一次搬迁;配 --process 只还原那几条流程")
parser.add_argument("--apply", action="store_true", help="真写;默认只干跑")
args = parser.parse_args(argv)
if args.restore:
restore(Path(args.restore), args.dst, args.process or None)
return 0
src_conf, tgt_conf = db_conf(args.src), db_conf(args.dst)
if (src_conf["host"], src_conf["name"]) == (tgt_conf["host"], tgt_conf["name"]):
raise SystemExit("源和目标是同一个库,没什么可搬的")
src = connect(src_conf, read_only=True)
tgt = connect(tgt_conf)
probe(src, src_conf)
probe(tgt, tgt_conf)
print(prove_read_only(src, src_conf) + "\n")
notes: list[str] = []
scope, scope_notes = collect_scope(src, args.process, not args.no_follow, not args.no_deps,
args.all_deps)
notes += scope_notes
if not scope.processes:
print("\n".join(f"注意:{line}" for line in notes) or "一个流程都没圈到")
return 1
plan = build_plan(src, tgt, scope, not args.no_deps, args.new_only, notes)
report(scope, plan, notes, src_conf, tgt_conf, args.new_only)
problems = audit(tgt, scope, plan)
if problems:
print("\n落地之后还要人看一眼的引用(脚本不猜人、也不替谁建角色):")
for line in problems:
print(f" ! {line}")
if not args.apply:
print(f"\n干跑结束:{plan.total} 行待写。加 --apply 落地(写之前先备份目标库)。")
return 0
if plan.total == 0:
print("\n两边已经一致,没有要写的。")
return 0
path = apply_plan(tgt, plan, tgt_conf)
print(f"\n已写入 {label(tgt_conf)},目标库原行备份在 {path.relative_to(BACKEND.parent)}")
print(f" 要还原:python3 scripts/migrate_design.py --to {args.dst} --restore {path}")
print(invalidate_cache(tgt, args.dst, scope.process_ids))
issues = verify(tgt, scope.process_ids)
if issues:
print(f"\n搬过去的流程没过设计器校验({len(issues)} 条):")
for line in issues[:20]:
print(f" ! {line}")
else:
print(f"设计器校验:{len(scope.process_ids)} 个流程全部通过")
return 0
if __name__ == "__main__":
sys.exit(main(sys.argv[1:]))