forked from mtusi2026/mtusi2026lab2
8 lines
1.3 KiB
SQL
8 lines
1.3 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE TABLE IF NOT EXISTS users (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), username VARCHAR(32) UNIQUE NOT NULL, display_name VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
|
|
CREATE TABLE IF NOT EXISTS chats (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), title VARCHAR(100) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
|
|
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 TABLE IF NOT EXISTS messages (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE, sender_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, text TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
|
|
CREATE INDEX IF NOT EXISTS idx_msg_chat_time_id ON messages(chat_id, created_at, id);
|
|
CREATE TABLE IF NOT EXISTS sessions (token VARCHAR(255) PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at); |