Index: openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql	8 Jul 2001 18:40:49 -0000	1.2
+++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql	13 Aug 2001 17:54:46 -0000	1.3
@@ -39,19 +39,18 @@
 declare
 	p_gc_object_id	alias for $1;
 begin
-     delete from acs_mail_gc_objects
+    delete from acs_mail_gc_objects
 		where gc_object_id = p_gc_object_id;
+
 	perform acs_object__delete( p_gc_object_id );
 
     return 1;
 end;
 ' language 'plpgsql';
 
--- end acs_mail_gc_object
+-- first create a CR item.
+-- then call acs_mail_body__new with the CR item's item_id
 
----
--- create or replace package body acs_mail_body
-
 create function acs_mail_body__new (integer,integer,integer,timestamp,varchar,varchar,text,text,text,integer,varchar,date,integer,varchar,integer)
 returns integer as ' 
 declare
@@ -64,16 +63,17 @@
 	p_header_subject    alias for $7;    -- default null
 	p_header_from       alias for $8;    -- default null
 	p_header_to         alias for $9;    -- default null
-	p_content_object_id alias for $10;   -- default null
+	p_content_item_id   alias for $10;   -- default null
 	p_object_type       alias for $11;   -- default acs_mail_body
 	p_creation_date     alias for $12;   -- default now()
 	p_creation_user     alias for $13;   -- default null
 	p_creation_ip       alias for $14;   -- default null
 	p_context_id        alias for $15;   -- default null
-    v_object_id       integer;
+    v_object_id         integer;
 	v_system_url		varchar;
 	v_domain_name		varchar;
 	v_idx				integer;
