Index: src/main/java/com/db/finki/www/build_board/controller/thread_controllers/ProjectController.java =================================================================== --- src/main/java/com/db/finki/www/build_board/controller/thread_controllers/ProjectController.java (revision 31a10a2c5c223b9dad338541ec34e59e1d1f69b5) +++ src/main/java/com/db/finki/www/build_board/controller/thread_controllers/ProjectController.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -59,4 +59,14 @@ } + @GetMapping("/{pr_title}/members") + public String getProjectMembersPage( + Model model, + @PathVariable(name = "pr_title") Project project + ) + { + model.addAttribute("project", project); + return "project_pages/members"; + } + @PreAuthorize("project.getUser().username.equals(username)") @PostMapping("/{title}/modify") @@ -103,4 +113,5 @@ return "redirect:/" ; } + } ///projects/topics/add Index: src/main/java/com/db/finki/www/build_board/controller/thread_controllers/requests/ProjectRequestController.java =================================================================== --- src/main/java/com/db/finki/www/build_board/controller/thread_controllers/requests/ProjectRequestController.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ src/main/java/com/db/finki/www/build_board/controller/thread_controllers/requests/ProjectRequestController.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,21 @@ +package com.db.finki.www.build_board.controller.thread_controllers.requests; + +import com.db.finki.www.build_board.entity.threads.Project; +import org.springframework.stereotype.Controller; +import org.springframework.ui.Model; +import org.springframework.web.bind.annotation.GetMapping; +import org.springframework.web.bind.annotation.PathVariable; +import org.springframework.web.bind.annotation.RequestMapping; + +@Controller +@RequestMapping("/project/{pr_title}/requests") +public class ProjectRequestController { + @GetMapping("/") + public String getRequestPage( + Model model, + @PathVariable(name = "pr_title") Project project + ) { + model.addAttribute("requests", project.getRequests()); + return "project_pages/requests/show-requests"; + } +} Index: src/main/java/com/db/finki/www/build_board/entity/requests/ProjectRequests.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/requests/ProjectRequests.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ src/main/java/com/db/finki/www/build_board/entity/requests/ProjectRequests.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,29 @@ +package com.db.finki.www.build_board.entity.requests; + +import com.db.finki.www.build_board.entity.threads.Project; +import com.db.finki.www.build_board.entity.user_types.BBUser; +import jakarta.persistence.*; +import lombok.Getter; +import lombok.NoArgsConstructor; +import lombok.Setter; + +@Getter +@Setter +@NoArgsConstructor +@Entity +@Table(name = "project_request") +public class ProjectRequests { + @Id + @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "project_req_gen") + @SequenceGenerator(name = "project_req_gen", sequenceName = "project_request_id_seq", initialValue = 1, allocationSize = 1) + private Integer id; + private String description; + + @ManyToOne + @JoinColumn(name = "project_id") + Project project; + + @ManyToOne + @JoinColumn(name = "user_id") + BBUser creator; +} Index: src/main/java/com/db/finki/www/build_board/entity/requests/Report.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/requests/Report.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ src/main/java/com/db/finki/www/build_board/entity/requests/Report.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,4 @@ +package com.db.finki.www.build_board.entity.requests; + +public class Report { +} Index: src/main/java/com/db/finki/www/build_board/entity/threads/Project.java =================================================================== --- src/main/java/com/db/finki/www/build_board/entity/threads/Project.java (revision 31a10a2c5c223b9dad338541ec34e59e1d1f69b5) +++ src/main/java/com/db/finki/www/build_board/entity/threads/Project.java (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -1,15 +1,20 @@ package com.db.finki.www.build_board.entity.threads; +import com.db.finki.www.build_board.entity.requests.ProjectRequests; import com.db.finki.www.build_board.entity.user_types.BBUser; import com.db.finki.www.build_board.entity.threads.interfaces.NamedThread; +import com.db.finki.www.build_board.entity.user_types.Developer; import jakarta.persistence.*; import lombok.*; import java.util.ArrayList; +import java.util.HashSet; import java.util.List; +import java.util.Set; //TODO: project request //TODO: crud na kanali -@Data +@Getter +@Setter @NoArgsConstructor @EqualsAndHashCode(callSuper = false) @@ -33,4 +38,15 @@ private List topics = new ArrayList<>(); + @ManyToMany + @JoinTable( + name ="developer_associated_with_project", + joinColumns = @JoinColumn(name = "project_id"), + inverseJoinColumns = @JoinColumn(name = "developer_id") + ) + private Set developers = new HashSet<>(); + + @OneToMany(mappedBy = "project") + private Set requests = new HashSet<>(); + @Override public String getTypeName() { Index: src/main/resources/db/migration/V1__init_ddl.sql =================================================================== --- src/main/resources/db/migration/V1__init_ddl.sql (revision 31a10a2c5c223b9dad338541ec34e59e1d1f69b5) +++ src/main/resources/db/migration/V1__init_ddl.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -93,5 +93,5 @@ CREATE TABLE project_thread ( - title VARCHAR(256) NOT NULL, + title VARCHAR(256) UNIQUE NOT NULL, repo_url TEXT, id INT PRIMARY KEY REFERENCES thread (id) on delete cascade Index: src/main/resources/templates/project_pages/members.html =================================================================== --- src/main/resources/templates/project_pages/members.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ src/main/resources/templates/project_pages/members.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,97 @@ + + + + + Members + + + + +
+ +
+

