Files

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]