| 36 | | === Поглед за прикажување на пермисиите за дадена улога во за сите канали во даден проект. |
| 37 | | {{{ |
| 38 | | CREATE OR REPLACE VIEW role_channel_permissions AS |
| 39 | | SELECT |
| 40 | | c.project_resource_id, |
| 41 | | c.name, |
| 42 | | pr.id as role_id, |
| 43 | | COALESCE( |
| 44 | | STRING_AGG( |
| 45 | | DISTINCT rp.permission_name, ',' ORDER BY rp.permission_name |
| 46 | | ) FILTER ( |
| 47 | | WHERE |
| 48 | | (pr.override_type = 'INCLUDE' AND rpo.project_resource_id IS NOT NULL) |
| 49 | | OR |
| 50 | | (pr.override_type = 'EXCLUDE' AND rpo.project_resource_id IS NULL) |
| 51 | | ), |
| 52 | | '' |
| 53 | | ) AS permissions |
| 54 | | FROM channel c |
| 55 | | JOIN project_role pr |
| 56 | | ON pr.project_id = c.project_id |
| 57 | | LEFT JOIN role_permissions rp |
| 58 | | ON rp.role_id = pr.id |
| 59 | | AND rp.permission_name IN ('READ','WRITE') |
| 60 | | LEFT JOIN role_permissions_overrides rpo |
| 61 | | ON rpo.role_id = pr.id |
| 62 | | AND rpo.permission_name = rp.permission_name |
| 63 | | AND rpo.project_resource_id = c.project_resource_id |
| 64 | | GROUP BY c.project_resource_id, c.name,pr.id |
| 65 | | }}} |
| 66 | | |
| 67 | | == Транзакции |
| 68 | | === Прифаќање на пријава и зачувување на приложениот feedback |
| 69 | | {{{ |
| 70 | | @Transactional |
| 71 | | public void accept(BBUser creator, Integer reqId) { |
| 72 | | ProjectRequests prReq = getRequestById(reqId); |
| 73 | | prReq.setStatus(Status.ACCEPTED); |
| 74 | | |
| 75 | | feedbackService.create(creator,FeedbackFor.P,prReq); |
| 76 | | projectService.addDeveloperToProject(prReq.getProject(), prReq.getCreator()); |
| 77 | | prReqRepo.save(prReq); |
| 78 | | } |
| 79 | | }}} |
| 80 | | |
| 81 | | === Одбивање на пријава и зачувување на прилоѓениот feedback |
| 82 | | {{{ |
| 83 | | @Transactional |
| 84 | | public void deny(Integer reqId, String desc, BBUser creator) { |
| 85 | | ProjectRequests prReq = getRequestById(reqId); |
| 86 | | prReq.setStatus(Status.DENIED); |
| 87 | | feedbackService.create(desc,creator,FeedbackFor.P,prReq); |
| 88 | | prReqRepo.save(prReq); |
| 89 | | } |
| 90 | | }}} |
| 91 | | |
| 92 | | === Ажурирање на постоечка улога |
| 93 | | {{{ |
| 94 | | @Transactional |
| 95 | | public void updateRole(Integer id, AddRoleDTOEntities addRoleDTO) { |
| 96 | | |
| 97 | | ProjectRole existingRole = projectRoleRepository.findById(id).orElseThrow(() -> new IllegalArgumentException("bad role id")); |
| 98 | | projectRolePermissionRepository.deleteAllByIdRole(existingRole); |
| 99 | | |
| 100 | | existingRole.setName(addRoleDTO.getName()); |
| 101 | | existingRole.setOverrideType(addRoleDTO.getProjectResourcePermissionOverrideType().toString()); |
| 102 | | |
| 103 | | projectRoleRepository.save(existingRole); |
| 104 | | |
| 105 | | List<ProjectRolePermission> newGlobalPermissions = mapGlobalsToProjectRolePermissions(existingRole, addRoleDTO.getGlobalPermissions()); |
| 106 | | List<ProjectRolePermission> newPerResourcePermissions = mapPerResourceToProjectRolePermissions(existingRole,addRoleDTO.getPermissionsResourceWrappers()); |
| 107 | | List<ProjectRolePermissionResourceOverride> resourceOverrides = mapToResourceOverrides(getResources(addRoleDTO.getPermissionsResourceWrappers()),newPerResourcePermissions); |
| 108 | | |
| 109 | | |
| 110 | | projectRolePermissionRepository.saveAll(newGlobalPermissions); |
| 111 | | projectRolePermissionRepository.saveAll(newPerResourcePermissions); |
| 112 | | projectRolePermissionResourceOverrideRepository.saveAll(resourceOverrides); |
| 113 | | |
| 114 | | } |
| 115 | | |
| 116 | | }}} |
| 117 | | === Додавање на нова улога во проект |
| 118 | | {{{ |
| 119 | | @Transactional |
| 120 | | public void addRole(AddRoleDTOEntities addRoleDTO) { |
| 121 | | ProjectRole role = projectRoleRepository.save( |
| 122 | | new ProjectRole( |
| 123 | | addRoleDTO.getProject(), |
| 124 | | addRoleDTO.getName(), |
| 125 | | addRoleDTO.getProjectResourcePermissionOverrideType().toString() |
| 126 | | ) |
| 127 | | ); |
| 128 | | |
| 129 | | List<ProjectRolePermission> entities = mapPerResourceToProjectRolePermissions(role, addRoleDTO.getPermissionsResourceWrappers()); |
| 130 | | projectRolePermissionRepository.saveAll(mapGlobalsToProjectRolePermissions(role, addRoleDTO.getGlobalPermissions())); |
| 131 | | projectRolePermissionRepository.saveAll(entities); |
| 132 | | projectRolePermissionResourceOverrideRepository.saveAll(mapToResourceOverrides( |
| 133 | | getResources(addRoleDTO.getPermissionsResourceWrappers()), |
| 134 | | entities |
| 135 | | )); |
| 136 | | }}} |
| 137 | | {{{}}} |
| 138 | | |
| 139 | | |
| 140 | | |
| 141 | | |
| 142 | | |
| 143 | | |
| 144 | | |
| 145 | | == Тригери |
| 146 | | === Проверка дали даден топик имат уникатно име во парент threadот |
| 147 | | {{{ |
| 148 | | CREATE OR REPLACE FUNCTION fn_validate_topic_title() |
| 149 | | RETURNS TRIGGER |
| 150 | | LANGUAGE plpgsql |
| 151 | | AS |
| 152 | | $$ |
| 153 | | BEGIN |
| 154 | | IF new.title IN |
| 155 | | (SELECT title |
| 156 | | FROM topic_thread |
| 157 | | AS t |
| 158 | | WHERE t.parent_id = new.parent_id |
| 159 | | OR (t.parent_id IS NULL AND new.parent_id IS NULL) |
| 160 | | ) |
| 161 | | THEN |
| 162 | | RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id; |
| 163 | | END IF; |
| 164 | | RETURN new; |
| 165 | | END; |
| 166 | | $$; |
| 167 | | }}} |
| 168 | | {{{ |
| 169 | | CREATE OR REPLACE TRIGGER tr_check_topic_name --RADI |
| 170 | | BEFORE INSERT OR UPDATE |
| 171 | | ON topic_thread |
| 172 | | FOR EACH ROW |
| 173 | | EXECUTE FUNCTION fn_validate_topic_title(); |
| 174 | | }}} |
| 175 | | |
| 176 | | === При креирање на топик, креаторот се внесува како модератор |
| 177 | | {{{ |
| 178 | | CREATE OR REPLACE FUNCTION fn_insert_topics_creator_as_moderator() |
| 179 | | RETURNS TRIGGER |
| 180 | | LANGUAGE plpgsql |
| 181 | | AS |
| 182 | | $$ |
| 183 | | DECLARE |
| 184 | | v_user_id INT; |
| 185 | | BEGIN |
| 186 | | SELECT v_topic_thread.user_id |
| 187 | | INTO v_user_id |
| 188 | | FROM v_topic_thread |
| 189 | | WHERE v_topic_thread.id = new.id; |
| 190 | | IF not check_if_user_exists_in('moderator', 'id', v_user_id::text) THEN |
| 191 | | INSERT INTO moderator values (v_user_id); |
| 192 | | END IF; |
| 193 | | INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id); |
| 194 | | RETURN NEW; |
| 195 | | END |
| 196 | | $$; |
| 197 | | }}} |
| 198 | | {{{ |
| 199 | | CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator |
| 200 | | AFTER INSERT |
| 201 | | ON topic_thread |
| 202 | | FOR EACH ROW |
| 203 | | EXECUTE FUNCTION fn_insert_topics_creator_as_moderator(); |
| 204 | | }}} |
| 205 | | |
| 206 | | === При бришење на topic, проверка дали модераторот сеуште модерира topicци |
| 207 | | {{{ |
| 208 | | CREATE OR REPLACE FUNCTION fn_remove_orphan_moderator() |
| 209 | | RETURNS trigger |
| 210 | | LANGUAGE plpgsql |
| 211 | | AS $function$ |
| 212 | | BEGIN |
| 213 | | IF not exists ( |
| 214 | | select 1 |
| 215 | | from topic_threads_moderators t |
| 216 | | where t.user_id = OLD.user_id |
| 217 | | ) |
| 218 | | THEN |
| 219 | | DELETE FROM moderator where id=OLD.user_id; |
| 220 | | END IF; |
| 221 | | IF not exists ( |
| 222 | | select 1 |
| 223 | | from topic_threads_moderators t |
| 224 | | where t.thread_id = OLD.thread_id |
| 225 | | ) |
| 226 | | THEN |
| 227 | | delete from discussion_thread where parent_id=OLD.thread_id; |
| 228 | | DELETE FROM topic_thread where id = OLD.thread_id; |
| 229 | | -- delete from thread where id = OLD.thread_id; |
| 230 | | END IF; |
| 231 | | RETURN OLD; |
| 232 | | END; |
| 233 | | }}} |
| 234 | | |
| 235 | | {{{ |
| 236 | | CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator |
| 237 | | AFTER DELETE |
| 238 | | ON topic_threads_moderators |
| 239 | | FOR EACH ROW |
| 240 | | EXECUTE FUNCTION fn_remove_orphan_moderator(); |
| 241 | | }}} |
| 242 | | |
| 243 | | === При креирање на проект, креаторот се внесува како PROJECT_OWNER |
| 244 | | {{{ |
| 245 | | CREATE OR REPLACE FUNCTION fn_insert_project_manager() |
| 246 | | RETURNS TRIGGER |
| 247 | | LANGUAGE plpgsql |
| 248 | | AS |
| 249 | | $$ |
| 250 | | DECLARE |
| 251 | | usrId INT; |
| 252 | | new_project_id INT; |
| 253 | | BEGIN |
| 254 | | SELECT user_id, id |
| 255 | | into usrId,new_project_id |
| 256 | | FROM v_project_thread p |
| 257 | | WHERE NEW.id = p.id; |
| 258 | | IF not EXISTS( |
| 259 | | select 1 |
| 260 | | from developer_associated_with_project dawp |
| 261 | | where dawp.project_id=new_project_id and dawp.developer_id=usrId |
| 262 | | ) THEN |
| 263 | | INSERT INTO developer_associated_with_project(project_id, developer_id, started_at) |
| 264 | | values (new_project_id, usrId, NOW()); |
| 265 | | end if; |
| 266 | | IF not check_if_user_exists_in('project_manager', 'id', usrId::text) THEN |
| 267 | | INSERT INTO project_manager VALUES (usrId); |
| 268 | | end if; |
| 269 | | RETURN NEW; |
| 270 | | END |
| 271 | | $$; |
| 272 | | }}} |
| 273 | | |
| 274 | | {{{ |
| 275 | | CREATE OR REPLACE TRIGGER tr_a_insert_project_manager --RADI |
| 276 | | AFTER INSERT |
| 277 | | ON project_thread |
| 278 | | FOR EACH ROW |
| 279 | | EXECUTE FUNCTION fn_insert_project_manager(); |
| 280 | | }}} |
| 281 | | |
| 282 | | |
| 283 | | === Бришење на не користени тагови |
| 284 | | |
| 285 | | {{{ |
| 286 | | create or replace function fn_delete_dangling_tags() |
| 287 | | RETURNS trigger |
| 288 | | LANGUAGE plpgsql |
| 289 | | AS $$ |
| 290 | | BEGIN |
| 291 | | IF NOT EXISTS(select 1 |
| 292 | | from tag_threads |
| 293 | | where tag_name = OLD.tag_name |
| 294 | | group by tag_name) THEN |
| 295 | | delete from tag where name = OLD.tag_name; |
| 296 | | end if; |
| 297 | | RETURN OLD; |
| 298 | | END; |
| 299 | | $$; |
| 300 | | }}} |
| 301 | | |
| 302 | | {{{ |
| 303 | | create or replace trigger tr_delete_dangling_tags |
| 304 | | after delete |
| 305 | | on tag_threads |
| 306 | | for each row |
| 307 | | execute function fn_delete_dangling_tags(); |
| 308 | | }}} |
| 309 | | |
| 310 | | === Внесување на даден корисник како developer, откако ќе стани дел од даден проект |
| 311 | | {{{ |
| 312 | | create or replace function fn_add_dev_if_not_exist() |
| 313 | | returns trigger |
| 314 | | language plpgsql |
| 315 | | as $$ |
| 316 | | BEGIN |
| 317 | | IF NOT check_if_user_exists_in('developer','id',new.developer_id::text) THEN |
| 318 | | INSERT INTO developer values (NEW.developer_id); |
| 319 | | end if; |
| 320 | | RETURN new; |
| 321 | | end; |
| 322 | | $$; |
| 323 | | |
| 324 | | }}} |
| 325 | | |
| 326 | | {{{ |
| 327 | | create or replace trigger tr_add_dev_if_not_exist |
| 328 | | before insert on developer_associated_with_project |
| 329 | | for each row |
| 330 | | execute function fn_add_dev_if_not_exist(); |
| 331 | | }}} |
| 332 | | |
| 333 | | === Креирање на General канал, при креирање на даден проект |
| 334 | | {{{ |
| 335 | | create or replace function fn_insert_general_for_project() |
| 336 | | returns trigger |
| 337 | | language plpgsql |
| 338 | | as $$ |
| 339 | | DECLARE |
| 340 | | developer_id INT; |
| 341 | | project_resource_id INT; |
| 342 | | BEGIN |
| 343 | | select user_id |
| 344 | | into developer_id |
| 345 | | from thread t |
| 346 | | where t.id=NEW.id; |
| 347 | | insert into project_resource default values returning id into project_resource_id; |
| 348 | | |
| 349 | | insert into channel(name,description,project_id,developer_id,project_resource_id) |
| 350 | | values ('General','General',NEW.id,developer_id,project_resource_id); |
| 351 | | |
| 352 | | return new; |
| 353 | | end; |
| 354 | | $$; |
| 355 | | }}} |
| 356 | | |
| 357 | | {{{ |
| 358 | | create or replace trigger tr_insert_general_for_project |
| 359 | | after insert on project_thread |
| 360 | | for each row |
| 361 | | execute function fn_insert_general_for_project(); |
| 362 | | }}} |
| 363 | | |
| 364 | | === Сите 'pending' пријави за даден корисник стануваат 'accepted', при додавање на тој корисник во 'blacklist' листата. |
| 365 | | {{{ |
| 366 | | CREATE OR REPLACE FUNCTION fn_change_status_on_pending_reports() |
| 367 | | RETURNS TRIGGER |
| 368 | | LANGUAGE plpgsql |
| 369 | | AS $$ |
| 370 | | BEGIN |
| 371 | | RAISE NOTICE 'user_id: %, topic_id: %', NEW.user_id, NEW.topic_id; |
| 372 | | |
| 373 | | UPDATE submission |
| 374 | | SET status = 'ACCEPTED' |
| 375 | | WHERE id in ( |
| 376 | | select id |
| 377 | | from report r |
| 378 | | where r.for_user_id = NEW.user_id and r.thread_id = NEW.topic_id |
| 379 | | ); |
| 380 | | |
| 381 | | RETURN NEW; |
| 382 | | END; |
| 383 | | $$; |
| 384 | | }}} |
| 385 | | |
| 386 | | {{{ |
| 387 | | CREATE OR REPLACE TRIGGER tr_change_status_on_pending_reports |
| 388 | | AFTER INSERT |
| 389 | | ON blacklisted_user |
| 390 | | FOR EACH ROW |
| 391 | | EXECUTE FUNCTION fn_change_status_on_pending_reports(); |
| 392 | | }}} |
| 393 | | |
| 394 | | === Оневозможувено повторно додавање на корисник во 'blacklist' листата, доколку е веќе присутен на неа |
| 395 | | |
| 396 | | {{{ |
| 397 | | create or replace function fn_add_blacklisted_user() |
| 398 | | RETURNS trigger |
| 399 | | LANGUAGE plpgsql |
| 400 | | AS $$ |
| 401 | | BEGIN |
| 402 | | IF NOT EXISTS( |
| 403 | | select 1 |
| 404 | | from blacklisted_user |
| 405 | | where topic_id = NEW.topic_id and user_id = NEW.user_id and end_date is NULL |
| 406 | | ) |
| 407 | | THEN |
| 408 | | RETURN NEW; |
| 409 | | END IF; |
| 410 | | RETURN NULL; |
| 411 | | END; |
| 412 | | $$; |
| 413 | | }}} |
| 414 | | |
| 415 | | {{{ |
| 416 | | create or replace trigger tr_add_blacklisted_user |
| 417 | | before insert on blacklisted_user |
| 418 | | for each row |
| 419 | | execute function fn_add_blacklisted_user(); |
| 420 | | }}} |
| 421 | | |
| 422 | | === Автоматско додавање на project_resource, при додавање на канал |
| 423 | | {{{ |
| 424 | | create or replace function fn_add_project_resource() |
| 425 | | returns trigger |
| 426 | | language plpgsql |
| 427 | | as $$ |
| 428 | | DECLARE |
| 429 | | project_resource_id INT; |
| 430 | | BEGIN |
| 431 | | insert into project_resource default values returning id into project_resource_id; |
| 432 | | new.project_resource_id := project_resource_id; |
| 433 | | return new; |
| 434 | | end; |
| 435 | | $$; |
| 436 | | }}} |
| 437 | | |
| 438 | | {{{ |
| 439 | | create or replace trigger tr_add_project_resource_channel |
| 440 | | before insert |
| 441 | | on channel |
| 442 | | for each row |
| 443 | | execute function fn_add_project_resource(); |
| 444 | | }}} |
| 445 | | |
| 446 | | == Индекси |
| 447 | | |
| 448 | | == Процедури |
