Index: docs/UseCase.txt =================================================================== --- docs/UseCase.txt (revision bc963876052cfd8d3eecad13add38dc5df078c43) +++ docs/UseCase.txt (revision 2d3f3a31f600db46ca1196dd4f75fe4c39e3b6bf) @@ -40,10 +40,10 @@ - Vo kolku proekti imat ucestvuvano daden developer d. - Od proektite so ucestvuval, vo koj najdolgo ucestvuval daden developer d. - - Kolku poraki imat pusteno nekoj developer d vo nekoj kanal k. - - Kolku poraki imat pusteno nekoj developer d vo vkupno vo site kanali na proekt p. - - Kolku developers imat vo proekt p - - Kolku razlicni developeri blacklistnal proektot p. + - Site poraki od daden developer vo daden kanal ili od site kanali + - Search spored content na poraka vo kanal + - Koi developers imat vo proekt p + //- Kolku razlicni developeri blacklistnal proektot p. - Kolu pati daden developer d imat ucevstvuvano vo proekt p (primer ako zaprit pa pak vlezit pa pak...) - - Kolku kanali imat kreirano daden developer d + - Kolku kanali imat kreirano daden developer d (mozda za badge) - Kolku useri vlegle vo proekt p vo daden interval na vreme i. Index: docs/buildboard-ddl.sql =================================================================== --- docs/buildboard-ddl.sql (revision bc963876052cfd8d3eecad13add38dc5df078c43) +++ docs/buildboard-ddl.sql (revision 2d3f3a31f600db46ca1196dd4f75fe4c39e3b6bf) @@ -1,3 +1,7 @@ ----- DROP TABLES +--- Trigger before update/insert za check na iminjata topic/discussion -> OK +--- Trigger za ko ke adnit dete na topic thread sho e vo proekt, da go dodajt kako belongs_to vo proektot +--- Trigger za check dali reply na discussion thread pripagjat na ist topic thread kako na toj so mu pret reply +--- IMENUVANJE: triggeri so provervat nesto prefix = check, funkcii za istite prefix = validate +--- Nemame contraint sho velit deka sekoj topic thread trebat da e moderiran DROP TABLE IF EXISTS users CASCADE; DROP TABLE IF EXISTS moderator CASCADE; @@ -11,5 +15,5 @@ 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 blacklisted_user CASCADE; DROP TABLE IF EXISTS project_thread CASCADE; DROP TABLE IF EXISTS discussion_thread CASCADE; @@ -24,196 +28,291 @@ DROP TABLE IF EXISTS messages CASCADE; DROP TABLE IF EXISTS threads_moderators 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; +DROP TYPE IF EXISTS status; +DROP VIEW IF EXISTS v_topic_thread CASCADE; +DROP VIEW IF EXISTS v_project_thread CASCADE; +DROP VIEW IF EXISTS v_discussion_thread CASCADE; +DROP VIEW IF EXISTS v_developer CASCADE; +DROP VIEW IF EXISTS v_project_owner CASCADE; +DROP VIEW IF EXISTS v_moderator CASCADE; +DROP FUNCTION IF EXISTS fn_project_insert_child_topic CASCADE; +DROP FUNCTION IF EXISTS fn_validate_same_parent CASCADE; +DROP TRIGGER IF EXISTS validate_same_parent ON discussion_thread CASCADE; ---- DDL -create table users( - id serial PRIMARY key, - username varchar(32) UNIQUE NOT NULL, - is_activate bool, - password varchar(72), - 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) -); -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), - guidelines jsonb, - next_discussion_id int, - 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 topic_blacklist( - 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, - primary key(user_id,moderator_id,topic_id,start_date) -); -create table project_thread ( - title varchar(32), - repo_url text -) inherits (thread); -create table discussion_thread( - user_id int not null references users(id), - reply_discussion_id int REFERENCES thread(id), - topic_id int REFERENCES thread(id) -) 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 not null, - 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 TABLE users +( + id SERIAL PRIMARY KEY, + username VARCHAR(32) UNIQUE NOT NULL, + is_activate bool, + password VARCHAR(72), + description VARCHAR(200), + registered_at TIMESTAMP, + sex VARCHAR(1) +); +CREATE TABLE moderator +( + id INT PRIMARY KEY REFERENCES users (id) on delete cascade +); +CREATE TABLE developer +( + id INT PRIMARY KEY REFERENCES users (id) on delete cascade +); +CREATE TABLE project_manager +( + id INT PRIMARY KEY REFERENCES users (id) on delete cascade +); +CREATE TABLE thread +( + id SERIAL PRIMARY KEY, + content TEXT, + user_id INT REFERENCES users (id) NOT NULL +); +CREATE TABLE topic_thread +( + title VARCHAR(32) NOT NULL, + guidelines jsonb, + parent_id INT REFERENCES thread (id), + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade +); +CREATE TABLE discussion_thread +( + parent_id INT REFERENCES thread (id) NOT NULL, + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade +); +CREATE TABLE project_thread +( + title VARCHAR(32) NOT NULL, + repo_url TEXT, + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade +); +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_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 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, - user_id int references users(id) not null, - project_id int references thread(id) not null -); -create table report( - id serial primary key, - created_at timestamp, - description varchar(200), - status status, - thread_id int references thread(id) not null, - for_user_id int references users(id) not null, - by_user_id int references users(id) not null -); -create table channel ( - name varchar (64), - description varchar(200), - logo_url text, - 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), - sent_by int references users(id) not null, - 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) -); +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) +); +------------------------VIEWS----------------------------- +CREATE OR REPLACE VIEW v_project_thread +AS +SELECT thread.id, content, user_id, title, repo_url +FROM project_thread project + JOIN thread + ON project.id = thread.id; +CREATE OR REPLACE VIEW v_discussion_thread +AS +SELECT thread.id, content, user_id,parent_id +FROM discussion_thread discussion + JOIN thread + ON discussion.id = thread.id; +CREATE OR REPLACE VIEW v_topic_thread +AS +SELECT thread.id, content, user_id, title, guidelines, parent_id +FROM topic_thread topic + JOIN thread + ON topic.id = thread.id; +CREATE OR REPLACE VIEW v_moderator +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM moderator + JOIN users ON moderator.id = users.id; + +CREATE OR REPLACE VIEW v_developer +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM developer + JOIN users ON developer.id = users.id; +CREATE OR REPLACE VIEW v_project_owner +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM project_manager + JOIN users ON project_manager.id = users.id; +CREATE OR REPLACE VIEW v_moderator +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM moderator + JOIN users ON moderator.id = users.id; + +CREATE OR REPLACE VIEW v_developer +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM developer + JOIN users ON developer.id = users.id; +CREATE OR REPLACE VIEW v_project_owner +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM project_manager + JOIN users ON project_manager.id = users.id; -------------------------- FUNCTIONS ---------------------- -CREATE FUNCTION validate_topic_title() - RETURNS trigger +CREATE OR REPLACE FUNCTION fn_validate_topic_title() + RETURNS TRIGGER LANGUAGE plpgsql -as $$ +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; + IF new.title IN + (SELECT title + FROM topic_thread + AS t + WHERE t.parent_id = new.parent_id OR (t.parent_id IS NULL AND new.parent_id IS NULL)) + THEN + RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id; END IF; +RETURN new; +END; +$$; +CREATE OR REPLACE FUNCTION fn_insert_topics_creator_as_moderator() + RETURNS TRIGGER + LANGUAGE plpgsql +AS +$$ +DECLARE +v_user_id INT; +BEGIN +SELECT v_topic_thread.user_id +INTO v_user_id +FROM v_topic_thread +WHERE v_topic_thread.id = new.id; +INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id); RETURN NEW; END; $$; -CREATE FUNCTION add_child_topic() - RETURNS TRIGGER - LANGUAGE plpgsql -AS $$ -DECLARE -project_id INT; + +CREATE OR REPLACE FUNCTION fn_insert_project_manager() +RETURNS TRIGGER +LANGUAGE plpgsql +AS + $$ + DECLARE usrId 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; +SELECT user_id INTO usrId FROM v_project_thread p WHERE NEW.id = p.id; +INSERT INTO developer VALUES (usrId); +INSERT INTO project_manager VALUES (usrId); RETURN NEW; END; -$$; + $$; -------------------------- TRIGGERS ---------------------- -CREATE or replace TRIGGER check_topic_name - BEFORE INSERT OR UPDATE ON topic_thread +CREATE OR REPLACE TRIGGER tr_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(); + EXECUTE FUNCTION fn_validate_topic_title(); +CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator + AFTER INSERT + ON topic_thread + FOR EACH ROW +EXECUTE FUNCTION fn_insert_topics_creator_as_moderator(); +CREATE OR REPLACE TRIGGER tr_insert_project_manager + AFTER INSERT + ON project_thread + FOR EACH ROW + EXECUTE FUNCTION fn_insert_project_manager(); + Index: docs/test-data.sql =================================================================== --- docs/test-data.sql (revision 2d3f3a31f600db46ca1196dd4f75fe4c39e3b6bf) +++ docs/test-data.sql (revision 2d3f3a31f600db46ca1196dd4f75fe4c39e3b6bf) @@ -0,0 +1,110 @@ +INSERT INTO users (username, is_activate, password, description, registered_at, sex) +VALUES + ('user1', true, 'password1', 'First user', NOW(), 'M'), + ('user2', true, 'password2', 'Second user', NOW(), 'F'), + ('user3', true, 'password3', 'Third user', NOW(), 'M'), + ('user4', true, 'password4', 'Fourth user', NOW(), 'F'), + ('user5', true, 'password5', 'Fifth user', NOW(), 'M'); + +INSERT INTO thread (content, user_id) +VALUES + ('Main content for topic thread 1', 1), + ('Main content for topic thread 2', 2), + ('Discussion content for topic 1', 1), + ('Discussion content for topic 2', 2), + ('Project-specific thread content', 3), + ('Reply to topic 1', 4), + ('Further discussion on topic 2', 5); + +INSERT INTO topic_thread (id, title, guidelines, parent_id) +VALUES + (1, 'Topic 1', '{"rule1": "Follow guidelines"}', NULL), + (2, 'Topic 2', '{"rule2": "Be respectful"}', NULL); + +INSERT INTO discussion_thread (id, parent_id) +VALUES + (3, 1), + (4, 2), + (6, 3), + (7, 4); + +INSERT INTO project_thread (id, title, repo_url) +VALUES + (5, 'Project 1 Thread', 'http://github.com/project1'); + +INSERT INTO likes (user_id, thread_id) +VALUES + (1, 3), + (2, 4), + (3, 5), + (4, 6), + (5, 7); + +INSERT INTO tag (name) +VALUES + ('Tag1'), + ('Tag2'), + ('Tag3'); + +INSERT INTO tag_threads (thread_id, tag_name) +VALUES + (1, 'Tag1'), + (3, 'Tag2'), + (5, 'Tag3'), + (6, 'Tag1'); + +INSERT INTO topic_belongs_to_project (topic_id, project_id) +VALUES + (1, 5), + (2, 5); + +INSERT INTO blacklisted_user (topic_id, user_id, moderator_id, start_date, end_date, reason) +VALUES + (1, 2, 1, NOW(), NOW() + INTERVAL '7 days', 'Spamming'), + (2, 3, 4, NOW(), NOW() + INTERVAL '3 days', 'Offensive language'); + +INSERT INTO developer_associated_with_project (project_id, developer_id, started_at) +VALUES + (5, 2, NOW()), + (5, 3, NOW()); + +INSERT INTO permissions (name) +VALUES + ('Create Thread'), + ('Delete Thread'); + +INSERT INTO project_roles (name, project_id, description) +VALUES + ('Admin', 5, 'Admin role for the project'), + ('Developer', 5, 'Developer role for the project'); + +INSERT INTO users_project_roles (user_id, project_id, role_name) +VALUES + (3, 5, 'Admin'), + (2, 5, 'Developer'), + (4, 5, 'Developer'); + +INSERT INTO project_roles_permissions (permission_name, role_name, project_id) +VALUES + ('Create Thread', 'Admin', 5), + ('Delete Thread', 'Admin', 5); + +INSERT INTO project_request (description, status, user_id, project_id) +VALUES + ('Request to join Project 1', 'PENDING', 2, 5), + ('Request to join Project 1', 'ACCEPTED', 4, 5); + +INSERT INTO report (created_at, description, status, thread_id, for_user_id, by_user_id) +VALUES + (NOW(), 'Inappropriate content', 'PENDING', 3, 2, 1), + (NOW(), 'Spam content', 'DENIED', 6, 4, 3); + +INSERT INTO channel (name, description, project_id, developer_id) +VALUES + ('General', 'General discussion channel', 5, 2), + ('Updates', 'Project updates channel', 5, 3); + +INSERT INTO messages (sent_at, content, sent_by, project_id, channel_name) +VALUES + (NOW(), 'Hello, team!', 2, 5, 'General'), + (NOW(), 'We need to push the deadline.', 3, 5, 'Updates'); Index: src/main/resources/db/migration/V3__init_ddl.sql =================================================================== --- src/main/resources/db/migration/V3__init_ddl.sql (revision bc963876052cfd8d3eecad13add38dc5df078c43) +++ src/main/resources/db/migration/V3__init_ddl.sql (revision 2d3f3a31f600db46ca1196dd4f75fe4c39e3b6bf) @@ -4,61 +4,64 @@ --- IMENUVANJE: triggeri so provervat nesto prefix = check, funkcii za istite prefix = validate --- Nemame contraint sho velit deka sekoj topic thread trebat da e moderiran - -CREATE OR REPLACE PROCEDURE clean_tables(schema_name text) -LANGUAGE plpgsql -AS $$ - DECLARE - row RECORD; - BEGIN - FOR row in SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema=schema_name - LOOP - EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(row.table_name) || ' CASCADE'; - END LOOP; - END; -$$; - -CREATE OR REPLACE PROCEDURE clean_routines(schema_name text) - LANGUAGE plpgsql -AS -$$ -DECLARE - row RECORD; -BEGIN - FOR row IN SELECT * - FROM information_schema.routines - WHERE specific_schema = schema_name - AND routine_type <> 'PROCEDURE' - LOOP - EXECUTE 'DROP FUNCTION IF EXISTS ' || quote_ident(row.routine_name) || ' CASCADE'; - END LOOP; -END; -$$; - +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 blacklisted_user 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 TYPE IF EXISTS status; +DROP VIEW IF EXISTS v_topic_thread CASCADE; +DROP VIEW IF EXISTS v_project_thread CASCADE; +DROP VIEW IF EXISTS v_discussion_thread CASCADE; +DROP VIEW IF EXISTS v_developer CASCADE; +DROP VIEW IF EXISTS v_project_owner CASCADE; +DROP VIEW IF EXISTS v_moderator CASCADE; +DROP FUNCTION IF EXISTS fn_project_insert_child_topic CASCADE; +DROP FUNCTION IF EXISTS fn_validate_same_parent CASCADE; +DROP TRIGGER IF EXISTS validate_same_parent ON discussion_thread CASCADE; ---- DDL CREATE TABLE users ( - id SERIAL PRIMARY KEY, - username VARCHAR(32) UNIQUE NOT NULL, - is_activate bool, - password VARCHAR(72), - description VARCHAR(200), + id SERIAL PRIMARY KEY, + username VARCHAR(32) UNIQUE NOT NULL, + is_activate bool, + password VARCHAR(72), + description VARCHAR(200), registered_at TIMESTAMP, - sex VARCHAR(1) + sex VARCHAR(1) ); CREATE TABLE moderator ( - id INT PRIMARY KEY REFERENCES users(id) + id INT PRIMARY KEY REFERENCES users (id) on delete cascade ); CREATE TABLE developer ( - id INT PRIMARY KEY REFERENCES users(id) -); + id INT PRIMARY KEY REFERENCES users (id) on delete cascade +); CREATE TABLE project_manager ( - id INT PRIMARY KEY REFERENCES users(id) + id INT PRIMARY KEY REFERENCES users (id) on delete cascade ); CREATE TABLE thread ( - id SERIAL PRIMARY KEY, + id SERIAL PRIMARY KEY, content TEXT, user_id INT REFERENCES users (id) NOT NULL @@ -66,25 +69,24 @@ CREATE TABLE topic_thread ( - title VARCHAR(32) NOT NULL, - guidelines jsonb, - parent_topic_id INT REFERENCES thread (id), - id INT PRIMARY KEY REFERENCES thread(id) + title VARCHAR(32) NOT NULL, + guidelines jsonb, + parent_id INT REFERENCES thread (id), + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade ); CREATE TABLE discussion_thread ( - reply_discussion_id INT REFERENCES thread (id), - topic_id INT REFERENCES thread (id) NOT NULL, - id INT PRIMARY KEY REFERENCES thread(id) + parent_id INT REFERENCES thread (id) NOT NULL, + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade ); CREATE TABLE project_thread ( - title VARCHAR(32) NOT NULL, + title VARCHAR(32) NOT NULL, repo_url TEXT, - id INT PRIMARY KEY REFERENCES thread(id) + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade ); CREATE TABLE likes ( - user_id INT REFERENCES users(id), - thread_id INT REFERENCES thread(id), + user_id INT REFERENCES users (id), + thread_id INT REFERENCES thread (id), PRIMARY KEY (user_id, thread_id) ); @@ -92,5 +94,5 @@ ( thread_id INT REFERENCES thread (id) ON DELETE CASCADE, - user_id INT REFERENCES users (id) ON DELETE CASCADE, + user_id INT REFERENCES users (id) ON DELETE CASCADE, PRIMARY KEY (thread_id, user_id) ); @@ -102,5 +104,5 @@ ( thread_id INT REFERENCES thread (id), - tag_name VARCHAR(64) REFERENCES tag (name), + tag_name VARCHAR(64) REFERENCES tag (name), PRIMARY KEY (thread_id, tag_name) ); @@ -108,5 +110,5 @@ CREATE TABLE topic_belongs_to_project ( - topic_id INT REFERENCES thread (id) ON DELETE CASCADE, + topic_id INT REFERENCES thread (id) ON DELETE CASCADE, project_id INT REFERENCES thread (id) ON DELETE CASCADE, PRIMARY KEY (topic_id, project_id) @@ -114,10 +116,10 @@ CREATE TABLE blacklisted_user ( - topic_id INT REFERENCES thread (id) ON DELETE CASCADE, - user_id INT REFERENCES users (id) ON DELETE CASCADE, + 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, + start_date TIMESTAMP, + end_date TIMESTAMP, + reason TEXT, PRIMARY KEY (user_id, moderator_id, topic_id, start_date) ); @@ -125,8 +127,8 @@ CREATE TABLE developer_associated_with_project ( - project_id INT REFERENCES thread (id), + project_id INT REFERENCES thread (id), developer_id INT REFERENCES users (id), - started_at TIMESTAMP, - ended_at TIMESTAMP, + started_at TIMESTAMP, + ended_at TIMESTAMP, PRIMARY KEY (project_id, developer_id, started_at) ); @@ -137,6 +139,6 @@ CREATE TABLE project_roles ( - name VARCHAR(32), - project_id INT REFERENCES thread (id) ON DELETE CASCADE, + name VARCHAR(32), + project_id INT REFERENCES thread (id) ON DELETE CASCADE, description TEXT, PRIMARY KEY (name, project_id) @@ -144,7 +146,7 @@ CREATE TABLE users_project_roles ( - user_id INT REFERENCES users (id), + user_id INT REFERENCES users (id), project_id INT, - role_name VARCHAR(32), + role_name VARCHAR(32), FOREIGN KEY (role_name, project_id) REFERENCES project_roles (name, project_id), @@ -154,6 +156,6 @@ ( permission_name VARCHAR(32) REFERENCES permissions (name), - role_name VARCHAR(32), - project_id INT, + role_name VARCHAR(32), + project_id INT, PRIMARY KEY (permission_name, role_name, project_id), FOREIGN KEY (role_name, project_id) @@ -163,26 +165,26 @@ 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 + 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, + id SERIAL, + created_at TIMESTAMP, description VARCHAR(200) NOT NULL, - status status, - thread_id INT REFERENCES thread (id), + status status, + thread_id INT REFERENCES thread (id), for_user_id INT REFERENCES users (id), - by_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, + 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) @@ -190,8 +192,8 @@ CREATE TABLE messages ( - sent_at TIMESTAMP, - content VARCHAR(200) NOT NULL, - sent_by INT REFERENCES users (id), - project_id INT, + 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) @@ -202,22 +204,54 @@ CREATE OR REPLACE VIEW v_project_thread AS - SELECT thread.id,content,user_id,title,repo_url - FROM project_thread project - JOIN thread - ON project.id = thread.id; +SELECT thread.id, content, user_id, title, repo_url +FROM project_thread project + JOIN thread + ON project.id = thread.id; CREATE OR REPLACE VIEW v_discussion_thread AS - SELECT thread.id,content,user_id,reply_discussion_id,topic_id - FROM discussion_thread discussion - JOIN thread - ON discussion.id = thread.id; +SELECT thread.id, content, user_id,parent_id +FROM discussion_thread discussion + JOIN thread + ON discussion.id = thread.id; CREATE OR REPLACE VIEW v_topic_thread AS - SELECT thread.id,content,user_id,title,guidelines,parent_topic_id - FROM topic_thread topic - JOIN thread - ON topic.id = thread.id; +SELECT thread.id, content, user_id, title, guidelines, parent_id +FROM topic_thread topic + JOIN thread + ON topic.id = thread.id; +CREATE OR REPLACE VIEW v_moderator +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM moderator + JOIN users ON moderator.id = users.id; + +CREATE OR REPLACE VIEW v_developer +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM developer + JOIN users ON developer.id = users.id; +CREATE OR REPLACE VIEW v_project_owner +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM project_manager + JOIN users ON project_manager.id = users.id; +CREATE OR REPLACE VIEW v_moderator +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM moderator + JOIN users ON moderator.id = users.id; + +CREATE OR REPLACE VIEW v_developer +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM developer + JOIN users ON developer.id = users.id; +CREATE OR REPLACE VIEW v_project_owner +AS +SELECT users.id, username, is_activate, password, description, registered_at, sex +FROM project_manager + JOIN users ON project_manager.id = users.id; -------------------------- FUNCTIONS ---------------------- -CREATE FUNCTION fn_validate_topic_title() +CREATE OR REPLACE FUNCTION fn_validate_topic_title() RETURNS TRIGGER LANGUAGE plpgsql @@ -229,12 +263,12 @@ FROM topic_thread AS t - WHERE t.parent_topic_id = new.parent_topic_id) + WHERE t.parent_id = new.parent_id OR (t.parent_id IS NULL AND new.parent_id IS NULL)) 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; + RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id; +END IF; +RETURN new; END; $$; -CREATE FUNCTION fn_project_insert_child_topic() +CREATE OR REPLACE FUNCTION fn_insert_topics_creator_as_moderator() RETURNS TRIGGER LANGUAGE plpgsql @@ -242,65 +276,42 @@ $$ DECLARE - parent_project_id INT; +v_user_id INT; BEGIN - IF new.parent_topic_id IS NOT NULL - THEN - SELECT project_id INTO parent_project_id FROM topic_belongs_to_project t WHERE new.parent_topic_id = t.topic_id; - IF parent_project_id IS NOT NULL THEN - INSERT INTO topic_belongs_to_project VALUES (new.id,parent_project_id); - END IF; - END IF; - RETURN new; +SELECT v_topic_thread.user_id +INTO v_user_id +FROM v_topic_thread +WHERE v_topic_thread.id = new.id; +INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id); +RETURN NEW; END; $$; -CREATE FUNCTION fn_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; -$$; - -CREATE FUNCTION fn_insert_topics_creator_as_moderator() + +CREATE OR REPLACE FUNCTION fn_insert_project_manager() RETURNS TRIGGER LANGUAGE plpgsql -as $$ - BEGIN - INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (old.id, old.user_id); - END; -$$; - - +AS + $$ + DECLARE usrId INT; +BEGIN +SELECT user_id INTO usrId FROM v_project_thread p WHERE NEW.id = p.id; +INSERT INTO developer VALUES (usrId); +INSERT INTO project_manager VALUES (usrId); +RETURN NEW; +END; + $$; -------------------------- TRIGGERS ---------------------- - -CREATE OR REPLACE TRIGGER TR_check_topic_name +CREATE OR REPLACE TRIGGER tr_check_topic_name BEFORE INSERT OR UPDATE - ON topic_thread - FOR EACH ROW -EXECUTE FUNCTION fn_Validate_topic_title(); - -CREATE OR REPLACE TRIGGER TR_project_insert_child_topic + ON topic_thread + FOR EACH ROW + EXECUTE FUNCTION fn_validate_topic_title(); +CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator AFTER INSERT ON topic_thread FOR EACH ROW -EXECUTE FUNCTION fn_Project_insert_child_topic(); - -CREATE OR REPLACE TRIGGER TR_check_same_parent - BEFORE INSERT - ON discussion_thread +EXECUTE FUNCTION fn_insert_topics_creator_as_moderator(); +CREATE OR REPLACE TRIGGER tr_insert_project_manager + AFTER INSERT + ON project_thread FOR EACH ROW -EXECUTE FUNCTION fn_Validate_same_parent(); - -CREATE TRIGGER tr_insert_topics_creator_as_moderator - AFTER INSERT ON topic_thread - FOR EACH ROW - EXECUTE FUNCTION fn_insert_topics_creator_as_moderator(); + EXECUTE FUNCTION fn_insert_project_manager();