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
} {