Index: docs/versioning/er_models/ER_BUILD_BOARD_v5.1.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v5.1.xml (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v5.1.xml (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -0,0 +1,1 @@
+RECIEVESPROJECT_THREADIS_MODERATED_BYStarted_atTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_OWNERPERMISSIONSDOUBLE3HASDOUBLE3CHANNEL3NCONSTRUCTS3NIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentASSOCIATED_WITHNRegister_at3trueNameSENDS_MESSAGE_INNContentStarted_atEnded_atSent_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleP3NRepo_urlDOUBLE3BLACKLISTED_USERDOUBLE3BLACKLISTED_BYDOUBLE3REFERS_TODOUBLE3BLACKLISTED_FROM3N3trueStart_dateEnd_date3N3NQtruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3M3N3M33PROJECT_RESOURCEtruetrueidIS_A_CHANNEL3DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLE3DOUBLE3FOR_PERMISSION3Noverride_typeFOR_RESOURCENMPROJECT_ROLEVALID_IN3N3truetrueidnameNMSent_at
Index: docs/versioning/er_models/ER_BUILD_BOARD_v5.xml
===================================================================
--- docs/versioning/er_models/ER_BUILD_BOARD_v5.xml (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ docs/versioning/er_models/ER_BUILD_BOARD_v5.xml (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -1,1 +1,1 @@
-RECIEVESPROJECT_THREADIS_MODERATED_BYStarted_atTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_OWNERPERMISSIONSDOUBLE3PROJECT_ROLEDOUBLE3VALID_IN3NDOUBLE3HASDOUBLE3CHANNEL3NCONSTRUCTS3NIS_CREATED_BY3NMODERATOR3trueNametruetrueNamePasswordContentASSOCIATED_WITHNRegister_at3trueNameSENDS_MESSAGE_INNContentStarted_atEnded_atSent_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDMPDISCUSSION_THREADTitleTitleP3NRepo_urlDOUBLE3BLACKLISTED_USERDOUBLE3BLACKLISTED_BYDOUBLE3REFERS_TODOUBLE3BLACKLISTED_FROM3N3trueStart_dateEnd_date3N3NQtruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3M3N3M33PROJECT_RESOURCEtruetrueidIS_A_CHANNEL3DOUBLE3PROJECT_ROLE_PERMISSIONDOUBLE3FOR_ROLEN3DOUBLE3FOR_PERMISSION3Noverride_typeFOR_RESOURCENMIS_A_DEVELOPER3IS_TOPIC3
+RECIEVESPROJECT_THREADIS_MODERATED_BYStarted_atTHREADtrue16dTOPIC_THREADUSER33DEVELOPERtrue16oPROJECT_OWNERPERMISSIONSDOUBLE3HASDOUBLE3CHANNEL3NCONSTRUCTS3NIS_CREATED_BY3NMODERATORtruetrueNamePasswordContentASSOCIATED_WITHNRegister_at3trueNameSENDS_MESSAGE_INNContentStarted_atEnded_atSent_atTAGCREATESASSISGNED_TONtruetrueNameDescriptionLIKESNIs_active3NSexDescriptionSUBBMITTED_BYSUBMISSION3MABOUTREPORT3NFOR_MISCONDUCTtruetrueIdPROJECT_REQUEST3NStatusDescriptionReasonIS_ASSIGNEDNDISCUSSION_THREADTitleTitleP3NRepo_urlDOUBLE3BLACKLISTED_USERDOUBLE3BLACKLISTED_BYDOUBLE3REFERS_TODOUBLE3BLACKLISTED_FROM3N3trueStart_dateEnd_date3N3NQtruetrueIdDOUBLE3FEEDBACKDescriptionSubmission_typeCreated_atWRITTEN_BYtruetrueIdDOUBLE3SUBBMITED_FOR3DOUBLE3GuidelinesCreated_atCreated_atEmailNameUernametrue16d333N3MEMBEDDABLEREFERENCED_BYMCONTAINED_IN3Ntrue16d3M3N3M33PROJECT_RESOURCEtruetrueidIS_A_CHANNEL3DOUBLE3ROLE_PERMISSIONSDOUBLE3FOR_ROLE3DOUBLE3FOR_PERMISSION3Noverride_typeFOR_RESOURCENMPROJECT_ROLEVALID_IN3N3truetrueidnameNMSent_at
Index: normalization.txt
===================================================================
--- normalization.txt (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
+++ normalization.txt (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -0,0 +1,155 @@
+= Нормализација и Подобрување на дизајн
+== Функционални зависности(ФЗ)
+Со цел целосно репрезентирање на атрибутите присутни во релациското мапирање, одредени атрибути со исти имиња ќе бидат преименувани. Лесно се заприметуваат таквите атрибути во релацијата R, на пр. **user(id) -> user_id** и **permission(name) -> permission_name**.
+{{{
+R(
+ user: username, description, password, register_at, email,sex,is_active,user_id,name,
+ blacklisted_user: start_date,end_date,reason,moderator_id
+ tag: tag_name
+ thread: thread_created_at,content,thread_id,parent
+ topic_thread: topic_title, guidelines,
+ project_thread: project_title, repo_url
+ permission: permission_name,
+ custom_role: custom_role_name
+ channel: channel_name, channel_description
+ submission: status, submission_id,
+ feedback: submission_type, feedback_created_at
+ sent_at, start_at_developer, end_at_developer, message_content,
+ moderator_started_at,
+ submission_description, submission_created_at,
+ feedback_description,
+ custom_role_description,
+ submission_creator_id,
+ feedback_creator_id,
+ channel_creator_id,
+ message_sent_by_id
+)
+}}}
+
+=== Иницијални ФЗ
+1. {{{ user_id -> username, description, password, register_at, email,sex,is_active,name (user) }}}
+
+2. {{{ username -> user_id(users) }}}
+
+3. {{{ thread_id -> project_title,repo_url (project) }}}
+
+4. {{{ thread_id -> topic_title,guidelines,parent (topic) }}}
+
+5. {{{ thread_id -> thread_created_at,content (thread) }}}
+
+6. {{{ project_title -> repo_url, thread_created_at, content }}} # Ako ojme so thread_id => project_title -> thread_id -> topic_title...; sho nemat smisla da vazhit.
+
+7. {{{ (parent, topic_title) -> guidelines,created_at,content }}} # (unqiues of topic_title in a given parent)
+
+8. {{{ {thread_id, channel_name} -> channel_description,channel_creator_id }}} # (channel)
+
+9. {{{ {thread_id,moderator_id,user_id, start_date} -> end_date,reason }}} #(blacklisted user)
+
+
+10. {{{ submission_id -> status,submission_description,submission_created_at, submission_creator_id }}} #(submission)
+
+11 {{{ submission_id -> submission_type,feedback_created_at,feedback_description,feedback_creator_id }}} #(feedback)
+
+12 {{{ {thread_id,user_id, start_at_developer} -> end_at_developer }}} #(associated_with)
+
+13 {{{ {thread_id, channel_name, user_id, sent_at} -> message_content,message_sent_by_id }}} #(message)
+
+14 {{{ custom_role_name, thread_id -> custom_role_description }}}
+
+
+15. {{{ {moderator_id, thread_id} -> moderator_started_at }}}
+
+
+16. {{{ moderator_id -> user_id }}}
+
+=== Изведени ФЗ
+1. {{{ username -> description, password, register_at, email,sex,is_active,name }}} #(transitive)
+
+2. {{{ moderator_id -> username, description, password, register_at, email,sex,is_active,name (transitive) }}}
+
+3. {{{ submission_id -> status,submission_description,submission_created_at, submission_type,feedback_created_at,feedback_description (union) }}}
+
+=== Анализа на функционални зависности
+За анализата на функционални зависности ќе го користиме методот на LHS/RHS поделба. Според овај метод потребно е да ги поделиме атрибутите во 3 групи, односно атрибути што се среќаваат на лева страна од Функционалните зависности(LHS) и според истата логика атрибути на десна страна и атрибути кои се среќаваат на 2те страни.
+
+> **LHS**:
+>> {{{ user_id, channel_name, moderator_id, start_date, submission_id, start_at_developer, sent_at, custom_role_name, thread_id }}}
+
+> **RHS**:
+>> {{{ description, password, register_at, email, sex, is_active, name, repo_url, thread_created_at, content, created_at, channel_description, end_date, reason, status, submission_description, submission_created_at, submission_type, feedback_created_at, feedback_description, end_at_developer, message_content, custom_role_description, moderator_started_at, submission_creator_id, feedback_creator_id, channel_creator_id, message_sent_by_id }}}
+
+> **LHS & RHS**:
+>> {{{ username, project_title, parent, topic_title }}}
+
+== Нормализација
+
+=== Сегашна нормална форма
+
+Имајќи предвид дека guidelines e повеќе вредносен атрибути, доаѓаме до заклучок дека сегашната имплементација на релација не е ни во 1NF форма.
+
+=== Декомпозиција на R во 1NF
+Со оделување на guidelines во посебна релација {{{ R1(thread_id, guideline) }}} и {{{ R2(username, thread_id, passowrd...) }}} постигнуваме релациите да се во 1NF.
+
+Според досега разгледаното за R1(thread_id, guideline) ги имаме следниве ФЗ:
+
+1. {{{ thread_id -> thread_id }}}
+2. {{{ guideline -> guideline }}}
+
+Според функционалните зависимости, единствената комбинација на атрибути која го задоволува условот за супер клуч е {{{ {thread_id, guideline} }}}, бидејќи {{{ {thread_id, guideline}+ = {thread_id, guideline} }}}. Сепак, со цел да оптимизираме операциите на базата (особено внесување и пребарување), ќе воведеме нов сурогат клуч **id** како примарен клуч. Заедно со него ги додаваме и следниве ФЗ {{{ id -> thread_id, guideline }}}.
+
+Ова е практично бидејќи природниот клуч {{{ {thread_id, guideline} }}} содржи guideline, која е varchar атрибут со променлива големина. Ако се користеше само природниот клуч, PostgreSQL ќе креираше B-tree индекс врз guideline, кој е поголем по големина и поради тоа побавно ќе се ажурира при **DRU** операции (потребни се повеќе IO страници за индексирање и податоци). Со воведување на сурогат клуч **id** како мал фиксно-димензионален атрибут (на пр. serial или bigserial), индексите се помали и операциите се поефикасни.
+
+# Не сум најсигурен за ова, имат смисла. Аргумент против ова е дека според вака дефинираното значит дека не смет некој guideline да се повторвит, шо да ќе клајме unqiue constraint, ама тогаш немат нешто шо оптимизиравме и падвит во вода нештово. Така да можда ке требат да се препишит имајќи го во предвид фактот дека некоја уникатност без id.
+
+Со ова R1 ни се совпаѓат со релацијата **topic_guidelines**, отсега понатаму ќе ја референцираме како таква.
+
+=== Декомпозиција R1 до BCNF
+Според следниве ФЗ за R1:
+1. {{{ thread_id -> thread_id }}}
+2. {{{ guideline -> guideline }}}
+3. {{{ id -> thread_id, guideline }}}
+
+Според тоа што само **id** ги определува сите атрибути({{{ id+ -> id,thread_id,guideline}}}) следува дека единствен супер клуч е **id**.
+
+Клуч за оваа релациja би бил **id**, бидејќи неговиот затварач е {{{ id+ = {thread_id, guideline} }}} и е најмал затварач кој ја определува целата релација.
+
+Според 2NF релацијата не смеe да содржи парцијални зависности. Ова веќе е задоволено од ФЗ за R1.
+Според 3NF релацијата потребно е секоја ФЗ(X->A) да ги задоволи следниве својства:
+
+1. X e супер клуч на R
+2. А е примарен атрибут во R.
+
+Според ова релацијата е веќе во 3NF.
+Според BCNF секоја релација потребно е да го задоволува следново својство:
+ "За секое нетривијално (X->A) важи дека X е суперклуч на R".
+Според тоа што **id** e клуч на R1, следува дека релацијата е во BCNF.
+
+=== Декомпозиција R2 во 2NF
+Најпрвин да согледаме дека во било кој супер клуч атрибутите кои се присутни само на левата страна мора да бидат вклучени. Најпрвин да започниме само со тие атрибути:
+
+{{{
+ {user_id, channel_name, moderator_id, start_date, submission_id, start_at_developer, sent_at, custom_role_name , thread_id} = {
+user_id, channel_name, moderator_id, start_date, submission_id, start_at_developer, sent_at, custom_role_name , thread_id, username, description, password, register_at, email,sex,is_active,name, project_title,repo_url, topic_title, guidelines, parent, thread_created_at, content, channel_description, end_date, status, submission_description, submission_created_at, submission_type,feedback_created_at,feedback_description, end_at_developer, message_content, custom_role_description, moderator_started_at,
+}
+}}}
+
+Според тоа што затворачот ги опфаќа сите атрибути може да се заклучи дека ова е најминималното множество чиишто покривач ја дава целата релација, што значи дека е примарен клуч.
+
+Имајќи во предвид примарниот клуч можит да ги изведиме следниве релации, притоа кај релациите кај кои има совпаѓање со некоја релација од релационото мапирање, ќе ги додаваме нивните имиња.
+
+
+{{{ R21(user_id,username, description, password, register_at, email,sex,is_active,name) == users }}}
+R22(project_title,repo_url,topic_title,guidelines,parent,thread_created_at,content)
+{{{ R23(thread_id,channel_name,channel_description, channel_creator_id) == channel }}}
+{{{ R24(thread_id,moderator_id,user_id,start_date,end_date) }}}
+{{{ R25(submission_id,status,submission_description,submission_created_at, submission_type,feedback_created_at,feedback_description, feedback_creator_id, submission_creator_id) }}} #Tuka ke imame problem, ama mozhit da rechime deka procenavme deka ke imat dosta nullki pa zaradi to rekovme da se odelat vo posebna relacija.
+{{{ R26(thread_id,user_id, start_at_developer,end_at_developer) == developer_associated_with_project }}}
+{{{ R27(thread_id, channel_name, message_sent_by_id, sent_at, message_content) === messages}}}
+{{{ R28(thread_id,custom_role_name,custom_role_description) == project_roles }}}
+{{{ R29(thread_id,moderator_id,moderator_started_at) == topic_threads_moderators}}}
+
+
+
+
+
+
Index: src/main/java/com/db/finki/www/build_board/config/WebSocketConfig.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/config/WebSocketConfig.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/config/WebSocketConfig.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -19,10 +19,7 @@
private final ObjectMapper objectMapper;
- private final WebSocketPermissionInterceptor webSocketPermissionInterceptor;
- public WebSocketConfig(ObjectMapper objectMapper, WebSocketPermissionInterceptor webSocketPermissionInterceptor) {
- this.objectMapper = objectMapper;
- this.webSocketPermissionInterceptor = webSocketPermissionInterceptor;
- }
+ public WebSocketConfig(ObjectMapper objectMapper) {
+ this.objectMapper = objectMapper;}
@Override
Index: c/main/java/com/db/finki/www/build_board/config/WebSocketPermissionInterceptor.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/config/WebSocketPermissionInterceptor.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ (revision )
@@ -1,83 +1,0 @@
-package com.db.finki.www.build_board.config;
-
-import com.db.finki.www.build_board.entity.access_managment.Permission;
-import com.db.finki.www.build_board.entity.channel.Channel;
-import com.db.finki.www.build_board.entity.thread.Project;
-import com.db.finki.www.build_board.entity.user_type.BBUser;
-import com.db.finki.www.build_board.service.access_managment.ProjectAccessManagementService;
-import com.db.finki.www.build_board.service.channel.ChannelService;
-import com.db.finki.www.build_board.service.thread.impl.ProjectService;
-import org.apache.coyote.BadRequestException;
-import org.springframework.http.server.ServerHttpRequest;
-import org.springframework.http.server.ServerHttpResponse;
-import org.springframework.security.access.AccessDeniedException;
-import org.springframework.http.server.ServletServerHttpRequest;
-import org.springframework.stereotype.Service;
-import org.springframework.util.MultiValueMap;
-import org.springframework.web.socket.WebSocketHandler;
-import org.springframework.web.socket.server.HandshakeInterceptor;
-import org.springframework.web.util.UriComponentsBuilder;
-
-import java.net.URI;
-import java.net.URLDecoder;
-import java.nio.charset.StandardCharsets;
-import java.util.Map;
-
-@Service
-public class WebSocketPermissionInterceptor implements HandshakeInterceptor {
- private final ProjectAccessManagementService projectAccessManagementService;
- private final ChannelService channelService;
- private final ProjectService projectService;
-
- public WebSocketPermissionInterceptor(ProjectAccessManagementService projectAccessManagementService, ChannelService channelService, ProjectService projectService) {this.projectAccessManagementService = projectAccessManagementService;
- this.channelService = channelService;
- this.projectService = projectService;
- }
-
- private boolean checkStringParam(String x){
- return x == null || x.isBlank();
- }
-
- @Override
- public boolean beforeHandshake(ServerHttpRequest request, ServerHttpResponse response, WebSocketHandler wsHandler, Map attributes) throws Exception {
- URI uri = request.getURI();
- MultiValueMap queryParams =
- UriComponentsBuilder
- .fromUri(uri).build().getQueryParams();
-
- String projectName = queryParams.getFirst("project");
- String channelName = queryParams.getFirst("channelName");
-
- if(checkStringParam(projectName) || checkStringParam(channelName)){
- throw new BadRequestException("You didn't provide the correct params");
- }
-
- projectName= URLDecoder.decode(projectName,
- StandardCharsets.UTF_8);
- channelName= URLDecoder.decode(channelName,StandardCharsets.UTF_8);
-
- Project project = projectService.getByTitle(projectName);
- Channel channel = channelService.getByNameAndProject(channelName,
- project);
-
- BBUser user = (BBUser)
- ((ServletServerHttpRequest) request).getServletRequest().getSession().getAttribute("user");
-
- if(!projectAccessManagementService.hasPermissionToAccessResource(
- user.getId(),
- Permission.WRITE,
- channel.getProjectResource()
- .getId(),
- project.getId()
- )){
- return false;
- }
-
- return true;
- }
-
- @Override
- public void afterHandshake(ServerHttpRequest request, ServerHttpResponse response, WebSocketHandler wsHandler, Exception exception) {
-
- }
-}
Index: src/main/java/com/db/finki/www/build_board/controller/channel/ChannelController.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/controller/channel/ChannelController.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/controller/channel/ChannelController.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -33,5 +33,5 @@
MessageService messageService, ProjectService projectService,
ProjectAccessManagementService projectAccessManagementService
- ) {
+ ) {
this.channelService = channelService;
this.messageMapper = messageMapper;
@@ -45,14 +45,15 @@
Channel channel,
int userId,
- int projectId,
+ Project project,
String permission
- ) {
- if (!projectAccessManagementService.hasPermissionToAccessResource(userId,
- permission,
- channel
- .getProjectResource()
- .getId(),
- projectId
- )) {
+ ) {
+
+ boolean hasPermission = false;
+ if(channel == null) {
+ hasPermission = projectAccessManagementService.hasPermissionToAccessResource(userId,permission,null,project);
+ } else {
+ hasPermission = projectAccessManagementService.hasPermissionToAccessResource(userId, permission, channel.getId(), project);
+ }
+ if (!hasPermission) {
System.out.println("vleze deny access");
model.addAttribute("error",
@@ -78,5 +79,5 @@
RedirectAttributes redirectAttributes,
@SessionAttribute @P("user") BBUser user
- ) {
+ ) {
Channel c = (Channel) redirectAttributes.getAttribute("channel");
@@ -98,17 +99,9 @@
try {
- checkIfAuthorized(model,
- c,
- user.getId(),
- project.getId(),
- Permission.READ
- );
- boolean canWrite = projectAccessManagementService
- .hasPermissionToAccessResource(user.getId(),
+ checkIfAuthorized(model, c, user.getId(), project, Permission.READ);
+ boolean canWrite = projectAccessManagementService.hasPermissionToAccessResource(user.getId(),
Permission.WRITE,
- c
- .getProjectResource()
- .getId(),
- project.getId());
+ c.getId(),
+ project);
model.addAttribute("canWrite",
canWrite);
@@ -116,5 +109,5 @@
return "channels/show-channel";
} catch (RuntimeException e) {
- if(e.getMessage().contains("Unauthorized")) {
+ if (e.getMessage().contains("Unauthorized")) {
return "redirect:/projects/" + project.getId();
}
@@ -131,16 +124,16 @@
@SessionAttribute @P("user") BBUser user,
RedirectAttributes redirectAttributes, Model model
- ) {
+ ) {
Channel c = channelService.getByNameAndProject(channelName,
project);
- try{
- checkIfAuthorized(model,c,user.getId(),project.getId(),Permission.DELETE);
+ try {
+ checkIfAuthorized(model, c, user.getId(), project, Permission.DELETE);
channelService.deleteChannel(channelName,
project);
return "redirect:/projects/" + project.getTitle();
- }catch (RuntimeException e) {
- if(e.getMessage().contains("Unauthorized")) {
+ } catch (RuntimeException e) {
+ if (e.getMessage().contains("Unauthorized")) {
return "redirect:/projects/" + project.getId();
}
@@ -156,17 +149,18 @@
@SessionAttribute @P("user") BBUser user, RedirectAttributes redirectAttributes,
Model model
- ) {
+ ) {
try {
- Channel channel = channelService.create(project,
- channelName,
- channelDescription,
- user);
- try{
- checkIfAuthorized(model,channel,user.getId(),project.getId(),Permission.CREATE);
+
+ try {
+ checkIfAuthorized(model, null, user.getId(), project, Permission.CREATE);
+ Channel channel = channelService.create(project,
+ channelName,
+ channelDescription,
+ user);
redirectAttributes.addFlashAttribute("channel",
channel);
- }catch (RuntimeException e) {
- if(e.getMessage().contains("Unauthorized")) {
+ } catch (RuntimeException e) {
+ if (e.getMessage().contains("Unauthorized")) {
return "redirect:/projects/" + project.getId();
}
Index: src/main/java/com/db/finki/www/build_board/controller/channel/ChannelWebSocketController.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/controller/channel/ChannelWebSocketController.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/controller/channel/ChannelWebSocketController.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -44,8 +44,7 @@
Project project = projectService.getById(Long.valueOf(messageDTO.getProjectId()));
Channel channel = channelService.getByNameAndProject(messageDTO.getChannelName(), project);
- if(!projectAccessManagementService.hasPermissionToAccessResource(user.getId(), Permission.WRITE,channel.getProjectResource().getId(),project.getId())){
+ if(!projectAccessManagementService.hasPermissionToAccessResource(user.getId(), Permission.WRITE,channel.getId(),project)){
return null;
}
-
messageDTO.setSentAt(LocalDateTime.now());
Index: src/main/java/com/db/finki/www/build_board/controller/thread_controller/ProjectController.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/controller/thread_controller/ProjectController.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/controller/thread_controller/ProjectController.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -53,4 +53,5 @@
@GetMapping("/{title}")
public String getProjectPage(
+ @SessionAttribute BBUser user,
@PathVariable(name = "title") Project project, Model model, RedirectAttributes redirectAttributes,
@RequestParam(required = false) String duplicateTitle
@@ -63,4 +64,9 @@
projectService.getAllDevelopersForProject(project));
String error = (String) redirectAttributes.getAttribute("error");
+ boolean canCreateChannel = false;
+ if(user != null){
+ canCreateChannel = projectAccessManagementService.hasPermissionToAccessResource(user.getId(),"CREATE",null,project);
+
+ }
if (error != null) {
@@ -72,4 +78,6 @@
"There already exists a project with the provided title");
}
+
+ model.addAttribute("canCreateChannel", canCreateChannel);
Hibernate.initialize(project.getTags());
Index: src/main/java/com/db/finki/www/build_board/dto/PermissionResourceDTO.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/dto/PermissionResourceDTO.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/dto/PermissionResourceDTO.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -6,4 +6,6 @@
import lombok.Setter;
+import java.util.UUID;
+
@AllArgsConstructor
@NoArgsConstructor
@@ -12,4 +14,4 @@
public class PermissionResourceDTO {
String permissionName;
- int projectResourceID;
+ UUID channelId;
}
Index: src/main/java/com/db/finki/www/build_board/dto/PermissionResourceWrapper.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/dto/PermissionResourceWrapper.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/dto/PermissionResourceWrapper.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -2,5 +2,5 @@
import com.db.finki.www.build_board.entity.access_managment.Permission;
-import com.db.finki.www.build_board.entity.access_managment.ProjectResource;
+import com.db.finki.www.build_board.entity.channel.Channel;
import lombok.AllArgsConstructor;
import lombok.Getter;
@@ -14,5 +14,5 @@
public class PermissionResourceWrapper {
Permission permission;
- ProjectResource resource;
+ Channel channel;
}
Index: c/main/java/com/db/finki/www/build_board/entity/access_managment/ProjectResource.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/access_managment/ProjectResource.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ (revision )
@@ -1,34 +1,0 @@
-package com.db.finki.www.build_board.entity.access_managment;
-
-import jakarta.persistence.*;
-import lombok.AllArgsConstructor;
-import lombok.Getter;
-import lombok.NoArgsConstructor;
-import lombok.Setter;
-
-import java.util.Objects;
-
-@Getter
-@Setter
-@AllArgsConstructor
-@NoArgsConstructor
-@Entity
-@Table
-public class ProjectResource {
- @Id
- @GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "project_resource_id_seq")
- @SequenceGenerator(name = "project_resource_id_seq",sequenceName = "project_resource_id_seq",allocationSize=1)
- int id;
-
- @Override
- public boolean equals(Object o) {
- if (o == null || getClass() != o.getClass()) return false;
- ProjectResource that = (ProjectResource) o;
- return id == that.id;
- }
-
- @Override
- public int hashCode() {
- return Objects.hashCode(id);
- }
-}
Index: src/main/java/com/db/finki/www/build_board/entity/access_managment/ProjectRolePermissionResourceOverride.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/access_managment/ProjectRolePermissionResourceOverride.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/entity/access_managment/ProjectRolePermissionResourceOverride.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -25,6 +25,3 @@
return id.getProjectRolePermission();
}
- public ProjectResource getProjectResource() {
- return id.getProjectResource();
- }
}
Index: src/main/java/com/db/finki/www/build_board/entity/channel/Channel.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/channel/Channel.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/entity/channel/Channel.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -1,13 +1,12 @@
package com.db.finki.www.build_board.entity.channel;
-import com.db.finki.www.build_board.entity.access_managment.ProjectResource;
-import com.db.finki.www.build_board.entity.compositeId.ChannelId;
import com.db.finki.www.build_board.entity.thread.Project;
import com.db.finki.www.build_board.entity.user_type.Developer;
import jakarta.persistence.*;
-import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
+
+import java.util.UUID;
@Table(name = "channel")
@@ -15,12 +14,14 @@
@Getter
@Setter
-@IdClass(ChannelId.class)
@NoArgsConstructor
public class Channel {
+
@Id
+ @GeneratedValue
+ private UUID id;
+
private String name;
-
- @Id
+
@ManyToOne
@JoinColumn(name = "project_id",referencedColumnName = "id",nullable = false)
@@ -33,7 +34,4 @@
private Developer developer;
- @ManyToOne
- @JoinColumn(name = "project_resource_id",referencedColumnName = "id")
- private ProjectResource projectResource;
public Channel(String name, Project project, String description, Developer developer) {
Index: c/main/java/com/db/finki/www/build_board/entity/compositeId/ChannelId.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/compositeId/ChannelId.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ (revision )
@@ -1,31 +1,0 @@
-package com.db.finki.www.build_board.entity.compositeId;
-
-import lombok.AllArgsConstructor;
-import lombok.Getter;
-import lombok.NoArgsConstructor;
-import lombok.Setter;
-import org.springframework.stereotype.Service;
-
-import java.util.Objects;
-
-@Getter
-@Setter
-@AllArgsConstructor
-@NoArgsConstructor
-public class ChannelId {
- private int project;
- private String name;
-
- @Override
- public boolean equals(Object o) {
- if (this == o) return true;
- if (o == null || getClass() != o.getClass()) return false;
- ChannelId channelId = (ChannelId) o;
- return project == channelId.project && Objects.equals(name, channelId.name);
- }
-
- @Override
- public int hashCode() {
- return Objects.hash(project, name);
- }
-}
Index: c/main/java/com/db/finki/www/build_board/entity/compositeId/ProjectRoleId.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/compositeId/ProjectRoleId.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ (revision )
@@ -1,6 +1,0 @@
-package com.db.finki.www.build_board.entity.compositeId;
-
-import com.db.finki.www.build_board.entity.thread.Project;
-import jakarta.persistence.*;
-import lombok.*;
-
Index: src/main/java/com/db/finki/www/build_board/entity/compositeId/ProjectRolePermissionResourceOverrideId.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/compositeId/ProjectRolePermissionResourceOverrideId.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/entity/compositeId/ProjectRolePermissionResourceOverrideId.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -1,6 +1,6 @@
package com.db.finki.www.build_board.entity.compositeId;
-import com.db.finki.www.build_board.entity.access_managment.ProjectResource;
import com.db.finki.www.build_board.entity.access_managment.ProjectRolePermission;
+import com.db.finki.www.build_board.entity.channel.Channel;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
@@ -24,6 +24,6 @@
private ProjectRolePermission projectRolePermission;
@ManyToOne(optional = false)
- @JoinColumn(name = "project_resource_id",referencedColumnName = "id")
- private ProjectResource projectResource;
+ @JoinColumn(name = "channel_id",referencedColumnName = "id")
+ private Channel channel;
@Override
@@ -31,10 +31,10 @@
if (o == null || getClass() != o.getClass()) return false;
ProjectRolePermissionResourceOverrideId that = (ProjectRolePermissionResourceOverrideId) o;
- return Objects.equals(projectRolePermission, that.projectRolePermission) && Objects.equals(projectResource, that.projectResource);
+ return Objects.equals(projectRolePermission, that.projectRolePermission) && Objects.equals(channel, that.channel);
}
@Override
public int hashCode() {
- return Objects.hash(projectRolePermission, projectResource);
+ return Objects.hash(projectRolePermission, channel);
}
}
Index: src/main/java/com/db/finki/www/build_board/entity/view/RoleChannelPermissions.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/entity/view/RoleChannelPermissions.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/entity/view/RoleChannelPermissions.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -10,4 +10,5 @@
import java.util.Objects;
+import java.util.UUID;
@Entity
@@ -20,6 +21,6 @@
public class RoleChannelPermissions {
@Id
- @Column(name = "project_resource_id")
- private Integer resourceId;
+ @Column(name = "channel_id")
+ private UUID channelId;
@Column(name = "name")
private String channelName;
@@ -34,10 +35,10 @@
if (o == null || getClass() != o.getClass()) return false;
RoleChannelPermissions that = (RoleChannelPermissions) o;
- return Objects.equals(resourceId, that.resourceId) && Objects.equals(channelName, that.channelName) && Objects.equals(role, that.role) && Objects.equals(permissions, that.permissions);
+ return Objects.equals(channelId, that.channelId) && Objects.equals(channelName, that.channelName) && Objects.equals(role, that.role) && Objects.equals(permissions, that.permissions);
}
@Override
public int hashCode() {
- return Objects.hash(resourceId, channelName, role, permissions);
+ return Objects.hash(channelId, channelName, role, permissions);
}
}
Index: src/main/java/com/db/finki/www/build_board/repository/ReportRepository.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/repository/ReportRepository.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/repository/ReportRepository.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -11,8 +11,8 @@
@Query(value = """
select *
- from report r
- join submission s
+ from report r
+ join submission s
on s.id = r.id
- where (:latest is null or (s.created_by,s.created_at) IN ( select created_by,max(created_at) from submission r group by created_by))
+ where (:latest is null or (s.created_by,s.created_at) IN ( select created_by,max(created_at) from submission r group by created_by))
and r.thread_id =:topicId
and (:status is null or s.status=:status)
@@ -24,17 +24,17 @@
@Param("status") String status,
@Param("latest") String forLatest
- );
+ );
@Query(
nativeQuery = true,
value = """
- select *
- from report r
- join submission s
- on s.id = r.id
- where s.created_by = :user_id and (:status is null or s.status= :status)
+ select *
+ from report r
+ join submission s
+ on s.id = r.id
+ where s.created_by = :user_id and (:status is null or s.status= :status)
"""
)
List findAllBySendByUsernameAndStatus(@Param("user_id") long userId,
- @Param("status") String status);
+ @Param("status") String status);
}
Index: src/main/java/com/db/finki/www/build_board/repository/access_managment/ProjectRolePermissionResourceOverrideRepository.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/repository/access_managment/ProjectRolePermissionResourceOverrideRepository.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/repository/access_managment/ProjectRolePermissionResourceOverrideRepository.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -14,4 +14,5 @@
//todo override_type vo project_role
import java.util.List;
+import java.util.UUID;
@Repository
@@ -29,5 +30,5 @@
ON pr.id = rpo.role_id
AND rpo.permission_name = rp.permission_name
- AND rpo.project_resource_id = :resourceId
+ AND rpo.channel_id = :resourceId
WHERE upr.user_id = :userId
@@ -35,11 +36,29 @@
AND rp.permission_name = :permissionName
AND (
- (pr.override_type = 'INCLUDE' AND rpo.project_resource_id IS NOT NULL)
- OR (pr.override_type = 'EXCLUDE' AND rpo.project_resource_id IS NULL)
+ (pr.override_type = 'INCLUDE' AND rpo.channel_id IS NOT NULL)
+ OR (pr.override_type = 'EXCLUDE' AND rpo.channel_id IS NULL)
)), FALSE
) AS has_access;
""", nativeQuery = true)
- boolean hasPermissionForResource(int projectId,int userId,String permissionName,int resourceId);
+ boolean hasPermissionForResource(int projectId, int userId, String permissionName, UUID resourceId);
List findAllByIdProjectRolePermissionIdRole(ProjectRole role);
+
+ @Query(nativeQuery = true,value = """
+ SELECT COALESCE(
+ EXISTS (SELECT 1
+ FROM users_project_roles upr
+ JOIN project_role pr
+ ON upr.role_id = pr.id
+ LEFT JOIN role_permissions rp
+ ON pr.id = rp.role_id AND rp.permission_name = :permissionName
+ WHERE upr.user_id = :userId
+ AND pr.project_id = :projectId
+ AND (
+ (pr.override_type = 'INCLUDE' AND rp.permission_name IS NOT NULL)
+ OR (pr.override_type = 'EXCLUDE' AND rp.permission_name IS NULL)
+ )), FALSE
+ ) AS has_access;
+""")
+ boolean hasGlobalPermission(String permissionName,int projectId,int userId);
@Modifying(clearAutomatically = true)
Index: src/main/java/com/db/finki/www/build_board/repository/channel/ChannelRepository.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/repository/channel/ChannelRepository.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/repository/channel/ChannelRepository.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -2,14 +2,21 @@
import com.db.finki.www.build_board.entity.channel.Channel;
-import com.db.finki.www.build_board.entity.compositeId.ChannelId;
+import com.db.finki.www.build_board.entity.thread.Project;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;
+import java.util.UUID;
@Repository
-public interface ChannelRepository extends JpaRepository {
+public interface ChannelRepository extends JpaRepository {
List findAllByProjectIdOrderByNameAsc(Integer projectId);
List findAllByDeveloperIdOrderByNameAsc(Integer developerId);
Channel findByProjectTitleAndNameOrderByNameAsc(String title, String name);
+
+ List findByIdIn(List ids);
+
+ void deleteByNameAndProjectId(String name, Integer projectId);
+
+ Channel findByNameAndProject(String name,Project project);
}
Index: src/main/java/com/db/finki/www/build_board/service/access_managment/AddRoleDTOEntitiesMapper.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/service/access_managment/AddRoleDTOEntitiesMapper.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/service/access_managment/AddRoleDTOEntitiesMapper.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -6,16 +6,26 @@
import com.db.finki.www.build_board.dto.PermissionResourceWrapper;
import com.db.finki.www.build_board.entity.access_managment.Permission;
-import com.db.finki.www.build_board.entity.access_managment.ProjectResource;
+import com.db.finki.www.build_board.entity.channel.Channel;
import com.db.finki.www.build_board.entity.thread.Project;
+import com.db.finki.www.build_board.repository.channel.ChannelRepository;
+import com.db.finki.www.build_board.service.channel.ChannelService;
import com.db.finki.www.build_board.service.thread.impl.ProjectService;
import org.springframework.stereotype.Service;
import java.util.List;
+import java.util.Map;
+import java.util.UUID;
+import java.util.function.Function;
+import java.util.stream.Collectors;
@Service
public class AddRoleDTOEntitiesMapper {
private final ProjectService projectService;
+ private final ChannelService channelService;
- public AddRoleDTOEntitiesMapper(ProjectService projectService) {this.projectService = projectService;}
+ public AddRoleDTOEntitiesMapper(ProjectService projectService, ChannelService channelService) {
+ this.projectService = projectService;
+ this.channelService = channelService;
+ }
private Project findProject(String projectTitle){
@@ -23,10 +33,22 @@
}
- private List findPermissionResource(List resourceDTOS){
- return resourceDTOS.stream().map(d -> new PermissionResourceWrapper(
- new Permission(d.getPermissionName().toUpperCase()),
- new ProjectResource(d.getProjectResourceID())
- )).toList();
+ private List findPermissionResource(List resourceDTOS) {
+ List channelResourceIds = resourceDTOS.stream()
+ .map(PermissionResourceDTO::getChannelId)
+ .toList();
+
+ List channels = channelService.getAllByIds(channelResourceIds);
+
+ Map channelMap = channels.stream()
+ .collect(Collectors.toMap(Channel::getId, Function.identity()));
+
+ return resourceDTOS.stream()
+ .map(dto -> new PermissionResourceWrapper(
+ new Permission(dto.getPermissionName().toUpperCase()),
+ channelMap.get(dto.getChannelId())
+ ))
+ .toList();
}
+
private List findGlobalPermissions(List global){
Index: src/main/java/com/db/finki/www/build_board/service/access_managment/ProjectAccessManagementService.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/service/access_managment/ProjectAccessManagementService.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/service/access_managment/ProjectAccessManagementService.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -1,7 +1,7 @@
package com.db.finki.www.build_board.service.access_managment;
-import com.db.finki.www.build_board.common.enums.ProjectResourcePermissionOverrideType;
import com.db.finki.www.build_board.dto.PermissionResourceWrapper;
import com.db.finki.www.build_board.entity.access_managment.*;
+import com.db.finki.www.build_board.entity.channel.Channel;
import com.db.finki.www.build_board.entity.compositeId.ProjectRolePermissionId;
import com.db.finki.www.build_board.entity.compositeId.ProjectRolePermissionResourceOverrideId;
@@ -13,13 +13,14 @@
import com.db.finki.www.build_board.repository.access_managment.ProjectRolePermissionRepository;
import com.db.finki.www.build_board.repository.access_managment.UserProjectRoleRepository;
+import com.db.finki.www.build_board.service.channel.ChannelService;
import jakarta.transaction.Transactional;
-import org.hibernate.Hibernate;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
-import java.util.Objects;
+import java.util.Map;
+import java.util.UUID;
+import java.util.function.Function;
import java.util.stream.Collectors;
-import java.util.stream.Stream;
@Service
@@ -30,9 +31,10 @@
private final ProjectRolePermissionResourceOverrideRepository projectRolePermissionResourceOverrideRepository;
private final UserProjectRoleRepository userProjectRoleRepository;
+ private final ChannelService channelService;
public ProjectAccessManagementService(
ProjectRoleRepository projectRoleRepository,
ProjectRolePermissionRepository projectRolePermissionRepository,
- ProjectRolePermissionResourceOverrideRepository projectRolePermissionResourceOverrideRepository, UserProjectRoleRepository userProjectRoleRepository
+ ProjectRolePermissionResourceOverrideRepository projectRolePermissionResourceOverrideRepository, UserProjectRoleRepository userProjectRoleRepository, ChannelService channelService
) {
this.projectRoleRepository = projectRoleRepository;
@@ -41,14 +43,20 @@
this.projectRolePermissionRepository = projectRolePermissionRepository;
this.userProjectRoleRepository = userProjectRoleRepository;
+ this.channelService = channelService;
}
public boolean hasPermissionToAccessResource(
- int userId, String permission, int resourceId,
- int projectId
+ int userId, String permission, UUID resourceId,
+ Project project
) {
- return projectRolePermissionResourceOverrideRepository.hasPermissionForResource(projectId,
- userId,
- permission,
- resourceId);
+ if(project.getUser().getId() == userId) return true;
+
+ if(resourceId == null){
+ return projectRolePermissionResourceOverrideRepository.hasGlobalPermission(permission,project.getId(),userId);
+ }else {
+ return projectRolePermissionResourceOverrideRepository.hasPermissionForResource(project.getId(), userId,permission,resourceId);
+ }
+
+
}
@@ -68,8 +76,5 @@
.stream()
.map(p -> new ProjectRolePermission(
- new ProjectRolePermissionId(
- p,
- role
- )
+ new ProjectRolePermissionId(p, role)
))
.toList();
@@ -91,5 +96,5 @@
private List mapToResourceOverrides(
- List resources,
+ List channels,
List rolePermissions
@@ -97,10 +102,11 @@
List overrides = new ArrayList<>();
- for (int i = 0; i < resources.size(); i++) {
+
+ for (int i = 0; i < channels.size(); i++) {
overrides.add(
new ProjectRolePermissionResourceOverride(
new ProjectRolePermissionResourceOverrideId(
rolePermissions.get(i),
- resources.get(i)
+ channels.get(i)
)
)
@@ -111,6 +117,6 @@
}
- private List getResources(List rolePermissions) {
- return rolePermissions.stream().map(PermissionResourceWrapper::getResource).toList();
+ private List getResources(List rolePermissions) {
+ return rolePermissions.stream().map(PermissionResourceWrapper::getChannel).toList();
}
@@ -158,5 +164,7 @@
public void deleteRole(ProjectRole role) {
- projectRoleRepository.deleteById(role.getId());
+ if(!role.getName().equals("Admin")) {
+ projectRoleRepository.deleteById(role.getId());
+ }
}
Index: src/main/java/com/db/finki/www/build_board/service/channel/ChannelService.java
===================================================================
--- src/main/java/com/db/finki/www/build_board/service/channel/ChannelService.java (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/java/com/db/finki/www/build_board/service/channel/ChannelService.java (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -3,5 +3,4 @@
import com.db.finki.www.build_board.entity.access_managment.ProjectRole;
import com.db.finki.www.build_board.entity.channel.Channel;
-import com.db.finki.www.build_board.entity.compositeId.ChannelId;
import com.db.finki.www.build_board.entity.thread.Project;
import com.db.finki.www.build_board.entity.user_type.BBUser;
@@ -12,7 +11,9 @@
import com.db.finki.www.build_board.repository.access_managment.ProjectRolePermissionResourceOverrideRepository;
import com.db.finki.www.build_board.repository.channel.ChannelRepository;
+import jakarta.transaction.Transactional;
import org.springframework.stereotype.Service;
import java.util.List;
+import java.util.UUID;
@Service
@@ -61,7 +62,11 @@
return channelRepository.findByProjectTitleAndNameOrderByNameAsc(project.getTitle(), channelName);
}
+ @Transactional
public void deleteChannel(String channelName,Project project){
- ChannelId cid = new ChannelId(project.getId(), channelName);
- channelRepository.deleteById(cid);
+ channelRepository.deleteByNameAndProjectId(channelName,project.getId());
+ }
+
+ public List getAllByIds(List ids){
+ return channelRepository.findByIdIn(ids);
}
Index: src/main/resources/db/migration/V1__init_ddl.sql
===================================================================
--- src/main/resources/db/migration/V1__init_ddl.sql (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/resources/db/migration/V1__init_ddl.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -1,7 +1,2 @@
---- 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;
@@ -45,4 +40,6 @@
drop table if exists embeddable_thread;
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
+
---- DDL
@@ -54,8 +51,8 @@
name varchar(32) not null,
is_activate bool DEFAULT true,
- password VARCHAR(72),
+ password VARCHAR(72) NOT NULL,
description VARCHAR(200),
registered_at TIMESTAMP DEFAULT NOW() NOT NULL,
- sex VARCHAR(1)
+ sex VARCHAR(1) NOT NULL
);
CREATE TABLE moderator
@@ -116,6 +113,6 @@
CREATE TABLE topic_threads_moderators
(
- thread_id INT REFERENCES topic_thread (id) ON DELETE CASCADE,
- user_id INT REFERENCES moderator (id) ON DELETE CASCADE,
+ thread_id INT REFERENCES topic_thread (id) ON DELETE CASCADE NOT NULL,
+ user_id INT REFERENCES moderator (id) ON DELETE CASCADE NOT NULL,
started_at TIMESTAMP DEFAULT NOW() NOT NULL,
PRIMARY KEY (thread_id, user_id)
@@ -124,5 +121,5 @@
(
name VARCHAR(64) PRIMARY KEY,
- creator_id int REFERENCES moderator (id) on delete CASCADE not null
+ creator_id int REFERENCES users (id) on delete CASCADE not null
);
CREATE TABLE tag_threads
@@ -135,13 +132,12 @@
CREATE TABLE blacklisted_user
(
- topic_id INT REFERENCES topic_thread (id) ON DELETE CASCADE, --BLACLISTED_FROM
- user_id INT REFERENCES users (id) ON DELETE CASCADE, --REFERS_TO
- moderator_id INT REFERENCES moderator (id) ON DELETE CASCADE, --BLACKLISTED_BY
- start_date TIMESTAMP,
+ topic_id INT REFERENCES topic_thread (id) ON DELETE CASCADE NOT NULL, --BLACLISTED_FROM
+ user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, --REFERS_TO
+ moderator_id INT REFERENCES moderator (id) ON DELETE CASCADE NOT NULL, --BLACKLISTED_BY
+ start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP,
reason TEXT,
PRIMARY KEY (user_id, moderator_id, topic_id, start_date)
);
-
CREATE TABLE developer_associated_with_project
(
@@ -152,5 +148,13 @@
PRIMARY KEY (project_id, developer_id, started_at)
);
----DO TUKA
+CREATE TABLE channel
+(
+ id uuid primary key default uuid_generate_v4(),
+ 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)
+);
CREATE TABLE permissions
(
@@ -158,8 +162,4 @@
);
-CREATE TABLE project_resource
-(
- id serial primary key
-);
create table project_role
@@ -167,5 +167,5 @@
id serial PRIMARY KEY ,
name varchar(32) NOT NULL,
- project_id int references project_thread (id) ON DELETE CASCADE,
+ project_id int references project_thread (id) ON DELETE CASCADE NOT NULL,
override_type varchar(20) check ( override_type in ('INCLUDE','EXCLUDE')) NOT NULL DEFAULT 'EXCLUDE'
);
@@ -173,5 +173,5 @@
CREATE TABLE role_permissions
(
- permission_name VARCHAR(32),
+ 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),
@@ -179,20 +179,17 @@
);
--- ova sa exceptions, primer ako vo role permissions imat entry ("READ","GUEST",5),
--- a vo roles_permissions overrides imat ("READ","GUEST",5,3) kade 3 da recime deka e Channel3
--- togas role GUEST mozit da citat vo site kanali osven Channel3
CREATE TABLE role_permissions_overrides
(
- permission_name VARCHAR(32),
+ 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,
- project_resource_id int references project_resource(id) NOT NULL,
FOREIGN KEY (role_id,permission_name) REFERENCES role_permissions (role_id,permission_name) ON DELETE CASCADE,
- PRIMARY KEY (role_id,permission_name,project_resource_id)
+ PRIMARY KEY (role_id,permission_name,channel_id)
);
CREATE TABLE users_project_roles
(
- user_id INT REFERENCES developer (id) on delete cascade,
- role_id INT REFERENCES project_role(id) on delete cascade,
+ user_id INT REFERENCES developer (id) on delete cascade NOT NULL,
+ role_id INT REFERENCES project_role(id) on delete cascade NOT NULL,
PRIMARY KEY (user_id, role_id)
);
@@ -202,8 +199,8 @@
(
id serial primary key,
- created_at TIMESTAMP default now() not null,
- description VARCHAR(200) NOT NULL,
+ created_at TIMESTAMP default now() NOT NULL ,
+ 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
+ created_by int REFERENCES users (id) NOT NULL
);
@@ -217,5 +214,5 @@
(
description TEXT,
- submission_type varchar(1) CHECK (submission_type IN ('P', 'R')),
+ 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
@@ -226,23 +223,15 @@
(
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
-);
-CREATE TABLE channel
-(
- name VARCHAR(64),
- description VARCHAR(200),
- project_id INT REFERENCES project_thread (id) ON DELETE CASCADE NOT NULL, --HAS
- project_resource_id INT REFERENCES project_resource (id) UNIQUE NOT NULL,
- developer_id INT REFERENCES developer (id) NOT NULL, --CONSTRUCTS
- PRIMARY KEY (name, project_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
+);
+
CREATE TABLE messages
(
- sent_at TIMESTAMP,
+ sent_at TIMESTAMP NOT NULL,
content VARCHAR(200) NOT NULL,
- sent_by INT REFERENCES developer (id),
- project_id INT,
- channel_name VARCHAR(64),
+ sent_by INT REFERENCES developer (id) NOT NULL,
+ project_id INT NOT NULL,
+ channel_name VARCHAR(64) NOT NULL,
FOREIGN KEY (channel_name, project_id)
REFERENCES channel (name, project_id) ON DELETE CASCADE,
@@ -251,72 +240,2 @@
-------------------------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_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_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, t.created_at as "created_at"
-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;
-
-
-CREATE OR REPLACE VIEW role_channel_permissions AS
-SELECT
- c.project_resource_id,
- c.name,
- pr.id as role_id,
- COALESCE(
- STRING_AGG(
- DISTINCT rp.permission_name, ',' ORDER BY rp.permission_name
- ) FILTER (
- WHERE
- (pr.override_type = 'INCLUDE' AND rpo.project_resource_id IS NOT NULL)
- OR
- (pr.override_type = 'EXCLUDE' AND rpo.project_resource_id IS NULL)
- ),
- ''
- ) AS permissions
-FROM channel c
- JOIN project_role pr
- ON pr.project_id = c.project_id
- LEFT JOIN role_permissions rp
- ON rp.role_id = pr.id
- AND rp.permission_name IN ('READ','WRITE')
- LEFT JOIN role_permissions_overrides rpo
- ON rpo.role_id = pr.id
- AND rpo.permission_name = rp.permission_name
- AND rpo.project_resource_id = c.project_resource_id
-GROUP BY c.project_resource_id, c.name,pr.id
-
-
Index: c/main/resources/db/migration/V2__triggers_ddl.sql
===================================================================
--- src/main/resources/db/migration/V2__triggers_ddl.sql (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ (revision )
@@ -1,283 +1,0 @@
-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 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 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
-$$;
-
-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 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
- 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 $$
-DECLARE
- developer_id INT;
- project_resource_id INT;
-BEGIN
- select user_id
- into developer_id
- from thread t
- where t.id=NEW.id;
- insert into project_resource default values returning id into project_resource_id;
-
- insert into channel(name,description,project_id,developer_id,project_resource_id)
- values ('General','General',NEW.id,developer_id,project_resource_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;
-$$;
-
-
-create or replace function fn_add_project_resource()
- returns trigger
- language plpgsql
-as $$
-DECLARE
- project_resource_id INT;
-BEGIN
- insert into project_resource default values returning id into project_resource_id;
- new.project_resource_id := project_resource_id;
- return new;
-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 TRIGGER tr_check_topic_name --RADI
- 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
- AFTER INSERT
- ON topic_thread
- FOR EACH ROW
-EXECUTE FUNCTION fn_insert_topics_creator_as_moderator();
-
-CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator --RADI
- AFTER DELETE
- ON topic_threads_moderators
- FOR EACH ROW
-EXECUTE FUNCTION fn_remove_orphan_moderator();
-
-CREATE OR REPLACE TRIGGER tr_a_insert_project_manager --RADI
- AFTER INSERT
- ON project_thread
- FOR EACH ROW
-EXECUTE FUNCTION fn_insert_project_manager();
-
-create or replace trigger tr_remove_unused_tags --RADI
- after delete
- on tag_threads
- for each row
-execute function fn_remove_unused_tags();
-
-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_project_resource_channel
- before insert
- on channel
- for each row
-execute function fn_add_project_resource();
-
-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
- AFTER INSERT
- ON blacklisted_user
- FOR EACH ROW
-EXECUTE FUNCTION fn_change_status_on_pending_reports();
Index: src/main/resources/db/migration/V2__views.sql
===================================================================
--- src/main/resources/db/migration/V2__views.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
+++ src/main/resources/db/migration/V2__views.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -0,0 +1,67 @@
+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_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_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, t.created_at as "created_at"
+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;
+
+
+CREATE OR REPLACE VIEW role_channel_permissions AS
+SELECT
+ c.id as channel_id,
+ c.name,
+ pr.id as role_id,
+ COALESCE(
+ STRING_AGG(
+ DISTINCT rp.permission_name, ',' ORDER BY rp.permission_name
+ ) FILTER (
+ WHERE
+ (pr.override_type = 'INCLUDE' AND rpo.channel_id IS NOT NULL)
+ OR
+ (pr.override_type = 'EXCLUDE' AND rpo.channel_id IS NULL)
+ ),
+ ''
+ ) AS permissions
+FROM channel c
+ JOIN project_role pr
+ ON pr.project_id = c.project_id
+ LEFT JOIN role_permissions rp
+ ON rp.role_id = pr.id
+ AND rp.permission_name 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
+GROUP BY c.id, c.name,pr.id
Index: c/main/resources/db/migration/V3__add_test_data.sql
===================================================================
--- src/main/resources/db/migration/V3__add_test_data.sql (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ (revision )
@@ -1,236 +1,0 @@
-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), --1
- ('Main content for topic thread 2', 2), --2
- ('Discussion content for topic 1', 1), --3
- ('Discussion content for topic 2', 2), --4
- ('Project-specific thread content', 3), --5
- ('Reply to topic 1', 4), -- 6
- ('Further discussion on topic 2', 5), --7
- ('Main content for topic thread', 1), --8
- ('Project-specific thread content 2', 5); --9
-
-insert into embeddable_thread(id)
-values (1),(2),(3),(4),(6),(7),(8);
-
-
-INSERT INTO project_thread (id, title, repo_url)
-VALUES
- (5, 'Project 1 Thread', 'http://github.com/project1'),
- (9, 'Project 2 Thread', 'http://github.com/project1');
-
-INSERT INTO topic_thread (id, title, parent_id)
-VALUES
- (1, 'Topic 1' , 5),
- (2, 'Topic 2', NULL),
- (8, 'Topic 7' , 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, 1),
- (7, 4);
-
-
-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, 2, NOW(), NOW() + INTERVAL '3 days', 'Offensive language');
-
-INSERT INTO permissions (name)
-VALUES
- ('READ'),
- ('WRITE'),
- ('CREATE'),
- ('DELETE');
-
-INSERT INTO project_role (name, project_id,override_type)
-VALUES
- ('Admin', 5,'EXCLUDE'),
- ('Developer', 5,'INCLUDE');
-
-
-INSERT INTO users_project_roles (user_id, role_id)
-VALUES
- (3, 1),
- (5, 2);
-
-INSERT INTO role_permissions (permission_name, role_id)
-VALUES
- ('READ', 1),
- ('WRITE', 1),
- ('CREATE',1),
- ('DELETE',1);
-
-insert into submission(created_by,status,description)
-values
- (1,'PENDING','Inappropriate content'),
- (3,'DENIED','Spam content');
-
-INSERT INTO report (id,thread_id, for_user_id)
-VALUES
- (1, 2, 1),
- (2, 1, 3);
-
-
-INSERT INTO channel (name, description, project_id, developer_id)
-VALUES
- ('Updates', 'Project updates channel', 5, 3);
-
-INSERT INTO messages (sent_at, content, sent_by, project_id, channel_name)
-VALUES
- (NOW(), 'Zdravo. Ova e real-time chat za dopisuvanje', 3, 5, 'General'),
- (NOW(), 'Resen ladno a?', 3, 5, 'Updates');
-
----------------- NOV TEST DATA
-
--- Add new users
-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
-INSERT INTO developer (id)
-VALUES
- (6), -- user6
- (7), -- user7
- (8), -- user8
- (9), -- user9
- (10); -- user10
-
--- Add new threads
-INSERT INTO thread (content, user_id)
-VALUES
- ('Main content for topic thread 3', 6), --10
- ('Main content for topic thread 4', 7), --11
- ('Discussion content for topic 3', 6), --12
- ('Discussion content for topic 4', 7), --13
- ('Project-specific thread content 3', 8), --14
- ('Reply to topic 3', 9), --15
- ('Further discussion on topic 4', 10), --16
- ('Main content for topic thread 5', 6), --17
- ('Project-specific thread content 4', 10); --18
-
--- Add embeddable_thread entries for topic and discussion threads
-INSERT INTO embeddable_thread (id)
-VALUES
- (10), (11), (12), (13), (15), (16), (17);
-
--- Add new project_threads
-INSERT INTO project_thread (id, title, repo_url)
-VALUES
- (14, 'Project 3 Thread', 'http://github.com/project3'),
- (18, 'Project 4 Thread', 'http://github.com/project4');
-
--- Add new topic_threads
-INSERT INTO topic_thread (id, title, parent_id)
-VALUES
- (10, 'Topic 3', 14),
- (11, 'Topic 4', NULL),
- (17, 'Topic 8', NULL);
-
--- Add new topic_guidelines
-INSERT INTO topic_guidelines (topic_id, description)
-VALUES
- (10, 'Stay on topic'),
- (11, 'No personal attacks');
-
--- Add new discussion_threads
-INSERT INTO discussion_thread (id, parent_id)
-VALUES
- (12, 10),
- (13, 11),
- (15, 10),
- (16, 13);
-
--- Add new likes
-INSERT INTO likes (user_id, thread_id)
-VALUES
- (6, 12),
- (7, 13),
- (8, 14),
- (9, 15),
- (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');
-
--- Add new submissions
-INSERT INTO submission (created_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)
-VALUES
- (3, 10, 7),
- (4, 11, 8);
-
--- Associate developers with projects
-INSERT INTO developer_associated_with_project (project_id, developer_id, 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
-
--- Add new channels
-INSERT INTO channel (name, description, project_id, developer_id)
-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
-
--- Add new messages (only users associated with the project can send messages)
-INSERT INTO messages (sent_at, content, sent_by, project_id, channel_name)
-VALUES
- (NOW(), 'Welcome to Project 3!', 6, 14, 'General'), -- Sent by user6 in Project 3
- (NOW(), 'Found a bug in the login module.', 7, 14, 'Bugs'), -- Sent by user7 in Project 3
- (NOW(), 'Let’s discuss new features.', 8, 18, 'General'), -- Sent by user8 in Project 4
- (NOW(), 'I have an idea for a new feature.', 9, 18, 'Ideas'); -- Sent by user9 in Project 4
-
-
-
-
-
-
-
-
Index: src/main/resources/db/migration/V3__triggers_ddl.sql
===================================================================
--- src/main/resources/db/migration/V3__triggers_ddl.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
+++ src/main/resources/db/migration/V3__triggers_ddl.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -0,0 +1,277 @@
+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 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 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
+$$;
+
+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 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
+ 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 $$
+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;
+$$;
+
+
+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;
+$$;
+
+
+
+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;
+ RETURN OLD;
+END;
+$$;
+
+------------------------------------------------------------
+
+CREATE OR REPLACE TRIGGER tr_check_topic_name --RADI
+ 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
+ AFTER INSERT
+ ON topic_thread
+ FOR EACH ROW
+EXECUTE FUNCTION fn_insert_topics_creator_as_moderator();
+
+CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator --RADI
+ AFTER DELETE
+ ON topic_threads_moderators
+ FOR EACH ROW
+EXECUTE FUNCTION fn_remove_orphan_moderator();
+
+CREATE OR REPLACE TRIGGER tr_a_insert_project_manager --RADI
+ AFTER INSERT
+ ON project_thread
+ FOR EACH ROW
+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_change_status_on_pending_reports
+ AFTER INSERT
+ ON blacklisted_user
+ FOR EACH ROW
+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();
Index: src/main/resources/db/migration/V5__add_test_data.sql
===================================================================
--- src/main/resources/db/migration/V5__add_test_data.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
+++ src/main/resources/db/migration/V5__add_test_data.sql (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -0,0 +1,236 @@
+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), --1
+ ('Main content for topic thread 2', 2), --2
+ ('Discussion content for topic 1', 1), --3
+ ('Discussion content for topic 2', 2), --4
+ ('Project-specific thread content', 3), --5
+ ('Reply to topic 1', 4), -- 6
+ ('Further discussion on topic 2', 5), --7
+ ('Main content for topic thread', 1), --8
+ ('Project-specific thread content 2', 5); --9
+
+insert into embeddable_thread(id)
+values (1),(2),(3),(4),(6),(7),(8);
+
+
+INSERT INTO project_thread (id, title, repo_url)
+VALUES
+ (5, 'Project 1 Thread', 'http://github.com/project1'),
+ (9, 'Project 2 Thread', 'http://github.com/project1');
+
+INSERT INTO topic_thread (id, title, parent_id)
+VALUES
+ (1, 'Topic 1' , 5),
+ (2, 'Topic 2', NULL),
+ (8, 'Topic 7' , 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, 1),
+ (7, 4);
+
+
+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, 2, NOW(), NOW() + INTERVAL '3 days', 'Offensive language');
+
+INSERT INTO permissions (name)
+VALUES
+ ('READ'),
+ ('WRITE'),
+ ('CREATE'),
+ ('DELETE');
+
+INSERT INTO project_role (name, project_id,override_type)
+VALUES
+ ('Admin', 5,'EXCLUDE'),
+ ('Developer', 5,'INCLUDE');
+
+
+INSERT INTO users_project_roles (user_id, role_id)
+VALUES
+ (3, 1),
+ (5, 2);
+
+INSERT INTO role_permissions (permission_name, role_id)
+VALUES
+ ('READ', 1),
+ ('WRITE', 1),
+ ('CREATE',1),
+ ('DELETE',1);
+
+insert into submission(created_by,status,description)
+values
+ (1,'PENDING','Inappropriate content'),
+ (3,'DENIED','Spam content');
+
+INSERT INTO report (id,thread_id, for_user_id)
+VALUES
+ (1, 2, 1),
+ (2, 1, 3);
+
+
+INSERT INTO channel (name, description, project_id, developer_id)
+VALUES
+ ('Updates', 'Project updates channel', 5, 3);
+
+INSERT INTO messages (sent_at, content, sent_by, project_id, channel_name)
+VALUES
+ (NOW(), 'Zdravo. Ova e real-time chat za dopisuvanje', 3, 5, 'General'),
+ (NOW(), 'Resen ladno a?', 3, 5, 'Updates');
+
+---------------- NOV TEST DATA
+
+-- Add new users
+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
+INSERT INTO developer (id)
+VALUES
+ (6), -- user6
+ (7), -- user7
+ (8), -- user8
+ (9), -- user9
+ (10); -- user10
+
+-- Add new threads
+INSERT INTO thread (content, user_id)
+VALUES
+ ('Main content for topic thread 3', 6), --10
+ ('Main content for topic thread 4', 7), --11
+ ('Discussion content for topic 3', 6), --12
+ ('Discussion content for topic 4', 7), --13
+ ('Project-specific thread content 3', 8), --14
+ ('Reply to topic 3', 9), --15
+ ('Further discussion on topic 4', 10), --16
+ ('Main content for topic thread 5', 6), --17
+ ('Project-specific thread content 4', 10); --18
+
+-- Add embeddable_thread entries for topic and discussion threads
+INSERT INTO embeddable_thread (id)
+VALUES
+ (10), (11), (12), (13), (15), (16), (17);
+
+-- Add new project_threads
+INSERT INTO project_thread (id, title, repo_url)
+VALUES
+ (14, 'Project 3 Thread', 'http://github.com/project3'),
+ (18, 'Project 4 Thread', 'http://github.com/project4');
+
+-- Add new topic_threads
+INSERT INTO topic_thread (id, title, parent_id)
+VALUES
+ (10, 'Topic 3', 14),
+ (11, 'Topic 4', NULL),
+ (17, 'Topic 8', NULL);
+
+-- Add new topic_guidelines
+INSERT INTO topic_guidelines (topic_id, description)
+VALUES
+ (10, 'Stay on topic'),
+ (11, 'No personal attacks');
+
+-- Add new discussion_threads
+INSERT INTO discussion_thread (id, parent_id)
+VALUES
+ (12, 10),
+ (13, 11),
+ (15, 10),
+ (16, 13);
+
+-- Add new likes
+INSERT INTO likes (user_id, thread_id)
+VALUES
+ (6, 12),
+ (7, 13),
+ (8, 14),
+ (9, 15),
+ (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');
+
+-- Add new submissions
+INSERT INTO submission (created_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)
+VALUES
+ (3, 10, 7),
+ (4, 11, 8);
+
+-- Associate developers with projects
+INSERT INTO developer_associated_with_project (project_id, developer_id, 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
+
+-- Add new channels
+INSERT INTO channel (name, description, project_id, developer_id)
+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
+
+-- Add new messages (only users associated with the project can send messages)
+INSERT INTO messages (sent_at, content, sent_by, project_id, channel_name)
+VALUES
+ (NOW(), 'Welcome to Project 3!', 6, 14, 'General'), -- Sent by user6 in Project 3
+ (NOW(), 'Found a bug in the login module.', 7, 14, 'Bugs'), -- Sent by user7 in Project 3
+ (NOW(), 'Let’s discuss new features.', 8, 18, 'General'), -- Sent by user8 in Project 4
+ (NOW(), 'I have an idea for a new feature.', 9, 18, 'Ideas'); -- Sent by user9 in Project 4
+
+
+
+
+
+
+
+
Index: src/main/resources/templates/project_pages/edit-role.html
===================================================================
--- src/main/resources/templates/project_pages/edit-role.html (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/resources/templates/project_pages/edit-role.html (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -74,12 +74,12 @@
+ th:for="${'read-' + rcp.getChannelId()}">READ
@@ -87,12 +87,12 @@
+ th:for="${'write-' + rcp.getChannelId()}">WRITE
@@ -149,15 +149,5 @@
}
- /*
- * if (!projectAccessManagementService.hasPermissionToAccessResource(user.getId(),
- Permission.READ,
- c.getProjectResource().getId(),
- project.getId()
- )){
- System.out.println("vleze deny access");
- model.addAttribute("error","You dont have permission to access this channel");
- return "redirect:/projects/" + project.getId();
- }
- * */
+
document.querySelector("#btn-cancel").addEventListener("click", ev => {
ev.preventDefault()
@@ -195,5 +185,5 @@
permissionResourceDTOS: perResourceSelectedPermissions.map(p => ({
permissionName: p.name,
- projectResourceID: p.resourceId
+ channelId: p.resourceId
}))
}),
Index: src/main/resources/templates/project_pages/project-roles.html
===================================================================
--- src/main/resources/templates/project_pages/project-roles.html (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/resources/templates/project_pages/project-roles.html (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -205,11 +205,11 @@
@@ -288,5 +288,5 @@
permissionResourceDTOS: perResourceSelectedPermissions.map(p => ({
permissionName: p.name,
- projectResourceID: p.resourceId
+ channelId: p.resourceId
}))
}),
Index: src/main/resources/templates/project_pages/show-project.html
===================================================================
--- src/main/resources/templates/project_pages/show-project.html (revision 2126c4548c1fa11b3b61cdf3232f0be44bc98ed9)
+++ src/main/resources/templates/project_pages/show-project.html (revision 2626dbcc27e66a04666be61151a9c4d7f5eb4249)
@@ -126,5 +126,5 @@
-