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.1 -r1.1.1.1
--- openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql	1 Jun 2002 19:02:29 -0000	1.1
+++ openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql	28 Jun 2006 20:30:28 -0000	1.1.1.1
@@ -1,4 +1,3 @@
-
 --
 -- The Forums Package
 --
@@ -9,74 +8,95 @@
 -- lifted from Gilbert. Thanks Orchard Labs!
 --
 
--- privileges
--- NO PRIVILEGES FOR MESSAGES
--- we don't individually permission messages
-
---
--- The Data Model
---
-
 create table forums_messages (
-       message_id               integer not null
-                                constraint forums_message_id_fk
-                                references acs_objects(object_id)
-                                constraint forums_message_id_pk
-                                primary key,
-       forum_id                 integer
-                                constraint forums_mess_forum_id_fk
-                                references forums_forums(forum_id),
-       subject                  varchar(200),
-       content                  text,
-       -- html_p only applies to the body. The subject is plaintext.
-       html_p                   char(1) default 'f'
-                                constraint forums_mess_html_p_ch
-                                check (html_p in ('t','f'))
-                                constraint forums_mess_html_p_nn not null,
-       user_id                  integer
-                                constraint forums_mess_user_id_fk
-                                references users(user_id)
-                                constraint forums_mess_user_id_nn
-                                not null,
-       posting_date             timestamp
-                                constraint forum_mess_post_date_nn not null,
-       state                    varchar(100)
-                                constraint forum_mess_state_ch check (state in ('pending','approved','rejected')),
-       -- Hierarchy of messages
-       parent_id                integer
-                                constraint forum_mess_parent_id_fk
-                                references forums_messages(message_id),
-       open_p                   char(1) default 't' not null
-                                constraint forum_mess_open_p_ch check (open_p in ('t','f')),
-       tree_sortkey             varbit,
-       max_child_sortkey        varbit,
-       constraint forums_mess_sk_forum_un
-       unique (tree_sortkey,forum_id)
+    message_id                      integer
+                                    constraint forums_message_id_fk
+                                    references acs_objects (object_id)
+                                    constraint forums_messages_pk
+                                    primary key,
+    forum_id                        integer
+                                    constraint forums_mess_forum_id_fk
+                                    references forums_forums (forum_id),
+    subject                         varchar(200),
+    content                         text,
+    user_id                         integer
+                                    constraint forums_mess_user_id_fk
+                                    references users(user_id)
+                                    constraint forums_mess_user_id_nn
+                                    not null,
+    posting_date                    timestamptz
+                                    default current_timestamp
+                                    constraint forum_mess_post_date_nn
+                                    not null,
+    state                           varchar(100)
+                                    constraint forum_mess_state_ck
+                                    check (state in ('pending','approved','rejected')),
+    format                          varchar(30)
+                                    default 'text/plain',
+    -- Hierarchy of messages
+    parent_id                       integer
+                                    constraint forum_mess_parent_id_fk
+                                    references forums_messages (message_id),
+    open_p                          char(1)
+                                    default 't'
+                                    constraint forum_mess_open_p_nn
+                                    not null
+                                    constraint forum_mess_open_p_ck
+                                    check (open_p in ('t','f')),
+    tree_sortkey                    varbit,
+    max_child_sortkey               varbit,
+    last_child_post                 timestamptz,
+    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)
 );
 
--- views
+-- We do a some big queries on forum_id (thread count on index.tcl) so create a second index 
+-- ordered so it's useful for them
+create unique index forums_mess_forum_sk_un on forums_messages(forum_id, tree_sortkey);
+-- 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);
+create index forum_messages_date_idx on forums_messages (forum_id, posting_date);
 
-create view forums_messages_approved as
-select * from forums_messages where state='approved';
+create or replace view forums_messages_approved
+as
+    select *
+    from forums_messages
+    where state = 'approved';
 
-create view forums_messages_pending as
-select * from forums_messages where state='pending';
+create or replace view forums_messages_pending
+as
+    select *
+    from forums_messages
+    where state= 'pending';
 
---
--- Object Type
---
-
+create or replace function inline_0 ()
+returns integer as '
 begin
-        select acs_object_type__create_type (
-            'forums_message',
-            'Forums Message',
-            'Forums Messages',
-            'acs_object',
-            'forums_messages',
-            'message_id',
-            'forums_message',
-	    'f',
-	    NULL,
-	    NULL
-        );
-end;
+    perform acs_object_type__create_type(
+        ''forums_message'',
+        ''Forums Message'',
+        ''Forums Messages'',
+        ''acs_object'',
+        ''forums_messages'',
+        ''message_id'',
+        ''forums_message'',
+        ''f'',
+        null,
+        ''forums_message__name''
+    );
+
+    return null;
+end;' language 'plpgsql';
+
+select inline_0();
+drop function inline_0 ();