fourcal-pms/backend/scripts/clean_demo_data.py

494 lines
20 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
"""把库清回"刚建完表 + seed 基础数据"的样子:演示数据、界面造的示例单据、
pytest 跑出来的垃圾,一次扫干净。
先分清两样东西
==============
**留下的**(系统基础数据,没有就等于系统空转,seed 也随时能补回来):
perm_node / perm_role / perm_role_node / perm_field_scope / perm_data_policy
system_config
approval_flow / approval_node
accounts 里只留 seed 会补回来的两个账号:内置超管(``[seed] admin_account``)
与本地应急管理员(``[seed] emergency_account``,默认 ``localadmin``)
**清掉的**(都可再生:组织与人点一次「全量同步」就回来,单据本来就是要重造的):
组织岗位 org_unit / org_position
账号关系 accounts(超管之外)/ account_identity / perm_account_role / approval_delegate
项目单据 project / project_member / project_stage_log / project_visible_role
biz_revision / estimate_*_line / budget_*_line / calc_snapshot / attachment
审批运行时 approval_instance / approval_record / approval_task
流水日志 login_log / operation_log / hub_sync_run / sso_handoff
怎么保证删得回来
================
* **默认干跑**:不加 ``--apply`` 只报每张表要删多少行,一行都不动。
* **删之前先备份**:被删的每一行都写成 INSERT 落到
``storage/backups/clean-<时间>-<库名>.sql``,事后想留哪张表就回放那一段;
``--no-backup`` 才跳过。
* **生产档拒绝执行**:``PMS_ENV=prod`` 时直接退出,非要删得再加
``--i-know-this-is-production``。
* 表清单是写死的,但会跟模型比对:模型里多出一张既不在"留"也不在"删"名单里的表,
脚本当场报错,不会悄悄留一堆孤儿行。
python3 scripts/clean_demo_data.py # 先看要删什么
python3 scripts/clean_demo_data.py --apply # 备份 + 真删
python3 scripts/clean_demo_data.py --restore <备份文件> # 先看要回什么
python3 scripts/clean_demo_data.py --restore <备份文件> --apply # 真回放
回放走脚本自己,不依赖 ``mysql`` 客户端(本机就没装);备份文件是标准 INSERT,
拿去 ``mysql 库名 < 备份文件`` 也一样能吃。
"""
from __future__ import annotations
import argparse
import sys
from datetime import date, datetime
from decimal import Decimal
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from sqlalchemy import func, select # noqa: E402
import app.models # noqa: F401,E402 (触发模型注册)
from app.config import settings # noqa: E402
from app.db import Base, SessionFactory # noqa: E402
#: 留下的表:系统基础数据,清掉等于把权限目录和业务参数一起扔了。
#: ``dict_type`` / ``dict_item`` 在这儿是因为它们是 seed 每次启动都同步的"代码字典"
#: (``seed._seed_dicts`` → ``app.dicts.sync``):清掉不出问题,但下次启动又会长回来,
#: 白清一趟;界面上自建的 ``source=custom`` 条目算配置不算测试数据,跟着一起留。
KEEP_TABLES = (
"perm_node",
"perm_role",
"perm_role_node",
"perm_field_scope",
"perm_data_policy",
"system_config",
"approval_flow",
"approval_node",
"dict_type",
"dict_item",
)
#: 清掉的表,顺序就是删除顺序:先子表后父表,外键才不会拦着
CLEAR_TABLES = (
# 审批运行时(引用账号、流程实例)
"approval_record",
"approval_task",
"approval_instance",
"approval_delegate",
# 项目与单据(引用账号、项目、版本)
"budget_fund_month",
"budget_income_line",
"budget_income_plan_line",
"budget_manage_line",
"budget_other_cost_line",
"budget_pay_plan_line",
"budget_purchase_line",
"estimate_cost_line",
"estimate_income_line",
"estimate_other_line",
"biz_revision",
"project_member",
"project_stage_log",
"project_visible_role",
"attachment",
"calc_snapshot",
"project",
# 账号与关系(引用组织、岗位)
"perm_account_role",
"account_identity",
"sso_handoff",
"accounts",
# 组织岗位与流水
"org_unit",
"org_position",
"hub_sync_run",
"login_log",
"operation_log",
)
#: 这几张表虽然要清,但只清 seed 账号之外的行——超管与应急管理员不能一起删掉
CLEAR_ALL_BUT_SEED = ("accounts",)
#: KEEP_TABLES 里的行并不全是"seed 补得回来的基础数据":``migrate_legacy.py``
#: 会往 ``perm_role`` / ``approval_flow`` 里插"由老四算导入"的角色和流程,
#: code / key 一律带 ``legacy_`` 前缀。整表留着就等于把上一次迁移的结果当成基础数据
#: 留了下来——清完库再导一次,同 key 直接"已导入"跳过,改都改不动。
#: 所以认这个前缀,把导入件当演示数据清掉,seed 那几行照旧留。
KEEP_DROP_IMPORTED = {
"perm_role": ("code", "legacy_"),
"approval_flow": ("key", "legacy_"),
}
#: 跟着父表连坐的子表:自己没有任何"我是导入的"标记,只能按外键跟着删
KEEP_DROP_IMPORTED_CHILDREN = {
"approval_node": ("approval_flow", "flow_id"),
}
def _protected_accounts() -> tuple[str, ...]:
"""seed 随时会补回来、所以清理时必须留着的那几个账号。
以前只留超管,结果清一次演示数据就把应急管理员一起删了:它不属于"演示数据",
要等下次启动 seed 才回来,而清库的人往往正卡在"进不去门"上——那段时间最需要它。
"""
names = [settings.seed.admin_account or "admin",
(settings.seed.emergency_account or "").strip()]
keep: list[str] = []
for name in names:
if name and name not in keep:
keep.append(name)
return tuple(keep)
def _imported_ids(session) -> dict[str, list[int]]:
"""KEEP 表里"由迁移脚本导入"的行 id,按表分组。"""
from sqlalchemy import select
out: dict[str, list[int]] = {}
# .scalars() 不是风格问题:少了它取回来的是 (id,) 元组,
# 拿元组去 IN 匹配不到任何行,删起来就是"报 0 行、什么也没删"的静默失败
for name, (column, prefix) in KEEP_DROP_IMPORTED.items():
table = Base.metadata.tables[name]
out[name] = list(session.scalars(
select(table.c.id).where(table.c[column].like(f"{prefix}%", escape="\\"))))
for name, (parent, fk) in KEEP_DROP_IMPORTED_CHILDREN.items():
table = Base.metadata.tables[name]
parent_ids = out.get(parent) or [-1]
rows = list(session.scalars(select(table.c.id).where(table.c[fk].in_(parent_ids))))
if rows:
out.setdefault(name, [])
out[name] += rows
return {name: ids for name, ids in out.items() if ids}
def _literal(value: object) -> str:
"""把取出来的值写成 SQL 字面量,够把备份文件直接 mysql 进去就行。"""
if value is None:
return "NULL"
if isinstance(value, bool):
return "1" if value else "0"
if isinstance(value, (int, Decimal)):
return str(value)
if isinstance(value, datetime):
return f"'{value.isoformat(sep=' ')}'"
if isinstance(value, date):
return f"'{value.isoformat()}'"
if isinstance(value, (bytes, bytearray)):
return f"_binary 0x{value.hex()}"
text = str(value)
escaped = (text.replace("\\", "\\\\").replace("'", "\\'")
.replace("\n", "\\n").replace("\r", "\\r"))
return f"'{escaped}'"
def _rows_to_inserts(table, rows: list[dict], batch: int = 200) -> list[str]:
if not rows:
return []
cols = ", ".join(f"`{c}`" for c in rows[0])
out = []
for start in range(0, len(rows), batch):
values = ",\n ".join(
"(" + ", ".join(_literal(row[table.c[c].name]) for c in rows[0]) + ")"
for row in rows[start:start + batch]
)
out.append(f"INSERT INTO `{table.name}` ({cols}) VALUES\n {values};")
return out
def split_statements(sql: str) -> list[str]:
"""按 MySQL 的规矩切语句:引号里、反引号里、注释里的分号都不算结束。
不能拿 ``split(";")`` 糊弄——``login_log.user_agent`` 里全是
``Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)`` 这种带分号的值,
硬切出来的半截 INSERT 会把回放带到沟里。
"""
statements: list[str] = []
buf: list[str] = []
quote = None # 当前字符串引号:单引号或双引号
in_backtick = False
i, n = 0, len(sql)
while i < n:
ch = sql[i]
if quote is not None:
if ch == "\\" and i + 1 < n: # 反斜杠转义,连下一个字符一起吃掉
buf.append(ch)
buf.append(sql[i + 1])
i += 2
continue
if ch == quote:
if i + 1 < n and sql[i + 1] == quote: # '' 这种双写转义
buf.append(ch)
buf.append(sql[i + 1])
i += 2
continue
quote = None
buf.append(ch)
i += 1
continue
if in_backtick:
buf.append(ch)
if ch == "`":
in_backtick = False
i += 1
continue
if ch in ("'", '"'):
quote = ch
buf.append(ch)
i += 1
continue
if ch == "`":
in_backtick = True
buf.append(ch)
i += 1
continue
if ch == "#" or sql.startswith("--", i) or sql.startswith("/*", i):
end = sql.find("\n", i) if ch in "#-" else sql.find("*/", i)
if ch == "/" and sql.startswith("/*", i):
i = (end + 2) if end != -1 else n
else:
i = end if end != -1 else n
continue
if ch == ";":
statement = "".join(buf).strip()
if statement:
statements.append(statement)
buf = []
i += 1
continue
buf.append(ch)
i += 1
tail = "".join(buf).strip()
if tail:
statements.append(tail)
return statements
def _restore(path: Path, apply: bool) -> int:
if not path.is_file():
print(f"备份文件不存在:{path}")
return 2
statements = split_statements(path.read_text(encoding="utf-8"))
kinds: dict[str, int] = {}
for stmt in statements:
head = stmt.split(None, 1)[0].upper()
table = stmt.split("`")[1] if "`" in stmt else ""
kinds[f"{head} {table}".strip()] = kinds.get(f"{head} {table}".strip(), 0) + (
stmt.count("),") + 1 if head == "INSERT" else 1)
print(f"备份文件 = {path}")
summary = ",".join(f"{k} x{v}" for k, v in kinds.items())
print(f" 语句 {len(statements)} 条:{summary}")
if not apply:
print("\n干跑:只解析了备份,一行都没写。确认无误再加 --apply。")
return 0
with SessionFactory()() as session:
# 用 exec_driver_sql 而不是 text():备份里的数据长得像绑参(Hub 报错原文里的
# "data":null、LIKE 'wecom-%'),交给 SQLAlchemy 解析会被当成 :参数 或 % 格式符。
for stmt in statements:
session.exec_driver_sql(stmt)
session.commit()
print("回放完成。下面这张表现场的行数应该都回来了:")
with SessionFactory()() as session:
imported = _imported_ids(session)
for name in list(KEEP_DROP_IMPORTED) + list(KEEP_DROP_IMPORTED_CHILDREN):
ids = imported.get(name)
if not ids:
continue
table = Base.metadata.tables[name]
rows = list(session.execute(table.select().where(table.c.id.in_(ids))))
keys = [c.name for c in table.columns]
data = [dict(zip(keys, row)) for row in rows]
lines.append(f"-- {name}: {len(data)} 行(迁移导入,非 seed)")
lines.extend(_rows_to_inserts(table, data))
lines.append("")
for name in reversed(CLEAR_TABLES):
table = Base.metadata.tables[name]
n = session.execute(select(func.count()).select_from(table)).scalar() or 0
if n:
print(f" {name:24s} {n}")
return 0
def _classified_or_die() -> None:
known = set(KEEP_TABLES) | set(CLEAR_TABLES)
all_tables = set(Base.metadata.tables)
missing = sorted(all_tables - known)
if missing:
raise SystemExit(
"模型里出现了没归过类的表:" + ", ".join(missing)
+ "\n请先决定它算基础数据(进 KEEP_TABLES)还是算演示数据(进 CLEAR_TABLES)再跑。"
)
def _counts(session, protected: tuple[str, ...]) -> dict[str, int]:
from sqlalchemy import func, select
out = {}
for name in CLEAR_TABLES:
table = Base.metadata.tables[name]
total = session.execute(select(func.count()).select_from(table)).scalar() or 0
if name in CLEAR_ALL_BUT_SEED:
keep = session.execute(
select(func.count()).select_from(table)
.where(table.c.account.in_(protected))
).scalar() or 0
total -= keep
out[name] = total
for name, ids in _imported_ids(session).items():
out[name] = len(ids)
return out
def _backup_dir() -> Path:
path = Path("./storage/backups")
path.mkdir(parents=True, exist_ok=True)
return path
def _write_backup(session, protected: tuple[str, ...]) -> Path:
"""把要被删的每一行导成 INSERT,按"先父表后子表"的顺序写,回放时省心得得多。"""
path = _backup_dir() / (
f"clean-{datetime.now():%Y%m%d-%H%M%S}-{settings.database.name}.sql"
)
lines = [
"-- 四算 PMS 清理前的备份:可直接 mysql 回放",
f"-- 库 = {settings.database.name}@{settings.database.host}:{settings.database.port}"
f" 档位 = {settings.env}",
f"-- 生成 = {datetime.now():%Y-%m-%d %H:%M:%S} 由 scripts/clean_demo_data.py 写出",
"-- 回放前先确认库是空的或至少不撞主键:",
"-- mysql -h HOST -P PORT USER 库名 < 本文件",
"",
"SET NAMES utf8mb4;",
"SET FOREIGN_KEY_CHECKS=0;",
"",
]
for name in reversed(CLEAR_TABLES):
table = Base.metadata.tables[name]
rows = list(session.execute(table.select()))
keys = [c.name for c in table.columns]
data = [dict(zip(keys, row)) for row in rows]
if name in CLEAR_ALL_BUT_SEED:
data = [row for row in data if row["account"] not in protected]
if not data:
continue
lines.append(f"-- {name}: {len(data)} 行")
lines.extend(_rows_to_inserts(table, data))
lines.append("")
lines.extend(["SET FOREIGN_KEY_CHECKS=1;", ""])
path.write_text("\n".join(lines), encoding="utf-8")
return path
def _wipe(session, protected: tuple[str, ...]) -> dict[str, int]:
from sqlalchemy import bindparam, text
# 自引用先拆掉:一条 DELETE 里同时删父亲和下属,InnoDB 会按行的先后拦外键,
# 干脆先把 leader_id 与组织岗位指针清空,删起来就没有先后顺序的讲究。
session.execute(text("UPDATE accounts SET leader_id = NULL WHERE leader_id IS NOT NULL"))
session.execute(
text("UPDATE accounts SET org_unit_id = NULL, position_id = NULL "
"WHERE account IN :names").bindparams(bindparam("names", expanding=True)),
{"names": list(protected)},
)
done = {}
for name in CLEAR_TABLES:
table = Base.metadata.tables[name]
if name in CLEAR_ALL_BUT_SEED:
result = session.execute(table.delete().where(table.c.account.notin_(protected)))
else:
result = session.execute(table.delete())
done[name] = result.rowcount or 0
# KEEP 表里的迁移导入件最后删:业务行此时已经清光,perm_role_node /
# project_visible_role 这些引用方不会再拦外键。
# 顺序是先子表(approval_node)再父表(approval_flow)。
imported = _imported_ids(session)
for name in list(KEEP_DROP_IMPORTED_CHILDREN) + list(KEEP_DROP_IMPORTED):
ids = imported.get(name)
if not ids:
continue
table = Base.metadata.tables[name]
done[name] = session.execute(
table.delete().where(table.c.id.in_(ids))).rowcount or 0
return done
def main() -> int:
parser = argparse.ArgumentParser(description="清掉演示数据,回到刚初始化完的库")
parser.add_argument("--apply", action="store_true", help="真的删;不加只干跑报数")
parser.add_argument("--no-backup", action="store_true", help="跳过备份(不建议)")
parser.add_argument("--restore", metavar="备份文件", help="把某次清理的备份回放进当前库")
parser.add_argument("--i-know-this-is-production", action="store_true",
help="明知是生产档还要删/回放(默认生产档拒绝执行)")
args = parser.parse_args()
_classified_or_die()
protected = _protected_accounts()
if args.restore:
print(f"库 = {settings.database.name}@{settings.database.host}:{settings.database.port}"
f" 档位 = {settings.env}")
return _restore(Path(args.restore), args.apply)
if settings.is_production and not args.i_know_this_is_production:
print(f"档位 {settings.env} 算上线环境,本脚本默认不碰。"
"\n真要删再加 --i-know-this-is-production。")
return 2
print(f"库 = {settings.database.name}@{settings.database.host}:{settings.database.port}"
f" 档位 = {settings.env} 保留账号 = {', '.join(protected)}")
print(f"保留的表 = {', '.join(KEEP_TABLES)}")
with SessionFactory()() as session:
plan = _counts(session, protected)
total = sum(plan.values())
print("\n=== 要删的行 ===")
for name, n in plan.items():
if n:
print(f" {name:24s} {n}")
print(f" {'合计':22s} {total}")
if not total:
print("\n库已经是干净的,没东西要删。")
return 0
if not args.apply:
print("\n干跑:什么都没动。确认无误再加 --apply。")
return 0
if args.no_backup:
print("\n按要求跳过备份——删完就没了。")
else:
path = _write_backup(session, protected)
print(f"\n备份已写出:{path}({path.stat().st_size // 1024} KB)")
deleted = _wipe(session, protected)
session.commit()
print("\n=== 实际删除 ===")
for name, n in deleted.items():
if n:
print(f" {name:24s} {n}")
after = _counts(session, protected)
from sqlalchemy import func, select
left = {t.name: session.execute(select(func.count()).select_from(t)).scalar() or 0
for t in (Base.metadata.tables[n] for n in KEEP_TABLES)}
left["accounts"] = session.execute(
select(func.count()).select_from(Base.metadata.tables["accounts"])).scalar() or 0
print("\n=== 清完之后 ===")
print(f" 演示与业务数据残留 = {sum(after.values())}")
print(f" 留下的 seed 账号 = {left['accounts']}(应为 {len(protected)}:"
f"{'、'.join(protected)})")
print(" 留下的基础数据 = " + ", ".join(f"{k}:{v}" for k, v in left.items() if v))
print("\n组织与人要回来:统一身份同步页点一次全量同步即可。")
return 0
if __name__ == "__main__":
raise SystemExit(main())