unis-oa/backend/scripts/dump_schema.py

61 lines
1.8 KiB
Python

#!/usr/bin/env python3
"""Export the live MySQL structure to sql/schema.sql.
Run after changing ``app/models.py`` so the checked-in snapshot stays truthful:
python3 scripts/dump_schema.py
"""
from __future__ import annotations
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.db import engine, settings # noqa: E402
HEADER = """-- UnisOA 流程审批平台 -- MySQL 结构快照
-- 来源: {host}:{port}/{database}
-- 导出时间: {stamp}
--
-- 说明: 后端启动时会自动建库建表 (backend/app/db.py),本文件用于人工审阅、手工初始化,
-- 或在无法自动建表的环境中直接执行。
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
CREATE DATABASE IF NOT EXISTS `{database}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `{database}`;
"""
def main() -> int:
url = engine.url
out = [
HEADER.format(
host=url.host,
port=url.port or 3306,
database=url.database,
stamp=datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
)
]
with engine.connect() as conn:
tables = sorted(conn.execute(text("SHOW TABLES")).scalars().all())
for name in tables:
ddl = conn.execute(text(f"SHOW CREATE TABLE `{name}`")).mappings().one()["Create Table"]
out.append(f"DROP TABLE IF EXISTS `{name}`;")
out.append(ddl.rstrip(";") + ";\n")
out.append("SET FOREIGN_KEY_CHECKS = 1;")
target = Path(__file__).resolve().parents[2] / "sql" / "schema.sql"
target.write_text("\n".join(out) + "\n", encoding="utf-8")
print(f"{target}: {len(tables)} tables")
return 0
if __name__ == "__main__":
raise SystemExit(main())