Index: openacs-4/packages/forums/forums.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/forums.info,v
diff -u -N -r1.20 -r1.21
--- openacs-4/packages/forums/forums.info 3 Oct 2003 11:53:25 -0000 1.20
+++ openacs-4/packages/forums/forums.info 3 Oct 2003 19:38:15 -0000 1.21
@@ -7,13 +7,13 @@
f
f
-
+
Ben Adida
Online discussion boards with many configuration options.
2003-10-03
This online discussion board software is the successor of the original bboard package. The biggest improvement is the new scalable datamodel. This package supports threaded and flat view, moderation, a stand alone search function as well as integration with the OpenACS search package and integration with the notifications package for email alerts. It also supports automatic posting of incoming email.
-
+
Index: openacs-4/packages/forums/sql/oracle/forums-messages-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-messages-package-create.sql,v
diff -u -N -r1.5 -r1.6
--- openacs-4/packages/forums/sql/oracle/forums-messages-package-create.sql 30 Sep 2003 12:10:07 -0000 1.5
+++ openacs-4/packages/forums/sql/oracle/forums-messages-package-create.sql 3 Oct 2003 19:38:15 -0000 1.6
@@ -122,14 +122,14 @@
where message_id = v_message_id;
update forums_forums
- set last_post = posting_date
+ set last_post = forums_message.new.posting_date
where forum_id = forums_message.new.forum_id;
update forums_messages
- set last_child_post = posting_date
+ set last_child_post = forums_message.new.posting_date
where forum_id = forums_message.new.forum_id
and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
-
+
return v_message_id;
end new;
Index: openacs-4/packages/forums/sql/oracle/upgrade-0.1d-0.2d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/Attic/upgrade-0.1d-0.2d.sql,v
diff -u -N
--- openacs-4/packages/forums/sql/oracle/upgrade-0.1d-0.2d.sql 30 Nov 2002 17:34:49 -0000 1.2
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,328 +0,0 @@
-alter table forums_forums add (last_post date);
-alter table forums_messages add (last_child_post date);
-
-declare
- v_date date;
-begin
-
- for row in (select forum_id
- from forums_forums)
- loop
-
- select last_modified into v_date
- from acs_objects
- where object_id = row.forum_id;
-
- update forums_forums
- set last_post = v_date
- where forum_id = row.forum_id;
-
- end loop;
-
- for row in (select message_id
- from forums_messages
- where parent_id is null)
- loop
-
- -- forums 0.1d did not properly set the last_modified field of the object
- -- row associated with the root message of a thread, so we need to calculate
- -- it here.
-
- select max(o.last_modified) into v_date
- from acs_objects o, forums_messages fm
- where forums_message.root_message_id(fm.message_id) = row.message_id
- and object_id = fm.message_id;
-
- update forums_messages
- set last_child_post = v_date
- where message_id = row.message_id;
-
- end loop;
-
-end;
-/
-show errors;
-
-drop view forums_messages_approved;
-create view forums_messages_approved
-as
- select *
- from forums_messages
- where state = 'approved';
-
-drop view forums_messages_pending;
-create view forums_messages_pending
-as
- select *
- from forums_messages
- where state= 'pending';
-
-drop view forums_forums_enabled;
-create view forums_forums_enabled
-as
- select *
- from forums_forums
- where enabled_p = 't';
-
-create or replace package body forums_message
-as
-
- function new (
- message_id in forums_messages.message_id%TYPE default null,
- object_type in acs_objects.object_type%TYPE default 'forums_message',
- forum_id in forums_messages.forum_id%TYPE,
- subject in forums_messages.subject%TYPE,
- content in varchar,
- html_p in forums_messages.html_p%TYPE default 'f',
- user_id in forums_messages.user_id%TYPE,
- posting_date in forums_messages.posting_date%TYPE default sysdate,
- state in forums_messages.state%TYPE default null,
- parent_id in forums_messages.parent_id%TYPE default null,
- creation_date in acs_objects.creation_date%TYPE default sysdate,
- creation_user in acs_objects.creation_user%TYPE,
- creation_ip in acs_objects.creation_ip%TYPE,
- context_id in acs_objects.context_id%TYPE default null
- ) return forums_messages.message_id%TYPE
- is
- v_message_id acs_objects.object_id%TYPE;
- v_sortkey forums_messages.tree_sortkey%TYPE;
- v_forum_policy forums_forums.posting_policy%TYPE;
- v_state forums_messages.state%TYPE;
- begin
-
- v_message_id := acs_object.new(
- object_id => message_id,
- object_type => object_type,
- creation_date => creation_date,
- creation_user => creation_user,
- creation_ip => creation_ip,
- context_id => nvl(context_id, forum_id)
- );
-
- if state is null
- then
- select posting_policy
- into v_forum_policy
- from forums_forums
- where forum_id= new.forum_id;
-
- if v_forum_policy = 'moderated' then
- v_state := 'pending';
- else
- v_state := 'approved';
- end if;
- else
- v_state := state;
- end if;
-
- insert into forums_messages
- (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state)
- values
- (v_message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, v_state);
-
- -- DRB: Can't use root_message_id() here because it triggers a "mutating table" error
-
- select tree_sortkey into v_sortkey
- from forums_messages
- where message_id = v_message_id;
-
- update forums_forums
- set last_post = posting_date
- where forum_id = forums_message.new.forum_id;
-
- update forums_messages
- set last_child_post = posting_date
- where forum_id = forums_message.new.forum_id
- and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
-
- return v_message_id;
- end new;
-
- function root_message_id (
- message_id in forums_messages.message_id%TYPE
- ) return forums_messages.message_id%TYPE
- is
- v_message_id forums_messages.message_id%TYPE;
- v_forum_id forums_messages.forum_id%TYPE;
- v_sortkey forums_messages.tree_sortkey%TYPE;
- begin
- select forum_id, tree_sortkey
- into v_forum_id, v_sortkey
- from forums_messages
- where message_id = root_message_id.message_id;
-
- select message_id
- into v_message_id
- from forums_messages
- where forum_id = v_forum_id
- and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
-
- return v_message_id;
- end root_message_id;
-
- procedure thread_open (
- message_id in forums_messages.message_id%TYPE
- )
- is
- v_forum_id forums_messages.forum_id%TYPE;
- v_sortkey forums_messages.tree_sortkey%TYPE;
- begin
- select forum_id, tree_sortkey
- into v_forum_id, v_sortkey
- from forums_messages
- where message_id = thread_open.message_id;
-
- update forums_messages
- set open_p = 't'
- where tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
- and forum_id = v_forum_id;
-
- update forums_messages
- set open_p = 't'
- where message_id = thread_open.message_id;
- end thread_open;
-
- procedure thread_close (
- message_id in forums_messages.message_id%TYPE
- )
- is
- v_forum_id forums_messages.forum_id%TYPE;
- v_sortkey forums_messages.tree_sortkey%TYPE;
- begin
- select forum_id, tree_sortkey
- into v_forum_id, v_sortkey
- from forums_messages
- where message_id = thread_close.message_id;
-
- update forums_messages
- set open_p = 'f'
- where tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
- and forum_id = v_forum_id;
-
- update forums_messages
- set open_p = 'f'
- where message_id = thread_close.message_id;
- end thread_close;
-
- procedure delete (
- message_id in forums_messages.message_id%TYPE
- )
- is
- begin
- acs_object.delete(message_id);
- end delete;
-
- procedure delete_thread (
- message_id in forums_messages.message_id%TYPE
- )
- is
- v_forum_id forums_messages.forum_id%TYPE;
- v_sortkey forums_messages.tree_sortkey%TYPE;
- v_message forums_messages%ROWTYPE;
- begin
- select forum_id, tree_sortkey
- into v_forum_id, v_sortkey
- from forums_messages
- where message_id = delete_thread.message_id;
-
- -- if it's already deleted
- if SQL%NOTFOUND then
- return;
- end if;
-
- -- delete all children
- -- order by tree_sortkey desc to guarantee
- -- that we never delete a parent before its child
- -- sortkeys are beautiful
- for v_message in (select *
- from forums_messages
- where forum_id = v_forum_id
- and tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
- order by tree_sortkey desc)
- loop
- forums_message.delete(v_message.message_id);
- end loop;
-
- -- delete the message itself
- forums_message.delete(delete_thread.message_id);
- end delete_thread;
-
- function name (
- message_id in forums_messages.message_id%TYPE
- ) return varchar
- is
- v_name forums_messages.subject%TYPE;
- begin
- select subject
- into v_name
- from forums_messages
- where message_id = forums_message.name.message_id;
-
- return v_name;
- end name;
-
-end forums_message;
-/
-show errors
-
-create or replace package body forums_forum
-as
-
- function new (
- forum_id in forums_forums.forum_id%TYPE default null,
- object_type in acs_objects.object_type%TYPE default 'forums_forum',
- name in forums_forums.name%TYPE,
- charter in forums_forums.charter%TYPE default null,
- presentation_type in forums_forums.presentation_type%TYPE,
- posting_policy in forums_forums.posting_policy%TYPE,
- package_id in forums_forums.package_id%TYPE,
- creation_date in acs_objects.creation_date%TYPE default sysdate,
- creation_user in acs_objects.creation_user%TYPE,
- creation_ip in acs_objects.creation_ip%TYPE,
- context_id in acs_objects.context_id%TYPE default null
- ) return forums_forums.forum_id%TYPE
- is
- v_forum_id forums_forums.forum_id%TYPE;
- begin
- v_forum_id := acs_object.new(
- object_id => forum_id,
- object_type => object_type,
- creation_date => creation_date,
- creation_user => creation_user,
- creation_ip => creation_ip,
- context_id => nvl(context_id, package_id)
- );
-
- insert into forums_forums
- (forum_id, name, charter, presentation_type, posting_policy, package_id)
- values
- (v_forum_id, name, charter, presentation_type, posting_policy, package_id);
-
- return v_forum_id;
- end new;
-
- function name (
- forum_id in forums_forums.forum_id%TYPE
- ) return varchar
- is
- v_name forums_forums.name%TYPE;
- begin
- select name
- into v_name
- from forums_forums
- where forum_id = name.forum_id;
-
- return v_name;
- end name;
-
- procedure delete (
- forum_id in forums_forums.forum_id%TYPE
- )
- is
- begin
- acs_object.delete(forum_id);
- end delete;
-
-end forums_forum;
-/
-show errors
Index: openacs-4/packages/forums/sql/oracle/upgrade-0.2d-0.3d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/Attic/upgrade-0.2d-0.3d.sql,v
diff -u -N
--- openacs-4/packages/forums/sql/oracle/upgrade-0.2d-0.3d.sql 16 Jan 2003 13:48:49 -0000 1.2
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,6 +0,0 @@
--- forums/sql/oracle/upgrade-0.2d-0.3d.sql
---
--- Changes for scalability davis@xarg.net
-
-create unique index forums_mess_forum_sk_un on forums_messages(forum_id, tree_sortkey);
-
Index: openacs-4/packages/forums/sql/oracle/upgrade-0.3d-0.4d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/Attic/upgrade-0.3d-0.4d.sql,v
diff -u -N
--- openacs-4/packages/forums/sql/oracle/upgrade-0.3d-0.4d.sql 17 May 2003 10:41:35 -0000 1.2
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,3 +0,0 @@
--- Need these two for RI checks
-create index forums_messages_user_id_idx ON forums_messages(user_id);
-create index forums_messages_parent_id_idx ON forums_messages(parent_id);
Index: openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.1d-0.2d.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,328 @@
+alter table forums_forums add (last_post date);
+alter table forums_messages add (last_child_post date);
+
+declare
+ v_date date;
+begin
+
+ for row in (select forum_id
+ from forums_forums)
+ loop
+
+ select last_modified into v_date
+ from acs_objects
+ where object_id = row.forum_id;
+
+ update forums_forums
+ set last_post = v_date
+ where forum_id = row.forum_id;
+
+ end loop;
+
+ for row in (select message_id
+ from forums_messages
+ where parent_id is null)
+ loop
+
+ -- forums 0.1d did not properly set the last_modified field of the object
+ -- row associated with the root message of a thread, so we need to calculate
+ -- it here.
+
+ select max(o.last_modified) into v_date
+ from acs_objects o, forums_messages fm
+ where forums_message.root_message_id(fm.message_id) = row.message_id
+ and object_id = fm.message_id;
+
+ update forums_messages
+ set last_child_post = v_date
+ where message_id = row.message_id;
+
+ end loop;
+
+end;
+/
+show errors;
+
+drop view forums_messages_approved;
+create view forums_messages_approved
+as
+ select *
+ from forums_messages
+ where state = 'approved';
+
+drop view forums_messages_pending;
+create view forums_messages_pending
+as
+ select *
+ from forums_messages
+ where state= 'pending';
+
+drop view forums_forums_enabled;
+create view forums_forums_enabled
+as
+ select *
+ from forums_forums
+ where enabled_p = 't';
+
+create or replace package body forums_message
+as
+
+ function new (
+ message_id in forums_messages.message_id%TYPE default null,
+ object_type in acs_objects.object_type%TYPE default 'forums_message',
+ forum_id in forums_messages.forum_id%TYPE,
+ subject in forums_messages.subject%TYPE,
+ content in varchar,
+ html_p in forums_messages.html_p%TYPE default 'f',
+ user_id in forums_messages.user_id%TYPE,
+ posting_date in forums_messages.posting_date%TYPE default sysdate,
+ state in forums_messages.state%TYPE default null,
+ parent_id in forums_messages.parent_id%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_user in acs_objects.creation_user%TYPE,
+ creation_ip in acs_objects.creation_ip%TYPE,
+ context_id in acs_objects.context_id%TYPE default null
+ ) return forums_messages.message_id%TYPE
+ is
+ v_message_id acs_objects.object_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ v_forum_policy forums_forums.posting_policy%TYPE;
+ v_state forums_messages.state%TYPE;
+ begin
+
+ v_message_id := acs_object.new(
+ object_id => message_id,
+ object_type => object_type,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip,
+ context_id => nvl(context_id, forum_id)
+ );
+
+ if state is null
+ then
+ select posting_policy
+ into v_forum_policy
+ from forums_forums
+ where forum_id= new.forum_id;
+
+ if v_forum_policy = 'moderated' then
+ v_state := 'pending';
+ else
+ v_state := 'approved';
+ end if;
+ else
+ v_state := state;
+ end if;
+
+ insert into forums_messages
+ (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state)
+ values
+ (v_message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, v_state);
+
+ -- DRB: Can't use root_message_id() here because it triggers a "mutating table" error
+
+ select tree_sortkey into v_sortkey
+ from forums_messages
+ where message_id = v_message_id;
+
+ update forums_forums
+ set last_post = posting_date
+ where forum_id = forums_message.new.forum_id;
+
+ update forums_messages
+ set last_child_post = posting_date
+ where forum_id = forums_message.new.forum_id
+ and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
+
+ return v_message_id;
+ end new;
+
+ function root_message_id (
+ message_id in forums_messages.message_id%TYPE
+ ) return forums_messages.message_id%TYPE
+ is
+ v_message_id forums_messages.message_id%TYPE;
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = root_message_id.message_id;
+
+ select message_id
+ into v_message_id
+ from forums_messages
+ where forum_id = v_forum_id
+ and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
+
+ return v_message_id;
+ end root_message_id;
+
+ procedure thread_open (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = thread_open.message_id;
+
+ update forums_messages
+ set open_p = 't'
+ where tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
+ and forum_id = v_forum_id;
+
+ update forums_messages
+ set open_p = 't'
+ where message_id = thread_open.message_id;
+ end thread_open;
+
+ procedure thread_close (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = thread_close.message_id;
+
+ update forums_messages
+ set open_p = 'f'
+ where tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
+ and forum_id = v_forum_id;
+
+ update forums_messages
+ set open_p = 'f'
+ where message_id = thread_close.message_id;
+ end thread_close;
+
+ procedure delete (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ begin
+ acs_object.delete(message_id);
+ end delete;
+
+ procedure delete_thread (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ v_message forums_messages%ROWTYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = delete_thread.message_id;
+
+ -- if it's already deleted
+ if SQL%NOTFOUND then
+ return;
+ end if;
+
+ -- delete all children
+ -- order by tree_sortkey desc to guarantee
+ -- that we never delete a parent before its child
+ -- sortkeys are beautiful
+ for v_message in (select *
+ from forums_messages
+ where forum_id = v_forum_id
+ and tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
+ order by tree_sortkey desc)
+ loop
+ forums_message.delete(v_message.message_id);
+ end loop;
+
+ -- delete the message itself
+ forums_message.delete(delete_thread.message_id);
+ end delete_thread;
+
+ function name (
+ message_id in forums_messages.message_id%TYPE
+ ) return varchar
+ is
+ v_name forums_messages.subject%TYPE;
+ begin
+ select subject
+ into v_name
+ from forums_messages
+ where message_id = forums_message.name.message_id;
+
+ return v_name;
+ end name;
+
+end forums_message;
+/
+show errors
+
+create or replace package body forums_forum
+as
+
+ function new (
+ forum_id in forums_forums.forum_id%TYPE default null,
+ object_type in acs_objects.object_type%TYPE default 'forums_forum',
+ name in forums_forums.name%TYPE,
+ charter in forums_forums.charter%TYPE default null,
+ presentation_type in forums_forums.presentation_type%TYPE,
+ posting_policy in forums_forums.posting_policy%TYPE,
+ package_id in forums_forums.package_id%TYPE,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_user in acs_objects.creation_user%TYPE,
+ creation_ip in acs_objects.creation_ip%TYPE,
+ context_id in acs_objects.context_id%TYPE default null
+ ) return forums_forums.forum_id%TYPE
+ is
+ v_forum_id forums_forums.forum_id%TYPE;
+ begin
+ v_forum_id := acs_object.new(
+ object_id => forum_id,
+ object_type => object_type,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip,
+ context_id => nvl(context_id, package_id)
+ );
+
+ insert into forums_forums
+ (forum_id, name, charter, presentation_type, posting_policy, package_id)
+ values
+ (v_forum_id, name, charter, presentation_type, posting_policy, package_id);
+
+ return v_forum_id;
+ end new;
+
+ function name (
+ forum_id in forums_forums.forum_id%TYPE
+ ) return varchar
+ is
+ v_name forums_forums.name%TYPE;
+ begin
+ select name
+ into v_name
+ from forums_forums
+ where forum_id = name.forum_id;
+
+ return v_name;
+ end name;
+
+ procedure delete (
+ forum_id in forums_forums.forum_id%TYPE
+ )
+ is
+ begin
+ acs_object.delete(forum_id);
+ end delete;
+
+end forums_forum;
+/
+show errors
Index: openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.2d-0.3d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.2d-0.3d.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.2d-0.3d.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,6 @@
+-- forums/sql/oracle/upgrade-0.2d-0.3d.sql
+--
+-- Changes for scalability davis@xarg.net
+
+create unique index forums_mess_forum_sk_un on forums_messages(forum_id, tree_sortkey);
+
Index: openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.3d-0.4d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.3d-0.4d.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/oracle/upgrade/upgrade-0.3d-0.4d.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,3 @@
+-- Need these two for RI checks
+create index forums_messages_user_id_idx ON forums_messages(user_id);
+create index forums_messages_parent_id_idx ON forums_messages(parent_id);
Index: openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.0d2-1.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.0d2-1.0d3.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.0d2-1.0d3.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,260 @@
+
+--
+-- Upgrade script for oracle 1.0d2 - 1.0d3
+--
+-- @author Lars Pind (lars@collaboraid.biz)
+-- @creation-date 2003-10-3
+--
+-- The forums_message.new function updated the thread's last post date to the posting date
+-- of the first post (!), because the code inadvertedly referred to the posting_date
+-- column instead of the posting_date parameter.
+--
+
+create or replace package forums_message
+as
+
+ function new (
+ message_id in forums_messages.message_id%TYPE default null,
+ object_type in acs_objects.object_type%TYPE default 'forums_message',
+ forum_id in forums_messages.forum_id%TYPE,
+ subject in forums_messages.subject%TYPE,
+ content in varchar,
+ html_p in forums_messages.html_p%TYPE default 'f',
+ user_id in forums_messages.user_id%TYPE,
+ posting_date in forums_messages.posting_date%TYPE default sysdate,
+ state in forums_messages.state%TYPE default null,
+ parent_id in forums_messages.parent_id%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_user in acs_objects.creation_user%TYPE,
+ creation_ip in acs_objects.creation_ip%TYPE,
+ context_id in acs_objects.context_id%TYPE default null
+ ) return forums_messages.message_id%TYPE;
+
+ function root_message_id (
+ message_id in forums_messages.message_id%TYPE
+ ) return forums_messages.message_id%TYPE;
+
+ procedure thread_open (
+ message_id in forums_messages.message_id%TYPE
+ );
+
+ procedure thread_close (
+ message_id in forums_messages.message_id%TYPE
+ );
+
+ procedure del (
+ message_id in forums_messages.message_id%TYPE
+ );
+
+ procedure delete_thread (
+ message_id in forums_messages.message_id%TYPE
+ );
+
+ function name (
+ message_id in forums_messages.message_id%TYPE
+ ) return varchar;
+
+end forums_message;
+/
+show errors
+
+create or replace package body forums_message
+as
+
+ function new (
+ message_id in forums_messages.message_id%TYPE default null,
+ object_type in acs_objects.object_type%TYPE default 'forums_message',
+ forum_id in forums_messages.forum_id%TYPE,
+ subject in forums_messages.subject%TYPE,
+ content in varchar,
+ html_p in forums_messages.html_p%TYPE default 'f',
+ user_id in forums_messages.user_id%TYPE,
+ posting_date in forums_messages.posting_date%TYPE default sysdate,
+ state in forums_messages.state%TYPE default null,
+ parent_id in forums_messages.parent_id%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_user in acs_objects.creation_user%TYPE,
+ creation_ip in acs_objects.creation_ip%TYPE,
+ context_id in acs_objects.context_id%TYPE default null
+ ) return forums_messages.message_id%TYPE
+ is
+ v_message_id acs_objects.object_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ v_forum_policy forums_forums.posting_policy%TYPE;
+ v_state forums_messages.state%TYPE;
+ begin
+
+ v_message_id := acs_object.new(
+ object_id => message_id,
+ object_type => object_type,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip,
+ context_id => nvl(context_id, forum_id)
+ );
+
+ if state is null
+ then
+ select posting_policy
+ into v_forum_policy
+ from forums_forums
+ where forum_id= new.forum_id;
+
+ if v_forum_policy = 'moderated' then
+ v_state := 'pending';
+ else
+ v_state := 'approved';
+ end if;
+ else
+ v_state := state;
+ end if;
+
+ insert into forums_messages
+ (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state)
+ values
+ (v_message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, v_state);
+
+ -- DRB: Can't use root_message_id() here because it triggers a "mutating table" error
+
+ select tree_sortkey into v_sortkey
+ from forums_messages
+ where message_id = v_message_id;
+
+ update forums_forums
+ set last_post = forums_message.new.posting_date
+ where forum_id = forums_message.new.forum_id;
+
+ update forums_messages
+ set last_child_post = forums_message.new.posting_date
+ where forum_id = forums_message.new.forum_id
+ and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
+
+ return v_message_id;
+ end new;
+
+ function root_message_id (
+ message_id in forums_messages.message_id%TYPE
+ ) return forums_messages.message_id%TYPE
+ is
+ v_message_id forums_messages.message_id%TYPE;
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = root_message_id.message_id;
+
+ select message_id
+ into v_message_id
+ from forums_messages
+ where forum_id = v_forum_id
+ and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
+
+ return v_message_id;
+ end root_message_id;
+
+ procedure thread_open (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = thread_open.message_id;
+
+ update forums_messages
+ set open_p = 't'
+ where tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
+ and forum_id = v_forum_id;
+
+ update forums_messages
+ set open_p = 't'
+ where message_id = thread_open.message_id;
+ end thread_open;
+
+ procedure thread_close (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = thread_close.message_id;
+
+ update forums_messages
+ set open_p = 'f'
+ where tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
+ and forum_id = v_forum_id;
+
+ update forums_messages
+ set open_p = 'f'
+ where message_id = thread_close.message_id;
+ end thread_close;
+
+ procedure del (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ begin
+ acs_object.del(message_id);
+ end del;
+
+ procedure delete_thread (
+ message_id in forums_messages.message_id%TYPE
+ )
+ is
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ v_message forums_messages%ROWTYPE;
+ begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = delete_thread.message_id;
+
+ -- if it's already deleted
+ if SQL%NOTFOUND then
+ return;
+ end if;
+
+ -- delete all children
+ -- order by tree_sortkey desc to guarantee
+ -- that we never delete a parent before its child
+ -- sortkeys are beautiful
+ for v_message in (select *
+ from forums_messages
+ where forum_id = v_forum_id
+ and tree_sortkey between tree.left(v_sortkey) and tree.right(v_sortkey)
+ order by tree_sortkey desc)
+ loop
+ forums_message.del(v_message.message_id);
+ end loop;
+
+ -- delete the message itself
+ forums_message.del(delete_thread.message_id);
+ end delete_thread;
+
+ function name (
+ message_id in forums_messages.message_id%TYPE
+ ) return varchar
+ is
+ v_name forums_messages.subject%TYPE;
+ begin
+ select subject
+ into v_name
+ from forums_messages
+ where message_id = forums_message.name.message_id;
+
+ return v_name;
+ end name;
+
+end forums_message;
+/
+show errors
Index: openacs-4/packages/forums/sql/postgresql/upgrade-0.1d-0.2d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/upgrade-0.1d-0.2d.sql,v
diff -u -N
--- openacs-4/packages/forums/sql/postgresql/upgrade-0.1d-0.2d.sql 17 May 2003 10:42:08 -0000 1.3
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,173 +0,0 @@
-alter table forums_forums add column last_post timestamptz;
-alter table forums_messages add column last_child_post timestamptz;
-
-create or replace function t () returns integer as '
-declare
- v_record record;
- v_timestamp timestamptz;
-begin
-
- for v_record in select forum_id
- from forums_forums
- loop
-
- select last_modified into v_timestamp
- from acs_objects
- where object_id = v_record.forum_id;
-
- update forums_forums
- set last_post = v_timestamp
- where forum_id = v_record.forum_id;
-
- end loop;
-
- for v_record in select message_id
- from forums_messages
- where parent_id is null
- loop
-
- -- forums 0.1d did not properly set the last_modified field of the object
- -- row associated with the root message of a thread, so we need to calculate
- -- it here.
-
- select max(o.last_modified) into v_timestamp
- from acs_objects o, forums_messages fm
- where forums_message__root_message_id(fm.message_id) = v_record.message_id
- and object_id = fm.message_id;
-
- update forums_messages
- set last_child_post = v_timestamp
- where message_id = v_record.message_id;
-
- end loop;
-
- return 1;
-
-end;' language 'plpgsql';
-
-select t();
-
-drop view forums_messages_approved;
-create view forums_messages_approved
-as
- select *
- from forums_messages
- where state = 'approved';
-
-drop view forums_messages_pending;
-create view forums_messages_pending
-as
- select *
- from forums_messages
- where state= 'pending';
-
-drop view forums_forums_enabled;
-create view forums_forums_enabled
-as
- select *
- from forums_forums
- where enabled_p = 't';
-
-create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer)
-returns integer as '
-declare
- p_message_id alias for $1;
- p_object_type alias for $2;
- p_forum_id alias for $3;
- p_subject alias for $4;
- p_content alias for $5;
- p_html_p alias for $6;
- p_user_id alias for $7;
- p_posting_date alias for $8;
- p_state alias for $9;
- p_parent_id alias for $10;
- p_creation_date alias for $11;
- p_creation_user alias for $12;
- p_creation_ip alias for $13;
- p_context_id alias for $14;
- v_message_id integer;
- v_forum_policy forums_forums.posting_policy%TYPE;
- v_state forums_messages.state%TYPE;
- v_posting_date forums_messages.posting_date%TYPE;
-begin
- v_message_id := acs_object__new(
- p_message_id,
- p_object_type,
- p_creation_date,
- p_creation_user,
- p_creation_ip,
- coalesce(p_context_id, p_forum_id)
- );
-
- if p_state is null then
- select posting_policy
- into v_forum_policy
- from forums_forums
- where forum_id = p_forum_id;
-
- if v_forum_policy = ''moderated''
- then v_state := ''pending'';
- else v_state := ''approved'';
- end if;
- else
- v_state := p_state;
- end if;
-
- if p_posting_date is null then
- v_posting_date = now();
- else
- v_posting_date = p_posting_date;
- end if;
-
- insert into forums_messages
- (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state)
- values
- (v_message_id, p_forum_id, p_subject, p_content, p_html_p, p_user_id, v_posting_date, p_parent_id, v_state);
-
- update forums_forums
- set last_post = v_posting_date
- where forum_id = p_forum_id;
-
- update forums_messages
- set last_child_post = v_posting_date
- where message_id = forums_message__root_message_id(v_message_id);
-
- return v_message_id;
-
-end;
-' language 'plpgsql';
-
-create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer)
-returns integer as '
-declare
- p_forum_id alias for $1;
- p_object_type alias for $2;
- p_name alias for $3;
- p_charter alias for $4;
- p_presentation_type alias for $5;
- p_posting_policy alias for $6;
- p_package_id alias for $7;
- p_creation_date alias for $8;
- p_creation_user alias for $9;
- p_creation_ip alias for $10;
- p_context_id alias for $11;
- v_forum_id integer;
-begin
- v_forum_id:= acs_object__new(
- p_forum_id,
- p_object_type,
- p_creation_date,
- p_creation_user,
- p_creation_ip,
- coalesce(p_context_id, p_package_id)
- );
-
- insert into forums_forums
- (forum_id, name, charter, presentation_type, posting_policy, package_id)
- values
- (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id);
-
- return v_forum_id;
-end;
-' language 'plpgsql';
-
Index: openacs-4/packages/forums/sql/postgresql/upgrade-0.2d-0.3d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/upgrade-0.2d-0.3d.sql,v
diff -u -N
--- openacs-4/packages/forums/sql/postgresql/upgrade-0.2d-0.3d.sql 16 Jan 2003 13:49:10 -0000 1.2
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,28 +0,0 @@
--- forums/sql/postgresql/upgrade-0.2d-0.3d.sql
---
--- Changes for scalability davis@xarg.net
-
-create unique index forums_mess_forum_sk_un on forums_messages(forum_id, tree_sortkey);
-
-create or replace function forums_message__root_message_id (integer)
-returns integer as '
-declare
- p_message_id alias for $1;
- v_message_id forums_messages.message_id%TYPE;
- v_forum_id forums_messages.forum_id%TYPE;
- v_sortkey forums_messages.tree_sortkey%TYPE;
-begin
- select forum_id, tree_sortkey
- into v_forum_id, v_sortkey
- from forums_messages
- where message_id = p_message_id;
-
- select message_id
- into v_message_id
- from forums_messages
- where forum_id = v_forum_id
- and tree_sortkey = tree_ancestor_key(v_sortkey, 1);
-
- return v_message_id;
-end;
-' language 'plpgsql' with(isstrict,iscachable);
Index: openacs-4/packages/forums/sql/postgresql/upgrade-0.3d-0.4d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/upgrade-0.3d-0.4d.sql,v
diff -u -N
--- openacs-4/packages/forums/sql/postgresql/upgrade-0.3d-0.4d.sql 17 May 2003 10:42:08 -0000 1.2
+++ /dev/null 1 Jan 1970 00:00:00 -0000
@@ -1,3 +0,0 @@
--- Need these two for RI checks
-create index forums_messages_user_id_idx ON forums_messages(user_id);
-create index forums_messages_parent_id_idx ON forums_messages(parent_id);
Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,173 @@
+alter table forums_forums add column last_post timestamptz;
+alter table forums_messages add column last_child_post timestamptz;
+
+create or replace function t () returns integer as '
+declare
+ v_record record;
+ v_timestamp timestamptz;
+begin
+
+ for v_record in select forum_id
+ from forums_forums
+ loop
+
+ select last_modified into v_timestamp
+ from acs_objects
+ where object_id = v_record.forum_id;
+
+ update forums_forums
+ set last_post = v_timestamp
+ where forum_id = v_record.forum_id;
+
+ end loop;
+
+ for v_record in select message_id
+ from forums_messages
+ where parent_id is null
+ loop
+
+ -- forums 0.1d did not properly set the last_modified field of the object
+ -- row associated with the root message of a thread, so we need to calculate
+ -- it here.
+
+ select max(o.last_modified) into v_timestamp
+ from acs_objects o, forums_messages fm
+ where forums_message__root_message_id(fm.message_id) = v_record.message_id
+ and object_id = fm.message_id;
+
+ update forums_messages
+ set last_child_post = v_timestamp
+ where message_id = v_record.message_id;
+
+ end loop;
+
+ return 1;
+
+end;' language 'plpgsql';
+
+select t();
+
+drop view forums_messages_approved;
+create view forums_messages_approved
+as
+ select *
+ from forums_messages
+ where state = 'approved';
+
+drop view forums_messages_pending;
+create view forums_messages_pending
+as
+ select *
+ from forums_messages
+ where state= 'pending';
+
+drop view forums_forums_enabled;
+create view forums_forums_enabled
+as
+ select *
+ from forums_forums
+ where enabled_p = 't';
+
+create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ p_object_type alias for $2;
+ p_forum_id alias for $3;
+ p_subject alias for $4;
+ p_content alias for $5;
+ p_html_p alias for $6;
+ p_user_id alias for $7;
+ p_posting_date alias for $8;
+ p_state alias for $9;
+ p_parent_id alias for $10;
+ p_creation_date alias for $11;
+ p_creation_user alias for $12;
+ p_creation_ip alias for $13;
+ p_context_id alias for $14;
+ v_message_id integer;
+ v_forum_policy forums_forums.posting_policy%TYPE;
+ v_state forums_messages.state%TYPE;
+ v_posting_date forums_messages.posting_date%TYPE;
+begin
+ v_message_id := acs_object__new(
+ p_message_id,
+ p_object_type,
+ p_creation_date,
+ p_creation_user,
+ p_creation_ip,
+ coalesce(p_context_id, p_forum_id)
+ );
+
+ if p_state is null then
+ select posting_policy
+ into v_forum_policy
+ from forums_forums
+ where forum_id = p_forum_id;
+
+ if v_forum_policy = ''moderated''
+ then v_state := ''pending'';
+ else v_state := ''approved'';
+ end if;
+ else
+ v_state := p_state;
+ end if;
+
+ if p_posting_date is null then
+ v_posting_date = now();
+ else
+ v_posting_date = p_posting_date;
+ end if;
+
+ insert into forums_messages
+ (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state)
+ values
+ (v_message_id, p_forum_id, p_subject, p_content, p_html_p, p_user_id, v_posting_date, p_parent_id, v_state);
+
+ update forums_forums
+ set last_post = v_posting_date
+ where forum_id = p_forum_id;
+
+ update forums_messages
+ set last_child_post = v_posting_date
+ where message_id = forums_message__root_message_id(v_message_id);
+
+ return v_message_id;
+
+end;
+' language 'plpgsql';
+
+create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer)
+returns integer as '
+declare
+ p_forum_id alias for $1;
+ p_object_type alias for $2;
+ p_name alias for $3;
+ p_charter alias for $4;
+ p_presentation_type alias for $5;
+ p_posting_policy alias for $6;
+ p_package_id alias for $7;
+ p_creation_date alias for $8;
+ p_creation_user alias for $9;
+ p_creation_ip alias for $10;
+ p_context_id alias for $11;
+ v_forum_id integer;
+begin
+ v_forum_id:= acs_object__new(
+ p_forum_id,
+ p_object_type,
+ p_creation_date,
+ p_creation_user,
+ p_creation_ip,
+ coalesce(p_context_id, p_package_id)
+ );
+
+ insert into forums_forums
+ (forum_id, name, charter, presentation_type, posting_policy, package_id)
+ values
+ (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id);
+
+ return v_forum_id;
+end;
+' language 'plpgsql';
+
Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.2d-0.3d.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,28 @@
+-- forums/sql/postgresql/upgrade-0.2d-0.3d.sql
+--
+-- Changes for scalability davis@xarg.net
+
+create unique index forums_mess_forum_sk_un on forums_messages(forum_id, tree_sortkey);
+
+create or replace function forums_message__root_message_id (integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ v_message_id forums_messages.message_id%TYPE;
+ v_forum_id forums_messages.forum_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+begin
+ select forum_id, tree_sortkey
+ into v_forum_id, v_sortkey
+ from forums_messages
+ where message_id = p_message_id;
+
+ select message_id
+ into v_message_id
+ from forums_messages
+ where forum_id = v_forum_id
+ and tree_sortkey = tree_ancestor_key(v_sortkey, 1);
+
+ return v_message_id;
+end;
+' language 'plpgsql' with(isstrict,iscachable);
Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.3d-0.4d.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.3d-0.4d.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-0.3d-0.4d.sql 3 Oct 2003 19:38:15 -0000 1.1
@@ -0,0 +1,3 @@
+-- Need these two for RI checks
+create index forums_messages_user_id_idx ON forums_messages(user_id);
+create index forums_messages_parent_id_idx ON forums_messages(parent_id);