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)
