forked from mtusi2026/mtusi2026lab2
377 lines
14 KiB
Python
377 lines
14 KiB
Python
"""PostgreSQL pool, миграции и параметризованные запросы."""
|
|
from __future__ import annotations
|
|
|
|
import logging
|
|
from pathlib import Path
|
|
from typing import List, Optional, Sequence
|
|
|
|
from psycopg import OperationalError
|
|
from psycopg.errors import UniqueViolation
|
|
from psycopg.rows import dict_row
|
|
from psycopg_pool import ConnectionPool
|
|
|
|
from app.config import settings
|
|
from app.errors import unavailable
|
|
from app.models import 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,
|
|
)
|
|
self.apply_migrations()
|
|
self.ready = True
|
|
logger.info("postgres pool ready; migrations applied")
|
|
|
|
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()
|
|
except OperationalError as exc:
|
|
raise unavailable("База данных недоступна") from exc
|
|
return [
|
|
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"]),
|
|
)
|
|
for r in rows
|
|
]
|
|
|
|
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)
|