forked from mtusi2026/mtusi2026lab2
57 lines
2.0 KiB
SQL
57 lines
2.0 KiB
SQL
CREATE SCHEMA IF NOT EXISTS "public";
|
|
|
|
CREATE TABLE "users" (
|
|
"id" UUID NOT NULL,
|
|
"username" VARCHAR(32) NOT NULL,
|
|
"display_name" VARCHAR(100) NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "chats" (
|
|
"id" UUID NOT NULL,
|
|
"title" VARCHAR(100) NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "chats_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "chat_members" (
|
|
"chat_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"position" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "chat_members_pkey" PRIMARY KEY ("chat_id","user_id")
|
|
);
|
|
|
|
CREATE TABLE "messages" (
|
|
"id" UUID NOT NULL,
|
|
"chat_id" UUID NOT NULL,
|
|
"sender_id" UUID NOT NULL,
|
|
"text" VARCHAR(4000) NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "messages_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "users_username_key" ON "users"("username");
|
|
|
|
CREATE INDEX "chats_created_at_id_idx" ON "chats"("created_at", "id");
|
|
|
|
CREATE INDEX "chat_members_user_id_chat_id_idx" ON "chat_members"("user_id", "chat_id");
|
|
|
|
CREATE UNIQUE INDEX "chat_members_chat_id_position_key" ON "chat_members"("chat_id", "position");
|
|
|
|
CREATE INDEX "messages_chat_id_created_at_id_idx" ON "messages"("chat_id", "created_at", "id");
|
|
|
|
CREATE INDEX "messages_sender_id_idx" ON "messages"("sender_id");
|
|
|
|
ALTER TABLE "chat_members" ADD CONSTRAINT "chat_members_chat_id_fkey" FOREIGN KEY ("chat_id") REFERENCES "chats"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "chat_members" ADD CONSTRAINT "chat_members_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "messages" ADD CONSTRAINT "messages_chat_id_fkey" FOREIGN KEY ("chat_id") REFERENCES "chats"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "messages" ADD CONSTRAINT "messages_sender_id_fkey" FOREIGN KEY ("sender_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|