forked from mtusi2026/mtusi2026lab2
147 lines
4.2 KiB
Python
147 lines
4.2 KiB
Python
"""PostgreSQL access: small bounded connection pool, versioned migrations, row helpers."""
|
|
import logging
|
|
import os
|
|
import queue
|
|
from contextlib import contextmanager
|
|
from datetime import timezone
|
|
from pathlib import Path
|
|
|
|
import psycopg
|
|
from psycopg.rows import dict_row
|
|
|
|
log = logging.getLogger("messenger.db")
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
MIGRATIONS = ROOT / "migrations"
|
|
|
|
|
|
def _read_secret(path):
|
|
if not path:
|
|
return None
|
|
value = Path(path).read_text(encoding="utf-8").strip()
|
|
if not value:
|
|
raise RuntimeError("secret file is empty: " + path)
|
|
return value
|
|
|
|
|
|
def database_url():
|
|
"""Prefer a mounted secret file; fall back to an environment variable for local runs."""
|
|
url = _read_secret(os.environ.get("DATABASE_URL_FILE"))
|
|
if url:
|
|
return url
|
|
url = os.environ.get("DATABASE_URL")
|
|
if not url:
|
|
raise RuntimeError("DATABASE_URL or DATABASE_URL_FILE is required")
|
|
return url
|
|
|
|
|
|
class Database:
|
|
"""A tiny FIFO pool. Each request borrows one connection and returns it."""
|
|
|
|
def __init__(self):
|
|
self._pool = None
|
|
self.dsn = None
|
|
|
|
def start(self):
|
|
self.dsn = database_url()
|
|
size = int(os.environ.get("DB_POOL_MAX", "8"))
|
|
self._pool = queue.Queue(maxsize=size)
|
|
for _ in range(size):
|
|
self._pool.put(self._connect())
|
|
self.migrate()
|
|
|
|
def _connect(self):
|
|
return psycopg.connect(self.dsn, connect_timeout=3, row_factory=dict_row, autocommit=True)
|
|
|
|
def stop(self):
|
|
if self._pool is None:
|
|
return
|
|
while not self._pool.empty():
|
|
try:
|
|
self._pool.get_nowait().close()
|
|
except queue.Empty:
|
|
break
|
|
self._pool = None
|
|
|
|
@contextmanager
|
|
def connection(self):
|
|
conn = self._pool.get(timeout=5)
|
|
try:
|
|
yield conn
|
|
finally:
|
|
self._pool.put(conn)
|
|
|
|
@contextmanager
|
|
def transaction(self):
|
|
with self.connection() as conn:
|
|
# Explicit BEGIN/COMMIT; rollback on any exception.
|
|
conn.execute("BEGIN")
|
|
try:
|
|
yield conn
|
|
except BaseException:
|
|
conn.execute("ROLLBACK")
|
|
raise
|
|
else:
|
|
conn.execute("COMMIT")
|
|
|
|
def ready(self):
|
|
try:
|
|
with self.connection() as conn:
|
|
conn.execute("SELECT 1").fetchone()
|
|
return True
|
|
except Exception:
|
|
log.exception("readiness check failed")
|
|
return False
|
|
|
|
def migrate(self):
|
|
"""Apply *.sql in name order, at most once each; restart-safe with the same volume."""
|
|
with self.transaction() as conn:
|
|
conn.execute(
|
|
"CREATE TABLE IF NOT EXISTS schema_migrations ("
|
|
"name TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp())"
|
|
)
|
|
applied = {r["name"] for r in conn.execute("SELECT name FROM schema_migrations").fetchall()}
|
|
for path in sorted(MIGRATIONS.glob("*.sql")):
|
|
if path.name in applied:
|
|
continue
|
|
conn.execute(path.read_text(encoding="utf-8"))
|
|
conn.execute("INSERT INTO schema_migrations(name) VALUES (%s)", (path.name,))
|
|
log.info("applied migration %s", path.name)
|
|
|
|
|
|
db = Database()
|
|
|
|
|
|
def utc_text(value):
|
|
"""RFC 3339 UTC with an explicit Z and microsecond precision."""
|
|
value = value.astimezone(timezone.utc)
|
|
return value.strftime("%Y-%m-%dT%H:%M:%S.%fZ")
|
|
|
|
|
|
def user_public(row):
|
|
return {
|
|
"id": str(row["id"]),
|
|
"username": row["username"],
|
|
"display_name": row["display_name"],
|
|
"created_at": utc_text(row["created_at"]),
|
|
}
|
|
|
|
|
|
def chat_public(row):
|
|
return {
|
|
"id": str(row["id"]),
|
|
"title": row["title"],
|
|
"member_ids": [str(x) for x in (row["member_ids"] or [])],
|
|
"created_at": utc_text(row["created_at"]),
|
|
}
|
|
|
|
|
|
def message_public(row):
|
|
return {
|
|
"id": str(row["id"]),
|
|
"chat_id": str(row["chat_id"]),
|
|
"sender_id": str(row["sender_id"]),
|
|
"text": row["text"],
|
|
"created_at": utc_text(row["created_at"]),
|
|
}
|