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.15 -r1.16 --- openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 29 Jun 2004 10:18:29 -0000 1.15 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 13 Jan 2005 13:58:16 -0000 1.16 @@ -12,9 +12,12 @@ -- chunks lifted from Gilbert. Thanks Orchard Labs! -- -select define_function_args ('forums_message__new', 'message_id,object_type;forums_message,forum_id,subject,content,format,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); +select define_function_args ('forums_message__new', 'message_id,object_type;forums_message,forum_id,subject,content,format,user_id,state,parent_id,creation_date,creation_user,creation_ip,context_id'); -create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer) +-- Get rid of the old version so we'll throw an error if the admin forgets to reboot +-- OpenACS after the upgrade (package_instantiate_object caches param lists) + +create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,varchar,varchar,integer,timestamptz,integer,varchar,integer) returns integer as ' declare p_message_id alias for $1; @@ -24,13 +27,12 @@ p_content alias for $5; p_format 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; + 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; @@ -70,29 +72,45 @@ 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, format, user_id, posting_date, parent_id, state) + (message_id, forum_id, subject, content, format, user_id, parent_id, state, last_child_post) values - (v_message_id, p_forum_id, p_subject, p_content, p_format, p_user_id, v_posting_date, p_parent_id, v_state); + (v_message_id, p_forum_id, p_subject, p_content, p_format, p_user_id, p_parent_id, + v_state, current_timestamp); update forums_forums - set last_post = v_posting_date + set last_post = current_timestamp 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); + 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_child_post = current_timestamp + where message_id = forums_message__root_message_id(v_message_id); + else + update forums_messages + set reply_count = reply_count + 1, + last_child_post = current_timestamp + where message_id = forums_message__root_message_id(v_message_id); + end if; + end if; return v_message_id; -end; -' language 'plpgsql'; +end;' language 'plpgsql'; select define_function_args ('forums_message__root_message_id', 'message_id'); @@ -173,18 +191,91 @@ 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 + 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; + p_message_id alias for $1; + v_cur record; begin - perform acs_object__delete(p_message_id); - return 0; -end; -' language 'plpgsql'; + -- 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'; + select define_function_args ('forums_message__delete_thread', 'message_id'); create or replace function forums_message__delete_thread (integer) @@ -215,16 +306,17 @@ and tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey) order by tree_sortkey desc loop + -- Avoid the count bookkeeping down in forums_message__delete perform forums_message__delete(v_message.message_id); end loop; -- delete the message itself perform forums_message__delete(p_message_id); return 0; -end; -' language 'plpgsql'; +end;' language 'plpgsql'; + select define_function_args('forums_message__name','message_id'); create or replace function forums_message__name (integer)