Projects Tittles

+
+
+
+ + + + + + + + +
+
+ +
+ +
+
+
Developer
+
+
+ +
+ + + + Index: c/main/resources/templates/project_pages/project.html =================================================================== --- src/main/resources/templates/project_pages/project.html (revision 31a10a2c5c223b9dad338541ec34e59e1d1f69b5) +++ (revision ) @@ -1,137 +1,0 @@ - - - - - Projects - - - - -
- -
-

Projects

-

View All Projects

-
- -
- -
-
-

Project 1

-
-
-

Short description of Project 1.

- -
-
-

More detailed information about Project 1...

-
-
-
-
- - -
-
-

Project 2

-
-
-

Short description of Project 2.

- -
-
-

More detailed information about Project 2...

-
-
-
-
- - -
-
-

Project 3

-
-
-

Short description of Project 3.

- -
-
-

More detailed information about Project 3...

-
-
-
-
- - -
-
-

Project 4

-
-
-

Short description of Project 4.

- -
-
-

More detailed information about Project 4...

-
-
-
-
- -
- - - - Index: src/main/resources/templates/project_pages/requests/show-requests.html =================================================================== --- src/main/resources/templates/project_pages/requests/show-requests.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ src/main/resources/templates/project_pages/requests/show-requests.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,15 @@ + + + + + Requests + + +
+
+
+
+ +
+ + Index: src/main/resources/templates/project_pages/show-project.html =================================================================== --- src/main/resources/templates/project_pages/show-project.html (revision 31a10a2c5c223b9dad338541ec34e59e1d1f69b5) +++ src/main/resources/templates/project_pages/show-project.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -1,4 +1,4 @@ - + @@ -15,15 +15,33 @@

Project Title

