Files

214 lines
7.7 KiB
Python

import logging
import time
from contextlib import contextmanager
from datetime import datetime
from uuid import UUID, uuid4
import psycopg
from psycopg import errors as pg_errors
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool, PoolTimeout
import settings
from errors import bad_request, conflict, not_found, unavailable
log = logging.getLogger("messenger.db")
pool = ConnectionPool(
conninfo=settings.DATABASE_URL,
min_size=settings.DB_POOL_MIN,
max_size=settings.DB_POOL_MAX,
timeout=settings.DB_POOL_WAIT_SECONDS,
kwargs={
"connect_timeout": settings.DB_CONNECT_TIMEOUT_SECONDS,
"row_factory": dict_row,
"options": f"-c statement_timeout={settings.DB_STATEMENT_TIMEOUT_MS}"
" -c idle_in_transaction_session_timeout=10000 -c TimeZone=UTC",
},
check=ConnectionPool.check_connection,
open=False,
)
@contextmanager
def connection(wait_seconds: float | None = None):
"""Соединение из пула. В конце блока COMMIT, при исключении ROLLBACK.
Если база недоступна, клиент получает 503 без подробностей.
"""
try:
with pool.connection(timeout=wait_seconds) as conn:
yield conn
except (psycopg.OperationalError, PoolTimeout) as exc:
log.warning("PostgreSQL is unavailable: %s", type(exc).__name__)
raise unavailable() from None
def ping() -> None:
with connection(wait_seconds=1) as conn:
conn.execute("SELECT 1")
MIGRATIONS_LOCK = 20260202
def run_migrations() -> list[str]:
files = sorted(settings.MIGRATIONS_DIR.glob("*.sql"))
applied = []
with psycopg.connect(settings.DATABASE_URL, autocommit=True,
connect_timeout=settings.DB_CONNECT_TIMEOUT_SECONDS) as conn:
conn.execute("SELECT pg_advisory_lock(%s)", (MIGRATIONS_LOCK,))
try:
conn.execute("""CREATE TABLE IF NOT EXISTS schema_migrations (
version text PRIMARY KEY,
applied_at timestamptz NOT NULL DEFAULT now())""")
done = {row[0] for row in conn.execute("SELECT version FROM schema_migrations")}
for path in files:
if path.name in done:
continue
with conn.transaction():
conn.execute(path.read_text(encoding="utf-8"))
conn.execute("INSERT INTO schema_migrations (version) VALUES (%s)", (path.name,))
applied.append(path.name)
finally:
conn.execute("SELECT pg_advisory_unlock(%s)", (MIGRATIONS_LOCK,))
return applied
def wait_and_migrate(max_wait_seconds: int = 60) -> None:
"""При запуске стенда PostgreSQL может стартовать позже API: ждём его, а не падаем."""
deadline = time.monotonic() + max_wait_seconds
while True:
try:
applied = run_migrations()
log.info("Migrations applied: %s", ", ".join(applied) if applied else "none (schema is up to date)")
return
except psycopg.OperationalError:
if time.monotonic() > deadline:
raise
log.warning("PostgreSQL is not ready yet, retry in 1 s")
time.sleep(1)
USER_COLUMNS = "id, username, display_name, created_at"
def create_user(username: str, display_name: str, password_hash: str) -> dict:
try:
with connection() as conn:
return conn.execute(
f"""INSERT INTO users (id, username, display_name, password_hash)
VALUES (%s, %s, %s, %s) RETURNING {USER_COLUMNS}""",
(uuid4(), username, display_name, password_hash),
).fetchone()
except pg_errors.UniqueViolation:
raise conflict("Username already exists") from None
def get_user(user_id: UUID) -> dict | None:
with connection() as conn:
return conn.execute(f"SELECT {USER_COLUMNS} FROM users WHERE id = %s", (user_id,)).fetchone()
def get_user_with_password(username: str) -> dict | None:
with connection() as conn:
return conn.execute(
f"SELECT {USER_COLUMNS}, password_hash FROM users WHERE username = %s", (username,)
).fetchone()
CHAT_COLUMNS = """c.id, c.title, c.created_at,
ARRAY(SELECT m.user_id FROM chat_members m WHERE m.chat_id = c.id ORDER BY m.user_id) AS member_ids"""
def create_chat(title: str, member_ids: list[UUID]) -> dict:
chat_id = uuid4()
members = sorted(member_ids)
try:
with connection() as conn:
with conn.transaction():
row = conn.execute(
"INSERT INTO chats (id, title) VALUES (%s, %s) RETURNING id, title, created_at",
(chat_id, title),
).fetchone()
with conn.cursor() as cur:
cur.executemany(
"INSERT INTO chat_members (chat_id, user_id) VALUES (%s, %s)",
[(chat_id, member_id) for member_id in members],
)
except pg_errors.ForeignKeyViolation:
raise bad_request("Unknown member") from None
return {**row, "member_ids": members}
def get_chat(chat_id: UUID, user_id: UUID) -> dict:
with connection() as conn:
chat = conn.execute(
f"""SELECT {CHAT_COLUMNS} FROM chats c
JOIN chat_members me ON me.chat_id = c.id AND me.user_id = %s
WHERE c.id = %s""",
(user_id, chat_id),
).fetchone()
if chat is None:
raise not_found("Chat not found")
return chat
def list_chats(user_id: UUID, after: tuple[datetime, UUID] | None, limit: int) -> list[dict]:
"""Чаты пользователя после курсора. Возвращает до limit+1 строк: лишняя значит «есть ещё»."""
where, params = "", [user_id]
if after is not None:
where = "WHERE (c.created_at, c.id) > (%s, %s)"
params += list(after)
with connection() as conn:
return conn.execute(
f"""SELECT {CHAT_COLUMNS} FROM chats c
JOIN chat_members me ON me.chat_id = c.id AND me.user_id = %s
{where}
ORDER BY c.created_at, c.id
LIMIT %s""",
(*params, limit + 1),
).fetchall()
MESSAGE_COLUMNS = "id, chat_id, sender_id, text, created_at"
def add_message(chat_id: UUID, sender_id: UUID, text: str) -> dict:
try:
with connection() as conn:
return conn.execute(
f"""INSERT INTO messages (id, chat_id, sender_id, text)
VALUES (%s, %s, %s, %s) RETURNING {MESSAGE_COLUMNS}""",
(uuid4(), chat_id, sender_id, text),
).fetchone()
except pg_errors.ForeignKeyViolation:
raise not_found("Chat not found") from None
def list_messages(chat_id: UUID, user_id: UUID, after: tuple[datetime, UUID] | None, limit: int) -> list[dict]:
"""История чата после курсора (keyset-пагинация), до limit+1 строк."""
where, params = "chat_id = %s", [chat_id]
if after is not None:
where += " AND (created_at, id) > (%s, %s)"
params += list(after)
with connection() as conn:
member = conn.execute(
"SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s", (chat_id, user_id)
).fetchone()
if member is None:
raise not_found("Chat not found")
return conn.execute(
f"""SELECT {MESSAGE_COLUMNS} FROM messages
WHERE {where}
ORDER BY created_at, id
LIMIT %s""",
(*params, limit + 1),
).fetchall()