File size: 5,422 Bytes
5aaeeaa a1f2800 aa22f66 a1f2800 aa22f66 a1f2800 aa22f66 a1f2800 04928f4 931b35e 5aaeeaa 04928f4 5aaeeaa 04928f4 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | """
Separate database configuration for GCP Cloud SQL PostgreSQL.
Instance : larmedix-db
Database : larmedix
Priority order for connection:
1. DATABASE_URL env var (recommended for HuggingFace Secrets / GCP)
e.g. postgresql+psycopg2://USER:PASSWORD@HOST:5432/larmedix
2. Cloud SQL Python Connector (if USE_CLOUD_SQL_CONNECTOR=true)
Requires: INSTANCE_CONNECTION_NAME="PROJECT:REGION:larmedix-db"
3. TCP / Unix-socket from individual env vars:
DB_USER, DB_PASSWORD, DB_HOST (public IP or 127.0.0.1 via proxy),
DB_PORT (default 5432), DB_NAME (default larmedix),
DB_UNIX_SOCKET (e.g. /cloudsql/PROJECT:REGION:larmedix-db)
4. Local SQLite fallback (dev only): sqlite:///hospital.db
On first successful connection, init_database(app) automatically
creates all tables (db.create_all()) and seeds the superadmin user.
We will plug real GCP credentials in later via env vars / secrets —
no code change needed.
"""
import os
# ---- GCP defaults (instance / database names are fixed) ----
GCP_INSTANCE_NAME = os.environ.get("GCP_INSTANCE_NAME", "larmedix-db")
DEFAULT_DB_NAME = os.environ.get("DB_NAME", "larmedix")
def get_database_uri():
"""Build the SQLAlchemy database URI from environment."""
# 1. Full URL wins (HF Space Secrets -> DATABASE_URL)
database_url = os.environ.get("DATABASE_URL")
if database_url:
# Some providers give postgres:// -> fix to postgresql://
if database_url.startswith("postgres://"):
database_url = database_url.replace("postgres://", "postgresql://", 1)
# Default to psycopg2 driver if no driver specified
if database_url.startswith("postgresql://"):
database_url = database_url.replace(
"postgresql://", "postgresql+psycopg2://", 1
)
return database_url
# 2. Individual parts -> build TCP URL (Cloud SQL public IP / proxy)
db_user = os.environ.get("DB_USER")
db_password = os.environ.get("DB_PASSWORD", "")
db_host = os.environ.get("DB_HOST") # Cloud SQL public IP
db_port = os.environ.get("DB_PORT", "5432")
db_name = os.environ.get("DB_NAME", DEFAULT_DB_NAME)
# Unix socket path (Cloud Run / GCE with cloud-sql-proxy):
# e.g. /cloudsql/PROJECT:REGION:larmedix-db
unix_socket = os.environ.get("DB_UNIX_SOCKET")
if db_user and unix_socket:
# Connect via Unix domain socket
return (
f"postgresql+psycopg2://{db_user}:{db_password}@/{db_name}"
f"?host={unix_socket}"
)
if db_user and db_host:
return (
f"postgresql+psycopg2://{db_user}:{db_password}"
f"@{db_host}:{db_port}/{db_name}"
)
# 3. Local fallback (dev / HF build without secrets)
return "sqlite:///hospital.db"
def is_postgres(uri: str) -> bool:
return uri.startswith("postgresql") or uri.startswith("postgres")
def get_engine_options(uri: str) -> dict:
"""Sensible pool defaults for Cloud SQL Postgres."""
if is_postgres(uri):
return {
"pool_size": int(os.environ.get("DB_POOL_SIZE", "5")),
"max_overflow": int(os.environ.get("DB_MAX_OVERFLOW", "10")),
"pool_timeout": 30,
"pool_recycle": 1800,
"pool_pre_ping": True,
}
# SQLite (local dev)
return {"connect_args": {"timeout": 30}}
def init_database(app):
"""
Call once inside app context after db.init_app(app).
Automatically creates all tables on first connect + seeds superadmin.
Safe to call on every startup (create_all is idempotent).
"""
# Import here to avoid circular imports (models defines db)
from models import db, User
from werkzeug.security import generate_password_hash
with app.app_context():
db.create_all()
# Auto-migrate: older Postgres DBs have user.password VARCHAR(150),
# but Werkzeug scrypt hashes are ~160+ chars. Widen if needed.
# Also widen filepath columns for Firebase Storage URLs.
try:
from sqlalchemy import text
if is_postgres(str(db.engine.url)):
with db.engine.begin() as conn:
conn.execute(text(
'ALTER TABLE "user" ALTER COLUMN password TYPE VARCHAR(500)'
))
conn.execute(text(
'ALTER TABLE report ALTER COLUMN filepath TYPE VARCHAR(1024)'
))
conn.execute(text(
'ALTER TABLE patient_profile ALTER COLUMN insurance_document TYPE VARCHAR(1024)'
))
except Exception as e:
print(f"column migrate skipped: {e}")
# Superadmin lives in the DB (User table, role=superadmin).
# Seeded once with defaults; afterwards DB is source of truth.
# Changing Secrets will NOT overwrite it — edit via SQL/app.
sa = User.query.filter_by(role="superadmin").first()
if not sa:
db.session.add(
User(
email="superadmin@system.com",
username="superadmin",
password=generate_password_hash("superadmin123"),
role="superadmin",
hospital_id=None,
)
)
db.session.commit()
print("Superadmin seeded (superadmin@system.com).")
|