RelationalDesign: buildboard-ddl.sql

---- DROP TABLES
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS moderator CASCADE;
DROP TABLE IF EXISTS developer CASCADE;
DROP TABLE IF EXISTS project_manager CASCADE;
DROP TABLE IF EXISTS thread CASCADE;
DROP TABLE IF EXISTS likes CASCADE;
DROP TABLE IF EXISTS topic_threads_moderators CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
DROP TABLE IF EXISTS tag_threads CASCADE;
DROP TABLE IF EXISTS topic_thread CASCADE;
DROP TABLE IF EXISTS topic_belongs_to_project CASCADE;
DROP TABLE IF EXISTS topic_blacklist CASCADE;
DROP TABLE IF EXISTS project_thread CASCADE;
DROP TABLE IF EXISTS discussion_thread CASCADE;
DROP TABLE IF EXISTS developer_associated_with_project CASCADE;
DROP TABLE IF EXISTS permissions CASCADE;
DROP TABLE IF EXISTS project_roles CASCADE;
DROP TABLE IF EXISTS users_project_roles CASCADE;
DROP TABLE IF EXISTS project_roles_permissions CASCADE;
DROP TABLE IF EXISTS project_request CASCADE;
DROP TABLE IF EXISTS report CASCADE;
DROP TABLE IF EXISTS channel CASCADE;
DROP TABLE IF EXISTS messages CASCADE;
DROP TABLE IF EXISTS threads_moderators CASCADE;
DROP TABLE IF EXISTS blacklisted_user CASCADE;
DROP TYPE IF EXISTS status;
DROP FUNCTION IF EXISTS add_child_topic;
DROP FUNCTION IF EXISTS validate_same_parent;
DROP FUNCTION IF EXISTS validate_topic_title;
---- DDL
CREATE TABLE users
(
    id SERIAL PRIMARY KEY,
    username VARCHAR(32) UNIQUE NOT NULL,
    is_activate bool,
    password VARCHAR(72) NOT NULL,
    description VARCHAR(200),
    registered_at TIMESTAMP,
    sex VARCHAR(1)
);
CREATE TABLE moderator
(
) INHERITS (users);
CREATE TABLE developer
(
) INHERITS (users);
CREATE TABLE project_manager
(
) INHERITS (users);
CREATE TABLE thread
(
    id SERIAL PRIMARY KEY,
    content TEXT,
    user_id INT REFERENCES users (id) NOT NULL
);
CREATE TABLE likes
(
    user_id INT REFERENCES users (id),
    thread_id INT REFERENCES thread (id),
    PRIMARY KEY (user_id, thread_id)
);
CREATE TABLE topic_threads_moderators
(
    thread_id INT REFERENCES thread (id) ON DELETE CASCADE,
    user_id INT REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (thread_id, user_id)
);
CREATE TABLE tag
(
    name VARCHAR(64) PRIMARY KEY
);
CREATE TABLE tag_threads
(
    thread_id INT REFERENCES thread (id),
    tag_name VARCHAR(64) REFERENCES tag (name),
    PRIMARY KEY (thread_id, tag_name)
);
CREATE TABLE topic_thread
(
    title VARCHAR(32) NOT NULL,
    guidelines jsonb,
    parent_topic_id INT REFERENCES thread (id)
) INHERITS (thread);
CREATE TABLE topic_belongs_to_project
(
    topic_id INT REFERENCES thread (id) ON DELETE CASCADE,
    project_id INT REFERENCES thread (id) ON DELETE CASCADE,
    PRIMARY KEY (topic_id, project_id)
);
CREATE TABLE blacklisted_user
(
    topic_id INT REFERENCES thread (id) ON DELETE CASCADE,
    user_id INT REFERENCES users (id) ON DELETE CASCADE,
    moderator_id INT REFERENCES users (id) ON DELETE CASCADE,
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    reason TEXT,
    PRIMARY KEY (user_id, moderator_id, topic_id, start_date)
);
CREATE TABLE project_thread
(
    title VARCHAR(32) NOT NULL,
    repo_url TEXT
) INHERITS (thread);
CREATE TABLE discussion_thread
(
    user_id INT REFERENCES users (id),
    reply_discussion_id INT REFERENCES thread (id),
    topic_id INT REFERENCES thread (id) NOT NULL
) INHERITS (thread);
CREATE TABLE developer_associated_with_project
(
    project_id INT REFERENCES thread (id),
    developer_id INT REFERENCES users (id),
    started_at TIMESTAMP,
    ended_at TIMESTAMP,
    PRIMARY KEY (project_id, developer_id, started_at)
);
CREATE TABLE permissions
(
    name VARCHAR(32) PRIMARY KEY
);
CREATE TABLE project_roles
(
    name VARCHAR(32),
    project_id INT REFERENCES thread (id) ON DELETE CASCADE,
    description TEXT,
    PRIMARY KEY (name, project_id)
);
CREATE TABLE users_project_roles
(
    user_id INT REFERENCES users (id),
    project_id INT,
    role_name VARCHAR(32),
    FOREIGN KEY (role_name, project_id)
        REFERENCES project_roles (name, project_id),
    PRIMARY KEY (user_id, project_id, role_name)
);
CREATE TABLE project_roles_permissions
(
    permission_name VARCHAR(32) REFERENCES permissions (name),
    role_name VARCHAR(32),
    project_id INT,
    PRIMARY KEY (permission_name, role_name, project_id),
    FOREIGN KEY (role_name, project_id)
        REFERENCES project_roles (name, project_id)
);
CREATE TYPE status AS ENUM ('ACCEPTED', 'DENIED', 'PENDING');
CREATE TABLE project_request
(
    id SERIAL PRIMARY KEY,
    description VARCHAR(200) ,
    status status NOT NULL,
    user_id INT REFERENCES users (id) NOT NULL,
    project_id INT REFERENCES thread (id) NOT NULL
);
CREATE TABLE report
(
    id SERIAL,
    created_at TIMESTAMP,
    description VARCHAR(200) NOT NULL,
    status status,
    thread_id INT REFERENCES thread (id),
    for_user_id INT REFERENCES users (id),
    by_user_id INT REFERENCES users (id),
    PRIMARY KEY (id, thread_id, for_user_id, by_user_id)
);
CREATE TABLE channel
(
    name VARCHAR(64),
    description VARCHAR(200),
    project_id INT REFERENCES thread (id) ON DELETE CASCADE,
    developer_id INT REFERENCES users (id),
    PRIMARY KEY (name, project_id)
);
CREATE TABLE messages
(
    sent_at TIMESTAMP,
    content VARCHAR(200) NOT NULL,
    sent_by INT REFERENCES users (id),
    project_id INT,
    channel_name VARCHAR(64),
    FOREIGN KEY (channel_name, project_id)
        REFERENCES channel (name, project_id) ON DELETE CASCADE,
    PRIMARY KEY (channel_name, project_id, sent_at, sent_by)
);
-------------------------- FUNCTIONS ----------------------
CREATE FUNCTION validate_topic_title()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF new.title IN
       (SELECT title
        FROM topic_thread
                 AS t
        WHERE t.parent_topic_id = new.parent_topic_id)
    THEN
        RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_topic_id;
    END IF;
    RETURN new;
