unis_hrms/app/db/schema.py

70 lines
2.7 KiB
Python

"""开发期轻量结构同步。
SQLite 阶段用 ``create_all`` 建表最省事,但它只会「建缺失的表」,不会给已存在的表
补新列——于是新增一个模型字段后,本地库必须手工删库重建,否则查询直接 500。
本模块只补**可空、非唯一、非主键**的缺失列(属于向后兼容变更:不删列、不改类型、
不改索引),让本地 SQLite 库能跟随模型演进。
MySQL 生产环境请仍然使用 alembic 正式迁移;这里执行前会打印告警,方便回查。
"""
from __future__ import annotations
import logging
from sqlalchemy import inspect, text
from sqlalchemy.engine import Connection
from .base import Base
log = logging.getLogger("hrms.schema")
def _skip_reason(column) -> str | None:
if column.primary_key:
return "主键"
if not column.nullable:
return "非空列需要显式默认值"
if column.unique:
return "唯一列需要去重"
if column.foreign_keys:
return "外键列需单独建约束"
return None
def sync_missing_columns(sync_conn: Connection) -> list[str]:
"""给已存在的表补上模型里新增的可空列,返回补过的列名列表。"""
inspector = inspect(sync_conn)
dialect = sync_conn.dialect
added: list[str] = []
for table_name, table in Base.metadata.tables.items():
if not inspector.has_table(table_name):
continue
existing = {c["name"].lower() for c in inspector.get_columns(table_name)}
for column in table.columns:
if column.name.lower() in existing or _skip_reason(column):
continue
sql_type = column.type.compile(dialect=dialect)
sync_conn.execute(text(
f"ALTER TABLE {table_name} ADD COLUMN {column.name} {sql_type} NULL"))
added.append(f"{table_name}.{column.name}")
return added
async def sync_schema(engine, *, is_sqlite: bool) -> None:
"""建表 + 补列;补列失败只告警,不阻断启动(避免权限不足时整个服务起不来)。"""
from sqlalchemy.ext.asyncio import AsyncEngine
assert isinstance(engine, AsyncEngine)
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
try:
added = await conn.run_sync(sync_missing_columns)
except Exception as exc: # pragma: no cover - 取决于数据库权限
log.warning("自动补列跳过:%s(正式环境请使用 alembic 迁移)", exc)
return
if added:
log.warning("自动补齐缺失列 %d 个:%s", len(added), ", ".join(added[:12]))
if not is_sqlite:
log.warning("非 SQLite 环境执行了自动补列,请尽快改用 alembic 正式迁移")