forked from mtusi2026/mtusi2026lab3
277 lines
11 KiB
Python
277 lines
11 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):
|
|
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
|
|
except psycopg.DataError as exc:
|
|
log.warning("PostgreSQL rejected a value: %s", type(exc).__name__)
|
|
raise bad_request("Invalid request data") from None
|
|
|
|
|
|
TEXT_FIELDS = ("display_name", "title", "text")
|
|
|
|
|
|
def to_db(value: str) -> bytes:
|
|
return value.encode("utf-8")
|
|
|
|
|
|
def from_db(row: dict | None) -> dict | None:
|
|
"""Строка из БД -> dict для API: байты текстовых полей обратно в str."""
|
|
if row is None:
|
|
return None
|
|
return {key: bytes(value).decode("utf-8") if key in TEXT_FIELDS else value for key, value in row.items()}
|
|
|
|
|
|
def ping() -> None:
|
|
with connection(wait_seconds=1) as conn:
|
|
conn.execute("SELECT 1")
|
|
|
|
|
|
MIGRATIONS_LOCK = 20260202
|
|
|
|
|
|
def migration_files() -> list:
|
|
return sorted(settings.MIGRATIONS_DIR.glob("*.sql"))
|
|
|
|
|
|
def run_migrations() -> list[str]:
|
|
files = migration_files()
|
|
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:
|
|
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)
|
|
|
|
|
|
def missing_migrations() -> list[str]:
|
|
expected = [path.name for path in migration_files()]
|
|
with psycopg.connect(settings.DATABASE_URL, autocommit=True,
|
|
connect_timeout=settings.DB_CONNECT_TIMEOUT_SECONDS) as conn:
|
|
try:
|
|
done = {row[0] for row in conn.execute("SELECT version FROM schema_migrations")}
|
|
except pg_errors.UndefinedTable:
|
|
return expected
|
|
return [name for name in expected if name not in done]
|
|
|
|
|
|
def wait_for_schema(max_wait_seconds: int) -> None:
|
|
deadline = time.monotonic() + max_wait_seconds
|
|
while True:
|
|
try:
|
|
missing = missing_migrations()
|
|
except psycopg.OperationalError:
|
|
missing = None
|
|
if missing == []:
|
|
log.info("Schema is up to date: %s", ", ".join(path.name for path in migration_files()))
|
|
return
|
|
if time.monotonic() > deadline:
|
|
raise RuntimeError(f"Database schema is not ready, missing migrations: {missing}")
|
|
log.warning("Waiting for the migrate service: %s", "PostgreSQL is not ready" if missing is None
|
|
else "missing " + ", ".join(missing))
|
|
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 from_db(conn.execute(
|
|
f"""INSERT INTO users (id, username, display_name, password_hash)
|
|
VALUES (%s, %s, %s, %s) RETURNING {USER_COLUMNS}""",
|
|
(uuid4(), username, to_db(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 from_db(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 from_db(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, to_db(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 {**from_db(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 from_db(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:
|
|
rows = 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()
|
|
return [from_db(row) for row in rows]
|
|
|
|
|
|
|
|
MESSAGE_COLUMNS = "id, chat_id, sender_id, text, created_at"
|
|
|
|
|
|
def add_message(chat_id: UUID, sender_id: UUID, text: str, idempotency_key: str, request_hash: bytes) -> dict:
|
|
try:
|
|
with connection() as conn:
|
|
with conn.transaction():
|
|
member = conn.execute(
|
|
"SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s", (chat_id, sender_id)
|
|
).fetchone()
|
|
if member is None: # проверяется и при повторе: чужой чат — 404, а не сохранённый ответ
|
|
raise not_found("Chat not found")
|
|
created = conn.execute(
|
|
f"""INSERT INTO messages (id, chat_id, sender_id, text, idempotency_key, request_hash)
|
|
VALUES (%s, %s, %s, %s, %s, %s)
|
|
ON CONFLICT (chat_id, sender_id, idempotency_key) DO NOTHING
|
|
RETURNING {MESSAGE_COLUMNS}""",
|
|
(uuid4(), chat_id, sender_id, to_db(text), idempotency_key, request_hash),
|
|
).fetchone()
|
|
if created is not None:
|
|
return from_db(created)
|
|
saved = conn.execute(
|
|
f"""SELECT {MESSAGE_COLUMNS}, request_hash FROM messages
|
|
WHERE chat_id = %s AND sender_id = %s AND idempotency_key = %s""",
|
|
(chat_id, sender_id, idempotency_key),
|
|
).fetchone()
|
|
except pg_errors.ForeignKeyViolation:
|
|
raise not_found("Chat not found") from None
|
|
if saved is None: # не должно случиться: конфликт был, значит строка есть; повтор безопасен
|
|
raise unavailable()
|
|
if bytes(saved.pop("request_hash")) != request_hash:
|
|
raise conflict("Idempotency-Key was already used with a different request")
|
|
return from_db(saved)
|
|
|
|
|
|
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")
|
|
rows = conn.execute(
|
|
f"""SELECT {MESSAGE_COLUMNS} FROM messages
|
|
WHERE {where}
|
|
ORDER BY created_at, id
|
|
LIMIT %s""",
|
|
(*params, limit + 1),
|
|
).fetchall()
|
|
return [from_db(row) for row in rows]
|