forked from mtusi2026/mtusi2026lab7
69 lines
4.7 KiB
SQL
69 lines
4.7 KiB
SQL
-- 004 (lab4): вложения. Метаданные — в PostgreSQL, байты — в приватном S3-бакете.
|
|
--
|
|
-- Жизненный цикл строки attachments (внутреннее поле state, в API не видно):
|
|
-- pending — намерение записано ДО S3 PUT (одна короткая транзакция);
|
|
-- ready — объект записан в S3, затем UPDATE ... WHERE state='pending'.
|
|
-- Наружу (GET, ссылки из сообщений) доступны только ready. Строки pending,
|
|
-- оставшиеся после сбоя между S3 и COMMIT, удаляет сборщик (src/reconcile.py)
|
|
-- после grace period; сначала удаляется строка (атомарный «захват»), потом объект.
|
|
-- Миграция только расширяющая (expand): реплики lab3 продолжают работать.
|
|
|
|
-- Сообщение с вложениями может иметь пустой текст (text = "").
|
|
-- Запрет «пустой текст без вложений» проверяет приложение.
|
|
ALTER TABLE messages DROP CONSTRAINT messages_text_len;
|
|
ALTER TABLE messages ADD CONSTRAINT messages_text_len CHECK (char_length(text) BETWEEN 0 AND 4000);
|
|
-- Цель составного внешнего ключа message_attachments (message_id, chat_id).
|
|
ALTER TABLE messages ADD CONSTRAINT messages_id_chat_key UNIQUE (id, chat_id);
|
|
|
|
CREATE TABLE attachments (
|
|
id uuid PRIMARY KEY,
|
|
chat_id uuid NOT NULL REFERENCES chats (id) ON DELETE RESTRICT,
|
|
owner_id uuid NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
|
|
-- Только отображаемое имя. Никогда не используется как путь или ключ S3.
|
|
filename text NOT NULL,
|
|
content_type text NOT NULL,
|
|
size_bytes bigint NOT NULL,
|
|
sha256 text NOT NULL,
|
|
width integer NOT NULL,
|
|
height integer NOT NULL,
|
|
-- Ключ объекта назначает сервер (случайный UUID), он не равен id вложения
|
|
-- и не раскрывается в API.
|
|
object_key text NOT NULL,
|
|
state text NOT NULL DEFAULT 'pending',
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
CONSTRAINT attachments_object_key_key UNIQUE (object_key),
|
|
CONSTRAINT attachments_id_chat_key UNIQUE (id, chat_id),
|
|
CONSTRAINT attachments_filename_len CHECK (char_length(filename) BETWEEN 1 AND 255),
|
|
CONSTRAINT attachments_content_type CHECK (content_type IN ('image/png', 'image/jpeg')),
|
|
CONSTRAINT attachments_size CHECK (size_bytes BETWEEN 1 AND 10485760),
|
|
CONSTRAINT attachments_sha256 CHECK (sha256 ~ '^[a-f0-9]{64}$'),
|
|
CONSTRAINT attachments_pixels CHECK (width > 0 AND height > 0 AND width::bigint * height <= 40000000),
|
|
CONSTRAINT attachments_state CHECK (state IN ('pending', 'ready'))
|
|
);
|
|
|
|
-- Сборщик незавершённых загрузок: WHERE state = 'pending' AND created_at < ...
|
|
CREATE INDEX attachments_pending_created_idx ON attachments (created_at) WHERE state = 'pending';
|
|
CREATE INDEX attachments_chat_idx ON attachments (chat_id);
|
|
|
|
-- Связь сообщение -> вложения (порядок attachment_ids сохраняется в position).
|
|
-- Составные внешние ключи гарантируют НА УРОВНЕ БД, что вложение и сообщение
|
|
-- принадлежат одному чату. ON DELETE RESTRICT: строку вложения, на которую
|
|
-- ссылается сообщение, нельзя удалить (сборщик удаляет только pending, а
|
|
-- pending прикрепить нельзя — двойная защита).
|
|
CREATE TABLE message_attachments (
|
|
message_id uuid NOT NULL,
|
|
attachment_id uuid NOT NULL,
|
|
chat_id uuid NOT NULL,
|
|
position smallint NOT NULL,
|
|
PRIMARY KEY (message_id, attachment_id),
|
|
CONSTRAINT message_attachments_position_key UNIQUE (message_id, position),
|
|
CONSTRAINT message_attachments_position CHECK (position BETWEEN 0 AND 9),
|
|
CONSTRAINT message_attachments_message_fk FOREIGN KEY (message_id, chat_id)
|
|
REFERENCES messages (id, chat_id) ON DELETE CASCADE,
|
|
CONSTRAINT message_attachments_attachment_fk FOREIGN KEY (attachment_id, chat_id)
|
|
REFERENCES attachments (id, chat_id) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE INDEX message_attachments_attachment_idx ON message_attachments (attachment_id);
|