9ff9ec27e9
- migrations/001_initial.sql: full schema with 12 tables, triggers, indexes - migrate.py: idempotent migration runner with version tracking - README.md: deploy instructions, architecture overview - .gitignore: exclude migration tracking data
86 lines
2.4 KiB
Python
Executable File
86 lines
2.4 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""
|
|
Jetour BI — Database Migration Runner
|
|
|
|
Usage:
|
|
python migrate.py [DB_URL]
|
|
|
|
Defaults to DATABASE_URL env var, or:
|
|
postgresql://postgres:password@host:5432/jetour-bi
|
|
|
|
Runs all .sql files in migrations/ in order, tracking applied
|
|
migrations in a _migrations table. Safe to run repeatedly —
|
|
only unapplied migrations are executed.
|
|
"""
|
|
|
|
import os, sys, re, psycopg
|
|
from pathlib import Path
|
|
|
|
MIGRATIONS_DIR = Path(__file__).parent / "migrations"
|
|
DB_URL = sys.argv[1] if len(sys.argv) > 1 else os.environ.get(
|
|
"DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/jetour-bi"
|
|
)
|
|
|
|
|
|
def main():
|
|
conn = psycopg.connect(DB_URL)
|
|
conn.autocommit = True
|
|
cur = conn.cursor()
|
|
|
|
# Ensure tracking table exists
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS _migrations (
|
|
id SERIAL PRIMARY KEY,
|
|
filename VARCHAR(200) NOT NULL UNIQUE,
|
|
applied_at TIMESTAMPTZ DEFAULT NOW()
|
|
)
|
|
""")
|
|
|
|
# Get list of applied migrations
|
|
cur.execute("SELECT filename FROM _migrations ORDER BY id")
|
|
applied = {r[0] for r in cur.fetchall()}
|
|
|
|
# Find and run pending .sql files
|
|
sql_files = sorted(MIGRATIONS_DIR.glob("*.sql"))
|
|
if not sql_files:
|
|
print("No migration files found in", MIGRATIONS_DIR)
|
|
return
|
|
|
|
pending = 0
|
|
for f in sql_files:
|
|
if f.name in applied:
|
|
continue
|
|
print(f" Applying: {f.name} ...", end=" ", flush=True)
|
|
try:
|
|
sql = f.read_text()
|
|
cur.execute(sql)
|
|
cur.execute("INSERT INTO _migrations (filename) VALUES (%s)", (f.name,))
|
|
print("✓")
|
|
pending += 1
|
|
except Exception as e:
|
|
print(f"✗ {e}")
|
|
conn.rollback()
|
|
sys.exit(1)
|
|
|
|
if pending:
|
|
print(f"\n✅ {pending} migration(s) applied")
|
|
else:
|
|
print("\n✓ All migrations already applied")
|
|
|
|
# Create a default admin user if none exists
|
|
cur.execute("SELECT count(*) FROM dashboard_users")
|
|
if cur.fetchone()[0] == 0:
|
|
import bcrypt
|
|
pw = bcrypt.hashpw(b"admin", bcrypt.gensalt()).decode()
|
|
cur.execute(
|
|
"INSERT INTO dashboard_users (username, email, password_hash, role) VALUES (%s, %s, %s, %s)",
|
|
("admin", "admin@jetour.mu", pw, "admin"),
|
|
)
|
|
print("✓ Default admin user created (username: admin, password: admin)")
|
|
|
|
conn.close()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|