+	v_header_message_id	acs_mail_bodies.header_message_id%TYPE;
 begin
 
      v_object_id := acs_mail_gc_object__new (
@@ -91,18 +91,18 @@
 	v_idx := position(''http://'' in v_system_url);
 	v_domain_name := trim (substr(v_system_url, v_idx + 7));
 
-	v_header_message_id := coalesce (header_message_id,
+	v_header_message_id := coalesce (p_header_message_id,
 		current_date || ''.'' || v_object_id || ''@'' || 
 		v_domain_name || ''.sddd'');
 
     insert into acs_mail_bodies
         (body_id, body_reply_to, body_from, body_date, 
           header_message_id, header_reply_to, header_subject, header_from,
-		 header_to, content_object_id)
+		 header_to, content_item_id)
     values
          (v_object_id, p_body_reply_to, p_body_from, p_body_date,
-          p_header_message_id, p_header_reply_to, p_header_subject, p_header_from,
-          p_header_to, p_content_object_id);
+          v_header_message_id, p_header_reply_to, p_header_subject, p_header_from,
+          p_header_to, p_content_item_id);
 
      return v_object_id;
 end;
@@ -121,11 +121,11 @@
 ' language 'plpgsql';
 
 create function acs_mail_body__body_p(integer) 
-returns char as '
+returns boolean as '
 	p_object_id		alias for $1;
     v_check_body_id integer;
 begin
-	select case when count(body_id)=0 then 0 else 1 end into v_check_body_id
+	select count(body_id) into v_check_body_id
 	  from acs_mail_bodies where body_id = p_object_id;
 
      if v_check_body_id <> 0 then
@@ -147,7 +147,7 @@
 	p_creation_user     alias for $5;    -- default null
 	p_creation_ip       alias for $6;    -- default null
 	p_context_id        alias for $7;    -- default null
-    v_object_id       integer;
+    v_object_id         integer;
     v_body_reply_to		integer;
     v_body_from         integer;
     v_body_date         timestamp;
@@ -156,14 +156,14 @@
     v_header_subject    text;
     v_header_from       text;
     v_header_to         text;
-    v_content_object_id integer;
+    v_content_item_id integer;
 begin
      select body_reply_to, body_from, body_date,
             header_reply_to, header_subject, header_from, header_to,
-            content_object_id
+            content_item_id
          into v_body_reply_to, v_body_from, v_body_date,
             v_header_reply_to, v_header_subject, v_header_from, v_header_to,
-            v_content_object_id
+            v_content_item_id
          from acs_mail_bodies
          where body_id = p_old_body_id;
 
@@ -176,7 +176,7 @@
 		v_header_subject,		-- header_subject 
 		v_header_from,			-- header_from 
 		v_header_to,			-- header_to 
-		v_content_object_id,	-- content_object_id 
+		v_content_item_id,		-- content_item_id 
 		p_object_type,			-- object_type 
 		p_creation_date,		-- creation_date 
 		p_creation_user,		-- creation_user 
@@ -192,10 +192,10 @@
 returns integer as '
 declare
 	p_body_id				alias for $1;
-	p_content_object_id		alias for $2;
+	p_content_item_id		alias for $2;
 begin
     update acs_mail_bodies
-        set content_object_id = p_content_object_id
+        set content_item_id = p_content_item_id
         where body_id = p_body_id;
 
     return 1;
@@ -215,14 +215,14 @@
     p_creation_user		alias for $5;    -- default null
     p_creation_ip		alias for $6;    -- default null
     p_context_id		alias for $7;    -- default null
-    v_object_id    integer;
+    v_object_id			integer;
 begin
     v_object_id := acs_mail_gc_object__new (
-		p_multipart_id,	-- gc_object_id 
-		p_object_type,	-- object_type 
+		p_multipart_id,		-- gc_object_id 
+		p_object_type,		-- object_type 
 		p_creation_date,	-- creation_date 
 		p_creation_user,	-- creation_user 
-		p_creation_ip,	-- creation_ip 
+		p_creation_ip,		-- creation_ip 
 		p_context_id		-- context_id 
     );
 	
@@ -250,11 +250,10 @@
 returns boolean as '
 declare
 	p_object_id				alias for $1;
-    v_check_multipart_id integer;
+    v_check_multipart_id    integer;
 begin
-	select case when count(multipart_id) = 0 then 0 else 1 end 
-		   into v_check_multipart_id
-        from acs_mail_multiparts
+	select count(multipart_id) into v_check_multipart_id
+      from acs_mail_multiparts
 	  where multipart_id = p_object_id;
 
     if v_check_multipart_id <> 0 then
@@ -273,9 +272,9 @@
 returns integer as ' 
 declare
 	p_multipart_id			alias for $1;
-    p_content_object_id		alias for $2;
-    v_multipart_id    integer;
-    v_max_num         integer;
+    p_content_item_id		alias for $2;
+    v_multipart_id			integer;
+    v_max_num				integer;
 begin
     -- get a row lock on the multipart item
     select multipart_id into v_multipart_id from acs_mail_multiparts
@@ -286,18 +285,18 @@
         where multipart_id = p_multipart_id;
 
     insert into acs_mail_multipart_parts
-        (multipart_id, sequence_number, content_object_id)
+        (multipart_id, sequence_number, content_item_id)
     values
-        (p_multipart_id, v_max_num + 1, p_content_object_id);
+        (p_multipart_id, v_max_num + 1, p_content_item_id);
 
+	return v_max_num + 1;
 end;
 ' language 'plpgsql';
 
 --end acs_mail_multipart;
 
 --create or replace package body acs_mail_link__
-create function acs_mail_link__new (integer,integer,integer,timestamp,
-integer,varchar,varchar)
+create function acs_mail_link__new (integer,integer,integer,timestamp,integer,varchar,varchar)
 returns integer as '
 declare
 	p_mail_link_id			alias for $1;    -- default null
@@ -307,23 +306,23 @@
 	p_creation_user			alias for $5;    -- default null
 	p_creation_ip			alias for $6;    -- default null
 	p_object_type			alias for $7;    -- default acs_mail_link
-    v_object_id     integer;
+    v_mail_link_id			acs_mail_links.mail_link_id%TYPE;
 begin
-    v_object_id := acs_object__new (
-		mail_link_id,	-- object_id 
-		context_id,		-- context_id 
-		creation_date,	-- creation_date 
-		creation_user,	-- creation_user 
-		creation_ip,	-- creation_ip 
-		object_type		-- object_type 
+    v_mail_link_id := acs_object__new (
+		p_mail_link_id,		-- object_id 
+		p_object_type,		-- object_type 
+		p_creation_date,	-- creation_date 
+		p_creation_user,	-- creation_user 
+		p_creation_ip,		-- creation_ip 
+		p_context_id		-- context_id 
     );
 
     insert into acs_mail_links 
 	 ( mail_link_id, body_id )
 	values 
-	 ( v_object_id, p_body_id );
+	 ( v_mail_link_id, p_body_id );
 
-    return v_object_id;
+    return v_mail_link_id;
 end;
 ' language 'plpgsql';
 
@@ -332,9 +331,6 @@
 declare
 	p_mail_link_id			alias for $1;
 begin
-    delete from acs_mail_links
-        where mail_link_id = p_mail_link_id;
-
 	perform acs_object__delete( p_mail_link_id );
 
     return 1;
@@ -345,11 +341,11 @@
 returns boolean as '
 declare
 	p_object_id				alias for $1;
-    v_check_link_id integer;
+    v_check_link_id			integer;
 begin
-    select case when count(mail_link_id) = 0 then 0 else 1 end into v_check_link_id
-        from acs_mail_links
-        where mail_link_id = p_object_id;
+    select count(mail_link_id) into v_check_link_id
+      from acs_mail_links
+      where mail_link_id = p_object_id;
 
     if v_check_link_id <> 0 then
         return ''t'';
