Index: openacs-4/packages/acs-mail/sql/postgresql/acs-mail-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgresql/acs-mail-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-mail/sql/postgresql/acs-mail-create.sql 23 May 2001 16:43:30 -0000 1.1
+++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-create.sql 8 Jul 2001 18:40:48 -0000 1.2
@@ -238,9 +238,9 @@
-- API -----------------------------------------------------------------
-- APIs for the datamodel in this file, separated out for future upgrades
-\i acs-mail-packages-create
+\i acs-mail-packages-create.sql
-- Supporting Datamodels -----------------------------------------------
-- The mail queue datamodel
-\i acs-mail-queue-create
+\i acs-mail-queue-create.sql
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;
Index: openacs-4/packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql 23 May 2001 16:43:30 -0000 1.1
+++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql 8 Jul 2001 18:40:49 -0000 1.2
@@ -21,13 +21,16 @@
create table acs_mail_queue_messages (
message_id integer
- constraint acs_mail_queue_ml_id_pk primary key
- constraint acs_mail_queue_ml_id_fk references acs_mail_links
+ constraint acs_mail_queue_ml_id_pk
+ primary key
+ constraint acs_mail_queue_ml_id_fk
+ references acs_mail_links
);
create table acs_mail_queue_incoming (
message_id integer
- constraint acs_mail_queue_in_mlid_pk primary key
+ constraint acs_mail_queue_in_mlid_pk
+ primary key
constraint acs_mail_queue_in_mlid_fk
references acs_mail_queue_messages,
envelope_from text,
@@ -36,7 +39,8 @@
create table acs_mail_queue_outgoing (
message_id integer
- constraint acs_mail_queue_out_mlid_pk primary key
+ constraint acs_mail_queue_out_mlid_pk
+ primary key
constraint acs_mail_queue_out_mlid_fk
references acs_mail_queue_messages,
envelope_from text,
@@ -49,41 +53,45 @@
integer,timestamp,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 sysdate
- 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 sysdate
+ 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_mail_link__new (
- mail_link_id => mail_link_id,
- body_id => body_id,
- context_id => context_id,
- creation_date => creation_date,
- creation_user => creation_user,
- creation_ip => creation_ip,
- object_type => object_type
+ p_mail_link_id, -- mail_link_id
+ p_body_id, -- body_id
+ p_context_id, -- context_id
+ p_creation_date, -- creation_date
+ p_creation_user, -- creation_user
+ p_creation_ip, -- creation_ip
+ p_object_type -- object_type
);
- insert into acs_mail_queue_messages ( message_id )
- values ( v_object_id );
+
+ insert into acs_mail_queue_messages
+ ( message_id )
+ values
+ ( v_object_id );
+
return v_object_id;
-end;
-' language 'plpgsql';
+end;' language 'plpgsql';
create function acs_mail_queue_message__delete (integer)
returns integer as '
declare
- message_id alias for $1;
+ p_message_id alias for $1;
begin
delete from acs_mail_queue_messages
- where message_id = acs_mail_queue_message.delete.message_id;
- acs_mail_link.delete(message_id);
+ where message_id = p_message_id;
+
+ perform acs_mail_link.delete( p_message_id );
+
return 1;
-end;
-' language 'plpgsql';
+end;' language 'plpgsql';
-- end acs_mail_queue_message;
Index: openacs-4/packages/acs-mail/tcl/acs-mail-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/tcl/acs-mail-procs-postgresql.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/acs-mail/tcl/acs-mail-procs-postgresql.xql 30 May 2001 07:21:35 -0000 1.3
+++ openacs-4/packages/acs-mail/tcl/acs-mail-procs-postgresql.xql 8 Jul 2001 18:40:49 -0000 1.4
@@ -56,33 +56,41 @@
-
- select acs_mail_gc_object__new (
- gc_object_id => :object_id,
- creation_user => :creation_user,
- creation_ip => :creation_ip
- );
-
+
+
+select acs_mail_gc_object__new (
+ :object_id, -- gc_object_id
+ 'acs_mail_gc_object', -- object_type
+ now(), -- creation_date
+ :creation_user, -- creation_user
+ :creation_ip, -- creation_ip
+ null -- context_id
+);
+
+
+
-
- select acs_mail_body__new (
- body_id => :body_id,
- body_reply_to => :body_reply_to,
- body_from => :body_from,
- body_date => :body_date,
- header_message_id => :header_message_id,
- header_reply_to => :header_reply_to,
- header_subject => :header_subject,
- header_from => :header_from,
- header_to => :header_to,
- content_object_id => :content_object_id,
- creation_user => :creation_user,
- creation_ip => :creation_ip
- );
-
+
+
+select acs_mail_body__new (
+ :body_id, -- body_id
+ :body_reply_to, -- body_reply_to
+ :body_from, -- body_from
+ :body_date, -- body_date
+ :header_message_id, -- header_message_id
+ :header_reply_to, -- header_reply_to
+ :header_subject, -- header_subject
+ :header_from, -- header_from
+ :header_to, -- header_to
+ :content_object_id, -- content_object_id
+ :creation_user, -- creation_user
+ :creation_ip -- creation_ip
+);
+
+
@@ -93,70 +101,88 @@
+
-
- select acs_mail_body__clone (
- old_body_id => :old_body_id,
- body_id => :body_id,
- creation_user => :creation_user,
- creation_ip => :creation_ip
- );
-
+
+
+select acs_mail_body__clone (
+ :old_body_id, -- old_body_id
+ :body_id, -- body_id
+ :creation_user, -- creation_user
+ :creation_ip -- creation_ip
+);
+
+
+
+
-
- select acs_mail_body__set_content_object (
- body_id => :body_id,
- content_object_id => :content_object_id
- );
-
+
+
+select acs_mail_body__set_content_object (
+ :body_id, -- body_id
+ :content_object_id -- content_object_id
+);
+
+
+
+
-
- select acs_mail_multipart__new (
- multipart_id => :multipart_id,
- multipart_kind => :multipart_kind,
- creation_user => :creation_user,
- creation_ip => :creation_ip
- );
-
+
+
+select acs_mail_multipart__new (
+ :multipart_id, -- multipart_id
+ :multipart_kind, -- multipart_kind
+ :creation_user, -- creation_user
+ :creation_ip -- creation_ip
+);
+
+
+
select acs_mail_multipart__multipart_p (:object_id);
+
-
- select acs_mail_multipart__add_content (
- multipart_id => :multipart_id,
- content_object_id => :content_object_id
- );
-
+
+select acs_mail_multipart__add_content (
+ :multipart_id, -- multipart_id
+ :content_object_id -- content_object_id
+);
+
+
+
-
- select acs_mail_link__new (
- mail_link_id => :mail_link_id,
- body_id => :body_id,
- context_id => :context_id,
- creation_user => :creation_user,
- creation_ip => :creation_ip
- );
-
+
+
+select acs_mail_link__new (
+ :mail_link_id, -- mail_link_id
+ :body_id, -- body_id
+ :context_id, -- context_id
+ :creation_user, -- creation_user
+ :creation_ip -- creation_ip
+);
+
+
+
select acs_mail_link__link_p (:object_id);