AzenKain
29944915cd
All checks were successful
Build and Release / release (push) Successful in 1m30s
43 lines
1.2 KiB
SQL
43 lines
1.2 KiB
SQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
CREATE TABLE IF NOT EXISTS wikis (
|
|
id UUID PRIMARY KEY DEFAULT uuidv7(),
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
title TEXT,
|
|
slug TEXT,
|
|
content TEXT,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_wikis_slug_not_deleted
|
|
ON wikis(slug)
|
|
WHERE is_deleted = false;
|
|
|
|
CREATE TABLE IF NOT EXISTS entity_wikis (
|
|
entity_id UUID REFERENCES entities(id) ON DELETE CASCADE,
|
|
wiki_id UUID REFERENCES wikis(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (entity_id, wiki_id)
|
|
);
|
|
|
|
CREATE INDEX idx_entity_wikis_project_id ON entity_wikis(project_id);
|
|
|
|
CREATE INDEX idx_entity_wikis_wiki_id
|
|
ON entity_wikis(wiki_id);
|
|
|
|
CREATE INDEX idx_wikis_created_active
|
|
ON wikis(created_at DESC)
|
|
WHERE is_deleted = false;
|
|
|
|
CREATE INDEX idx_wikis_title_search
|
|
ON wikis USING GIN (title gin_trgm_ops)
|
|
WHERE is_deleted = false;
|
|
|
|
CREATE INDEX idx_wikis_project_id ON wikis(project_id);
|
|
|
|
CREATE TRIGGER trigger_wikis_updated_at
|
|
BEFORE UPDATE ON wikis
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at(); |