Index: src/main/java/com/db/finki/www/build_board/controller/HomePageController.java =================================================================== --- src/main/java/com/db/finki/www/build_board/controller/HomePageController.java (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ src/main/java/com/db/finki/www/build_board/controller/HomePageController.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -1,5 +1,8 @@ package com.db.finki.www.build_board.controller; +import com.db.finki.www.build_board.service.threads.ThreadsWithTittleService; +import com.db.finki.www.build_board.service.threads.TopicsService; import org.springframework.stereotype.Controller; +import org.springframework.ui.Model; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; @@ -8,6 +11,13 @@ @RequestMapping("/") public class HomePageController { + private final ThreadsWithTittleService threadsWithTittleService; + + public HomePageController(ThreadsWithTittleService threadsWithTittleService) { + this.threadsWithTittleService = threadsWithTittleService; + } + @GetMapping - public String homePage() { + public String homePage(Model model) { + model.addAttribute("threads",threadsWithTittleService.findAll()); return "home"; } Index: src/main/java/com/db/finki/www/build_board/entity/BBUser.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/BBUser.java (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ src/main/java/com/db/finki/www/build_board/entity/BBUser.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -1,4 +1,5 @@ package com.db.finki.www.build_board.entity; +import com.db.finki.www.build_board.entity.threads.BBThread; import jakarta.persistence.*; import lombok.Getter; @@ -28,4 +29,7 @@ private String sex; + @OneToMany(mappedBy = "user") + private List threads; + @Override public boolean isEnabled() { Index: src/main/java/com/db/finki/www/build_board/entity/threads/BBThread.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/threads/BBThread.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/entity/threads/BBThread.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,30 @@ +package com.db.finki.www.build_board.entity.threads; + +import com.db.finki.www.build_board.entity.BBUser; +import jakarta.persistence.*; +import lombok.Data; +import lombok.Getter; +import lombok.NoArgsConstructor; +import lombok.Setter; + +import java.math.BigInteger; + +@Entity +@Getter +@Setter +@NoArgsConstructor +@Table(name = "thread") +@Inheritance(strategy = InheritanceType.JOINED) +public class BBThread { + @Id + @GeneratedValue(strategy = GenerationType.IDENTITY) + private Integer id; + + private String content; + + @ManyToOne + @JoinColumn(name="user_id") + private BBUser user; + + public Integer getId() {return id;} +} Index: src/main/java/com/db/finki/www/build_board/entity/threads/Topic.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/threads/Topic.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/entity/threads/Topic.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,37 @@ +package com.db.finki.www.build_board.entity.threads; + +import com.db.finki.www.build_board.entity.threads.interfaces.ThreadsWithTittle; +import jakarta.persistence.Entity; +import jakarta.persistence.JoinColumn; +import jakarta.persistence.ManyToOne; +import jakarta.persistence.Table; +import lombok.Data; +import lombok.NoArgsConstructor; +import org.hibernate.annotations.JdbcTypeCode; +import org.hibernate.type.SqlTypes; + +import java.math.BigInteger; +import java.util.Map; + +@Entity +@Data +@NoArgsConstructor +@Table(name = "topic_thread") +public class Topic extends BBThread implements ThreadsWithTittle { + + private String title; + + @JdbcTypeCode(SqlTypes.JSON) + private Map guidelines; + + @ManyToOne + @JoinColumn(name = "parent_id") + private Topic parent; + + @Override + public String getTypeName() {return "topic";} + @Override + public Integer getId() {return super.getId();} + @Override + public String getTitle() {return this.title;} +} Index: src/main/java/com/db/finki/www/build_board/entity/threads/interfaces/ThreadsWithTittle.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/threads/interfaces/ThreadsWithTittle.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/entity/threads/interfaces/ThreadsWithTittle.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,9 @@ +package com.db.finki.www.build_board.entity.threads.interfaces; + +import java.math.BigInteger; + +public interface ThreadsWithTittle { + String getTitle(); + String getTypeName(); + Integer getId(); +} Index: src/main/java/com/db/finki/www/build_board/repository/threads/BBThreadRepository.java =================================================================== --- src/main/java/com/db/finki/www/build_board/repository/threads/BBThreadRepository.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/repository/threads/BBThreadRepository.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,7 @@ +package com.db.finki.www.build_board.repository.threads; + +import com.db.finki.www.build_board.entity.threads.BBThread; +import org.springframework.data.jpa.repository.JpaRepository; + +public interface BBThreadRepository extends JpaRepository { +} Index: src/main/java/com/db/finki/www/build_board/repository/threads/TopicRepository.java =================================================================== --- src/main/java/com/db/finki/www/build_board/repository/threads/TopicRepository.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/repository/threads/TopicRepository.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,7 @@ +package com.db.finki.www.build_board.repository.threads; + +import com.db.finki.www.build_board.entity.threads.Topic; +import org.springframework.data.jpa.repository.JpaRepository; + +public interface TopicRepository extends JpaRepository { +} Index: src/main/java/com/db/finki/www/build_board/service/threads/ThreadsWithTittleService.java =================================================================== --- src/main/java/com/db/finki/www/build_board/service/threads/ThreadsWithTittleService.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/service/threads/ThreadsWithTittleService.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,24 @@ +package com.db.finki.www.build_board.service.threads; + +import com.db.finki.www.build_board.entity.threads.interfaces.ThreadsWithTittle; +import org.springframework.stereotype.Service; + +import java.util.Collections; +import java.util.List; + +@Service +public class ThreadsWithTittleService { + //TODO: Add project support also + private final TopicsService topicsService; + + public ThreadsWithTittleService(TopicsService topicsService) { + this.topicsService = topicsService; + } + + public List findAll(){ + List results = (List) (List) topicsService.findAll(); + + Collections.shuffle(results); + return results; + } +} Index: src/main/java/com/db/finki/www/build_board/service/threads/TopicsService.java =================================================================== --- src/main/java/com/db/finki/www/build_board/service/threads/TopicsService.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/java/com/db/finki/www/build_board/service/threads/TopicsService.java (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,22 @@ +package com.db.finki.www.build_board.service.threads; + +import com.db.finki.www.build_board.entity.threads.BBThread; +import com.db.finki.www.build_board.entity.threads.Topic; +import com.db.finki.www.build_board.repository.threads.TopicRepository; +import org.springframework.stereotype.Service; +import org.springframework.data.util.Pair; +import java.util.List; + +@Service +public class TopicsService { + private final TopicRepository topicRepository; + + public TopicsService(TopicRepository topicRepository) { + this.topicRepository = topicRepository; + } + + public List findAll() { + return topicRepository.findAll(); + } + +} Index: src/main/resources/application.properties =================================================================== --- src/main/resources/application.properties (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ src/main/resources/application.properties (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -7,2 +7,3 @@ spring.flyway.password=${POSTGRES_PASSWORD} spring.flyway.user=${POSTGRES_USER} +spring.jpa.hibernate.ddl-auto=validate Index: src/main/resources/db/migration/U1__remove_ddl.sql =================================================================== --- src/main/resources/db/migration/U1__remove_ddl.sql (revision 677a272c7bee18583dc6df16de74ebcb32b65696) +++ src/main/resources/db/migration/U1__remove_ddl.sql (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -0,0 +1,38 @@ +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_insert_project_manager CASCADE; +drop function if exists fn_insert_topics_creator_as_moderator CASCADE; +drop function if exists fn_validate_topic_title CASCADE; +drop function if exists clean_tables CASCADE; +drop function if exists clean_routines CASCADE; +DROP TRIGGER IF EXISTS validate_same_parent ON discussion_thread CASCADE; + Index: src/main/resources/db/migration/V1__init_ddl.sql =================================================================== --- src/main/resources/db/migration/V1__init_ddl.sql (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ src/main/resources/db/migration/V1__init_ddl.sql (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -1,151 +1,320 @@ -create table users( - username varchar(32) primary key, - 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, - description text, - logo_url text, - title varchar(32), - username varchar(32) references users(username) -); - -create table likes( - username varchar(32) references users(username), - thread_id int references thread(id), - primary key(username, thread_id) -); - -create table threads_moderators( - thread_id int references thread(id) on delete cascade, - username varchar(32) references users(username) on delete cascade, - primary key(thread_id, username) -); - -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 ( - guidelines jsonb, - next_discussion_id int -) 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( - username varchar(32) references users(username) on delete cascade, - project_id varchar(32) references users(username) on delete cascade, - primary key(username,project_id) -); - -create table project_thread ( - repo_url text -) inherits (thread); - -create table discussion_thread( - text text, - id int, - topic_id int not null references thread(id), - created_by_user varchar(32) not null references users(username), - reply_discussion int, - reply_topic_id int, - primary key(topic_id, id), - check ((reply_discussion is null) and (topic_id = reply_topic_id)) -); - -create table developer_associated_with_project( - project_id int references thread(id), - developer varchar(32) references users(username), - started_at timestamp, - ended_at timestamp, - primary key(project_id, developer, 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( - username varchar(32) references users(username), - project_id int, - role_name varchar(32), - FOREIGN KEY (role_name, project_id) - REFERENCES project_roles(name, project_id), - primary key(username, 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) -); - +--- 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; +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_insert_project_manager CASCADE; +drop function if exists fn_insert_topics_creator_as_moderator CASCADE; +drop function if exists fn_validate_topic_title CASCADE; +drop function if exists clean_tables CASCADE; +drop function if exists clean_routines 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 +( + 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, - reason varchar (200), - status status, - submited_by_user varchar(32) references users(username) not null, - for_project 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 varchar(32) references users(username) not null, - by_user varchar(32) references users(username) not null -); - -create table channel ( - name varchar (64), - description varchar(200), - logo_url text, - project_id int references thread(id) on delete cascade, - developer varchar(32) references users(username), - primary key(name, project_id) -); - -create table messages ( - sent_at timestamp, - content varchar(200), - sent_by varchar(32) references users(username) 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 OR REPLACE FUNCTION fn_validate_topic_title() + RETURNS TRIGGER + LANGUAGE plpgsql +AS +$$ +BEGIN + 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 OR REPLACE FUNCTION fn_insert_project_manager() +RETURNS TRIGGER +LANGUAGE plpgsql +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 + BEFORE INSERT OR UPDATE + 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_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: c/main/resources/db/migration/V2__init_ddl-corrected.sql =================================================================== --- src/main/resources/db/migration/V2__init_ddl-corrected.sql (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ (revision ) @@ -1,264 +1,0 @@ ---- 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\ - - - --- 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 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( - username varchar(32) primary key, - 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, - username varchar(32) references users(username) -); - -create table likes( - username varchar(32) references users(username), - thread_id int references thread(id), - primary key(username, thread_id) -); - -create table topic_threads_moderators( - thread_id int references thread(id) on delete cascade, - username varchar(32) references users(username) on delete cascade, - primary key(thread_id, username) -); - -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, - username varchar(32) references users(username) on delete cascade, - moderator varchar(32) references users(username) on delete cascade, - primary key(username,moderator,topic_id) -); - -create table project_thread ( - title varchar(32), - repo_url text -) inherits (thread); - -create table discussion_thread( - created_by_user varchar(32) not null references users(username), - 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 varchar(32) references users(username), - started_at timestamp, - ended_at timestamp, - primary key(project_id, developer, 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( - username varchar(32) references users(username), - project_id int, - role_name varchar(32), - FOREIGN KEY (role_name, project_id) - REFERENCES project_roles(name, project_id), - primary key(username, 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, - submited_by_user varchar(32) references users(username) 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 varchar(32) references users(username) not null, - by_user varchar(32) references users(username) not null -); - -create table channel ( - name varchar (64), - description varchar(200), - logo_url text, - project_id int references thread(id) on delete cascade, - developer varchar(32) references users(username), - primary key(name, project_id) -); - -create table messages ( - sent_at timestamp, - content varchar(200), - sent_by varchar(32) references users(username) 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) -); - - --------------------------- 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(); - - - - - Index: c/main/resources/db/migration/V3__init_ddl.sql =================================================================== --- src/main/resources/db/migration/V3__init_ddl.sql (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ (revision ) @@ -1,320 +1,0 @@ ---- 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; -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_insert_project_manager CASCADE; -drop function if exists fn_insert_topics_creator_as_moderator CASCADE; -drop function if exists fn_validate_topic_title CASCADE; -drop function if exists clean_tables CASCADE; -drop function if exists clean_routines 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 -( - 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 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 OR REPLACE FUNCTION fn_validate_topic_title() - RETURNS TRIGGER - LANGUAGE plpgsql -AS -$$ -BEGIN - 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 OR REPLACE FUNCTION fn_insert_project_manager() -RETURNS TRIGGER -LANGUAGE plpgsql -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 - BEFORE INSERT OR UPDATE - 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_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: src/main/resources/templates/home.html =================================================================== --- src/main/resources/templates/home.html (revision 71ac555e78384cdef99014906affeafbde48a7f5) +++ src/main/resources/templates/home.html (revision 677a272c7bee18583dc6df16de74ebcb32b65696) @@ -1,4 +1,7 @@ - + @@ -7,18 +10,18 @@ - -

- -

- +