#forums.Posted_by# @message.user_name@ on @message.posting_date_pretty@
- 
- 
- 
+
+
+
+
+
+
+ -
+
- #forums.reply# +
- #forums.forward# +
- #forums.edit# +
- #forums.delete# +
- #forums.approve# +
- #forums.reject# +
+
+
+
select fm.message_id,
fm.subject,
- fm.user_id,
- person__name(fm.user_id) as user_name,
+ fm.last_poster as user_id,
+ person__name(fm.last_poster) as user_name,
to_char(fm.posting_date, 'YYYY-MM-DD HH24:MI:SS') as posting_date_ansi,
fm.state,
$replies as n_messages,
@@ -37,12 +37,12 @@
- lower(person__name(fm.user_id)) desc
+ lower(person__name(fm.last_poster)) desc
- lower(person__name(fm.user_id)) asc
+ lower(person__name(fm.last_poster)) asc
Index: openacs-4/packages/forums/sql/oracle/forums-messages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-messages-create.sql,v
diff -u -r1.10 -r1.11
--- openacs-4/packages/forums/sql/oracle/forums-messages-create.sql 13 Jan 2005 13:58:15 -0000 1.10
+++ openacs-4/packages/forums/sql/oracle/forums-messages-create.sql 15 Mar 2005 20:06:45 -0000 1.11
@@ -46,8 +46,15 @@
tree_sortkey raw(240),
max_child_sortkey raw(100),
last_child_post date,
- reply_count integer default 0,
- approved_reply_count integer default 0,
+ reply_count integer
+ constraint forums_mess_reply_count_ck
+ check (reply_count >= 0) default 0,
+ approved_reply_count integer
+ constraint forums_mess_app_rep_count_ck
+ check (approved_reply_count >= 0) default 0,
+ last_poster integer
+ constraint forums_mess_last_poster_fk
+ references users(user_id),
constraint forums_mess_sk_forum_un
unique (tree_sortkey, forum_id)
);
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 -r1.9 -r1.10
--- openacs-4/packages/forums/sql/oracle/forums-messages-package-create.sql 13 Jan 2005 13:58:15 -0000 1.9
+++ openacs-4/packages/forums/sql/oracle/forums-messages-package-create.sql 15 Mar 2005 20:06:45 -0000 1.10
@@ -120,9 +120,9 @@
end if;
insert into forums_messages
- (message_id, forum_id, subject, content, format, user_id, posting_date, last_child_post, parent_id, state)
+ (message_id, forum_id, subject, content, format, user_id, posting_date, last_child_post, parent_id, state, last_poster)
values
- (v_message_id, forum_id, subject, content, format, user_id, posting_date, posting_date, parent_id, v_state);
+ (v_message_id, forum_id, subject, content, format, user_id, posting_date, posting_date, parent_id, v_state, user_id);
select tree_sortkey into v_sortkey
from forums_messages
@@ -150,13 +150,13 @@
update forums_messages
set approved_reply_count = approved_reply_count + 1,
reply_count = reply_count + 1,
+ last_poster = user_id,
last_child_post = sysdate
where forum_id = forums_message.new.forum_id
and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
else
update forums_messages
- set reply_count = reply_count + 1,
- last_child_post = sysdate
+ set reply_count = reply_count + 1
where forum_id = forums_message.new.forum_id
and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
end if;
@@ -347,7 +347,9 @@
else
if state = 'approved' and v_cur.state <> 'approved' then
update forums_messages
- set approved_reply_count = approved_reply_count + 1
+ set approved_reply_count = approved_reply_count + 1,
+ last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end),
+ last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end)
where forum_id = v_cur.forum_id
and tree_sortkey = tree.ancestor_key(v_cur.tree_sortkey, 1);
elsif state <> 'approved' and v_cur.state = 'approved' then
Index: openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.2d1-1.2d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.2d1-1.2d2.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/oracle/upgrade/upgrade-1.2d1-1.2d2.sql 15 Mar 2005 20:06:45 -0000 1.1
@@ -0,0 +1,414 @@
+-- @author Jeff Davis davis@xarg.net
+--
+-- bug 1807 last_poster rather than first poster should be shown in forums index page
+-- add a last_poster to support this and update triggers to support it.
+
+alter table forums_messages add column last_poster integer
+ constraint forums_mess_last_poster_fk
+ references users(user_id);
+
+-- Now populate the new column
+-- this depends on last_child_post being properly set.
+-- use min(user_id) just in case there are two that have the same timestamp)
+
+update forums_messages set last_poster = (select min(user_id)
+ from forums_messages fm1
+ where fm1.posting_date = forums_messages.last_child_post
+ and forums_messages.forum_id = fm1.forum_id
+ and fm1.tree_sortkey
+ between tree_left(forums_messages.tree_sortkey)
+ and tree_right(forums_messages.tree_sortkey) )
+where parent_id is null;
+
+-- the better method above fails for some things (like notably openacs.org where
+-- the last_child_post may not exist in the child posts due to import and upgrade
+-- glitches. try this one which will give us a name no matter what.
+update forums_messages
+set last_poster = (select user_id
+ from forums_messages fm1
+ where fm1.message_id = (select max(message_id)
+ from forums_messages fm2
+ where forums_messages.forum_id = fm2.forum_id
+ and fm2.tree_sortkey
+ between tree_left(forums_messages.tree_sortkey)
+ and tree_right(forums_messages.tree_sortkey) ))
+where parent_id is null and last_poster is null;
+
+
+create or replace view forums_messages_approved
+as
+ select *
+ from forums_messages
+ where state = 'approved';
+
+create or replace view forums_messages_pending
+as
+ select *
+ from forums_messages
+ where state= 'pending';
+
+
+
+--
+-- Replace the procs which manipulate state and new message to save last_poster.
+--
+
+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,
+ format in forums_messages.format%TYPE default 'text/plain',
+ 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
+ );
+
+ procedure set_state(
+ message_id in forums_messages.message_id%TYPE,
+ state in forums_messages.state%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,
+ format in forums_messages.format%TYPE default 'text/plain',
+ 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_package_id acs_objects.package_id%TYPE;
+ v_sortkey forums_messages.tree_sortkey%TYPE;
+ v_forum_policy forums_forums.posting_policy%TYPE;
+ v_state forums_messages.state%TYPE;
+ begin
+ select package_id into v_package_id from forums_forums where forum_id = new.forum_id;
+
+ 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),
+ title => subject,
+ package_id => v_package_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, format, user_id, posting_date, last_child_post, parent_id, state, last_poster)
+ values
+ (v_message_id, forum_id, subject, content, format, user_id, posting_date, posting_date, parent_id, v_state, user_id);
+
+ 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;
+
+ -- DRB: Can't use root_message_id() here because it triggers a "mutating table" error
+
+ if parent_id is null then
+ if v_state = 'approved' then
+ update forums_forums
+ set thread_count = thread_count + 1,
+ approved_thread_count = approved_thread_count + 1
+ where forum_id = forums_message.new.forum_id;
+ else
+ update forums_forums
+ set thread_count = thread_count + 1
+ where forum_id=forums_message.new.forum_id;
+ end if;
+ else
+ if v_state = 'approved' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count + 1,
+ reply_count = reply_count + 1,
+ last_poster = user_id,
+ last_child_post = sysdate
+ where forum_id = forums_message.new.forum_id
+ and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
+ else
+ update forums_messages
+ set reply_count = reply_count + 1
+ where forum_id = forums_message.new.forum_id
+ and tree_sortkey = tree.ancestor_key(v_sortkey, 1);
+ end if;
+ end if;
+
+ 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
+ v_cur forums_messages%ROWTYPE;
+ begin
+
+ -- Maintain the forum thread counts
+
+ select * into v_cur
+ from forums_messages
+ where message_id = forums_message.del.message_id;
+
+ if v_cur.parent_id is null then
+ if v_cur.state = 'approved' then
+ update forums_forums
+ set thread_count = thread_count - 1,
+ approved_thread_count = approved_thread_count - 1
+ where forum_id=v_cur.forum_id;
+ else
+ update forums_forums
+ set thread_count = thread_count - 1
+ where forum_id=v_cur.forum_id;
+ end if;
+ elsif v_cur.state = 'approved' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count - 1,
+ reply_count = reply_count - 1
+ where forum_id = v_cur.forum_id
+ and tree_sortkey = tree.ancestor_key(v_cur.tree_sortkey, 1);
+ else
+ update forums_messages
+ set reply_count = reply_count - 1
+ where forum_id = v_cur.forum_id
+ and tree_sortkey = tree.ancestor_key(v_cur.tree_sortkey, 1);
+ end if;
+
+ 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;
+
+ procedure set_state(
+ message_id in forums_messages.message_id%TYPE,
+ state in forums_messages.state%TYPE
+ )
+ is
+ v_cur forums_messages%ROWTYPE;
+ begin
+
+ select * into v_cur
+ from forums_messages
+ where message_id = forums_message.set_state.message_id;
+
+ if v_cur.parent_id is null then
+ if state = 'approved' and v_cur.state <> 'approved' then
+ update forums_forums
+ set approved_thread_count = approved_thread_count + 1
+ where forum_id=v_cur.forum_id;
+ elsif state <> 'approved' and v_cur.state = 'approved' then
+ update forums_forums
+ set approved_thread_count = approved_thread_count - 1
+ where forum_id=v_cur.forum_id;
+ end if;
+ else
+ if state = 'approved' and v_cur.state <> 'approved' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count + 1,
+ last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end),
+ last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end)
+ where forum_id = v_cur.forum_id
+ and tree_sortkey = tree.ancestor_key(v_cur.tree_sortkey, 1);
+ elsif state <> 'approved' and v_cur.state = 'approved' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count - 1
+ where forum_id = v_cur.forum_id
+ and tree_sortkey = tree.ancestor_key(v_cur.tree_sortkey, 1);
+ end if;
+ end if;
+
+ update forums_messages
+ set state = forums_message.set_state.state
+ where message_id = forums_message.set_state.message_id;
+
+ end set_state;
+
+end forums_message;
+/
+show errors
+
Index: openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql,v
diff -u -r1.12 -r1.13
--- openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 13 Jan 2005 13:58:16 -0000 1.12
+++ openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 15 Mar 2005 20:06:45 -0000 1.13
@@ -46,8 +46,15 @@
tree_sortkey varbit,
max_child_sortkey varbit,
last_child_post timestamptz,
- reply_count integer default 0,
- approved_reply_count integer default 0,
+ reply_count integer
+ constraint forums_mess_reply_count_ck
+ check (reply_count >= 0) default 0,
+ approved_reply_count integer
+ constraint forums_mess_app_rep_count_ck
+ check (approved_reply_count >= 0) default 0,
+ last_poster integer
+ constraint forums_mess_last_poster_fk
+ references users(user_id),
constraint forums_mess_sk_forum_un
unique (tree_sortkey, forum_id)
);
Index: openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql,v
diff -u -r1.17 -r1.18
--- openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 25 Jan 2005 13:01:14 -0000 1.17
+++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 15 Mar 2005 20:06:45 -0000 1.18
@@ -62,7 +62,7 @@
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'';
@@ -72,10 +72,10 @@
end if;
insert into forums_messages
- (message_id, forum_id, subject, content, format, user_id, parent_id, state, last_child_post)
+ (message_id, forum_id, subject, content, format, user_id, parent_id, state, last_child_post, last_poster)
values
(v_message_id, p_forum_id, p_subject, p_content, p_format, p_user_id, p_parent_id,
- v_state, current_timestamp);
+ v_state, current_timestamp, p_user_id);
update forums_forums
set last_post = current_timestamp
@@ -97,16 +97,17 @@
update forums_messages
set approved_reply_count = approved_reply_count + 1,
reply_count = reply_count + 1,
+ last_poster = p_user_id,
last_child_post = current_timestamp
where message_id = forums_message__root_message_id(v_message_id);
else
+ -- dont update last_poster, last_child_post when not approved
update forums_messages
- set reply_count = reply_count + 1,
- last_child_post = current_timestamp
+ set reply_count = reply_count + 1
where message_id = forums_message__root_message_id(v_message_id);
end if;
end if;
-
+
return v_message_id;
end;' language 'plpgsql';
@@ -216,7 +217,9 @@
else
if p_state = ''approved'' and v_cur.state <> ''approved'' then
update forums_messages
- set approved_reply_count = approved_reply_count + 1
+ set approved_reply_count = approved_reply_count + 1,
+ last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end),
+ last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end)
where message_id = forums_message__root_message_id(v_cur.message_id);
elsif p_state <> ''approved'' and v_cur.state = ''approved'' then
update forums_messages
@@ -271,8 +274,7 @@
end if;
perform acs_object__delete(p_message_id);
- return 0;
-
+ return 0;
end;' language 'plpgsql';
select define_function_args ('forums_message__delete_thread', 'message_id');
@@ -289,7 +291,7 @@
into v_forum_id, v_sortkey
from forums_messages
where message_id = p_message_id;
-
+
-- if it is already deleted
if v_forum_id is null
then return 0;
Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.2d1-1.2d2.sql 15 Mar 2005 20:06:45 -0000 1.1
@@ -0,0 +1,238 @@
+-- @author Jeff Davis davis@xarg.net
+--
+-- bug 1807 last_poster rather than first poster should be shown in forums index page
+-- add a last_poster to support this and update triggers to support it.
+
+alter table forums_messages add column last_poster integer
+ constraint forums_mess_last_poster_fk
+ references users(user_id);
+
+-- Now populate the new column
+-- this depends on last_child_post being properly set.
+-- use min(user_id) just in case there are two that have the same timestamp)
+
+update forums_messages set last_poster = (select min(user_id)
+ from forums_messages fm1
+ where fm1.posting_date = forums_messages.last_child_post
+ and forums_messages.forum_id = fm1.forum_id
+ and fm1.tree_sortkey
+ between tree_left(forums_messages.tree_sortkey)
+ and tree_right(forums_messages.tree_sortkey) )
+where parent_id is null;
+
+-- the better method above fails for some things (like notably openacs.org where
+-- the last_child_post may not exist in the child posts due to import and upgrade
+-- glitches. try this one which will give us a name no matter what.
+update forums_messages
+set last_poster = (select user_id
+ from forums_messages fm1
+ where fm1.message_id = (select max(message_id)
+ from forums_messages fm2
+ where forums_messages.forum_id = fm2.forum_id
+ and fm2.tree_sortkey
+ between tree_left(forums_messages.tree_sortkey)
+ and tree_right(forums_messages.tree_sortkey) ))
+where parent_id is null and last_poster is null;
+
+
+-- Need to drop and recreate because Postgres doesn't allow one to change the
+-- number of columns in a view when you do a "replace".
+drop view forums_messages_approved;
+create or replace view forums_messages_approved
+as
+ select *
+ from forums_messages
+ where state = 'approved';
+
+drop view forums_messages_pending;
+create or replace view forums_messages_pending
+as
+ select *
+ from forums_messages
+ where state= 'pending';
+
+
+--
+-- Replace the procs which manipulate state and new message to save last_poster.
+--
+create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,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_format alias for $6;
+ p_user_id alias for $7;
+ p_state alias for $8;
+ p_parent_id alias for $9;
+ p_creation_date alias for $10;
+ p_creation_user alias for $11;
+ p_creation_ip alias for $12;
+ p_context_id alias for $13;
+ 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;
+ v_package_id acs_objects.package_id%TYPE;
+begin
+
+ select package_id into v_package_id from forums_forums where forum_id = p_forum_id;
+
+ if v_package_id is null then
+ raise exception ''forums_message__new: forum_id % not found'', p_forum_id;
+ end if;
+
+ 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),
+ ''t'',
+ p_subject,
+ v_package_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;
+
+ insert into forums_messages
+ (message_id, forum_id, subject, content, format, user_id, parent_id, state, last_child_post, last_poster)
+ values
+ (v_message_id, p_forum_id, p_subject, p_content, p_format, p_user_id, p_parent_id,
+ v_state, current_timestamp, p_user_id);
+
+ update forums_forums
+ set last_post = current_timestamp
+ where forum_id = p_forum_id;
+
+ if p_parent_id is null then
+ if v_state = ''approved'' then
+ update forums_forums
+ set thread_count = thread_count + 1,
+ approved_thread_count = approved_thread_count + 1
+ where forum_id=p_forum_id;
+ else
+ update forums_forums
+ set thread_count = thread_count + 1
+ where forum_id=p_forum_id;
+ end if;
+ else
+ if v_state = ''approved'' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count + 1,
+ reply_count = reply_count + 1,
+ last_poster = p_user_id,
+ last_child_post = current_timestamp
+ where message_id = forums_message__root_message_id(v_message_id);
+ else
+ -- dont update last_poster, last_child_post when not approved
+ update forums_messages
+ set reply_count = reply_count + 1
+ where message_id = forums_message__root_message_id(v_message_id);
+ end if;
+ end if;
+
+ return v_message_id;
+
+end;' language 'plpgsql';
+
+select define_function_args ('forums_message__set_state', 'message_id,state');
+
+create or replace function forums_message__set_state(integer,varchar) returns integer as '
+declare
+ p_message_id alias for $1;
+ p_state alias for $2;
+ v_cur record;
+begin
+
+ select into v_cur *
+ from forums_messages
+ where message_id = p_message_id;
+
+ if v_cur.parent_id is null then
+ if p_state = ''approved'' and v_cur.state <> ''approved'' then
+ update forums_forums
+ set approved_thread_count = approved_thread_count + 1
+ where forum_id=v_cur.forum_id;
+ elsif p_state <> ''approved'' and v_cur.state = ''approved'' then
+ update forums_forums
+ set approved_thread_count = approved_thread_count - 1
+ where forum_id=v_cur.forum_id;
+ end if;
+ else
+ if p_state = ''approved'' and v_cur.state <> ''approved'' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count + 1,
+ last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end),
+ last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end)
+ where message_id = forums_message__root_message_id(v_cur.message_id);
+ elsif p_state <> ''approved'' and v_cur.state = ''approved'' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count - 1
+ where message_id = forums_message__root_message_id(v_cur.message_id);
+ end if;
+ end if;
+
+ update forums_messages
+ set state = p_state
+ where message_id = p_message_id;
+
+ return 0;
+
+end;' language 'plpgsql';
+
+select define_function_args ('forums_message__delete', 'message_id');
+
+create or replace function forums_message__delete (integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ v_cur record;
+begin
+
+ -- Maintain the forum thread counts
+
+ select into v_cur *
+ from forums_messages
+ where message_id = p_message_id;
+
+ if v_cur.parent_id is null then
+ if v_cur.state = ''approved'' then
+ update forums_forums
+ set thread_count = thread_count - 1,
+ approved_thread_count = approved_thread_count - 1
+ where forum_id=v_cur.forum_id;
+ else
+ update forums_forums
+ set thread_count = thread_count - 1
+ where forum_id=v_cur.forum_id;
+ end if;
+ elsif v_cur.state = ''approved'' then
+ update forums_messages
+ set approved_reply_count = approved_reply_count - 1,
+ reply_count = reply_count - 1
+ where message_id = forums_message__root_message_id(v_cur.message_id);
+ else
+ update forums_messages
+ set reply_count = reply_count - 1
+ where message_id = forums_message__root_message_id(v_cur.message_id);
+ end if;
+
+ perform acs_object__delete(p_message_id);
+ return 0;
+end;' language 'plpgsql';
Index: openacs-4/packages/forums/www/message-email.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/message-email.adp,v
diff -u -r1.8 -r1.9
--- openacs-4/packages/forums/www/message-email.adp 17 Dec 2003 15:45:30 -0000 1.8
+++ openacs-4/packages/forums/www/message-email.adp 15 Mar 2005 20:06:45 -0000 1.9
@@ -1,11 +1,19 @@
-#forums.Email_Message# @message.forum_name;noquote@ - @message.subject;noquote@
-@context;noquote@
+ #forums.Email_Message# @message.forum_name;noquote@ - @message.subject;noquote@
+ @context;noquote@
+
+
+
+
+
+
+
+
-
-
+
#forums.Confirm_Post_to_Forum# @forum.name;noquote@
@context;noquote@
+
+
+
+
+
+
+
+
#forums.Post_to_Forum# @forum.name;noquote@
@context;noquote@
message.subject
+
+
+
+
+
+
+
+
-#forums.Forum# @forum.name;noquote@: @message.subject;noquote@
-@context;noquote@
-
-
-
-@alternate_style_sheet;noquote@
-@dynamic_script;noquote@
-
-@message_id@
-
-
-
+ #forums.Thread_title#
+ @context;noquote@
+ @message_id@
+
+
+
+
+
+
+
+
+
+
+ @dynamic_script;noquote@
+
+
+
+
-nan
-
+
-
-
-
-
+
+
+
Index: openacs-4/packages/forums/www/messages-get.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/messages-get.tcl,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/forums/www/messages-get.tcl 27 Jan 2005 22:24:07 -0000 1.1
+++ openacs-4/packages/forums/www/messages-get.tcl 15 Mar 2005 20:06:45 -0000 1.2
@@ -1,110 +1,15 @@
-ad_page_contract {
- this assumes that all fetched messages are under one
- message root. it may fail when given differently rooted messages
+ad_page_contract {
} {
- cid:integer,multiple
+ cid:integer
{op noop}
- sid:integer
+ {sid nosid}
{dynamicmode:integer}
}
-forum::message::get -message_id $sid -array root_message
-set root_message_id $sid
-set forum_id $root_message(forum_id)
-forum::get -forum_id $forum_id -array forum
-
-if {$forum(posting_policy) == "moderated"} {
- set forum_moderated_p 1
-} else {
- set forum_moderated_p 0
-}
-
-set moderate_forum_p [forum::security::can_moderate_forum_p -forum_id $forum_id]
-if { $moderate_forum_p } {
- set table_name "forums_messages"
-} else {
- set table_name "forums_messages_approved"
-}
-
-set message_id_list [db_list select_message_ordering {
- select fma.message_id
- from forums_messages fm,
- forums_messages_approved fma
- where fm.message_id = :root_message_id
- and fma.forum_id = :forum_id
- and fma.tree_sortkey between fm.tree_sortkey and tree_right(fm.tree_sortkey)
- order by fma.message_id
-}]
-
-set direct_url_base [export_vars -base message-view { { message_id $root_message_id } }]
-
if {$dynamicmode} {
- set query "
- select message_id,
- (select count(*) from attachments where object_id = message_id) as n_attachments,
- subject,
- content,
- format,
- person__name(user_id) as user_name,
- to_char(posting_date, 'YYYY-MM-DD HH24:MI:SS') as posting_date_ansi,
- tree_level(tree_sortkey) as tree_level,
- state,
- user_id,
- parent_id,
- tree_sortkey,
- open_p,
- max_child_sortkey
- from $table_name
- where message_id in ([join $cid ","])
- order by tree_sortkey
-"
+ forum::message::get -message_id $cid -array message
+ set message(content) [ad_html_text_convert -from $message(format) -to text/html -- $message(content)]
} else {
- set query "
- select message_id,
- (select count(*) from attachments where object_id = message_id) as n_attachments,
- subject,
- format,
- person__name(user_id) as user_name,
- to_char(posting_date, 'YYYY-MM-DD HH24:MI:SS') as posting_date_ansi,
- tree_level(tree_sortkey) as tree_level,
- state,
- user_id,
- parent_id,
- tree_sortkey,
- open_p,
- max_child_sortkey
- from $table_name
- where message_id in ([join $cid ","])
- order by tree_sortkey
-"
+ set message(content) {}
}
-# convert emoticons to images if the parameter is set
-set convert_emoticons_p [string is true [parameter::get -parameter DisplayEmoticonsAsImagesP -default 0]]
-db_multirow -extend { reply_p moderate_p posting_date_pretty number parent_number direct_url parent_direct_url htmltext webeq links own_p} message get_messages $query {
- set number [expr [lsearch $message_id_list $message_id] + 1]
- set parent_number [expr [lsearch $message_id_list $parent_id] + 1]
- set posting_date_pretty [lc_time_fmt $posting_date_ansi "%x %X"]
- set direct_url "$direct_url_base\#$message_id"
- set parent_direct_url "$direct_url_base\#$parent_id"
- if {$dynamicmode} {
- set content [ad_html_text_convert -from $format -to text/html -- $content]
-
- if { $convert_emoticons_p } {
- set content [forum::format::emoticons -content $content]
- }
-
- set links "#forums.edit# Text"
-
- set moderate_p [forum::security::can_moderate_message_p -message_id $message_id]
- set reply_p [expr [string equal $open_p "t"] || [string equal $user_id [ad_conn user_id]]]
- set allow_edit_own_p [parameter::get -parameter AllowUsersToEditOwnPostsP -default 0]
- set own_p [expr [string equal $user_id [ad_conn user_id]] && $allow_edit_own_p]
- } else {
- set moderate_p 0
- set reply_p 0
- set own_p 0
- }
-}
-
-set viewer_id [ad_conn user_id]
Index: openacs-4/packages/forums/www/moderate/message-delete.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/moderate/message-delete.adp,v
diff -u -r1.8 -r1.9
--- openacs-4/packages/forums/www/moderate/message-delete.adp 17 Dec 2003 15:45:31 -0000 1.8
+++ openacs-4/packages/forums/www/moderate/message-delete.adp 15 Mar 2005 20:06:45 -0000 1.9
@@ -1,5 +1,18 @@
-#forums.Confirm_Delete# @message.subject;noquote@
-#forums.delete#
+ #forums.Confirm_Delete# @message.subject;noquote@
+ #forums.delete#
+
+
+
+
+
-
+
+ 
+
+
+
+ @message.number@:
+ @message.subject@
+
+
+
Index: openacs-4/packages/forums/lib/message/row.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/lib/message/row.tcl,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/forums/lib/message/row.tcl 27 Jan 2005 16:08:31 -0000 1.4
+++ openacs-4/packages/forums/lib/message/row.tcl 15 Mar 2005 20:06:45 -0000 1.5
@@ -1,7 +1,7 @@
ad_page_contract {
-
- a message chunk to be included in a listing of messages
+ a message chunk to be included in a table listing of messages
+
@author yon (yon@openforce.net)
@author arjun (arjun@openforce.net)
@creation-date 2002-06-02
@@ -33,61 +33,12 @@
if {[exists_and_not_null alt_template]} {
ad_return_template $alt_template
}
-
-
-if {![info exists message(message_id)]} {
+if {![info exists message(message_id)]} {
set message(message_id) none
}
-if {![info exists message(tree_level)]} {
+if {![info exists message(tree_level)]} {
set message(tree_level) 0
}
-if {![exists_and_not_null level_exp]} {
- set level_exp 0
-}
-
-if {![exists_and_not_null moderate_p]} {
- set moderate_p 0
-}
-
-if {![exists_and_not_null message(reply_p)]} {
- set message(reply_p) 0
-}
-
-if {![exists_and_not_null display_mode]} {
- set display_mode dynamic_minimal
-}
-
-if { $moderate_p } {
- set table_name "forums_messages"
-} else {
- set table_name "forums_messages_approved"
-}
-
-set links ""
-
set allow_edit_own_p [parameter::get -parameter AllowUsersToEditOwnPostsP -default 0]
set own_p [expr [string equal $message(user_id) $viewer_id] && $allow_edit_own_p]
-
-switch -exact $display_mode {
- flat {
- ad_return_template "../lib/message/row-flat"
- return
- }
- nested {
- ad_return_template "../lib/message/row-nested"
- return
- }
- threaded {
- ad_return_template "../lib/message/row-threaded"
- return
- }
- minimal {
- ad_return_template "../lib/message/row-minimal"
- return
- }
- default {
- ad_return_template "../lib/message/row"
- return
- }
-}
Index: openacs-4/packages/forums/lib/message/thread-chunk-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/lib/message/thread-chunk-oracle.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/forums/lib/message/thread-chunk-oracle.xql 27 Jan 2005 16:08:31 -0000 1.3
+++ openacs-4/packages/forums/lib/message/thread-chunk-oracle.xql 15 Mar 2005 20:06:45 -0000 1.4
@@ -21,7 +21,7 @@
0 as n_attachments,
subject,
content,
- html_p,
+ format,
person.name(user_id) as user_name,
to_char(posting_date, 'YYYY-MM-DD HH24:MI:SS') as posting_date_ansi,
tree.tree_level(tree_sortkey) as tree_level,
@@ -41,7 +41,7 @@
(select count(*) from attachments where object_id = message_id) as n_attachments,
subject,
content,
- html_p,
+ format,
person.name(user_id) as user_name,
to_char(posting_date, 'YYYY-MM-DD HH24:MI:SS') as posting_date_ansi,
tree.tree_level(tree_sortkey) as tree_level,
Index: openacs-4/packages/forums/lib/message/thread-chunk-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/lib/message/thread-chunk-postgresql.xql,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/forums/lib/message/thread-chunk-postgresql.xql 24 Feb 2005 13:33:19 -0000 1.4
+++ openacs-4/packages/forums/lib/message/thread-chunk-postgresql.xql 15 Mar 2005 20:06:45 -0000 1.5
@@ -42,7 +42,7 @@
(select count(*) from attachments where object_id = message_id) as n_attachments,
subject,
content,
- html_p,
+ format,
person__name(user_id) as user_name,
to_char(posting_date, 'YYYY-MM-DD HH24:MI:SS') as posting_date_ansi,
tree_level(tree_sortkey) as tree_level,
Index: openacs-4/packages/forums/lib/message/thread-chunk.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/lib/message/thread-chunk.adp,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/forums/lib/message/thread-chunk.adp 27 Jan 2005 16:08:31 -0000 1.4
+++ openacs-4/packages/forums/lib/message/thread-chunk.adp 15 Mar 2005 20:06:45 -0000 1.5
@@ -1,23 +1,17 @@
@message.subject@
+
-
+
-
- <%# ----------------------------------------------------------- %>
- <%# Preview %>
- <%# ----------------------------------------------------------- %>
-
-
+
+ @message.number@
-
-
-
-
-
-
-
-
-
-
- <%# ----------------------------------------------------------- %>
- <%# Expanded Message %>
- <%# ----------------------------------------------------------- %>
-
-
-
-
-
- <%# ----------------------------------------------------------- %>
- <%# Collapsed Message %>
- <%# ----------------------------------------------------------- %>
-
-
- (response to @message.parent_number@)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- #forums.reply#
- #forums.forward#
-
-
- #forums.edit#
-
-
- #forums.delete#
-
-
- #forums.approve#
-
-
- #forums.reject#
-
-
-
-
-
-
- @message.content;noquote@
-
-
-
-
-
-
-
- [open parent]
-
-
-
-
-
-
-
- @message.number@:
- @message.subject@
-
-
- @message.number@
-
-
- #forums.Posted_by# @message.user_name@ on @message.posting_date_pretty@
-
- @message.subject@
+
+ (response to @message.parent_number@)
+
+
#forums.Posted_by# @message.user_name@ on @message.posting_date_pretty@
- @message.content;noquote@
+
-
+
+
-
-
-
-
-
+
+
+
-
-
+ forum_moderated_p=@forum_moderated_p;noquote@
+ moderate_p=@permissions.moderate_p;noquote@
+ &message="responses">
+
+
@response_arrays_stub;noquote@
Index: openacs-4/packages/forums/lib/message/threads-chunk-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/lib/message/Attic/threads-chunk-postgresql.xql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/forums/lib/message/threads-chunk-postgresql.xql 13 Jan 2005 13:58:15 -0000 1.2
+++ openacs-4/packages/forums/lib/message/threads-chunk-postgresql.xql 15 Mar 2005 20:06:45 -0000 1.3
@@ -20,8 +20,8 @@
#forums.lt_Email_a_copy_of_the_f#
+#forums.lt_Email_a_copy_of_the_f#
-
+
-
@@ -22,29 +26,33 @@
- #forums.Back_to_thread_label# -
- @notification_chunk;noquote@ -
@notification_chunk;noquote@
--
-
- #forums.Post_a_Reply# +
- #forums.Reply_to_first_post_on_page_label# -
- #forums.Back_to_thread_link# -
-
-
-
- @message.number@:
- @message.subject@
- #forums.Posted_by# @message.user_name@ on @message.posting_date_pretty@
-
- (response to @message.parent_number@)
-
-
- 
-
-
-
-
- @message.number@:
- @message.subject@
-
- by @message.user_name@ on @message.posting_date_pretty@ -
- (response to @message.parent_number@)
-
-
- - by @message.user_name@ on @message.posting_date_pretty@ -
- @message.content;noquote@
-
-
-
-
-
- #forums.reply#
- #forums.forward#
-
-
- @message.links;noquote@
-
-
- #forums.delete#
-
-
- #forums.approve#
-
-
- #forums.reject#
-
-
- User Views
-
-
-
-
-
- [open parent]
-
-
-
- -
-
-
- @message.content;noquote@