118 lines
4.5 KiB
Python
118 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
|
"""Apply pending SQL migrations to the accounts database.
|
|
|
|
This project has no Alembic. SQLAlchemy ``create_all()`` only creates *missing
|
|
tables*, so column additions to the long-lived accounts DB are done with the
|
|
hand-written ``.sql`` files in ``deploy/migrations/``. This runner applies the
|
|
ones a given database hasn't seen yet and records them in a ``schema_migrations``
|
|
table, so it is safe (and cheap) to run on every deploy.
|
|
|
|
It resolves the database exactly as the app does (``db.py``):
|
|
``THERMOGRAPH_ACCOUNTS_DB`` if set, otherwise ``<repo>/data/accounts.sqlite``.
|
|
|
|
Fresh database: the app's ``create_all()`` builds the *current* schema on first
|
|
start, so there is nothing to ALTER. This runner detects that (no file yet, or no
|
|
``user`` table) and records the existing migrations as a baseline instead of
|
|
replaying them, so they never run against a schema that already has the columns.
|
|
|
|
Usage (wired into deploy.sh / deploy-dev.sh, but runnable by hand too):
|
|
|
|
/opt/thermograph/.venv/bin/python deploy/migrate-db.py
|
|
"""
|
|
import glob
|
|
import os
|
|
import shutil
|
|
import sqlite3
|
|
import sys
|
|
import time
|
|
|
|
HERE = os.path.dirname(os.path.abspath(__file__))
|
|
REPO = os.path.dirname(HERE)
|
|
MIGRATIONS_DIR = os.path.join(HERE, "migrations")
|
|
DB_PATH = os.environ.get("THERMOGRAPH_ACCOUNTS_DB") or os.path.join(REPO, "data", "accounts.sqlite")
|
|
|
|
|
|
def _applied(conn: sqlite3.Connection) -> set[str]:
|
|
conn.execute(
|
|
"CREATE TABLE IF NOT EXISTS schema_migrations ("
|
|
"name TEXT PRIMARY KEY, applied_at TEXT NOT NULL)"
|
|
)
|
|
return {row[0] for row in conn.execute("SELECT name FROM schema_migrations")}
|
|
|
|
|
|
def _record(conn: sqlite3.Connection, name: str) -> None:
|
|
conn.execute(
|
|
"INSERT OR IGNORE INTO schema_migrations(name, applied_at) "
|
|
"VALUES (?, datetime('now'))",
|
|
(name,),
|
|
)
|
|
|
|
|
|
def _has_user_table(conn: sqlite3.Connection) -> bool:
|
|
row = conn.execute(
|
|
"SELECT 1 FROM sqlite_master WHERE type='table' AND name='user'"
|
|
).fetchone()
|
|
return row is not None
|
|
|
|
|
|
def main() -> None:
|
|
migrations = sorted(glob.glob(os.path.join(MIGRATIONS_DIR, "*.sql")))
|
|
if not migrations:
|
|
print("migrate-db: no migrations found")
|
|
return
|
|
|
|
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
|
|
fresh = not os.path.exists(DB_PATH)
|
|
conn = sqlite3.connect(DB_PATH)
|
|
try:
|
|
applied = _applied(conn)
|
|
pending = [m for m in migrations if os.path.basename(m) not in applied]
|
|
if not pending:
|
|
print(f"migrate-db: up to date ({len(applied)} applied) -> {DB_PATH}")
|
|
return
|
|
|
|
# Fresh DB (or one whose accounts schema hasn't been built yet): the app's
|
|
# create_all() will produce the current schema on next start, so record the
|
|
# existing migrations as a baseline rather than ALTERing a table that isn't
|
|
# there. Only genuinely older databases need the ALTERs replayed.
|
|
if fresh or not _has_user_table(conn):
|
|
for m in pending:
|
|
_record(conn, os.path.basename(m))
|
|
conn.commit()
|
|
print(f"migrate-db: fresh database, baselined {len(pending)} migration(s) -> {DB_PATH}")
|
|
return
|
|
|
|
# These are the authoritative, non-regenerable accounts. Back the DB up
|
|
# before touching it, next to the DB with a timestamp.
|
|
backup = f"{DB_PATH}.bak-{time.strftime('%Y%m%d-%H%M%S')}"
|
|
shutil.copy2(DB_PATH, backup)
|
|
print(f"migrate-db: backed up -> {backup}")
|
|
|
|
for m in pending:
|
|
name = os.path.basename(m)
|
|
with open(m, encoding="utf-8") as f:
|
|
sql = f.read()
|
|
try:
|
|
conn.executescript(sql)
|
|
except sqlite3.OperationalError as e:
|
|
msg = str(e).lower()
|
|
# Idempotent: a column/index this migration adds already exists
|
|
# (applied by hand before, or created fresh from the model). Treat
|
|
# as already-applied and record it; re-raise anything unexpected.
|
|
if "duplicate column" in msg or "already exists" in msg:
|
|
print(f"migrate-db: {name} already present in schema, recording")
|
|
else:
|
|
conn.rollback()
|
|
print(f"migrate-db: FAILED on {name}: {e}", file=sys.stderr)
|
|
raise
|
|
_record(conn, name)
|
|
conn.commit()
|
|
print(f"migrate-db: applied {name}")
|
|
|
|
print(f"migrate-db: done ({len(pending)} newly recorded) -> {DB_PATH}")
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|