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