Index: openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/general-comments/sql/postgresql/general-comments-create.sql	4 May 2001 09:41:11 -0000	1.1
@@ -0,0 +1,96 @@
+--
+-- packages/general-comments/sql/general-comments-create.sql
+--
+-- @author Phong Nguyen (phong@arsdigita.com)
+-- @creation-date 2000-10-12
+--
+-- @cvs-id $Id: general-comments-create.sql,v 1.1 2001/05/04 09:41:11 pascals Exp $
+--
+-- General comments: Commenting facility for any object in ACS 4.0
+-- 
+
+-- create a table to extend cr_items
+create table general_comments (
+    comment_id  integer constraint general_comments_comment_id_fk
+                references acs_messages (message_id) on delete cascade 
+                constraint general_comments_pk
+                primary key,
+    object_id   integer constraint general_comments_object_id_fk
+	        references acs_objects (object_id) on delete cascade,
+    category    varchar(1000)
+);
+comment on table general_comments is '
+    Extends the acs_messages table to hold item level data.
+'; 
+comment on column general_comments.object_id is '
+    The id of the object to associate message with
+';
+comment on column general_comments.category is '
+    This feature is not complete. The purpose is to allow separation of 
+    comments into categories.  
+';
+
+-- create an index on foreign key constraint
+create index general_comments_object_id_idx on general_comments (object_id);
+
+create function inline_0 ()
+returns integer as '
+-- define and grant privileges
+declare
+    registered_users acs_objects.object_id%TYPE;
+    default_context  acs_objects.object_id%TYPE;
+begin
+
+    -- retreive object ids for magic objects
+    registered_users := acs.magic_object_id(''registered_users'');
+    default_context  := acs.magic_object_id(''default_context'');
+
+    -- create privileges
+    acs_privilege.create_privilege(''general_comments_create'');
+
+    -- associte privileges to global privileges
+    acs_privilege.add_child(''create'',''general_comments_create'');
+    
+    -- allow registered users to create comments
+    acs_permission.grant_permission (
+       object_id  => default_context,
+       grantee_id => registered_users,
+       privilege  => ''general_comments_create''
+    );
+
+end;' language 'plpgsql';
+
+select inline_0 ();
+
+drop function inline_0 ();
+
+-- show errors
+
+-- NOTE: this is only temporary until we figure out how
+--       packages will register child types to an acs-message
+create function inline_1 ()
+returns integer as '
+begin
+
+    content_type.register_child_type (
+        parent_type => ''acs_message_revision'',
+        child_type  => ''content_revision''
+    );
+    content_type.register_child_type (
+        parent_type => ''acs_message_revision'',
+        child_type  => ''image''
+    );
+    content_type.register_child_type (
+        parent_type => ''acs_message_revision'',
+        child_type  => ''content_extlink''
+    );
+
+end;' language 'plpgsql';
+
+select inline_1 ();
+
+drop function inline_1 ();
+
+-- show errors
+
+
Index: openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/general-comments/sql/postgresql/general-comments-drop.sql	4 May 2001 09:41:11 -0000	1.1
@@ -0,0 +1,45 @@
+--
+-- packages/general-comments/sql/general-comments-drop.sql
+--
+-- @author Phong Nguyen phong@arsdigita.com
+-- @creation-date 2000-10-12
+--
+-- @cvs-id $Id: general-comments-drop.sql,v 1.1 2001/05/04 09:41:11 pascals Exp $
+--
+
+
+-- revoke all 'general_comments_create' permissions
+delete from 
+    acs_permissions
+where 
+    privilege = 'general_comments_create';
+
+-- remove create privilege from the system
+begin
+    acs_privilege.remove_child('create','general_comments_create');
+    acs_privilege.drop_privilege('general_comments_create');
+end;
+/
+
+-- remove all comments from the system
+declare
+    cursor comment_cur is
+        select comment_id
+          from general_comments;
+begin
+    for comment_rec in comment_cur loop
+        -- There is a bug in content_item.delete that results in
+        -- referential integrity violations when deleting a content
+        -- item that has an image attachment. This is a temporary fix
+        -- until ACS 4.1 is released.
+        delete from images
+        where image_id in (select latest_revision
+                             from cr_items
+                            where parent_id = comment_rec.comment_id);
+
+        acs_message.delete(comment_rec.comment_id);
+    end loop;
+end;
+/
+
+drop table general_comments;