forked from mtusi2026/mtusi2026lab4
Стенд: PostgreSQL 16 + две API-реплики за HAProxy + SeaweedFS S3 в приватном bucket. Метаданные вложений в PostgreSQL, байты в S3, object key назначает сервер. Лимиты 10 MiB и 40 млн пикселей, MIME по магическим байтам, проверка filename. Добавлены smoke/негативные тесты, хуки restart/stop-one, сценарий отказа S3 и отчёт с доказательствами.
129 lines
4.2 KiB
Python
129 lines
4.2 KiB
Python
"""PostgreSQL pool and idempotent schema bootstrap."""
|
|
import time
|
|
|
|
import psycopg
|
|
from psycopg.rows import dict_row
|
|
from psycopg_pool import ConnectionPool
|
|
|
|
MIGRATION_LOCK = 0x4C414234 # "LAB4"
|
|
|
|
SCHEMA = [
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
)
|
|
""",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token_hash TEXT PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
)
|
|
""",
|
|
"CREATE INDEX IF NOT EXISTS sessions_expires_idx ON sessions(expires_at)",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS chats (
|
|
id UUID PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
)
|
|
""",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS chat_members (
|
|
chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (chat_id, user_id)
|
|
)
|
|
""",
|
|
"CREATE INDEX IF NOT EXISTS chat_members_user_idx ON chat_members(user_id, chat_id)",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY,
|
|
chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
|
|
sender_id UUID NOT NULL REFERENCES users(id),
|
|
text TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
)
|
|
""",
|
|
"CREATE INDEX IF NOT EXISTS messages_chat_order_idx ON messages(chat_id, created_at, id)",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS attachments (
|
|
id UUID PRIMARY KEY,
|
|
chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
filename TEXT NOT NULL,
|
|
content_type TEXT NOT NULL,
|
|
size_bytes BIGINT NOT NULL,
|
|
sha256 TEXT NOT NULL,
|
|
object_key TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL,
|
|
variants JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
error_code TEXT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
)
|
|
""",
|
|
"CREATE INDEX IF NOT EXISTS attachments_chat_idx ON attachments(chat_id)",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS message_attachments (
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
attachment_id UUID NOT NULL REFERENCES attachments(id),
|
|
position INTEGER NOT NULL,
|
|
PRIMARY KEY (message_id, attachment_id),
|
|
UNIQUE (message_id, position)
|
|
)
|
|
""",
|
|
"CREATE INDEX IF NOT EXISTS message_attachments_attachment_idx ON message_attachments(attachment_id)",
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS idempotency (
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
|
|
key TEXT NOT NULL,
|
|
request_hash TEXT NOT NULL,
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (user_id, chat_id, key)
|
|
)
|
|
""",
|
|
"CREATE INDEX IF NOT EXISTS idempotency_created_idx ON idempotency(created_at)",
|
|
]
|
|
|
|
|
|
def wait_for_database(dsn, timeout_seconds=90):
|
|
deadline = time.monotonic() + timeout_seconds
|
|
last_error = None
|
|
while time.monotonic() < deadline:
|
|
try:
|
|
with psycopg.connect(dsn, connect_timeout=3) as connection:
|
|
connection.execute("SELECT 1")
|
|
return
|
|
except psycopg.Error as error:
|
|
last_error = error
|
|
time.sleep(1)
|
|
raise RuntimeError("PostgreSQL did not become available") from last_error
|
|
|
|
|
|
def create_pool(dsn):
|
|
return ConnectionPool(
|
|
conninfo=dsn,
|
|
min_size=1,
|
|
max_size=10,
|
|
timeout=10,
|
|
kwargs={"row_factory": dict_row},
|
|
open=False,
|
|
)
|
|
|
|
|
|
def migrate(pool):
|
|
with pool.connection() as connection:
|
|
with connection.transaction():
|
|
with connection.cursor() as cursor:
|
|
cursor.execute("SELECT pg_advisory_xact_lock(%s)", (MIGRATION_LOCK,))
|
|
for statement in SCHEMA:
|
|
cursor.execute(statement)
|