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'';