Index: openacs-4/packages/forums/forums.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/forums.info,v diff -u -N -r1.56.2.12 -r1.56.2.13 --- openacs-4/packages/forums/forums.info 6 Oct 2020 08:23:19 -0000 1.56.2.12 +++ openacs-4/packages/forums/forums.info 29 Apr 2021 14:03:42 -0000 1.56.2.13 @@ -9,7 +9,7 @@ f t - + OpenACS Online discussion forums. 2017-08-06 @@ -19,7 +19,7 @@ 2 #forums.Forums# - + 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 -N -r1.7 -r1.7.6.1 --- 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 29 Apr 2021 14:03:42 -0000 1.7.6.1 @@ -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; Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.4.0d3-1.4.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/Attic/upgrade-1.4.0d3-1.4.0d4.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.4.0d3-1.4.0d4.sql 29 Apr 2021 14:03:42 -0000 1.1.2.1 @@ -0,0 +1,124 @@ +begin; + +-- Account for moderation in the search package triggers: make sure we +-- do not index unapproved threads and messages. + +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 and new.state = 'approved' then + -- New threads are indexed only if they are approved. + perform search_observer__enqueue(new.message_id,'INSERT'); + 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 new; +END; +$$ LANGUAGE plpgsql; + + + +-- +-- procedure forums_message_search__dtrg/0 +-- +CREATE OR REPLACE FUNCTION forums_message_search__dtrg( + +) RETURNS trigger AS $$ +DECLARE + 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 + -- 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; + + return old; +END; +$$ 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 + 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; + +-- Schedule unindexing of all unapproved threads +select search_observer__enqueue(message_id,'DELETE') +from forums_messages +where parent_id is null + and state <> 'approved'; + +-- Schedule the reindexing of all threads that are themselves +-- approved, but contain unapproved messages. The datasource callback +-- will take care of not rendering the unapproved messages. +select search_observer__enqueue(message_id,'UPDATE') +from ( +select distinct thread.message_id + from forums_messages thread, + forums_messages messages + where thread.forum_id = messages.forum_id + and thread.parent_id is null + and thread.state = 'approved' + and thread.tree_sortkey = tree_ancestor_key(messages.tree_sortkey, 1) + and messages.parent_id is not null + and messages.state <> 'approved') as threads_with_unapproved_messages; + +end; Index: openacs-4/packages/forums/tcl/forums-callback-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/tcl/forums-callback-procs.tcl,v diff -u -N -r1.12.2.6 -r1.12.2.7 --- openacs-4/packages/forums/tcl/forums-callback-procs.tcl 1 Feb 2021 11:01:48 -0000 1.12.2.6 +++ openacs-4/packages/forums/tcl/forums-callback-procs.tcl 29 Apr 2021 14:03:42 -0000 1.12.2.7 @@ -170,18 +170,21 @@ array set forum [forum::get -forum_id $message(forum_id) -array forum] set package_id $forum(package_id) + # We only render the content of approved messages. db_foreach messages { with recursive thread(message_id, parent_id, subject, content, format) as ( select message_id, parent_id, subject, content, format from forums_messages where message_id = :message_id + and m.state = 'approved' union all select m.message_id, m.parent_id, m.subject, m.content, m.format from forums_messages m, thread t where m.parent_id = t.message_id + and m.state = 'approved' ) select subject, content, format from thread } {