291 lines
12 KiB
Python
291 lines
12 KiB
Python
#!/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())
|