Files
mtusi2026lab2/db.py
2026-10-23 18:28:52 +03:00

262 lines
9.4 KiB
Python

import os
import uuid
from pathlib import Path
from psycopg import errors
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool, PoolTimeout
MIGRATIONS_DIR = Path(__file__).resolve().parent / 'migrations'
class ApiError(Exception):
def __init__(self, status, code, message):
super().__init__(message)
self.status = status
self.code = code
self.message = message
def read_database_url():
path = os.environ.get('DATABASE_URL_FILE')
if path and os.path.isfile(path):
return Path(path).read_text(encoding='utf-8').strip()
url = os.environ.get('DATABASE_URL', '').strip()
if not url:
raise RuntimeError('DATABASE_URL or DATABASE_URL_FILE is required')
return url
class Store:
def __init__(self, pool: ConnectionPool):
self.pool = pool
@classmethod
def connect(cls):
pool = ConnectionPool(
conninfo=read_database_url(),
min_size=1,
max_size=10,
timeout=5.0,
kwargs={
'row_factory': dict_row,
'options': '-c statement_timeout=5000 -c lock_timeout=2000',
},
open=True,
)
store = cls(pool)
store.run_migrations()
return store
def close(self):
self.pool.close()
def ping(self):
with self.pool.connection() as conn:
conn.execute('SELECT 1')
def run_migrations(self):
with self.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 sorted(MIGRATIONS_DIR.glob('*.sql')):
version = path.name
row = conn.execute(
'SELECT 1 FROM schema_migrations WHERE version = %s',
(version,),
).fetchone()
if row:
continue
sql = path.read_text(encoding='utf-8')
with conn.transaction():
conn.execute(sql)
conn.execute(
'INSERT INTO schema_migrations (version) VALUES (%s)',
(version,),
)
@staticmethod
def _user_row(row):
return {
'id': str(row['id']),
'username': row['username'],
'display_name': row['display_name'],
'created_at': row['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
}
def register_user(self, username, display_name, password_hash, created_at):
user_id = str(uuid.uuid4())
try:
with self.pool.connection() as conn:
conn.execute(
'INSERT INTO users (id, username, display_name, password_hash, created_at) '
'VALUES (%s, %s, %s, %s, %s)',
(user_id, username, display_name, password_hash, created_at),
)
except errors.UniqueViolation:
raise ApiError(409, 'conflict', 'Username already exists') from None
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
return {
'id': user_id,
'username': username,
'display_name': display_name,
'created_at': created_at,
}
def get_user(self, user_id):
with self.pool.connection() as conn:
row = conn.execute(
'SELECT id, username, display_name, created_at FROM users WHERE id = %s',
(user_id,),
).fetchone()
if row is None:
return None
return self._user_row(row)
def get_user_with_password(self, username):
with self.pool.connection() as conn:
row = conn.execute(
'SELECT id, username, display_name, password_hash, created_at FROM users WHERE username = %s',
(username,),
).fetchone()
return row
def create_chat(self, user_id, title, member_ids, created_at):
chat_id = str(uuid.uuid4())
try:
with self.pool.connection() as conn:
with conn.transaction():
rows = conn.execute(
'SELECT id FROM users WHERE id = ANY(%s)',
(member_ids,),
).fetchall()
if len(rows) != len(set(member_ids)):
raise ApiError(400, 'invalid_request', 'Unknown chat member')
conn.execute(
'INSERT INTO chats (id, title, created_at) VALUES (%s, %s, %s)',
(chat_id, title, created_at),
)
for index, member_id in enumerate(member_ids):
conn.execute(
'INSERT INTO chat_members (chat_id, user_id, position) VALUES (%s, %s, %s)',
(chat_id, member_id, index),
)
except ApiError:
raise
except errors.Error:
raise ApiError(500, 'internal_error', 'Internal server error') from None
return {
'id': chat_id,
'title': title,
'member_ids': list(member_ids),
'created_at': created_at,
}
def _is_member(self, conn, chat_id, user_id):
row = conn.execute(
'SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s',
(chat_id, user_id),
).fetchone()
return row is not None
def get_chat(self, user_id, chat_id):
with self.pool.connection() as conn:
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
chat = conn.execute(
'SELECT id, title, created_at FROM chats WHERE id = %s',
(chat_id,),
).fetchone()
members = conn.execute(
'SELECT user_id FROM chat_members WHERE chat_id = %s ORDER BY position',
(chat_id,),
).fetchall()
return {
'id': str(chat['id']),
'title': chat['title'],
'member_ids': [str(row['user_id']) for row in members],
'created_at': chat['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
}
def list_chats(self, user_id, limit, cursor_key):
query = (
'SELECT c.id, c.title, c.created_at '
'FROM chats c '
'JOIN chat_members cm ON cm.chat_id = c.id AND cm.user_id = %s '
)
params = [user_id]
if cursor_key:
query += 'WHERE (c.created_at, c.id) > (%s, %s) '
params.extend(cursor_key)
query += 'ORDER BY c.created_at ASC, c.id ASC LIMIT %s'
params.append(limit + 1)
with self.pool.connection() as conn:
rows = conn.execute(query, params).fetchall()
has_more = len(rows) > limit
page = rows[:limit]
items = []
for row in page:
chat_id = str(row['id'])
items.append(self.get_chat(user_id, chat_id))
next_cursor = None
if has_more:
last = page[-1]
next_cursor = (last['created_at'], str(last['id']))
return items, next_cursor
def create_message(self, user_id, chat_id, text, created_at):
message_id = str(uuid.uuid4())
with self.pool.connection() as conn:
with conn.transaction():
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
conn.execute(
'INSERT INTO messages (id, chat_id, sender_id, text, created_at) '
'VALUES (%s, %s, %s, %s, %s)',
(message_id, chat_id, user_id, text, created_at),
)
return {
'id': message_id,
'chat_id': chat_id,
'sender_id': user_id,
'text': text,
'created_at': created_at,
}
def list_messages(self, user_id, chat_id, limit, cursor_key):
with self.pool.connection() as conn:
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
query = (
'SELECT id, chat_id, sender_id, text, created_at FROM messages '
'WHERE chat_id = %s '
)
params = [chat_id]
if cursor_key:
query += 'AND (created_at, id) > (%s, %s) '
params.extend(cursor_key)
query += 'ORDER BY created_at ASC, id ASC LIMIT %s'
params.append(limit + 1)
with self.pool.connection() as conn:
rows = conn.execute(query, params).fetchall()
has_more = len(rows) > limit
page = rows[:limit]
items = [
{
'id': str(row['id']),
'chat_id': str(row['chat_id']),
'sender_id': str(row['sender_id']),
'text': row['text'],
'created_at': row['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
}
for row in page
]
next_cursor = None
if has_more:
last = page[-1]
next_cursor = (last['created_at'], str(last['id']))
return items, next_cursor