unis-oa/backend/scripts/migrate_to_users_table.py

291 lines
12 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
"""把旧的 ``oa_person`` / ``oa_unit`` 迁到 ``users`` + BIGINT 主键.
旧结构沿用 O2OA 的 32 位十六进制主键,人员/部门 id 以字符串形式散落在流程、
日志、附件、门户等表里;新结构是 ``users.id`` / ``oa_unit.id`` 用
``BIGINT UNSIGNED AUTO_INCREMENT``,``users.organization_id`` 外键指向 ``oa_unit``。
步骤:
1. 把 4 张要重建的老表整表导出成 INSERT(``sql/backup/*.sql``),可随时回滚;
2. 老表改名 ``*_legacy`` 让位,再由 ``create_schema()`` 建出新表;
3. 按创建顺序搬部门、人员、权限角色,生成 old -> new id 映射;
(老结构里那张 ``oa_identity`` 一人多身份的档案表不搬:它早已整张下线,
认人只看 ``users.account``,DN 由部门路径与姓名现算。)
4. 全库扫描字符列,把整值命中老 id 的地方换成新 id(人名、身份 DN 一律不碰)。
用法::
python3 scripts/migrate_to_users_table.py --dry-run
python3 scripts/migrate_to_users_table.py
python3 scripts/migrate_to_users_table.py --keep-legacy
"""
from __future__ import annotations
import argparse
import json
import sys
from datetime import datetime
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[0].parent))
from sqlalchemy import text # noqa: E402
from app import models # noqa: E402
from app.db import create_schema, engine, settings # noqa: E402
# 这几张表按新结构重建,其余表只改引用值。
REBUILT = ("oa_unit", "oa_person", "oa_person_auth_role")
NEW_TABLES = ("users", "oa_unit", "oa_person_auth_role")
ROLE_MAP = {"admin": models.ROLE_ADMIN, "user": models.ROLE_MEMBER, "member": models.ROLE_MEMBER}
def _sql_literal(value) -> str:
if value is None:
return "NULL"
if isinstance(value, bool):
return "1" if value else "0"
if isinstance(value, (int, float)):
return str(value)
if isinstance(value, (dict, list)):
value = json.dumps(value, ensure_ascii=False)
return "'" + str(value).replace("\\", "\\\\").replace("'", "''") + "'"
def _tables(conn) -> list[str]:
return [row[0] for row in conn.execute(text("SHOW TABLES"))]
_COLUMN_CACHE: dict[str, list[str]] = {}
def _text_columns(table: str) -> list[str]:
"""Character/JSON columns of a table, resolved in one round trip per schema."""
if not _COLUMN_CACHE:
with engine.connect() as conn:
rows = conn.execute(
text(
"SELECT table_name, column_name, data_type FROM information_schema.columns"
" WHERE table_schema = :schema"
),
{"schema": settings.db_name},
).all()
for table_name, column_name, data_type in rows:
if any(kind in str(data_type).upper() for kind in ("CHAR", "TEXT", "JSON")):
_COLUMN_CACHE.setdefault(str(table_name), []).append(str(column_name))
return _COLUMN_CACHE.get(table, [])
def _fetch_all(conn, table: str) -> list[dict]:
if not conn.execute(text(f"SHOW TABLES LIKE '{table}'")).first():
return []
return [dict(row) for row in conn.execute(text(f"SELECT * FROM `{table}`")).mappings().all()]
def _backup(conn, out_dir: Path) -> Path:
out_dir.mkdir(parents=True, exist_ok=True)
path = out_dir / f"pre_users_migration_{datetime.now():%Y%m%d_%H%M%S}.sql"
lines = [
"-- users 表结构迁移前的原始数据备份(可直接 mysql < 本文件 还原老表)",
f"-- 数据库: {settings.db_name}",
f"-- 时间: {datetime.now():%Y-%m-%d %H:%M:%S}",
"",
]
for table in REBUILT:
rows = _fetch_all(conn, table)
if not rows:
continue
columns = list(rows[0])
lines.append(f"-- ---------- {table} ----------")
for row in rows:
values = ", ".join(_sql_literal(row[column]) for column in columns)
names = ", ".join(f"`{column}`" for column in columns)
lines.append(f"INSERT INTO `{table}` ({names}) VALUES ({values});")
lines.append("")
path.write_text("\n".join(lines), encoding="utf-8")
return path
def _find_reference_columns(conn, legacy_ids: set[str]) -> dict[str, int]:
"""找出哪些字符列整值存着老 id。只比对整值,所以人名/DN/路径不会被误伤。"""
found: dict[str, int] = {}
if not legacy_ids:
return found
for table in _tables(conn):
if table.endswith("_legacy") or table in REBUILT:
continue
for column in _text_columns(table):
values = {
row[0]
for row in conn.execute(text(f"SELECT DISTINCT `{column}` FROM `{table}`"))
if isinstance(row[0], str)
}
matched = values & legacy_ids
if not matched:
continue
clause = ", ".join(_sql_literal(value) for value in matched)
count = conn.execute(text(f"SELECT COUNT(*) FROM `{table}` WHERE `{column}` IN ({clause})")).scalar()
found[f"{table}.{column}"] = int(count or 0)
return found
def _units_in_parent_order(rows: list[dict]) -> list[dict]:
by_id = {row["id"]: row for row in rows}
def depth(row: dict) -> int:
level, current, guard = 0, row, 0
while current.get("parent_id") in by_id and guard < 64:
current = by_id[current["parent_id"]]
level += 1
guard += 1
return level
return sorted(rows, key=lambda row: (depth(row), row["create_time"] or datetime.min, row["id"]))
def _copy_units(conn, rows: list[dict]) -> dict[str, int]:
mapping: dict[str, int] = {}
for row in _units_in_parent_order(rows):
conn.execute(
text(
"INSERT INTO oa_unit (name, code, alias, description, parent_id, parent_name, path,"
" distinguished_name, short_name, order_number, status, create_time, update_time)"
" VALUES (:name, :code, :alias, :description, :parent_id, :parent_name, :path,"
" :distinguished_name, :short_name, :order_number, :status, :create_time, :update_time)"
),
{
"name": row["name"],
# 老表没有编码列, alias 就是本地编码;status 由老 enabled 折算。
"code": row["alias"] or "",
"alias": row["alias"] or "",
"description": row["description"] or "",
"parent_id": mapping.get(row["parent_id"]) if row["parent_id"] else None,
"parent_name": row["parent_name"] or "",
"path": row["path"] or "",
"distinguished_name": row["distinguished_name"] or "",
"short_name": row["short_name"] or "",
"order_number": row["order_number"],
"status": 1 if row["enabled"] else 0,
"create_time": row["create_time"],
"update_time": row["update_time"],
},
)
mapping[row["id"]] = int(conn.execute(text("SELECT LAST_INSERT_ID()")).scalar())
return mapping
def _copy_persons(conn, rows: list[dict], unit_map: dict[str, int]) -> dict[str, int]:
mapping: dict[str, int] = {}
for row in sorted(rows, key=lambda r: (r["create_time"] or datetime.min, r["sort"])):
conn.execute(
text(
"INSERT INTO users (account, email, password_hash, display_name, avatar_url,"
" organization_id, role, status, last_login_at, created_at, updated_at)"
" VALUES (:account, :email, :password_hash, :display_name, :avatar_url,"
" :organization_id, :role, :status, :last_login_at, :created_at, :updated_at)"
),
{
"account": row["account"],
"email": row["email"] or "",
"password_hash": row["password_hash"] or "",
"display_name": (row["name"] or "")[:100],
"avatar_url": (row["avatar"] or "")[:500],
"organization_id": unit_map.get(row["unit_id"]) if row["unit_id"] else None,
"role": ROLE_MAP.get(row["role"], row["role"] or models.ROLE_MEMBER),
"status": models.UserStatus.ACTIVE if row["enabled"] else models.UserStatus.DISABLED,
"last_login_at": row["last_login_time"],
"created_at": row["create_time"],
"updated_at": row["update_time"],
},
)
mapping[row["id"]] = int(conn.execute(text("SELECT LAST_INSERT_ID()")).scalar())
return mapping
def _copy_links(conn, rows: list[dict], user_map: dict[str, int]) -> None:
for row in rows:
person_id = user_map.get(row["person_id"])
if person_id is None:
continue
conn.execute(
text("INSERT IGNORE INTO oa_person_auth_role (person_id, role_id) VALUES (:person_id, :role_id)"),
{"person_id": person_id, "role_id": row["role_id"]},
)
def _rewrite_references(conn, columns: dict[str, int], remap: dict[str, int]) -> int:
written = 0
for qualified in columns:
table, column = qualified.split(".", 1)
for old, new in remap.items():
result = conn.execute(
text(f"UPDATE `{table}` SET `{column}` = :new WHERE `{column}` = :old"),
{"new": str(new), "old": old},
)
written += int(result.rowcount or 0)
return written
def migrate(dry_run: bool, keep_legacy: bool) -> int:
with engine.connect() as conn:
if not all(conn.execute(text(f"SHOW TABLES LIKE '{t}'")).first() for t in ("oa_person", "oa_unit")):
leftover = [t for t in _tables(conn) if t.endswith("_legacy")]
if leftover:
print(f"看起来已经迁移过,只剩老表:{', '.join(sorted(leftover))}")
print("确认无误后可手工 DROP,或用 --keep-legacy 之外的那次运行收尾。")
else:
print("库里没有 oa_person / oa_unit,无需迁移。")
return 0
legacy_units = _fetch_all(conn, "oa_unit")
legacy_persons = _fetch_all(conn, "oa_person")
legacy_links = _fetch_all(conn, "oa_person_auth_role")
legacy_ids = {row["id"] for row in legacy_units} | {row["id"] for row in legacy_persons}
references = _find_reference_columns(conn, legacy_ids)
print(f"待迁移:{len(legacy_units)} 个部门 / {len(legacy_persons)} 个用户")
print("引用老 id 的列:")
for qualified, count in sorted(references.items()):
print(f" {qualified}: {count} 行")
if dry_run:
print("\ndry-run:未写库。")
return 0
print(f"备份: {_backup(conn, Path(__file__).resolve().parents[2] / 'sql' / 'backup')}")
for table in REBUILT:
conn.execute(text(f"RENAME TABLE `{table}` TO `{table}_legacy`"))
conn.commit()
create_schema()
with engine.begin() as conn:
unit_map = _copy_units(conn, legacy_units)
user_map = _copy_persons(conn, legacy_persons, unit_map)
_copy_links(conn, legacy_links, user_map)
print(f"重写引用: {_rewrite_references(conn, references, {**user_map, **unit_map})} 行")
leftover = _find_reference_columns(conn, set({**user_map, **unit_map}))
if leftover:
raise SystemExit(f"仍有残留引用,已停止:{leftover}")
if keep_legacy:
print("保留了 *_legacy 老表。")
else:
# 老表之间有外键(oa_identity_legacy -> oa_person_legacy),先关掉检查再删。
conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))
try:
for table in REBUILT:
conn.execute(text(f"DROP TABLE IF EXISTS `{table}_legacy`"))
finally:
conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))
print("迁移完成。记得跑一次 python3 scripts/dump_schema.py 刷新 sql/schema.sql")
return 0
def main() -> int:
parser = argparse.ArgumentParser(description="oa_person -> users 结构迁移")
parser.add_argument("--dry-run", action="store_true", help="只扫描并打印,不写库")
parser.add_argument("--keep-legacy", action="store_true", help="迁移后保留 *_legacy 老表")
args = parser.parse_args()
return migrate(args.dry_run, args.keep_legacy)
if __name__ == "__main__":
raise SystemExit(main())