#!/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 (/scripts, app code in /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))