Files
mtusi2026lab4-fork/app/db.py
daniil_sobolev 747ca222e5 Implement lab4 attachments on RustFS for grade 4.
Carry over lab3 HA stack and add private S3 storage, Attachment API, orphan compensation/reaper, own tests, and evidence for smoke/persistence/failover.
2026-11-12 20:41:02 +03:00

583 lines
22 KiB
Python

"""PostgreSQL pool, миграции и параметризованные запросы."""
from __future__ import annotations
import json
import logging
from pathlib import Path
from typing import List, Optional, Sequence, Tuple
from psycopg import OperationalError
from psycopg.errors import UniqueViolation
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool
from psycopg.types.json import Json
from app.config import settings
from app.errors import conflict, unavailable
from app.models import Attachment, Chat, Message, User
from app.timefmt import format_utc
logger = logging.getLogger("messenger")
MIGRATIONS_DIR = Path(__file__).resolve().parent.parent / "migrations"
class Database:
def __init__(self) -> None:
self.pool: Optional[ConnectionPool] = None
self.accepting = True
self.ready = False
def open(self) -> None:
self.pool = ConnectionPool(
conninfo=settings.database_url,
min_size=settings.db_pool_min,
max_size=settings.db_pool_max,
timeout=settings.db_connect_timeout,
kwargs={
"connect_timeout": max(1, int(settings.db_connect_timeout)),
"row_factory": dict_row,
},
open=True,
)
if settings.run_migrations:
self.apply_migrations()
logger.info("postgres pool ready; migrations applied")
else:
logger.info("postgres pool ready; migrations skipped (RUN_MIGRATIONS=false)")
self.ready = True
def close(self) -> None:
self.ready = False
self.accepting = False
if self.pool is not None:
self.pool.close()
self.pool = None
def begin_shutdown(self) -> None:
self.accepting = False
self.ready = False
def _require_pool(self) -> ConnectionPool:
if self.pool is None:
raise unavailable("База данных недоступна")
return self.pool
def ping(self) -> bool:
try:
pool = self._require_pool()
with pool.connection() as conn:
conn.execute("SELECT 1")
return True
except Exception:
return False
@staticmethod
def _split_sql(script: str) -> List[str]:
statements: List[str] = []
buf: List[str] = []
for line in script.splitlines():
stripped = line.strip()
if not stripped or stripped.startswith("--"):
continue
buf.append(line)
if stripped.endswith(";"):
stmt = "\n".join(buf).strip()
if stmt:
statements.append(stmt)
buf = []
tail = "\n".join(buf).strip()
if tail:
statements.append(tail)
return statements
def apply_migrations(self) -> None:
pool = self._require_pool()
files = sorted(MIGRATIONS_DIR.glob("*.sql"))
with pool.connection() as conn:
conn.execute(
"""
CREATE TABLE IF NOT EXISTS schema_migrations (
version TEXT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
"""
)
for path in files:
version = path.name
row = conn.execute(
"SELECT 1 FROM schema_migrations WHERE version = %s",
(version,),
).fetchone()
if row:
continue
statements = self._split_sql(path.read_text(encoding="utf-8"))
with conn.transaction():
for stmt in statements:
conn.execute(stmt)
conn.execute(
"INSERT INTO schema_migrations (version) VALUES (%s)",
(version,),
)
logger.info("applied migration %s", version)
def _user_from_row(self, row: dict) -> User:
return User(
id=str(row["id"]),
username=row["username"],
display_name=row["display_name"],
created_at=format_utc(row["created_at"]),
password_hash=row.get("password_hash", ""),
)
def add_user(self, user: User) -> None:
pool = self._require_pool()
try:
with pool.connection() as conn:
conn.execute(
"""
INSERT INTO users (id, username, display_name, password_hash, created_at)
VALUES (%s, %s, %s, %s, %s::timestamptz)
""",
(
user.id,
user.username,
user.display_name,
user.password_hash,
user.created_at,
),
)
except UniqueViolation as exc:
raise KeyError("username") from exc
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
def get_user(self, user_id: str) -> Optional[User]:
pool = self._require_pool()
try:
with pool.connection() as conn:
row = conn.execute(
"""
SELECT id, username, display_name, password_hash, created_at
FROM users WHERE id = %s
""",
(user_id,),
).fetchone()
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return self._user_from_row(row) if row else None
def get_user_by_username(self, username: str) -> Optional[User]:
pool = self._require_pool()
try:
with pool.connection() as conn:
row = conn.execute(
"""
SELECT id, username, display_name, password_hash, created_at
FROM users WHERE username = %s
""",
(username,),
).fetchone()
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return self._user_from_row(row) if row else None
def users_exist(self, user_ids: Sequence[str]) -> bool:
if not user_ids:
return True
pool = self._require_pool()
try:
with pool.connection() as conn:
row = conn.execute(
"SELECT COUNT(*) AS c FROM users WHERE id = ANY(%s::uuid[])",
(list(user_ids),),
).fetchone()
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return int(row["c"]) == len(set(user_ids))
def add_chat(self, chat: Chat) -> None:
"""Атомарно: чат + участники в одной транзакции."""
pool = self._require_pool()
try:
with pool.connection() as conn:
with conn.transaction():
# Повторная проверка участников внутри транзакции.
row = conn.execute(
"SELECT COUNT(*) AS c FROM users WHERE id = ANY(%s::uuid[])",
(list(chat.member_ids),),
).fetchone()
if int(row["c"]) != len(set(chat.member_ids)):
raise ValueError("unknown member")
conn.execute(
"""
INSERT INTO chats (id, title, created_at)
VALUES (%s, %s, %s::timestamptz)
""",
(chat.id, chat.title, chat.created_at),
)
for mid in chat.member_ids:
conn.execute(
"""
INSERT INTO chat_members (chat_id, user_id)
VALUES (%s, %s)
""",
(chat.id, mid),
)
except ValueError:
raise
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
def get_chat(self, chat_id: str) -> Optional[Chat]:
pool = self._require_pool()
try:
with pool.connection() as conn:
row = conn.execute(
"SELECT id, title, created_at FROM chats WHERE id = %s",
(chat_id,),
).fetchone()
if not row:
return None
members = conn.execute(
"""
SELECT user_id FROM chat_members
WHERE chat_id = %s
ORDER BY user_id
""",
(chat_id,),
).fetchall()
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return Chat(
id=str(row["id"]),
title=row["title"],
member_ids=[str(m["user_id"]) for m in members],
created_at=format_utc(row["created_at"]),
)
def list_chats_for(self, user_id: str) -> List[Chat]:
pool = self._require_pool()
try:
with pool.connection() as conn:
rows = conn.execute(
"""
SELECT c.id, c.title, c.created_at
FROM chats c
JOIN chat_members m ON m.chat_id = c.id
WHERE m.user_id = %s
ORDER BY c.created_at ASC, c.id ASC
""",
(user_id,),
).fetchall()
result: List[Chat] = []
for row in rows:
members = conn.execute(
"""
SELECT user_id FROM chat_members
WHERE chat_id = %s
ORDER BY user_id
""",
(row["id"],),
).fetchall()
result.append(
Chat(
id=str(row["id"]),
title=row["title"],
member_ids=[str(m["user_id"]) for m in members],
created_at=format_utc(row["created_at"]),
)
)
return result
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
def add_message(self, message: Message) -> Message:
pool = self._require_pool()
try:
with pool.connection() as conn:
conn.execute(
"""
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
VALUES (%s, %s, %s, %s, %s::timestamptz)
""",
(
message.id,
message.chat_id,
message.sender_id,
message.text,
message.created_at,
),
)
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return message
def list_messages_page(
self,
chat_id: str,
limit: int,
after_created_at: Optional[str] = None,
after_id: Optional[str] = None,
) -> List[Message]:
"""Keyset page using index (chat_id, created_at, id)."""
pool = self._require_pool()
try:
with pool.connection() as conn:
if after_created_at is None:
rows = conn.execute(
"""
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %s
ORDER BY created_at ASC, id ASC
LIMIT %s
""",
(chat_id, limit),
).fetchall()
else:
rows = conn.execute(
"""
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %s
AND (created_at, id) > (%s::timestamptz, %s::uuid)
ORDER BY created_at ASC, id ASC
LIMIT %s
""",
(chat_id, after_created_at, after_id, limit),
).fetchall()
result: List[Message] = []
for r in rows:
aids = conn.execute(
"""
SELECT attachment_id FROM message_attachments
WHERE message_id = %s
ORDER BY position ASC
""",
(r["id"],),
).fetchall()
result.append(
Message(
id=str(r["id"]),
chat_id=str(r["chat_id"]),
sender_id=str(r["sender_id"]),
text=r["text"],
created_at=format_utc(r["created_at"]),
attachment_ids=[str(a["attachment_id"]) for a in aids],
)
)
return result
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
def explain_messages_page(self, chat_id: str, limit: int = 50) -> str:
pool = self._require_pool()
with pool.connection() as conn:
rows = conn.execute(
"""
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, chat_id, sender_id, text, created_at
FROM messages
WHERE chat_id = %s
ORDER BY created_at ASC, id ASC
LIMIT %s
""",
(chat_id, limit),
).fetchall()
# EXPLAIN text rows come as single-column dicts
lines = []
for r in rows:
lines.append(next(iter(r.values())))
return "\n".join(lines)
def create_message_idempotent(
self,
message: Message,
*,
user_id: str,
method: str,
path: str,
idem_key: str,
request_hash: str,
) -> Tuple[dict, bool]:
"""Atomically create message or replay prior result.
Returns (message_dict, created_new).
"""
pool = self._require_pool()
try:
with pool.connection() as conn:
with conn.transaction():
claimed = conn.execute(
"""
INSERT INTO idempotency_keys (
user_id, method, path, idem_key, request_hash,
response_status, response_body, created_at
)
VALUES (
%s, %s, %s, %s, %s,
0, '{}'::jsonb, %s::timestamptz
)
ON CONFLICT DO NOTHING
RETURNING idem_key
""",
(
user_id,
method,
path,
idem_key,
request_hash,
message.created_at,
),
).fetchone()
if claimed is None:
existing = conn.execute(
"""
SELECT request_hash, response_status, response_body
FROM idempotency_keys
WHERE user_id = %s AND method = %s
AND path = %s AND idem_key = %s
FOR UPDATE
""",
(user_id, method, path, idem_key),
).fetchone()
if existing is None:
raise unavailable("Идемпотентность временно недоступна")
if existing["request_hash"] != request_hash:
raise conflict("Idempotency-Key уже использован с другим телом")
if int(existing["response_status"]) != 201:
# Another replica still writing; rare — wait via conflict for client retry
raise unavailable("Идемпотентный запрос ещё обрабатывается")
body = existing["response_body"]
if isinstance(body, str):
body = json.loads(body)
return dict(body), False
conn.execute(
"""
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
VALUES (%s, %s, %s, %s, %s::timestamptz)
""",
(
message.id,
message.chat_id,
message.sender_id,
message.text,
message.created_at,
),
)
for pos, aid in enumerate(message.attachment_ids):
conn.execute(
"""
INSERT INTO message_attachments (message_id, attachment_id, position)
VALUES (%s, %s, %s)
""",
(message.id, aid, pos),
)
payload = message.to_dict()
conn.execute(
"""
UPDATE idempotency_keys
SET response_status = 201, response_body = %s
WHERE user_id = %s AND method = %s
AND path = %s AND idem_key = %s
""",
(Json(payload), user_id, method, path, idem_key),
)
return payload, True
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
def _attachment_from_row(self, row: dict) -> Attachment:
return Attachment(
id=str(row["id"]),
chat_id=str(row["chat_id"]),
owner_id=str(row["owner_id"]),
filename=row["filename"],
content_type=row["content_type"],
size_bytes=int(row["size_bytes"]),
sha256=row["sha256"],
object_key=row["object_key"],
status=row["status"],
error_code=row["error_code"],
created_at=format_utc(row["created_at"]),
updated_at=format_utc(row["updated_at"]),
variants=[],
)
def add_attachment(self, att: Attachment) -> None:
pool = self._require_pool()
try:
with pool.connection() as conn:
conn.execute(
"""
INSERT INTO attachments (
id, chat_id, owner_id, filename, content_type, size_bytes,
sha256, object_key, status, error_code, created_at, updated_at
)
VALUES (
%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s::timestamptz, %s::timestamptz
)
""",
(
att.id,
att.chat_id,
att.owner_id,
att.filename,
att.content_type,
att.size_bytes,
att.sha256,
att.object_key,
att.status,
att.error_code,
att.created_at,
att.updated_at,
),
)
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
def get_attachment(self, attachment_id: str) -> Optional[Attachment]:
pool = self._require_pool()
try:
with pool.connection() as conn:
row = conn.execute(
"""
SELECT id, chat_id, owner_id, filename, content_type, size_bytes,
sha256, object_key, status, error_code, created_at, updated_at
FROM attachments WHERE id = %s
""",
(attachment_id,),
).fetchone()
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return self._attachment_from_row(row) if row else None
def get_attachments_for_chat(self, chat_id: str, ids: Sequence[str]) -> List[Attachment]:
if not ids:
return []
pool = self._require_pool()
try:
with pool.connection() as conn:
rows = conn.execute(
"""
SELECT id, chat_id, owner_id, filename, content_type, size_bytes,
sha256, object_key, status, error_code, created_at, updated_at
FROM attachments
WHERE chat_id = %s AND id = ANY(%s::uuid[])
""",
(chat_id, list(ids)),
).fetchall()
except OperationalError as exc:
raise unavailable("База данных недоступна") from exc
return [self._attachment_from_row(r) for r in rows]
def list_known_object_keys(self) -> List[str]:
pool = self._require_pool()
with pool.connection() as conn:
rows = conn.execute("SELECT object_key FROM attachments").fetchall()
return [r["object_key"] for r in rows]