unis-oa/backend/scripts/purge_test_data.py

581 lines
26 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
"""把流程跑出来的数据清掉,配置数据一行不动。
三种口径:
· 按流程名保留(默认):``--keep`` 点名的流程原样留着(含嵌入子单),其余流程的
在途单 + 归档单连带派生行一起删。
· ``--all``:一张单据都不留,所有流程的运行痕迹一起清。
· ``--initiator``:只删点名人发起的单(账号 / users.id / 姓名三种写法都认),别人
的单据一张都不碰。这一档默认也不扫早先删单留下的悬空残渣——那些行认不出发起人,
算不到这个人头上,要一起清加 ``--orphans``。操作日志同理:只删明确指向这批单据的,
不加 ``--logs`` 就连这几条也不动。
删的是「跑出来的东西」:``oa_work`` / ``oa_work_completed`` 两张单据表,连带
``oa_task(_completed)`` 待办与已办、``oa_work_log`` 流转日志、``oa_read(_completed)``
待阅与抄送、``oa_work_comment`` 批注、``oa_attachment`` 附件、``oa_notification``
站内信、``oa_draft`` 草稿,以及按单据记痕的 ``oa_outbound_message`` 发件箱、
``oa_notify_delivery`` 渠道投递、``oa_open_event`` 事件队列、``oa_data_source_log``
取数留痕。额度只跟着来源单据走:备用金这类 ``source=work`` 的 ``oa_quota`` 是办结时
生成的,单据没了那几笔就是冲不掉的欠款,连同 ``oa_quota_ledger`` 流水一起删。
顺手把早先删单留下的悬空行(单据已经没了但待办、日志、通知还挂着 job)一起扫掉。
配置数据一律不碰:用户 / 组织 / 身份 / 角色权限 / 流程与环节路由 / 表单 / 分类 /
字典 / 额度定义 / 取数定义原样留着;``oa_serial_number`` 流水号计数器默认也不动
(要清零加 ``--serials``)。
``oa_operation_log`` 只认指向单据的那几种动作(发起、处理待办、退回、终止……),
登录、改配置、权限成员一类一律保留:默认不动它,``--logs`` 打开,``--all`` 下默认
打开(``--keep-logs`` 关掉)。附件默认只删库里的记录,MinIO / 磁盘上的对象要一起删
加 ``--files``。
删之前整包备份成 gzip JSON(按表存原始行),``--restore`` 按行还原。
python3 scripts/purge_test_data.py # 干跑,只报数
python3 scripts/purge_test_data.py --all # 干跑,全清口径
python3 scripts/purge_test_data.py --all --apply # 真清
python3 scripts/purge_test_data.py --keep 费用 --apply # 只留流程名以「费用」开头的
python3 scripts/purge_test_data.py --initiator zhangsan # 干跑:只删某人发起的
python3 scripts/purge_test_data.py --initiator zhangsan --logs --apply
python3 scripts/purge_test_data.py --restore ../backups/purge-20261015-143000.json.gz
"""
from __future__ import annotations
import argparse
import gzip
import json
import sys
from datetime import date, datetime
from pathlib import Path
from typing import Any
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
from sqlalchemy import select # noqa: E402
from app import db, models # noqa: E402
from app.service import personref # noqa: E402
BACKUP_DIR = Path(__file__).resolve().parents[2] / "backups"
#: 默认留下的演示现场(老口径,``--all`` 就不看它了)
DEFAULT_KEEP = ["费用报销审批流程", "费用合规核验子流程", "审批兜底验证流程"]
#: 单据 -> 关联行。元组 = (模型, 用来认领的列名),行上任意一列指到这批单据就算它。
#: 光按 ``job`` 认是不够的:``oa_data_source_log`` 的取数留痕有一多半只记了 ``work``
#: 没记 job,嵌入会话与环节决定也是两头都记——按老写法只认 job,单据删完这些行就
#: 挂在一个已经不存在的单据 id 上,界面上点开就是一张查无此单的空壳。
#: 列名以 ``job`` 结尾的拿待办 job 比,其余(``work``)拿单据 id 比,两套键不混用。
CHILDREN: tuple[tuple[type, tuple[str, ...]], ...] = (
(models.Task, ("job", "work")), (models.TaskCompleted, ("job", "work")),
(models.WorkLog, ("job", "work")), (models.Read, ("job", "work")),
(models.ReadCompleted, ("job", "work")), (models.Attachment, ("job", "work")),
(models.Notification, ("job", "work")), (models.WorkComment, ("job", "work")),
(models.OutboundMessage, ("job", "work")), (models.NotifyDelivery, ("job", "work")),
(models.OpenEvent, ("job", "work")), (models.DataSourceLog, ("job", "work")),
(models.QuotaLedger, ("job", "work")),
# 嵌入子流程的免登会话、开放表态记下的环节决定:都跟着单据走
(models.EmbedSession, ("job", "work")), (models.NodeDecision, ("job", "work")),
)
#: 操作日志里「流程跑出来的」那几种动作(见 ``app/api/runtime.py``)。
#: 只在这几种动作里删日志:登录、改配置、权限成员、Hub 同步的目标 id 也可能是
#: 32 位十六进制串,拿「查不到单据」当理由一刀切会把平台行为的留痕一起删掉。
WORK_ACTIONS = frozenset({
"create_job", "terminate_job", "admin_terminate", "execute_task",
"batch_execute_task", "goback_task", "add_task", "transfer_task",
"cc_task", "share_work", "comment_work", "delete_comment",
"handle_read", "reset_task",
# 这些是后来加的流程动作,target 同样只指向单据 / 待办 / 环节决定:漏在这儿,
# 全清之后库里就剩一地指向已删单据的悬空日志(界面上操作记录点开查无此单)。
"work_start", "delete_job", "admin_transfer", "admin_suspend", "admin_resume",
"admin_move",
"admin_press_work", "node_decision_confirm", "node_decision_reject",
"external_check_submit", "external_check_pass", "external_check_withdraw",
"external_check_refresh", "admin_fix_data", "admin_transfer_work",
})
#: 还原时的插回顺序:先单据与额度,再是挂在它们身上的行
RESTORE_ORDER = ("oa_work", "oa_work_completed", "oa_quota")
BY_TABLE = {
mapper.class_.__tablename__: mapper.class_
for mapper in db.Base.registry.mappers
if hasattr(mapper.class_, "__tablename__")
}
# ---------------------------------------------------------------------------
# 小工具
# ---------------------------------------------------------------------------
def _jsonable(value: object) -> object:
return value.isoformat() if isinstance(value, (datetime, date)) else value
def _row_to_dict(obj: Any) -> dict[str, Any]:
return {col.name: _jsonable(getattr(obj, col.name)) for col in obj.__table__.columns}
def _coerce(column: Any, value: Any) -> Any:
"""备份里的时间是 ISO 串,插回去之前按列类型换回来。"""
if value is None:
return None
type_name = column.type.__class__.__name__
if type_name in ("DateTime", "Timestamp") and isinstance(value, str):
return datetime.fromisoformat(value)
if type_name == "Date" and isinstance(value, str):
return date.fromisoformat(value)
return value
class Victims:
"""这一趟要删的行:按表收着,同一行只收一次。"""
def __init__(self) -> None:
self.rows: dict[str, dict[tuple[str, ...], Any]] = {}
def add(self, model: type, obj: Any) -> None:
table = model.__tablename__
key = tuple(str(getattr(obj, c.name, "")) for c in model.__table__.primary_key)
self.rows.setdefault(table, {})[key] = obj
def add_all(self, model: type, objs: Any) -> None:
for obj in objs:
self.add(model, obj)
def counts(self) -> dict[str, int]:
return {table: len(rows) for table, rows in self.rows.items()}
def dump(self) -> dict[str, list[dict[str, Any]]]:
return {
table: [_row_to_dict(obj) for obj in rows.values()]
for table, rows in self.rows.items()
}
# ---------------------------------------------------------------------------
# 算清单
# ---------------------------------------------------------------------------
def initiator_keys(session, values: list[str]) -> set[str]:
"""发起人筛选值 -> 拿去和单据发起人比对的键集合。
认人走 :mod:`app.service.personref` 的口径(先登录账号,再 ``users.id``),认得出人
就把他的账号 / 主键 / 姓名都收进来;认不出人时原样收进去按字面比一次——命令行上敲错
的名字只会筛不出单子(干跑一眼看得见),不会反过来把整库当成"没人发起的"删光。
"""
keys: set[str] = set()
for raw in values:
text = str(raw or "").strip()
if not text:
continue
keys.add(text)
person = personref.resolve(session, text)
if person is not None:
keys |= {
str(person.account or ""), str(person.id or ""),
str(person.display_name or ""),
}
return {key for key in keys if key}
def collect(
session,
keep: list[str],
*,
all_mode: bool,
initiators: list[str] | None = None,
sweep_orphans: bool = True,
) -> dict[str, Any]:
"""按口径算出保留哪些单据、删哪些行。
返回 ``{"keep": [...], "doom": [...], "victims": Victims, "orphans": {...},
"doomed_keys": {...}}``:``victims`` 是全部要删的行,``doomed_keys`` 是这批单据的
id / job / 单号,给操作日志对账用。
"""
keys = initiator_keys(session, initiators or [])
def wanted(name: str) -> bool:
return not all_mode and any(str(name or "").startswith(p) for p in keep)
def initiated(w: Any) -> bool:
"""这张单的发起人是不是口径里点的人;不点名时一律算"是"(老口径一字不变)。"""
if not initiators:
return True
stated = {str(w.creator_identity or ""), str(w.creator or ""),
str(w.creator_person or "")}
return bool(stated & keys)
works = list(session.execute(select(models.Work)).scalars())
archived = list(session.execute(select(models.WorkCompleted)).scalars())
everything = works + archived
# 白名单流程、以及不是这次点名发起人建的单,都留下
keep_works = [w for w in everything if wanted(w.process_name) or not initiated(w)]
doom = [w for w in everything if not wanted(w.process_name) and initiated(w)]
# 保留单据派生出去的子单(嵌入子流程等),哪怕流程名不在白名单里也一起留
keep_ids = {w.id for w in keep_works}
grown = True
while grown:
grown = False
for w in everything:
if w.parent_work in keep_ids and w.id not in keep_ids:
keep_ids.add(w.id)
doom = [x for x in doom if x.id != w.id]
keep_works.append(w)
grown = True
keep_jobs = {w.job for w in keep_works if w.job}
doom_jobs = {w.job for w in doom if w.job}
all_jobs = keep_jobs | doom_jobs
# keep_ids 是上面那圈"保留单的子单也一起留"长出来的,这里只补另外两套键
doom_ids = {w.id for w in doom}
all_ids = keep_ids | doom_ids
victims = Victims()
orphans: dict[str, int] = {}
for model, cols in CHILDREN:
doomed, dangling = [], []
for row in session.execute(select(model)).scalars():
hits_doomed = hits_dangling = False
for col in cols:
value = getattr(row, col)
if not value:
continue
doomed_by, known = ((doom_jobs, all_jobs) if str(col).endswith("job")
else (doom_ids, all_ids))
if value in doomed_by:
hits_doomed = True
elif value not in known:
# 悬空 = 这一列指的单据既不属于保留单、也不属于这次要删的单
#(早先删单留下的残渣)
hits_dangling = True
if hits_doomed:
doomed.append(row)
elif hits_dangling:
dangling.append(row)
victims.add_all(model, doomed)
# 按发起人清理时不碰悬空行:那些行挂的是早先就删掉的单,算不到这个人头上
if sweep_orphans:
victims.add_all(model, dangling)
orphans[model.__tablename__] = len(dangling)
# 额度:备用金这类 source=work 的额度行是来源单据办结时长出来的,单据走了它就是
# 一笔冲不掉的欠款(还会按 blockNewWhenOpen 挡住下次发起);外部导入 / 人工建的
# 额度没有来源单据,一律不动
doomed_quotas = [
q for q in session.execute(select(models.Quota)).scalars()
if (q.source_job or q.source_work) and q.source_job not in keep_jobs
]
victims.add_all(models.Quota, doomed_quotas)
quota_ids = {q.id for q in doomed_quotas}
if quota_ids:
# 挂在这些额度上的流水(含人工调整:额度行都没了,流水留着也对不上账)
victims.add_all(
models.QuotaLedger,
[
ledger for ledger in session.execute(select(models.QuotaLedger)).scalars()
if ledger.quota in quota_ids
],
)
# 草稿:没有单据可留的流程,草稿一律不算数
keep_pids = {w.process for w in keep_works if w.process}
drafts = list(session.execute(select(models.Draft)).scalars())
if initiators:
# 点名发起人时草稿改按人筛,不看那张流程还有没有保留单:这一档要带走的是
# "他自己还没发出去的草稿",别人的草稿一张都不碰
doomed_drafts = [
d for d in drafts
if {str(d.identity or ""), str(d.person or ""),
str(d.person_name or "")} & keys
]
else:
doomed_drafts = [d for d in drafts if d.process not in keep_pids]
victims.add_all(models.Draft, doomed_drafts)
victims.add_all(models.Work, [w for w in doom if isinstance(w, models.Work)])
victims.add_all(models.WorkCompleted, [w for w in doom if isinstance(w, models.WorkCompleted)])
doomed_keys: set[str] = set()
for w in doom:
doomed_keys |= {w.id, w.job, w.serial}
return {
"keep": keep_works,
"doom": doom,
"victims": victims,
"orphans": orphans,
"doomed_keys": doomed_keys,
"keep_processes": sorted({w.process for w in keep_works if w.process}),
"doom_initiators": sorted(
{str(w.creator_identity or w.creator_person or w.creator) for w in doom}
),
"swept_orphans": sweep_orphans,
}
def collect_logs(
session, doomed_keys: set[str], *, all_mode: bool, scoped: bool = False
) -> list[Any]:
"""挑出该删的操作日志:只认 :data:`WORK_ACTIONS`,登录 / 改配置一类一律不碰。
``scoped=True``(按发起人清理)只收明确指向这批单据的那几条:悬空日志属于早先就删掉
的单,认不出是谁发起的,这一趟不该顺手带走。
"""
alive_keys: set[str] = set()
for model in (models.Work, models.WorkCompleted):
for w in session.execute(select(model)).scalars():
alive_keys |= {w.id, w.job, w.serial}
doomed: list[Any] = []
for row in session.execute(select(models.OperationLog)).scalars():
if row.action not in WORK_ACTIONS:
continue
target = str(row.target or "")
# ① 明确指向这次删掉的单据;② 全清口径下流程动作一律不留;
# ③ 悬空:target 看着像单据 / 待办 id,但那张单早先就被删了
if target in doomed_keys:
doomed.append(row)
elif not scoped and (
all_mode
or (bool(target) and target not in alive_keys
and (len(target) == 32 or target.isdigit()))
):
doomed.append(row)
return doomed
# ---------------------------------------------------------------------------
# 执行
# ---------------------------------------------------------------------------
def backup(path: Path, payload: dict) -> None:
path.parent.mkdir(parents=True, exist_ok=True)
with gzip.open(path, "wt", encoding="utf-8") as fh:
json.dump(payload, fh, ensure_ascii=False)
def delete_files(session, attachments: list[Any]) -> list[str]:
"""把已删附件对应的对象从 MinIO / 磁盘上抹掉(界面删附件就是这么干的)。"""
from app.storage import StorageError, resolve_uri # 只有 --files 才需要连通存储
gone: list[str] = []
for row in attachments:
uri = str(row.storage or "")
if not uri:
continue
try:
resolve_uri(uri).delete(uri)
gone.append(uri)
except StorageError as exc:
# 文件删不掉不该挡住这趟清理,留个话,剩下的当孤儿对象手工处理
print(f" 附件文件删除失败 {uri}:{exc}", file=sys.stderr)
return gone
def run(
keep: list[str],
apply_changes: bool,
with_logs: bool = False,
all_mode: bool = False,
with_files: bool = False,
reset_serials: bool = False,
initiators: list[str] | None = None,
sweep_orphans: bool = True,
) -> dict[str, Any]:
session = db.SessionLocal()
try:
plan = collect(
session, keep,
all_mode=all_mode, initiators=initiators, sweep_orphans=sweep_orphans,
)
victims: Victims = plan["victims"]
orphans = plan["orphans"]
logs_deleted = 0
if with_logs:
log_rows = collect_logs(
session, plan["doomed_keys"],
all_mode=all_mode, scoped=bool(initiators),
)
logs_deleted = len(log_rows)
victims.add_all(models.OperationLog, log_rows)
serial_rows: list[Any] = []
if reset_serials:
keep_pids = set(plan["keep_processes"])
serial_rows = [
n for n in session.execute(select(models.SerialNumber)).scalars()
if n.process not in keep_pids
]
victims.add_all(models.SerialNumber, serial_rows)
counts = victims.counts()
dump = victims.dump()
attachments = (
list(victims.rows.get(models.Attachment.__tablename__, {}).values())
if with_files else []
)
if apply_changes:
# 备份先落盘再动手:删完才写的话,中间崩一下就连还原材料都没有了
stamp = datetime.now().strftime("%Y%m%d-%H%M%S")
path = BACKUP_DIR / f"purge-{'all' if all_mode else 'works'}-{stamp}.json.gz"
backup(path, dump)
for table, rows in victims.rows.items():
for obj in rows.values():
session.delete(obj)
session.commit()
files_deleted = delete_files(session, attachments) if with_files else []
else:
path = None
session.rollback()
files_deleted = []
return {
"mode": "apply" if apply_changes else "dry-run",
"scope": ("initiator:" + "、".join(initiators) if initiators
else "all" if all_mode else "keep-list"),
"keep_works": [
{"serial": w.serial, "status": w.status, "process": w.process_name,
"title": w.title, "initiator": w.creator_identity} for w in plan["keep"]
],
"doom_works": [
{"serial": w.serial, "status": w.status, "process": w.process_name,
"title": w.title, "initiator": w.creator_identity} for w in plan["doom"]
],
"orphan_children": orphans,
"deleted_counts": counts,
"operation_logs": logs_deleted,
"serial_numbers": len(serial_rows),
"files_deleted": len(files_deleted),
"backup": str(path) if path else "",
"doom_initiators": plan["doom_initiators"],
"orphan_rows": sum(orphans.values()),
"orphans_swept": plan["swept_orphans"],
}
finally:
session.close()
def restore(path: Path) -> dict[str, int]:
"""按备份逐表回写:主键不在了就插回整行,还在就把值改回备份里的样子。"""
with gzip.open(path, "rt", encoding="utf-8") as fh:
payload = json.load(fh)
# 老备份是扁平的 {表: 行},新备份同一个形状;万一以后加了包装层这里兜一下
tables_payload = payload.get("deleted") if "deleted" in payload else payload
tables = list(tables_payload or {})
ordered = [t for t in RESTORE_ORDER if t in tables] + [t for t in tables if t not in RESTORE_ORDER]
counts: dict[str, int] = {}
session = db.SessionLocal()
try:
for table in ordered:
model = BY_TABLE.get(table)
if model is None:
print(f"跳过 {table}:这个版本的模型里没有这张表")
continue
meta = model.__table__
pk = [c.name for c in meta.primary_key]
alive = {
tuple(str(v) for v in row)
for row in session.execute(select(*[meta.c[c] for c in pk]))
}
for row in tables_payload.get(table, []):
values = {c.name: _coerce(c, row.get(c.name)) for c in meta.columns if c.name in row}
key = tuple(str(row.get(c, "")) for c in pk)
if key in alive:
session.execute(
meta.update()
.where(*(meta.c[c] == values[c] for c in pk))
.values(**{c: v for c, v in values.items() if c not in pk})
)
else:
session.execute(meta.insert().values(**values))
counts[table] = counts.get(table, 0) + 1
session.commit()
finally:
session.close()
return counts
def main() -> int:
ap = argparse.ArgumentParser()
ap.add_argument("--keep", action="append", default=[], help="流程名前缀,可重复")
ap.add_argument("--all", action="store_true", help="全清:一张单据都不留(忽略 --keep)")
ap.add_argument("--initiator", action="append", default=[],
help="只删这个发起人发起的单(登录账号 / users.id / 姓名),可重复")
ap.add_argument("--orphans", action="store_true",
help="按发起人清理时,连早先删单留下的悬空行一起扫(默认不碰)")
ap.add_argument("--apply", action="store_true", help="真删(默认只干跑报数)")
ap.add_argument("--logs", action="store_true", help="连指向单据的操作日志一起清")
ap.add_argument("--keep-logs", action="store_true",
help="全清口径下也保留操作日志(默认 --all 会一起清)")
ap.add_argument("--serials", action="store_true", help="把流水号计数器一起清零")
ap.add_argument("--files", action="store_true",
help="附件对应的 MinIO / 磁盘对象一起删(默认只删记录)")
ap.add_argument("--restore", metavar="FILE", help="从备份还原,不做清理")
args = ap.parse_args()
if args.restore:
counts = restore(Path(args.restore))
for table, n in sorted(counts.items(), key=lambda kv: -kv[1]):
print(f" {table:26} {n}")
print(f"\n共回写 {sum(counts.values())} 行")
return 0
if args.initiator and args.all:
ap.error("--initiator 与 --all 冲突:前者只删点名人发起的,后者一张不留")
if args.all:
keep = []
elif args.initiator:
# 按发起人这一档不再默认留那几条演示流程:点名要删的人,他的演示单也算进去
keep = args.keep
else:
keep = args.keep or DEFAULT_KEEP
with_logs = args.logs or (args.all and not args.keep_logs)
result = run(
keep, args.apply,
with_logs=with_logs,
all_mode=args.all,
with_files=args.files,
reset_serials=args.serials,
initiators=args.initiator,
# 悬空残渣只有在"全清 / 按流程名"那两档才是顺手就扫的活,按发起人这档要显式点名
sweep_orphans=not args.initiator or args.orphans,
)
printable = {k: v for k, v in result.items()
if k not in ("keep_works", "doom_initiators")}
print(json.dumps(printable, ensure_ascii=False, indent=2))
print("\n逐表:")
for key, n in sorted(result["deleted_counts"].items(), key=lambda kv: (-kv[1], kv[0])):
orphan = result["orphan_children"].get(key, 0)
tail = ""
if orphan:
tail = f"(含悬空 {orphan})" if result["orphans_swept"] else f"(另有悬空 {orphan} 未删)"
print(f" {key:26} {n}{tail}")
if not result["orphans_swept"] and result["orphan_rows"]:
print("\n悬空残渣(早先删单留下的、认不出发起人)共 %d 行,本趟不碰;要一起清加 --orphans"
% result["orphan_rows"])
if args.initiator:
print("\n口径:只删 %s 发起的单据,别人的单据与配置表一律不动。"
% "、".join(args.initiator))
if not result["doom_works"]:
print("⚠️ 一个发起人都没匹配上:核对一下账号有没有写错(也确认真没有他发起的单)。")
else:
print("命中的发起人:%s" % "、".join(result["doom_initiators"]))
print("保留 %d 张(含别人发起的)" % len(result["keep_works"]))
elif args.all:
print("\n口径:全清——所有流程的单据与运行痕迹都删,配置表不动。")
else:
print("\n保留 %d 张:%s" % (len(result["keep_works"]),
"、".join(sorted({w["process"] or "?" for w in result["keep_works"]})) or "(无)"))
if not args.apply:
print("\n以上为干跑,未改任何数据;确认后加 --apply")
return 0
if __name__ == "__main__":
sys.exit(main())