Index: docs/versioning/er_models/ER_BUILD_BOARD_v5.3.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v5.3.xml (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v5.3.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -1,1 +1,1 @@
-NametruetrueIdRECIEVESPROJECT_THREADIS_MODERATED_BYTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_OWNERPERMISSIONSCONSTRUCTSIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentRegister_atContentEnded_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleN3NRepo_urlEnd_datetruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3N3M33DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLE3DOUBLE3FOR_PERMISSION3Noverride_typePROJECT_ROLEVALID_IN3NtruetrueidnameNMCHANNEL3NHAS3MFOR_RESOURCE3NtruetrueIdStart_dateBLACKLISTED_USERBLACKLISTED_BY3NRFERS_TO3MBLACKLISTED_FROM3MDOUBLE3MESSAGE3trueSent_atDOUBLE3SENT_INDOUBLE3SENT_BY3M3MDOUBLE3DEVELOPER_ASSOCIATED_WITH_PROJECT3trueStarted_atDOUBLE3IN_PROJECTDOUBLE3ABOUT_DEV3N3N3
+FOR_RESOURCEDOUBLE3ROLE_PERMISSION_OVERRIDES3NNametruetrueIdRECIEVESPROJECT_THREADIS_MODERATED_BYTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_MANAGERPERMISSIONSCONSTRUCTSIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentRegister_atContentEnded_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleN3NRepo_urlEnd_datetruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3N3M33DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLE3DOUBLE3FOR_PERMISSION3Noverride_typePROJECT_ROLEVALID_IN3NtruetrueidnameNMCHANNEL3NHAS3MtruetrueIdStart_dateBLACKLISTED_USERBLACKLISTED_BY3NRFERS_TO3MBLACKLISTED_FROM3MDOUBLE3MESSAGE3trueSent_atDOUBLE3SENT_INDOUBLE3SENT_BY3M3MDOUBLE3DEVELOPER_ASSOCIATED_WITH_PROJECT3trueStarted_atDOUBLE3IN_PROJECTDOUBLE3ABOUT_DEV3N3N3DOUBLE3FOR_ROLE_PERMISSION3N3
Index: docs/versioning/er_models/ER_BUILD_BOARD_v5.4.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v5.4.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v5.4.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -0,0 +1,1 @@
+NametruetrueIdRECIEVESPROJECT_THREADIS_MODERATED_BYTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_MANAGERPERMISSIONSCONSTRUCTSIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentRegister_atContentEnded_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleN3NRepo_urlEnd_datetruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3N3M33DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLE3DOUBLE3FOR_PERMISSION3Noverride_typePROJECT_ROLEVALID_IN3NtruetrueidnameNMCHANNEL3NHAS3MtruetrueIdStart_dateBLACKLISTED_USERBLACKLISTED_BY3NRFERS_TO3MBLACKLISTED_FROM3MDOUBLE3MESSAGE3trueSent_atDOUBLE3SENT_INDOUBLE3SENT_BY3M3MDOUBLE3DEVELOPER_ASSOCIATED_WITH_PROJECT3trueStarted_atDOUBLE3IN_PROJECTDOUBLE3ABOUT_DEV3N3N3DOUBLE3ROLE_PERMISSION_OVERRIDESDOUBLE3FOR_ROLE_PERMISSION3N3DOUBLE3FOR_RESOURCE3N
Index: docs/versioning/er_models/ER_BUILD_BOARD_v5.5.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v5.5.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v5.5.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -0,0 +1,1 @@
+NametruetrueIdRECIEVESPROJECT_THREADIS_MODERATED_BYTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_MANAGERPERMISSIONSCONSTRUCTSIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentRegister_atContentEnded_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleN3NRepo_urlEnd_datetruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3N3M33DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLEDOUBLE3FOR_PERMISSIONoverride_typePROJECT_ROLEVALID_IN3NtruetrueidnameMCHANNEL3NHAS3MtruetrueIdStart_dateBLACKLISTED_USERBLACKLISTED_BY3NRFERS_TO3MBLACKLISTED_FROM3MDOUBLE3MESSAGE3trueSent_atDOUBLE3SENT_INDOUBLE3SENT_BY3M3MDOUBLE3DEVELOPER_ASSOCIATED_WITH_PROJECT3trueStarted_atDOUBLE3IN_PROJECTDOUBLE3ABOUT_DEV3N3N3DOUBLE3ROLE_PERMISSION_OVERRIDESDOUBLE3FOR_ROLE_PERMISSION3N3DOUBLE3FOR_RESOURCE3N3M3N
Index: docs/versioning/er_models/ER_BUILD_BOARD_v5.6.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v5.6.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v5.6.xml (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -0,0 +1,1 @@
+NametruetrueIdRECIEVESPROJECT_THREADIS_MODERATED_BYTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_MANAGERPERMISSIONSCONSTRUCTSIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentRegister_atContentEnded_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleN3NRepo_urlEnd_datetruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3N3M33DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLEDOUBLE3FOR_PERMISSIONoverride_typePROJECT_ROLEVALID_IN3NtruetrueidnameMCHANNEL3NHAS3MtruetrueIdStart_dateBLACKLISTED_USERBLACKLISTED_BY3NRFERS_TO3MBLACKLISTED_FROM3MDOUBLE3MESSAGE3trueSent_atDOUBLE3SENT_INDOUBLE3SENT_BY3M3MDOUBLE3DEVELOPER_ASSOCIATED_WITH_PROJECT3trueStarted_atDOUBLE3IN_PROJECTDOUBLE3ABOUT_DEV3N3N3DOUBLE3ROLE_PERMISSION_OVERRIDESDOUBLE3FOR_ROLE_PERMISSION3N3DOUBLE3FOR_RESOURCE3N3M3N
Index: src/main/java/com/db/finki/www/build_board/entity/access_managment/UsersProjectRoles.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/access_managment/UsersProjectRoles.java (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/java/com/db/finki/www/build_board/entity/access_managment/UsersProjectRoles.java (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -12,5 +12,5 @@
@Entity
-@Table(name = "users_project_roles")
+@Table(name = "project_role_is_assigned_to_developer")
@AllArgsConstructor
@NoArgsConstructor
Index: src/main/java/com/db/finki/www/build_board/entity/thread/BBThread.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/thread/BBThread.java (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/java/com/db/finki/www/build_board/entity/thread/BBThread.java (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -29,5 +29,5 @@
@ManyToMany
@JoinTable(
- name = "tag_threads",
+ name = "tag_assigned_to_thread",
joinColumns = @JoinColumn(name = "thread_id"),
inverseJoinColumns = @JoinColumn(name = "tag_name")
Index: c/main/resources/db/migration/U1__remove_ddl.sql
===================================================================
--- src/main/resources/db/migration/U1__remove_ddl.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ (revision )
@@ -1,39 +1,0 @@
-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_role CASCADE;
-DROP TABLE IF EXISTS users_project_roles CASCADE;
-DROP TABLE IF EXISTS 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 TABLE IF EXISTS project_resource CASCADE ;
-DROP TABLE IF EXISTS role_permissions_overrides 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 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/resources/db/migration/V1__init_ddl.sql (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -5,7 +5,7 @@
DROP TABLE IF EXISTS thread CASCADE;
DROP TABLE IF EXISTS likes CASCADE;
-DROP TABLE IF EXISTS topic_threads_moderators CASCADE;
+DROP TABLE IF EXISTS topic_thread_is_moderated_by_moderator CASCADE;
DROP TABLE IF EXISTS tag CASCADE;
-DROP TABLE IF EXISTS tag_threads CASCADE;
+DROP TABLE IF EXISTS tag_assigned_to_thread CASCADE;
DROP TABLE IF EXISTS topic_thread CASCADE;
DROP TABLE IF EXISTS topic_belongs_to_project CASCADE;
@@ -15,7 +15,8 @@
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_role CASCADE;
+DROP TABLE IF EXISTS project_role_is_assigned_to_developer CASCADE;
+DROP TABLE IF EXISTS role_permissions CASCADE;
+DROP TABLE IF EXISTS role_permissions_overrides CASCADE;
DROP TABLE IF EXISTS project_request CASCADE;
DROP TABLE IF EXISTS report CASCADE;
@@ -73,5 +74,5 @@
content TEXT,
created_at timestamp DEFAULT NOW() NOT NULL,
- user_id INT REFERENCES users (id) NOT NULL --IS_CREATED_BY TOTAL
+ is_created_by INT REFERENCES users (id) NOT NULL --IS_CREATED_BY TOTAL
);
CREATE TABLE project_thread
@@ -90,6 +91,6 @@
title VARCHAR(256) NOT NULL,
id INT PRIMARY KEY REFERENCES embeddable_thread (id) on delete cascade, --INHERITANCE
- parent_id int REFERENCES project_thread (id) on delete CASCADE, --PARENT
- UNIQUE (parent_id, title)
+ referenced_by int REFERENCES project_thread (id) on delete CASCADE, --PARENT
+ UNIQUE (referenced_by, title)
);
create table topic_guidelines
@@ -103,5 +104,5 @@
(
id INT PRIMARY KEY REFERENCES embeddable_thread (id) on delete cascade, --INHERITANCE,
- parent_id int REFERENCES embeddable_thread (id) NOT NULL --on delete CASCADE ne tuku preku trigger PARENT TOTAL BIGINT
+ contained_in int REFERENCES embeddable_thread (id) NOT NULL --on delete CASCADE ne tuku preku trigger PARENT TOTAL BIGINT
);
@@ -112,5 +113,5 @@
PRIMARY KEY (user_id, thread_id)
);
-CREATE TABLE topic_threads_moderators
+CREATE TABLE topic_thread_is_moderated_by_moderator
(
thread_id INT REFERENCES topic_thread (id) ON DELETE CASCADE NOT NULL,
@@ -124,5 +125,5 @@
creator_id int REFERENCES users (id) on delete CASCADE not null
);
-CREATE TABLE tag_threads
+CREATE TABLE tag_assigned_to_thread
(
thread_id INT REFERENCES thread (id) ON DELETE CASCADE,
@@ -134,19 +135,19 @@
(
id serial primary key,
- 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,
+ blacklisted_from INT REFERENCES topic_thread (id) ON DELETE CASCADE,
+ refers_to INT REFERENCES users (id) ON DELETE CASCADE,
+ blacklisted_by INT REFERENCES moderator (id) ON DELETE CASCADE,
start_date TIMESTAMP,
end_date TIMESTAMP,
reason TEXT,
- UNIQUE (user_id, moderator_id, topic_id, start_date)
+ UNIQUE (refers_to, blacklisted_by, blacklisted_from, start_date)
);
CREATE TABLE developer_associated_with_project
(
- project_id INT REFERENCES project_thread (id) on delete cascade,
- developer_id INT REFERENCES developer (id) on delete cascade,
+ in_project INT REFERENCES project_thread (id) on delete cascade,
+ about_dev INT REFERENCES developer (id) on delete cascade,
started_at TIMESTAMP DEFAULT NOW() NOT NULL,
ended_at TIMESTAMP,
- PRIMARY KEY (project_id, developer_id, started_at)
+ PRIMARY KEY (in_project, about_dev, started_at)
);
CREATE TABLE channel
@@ -155,7 +156,7 @@
name VARCHAR(64) NOT NULL,
description VARCHAR(200),
- project_id INT REFERENCES project_thread (id) ON DELETE CASCADE NOT NULL, --HAS
- developer_id INT REFERENCES developer (id) NOT NULL, --CONSTRUCTS
- UNIQUE (name,project_id)
+ project_has INT REFERENCES project_thread (id) ON DELETE CASCADE NOT NULL, --HAS
+ constructed_by INT REFERENCES developer (id) NOT NULL, --CONSTRUCTS
+ UNIQUE (name,project_has)
);
CREATE TABLE permissions
@@ -169,5 +170,5 @@
id serial PRIMARY KEY ,
name varchar(32) NOT NULL,
- project_id int references project_thread (id) ON DELETE CASCADE NOT NULL,
+ valid_in int references project_thread (id) ON DELETE CASCADE NOT NULL,
override_type varchar(20) check ( override_type in ('INCLUDE','EXCLUDE')) NOT NULL DEFAULT 'EXCLUDE'
);
@@ -175,20 +176,20 @@
CREATE TABLE role_permissions
(
- permission_name VARCHAR(32) NOT NULl,
- role_id INT REFERENCES project_role(id) ON DELETE CASCADE NOT NULL,
- FOREIGN KEY (permission_name) REFERENCES permissions(name),
- PRIMARY KEY (permission_name, role_id)
+ for_permission VARCHAR(32) NOT NULl,
+ for_role INT REFERENCES project_role(id) ON DELETE CASCADE NOT NULL,
+ FOREIGN KEY (for_permission) REFERENCES permissions(name),
+ PRIMARY KEY (for_permission, for_role)
);
CREATE TABLE role_permissions_overrides
(
- channel_id uuid references channel(id) on delete cascade ,
- permission_name VARCHAR(32) NOT NULL,
- role_id INT REFERENCES project_role(id) ON DELETE CASCADE NOT NULL,
- FOREIGN KEY (role_id,permission_name) REFERENCES role_permissions (role_id,permission_name) ON DELETE CASCADE,
- PRIMARY KEY (role_id,permission_name,channel_id)
-);
-
-CREATE TABLE users_project_roles
+ for_resource uuid references channel(id) on delete cascade ,
+ for_role_permission_permission_name VARCHAR(32) NOT NULL,
+ for_role_permission_role_id INT REFERENCES project_role(id) ON DELETE CASCADE NOT NULL,
+ FOREIGN KEY (for_role_permission_permission_name, for_role_permission_role_id) REFERENCES role_permissions(for_permission, for_role) ON DELETE CASCADE,
+ PRIMARY KEY (for_role_permission_role_id,for_role_permission_permission_name,for_resource)
+);
+
+CREATE TABLE project_role_is_assigned_to_developer
(
user_id INT REFERENCES developer (id) on delete cascade NOT NULL,
@@ -204,5 +205,5 @@
description VARCHAR(200) NOT NULL,
status varchar(32) default 'PENDING' CHECK (status IN ('ACCEPTED', 'DENIED', 'PENDING')) NOT NULL,
- created_by int REFERENCES users (id) NOT NULL
+ submitted_by int REFERENCES users (id) NOT NULL
);
@@ -210,5 +211,5 @@
(
id int PRIMARY KEY REFERENCES submission (id),
- project_id INT REFERENCES thread (id) ON DELETE CASCADE NOT NULL --RECIEVES
+ project_receives INT REFERENCES project_thread (id) ON DELETE CASCADE NOT NULL --RECIEVES
);
@@ -218,6 +219,6 @@
submission_type varchar(1) CHECK (submission_type IN ('P', 'R')) NOT NULL,
created_at timestamp default now() not null,
- created_by int references users (id) NOT NULL, --WRITTEN_BY
- submission_id int PRIMARY KEY references submission (id) on delete cascade
+ written_by int references users (id) NOT NULL, --WRITTEN_BY
+ submitted_for int PRIMARY KEY references submission (id) on delete cascade
);
@@ -225,6 +226,6 @@
(
id int PRIMARY KEY REFERENCES submission (id),
- thread_id INT REFERENCES topic_thread (id) on delete cascade NOT NULL, --FOR_MISCONDUCT
- for_user_id INT REFERENCES users (id) on delete cascade NOT NULL --ABOUT
+ for_misconduct_in INT REFERENCES topic_thread (id) on delete cascade NOT NULL, --FOR_MISCONDUCT
+ about INT REFERENCES users (id) on delete cascade NOT NULL --ABOUT
);
@@ -234,7 +235,5 @@
content VARCHAR(200) NOT NULL,
sent_by INT REFERENCES developer (id) on delete no action NOT NULL,
- channel_id uuid references channel(id) ON DELETE CASCADE NOT NULL,
- PRIMARY KEY (channel_id,sent_by,sent_at)
-);
-
-
+ sent_in uuid references channel(id) ON DELETE CASCADE NOT NULL,
+ PRIMARY KEY (sent_in,sent_by,sent_at)
+);
Index: src/main/resources/db/migration/V2__views.sql
===================================================================
--- src/main/resources/db/migration/V2__views.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/resources/db/migration/V2__views.sql (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -1,5 +1,5 @@
CREATE OR REPLACE VIEW v_project_thread
AS
-SELECT thread.id, content, user_id, title, repo_url
+SELECT thread.id, content, is_created_by, title, repo_url
FROM project_thread project
JOIN thread
@@ -7,5 +7,5 @@
CREATE OR REPLACE VIEW v_topic_thread
AS
-SELECT thread.id, content, user_id, title, parent_id
+SELECT thread.id, content, is_created_by, title, topic.referenced_by
FROM topic_thread topic
JOIN thread
@@ -21,15 +21,15 @@
with recursive
depth_table as
- (select parent_id, id, 0 as depth
+ (select contained_in, id, 0 as depth
from discussion_thread
UNION ALL
- select discuss.parent_id, dpth.id, dpth.depth + 1
+ select discuss.contained_in, dpth.id, dpth.depth + 1
from depth_table dpth
join discussion_thread discuss
- on dpth.parent_id = discuss.id),
+ on dpth.contained_in = 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, t.created_at as "created_at"
+select d.id as id, t.is_created_by as user_id, d.depth as depth, d1.contained_in as parent_id, t.created_at as "created_at"
from tmp d
join depth_table d1
@@ -46,10 +46,10 @@
COALESCE(
STRING_AGG(
- DISTINCT rp.permission_name, ',' ORDER BY rp.permission_name
+ DISTINCT rp.for_permission, ',' ORDER BY rp.for_permission
) FILTER (
WHERE
- (pr.override_type = 'INCLUDE' AND rpo.channel_id IS NOT NULL)
+ (pr.override_type = 'INCLUDE' AND rpo.for_resource IS NOT NULL)
OR
- (pr.override_type = 'EXCLUDE' AND rpo.channel_id IS NULL)
+ (pr.override_type = 'EXCLUDE' AND rpo.for_resource IS NULL)
),
''
@@ -57,11 +57,11 @@
FROM channel c
JOIN project_role pr
- ON pr.project_id = c.project_id
+ ON pr.valid_in = c.project_has
LEFT JOIN role_permissions rp
- ON rp.role_id = pr.id
- AND rp.permission_name IN ('READ','WRITE')
+ ON rp.for_role = pr.id
+ AND rp.for_permission IN ('READ','WRITE')
LEFT JOIN role_permissions_overrides rpo
- ON rpo.role_id = pr.id
- AND rpo.permission_name = rp.permission_name
- AND rpo.channel_id = c.id
+ ON rpo.for_role_permission_role_id = pr.id
+ AND rpo.for_role_permission_permission_name = rp.for_permission
+ AND rpo.for_resource = c.id
GROUP BY c.id, c.name,pr.id
Index: src/main/resources/db/migration/V3__triggers_ddl.sql
===================================================================
--- src/main/resources/db/migration/V3__triggers_ddl.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/resources/db/migration/V3__triggers_ddl.sql (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -7,18 +7,18 @@
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)
+ FROM topic_thread AS t
+ WHERE t.referenced_by = new.referenced_by
+ OR (t.referenced_by IS NULL AND new.referenced_by IS NULL)
)
THEN
- RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id;
+ RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %', new.title, new.referenced_by;
END IF;
RETURN new;
END;
$$;
-create or replace function check_if_user_exists_in(table_name text, field_name text, field_value text) returns boolean
- language plpgsql
-as
+
+CREATE OR REPLACE FUNCTION check_if_user_exists_in(table_name text, field_name text, field_value text) RETURNS boolean
+ LANGUAGE plpgsql
+AS
$$
DECLARE
@@ -28,6 +28,7 @@
INTO result;
RETURN result;
-END
-$$;
+END;
+$$;
+
CREATE OR REPLACE FUNCTION fn_insert_topics_creator_as_moderator()
RETURNS TRIGGER
@@ -38,14 +39,16 @@
v_user_id INT;
BEGIN
- SELECT v_topic_thread.user_id
+ SELECT v_topic_thread.is_created_by
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 moderator values (v_user_id);
- END IF;
- INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id);
- RETURN NEW;
-END
+ 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);
+ END IF;
+
+ INSERT INTO topic_thread_is_moderated_by_moderator(thread_id, user_id) VALUES (NEW.id, v_user_id);
+ RETURN NEW;
+END;
$$;
@@ -56,163 +59,176 @@
$$
DECLARE
- usrId INT;
+ usrId INT;
new_project_id INT;
BEGIN
- SELECT user_id, id
- into usrId,new_project_id
+ SELECT is_created_by, id
+ INTO usrId, new_project_id
FROM v_project_thread p
WHERE NEW.id = p.id;
- IF not EXISTS(
- select 1
- from developer_associated_with_project dawp
- where dawp.project_id=new_project_id and dawp.developer_id=usrId
- ) THEN
- INSERT INTO developer_associated_with_project(project_id, developer_id, started_at)
- values (new_project_id, usrId, NOW());
- end if;
- IF not check_if_user_exists_in('project_manager', 'id', usrId::text) THEN
+
+ IF NOT EXISTS(
+ SELECT 1
+ FROM developer_associated_with_project dawp
+ WHERE dawp.in_project = new_project_id
+ AND dawp.about_dev = usrId
+ ) THEN
+ INSERT INTO developer_associated_with_project(in_project, about_dev, started_at)
+ VALUES (new_project_id, usrId, NOW());
+ 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
- delete from tag t where t.name = old.tag_name;
- end if;
- return old;
-end;
-$$;
-create or replace function fn_add_dev_if_not_exist()
- returns trigger
- language plpgsql
-as $$
-BEGIN
- IF NOT check_if_user_exists_in('developer','id',new.developer_id::text) THEN
- INSERT INTO developer values (NEW.developer_id);
- end if;
- RETURN new;
-end;
-$$;
-
-create or replace function fn_insert_general_for_project()
- returns trigger
- language plpgsql
-as $$
+ 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_assigned_to_thread', 'tag_name', OLD.tag_name) THEN
+ DELETE FROM tag WHERE name = OLD.tag_name;
+ END IF;
+ RETURN OLD;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION fn_add_dev_if_not_exist()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS
+$$
+BEGIN
+ IF NOT check_if_user_exists_in('developer', 'id', NEW.about_dev::text) THEN
+ INSERT INTO developer VALUES (NEW.about_dev);
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION fn_insert_general_for_project()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS
+$$
DECLARE
developer_id INT;
BEGIN
- select user_id
- into developer_id
- from thread t
- where t.id=NEW.id;
- insert into channel(name,description,project_id,developer_id)
- values ('General','General',NEW.id,developer_id);
-
- return new;
-end;
-$$;
-
+ SELECT is_created_by
+ INTO developer_id
+ FROM thread t
+ WHERE t.id = NEW.id;
+
+ INSERT INTO channel(name, description, project_has, constructed_by)
+ VALUES ('General', 'General', NEW.id, developer_id);
+
+ RETURN NEW;
+END;
+$$;
CREATE OR REPLACE FUNCTION fn_remove_orphan_moderator()
- RETURNS trigger
- LANGUAGE plpgsql
-AS $function$
-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;
- END IF;
- IF not exists (
- select 1
- from topic_threads_moderators t
- where t.thread_id = OLD.thread_id
- )
- THEN
- delete from discussion_thread where parent_id=OLD.thread_id;
- DELETE FROM topic_thread where id = OLD.thread_id;
--- delete from thread where id = OLD.thread_id;
- END IF;
- RETURN OLD;
-END;
-$function$
-;
-create or replace function fn_aa_rm_orphan_dics()
- returns trigger
- language plpgsql
-as
-$$
-BEGIN
- -- RAISE NOTICE '%',OLD.id;
-
- delete from discussion_thread dt
- where dt.parent_id=OLD.id;
- delete from embeddable_thread
- where id = OLD.id;
- delete from thread t
- where t.id=OLD.id;
- RETURN OLD;
-END;
-$$;
-
-
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS
+$$
+BEGIN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM topic_thread_is_moderated_by_moderator 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_thread_is_moderated_by_moderator t
+ WHERE t.thread_id = OLD.thread_id
+ ) THEN
+ DELETE FROM discussion_thread WHERE contained_in = OLD.thread_id;
+ DELETE FROM topic_thread WHERE id = OLD.thread_id;
+ END IF;
+
+ RETURN OLD;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION fn_aa_rm_orphan_dics()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS
+$$
+BEGIN
+ DELETE FROM discussion_thread dt
+ WHERE dt.contained_in = OLD.id;
+
+ DELETE FROM embeddable_thread
+ WHERE id = OLD.id;
+
+ DELETE FROM thread t
+ WHERE t.id = OLD.id;
+
+ RETURN OLD;
+END;
+$$;
CREATE OR REPLACE FUNCTION fn_change_status_on_pending_reports()
RETURNS TRIGGER
LANGUAGE plpgsql
-AS $$
-BEGIN
- RAISE NOTICE 'user_id: %, topic_id: %', NEW.user_id, NEW.topic_id;
-
-UPDATE submission
-SET status = 'ACCEPTED'
-WHERE id in (
- select id
- from report r
- where r.for_user_id = NEW.user_id and r.thread_id = NEW.topic_id
-);
-
-RETURN NEW;
-END;
-$$;
-
-create or replace function fn_add_blacklisted_user()
-RETURNS trigger
-LANGUAGE plpgsql
-AS $$
-BEGIN
- IF NOT EXISTS(
- select 1
- from blacklisted_user
- where topic_id = NEW.topic_id and user_id = NEW.user_id and end_date is NULL
- )
- THEN
- RETURN NEW;
-END IF;
-RETURN NULL;
-END;
-$$;
-
-create or replace function fn_delete_dangling_tags()
- RETURNS trigger
- LANGUAGE plpgsql
-AS $$
-BEGIN
- IF NOT EXISTS(select 1
- from tag_threads
- where tag_name = OLD.tag_name
- group by tag_name) THEN
- delete from tag where name = OLD.tag_name;
- end if;
+AS
+$$
+BEGIN
+ RAISE NOTICE 'user_id: %, topic_id: %', NEW.refers_to, NEW.blacklisted_from;
+
+ UPDATE submission
+ SET status = 'ACCEPTED'
+ WHERE id IN (
+ SELECT id
+ FROM report r
+ WHERE r.about = NEW.refers_to
+ AND r.for_misconduct_in = NEW.blacklisted_from
+ );
+
+ RETURN NEW;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION fn_add_blacklisted_user()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS
+$$
+BEGIN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM blacklisted_user
+ WHERE blacklisted_from = NEW.blacklisted_from
+ AND refers_to = NEW.refers_to
+ AND end_date IS NULL
+ ) THEN
+ RETURN NEW;
+ END IF;
+ RETURN NULL;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION fn_delete_dangling_tags()
+ RETURNS TRIGGER
+ LANGUAGE plpgsql
+AS
+$$
+BEGIN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM tag_assigned_to_thread
+ WHERE tag_name = OLD.tag_name
+ GROUP BY tag_name
+ ) THEN
+ DELETE FROM tag WHERE name = OLD.tag_name;
+ END IF;
RETURN OLD;
END;
@@ -221,10 +237,11 @@
------------------------------------------------------------
-CREATE OR REPLACE TRIGGER tr_check_topic_name --RADI
+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 --RADI
+
+CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator
AFTER INSERT
ON topic_thread
@@ -232,11 +249,11 @@
EXECUTE FUNCTION fn_insert_topics_creator_as_moderator();
-CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator --RADI
+CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator
AFTER DELETE
- ON topic_threads_moderators
+ ON topic_thread_is_moderated_by_moderator
FOR EACH ROW
EXECUTE FUNCTION fn_remove_orphan_moderator();
-CREATE OR REPLACE TRIGGER tr_a_insert_project_manager --RADI
+CREATE OR REPLACE TRIGGER tr_a_insert_project_manager
AFTER INSERT
ON project_thread
@@ -244,24 +261,27 @@
EXECUTE FUNCTION fn_insert_project_manager();
-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
- after insert on project_thread
- for each row
-execute function fn_insert_general_for_project();
-
-create or replace trigger tr_rm_orphan_disc
- after delete
- on discussion_thread
- for each row
-execute function fn_aa_rm_orphan_dics();
-
-create or replace trigger tr_add_blacklisted_user
-before insert on blacklisted_user
-for each row
-execute function fn_add_blacklisted_user();
+CREATE OR REPLACE TRIGGER tr_add_dev_if_not_exist
+ 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
+ AFTER INSERT
+ ON project_thread
+ FOR EACH ROW
+EXECUTE FUNCTION fn_insert_general_for_project();
+
+CREATE OR REPLACE TRIGGER tr_rm_orphan_disc
+ AFTER DELETE
+ ON discussion_thread
+ FOR EACH ROW
+EXECUTE FUNCTION fn_aa_rm_orphan_dics();
+
+CREATE OR REPLACE TRIGGER tr_add_blacklisted_user
+ BEFORE INSERT
+ ON blacklisted_user
+ FOR EACH ROW
+EXECUTE FUNCTION fn_add_blacklisted_user();
CREATE OR REPLACE TRIGGER tr_change_status_on_pending_reports
@@ -271,7 +291,7 @@
EXECUTE FUNCTION fn_change_status_on_pending_reports();
-create or replace trigger tr_delete_dangling_tags
- after delete
- on tag_threads
- for each row
- execute function fn_delete_dangling_tags();
+CREATE OR REPLACE TRIGGER tr_delete_dangling_tags
+ AFTER DELETE
+ ON tag_assigned_to_thread
+ FOR EACH ROW
+EXECUTE FUNCTION fn_delete_dangling_tags();
Index: src/main/resources/db/migration/V4__procedures.sql
===================================================================
--- src/main/resources/db/migration/V4__procedures.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/resources/db/migration/V4__procedures.sql (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -1,80 +1,75 @@
CREATE OR REPLACE PROCEDURE mark_denied_reports_older_than_1month()
-LANGUAGE plpgsql
+ LANGUAGE plpgsql
AS $$
DECLARE
older_reports RECORD;
- user_id int;
+ user_id INT;
BEGIN
FOR older_reports IN
SELECT
r.id AS submission_id,
- r.thread_id as topic
+ r.for_misconduct_in AS topic
FROM report r
JOIN submission s ON s.id = r.id
- WHERE s.status = 'PENDING' and now() - s.created_at >= INTERVAL '1 month'
- LOOP
- BEGIN
- UPDATE submission
- SET status = 'DENIED'
- WHERE id = older_reports.submission_id;
+ WHERE s.status = 'PENDING' AND now() - s.created_at >= INTERVAL '1 month'
+ LOOP
+ BEGIN
+ UPDATE submission
+ SET status = 'DENIED'
+ WHERE id = older_reports.submission_id;
- select u.id
- into user_id
- from thread t
- join users u
- on t.user_id = u.id
- where t.id = older_reports.thread_id;
+ SELECT t.is_created_by
+ INTO user_id
+ FROM thread t
+ WHERE t.id = older_reports.topic;
-
- INSERT INTO feedback(description, submission_type, created_at, submission_id, created_by)
- VALUES (
- 'Stale report. Closing due to inactivity.',
- 'R',
- now(),
- older_reports.submission_id,
- user_id
- );
- END;
- END LOOP;
+ INSERT INTO feedback(description, submission_type, created_at, submitted_for, written_by)
+ VALUES (
+ 'Stale report. Closing due to inactivity.',
+ 'R',
+ NOW(),
+ older_reports.submission_id,
+ user_id
+ );
+ END;
+ END LOOP;
END;
$$;
CREATE OR REPLACE PROCEDURE mark_denied_pr_requests_older_than_1month()
-LANGUAGE plpgsql
+ LANGUAGE plpgsql
AS $$
DECLARE
-older_reports RECORD;
-user_id int;
+ older_reports RECORD;
+ user_id INT;
BEGIN
FOR older_reports IN
SELECT
pr.id AS submission_id,
- pr.project_id as thread_id
+ pr.project_receives AS project_id
FROM project_request pr
JOIN submission s ON s.id = pr.id
- WHERE s.status = 'PENDING' and now() - s.created_at >= INTERVAL '1 month'
- LOOP
- BEGIN
- UPDATE submission
- SET status = 'DENIED'
- WHERE id = older_reports.submission_id;
+ WHERE s.status = 'PENDING' AND now() - s.created_at >= INTERVAL '1 month'
+ LOOP
+ BEGIN
+ UPDATE submission
+ SET status = 'DENIED'
+ WHERE id = older_reports.submission_id;
- select u.id
- into user_id
- from thread t
- join users u
- on t.user_id = u.id
- where older_reports.thread_id = t.id;
+ SELECT t.is_created_by
+ INTO user_id
+ FROM thread t
+ WHERE t.id = older_reports.project_id;
- INSERT INTO feedback(description, submission_type, created_at, submission_id, created_by)
- VALUES (
- 'Stale report. Closing due to inactivity.',
- 'P',
- now(),
- older_reports.submission_id,
- user_id
- );
- END;
- END LOOP;
+ INSERT INTO feedback(description, submission_type, created_at, submitted_for, written_by)
+ VALUES (
+ 'Stale report. Closing due to inactivity.',
+ 'P',
+ NOW(),
+ older_reports.submission_id,
+ user_id
+ );
+ END;
+ END LOOP;
END;
$$;
Index: src/main/resources/db/migration/V5__indexes.sql
===================================================================
--- src/main/resources/db/migration/V5__indexes.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/resources/db/migration/V5__indexes.sql (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -1,12 +1,1 @@
--- CREATE INDEX idx_messages_channel_sentat -- za fetchot vo channel
--- ON messages(channel_id);
---
--- drop index idx_messages_channel_sentat;
---
--- CREATE UNIQUE INDEX idx_channel_name_project ON channel(project_id,name)
--- --
--- CREATE INDEX idx_discussion_parent ON discussion_thread(parent_id); -- za rekurzivnoto
--- CREATE INDEX idx_project_title ON project_thread(title)
--- CREATE INDEX idx_project_role_name_project_id ON project_role(name,project_id);
---
---
+
Index: src/main/resources/db/migration/V6__add_test_data.sql
===================================================================
--- src/main/resources/db/migration/V6__add_test_data.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ src/main/resources/db/migration/V6__add_test_data.sql (revision 0a97dcffc15df5c25429f2b43f4fe9720b6b0322)
@@ -1,3 +1,3 @@
-INSERT INTO users (username, is_activate, password, description, registered_at, sex,name,email)
+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'),
@@ -8,5 +8,5 @@
-INSERT INTO thread (content, user_id)
+INSERT INTO thread (content, is_created_by)
VALUES
('Main content for topic thread 1', 1), --1
@@ -29,5 +29,5 @@
(9, 'Project 2 Thread', 'http://github.com/project1');
-INSERT INTO topic_thread (id, title, parent_id)
+INSERT INTO topic_thread (id, title, referenced_by)
VALUES
(1, 'Topic 1' , 5),
@@ -38,7 +38,7 @@
values
(1,'Follow guidelines'),
- ( 2,'Be respectful');
+ (2,'Be respectful');
-INSERT INTO discussion_thread (id, parent_id)
+INSERT INTO discussion_thread (id, contained_in)
VALUES
(3, 1),
@@ -56,9 +56,5 @@
(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, 2, NOW(), NOW() + INTERVAL '3 days', 'Offensive language');
+-- blacklisted_user matches DDL, so skipped for now
INSERT INTO permissions (name)
@@ -69,5 +65,5 @@
('DELETE');
-INSERT INTO project_role (name, project_id,override_type)
+INSERT INTO project_role (name, valid_in, override_type)
VALUES
('Admin', 5,'EXCLUDE'),
@@ -75,22 +71,22 @@
-INSERT INTO users_project_roles (user_id, role_id)
+INSERT INTO project_role_is_assigned_to_developer (user_id, role_id)
VALUES
(3, 1),
(5, 2);
-INSERT INTO role_permissions (permission_name, role_id)
+INSERT INTO role_permissions (for_permission, for_role)
VALUES
('READ', 1),
('WRITE', 1),
- ('CREATE',1),
- ('DELETE',1);
+ ('CREATE', 1),
+ ('DELETE', 1);
-insert into submission(created_by,status,description)
+insert into submission(submitted_by, status, description)
values
(1,'PENDING','Inappropriate content'),
(3,'DENIED','Spam content');
-INSERT INTO report (id,thread_id, for_user_id)
+INSERT INTO report (id, for_misconduct_in, about)
VALUES
(1, 2, 1),
@@ -98,5 +94,5 @@
-INSERT INTO channel (name, description, project_id, developer_id)
+INSERT INTO channel (name, description, project_has, constructed_by)
VALUES
('Updates', 'Project updates channel', 5, 3);
@@ -105,29 +101,19 @@
---------------- NOV TEST DATA
--- Add new users
+-- Add new users (already correct)
INSERT INTO users (username, is_activate, password, description, registered_at, sex, name, email)
VALUES
- -- Password: user6pass
('user6', true, '$2a$12$jB9g/.KP95fsYYOTy0pwZ.kFrwA/G2cMvPvFLzGtCk8jJ2qO3O.3u', 'Sixth user', NOW(), 'M', 'marko', 'marko@gmail.com'),
- -- Password: user7pass
('user7', true, '$2a$12$KRxRufuMscrlQOLKGw4fBehNLWaP7Zu.M964G2JedKVM4o4wTiJaG', 'Seventh user', NOW(), 'F', 'jana', 'jana@gmail.com'),
- -- Password: user8pass
('user8', true, '$2a$12$SCqlK.Rl72tFT0kIUNP6KuSy6BYzfdb9sKJPSWbIK8/uk7y8U7hgS', 'Eighth user', NOW(), 'M', 'nikola', 'nikola@gmail.com'),
- -- Password: user9pass
('user9', true, '$2a$12$LpDTYNb/i0cohkmszkx93ef9rkgFTNFQz/KqHEYIAE9MPOmlyXJ9m', 'Ninth user', NOW(), 'F', 'elena', 'elena@gmail.com'),
- -- Password: user10pass
('user10', true, '$2a$12$p/kZdDKCUCmXjWTsknss/.UaD4a8vxrTcfvc6mdkpHRRPqRZLLtr6', 'Tenth user', NOW(), 'M', 'petar', 'petar@gmail.com');
--- Add new developers
+-- Add new developers (matches DDL)
INSERT INTO developer (id)
-VALUES
- (6), -- user6
- (7), -- user7
- (8), -- user8
- (9), -- user9
- (10); -- user10
+VALUES (6),(7),(8),(9),(10);
-- Add new threads
-INSERT INTO thread (content, user_id)
+INSERT INTO thread (content, is_created_by)
VALUES
('Main content for topic thread 3', 6), --10
@@ -141,10 +127,9 @@
('Project-specific thread content 4', 10); --18
--- Add embeddable_thread entries for topic and discussion threads
+-- Add embeddable_thread entries
INSERT INTO embeddable_thread (id)
-VALUES
- (10), (11), (12), (13), (15), (16), (17);
+VALUES (10),(11),(12),(13),(15),(16),(17);
--- Add new project_threads
+-- New project_threads (matches DDL)
INSERT INTO project_thread (id, title, repo_url)
VALUES
@@ -152,6 +137,6 @@
(18, 'Project 4 Thread', 'http://github.com/project4');
--- Add new topic_threads
-INSERT INTO topic_thread (id, title, parent_id)
+-- New topic_threads
+INSERT INTO topic_thread (id, title, referenced_by)
VALUES
(10, 'Topic 3', 14),
@@ -159,5 +144,5 @@
(17, 'Topic 8', NULL);
--- Add new topic_guidelinessubmission_id
+-- Guidelines
INSERT INTO topic_guidelines (topic_id, description)
VALUES
@@ -165,6 +150,6 @@
(11, 'No personal attacks');
--- Add new discussion_threads
-INSERT INTO discussion_thread (id, parent_id)
+-- New discussion_threads
+INSERT INTO discussion_thread (id, contained_in)
VALUES
(12, 10),
@@ -173,5 +158,5 @@
(16, 13);
--- Add new likes
+-- Likes (matches DDL)
INSERT INTO likes (user_id, thread_id)
VALUES
@@ -182,18 +167,14 @@
(10, 16);
--- Add new blacklisted_user entries
-INSERT INTO blacklisted_user (topic_id, user_id, moderator_id, start_date, end_date, reason)
-VALUES
- (10, 7, 6, NOW(), NOW() + INTERVAL '5 days', 'Repeated off-topic posts'),
- (11, 8, 7, NOW(), NOW() + INTERVAL '10 days', 'Harassment');
+-- blacklisted_user (already matches DDL)
--- Add new submissions
-INSERT INTO submission (created_by, status, description)
+-- New submissions
+INSERT INTO submission (submitted_by, status, description)
VALUES
(6, 'PENDING', 'Request for new feature'),
(7, 'ACCEPTED', 'Bug report');
--- Add new reports
-INSERT INTO report (id, thread_id, for_user_id)
+-- Reports
+INSERT INTO report (id, for_misconduct_in, about)
VALUES
(3, 10, 7),
@@ -201,22 +182,16 @@
-- Associate developers with projects
-INSERT INTO developer_associated_with_project (project_id, developer_id, started_at)
+INSERT INTO developer_associated_with_project (in_project, about_dev, started_at)
VALUES
- (14, 6, NOW()), -- user6 associated with Project 3
- (14, 7, NOW()), -- user7 associated with Project 3
- (18, 8, NOW()), -- user8 associated with Project 4
- (18, 9, NOW()); -- user9 associated with Project 4
+ (14, 6, NOW()),
+ (14, 7, NOW()),
+ (18, 8, NOW()),
+ (18, 9, NOW());
--- Add new channels
-INSERT INTO channel (name, description, project_id, developer_id)
+-- Channels
+INSERT INTO channel (name, description, project_has, constructed_by)
VALUES
- ('General2', 'General discussion', 14, 6), -- Created by user6 for Project 3
- ('Bugs', 'Bug reports and fixes', 14, 7), -- Created by user7 for Project 3
- ('General2', 'General discussion', 18, 8), -- Created by user8 for Project 4
- ('Ideas', 'Feature ideas', 18, 9); -- Created by user9 for Project 4
-
-
-
-
-
-
+ ('General2', 'General discussion', 14, 6),
+ ('Bugs', 'Bug reports and fixes', 14, 7),
+ ('General2', 'General discussion', 18, 8),
+ ('Ideas', 'Feature ideas', 18, 9);
Index: c/main/resources/db/migration/V7__add-messages.sql
===================================================================
--- src/main/resources/db/migration/V7__add-messages.sql (revision 56b2fe3beb32b81105fb75d25594a5d46ae45ddf)
+++ (revision )
@@ -1,7 +1,0 @@
--- Insert 100 messages per channel
--- Insert 10 channels per project
-
-
-
-
-