Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/29/25 18:58:07 (9 months ago)

Author:

226026

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (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();
+}}} 
+