cosmo/scripts/upgrade_db.py

171 lines
7.4 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
"""
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))