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;