-
- +
+ + + + + + -
Index: target/classes/application.properties =================================================================== --- target/classes/application.properties (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/application.properties (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,13 @@ +spring.application.name=build_board + +spring.datasource.username=${POSTGRES_USER} +spring.datasource.password=${POSTGRES_PASSWORD} +spring.datasource.url=jdbc:postgresql://localhost:5432/${POSTGRES_DB} + +spring.flyway.password=${POSTGRES_PASSWORD} +spring.flyway.user=${POSTGRES_USER} +spring.jpa.hibernate.ddl-auto=validate +spring.jpa.show-sql=true + +avatar.upload-dir=/uploads/user-avatars +logging.level.org.springframework.security=DEBUG Index: target/classes/db/migration/U1__remove_ddl.sql =================================================================== --- target/classes/db/migration/U1__remove_ddl.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/db/migration/U1__remove_ddl.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,37 @@ +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: target/classes/db/migration/V1__init_ddl.sql =================================================================== --- target/classes/db/migration/V1__init_ddl.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/db/migration/V1__init_ddl.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,451 @@ +--- 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, + email varchar(60) not null, + name varchar(32) not null, + is_activate bool DEFAULT true, + password VARCHAR(72), + description VARCHAR(200), + registered_at TIMESTAMP DEFAULT NOW(), + 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(256) NOT NULL, + parent_id INT REFERENCES thread (id) on delete cascade, + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade +); +create table topic_guidelines +( + id serial, + topic_id int references topic_thread (id) on delete cascade, + description text, + PRIMARY KEY (id, topic_id) +); +CREATE TABLE discussion_thread +( + parent_id INT REFERENCES thread (id) on delete cascade NOT NULL, + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade +); +CREATE TABLE project_thread +( + title VARCHAR(256) UNIQUE NOT NULL, + repo_url TEXT, + id INT PRIMARY KEY REFERENCES thread (id) on delete cascade +); +CREATE TABLE likes +( + user_id INT REFERENCES users (id) on delete cascade , + thread_id INT REFERENCES thread (id) on delete cascade, + 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) ON DELETE CASCADE, + tag_name VARCHAR(64) REFERENCES tag (name) ON DELETE CASCADE, + 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) on delete cascade, + developer_id INT REFERENCES users (id) on delete cascade, + started_at TIMESTAMP DEFAULT NOW(), + 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) on delete cascade, + project_id INT, + role_name VARCHAR(32), + FOREIGN KEY (role_name, project_id) + REFERENCES project_roles (name, project_id) ON DELETE CASCADE, + 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) ON DELETE CASCADE +); +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) ON DELETE CASCADE NOT NULL , + project_id INT REFERENCES thread (id) ON DELETE CASCADE NOT NULL +); +CREATE TABLE report +( + id SERIAL, + created_at TIMESTAMP, + description VARCHAR(200) NOT NULL, + status status, + thread_id INT REFERENCES thread (id) on delete cascade, + for_user_id INT REFERENCES users (id) on delete cascade, + by_user_id INT REFERENCES users (id) on delete cascade, + 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, 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_discussion_thread +as +with recursive + depth_table as + (select parent_id, id, 0 as depth + from discussion_thread + UNION ALL + select discuss.parent_id, dpth.id, dpth.depth + 1 + from depth_table dpth + join discussion_thread discuss + on dpth.parent_id = discuss.id), + tmp as (select id, max(depth) as depth + from depth_table + group by id) +select d.id as id, t.user_id as user_id, d.depth as depth, d1.parent_id as parent_id +from tmp d + join depth_table d1 + on d.id = d1.id and d1.depth = d.depth + join thread t + on t.id = d.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 function check_if_user_exists_in(table_name text, field_name text, field_value text) returns boolean + language plpgsql +as +$$ +DECLARE + result BOOL; +BEGIN + EXECUTE format('SELECT EXISTS (SELECT 1 FROM %I WHERE %I = %L)', table_name, field_name, field_value) + INTO result; + RETURN result; +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; + IF not check_if_user_exists_in('moderator', 'id', v_user_id::text) THEN + INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id); + END IF; + RETURN NEW; +END +$$; + +CREATE OR REPLACE FUNCTION fn_insert_project_manager() + RETURNS TRIGGER + LANGUAGE plpgsql +AS +$$ +DECLARE + usrId INT; + new_project_id INT; +BEGIN + SELECT user_id, id + into usrId,new_project_id + FROM v_project_thread p + WHERE NEW.id = p.id; + IF not check_if_user_exists_in('developer', 'id', usrId::text) THEN + INSERT INTO developer VALUES (usrId); + IF NOT EXISTS ( + select 1 + from developer_associated_with_project dp + where dp.project_id=new_project_id and dp.developer_id=usrId + ) + THEN + INSERT INTO developer_associated_with_project(project_id, developer_id, started_at) + values (new_project_id, usrId, NOW()); + END IF; + end if; + IF not check_if_user_exists_in('project_manager', 'id', usrId::text) THEN + INSERT INTO project_manager VALUES (usrId); + end if; + RETURN NEW; +END +$$; +create or replace function fn_remove_unused_tags() + returns trigger + language plpgsql +as +$$ +BEGIN + IF not check_if_user_exists_in('tag_threads', 'tag_name', old.tag_name) + THEN + raise notice 'kakosi'; + delete from tag t where t.name = old.tag_name; + end if; + return old; +end; +$$; +create or replace function fn_remove_not_active_project_manager() + returns trigger + language plpgsql +as +$$ +DECLARE + creator_id int; +begin + select user_id + into creator_id + from thread t + where t.id = old.id; + + IF NOT EXISTS(select 1 + from thread t + where t.id in (select id from project_thread) + and t.user_id = creator_id) THEN + delete from project_manager where id = creator_id; + end if; + return new; +end +$$; + +create or replace function fn_remove_not_active_developer() + returns trigger + language plpgsql +as +$$ +begin + IF not check_if_user_exists_in('developer_associated_with_project','developer_id',old.developer_id::text) + THEN + delete from developer d + where d.id=old.developer_id; + end if; + 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(); + +create or replace trigger tr_remove_unused_tags + after delete + on tag_threads + for each row +execute function fn_remove_unused_tags(); + +create trigger tr_remove_not_project_managers + after delete + on project_thread + for each row +execute function fn_remove_not_active_project_manager(); + +create trigger tr_remove_not_active_developer + after delete + on developer_associated_with_project + for each row +execute function fn_remove_not_active_developer(); + Index: target/classes/db/migration/V2__add_test_data.sql =================================================================== --- target/classes/db/migration/V2__add_test_data.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/db/migration/V2__add_test_data.sql (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,115 @@ +INSERT INTO users (username, is_activate, password, description, registered_at, sex,name,email) +VALUES + ('user1', true, '$2a$12$0f.x7aBM2wFBZBXoLPj0BObVsk.J1kXFYo5nb4niAWkI4hk5tHvDy', 'First user', NOW(), 'M','viki', 'viki@gmail.com'), + ('user2', true, '$2a$12$VkR0a47LDVM6aUqFcEJGSu9jhZCz.05tCoyiRicFObt4f2x2gijKa', 'Second user', NOW(), 'F','stefan', 'stefan@gmail.com'), + ('user3', true, '$2a$12$eSLdHHJ1KFgv.dOupmloXeItjrt2o1IB6ER6Nq7WYj9Jfr2bEwK2a', 'Third user', NOW(), 'M','darko', 'darko@gmail.com'), + ('user4', true, '$2a$12$dF5SXcNhMulgU3Qre3nh1e.aatRiJZsnfoBSqReGnXe9rIbHYVWhe', 'Fourth user', NOW(), 'F','andrej', 'andrej@gmail.com'), + ('user5', true, '$2a$12$zHrloz8WG2zo5S6MTf1C0ez1raMlmDJdB8OOa2I1S2pVy9oI76YTa', 'Fifth user', NOW(), 'M','ramche', 'ramche@gmail.com'); + + +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), + ('Main content for topic thread', 1), + ('Main content for topic thread', 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), + ('Further discussion on topic 2', 5), + ('Main content for topic thread', 1), + ('Main content for topic thread', 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, parent_id) +VALUES + (1, 'Topic 1' , 5), + (2, 'Topic 2', 5), + (8, 'Topic 7' , NULL), + (9, 'Topic 8', NULL), + (16, 'Topic 9' , NULL), + (17, 'Topic 10', NULL); + +insert into topic_guidelines(topic_id,description) +values + (1,'Follow guidelines'), + ( 2,'Be respectful'); + +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'), + (10, 'Project 2 Thread', 'http://github.com/project1'), + (15, 'Project 3 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 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 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: target/classes/templates/create-topic.html =================================================================== --- target/classes/templates/create-topic.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/create-topic.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,36 @@ + + + + + + Add Topic + + + +
+
+
+
+

Add a New Topic

+
+
+
+
+ + +
+
+ + +
+ + + +
+
+
+
+ + + + Index: target/classes/templates/fragments/user_fields.html =================================================================== --- target/classes/templates/fragments/user_fields.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/fragments/user_fields.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,35 @@ +
+
Personal Details
+ + + + + + + +
Index: target/classes/templates/home_pages/home.html =================================================================== --- target/classes/templates/home_pages/home.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/home_pages/home.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,208 @@ + + + + + + Home Page + + + + + + + + +
+
+
+
+ +
+
+ + + + + +
+ +
+
+
+
+ +
+ + + +
+
+ Filters: +
+ + +
+
+ + +
+
+
+ + +
+
+ +
+
+
+
+ + +
+
+

Threads

+ +
+
+
+
+
+ +
+ +
+ + +
+
+
+ + + + + + Index: target/classes/templates/home_pages/login.html =================================================================== --- target/classes/templates/home_pages/login.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/home_pages/login.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,37 @@ + + + + + + Login + + + + + +
+
+
+

Login

+
+
+ + +
+
+ + +
+ +
+
+ Don't have an account? Register +
+
+
+
+ + + + + Index: target/classes/templates/home_pages/profile.html =================================================================== --- target/classes/templates/home_pages/profile.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/home_pages/profile.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,88 @@ + + + + + + + Profile + + + +
+
+ +
+
+

User Profile

+
+ +
+ + +
+
+ Profile Picture + + + +
+
+ +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
+
+ + + + Index: target/classes/templates/home_pages/project_description.html =================================================================== --- target/classes/templates/home_pages/project_description.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/home_pages/project_description.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,67 @@ + + + + + + + + About BuildBoard + + +
+
+
+ Logo for BuildBoard project +

BuildBoard

+
+
+

Short description

+

BuildBoard is intended to be a social platform focused on + software + developers. The idea is to facilitate open discussions about technologies, approaches to problems, and more, + making it accessible to every software developer. Additionally, the application offers an integrated project + management system where multiple software developers can collaborate and build their own applications. The + goal is to create a single platform where people can talk, learn, and discuss software development while + also having the opportunity to explore real projects and connect with the developers behind them, whether + for consultation or collaboration purposes.

+

Purpose

+
+

+ In today's world, there is an endless number of resources and ways for someone to acquire knowledge. The + problem + does not lie in the abundance of available knowledge but in the guidance, specifically, how to achieve + the + desired outcome. Every solution to a problem has its own path to reach the goal. This project aims to + solve + the + issue of guidance by enabling software developers to guide and assist one another. +

+

+ Another issue being addressed is the problem of centralization. This issue is closely related to the + aforementioned one, as it is much easier to guide someone who wants to learn if all the necessary + resources + are + in one place. On our platform, this is reflected through various informative threads and discussion + threads. +

+
+

Contact

+ +
+
+ + + Index: target/classes/templates/home_pages/register.html =================================================================== --- target/classes/templates/home_pages/register.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/home_pages/register.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,61 @@ + + + + + Register new user + + + + +
+
+
+
+

Create new user

+
+ +
+ + + + + +
+ + + +
+ +
+
+
+ + + Index: target/classes/templates/project_pages/members.html =================================================================== --- target/classes/templates/project_pages/members.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/project_pages/members.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,97 @@ + + + + + Members + + + + +
+ +
+

Projects Tittles

+
+
+
+ + + + + + + + +
+
+ +
+ +
+
+
Developer
+
+
+
+ + + + + Index: target/classes/templates/project_pages/project-create.html =================================================================== --- target/classes/templates/project_pages/project-create.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/project_pages/project-create.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,47 @@ + + + + + + Add project + + + +
+ +
+
+
+

Create new project

+
+
+
+
+ + +
+
+ + +
+
+ + +
+ + +
+
+
+
+ + + + Index: target/classes/templates/project_pages/requests/show-requests.html =================================================================== --- target/classes/templates/project_pages/requests/show-requests.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/project_pages/requests/show-requests.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,15 @@ + + + + + Requests + + +
+
+
+
+ +
+ + Index: target/classes/templates/project_pages/show-project.html =================================================================== --- target/classes/templates/project_pages/show-project.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/project_pages/show-project.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,207 @@ + + + + + + + + + +
+ +
+
+
+

Project Title

+
+ + + + + + + + +
+
+
+
+

Description

+

Description of the project goes here.

+
+
+

Repository

+ + There's no provided url for this project +
+
+
Tags:
+
    +
  • + Tag Name +
    + + +
    +
    • +
+
+
+
+

Topics:

+
+
+
+
+ +
+ +
+ + +
+
+
+ +
+

Channels:

+
+
+
+
+ +
+ +
+ + +
+
+
+ +
+
+
+
+ + +
+
+ + +
+
+
+
+
+
+ + + +
+
+
+ +
+ + + + + + + + + + Index: target/classes/templates/show-topic.html =================================================================== --- target/classes/templates/show-topic.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) +++ target/classes/templates/show-topic.html (revision 11b8b1cf9ac55a7b9f5090981a1706c178bae03e) @@ -0,0 +1,403 @@ + + + + + + + + + + + + + +
+ +Test + +
+ +
+
+

Topic Title

+ + +
+
+

Description of the topic goes here.

+ +
+
Tags:
+
    +
  • + + Tag Name +
    + + +
    +     +
    • +
+
+ +
+
+
+
+ + +
+
+ + +
+ +
+
+ +
+
+
+
+
+
+ + +
+
+ + +
+
+
+ + +
+ +
+
+ +
+ + + +
+
+
+
+ + + +
+
+
+ Profile Picture + Reply Author + + + + + +
+
+ +
+ + +
+ +
+
+ + + +
+
+
+
+ + + +
+
+

Reply content goes here.

+
+
+
+ + + + + + +
+ +
+ + +
+
+
+ + +
+
+ + +
+
+
+ +
+
+
+ + + +
+
+
+ + +
+
+ + +
+ +
+
+
+
+
+ + +
+ + + + + + + + + + + + + + +