unis-oa/backend/scripts/check_schema_bootstrap.py

224 lines
11 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
"""启动时建表补列那段(``app/db.py: create_schema``)的体检:空库建得出、拆坏补得回。
为什么要有这个脚本:这一步 2026-10 为启动提速改过两处——六步共用一份反射、
只把"模型里有、库里还没有"的表交给 ``create_all``。两处都能种出**看不见的**坏结果:
* ``create_all(tables=...)`` 要的是 Table 对象,写成表名会炸成 ``AttributeError: name``,
症状是**空白库一张表都建不出来**(真炸过一次,靠这个拆坏测试抓到);
* 六步共用一份反射之后,谁真的改了表结构就得让缓存作废,不然下一步拿着的还是旧结构:
实测关掉作废之后,``users.hub_user_id`` 补回来了、它上面的唯一索引却在那一趟被跳过,
而 ``create_schema`` 一声不吭——正是最难查的那种"少个索引,半年后才看得见"。
所以这里量两件事:空库能不能一次建出模型声明的全部表;以及删掉一张表 / 一列 /
一个索引之后,**同一趟** ``create_schema`` 能不能全补回来(列和它上面的唯一索引一起回来,
就是在验证缓存失效那道守卫)。顺带记一笔"结构已经对了"那一趟花多少条语句,别让它长回去。
只在临时库里做:脚本把自己用 ``OA_DB_NAME=<库名>_schemacheck`` 重启一次,跑完删掉。
cd backend && PYTHONPATH=. python3 scripts/check_schema_bootstrap.py
cd backend && PYTHONPATH=. python3 scripts/check_schema_bootstrap.py --keep
"""
from __future__ import annotations
import os
import sys
NAME = "unis_oa_schemacheck"
SUFFIX = "_schemacheck"
if os.environ.get("OA_DB_NAME") != NAME:
os.environ["OA_DB_NAME"] = NAME
os.environ.setdefault("OA_DB_CREATE_ALLOWLIST", NAME)
os.environ.setdefault("OA_AUTO_CREATE_SCHEMA", "true")
os.execv(sys.executable, [sys.executable, *sys.argv])
import logging # noqa: E402
from sqlalchemy import event, text # noqa: E402
import app.models # noqa: E402,F401 (注册 mapper,Base.metadata 才有表)
from app import db # noqa: E402
from app.db import engine, ensure_database # noqa: E402
logging.basicConfig(level=logging.ERROR, format="%(message)s")
#: 拆坏用的三样东西:一张表、一列(上面挂着唯一索引)、一个普通索引
VICTIM_TABLE = "oa_dict_item"
VICTIM_COLUMN_TABLE = "users"
VICTIM_COLUMN = "hub_user_id"
VICTIM_UNIQUE_INDEX = "uq_users_hub_user_id"
VICTIM_PLAIN_INDEX = "idx_users_mobile"
results: list[tuple[bool, str]] = []
counter = {"n": 0}
@event.listens_for(engine, "before_cursor_execute")
def _count(conn, cursor, statement, parameters, context, executemany): # noqa: ANN001
counter["n"] += 1
def check(ok: bool, label: str, detail: str = "") -> None:
results.append((bool(ok), label if ok else f"{label} —— {detail}"))
def db_tables() -> set[str]:
with engine.connect() as conn:
return {str(row[0]) for row in conn.execute(text("SHOW TABLES")).all()}
def db_columns(table: str) -> set[str]:
with engine.connect() as conn:
return {str(row[0]) for row in conn.execute(text(f"SHOW COLUMNS FROM `{table}`")).all()}
def db_indexes(table: str) -> set[str]:
"""库上已有的索引名。注意 SHOW INDEX 第一列是 Table,名字在 Key_name 那一列。"""
with engine.connect() as conn:
rows = conn.execute(text(f"SHOW INDEX FROM `{table}`")).all()
return {str(row._mapping["Key_name"]) for row in rows}
def structure() -> list[tuple[str, ...]]:
"""整库结构(列的类型/可空/默认/注释 + 索引的列与唯一性)。
自己写查询读 information_schema,不走被测的那套反射,免得"用错读法"这件事
既当运动员又当裁判员。
"""
with engine.connect() as conn:
columns = [tuple(str(item) for item in row) for row in conn.execute(text(
"SELECT 'C', TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, "
"IFNULL(COLUMN_DEFAULT, '~'), IFNULL(COLUMN_COMMENT, '~') "
"FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()"
)).all()]
indexes = [tuple(str(item) for item in row) for row in conn.execute(text(
"SELECT 'I', TABLE_NAME, INDEX_NAME, NON_UNIQUE, "
"GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) "
"FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() "
"GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE"
)).all()]
return sorted(columns + indexes)
def wreck() -> None:
"""删一张表、删一列(连带它上面的唯一索引)、删一个普通索引。两种读法拆得一模一样。"""
with engine.begin() as conn:
conn.execute(text(f"DROP TABLE IF EXISTS `{VICTIM_TABLE}`"))
conn.execute(text(f"ALTER TABLE `{VICTIM_COLUMN_TABLE}` DROP COLUMN `{VICTIM_COLUMN}`"))
conn.execute(text(f"DROP INDEX `{VICTIM_PLAIN_INDEX}` ON `{VICTIM_COLUMN_TABLE}`"))
def run_schema(label: str, reflection: str | None = None) -> tuple[list[str], int]:
"""跑一趟 create_schema,把"这一步自己花了几条语句"当场记下来。
必须在这里就地取数:外面还要读 SHOW COLUMNS / SHOW INDEX 对结构,那些也算语句,
事后翻 counter 会把对结构的花费算到建表头上(第一版就这么误报过一次超预算)。
"""
counter["n"] = 0
problems = list(db.create_schema(reflection))
stmts = counter["n"]
print(f" {label}: {stmts} 条语句,问题 {problems if problems else '无'}")
return problems, stmts
def main() -> int:
keep = "--keep" in sys.argv
ensure_database()
with engine.connect() as conn:
# 清临时库这一段不看外键:SHOW TABLES 是字母序,子表排在父表后面就删不掉。
# 中途失败会留下临时库(末尾 DROP DATABASE 不在 finally 里),下一趟就会被
# oa_auth_role / oa_person_auth_role 这类父子表卡死,报的还是一句不相干的 3730。
conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))
for row in conn.execute(text("SHOW TABLES")).fetchall():
conn.execute(text(f"DROP TABLE IF EXISTS `{row[0]}`"))
conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))
conn.commit()
wanted_tables = set(db.Base.metadata.tables)
check(len(wanted_tables) >= 40, "模型里注册了表", f"只有 {len(wanted_tables)} 张")
# 1) 空白库一次建齐:这里断的就是"create_all 收到了对的东西"
problems, _ = run_schema("空白库建结构")
built = db_tables()
check(not problems, "空库建结构一步都没失败", "; ".join(problems))
check(wanted_tables <= built, "模型声明的表全建出来了",
f"缺 {sorted(wanted_tables - built)[:5]}")
for table in sorted(wanted_tables):
model_columns = set(db.Base.metadata.tables[table].columns.keys())
missing = model_columns - db_columns(table)
if missing:
check(False, f"{table} 列建齐", f"缺 {sorted(missing)[:5]}")
break
else:
check(True, "每张表的列都按模型建出来了")
# 2) 结构已经对了:这一趟不该再动任何东西,语句条数也只够反射一次
before = {table: (db_columns(table), db_indexes(table)) for table in sorted(wanted_tables)}
problems, stmts = run_schema("结构已对,再跑一趟")
after = {table: (db_columns(table), db_indexes(table)) for table in sorted(wanted_tables)}
check(not problems, "第二趟没报失败", "; ".join(problems))
check(before == after, "第二趟一行结构都没改")
# 预算按"一次读齐"定:3 条读结构 + 几条收尾。退回每表一个 SHOW CREATE TABLE
# 就是 65 条,这条断言会立刻把它抓住(要临时放宽用 OA_SCHEMA_CHECK_BUDGET)。
budget = int(os.environ.get("OA_SCHEMA_CHECK_BUDGET", "12"))
check(stmts <= budget, f"结构已对时一趟 {stmts} 条语句(预算 {budget})",
f"超出 {stmts - budget} 条:又变成每步各反射一遍了")
# 3) 拆坏:删一张表、删一列(连带它上面的唯一索引一起没了)、删一个普通索引
wreck()
check(VICTIM_TABLE not in db_tables(), "拆坏:表已删掉")
check(VICTIM_COLUMN not in db_columns(VICTIM_COLUMN_TABLE), "拆坏:列已删掉")
check(VICTIM_PLAIN_INDEX not in db_indexes(VICTIM_COLUMN_TABLE), "拆坏:索引已删掉")
# 4) 同一趟补回来。列与它上面的唯一索引一起回来 = 缓存失效那道守卫还在起作用:
# 补列之后若不作废反射,补索引那步看不到新列,就会把唯一索引悄悄跳过。
problems, _ = run_schema("拆坏之后补结构")
tables = db_tables()
columns_now = db_columns(VICTIM_COLUMN_TABLE)
indexes_now = db_indexes(VICTIM_COLUMN_TABLE)
check(not problems, "补结构一步都没失败", "; ".join(problems))
check(VICTIM_TABLE in tables, f"{VICTIM_TABLE} 建回来了")
check(VICTIM_COLUMN in columns_now, f"{VICTIM_COLUMN_TABLE}.{VICTIM_COLUMN} 补回来了")
check(VICTIM_UNIQUE_INDEX in indexes_now,
f"同一趟里 {VICTIM_UNIQUE_INDEX} 也建好了(反射缓存已作废)")
check(VICTIM_PLAIN_INDEX in indexes_now, f"{VICTIM_PLAIN_INDEX} 建回来了")
# 5) 补完之后整库结构仍要等于模型
problems, _ = run_schema("补完之后再跑一趟")
check(not problems, "补完之后没有遗留问题", "; ".join(problems))
check(wanted_tables <= db_tables(), "表还是齐的")
# 6) 一次读齐 vs 逐表反射:同一份拆坏,两种读法必须补出同一个结构。
# (per_table 是 [database] schema_reflection 留给"information_schema 看不清"
# 那种环境的退路,退路必须和主路一样修得干净。)
wreck()
problems, _ = run_schema("per_table 读法补结构", "per_table")
check(not problems, "per_table 那趟也没报失败", "; ".join(problems))
by_per_table = structure()
wreck()
problems, _ = run_schema("bulk 读法补结构", "bulk")
check(not problems, "bulk 那趟也没报失败", "; ".join(problems))
by_bulk = structure()
diff = [row for row in set(by_bulk) ^ set(by_per_table)]
check(by_bulk == by_per_table, "两种反射读法补出来的结构一字不差",
f"差异 {len(diff)} 处,头两处:{diff[:2]}")
if not keep:
assert NAME.endswith(SUFFIX) and os.environ["OA_DB_NAME"] == NAME, "拒绝删不是临时库的名字"
with engine.connect() as conn:
conn.execute(text(f"DROP DATABASE `{NAME}`"))
conn.commit()
print(f"临时库 {NAME} 已删除")
else:
print(f"按 --keep 保留临时库 {NAME},记得自己删")
passed = sum(1 for ok, _ in results if ok)
for ok, label in results:
print((" ok " if ok else " FAIL ") + label)
print(f"\n建表补列体检:{passed}/{len(results)} 项通过")
return 0 if passed == len(results) else 1
if __name__ == "__main__":
raise SystemExit(main())