Напредни извештаи од базата (SQL)

Целосен приказ на дискусии, заедно со топикот на кој припаѓаат и нивната длабочина.

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 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 TRIGGER tr_check_topic_name
    BEFORE INSERT OR UPDATE
    ON topic_thread
    FOR EACH ROW
EXECUTE FUNCTION fn_validate_topic_title();

Тригер за отстранување на неискористени тагови

create or replace function fn_remove_unused_tags()
    returns trigger
    language plpgsql
as
$$
BEGIN
    IF not check_if_user_exists_in('tag_threads', 'tag_name', old.tag_name)
    THEN
        raise notice 'kakosi';
        delete from tag t where t.name = old.tag_name;
    end if;
    return old;
end;
$$;

create or replace trigger tr_remove_unused_tags
    after delete
    on tag_threads
    for each row
execute function fn_remove_unused_tags();

Проверка дали даден корисник поседува дадена пермисија за даден ресурс

SELECT COALESCE(
                  EXISTS (SELECT 1
                          FROM users_project_roles upr
                                   JOIN project_role pr
                               ON upr.role_id = pr.id
                                   JOIN role_permissions rp
                                    ON pr.id = rp.role_id
                                   LEFT JOIN role_permissions_overrides rpo
                                             ON pr.id = rpo.role_id
                                                 AND rpo.permission_name = rp.permission_name
                                                 AND rpo.project_resource_id = :resourceId
   
                          WHERE upr.user_id = :userId
                            AND pr.project_id = :projectId
                            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)
                              )), FALSE
          ) AS has_access;