forked from mtusi2026/mtusi2026lab2
265 lines
10 KiB
Python
265 lines
10 KiB
Python
from __future__ import annotations
|
|
|
|
import base64
|
|
import json
|
|
import uuid
|
|
from datetime import datetime, timezone
|
|
from typing import List, Optional, Tuple
|
|
|
|
import asyncpg
|
|
|
|
from app import config
|
|
from app.errors import ApiError
|
|
|
|
|
|
def fmt_ts(value: datetime) -> str:
|
|
return value.astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%S.%fZ")
|
|
|
|
|
|
def user_row(row: asyncpg.Record) -> dict:
|
|
return {
|
|
"id": str(row["id"]),
|
|
"username": row["username"],
|
|
"display_name": row["display_name"],
|
|
"created_at": fmt_ts(row["created_at"]),
|
|
}
|
|
|
|
|
|
def chat_row(row: asyncpg.Record) -> dict:
|
|
return {
|
|
"id": str(row["id"]),
|
|
"title": row["title"],
|
|
"member_ids": [str(m) for m in row["member_ids"]],
|
|
"created_at": fmt_ts(row["created_at"]),
|
|
}
|
|
|
|
|
|
def message_row(row: asyncpg.Record) -> dict:
|
|
return {
|
|
"id": str(row["id"]),
|
|
"chat_id": str(row["chat_id"]),
|
|
"sender_id": str(row["sender_id"]),
|
|
"text": row["text"],
|
|
"created_at": fmt_ts(row["created_at"]),
|
|
}
|
|
|
|
|
|
def encode_cursor(resource: str, created_at: str, item_id: str) -> str:
|
|
payload = json.dumps({"r": resource, "t": created_at, "i": item_id}, separators=(",", ":"))
|
|
return base64.urlsafe_b64encode(payload.encode("utf-8")).decode("ascii").rstrip("=")
|
|
|
|
|
|
def decode_cursor(resource: str, cursor: str) -> Tuple[datetime, uuid.UUID]:
|
|
try:
|
|
padded = cursor + "=" * (-len(cursor) % 4)
|
|
payload = json.loads(base64.urlsafe_b64decode(padded.encode("ascii")).decode("utf-8"))
|
|
if payload.get("r") != resource:
|
|
raise ValueError("cursor belongs to a different resource")
|
|
created_at = datetime.fromisoformat(payload["t"].replace("Z", "+00:00"))
|
|
if created_at.tzinfo is None:
|
|
raise ValueError("cursor time has no timezone")
|
|
return created_at, uuid.UUID(payload["i"])
|
|
except Exception as exc:
|
|
raise ApiError(400, "invalid_request", "invalid cursor") from exc
|
|
|
|
|
|
def page_of(items: List[dict], resource: str, limit: int) -> dict:
|
|
page = items[:limit]
|
|
next_cursor = None
|
|
if len(items) > limit:
|
|
last = page[-1]
|
|
next_cursor = encode_cursor(resource, last["created_at"], last["id"])
|
|
return {"items": page, "next_cursor": next_cursor}
|
|
|
|
|
|
class Store:
|
|
def __init__(self):
|
|
self.pool: Optional[asyncpg.Pool] = None
|
|
|
|
async def open(self) -> None:
|
|
self.pool = await asyncpg.create_pool(
|
|
config.DATABASE_URL,
|
|
min_size=0,
|
|
max_size=config.DB_POOL_MAX,
|
|
timeout=config.DB_CONNECT_TIMEOUT,
|
|
command_timeout=config.DB_COMMAND_TIMEOUT,
|
|
max_inactive_connection_lifetime=60,
|
|
)
|
|
|
|
async def close(self) -> None:
|
|
if self.pool is not None:
|
|
await self.pool.close()
|
|
|
|
def acquire(self):
|
|
return self.pool.acquire(timeout=config.DB_ACQUIRE_TIMEOUT)
|
|
|
|
async def ping(self) -> bool:
|
|
async with self.acquire() as conn:
|
|
return await conn.fetchval("SELECT 1") == 1
|
|
|
|
|
|
async def create_user(self, username: str, display_name: str, password_hash: str) -> dict:
|
|
async with self.acquire() as conn:
|
|
try:
|
|
row = await conn.fetchrow(
|
|
"""
|
|
INSERT INTO users (id, username, display_name, password_hash)
|
|
VALUES ($1, $2, $3, $4)
|
|
RETURNING id, username, display_name, created_at
|
|
""",
|
|
uuid.uuid4(), username, display_name, password_hash,
|
|
)
|
|
except asyncpg.UniqueViolationError:
|
|
raise ApiError(409, "conflict", "username already taken") from None
|
|
return user_row(row)
|
|
|
|
async def get_credentials(self, username: str) -> Optional[asyncpg.Record]:
|
|
async with self.acquire() as conn:
|
|
return await conn.fetchrow(
|
|
"SELECT id, username, display_name, created_at, password_hash FROM users WHERE username = $1",
|
|
username,
|
|
)
|
|
|
|
|
|
async def create_session(self, token_hash: bytes, user_id: uuid.UUID, ttl_seconds: int) -> datetime:
|
|
async with self.acquire() as conn:
|
|
return await conn.fetchval(
|
|
"""
|
|
INSERT INTO sessions (token_hash, user_id, expires_at)
|
|
VALUES ($1, $2, clock_timestamp() + make_interval(secs => $3))
|
|
RETURNING expires_at
|
|
""",
|
|
token_hash, user_id, float(ttl_seconds),
|
|
)
|
|
|
|
async def find_session(self, token_hash: bytes) -> Optional[asyncpg.Record]:
|
|
async with self.acquire() as conn:
|
|
return await conn.fetchrow(
|
|
"""
|
|
SELECT s.expires_at, u.id, u.username, u.display_name, u.created_at
|
|
FROM sessions s JOIN users u ON u.id = s.user_id
|
|
WHERE s.token_hash = $1 AND s.revoked_at IS NULL AND s.expires_at > clock_timestamp()
|
|
""",
|
|
token_hash,
|
|
)
|
|
|
|
async def revoke_session(self, token_hash: bytes) -> Optional[datetime]:
|
|
async with self.acquire() as conn:
|
|
return await conn.fetchval(
|
|
"""
|
|
UPDATE sessions SET revoked_at = clock_timestamp()
|
|
WHERE token_hash = $1 AND revoked_at IS NULL AND expires_at > clock_timestamp()
|
|
RETURNING expires_at
|
|
""",
|
|
token_hash,
|
|
)
|
|
|
|
|
|
async def create_chat(self, title: str, member_ids: List[uuid.UUID]) -> dict:
|
|
chat_id = uuid.uuid4()
|
|
async with self.acquire() as conn:
|
|
try:
|
|
async with conn.transaction():
|
|
row = await conn.fetchrow(
|
|
"INSERT INTO chats (id, title) VALUES ($1, $2) RETURNING id, title, created_at",
|
|
chat_id, title,
|
|
)
|
|
await conn.execute(
|
|
"""
|
|
INSERT INTO chat_members (chat_id, user_id, position)
|
|
SELECT $1, m.user_id, m.ord - 1
|
|
FROM unnest($2::uuid[]) WITH ORDINALITY AS m(user_id, ord)
|
|
""",
|
|
chat_id, member_ids,
|
|
)
|
|
except asyncpg.ForeignKeyViolationError:
|
|
raise ApiError(400, "invalid_request", "unknown member in member_ids") from None
|
|
return {
|
|
"id": str(row["id"]),
|
|
"title": row["title"],
|
|
"member_ids": [str(m) for m in member_ids],
|
|
"created_at": fmt_ts(row["created_at"]),
|
|
}
|
|
|
|
async def get_chat_for_member(self, chat_id: uuid.UUID, user_id: uuid.UUID) -> Optional[dict]:
|
|
async with self.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"""
|
|
SELECT c.id, c.title, c.created_at,
|
|
array_agg(m.user_id ORDER BY m.position) AS member_ids
|
|
FROM chats c JOIN chat_members m ON m.chat_id = c.id
|
|
WHERE c.id = $1
|
|
AND EXISTS (SELECT 1 FROM chat_members me WHERE me.chat_id = c.id AND me.user_id = $2)
|
|
GROUP BY c.id
|
|
""",
|
|
chat_id, user_id,
|
|
)
|
|
return chat_row(row) if row else None
|
|
|
|
async def list_chats(self, user_id: uuid.UUID, limit: int, cursor: Optional[str]) -> dict:
|
|
after_t, after_i = decode_cursor("chats", cursor) if cursor else (None, None)
|
|
async with self.acquire() as conn:
|
|
rows = await conn.fetch(
|
|
"""
|
|
SELECT c.id, c.title, c.created_at,
|
|
array_agg(m.user_id ORDER BY m.position) AS member_ids
|
|
FROM chat_members me
|
|
JOIN chats c ON c.id = me.chat_id
|
|
JOIN chat_members m ON m.chat_id = c.id
|
|
WHERE me.user_id = $1
|
|
AND ($2::timestamptz IS NULL OR (c.created_at, c.id) > ($2, $3::uuid))
|
|
GROUP BY c.id
|
|
ORDER BY c.created_at, c.id
|
|
LIMIT $4
|
|
""",
|
|
user_id, after_t, after_i, limit + 1,
|
|
)
|
|
return page_of([chat_row(r) for r in rows], "chats", limit)
|
|
|
|
async def is_member(self, chat_id: uuid.UUID, user_id: uuid.UUID) -> bool:
|
|
async with self.acquire() as conn:
|
|
return bool(await conn.fetchval(
|
|
"SELECT EXISTS (SELECT 1 FROM chat_members WHERE chat_id = $1 AND user_id = $2)",
|
|
chat_id, user_id,
|
|
))
|
|
|
|
|
|
async def create_message(self, chat_id: uuid.UUID, sender_id: uuid.UUID, text: str) -> Optional[dict]:
|
|
async with self.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"""
|
|
INSERT INTO messages (id, chat_id, sender_id, text)
|
|
SELECT $1, $2, $3, $4
|
|
WHERE EXISTS (SELECT 1 FROM chat_members WHERE chat_id = $2 AND user_id = $3)
|
|
RETURNING id, chat_id, sender_id, text, created_at
|
|
""",
|
|
uuid.uuid4(), chat_id, sender_id, text,
|
|
)
|
|
return message_row(row) if row else None
|
|
|
|
async def list_messages(self, chat_id: uuid.UUID, limit: int, cursor: Optional[str]) -> dict:
|
|
resource = str(chat_id)
|
|
async with self.acquire() as conn:
|
|
if cursor:
|
|
after_t, after_i = decode_cursor(resource, cursor)
|
|
rows = await conn.fetch(
|
|
"""
|
|
SELECT id, chat_id, sender_id, text, created_at FROM messages
|
|
WHERE chat_id = $1 AND (created_at, id) > ($2, $3)
|
|
ORDER BY created_at, id
|
|
LIMIT $4
|
|
""",
|
|
chat_id, after_t, after_i, limit + 1,
|
|
)
|
|
else:
|
|
rows = await conn.fetch(
|
|
"""
|
|
SELECT id, chat_id, sender_id, text, created_at FROM messages
|
|
WHERE chat_id = $1
|
|
ORDER BY created_at, id
|
|
LIMIT $2
|
|
""",
|
|
chat_id, limit + 1,
|
|
)
|
|
return page_of([message_row(r) for r in rows], resource, limit)
|