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.1 -r1.2
--- openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql	23 May 2001 16:43:30 -0000	1.1
+++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql	8 Jul 2001 18:40:49 -0000	1.2
@@ -11,35 +11,38 @@
 create function acs_mail_gc_object__new (integer,varchar,timestamp,integer,varchar,integer)
 returns integer as '
 declare
-    gc_object_id  alias for $1;    -- default null
-    object_type   alias for $2;    -- default acs_mail_gc_object
-    creation_date alias for $3;    -- default now
-    creation_user alias for $4;    -- default null
-    creation_ip   alias for $5;    -- default null
-    context_id    alias for $6;    -- default null
+    p_gc_object_id  alias for $1;    -- default null
+    p_object_type   alias for $2;    -- default acs_mail_gc_object
+    p_creation_date alias for $3;    -- default now
+    p_creation_user alias for $4;    -- default null
+    p_creation_ip   alias for $5;    -- default null
+    p_context_id    alias for $6;    -- default null
     v_object_id   integer;
- begin
+begin
     v_object_id := acs_object__new (
-        object_id => gc_object_id,
-        object_type => object_type,
-        creation_date => creation_date,
-        creation_user => creation_user,
-        creation_ip => creation_ip,
-        context_id => context_id
+		p_gc_object_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_gc_objects values ( v_object_id );
+
     return v_object_id;
- end;
+end;
 ' language 'plpgsql';
 
 create function acs_mail_gc_object__delete(integer) 
 returns integer as '
 declare
-    gc_object_id alias for $1;
+	p_gc_object_id	alias for $1;
 begin
      delete from acs_mail_gc_objects
-         where gc_object_id = acs_mail_gc_object.delete.gc_object_id;
-     acs_object__delete(gc_object_id);
+		where gc_object_id = p_gc_object_id;
+	perform acs_object__delete( p_gc_object_id );
+
     return 1;
 end;
 ' language 'plpgsql';
@@ -49,52 +52,58 @@
 ---
 -- create or replace package body acs_mail_body
 
--- note for docs that I am making header_message_id mandatory
--- jag
-drop function acs_mail_body__new (integer,integer,integer,timestamp,varchar,varchar,text,text,text,integer,varchar,date,integer,varchar,integer);
-
 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
-    body_id           alias for $1;    -- default null
-    body_reply_to     alias for $2;    -- default null
-    body_from         alias for $3;    -- default null
-    body_date         alias for $4;    -- default null
-    header_message_id alias for $5;    -- default null
-    header_reply_to   alias for $6;    -- default null
-    header_subject    alias for $7;    -- default null
-    header_from       alias for $8;    -- default null
-    header_to         alias for $9;    -- default null
-    content_object_id alias for $10;   -- default null
-    object_type       alias for $11;   -- default acs_mail_body
-    creation_date     alias for $12;   -- default now()
-    creation_user     alias for $13;   -- default null
-    creation_ip       alias for $14;   -- default null
-    context_id        alias for $15;   -- default null
+	p_body_id			alias for $1;    -- default null
+	p_body_reply_to		alias for $2;    -- default null
+	p_body_from			alias for $3;    -- default null
+	p_body_date			alias for $4;    -- default null
+	p_header_message_id	alias for $5;    -- default null
+	p_header_reply_to   alias for $6;    -- default null
+	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_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;
- begin
+	v_system_url		varchar;
+	v_domain_name		varchar;
+	v_idx				integer;
+begin
 
-    if header_message_id is null or header_message_id = '''' then 
-        raise EXCEPTION ''-20100: You didn''t supply a header_message_id'';
-    end if;
-
      v_object_id := acs_mail_gc_object__new (
-         gc_object_id => body_id,
-         object_type => object_type,
-         creation_date => creation_date,
-         creation_user => creation_user,
-         creation_ip => creation_ip,
-         context_id => context_id
+		p_body_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_context_id		-- context_id 
      );
 
+	-- vinodk: get SystemURL parameter and use it to extract domain name
+	select apm__get_value(package_id, ''SystemURL'') into v_system_url
+		from apm_packages where package_key=''acs-kernel'';
+	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,
+		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)
-    values
-         (v_object_id, body_reply_to, body_from, body_date,
+        (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_object_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);
+
      return v_object_id;
 end;
 ' language 'plpgsql';
@@ -103,91 +112,92 @@
 create function acs_mail_body__delete(integer)
 returns integer as ' 
 declare
-    body_id alias for $1;
+	p_body_id		alias for $1;
 begin
-    PERFORM acs_mail_gc_object__delete(body_id);
+	perform acs_mail_gc_object__delete( p_body_id );
 
     return 1;
 end;
 ' language 'plpgsql';
 
 create function acs_mail_body__body_p(integer) 
 returns char as '
-    object_id alias for $1;
+	p_object_id		alias for $1;
     v_check_body_id integer;
 begin
-     select case when (count(body_id)=0 then 0 else 1) into v_check_body_id
-         from acs_mail_bodies
-         where body_id = object_id;
+	select case when count(body_id)=0 then 0 else 1 end into v_check_body_id
+	  from acs_mail_bodies where body_id = p_object_id;
+
      if v_check_body_id <> 0 then
          return ''t'';
      else
          return ''f'';
      end if;
- end;
+end;
 ' language 'plpgsql';
 
 create function acs_mail_body__clone (integer,integer,varchar,timestamp,
 integer,varchar,integer) 
 returns integer as '
 declare 
-    old_body_id       alias for $1;
-    body_id           alias for $2;    -- default null
-    object_type       alias for $3;    -- default acs_mail_body
-    creation_date     alias for $4;    -- default now()
-    creation_user     alias for $5;    -- default null
-    creation_ip       alias for $6;    -- default null
-    context_id        alias for $7;    -- default null
+	p_old_body_id       alias for $1;
+	p_body_id           alias for $2;    -- default null
+	p_object_type       alias for $3;    -- default acs_mail_body
+	p_creation_date     alias for $4;    -- default now()
+	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;
-    body_reply_to     integer;
-    body_from         integer;
-    body_date         timestamp;
-    header_message_id varchar;
-    header_reply_to   varchar;
-    header_subject    text;
-    header_from       text;
-    header_to         text;
-    content_object_id integer;
- begin
+    v_body_reply_to		integer;
+    v_body_from         integer;
+    v_body_date         timestamp;
+    v_header_message_id varchar;
+    v_header_reply_to   varchar;
+    v_header_subject    text;
+    v_header_from       text;
+    v_header_to         text;
+    v_content_object_id integer;
+begin
      select body_reply_to, body_from, body_date,
             header_reply_to, header_subject, header_from, header_to,
             content_object_id
-         into body_reply_to, body_from, body_date,
-            header_reply_to, header_subject, header_from, header_to,
-            content_object_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
          from acs_mail_bodies
-         where body_id = old_body_id;
+         where body_id = p_old_body_id;
+
      v_object_id := acs_mail_body__new (
-         body_id => body_id,
-         body_reply_to => body_reply_to,
-         body_from => body_from,
-         body_date => body_date,
-         header_reply_to => header_reply_to,
-         header_subject => header_subject,
-         header_from => header_from,
-         header_to => header_to,
-         content_object_id => content_object_id,
-         object_type => object_type,
-         creation_date => creation_date,
-         creation_user => creation_user,
-         creation_ip => creation_ip,
-         context_id => context_id
+		p_body_id,				-- body_id 
+		v_body_reply_to,		-- body_reply_to 
+		v_body_from,			-- body_from 
+		v_body_date,			-- body_date 
+		v_header_reply_to,		-- header_reply_to 
+		v_header_subject,		-- header_subject 
+		v_header_from,			-- header_from 
+		v_header_to,			-- header_to 
+		v_content_object_id,	-- content_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 
      );
+
      return v_object_id;
 end;
 ' language 'plpgsql';
 
--- had to truncate the proc name from 
--- acs_mail_body__set_content_object
-create function acs_mail_body__set_content_obj (integer,integer) 
+create function acs_mail_body__set_content_object (integer,integer) 
 returns integer as '
 declare
-    body_id           alias for $1;
-    content_object_id alias for $2;
+	p_body_id				alias for $1;
+	p_content_object_id		alias for $2;
 begin
     update acs_mail_bodies
-        set content_object_id = set_content_object.content_object_id
-        where body_id = set_content_object.body_id;
+        set content_object_id = p_content_object_id
+        where body_id = p_body_id;
+
     return 1;
 end;
 ' language 'plpgsql';
@@ -198,48 +208,55 @@
 timestamp,integer,varchar,integer) 
 returns integer as '
 declare
-    multipart_id   alias for $1;    -- default null,
-    multipart_kind alias for $2;
-    object_type    alias for $3;    -- default acs_mail_multipart
-    creation_date  alias for $4;    -- default now()
-    creation_user  alias for $5;    -- default null
-    creation_ip    alias for $6;    -- default null
-    context_id     alias for $7;    -- default null
+	p_multipart_id		alias for $1;    -- default null,
+	p_multipart_kind	alias for $2;
+    p_object_type		alias for $3;    -- default acs_mail_multipart
+    p_creation_date		alias for $4;    -- default now()
+    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;
 begin
     v_object_id := acs_mail_gc_object__new (
-        gc_object_id => multipart_id,
-        object_type => object_type,
-        creation_date => creation_date,
-        creation_user => creation_user,
-        creation_ip => creation_ip,
-        context_id => context_id
+		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_context_id		-- context_id 
     );
-    insert into acs_mail_multiparts (multipart_id, multipart_kind)
-        values (v_object_id, multipart_kind);
+	
+	insert into acs_mail_multiparts 
+	 (multipart_id, multipart_kind)
+	values 
+	 (v_object_id, p_multipart_kind);
+
     return v_object_id;
 end;
 ' language 'plpgsql';
 
 create function acs_mail_multipart__delete (integer)
 returns integer as '
 declare
-    multipart_id alias for $1;
+	p_multipart_id		alias for $1;
 begin
-    acs_mail_gc_object__delete(multipart_id);
+	perform acs_mail_gc_object__delete( p_multipart_id );
+
     return 1;
 end;
 ' language 'plpgsql';
 
 create function acs_mail_multipart__multipart_p (integer)
 returns boolean as '
 declare
-    object_id alias for $1;
+	p_object_id				alias for $1;
     v_check_multipart_id integer;
 begin
-    select (case when count(multipart_id) = 0 then 0 else 1 end) into v_check_multipart_id
+	select case when count(multipart_id) = 0 then 0 else 1 end 
+		   into v_check_multipart_id
         from acs_mail_multiparts
-        where multipart_id = object_id;
+	  where multipart_id = p_object_id;
+
     if v_check_multipart_id <> 0 then
         return ''t'';
     else
@@ -255,21 +272,24 @@
 create function acs_mail_multipart__add_content (integer,integer)
 returns integer as ' 
 declare
-    multipart_id      alias for $1;
-    content_object_id alias for $2;
+	p_multipart_id			alias for $1;
+    p_content_object_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
-        where multipart_id = add_content.multipart_id for update;
+        where multipart_id = p_multipart_id for update;
+
     select coalesce(max(sequence_number),0) into v_max_num
         from acs_mail_multipart_parts
-        where multipart_id = add_content.multipart_id;
+        where multipart_id = p_multipart_id;
+
     insert into acs_mail_multipart_parts
         (multipart_id, sequence_number, content_object_id)
     values
-         (multipart_id, v_max_num + 1, content_object_id);
+        (p_multipart_id, v_max_num + 1, p_content_object_id);
+
 end;
 ' language 'plpgsql';
 
@@ -280,56 +300,63 @@
 integer,varchar,varchar)
 returns integer as '
 declare
-    mail_link_id    alias for $1;    -- default null
-    body_id         alias for $2;
-    context_id      alias for $3;    -- default null
-    creation_date   alias for $4;    -- default now()
-    creation_user   alias for $5;    -- default null
-    creation_ip     alias for $6;    -- default null
-    object_type     alias for $7;    -- default acs_mail_link
+	p_mail_link_id			alias for $1;    -- default null
+	p_body_id				alias for $2;
+	p_context_id			alias for $3;    -- default null
+	p_creation_date			alias for $4;    -- default now()
+	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;
- begin
+begin
     v_object_id := acs_object__new (
-        object_id => mail_link_id,
-        context_id => context_id,
-        creation_date => creation_date,
-        creation_user => creation_user,
-        creation_ip => creation_ip,
-        object_type => object_type
+		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 
     );
-    insert into acs_mail_links ( mail_link_id, body_id )
-        values ( v_object_id, body_id );
+
+    insert into acs_mail_links 
+	 ( mail_link_id, body_id )
+	values 
+	 ( v_object_id, p_body_id );
+
     return v_object_id;
 end;
 ' language 'plpgsql';
 
 create function acs_mail_link__delete (integer)
 returns integer as '
 declare
-    mail_link_id alias for $1;
+	p_mail_link_id			alias for $1;
 begin
     delete from acs_mail_links
-        where mail_link_id = acs_mail_link.delete.mail_link_id;
-    acs_object__delete(mail_link_id);
+        where mail_link_id = p_mail_link_id;
+
+	perform acs_object__delete( p_mail_link_id );
+
     return 1;
 end;
 ' language 'plpgsql';
 
 create function acs_mail_link__link_p (integer)
 returns boolean as '
 declare
-    object_id alias for $1;
+	p_object_id				alias for $1;
     v_check_link_id integer;
 begin
-    select (case when count(mail_link_id) = 0 then 0 else 1) into v_check_link_id
+    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 = object_id;
+        where mail_link_id = p_object_id;
+
     if v_check_link_id <> 0 then
         return ''t'';
     else
         return ''f'';
     end if;
-end; -- link_p
+end;
 ' language 'plpgsql';
 
 --end acs_mail_link;
