Index: docs/versioning/er_models/ER_BUILD_BOARD_v2.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v2.xml (revision f50f127750f1e6151b56f1d8651579460fec6c53)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v2.xml (revision e6be2c6a297d2df55af2e66f06b7bf7a19c0a3b1)
@@ -1,1 +1,1 @@
-DOUBLE3RECIEVESPROJECT_THREADIS_MODERATED_BYTOPIC_THREAD3NStarted_atDOUBLE3SUBMITSUSERTHREADtrue16dBELONGS_TON33DEVELOPERtrue16oPROJECT_OWNERPERMISSIONSDEFINED_BYNDOUBLE3CUSTOM_ROLE3MDOUBLE3VALID_IN3NDOUBLE3HASDOUBLE3CHANNEL3NCONSTRUCTS3NIS_CREATED_BY3NMODERATORREPLIES3trueNametruetrueNametruetrueUsernamePasswordContentASSOCIATED_WITHNMRegister_date3trueNameSENDS_MESSAGE_INNMsg_txtStarted_involvment_atEnded_involment_atTimestampTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_activeDescriptionStatus3NCreated_atSexDescriptionGuidelines3trueIdDOUBLE3REPORTDOUBLE3SUBBMITTED_BYDOUBLE3ABOUT3M3NDOUBLE3FOR_MISCONDUCTtruetrueIdDOUBLE3PROJECT_REQUEST3P3trueId3NStatusDescriptionNext_discussion_idReasonIS_ASSIGNEDMPCONTAINSDISCUSSION_THREAD3PRIS_CHILD_TOTitleTitleP3NMRepo_urlDOUBLE3BLACKLISTED_USERDOUBLE3BLACKLISTED_BYDOUBLE3REFERS_TODOUBLE3BLACKLISTED_FROM3N3trueStart_dateEnd_date3N3NQtruetrueId
+DOUBLE3RECIEVESPROJECT_THREADIS_MODERATED_BYTOPIC_THREAD3NStarted_atDOUBLE3SUBMITSUSERTHREADtrue16dBELONGS_TON33DEVELOPERtrue16oPROJECT_OWNERPERMISSIONSDEFINED_BYNDOUBLE3CUSTOM_ROLE3MDOUBLE3VALID_IN3NDOUBLE3HASDOUBLE3CHANNEL3NCONSTRUCTS3NIS_CREATED_BY3NMODERATORREPLIES3trueNametruetrueNametruetrueUsernamePasswordContentASSOCIATED_WITHNMRegister_date3trueNameSENDS_MESSAGE_INNContentStarted_atEnded_atSent_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_activeDescriptionStatus3NCreated_atSexDescription3trueIdDOUBLE3REPORTDOUBLE3SUBBMITTED_BYDOUBLE3ABOUT3M3NDOUBLE3FOR_MISCONDUCTtruetrueIdDOUBLE3PROJECT_REQUEST3P3trueId3NStatusDescriptionNext_discussion_idReasonIS_ASSIGNEDMPCONTAINSDISCUSSION_THREAD3PRIS_CHILD_TOTitleTitleP3NMRepo_urlDOUBLE3BLACKLISTED_USERDOUBLE3BLACKLISTED_BYDOUBLE3REFERS_TODOUBLE3BLACKLISTED_FROM3N3trueStart_dateEnd_date3N3NQtruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYNSUBMISSIONtruetrueIdtrue16u3333DOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailName
Index: src/main/java/com/db/finki/www/build_board/repository/UserRepository.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/repository/UserRepository.java (revision f50f127750f1e6151b56f1d8651579460fec6c53)
+++ src/main/java/com/db/finki/www/build_board/repository/UserRepository.java (revision e6be2c6a297d2df55af2e66f06b7bf7a19c0a3b1)
@@ -16,4 +16,6 @@
BBUser findById(long id);
+ // select * from user join developer_asso
+
@Query("""
SELECT d.developer
Index: src/main/resources/db/migration/V1__init_ddl.sql
===================================================================
--- src/main/resources/db/migration/V1__init_ddl.sql (revision f50f127750f1e6151b56f1d8651579460fec6c53)
+++ src/main/resources/db/migration/V1__init_ddl.sql (revision e6be2c6a297d2df55af2e66f06b7bf7a19c0a3b1)
@@ -40,6 +40,6 @@
drop function if exists clean_routines CASCADE;
DROP TRIGGER IF EXISTS validate_same_parent ON discussion_thread CASCADE;
-drop table if exists topic_guidelines cascade;
-drop table if exists submission cascade;
+drop table if exists topic_guidelines cascade;
+drop table if exists submission cascade;
drop table if exists feedback;
@@ -48,13 +48,13 @@
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)
+ 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
@@ -75,5 +75,5 @@
content TEXT,
created_at timestamp DEFAULT NOW(),
- user_id INT REFERENCES users (id) NOT NULL --IS_CREATED_BY TOTAL
+ user_id INT REFERENCES users (id) NOT NULL --IS_CREATED_BY TOTAL
);
CREATE TABLE topic_thread
@@ -81,5 +81,5 @@
title VARCHAR(256) NOT NULL,
id INT PRIMARY KEY REFERENCES thread(id) on delete cascade, --INHERITANCE
- parent_id int REFERENCES thread(id) on delete CASCADE --PARENT
+ parent_id int REFERENCES thread(id) on delete CASCADE --PARENT
);
create table topic_guidelines
@@ -111,5 +111,5 @@
thread_id INT REFERENCES topic_thread(id) ON DELETE CASCADE,
user_id INT REFERENCES moderator(id) ON DELETE CASCADE,
- started_at TIMESTAMP DEFAULT NOW(),
+ started_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (thread_id, user_id)
);
@@ -117,5 +117,5 @@
(
name VARCHAR(64) PRIMARY KEY,
- creator_id int REFERENCES moderator(id) on delete CASCADE not null
+ creator_id int REFERENCES moderator(id) on delete CASCADE not null
);
CREATE TABLE tag_threads
@@ -145,5 +145,5 @@
PRIMARY KEY (project_id, developer_id, started_at)
);
----DO TUKA
+---DO TUKA
CREATE TABLE permissions
(
@@ -181,5 +181,5 @@
description VARCHAR(200) NOT NULL,
status varchar(32) default 'PENDING' CHECK(status IN ( 'ACCEPTED', 'DENIED', 'PENDING')),
- created_by int REFERENCES users(id) not null
+ created_by int REFERENCES users(id) not null
);
@@ -208,5 +208,5 @@
name VARCHAR(64),
description VARCHAR(200),
- project_id INT REFERENCES project_thread(id) ON DELETE CASCADE NOT NULL, --HAS
+ project_id INT REFERENCES project_thread(id) ON DELETE CASCADE NOT NULL, --HAS
developer_id INT REFERENCES developer(id) NOT NULL, --CONSTRUCTS
PRIMARY KEY (name, project_id)
@@ -309,5 +309,5 @@
WHERE v_topic_thread.id = new.id;
IF not check_if_user_exists_in('moderator', 'id', v_user_id::text) THEN
- INSERT INTO moderator values (v_user_id);
+ INSERT INTO moderator values (v_user_id);
END IF;
INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id);
@@ -388,14 +388,14 @@
AS $$
BEGIN
-IF not exists(
- select 1
- from project_roles_permissions p
- where p.role_name=OLD.role_name and p.project_id = OLD.project_id
-) THEN
- DELETE FROM project_roles
- where name=OLD.role_name and project_id=OLD.project_id;
-END IF;
-RETURN OLD;
-END;
+IF not exists(
+ select 1
+ from project_roles_permissions p
+ where p.role_name=OLD.role_name and p.project_id = OLD.project_id
+) THEN
+ DELETE FROM project_roles
+ where name=OLD.role_name and project_id=OLD.project_id;
+END IF;
+RETURN OLD;
+END;
$$
;
@@ -407,22 +407,22 @@
BEGIN
IF not exists (
- select 1
- from topic_threads_moderators t
- where t.user_id = OLD.user_id
- )
-THEN
- DELETE FROM moderator where id=OLD.user_id;
+ select 1
+ from topic_threads_moderators t
+ where t.user_id = OLD.user_id
+ )
+THEN
+ DELETE FROM moderator where id=OLD.user_id;
END IF;
- IF not exists (
- select 1
- from topic_threads_moderators t
- where t.thread_id = OLD.thread_id
- )
-THEN
- DELETE FROM topic_thread where id = OLD.thread_id;
+ IF not exists (
+ select 1
+ from topic_threads_moderators t
+ where t.thread_id = OLD.thread_id
+ )
+THEN
+ DELETE FROM topic_thread where id = OLD.thread_id;
delete from thread where id = OLD.thread_id;
- END IF;
+ END IF;
RETURN OLD;
-END;
+END;
$function$
;
@@ -447,5 +447,5 @@
FOR EACH ROW
EXECUTE FUNCTION fn_validate_topic_title();
-CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator --RADI
+CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator --RADI
AFTER INSERT
ON topic_thread
@@ -453,14 +453,14 @@
EXECUTE FUNCTION fn_insert_topics_creator_as_moderator();
----
-create or replace TRIGGER tr_defined_by_total_custom_roles
-after delete
+create or replace TRIGGER tr_defined_by_total_custom_roles
+after delete
on project_roles_permissions
-for each row
-execute function fn_defined_by_total_custom_roles();
-
-CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator --RADI
-AFTER DELETE
+for each row
+execute function fn_defined_by_total_custom_roles();
+
+CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator --RADI
+AFTER DELETE
ON topic_threads_moderators
-FOR EACH ROW
+FOR EACH ROW
EXECUTE FUNCTION fn_remove_orphan_moderator();
@@ -472,5 +472,5 @@
EXECUTE FUNCTION fn_insert_project_manager();
-create or replace trigger tr_remove_unused_tags --RADI
+create or replace trigger tr_remove_unused_tags --RADI
after delete
on tag_threads
@@ -478,10 +478,10 @@
execute function fn_remove_unused_tags();
-create or replace trigger tr_add_dev_if_not_exist --RADI
+create or replace trigger tr_add_dev_if_not_exist --RADI
before insert on developer_associated_with_project
for each row
execute function fn_add_dev_if_not_exist();
-create or replace trigger tr_insert_general_for_project --RADI
+create or replace trigger tr_insert_general_for_project --RADI
after insert on project_thread
for each row