END;
$$;
CREATE FUNCTION add_child_topic()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    project_id INT;
BEGIN
    SELECT t.project_id
    INTO project_id
    FROM topic_belongs_to_project AS t
    WHERE new.id = t.topic_id;
    INSERT INTO topic_belongs_to_project VALUES (new.id, project_id);
END;
$$;
CREATE FUNCTION validate_same_parent()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM discussion_thread
                            AS dt
                   WHERE new.reply_discussion_id = dt.id
                     AND dt.topic_id = new.topic_id) THEN
        RAISE EXCEPTION 'Can not reply to a discussion that is not in the same topic';
    END IF;
    RETURN new;
END;
$$;

-------------------------- TRIGGERS ----------------------

CREATE OR REPLACE TRIGGER check_topic_name
    BEFORE INSERT OR UPDATE
    ON topic_thread
    FOR EACH ROW
EXECUTE FUNCTION validate_topic_title();

CREATE OR REPLACE TRIGGER project_insert_child_topic
    AFTER INSERT
    ON topic_thread
    FOR EACH ROW
EXECUTE FUNCTION add_child_topic();

CREATE OR REPLACE TRIGGER check_same_parent
    BEFORE INSERT
    ON discussion_thread
    FOR EACH ROW
EXECUTE FUNCTION validate_same_parent();