Files
mtusi2026lab2/app/storage.py

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)