Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql,v
diff -u -r1.1 -r1.1.1.1
--- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql	25 Mar 2004 15:59:30 -0000	1.1
+++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.1d1-1.1d2.sql	28 Jun 2006 20:30:28 -0000	1.1.1.1
@@ -1,73 +1,119 @@
--- propigate title and package_id to acs_objects.
-create or replace function bookshelf_book__new (
-  integer, -- book_id
-  varchar, -- object_type
-  integer, -- package_id
-  varchar, -- isbn
-  text,    -- book_author
-  text,    -- book_title
-  text,    -- main_entry
-  text,    -- additional_entry
-  text,    -- excerpt
-  varchar, -- publish_status
-  varchar, -- read_status
-  date,    -- creation_date
-  integer, -- creation_user
-  varchar, -- creation_ip       
-  integer  -- context_id
-)
+-- replace new functions with ones that set acs_object.title, package_id
+
+create or replace function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamptz,integer,varchar,integer)
 returns integer as '
 declare
-    p_book_id                       alias for $1;
+    p_forum_id                      alias for $1;
     p_object_type                   alias for $2;
-    p_package_id                    alias for $3;
-    p_isbn                          alias for $4;
-    p_book_author                   alias for $5;
-    p_book_title                    alias for $6;
-    p_main_entry                    alias for $7;
-    p_additional_entry              alias for $8;
-    p_excerpt                       alias for $9;
-    p_publish_status                alias for $10;
-    p_read_status                   alias for $11;
-    p_creation_date                 alias for $12;
-    p_creation_user                 alias for $13;
-    p_creation_ip                   alias for $14;
-    p_context_id                    alias for $15;
-    v_book_id                       integer;
-    v_book_no                       integer;
-    v_creation_date                 date;
+    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
-    if p_creation_date is null then
-        v_creation_date := now();
-    else
-        v_creation_date := p_creation_date;
-    end if;
-
-    v_book_id := acs_object__new(
-        p_book_id,
+    v_forum_id:= acs_object__new(
+        p_forum_id,
         p_object_type,
-        v_creation_date,
+        p_creation_date,
         p_creation_user,
         p_creation_ip,
         coalesce(p_context_id, p_package_id),
         ''t'',
-        p_book_title,
+        p_name,
         p_package_id
     );
 
-    select coalesce(max(book_no),0) + 1
-    into   v_book_no
-    from   bookshelf_books
-    where  package_id = p_package_id;
-
-    insert into bookshelf_books
-    (book_id, book_no, isbn, book_author, book_title, main_entry, additional_entry, excerpt, 
-     publish_status, read_status, package_id)
+    insert into forums_forums
+    (forum_id, name, charter, presentation_type, posting_policy, package_id)
     values
-    (v_book_id, v_book_no, p_isbn, p_book_author, p_book_title, p_main_entry, p_additional_entry, p_excerpt,
-     p_publish_status, p_read_status, p_package_id);
+    (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id);
 
-    return v_book_id;
+    return v_forum_id;
 end;
 ' language 'plpgsql';
 
+
+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;
+    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;
+
+    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';
