forked from mtusi2026/mtusi2026lab6
55 lines
1.9 KiB
SQL
55 lines
1.9 KiB
SQL
-- +goose Up
|
|
CREATE TABLE attachments
|
|
(
|
|
id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
|
|
chat_id UUID NOT NULL,
|
|
owner_id UUID NOT NULL,
|
|
filename text NOT NULL,
|
|
object_key text UNIQUE NOT NULL,
|
|
content_type VARCHAR(10) NOT NULL,
|
|
size_bytes bigint NOT NULL,
|
|
sha256 text NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT fk_attachments_chat_member FOREIGN KEY (chat_id, owner_id)
|
|
REFERENCES chat_members(chat_id, user_id),
|
|
CONSTRAINT attachments_sha256_format_check CHECK (sha256 COLLATE "C" ~ '^[0-9a-f]{64}$'),
|
|
CONSTRAINT attachments_content_type_check CHECK (content_type IN ('image/png', 'image/jpeg')),
|
|
CONSTRAINT attachments_filename_check CHECK (
|
|
char_length(filename) BETWEEN 1 AND 255
|
|
AND filename !~ '[/\\]'
|
|
),
|
|
CONSTRAINT attachments_size_bytes_check CHECK (size_bytes BETWEEN 1 AND 10485760),
|
|
CONSTRAINT attachments_id_chat_unique UNIQUE (id, chat_id),
|
|
CONSTRAINT attachments_object_key_length_check CHECK (char_length(object_key) > 0)
|
|
|
|
);
|
|
|
|
ALTER TABLE messages
|
|
ADD CONSTRAINT messages_id_chat_unique UNIQUE (id, chat_id);
|
|
|
|
CREATE TABLE messages_attachments
|
|
(
|
|
message_id UUID NOT NULL ,
|
|
attachment_id UUID NOT NULL ,
|
|
chat_id UUID NOT NULL,
|
|
position SMALLINT NOT NULL,
|
|
|
|
|
|
CONSTRAINT message_attachments_pk PRIMARY KEY (message_id, attachment_id),
|
|
CONSTRAINT message_attachments_position_unique UNIQUE (message_id, position),
|
|
CONSTRAINT message_attachments_position_check CHECK (position BETWEEN 0 AND 9),
|
|
|
|
CONSTRAINT message_attachments_message_chat_fk
|
|
FOREIGN KEY (message_id, chat_id) REFERENCES messages(id, chat_id),
|
|
CONSTRAINT message_attachments_attachment_chat_fk
|
|
FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments(id, chat_id)
|
|
);
|
|
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS messages_attachments;
|
|
DROP TABLE IF EXISTS attachments;
|
|
ALTER TABLE messages DROP CONSTRAINT IF EXISTS messages_id_chat_unique;
|