171 lines
7.4 KiB
Python
171 lines
7.4 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
Schema upgrade script for an existing Cosmo database.
|
||
|
||
The backend runs `Base.metadata.create_all()` on every boot, which creates tables
|
||
added since the first deploy but never touches tables that already exist. Columns
|
||
added to the ORM models afterwards stay missing on the server. This script diffs the
|
||
ORM metadata against the live database and applies what is missing:
|
||
|
||
- missing tables (with their indexes and constraints, via create_all)
|
||
- missing columns on existing tables (ADD COLUMN IF NOT EXISTS)
|
||
- missing indexes on existing tables (CREATE INDEX IF NOT EXISTS)
|
||
|
||
It is additive only: existing columns, types, constraints and data are never changed
|
||
or dropped. Data (new rocket models, system settings, ...) is out of scope here.
|
||
|
||
Usage:
|
||
python scripts/upgrade_db.py # dry run, print the DDL
|
||
python scripts/upgrade_db.py --apply # execute it
|
||
"""
|
||
import asyncio
|
||
import logging
|
||
import os
|
||
import sys
|
||
from pathlib import Path
|
||
|
||
# Runs from the repo (<project>/scripts, app code in <project>/backend) and from
|
||
# inside the backend image (deploy.sh copies it to /app/scripts, app code in /app).
|
||
BACKEND_DIR = Path(__file__).resolve().parent.parent / "backend"
|
||
if not (BACKEND_DIR / "app").is_dir():
|
||
BACKEND_DIR = Path(__file__).resolve().parent.parent
|
||
sys.path.insert(0, str(BACKEND_DIR))
|
||
os.chdir(BACKEND_DIR)
|
||
|
||
from sqlalchemy import inspect, text
|
||
from sqlalchemy.schema import CreateIndex, CreateTable
|
||
|
||
from app.database import engine, Base
|
||
from app.config import settings
|
||
import app.models.db # noqa: F401 (register every model on Base.metadata)
|
||
|
||
logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(message)s")
|
||
logger = logging.getLogger(__name__)
|
||
|
||
|
||
def column_stmt(table_name: str, col, dialect, strict: bool) -> str:
|
||
"""ALTER TABLE ... ADD COLUMN for one missing column.
|
||
|
||
strict=False drops NOT NULL, used as fallback when the table already has rows
|
||
and the model gives no default Postgres could fill in.
|
||
"""
|
||
sql = (f'ALTER TABLE "{table_name}" ADD COLUMN IF NOT EXISTS "{col.name}" '
|
||
f'{col.type.compile(dialect=dialect)}')
|
||
if strict and not col.nullable:
|
||
sql += " NOT NULL"
|
||
default = col.server_default.arg if col.server_default is not None else None
|
||
if default is None and col.default is not None and not col.default.is_callable:
|
||
default = col.default.arg
|
||
if hasattr(default, "compile"): # func.now() and friends
|
||
sql += " DEFAULT CURRENT_TIMESTAMP"
|
||
elif isinstance(default, str):
|
||
sql += " DEFAULT '" + default.replace("'", "''") + "'"
|
||
elif isinstance(default, bool):
|
||
sql += " DEFAULT " + ("TRUE" if default else "FALSE")
|
||
elif default is not None:
|
||
sql += f" DEFAULT {default}"
|
||
return sql
|
||
|
||
|
||
def diff(conn):
|
||
"""Compare ORM metadata with the live schema; returns (changes, notes)."""
|
||
live = inspect(conn)
|
||
tables = live.get_table_names()
|
||
if not tables:
|
||
return [], ["空库:后端首次启动会自己建全部表,无需本脚本。"]
|
||
|
||
changes, notes = [], []
|
||
for name, table in Base.metadata.tables.items():
|
||
if name not in tables:
|
||
changes.append({"kind": "table", "label": name,
|
||
"sql": str(CreateTable(table).compile(dialect=conn.dialect))})
|
||
continue
|
||
|
||
existing = {c["name"] for c in live.get_columns(name)}
|
||
for col in table.columns:
|
||
if col.name in existing:
|
||
continue
|
||
changes.append({
|
||
"kind": "column", "label": f"{name}.{col.name}",
|
||
"sql": column_stmt(name, col, conn.dialect, strict=True),
|
||
"fallback": column_stmt(name, col, conn.dialect, strict=False),
|
||
})
|
||
|
||
pk_cols = {c.name for c in table.primary_key}
|
||
# Compare by covered columns, not only by name: the hand-written init_db.sql
|
||
# names indexes idx_*, the ORM declares ix_*, so a name-only check wants to
|
||
# add a duplicate index over identical columns on every existing database.
|
||
covered = set()
|
||
for item in live.get_indexes(name):
|
||
if item.get("column_names"):
|
||
covered.add((tuple(item["column_names"]), bool(item.get("unique"))))
|
||
for item in live.get_unique_constraints(name):
|
||
if item.get("column_names"):
|
||
covered.add((tuple(item["column_names"]), True))
|
||
live_names = {i["name"] for i in live.get_indexes(name)}
|
||
for index in table.indexes:
|
||
cols = tuple(c.name for c in index.columns)
|
||
if set(cols) <= pk_cols:
|
||
continue # the primary key already covers single-PK indexes
|
||
if index.name in live_names or (cols, bool(index.unique)) in covered:
|
||
continue # same columns already indexed (unique constraint counts)
|
||
sql = str(CreateIndex(index, if_not_exists=True).compile(dialect=conn.dialect))
|
||
changes.append({"kind": "index", "label": index.name, "sql": sql})
|
||
|
||
# Missing foreign keys on existing tables are reported, not applied: they can
|
||
# fail on orphan rows and silently lock out writes.
|
||
for name, table in Base.metadata.tables.items():
|
||
if name not in tables:
|
||
continue
|
||
live_fk_cols = {c for fk in live.get_foreign_keys(name) for c in fk["constrained_columns"]}
|
||
for fk in table.foreign_keys:
|
||
if fk.parent.name not in live_fk_cols:
|
||
notes.append(f"{name}: 缺少外键 {fk.parent.name} -> {fk.target_fullname}"
|
||
f"(脚本不加外键,确认数据干净后手工执行)")
|
||
return changes, notes
|
||
|
||
|
||
async def main(apply: bool):
|
||
logger.info(f"Database: {settings.database_url.split('@')[-1]}")
|
||
|
||
async with engine.connect() as conn:
|
||
changes, notes = await conn.run_sync(diff)
|
||
|
||
for change in changes:
|
||
logger.info(f"[{change['kind']}] {change['label']}")
|
||
logger.info(" " + " ".join(change["sql"].split()))
|
||
if not changes:
|
||
logger.info("✓ Schema is up to date, nothing to do")
|
||
for note in notes:
|
||
logger.warning(f"! {note}")
|
||
|
||
if not changes or not apply:
|
||
if changes:
|
||
logger.info("Dry run only. Re-run with --apply to execute.")
|
||
return
|
||
|
||
async with engine.begin() as conn:
|
||
for change in changes:
|
||
try:
|
||
# SAVEPOINT per statement: one failure must not abort the whole upgrade.
|
||
async with conn.begin_nested():
|
||
if change["kind"] == "table":
|
||
await conn.run_sync(lambda c, t=change["label"]: Base.metadata.create_all(
|
||
c, tables=[Base.metadata.tables[t]], checkfirst=True))
|
||
else:
|
||
await conn.execute(text(change["sql"]))
|
||
logger.info(f"✓ {change['kind']} {change['label']} applied")
|
||
except Exception as e:
|
||
if change["kind"] != "column":
|
||
raise
|
||
async with conn.begin_nested():
|
||
await conn.execute(text(change["fallback"]))
|
||
logger.warning(f"! {change['label']}: 已按 NULL 建列(原表有数据,NOT NULL 填不出默认值),"
|
||
f"请回填后再收紧 ({e})")
|
||
|
||
logger.info("✓ Schema upgrade finished")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
asyncio.run(main("--apply" in sys.argv))
|