Index: openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql,v
diff -u -r1.7 -r1.8
--- openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql	12 Aug 2013 09:34:30 -0000	1.7
+++ openacs-4/packages/forums/sql/postgresql/forums-sc-create.sql	3 Sep 2024 15:37:38 -0000	1.8
@@ -9,11 +9,29 @@
 -- change to the thread.
 
 CREATE OR REPLACE FUNCTION forums_message_search__itrg () RETURNS trigger AS $$
+DECLARE
+    v_root_message_id forums_messages.message_id%TYPE;
+    v_is_approved     boolean;
 BEGIN
-    if new.parent_id is null then
+    if new.parent_id is null and new.state = 'approved' then
+        -- New threads are indexed only if they are approved.
         perform search_observer__enqueue(new.message_id,'INSERT');
     else
-        perform search_observer__enqueue(forums_message__root_message_id(new.parent_id),'UPDATE');
+        -- Non-root messages trigger the indexing of the whole thread,
+        -- but only if the thread (the root message) has been
+        -- approved.
+        -- We do not care about the approval of the message itself in
+        -- this case, as the datasource callback will take care of not
+        -- rendering any unapproved non-root message.
+        v_root_message_id := forums_message__root_message_id(new.parent_id);
+
+        select state = 'approved' into v_is_approved
+          from forums_messages
+         where message_id = v_root_message_id;
+
+        if v_is_approved then
+            perform search_observer__enqueue(v_root_message_id,'UPDATE');
+        end if;
     end if;
     return new;
 END;
@@ -28,15 +46,27 @@
 
 ) RETURNS trigger AS $$
 DECLARE
-     v_root_message_id          forums_messages.message_id%TYPE;
+    v_root_message_id forums_messages.message_id%TYPE;
+    v_is_approved     boolean;
 BEGIN
     -- if the deleted msg has a parent then its an UPDATE to a thread, otherwise a DELETE.
 
     if old.parent_id is null then
         perform search_observer__enqueue(old.message_id,'DELETE');
     else
-        v_root_message_id := forums_message__root_message_id(old.parent_id);
-        if not v_root_message_id is null then
+        -- Deleting non-root messages triggers the indexing of the
+        -- whole thread, but only if the thread (the root message) has
+        -- been approved.
+        -- We do not care about the approval of the message itself in
+        -- this case, as the datasource callback will take care of not
+        -- rendering any unapproved non-root message.
+        v_root_message_id := forums_message__root_message_id(new.parent_id);
+
+        select state = 'approved' into v_is_approved
+          from forums_messages
+         where message_id = v_root_message_id;
+
+        if v_is_approved then
             perform search_observer__enqueue(v_root_message_id,'UPDATE');
         end if;
     end if;
@@ -46,8 +76,31 @@
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION forums_message_search__utrg () RETURNS trigger AS $$
+DECLARE
+    v_root_message_id forums_messages.message_id%TYPE;
+    v_is_approved     boolean;
 BEGIN
-    perform search_observer__enqueue(forums_message__root_message_id (old.message_id),'UPDATE');
+    if old.parent_id is null and new.state <> 'approved' then
+         -- New threads that have been revoked approval should be
+         -- removed from the search results.
+         perform search_observer__enqueue(old.message_id,'DELETE');
+    else
+        -- Non-root messages trigger the indexing of the whole thread,
+        -- but only if the thread (the root message) has been
+        -- approved.
+        -- We do not care about the approval of the message itself in
+        -- this case, as the datasource callback will take care of not
+        -- rendering any unapproved non-root message.
+        v_root_message_id := forums_message__root_message_id(new.parent_id);
+
+        select state = 'approved' into v_is_approved
+          from forums_messages
+         where message_id = v_root_message_id;
+
+        if v_is_approved then
+            perform search_observer__enqueue(v_root_message_id,'UPDATE');
+        end if;
+    end if;
     return old;
 END;
 $$ LANGUAGE plpgsql;