Files
mtusi2026lab7/migrations/004_attachments.sql

49 lines
3.2 KiB
SQL

-- lab4: вложения. Байты лежат в S3, здесь только метаданные и связь с чатом/сообщением.
-- Миграция только добавляет таблицы и ослабляет одно ограничение, API lab3 продолжает работать.
-- Нужны для составных внешних ключей ниже: вложение и сообщение связываются только в пределах одного чата.
ALTER TABLE messages ADD CONSTRAINT messages_id_chat UNIQUE (id, chat_id);
CREATE TABLE attachments (
id uuid PRIMARY KEY,
chat_id uuid NOT NULL,
owner_id uuid NOT NULL,
filename bytea NOT NULL CHECK (octet_length(filename) BETWEEN 1 AND 1020),
content_type text NOT NULL CHECK (content_type IN ('image/png', 'image/jpeg')),
size_bytes integer NOT NULL CHECK (size_bytes BETWEEN 1 AND 10485760),
sha256 text NOT NULL CHECK (sha256 ~ '^[a-f0-9]{64}$'),
width integer NOT NULL CHECK (width >= 1),
height integer NOT NULL CHECK (height >= 1),
-- Ключ объекта назначает сервер (originals/<случайный hex>), имя файла в него не входит.
object_key text NOT NULL UNIQUE CHECK (object_key ~ '^originals/[a-f0-9]{32}$'),
-- uploading: строка-намерение записана ДО PUT в S3, клиенту не видна;
-- ready: объект в S3 сохранён, можно читать и прикреплять.
state text NOT NULL CHECK (state IN ('uploading', 'ready')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
CHECK (width::bigint * height <= 40000000),
UNIQUE (id, chat_id),
-- владелец на момент загрузки был участником именно этого чата
FOREIGN KEY (chat_id, owner_id) REFERENCES chat_members (chat_id, user_id)
);
-- janitor ищет старые незавершённые загрузки
CREATE INDEX attachments_uploading ON attachments (created_at) WHERE state = 'uploading';
CREATE TABLE message_attachments (
message_id uuid NOT NULL,
chat_id uuid NOT NULL,
attachment_id uuid NOT NULL,
position smallint NOT NULL CHECK (position BETWEEN 0 AND 9),
PRIMARY KEY (message_id, position),
UNIQUE (message_id, attachment_id),
-- один chat_id в обоих ключах: вложение другого чата прикрепить нельзя даже в обход API
FOREIGN KEY (message_id, chat_id) REFERENCES messages (id, chat_id),
FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments (id, chat_id)
);
CREATE INDEX message_attachments_by_attachment ON message_attachments (attachment_id);
-- С lab4 текст может быть пустым, если есть вложение (проверяет API).
ALTER TABLE messages DROP CONSTRAINT messages_text_check;
ALTER TABLE messages ADD CONSTRAINT messages_text_check CHECK (octet_length(text) <= 16000);