Index: openacs-4/packages/acs-mail/sql/postgres/acs-mail-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgres/Attic/acs-mail-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-mail/sql/postgres/acs-mail-create.sql 15 May 2001 05:56:50 -0000 1.1 @@ -0,0 +1,255 @@ +-- +-- packages/acs-mail/sql/acs-mail-create.sql +-- +-- @author John Prevost +-- @creation-date 2001-01-08 +-- @cvs-id $Id: acs-mail-create.sql,v 1.1 2001/05/15 05:56:50 jong Exp $ +-- + +-- Typical usage when creating: + +-- For text only +-- +-- body_id := acs_mail_body.new ( ... ); +-- text_id := acs_mail_object.new ( ... ); +-- acs_content.set_parameters ( text_id, ... ); +-- update acs_content set content = empty_blob() where object_id = text_id; +-- acs_mail_body.set_content(text_id); +-- bboard_message.new ( ..., body_id ); + +-- tcl possibilities: +-- set body_id [acs_mail_body_new ...] +-- set text_id [acs_mail_object_new ... -content $text ] +-- acs_mail_body_set_content $body_id $text_id +-- set msg_id [bboard_message_new ... $body_id] +-- *or* +-- set body_id [acs_mail_body_new ... -content $text] +-- set msg_id [bboard_message_new ... $body_id] + +-- For attachments (multipart/mixed) +-- +-- body_id := acs_mail_body.new ( ... ); +-- part_id := acs_mail_multipart.new ( ..., 'multipart/mixed' ); +-- text_id := acs_mail_object.new ( ... ); +-- { ... content stuff ... } +-- photo_id := acs_mail_object.new ( ... ); +-- { ... content stuff ... } +-- acs_mail_multipart.add_content ( part_id, text_id ); +-- acs_mail_multipart.add_content ( part_id, photo_id ); +-- acs_mail_body.set_content ( part_id ); +-- bboard_message.new ( ..., body_id ); + +-- For alternatives +-- (Same as above, but 'multipart/alternative' instead of 'multipart/mixed') + +-- Typical usage when displaying: + +-- select ... from ... (tree query) +-- 0 RFC822 Header (ignored) +-- 1 multipart/mixed (attachments!) +-- 1.1 text/plain (spit it out) +-- 1.2 image/gif (inline it) +-- 1.3 text/plain (more text to spit out +-- 1.4 message/rfc822 +-- 1.4.0 header of submessage + +-- 0000 (not sure about the numbering stuff yet) is always the RFC822 +-- header, autogenerated or from incoming email. +-- 0001 will always be the content of the message +-- within 0001 may be more items, depending on the structure of the message. + +-- Common headers are also available decomposed into useful forms. + +-- Exactly how incoming messages get transformed into this structure +-- and how outgoing messages get transformed from this structure is to +-- be implemented soon. + +set feedback off + +-- Object System Metadata ---------------------------------------------- + +-- A messaging object, which is subject to garbage collection by the +-- messaging system. If any object in this table is not found in + +-- select body_id as o_id from acs_mail_links +-- union +-- select object_id as o_id from acs_mail_multipart_parts +-- union +-- select body_content as o_id from acs_mail_bodies + +-- then it is removed. It is assumed that an object cannot satisfy +-- the above predicate if it ever stops satisfying it (outside of a +-- transaction.) + +begin + acs_object_type__create_type ( + 'acs_mail_gc_object', + 'ACS Messaging Object', + 'ACS Messaging Objects', + 'acs_object', + 'ACS_MAIL_GC_OBJECTS', + 'OBJECT_ID', + 'ACS_MAIL_GC_OBJECT', + 'f', + null, + 'ACS_OBJECT.DEFAULT_NAME' + ); +end; + +-- Mail bodies are automatically GC'd. These contain the data +-- relevant to a single message. These are shared by being pointed to +-- by many acs_mail_links. This should not be subtyped. + +begin + acs_object_type__create_type ( + 'acs_mail_body', + 'Mail Body', + 'Mail Bodies', + 'acs_mail_gc_object', + 'ACS_MAIL_BODIES', + 'BODY_ID', + 'ACS_MAIL_BODY', + 'f', + null, + 'ACS_OBJECT.DEFAULT_NAME' + ); +end; + +-- multipart mime parts are automatically GC'd. These contain +-- multiple parts to make up alternatives or mixed content +-- (attachments). These may be shared by belonging to multiple +-- mail_links. + +begin + acs_object_type__create_type ( + 'acs_mail_multipart', + 'ACS Mail Multipart Object', + 'ACS Mail Multipart Objects', + 'acs_mail_gc_object', + 'ACS_MAIL_MULTIPARTS', + 'MULTIPART_ID', + 'ACS_MAIL_MULTIPART', + 'f', + null, + 'ACS_OBJECT.DEFAULT_NAME' + ); +end; + +-- A mail_link, subtypable, and used by applications to track messages. +-- Permissions should be set at this level. These should not be +-- shared between applications: rather, an application should create a +-- new mail_link and use it as it wills. When it's done, it should +-- delete this, which will cause the other objects to be garbage +-- collected. + +begin + acs_object_type__create_type ( + 'acs_mail_link', + 'Mail Message', + 'Mail Messages', + 'acs_object', + 'ACS_MAIL_LINKS', + 'MAIL_LINK_ID', + 'ACS_MAIL_LINK', + 'f', + null, + 'ACS_OBJECT.DEFAULT_NAME' + ); + +end; + +-- Raw Tables and Comments --------------------------------------------- + + -- All garbage collectable objects are in this table + +create table acs_mail_gc_objects ( + gc_object_id integer + constraint acs_mail_gc_objs_object_id_pk + primary key + constraint acs_mail_gc_objs_object_id_fk + references acs_objects +); + + -- Mail bodies + +create table acs_mail_bodies ( + body_id integer + constraint acs_mail_bodies_body_id_pk primary key + constraint acs_mail_bodies_body_id_fk + references acs_mail_gc_objects on delete cascade, + body_reply_to integer + constraint acs_mail_bodies_reply_to_fk + references acs_mail_bodies on delete set null, + body_from integer + constraint acs_mail_bodies_body_from_fk + references parties on delete set null, + body_date date, + header_message_id varchar(1000) + constraint acs_mail_bodies_h_m_id_un unique + constraint acs_mail_bodies_h_m_id_nn not null, + header_reply_to varchar(1000), + header_subject text, + header_from text, + header_to text, + content_object_id integer + constraint acs_mail_bodies_content_oid_fk + references acs_objects +); + +create table acs_mail_body_headers ( + body_id integer + constraint acs_mail_body_heads_body_id_fk + references acs_mail_bodies on delete cascade, + header_name varchar(1000), + header_content text +); + +create index acs_mail_body_hdrs_body_id_idx + on acs_mail_body_headers (body_id); + + -- MIME Multiparts + +create table acs_mail_multiparts ( + multipart_id integer + constraint acs_mail_multiparts_mp_id_pk primary key + constraint acs_mail_multiparts_mp_id_fk + references acs_mail_gc_objects on delete cascade, + multipart_kind varchar(120) + constraint acs_mail_multiparts_mp_kind_nn not null +); + +create table acs_mail_multipart_parts ( + multipart_id integer + constraint acs_mail_mp_parts_mp_id_fk + references acs_mail_multiparts + on delete cascade, + mime_filename varchar(1000), + mime_disposition varchar(1000), + sequence_number integer, + content_object_id integer + constraint acs_mail_mp_parts_c_obj_id_fk references acs_objects, + constraint acs_mail_multipart_parts_pk + primary key (multipart_id, sequence_number) +); + + -- Mail Links + +create table acs_mail_links ( + mail_link_id integer + constraint acs_mail_links_ml_id_pk primary key + constraint acs_mail_links_ml_id_fk references acs_objects, + body_id integer + constraint acs_mail_links_body_id_nn not null + constraint acs_mail_links_body_id_fk references acs_mail_bodies +); + + +-- API ----------------------------------------------------------------- + +-- APIs for the datamodel in this file, separated out for future upgrades +@@ acs-mail-packages-create + +-- Supporting Datamodels ----------------------------------------------- + +-- The mail queue datamodel +@@ acs-mail-queue-create Index: openacs-4/packages/acs-mail/sql/postgres/acs-mail-packages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgres/Attic/acs-mail-packages-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-mail/sql/postgres/acs-mail-packages-create.sql 15 May 2001 05:56:50 -0000 1.1 @@ -0,0 +1,329 @@ +-- +-- packages/acs-mail/sql/acs-mail-create-packages.sql +-- +-- @author John Prevost +-- @creation-date 2001-01-08 +-- @cvs-id $Id: acs-mail-packages-create.sql,v 1.1 2001/05/15 05:56:50 jong Exp $ +-- + +-- Package Implementations --------------------------------------------- + +create function acs_mail_gc_object__new (integer,varchar,datetime,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 sysdate + creation_user alias for $4; -- default null + creation_ip alias for $5; -- default null + context_id alias for $6; -- default null + v_object_id integer; + 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 + ); + insert into acs_mail_gc_objects values ( v_object_id ); + return v_object_id; + end; +' language 'plpgsql'; + +procedure acs_mail_gc_object__delete (integer) +returns integer as ' +declare + 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); + return 1; +end; +' language 'plpgsql'; + +-- end acs_mail_gc_object + +--- +-- create or replace package body acs_mail_body + +create function acs_mail_body__new (integer,integer,integer,date, +varchar,varchar,text,text,text,integer,varchar,date,integer,varchar,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 sysdate + creation_user alias for $13; -- default null + creation_ip alias for $14; -- default null + context_id alias for $15; -- default null + v_object_id integer; + v_header_message_id varchar; + begin + 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 + ); +-- this needs to change it is using an oracle proc + v_header_message_id := + nvl(header_message_id, + sysdate || '.' || v_object_id || '@' || + utl_inaddr.get_host_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, + v_header_message_id, header_reply_to, header_subject, header_from, + header_to, content_object_id); + return v_object_id; +end; +' language 'pgplsql'; + +create function acs_mail_body__delete (integer) +returns integer as ' +declare + body_id alias for $1; +begin + acs_mail_gc_object__delete(body_id); + return 1; +end; +' language 'pgpsql'; + +create function acs_mail_body__body_p (integer) +returns char as ' + object_id alias for $1; + v_check_body_id integer; +begin + select decode(count(body_id),0,0,1) into v_check_body_id + from acs_mail_bodies + where body_id = object_id; + if v_check_body_id <> 0 then + return 't'; + else + return 'f'; + end if; + end; +' language 'pgplsql'; + +create function acs_mail_body__clone (integer,integer,varchar,date, +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 sysdate + creation_user alias for $5; -- default null + creation_ip alias for $6; -- default null + context_id alias for $7; -- default null + v_object_id integer; + body_reply_to integer; + body_from integer; + body_date date; + header_message_id varchar; + header_reply_to varchar; + header_subject text; + header_from text; + header_to text; + 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 + from acs_mail_bodies + where body_id = 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 + ); + return v_object_id; +end; +' language 'pgplsql'; + +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; +begin + update acs_mail_bodies + set content_object_id = set_content_object.content_object_id + where body_id = set_content_object.body_id; + return 1; +end; +' language 'pgplsql'; + +---- +--create or replace package body acs_mail_multipart +create function acs_mail_multipart__new (integer,varchar,varchar, +date,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 sysdate + creation_user alias for $5; -- default null + creation_ip alias for $6; -- default null + 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 + ); + insert into acs_mail_multiparts (multipart_id, multipart_kind) + values (v_object_id, multipart_kind); + return v_object_id; +end; +' language 'pgplsql'; + +create function acs_mail_multipart__delete (integer) +returns integer as ' +declare + multipart_id alias for $1; +begin + acs_mail_gc_object__delete(multipart_id); + return 1; +end; +' language 'pgplsql'; + +create function acs_mail_multipart__multipart_p (integer) +returns char as ' +declare + object_id alias for $1; + v_check_multipart_id integer; +begin + select decode(count(multipart_id),0,0,1) into v_check_multipart_id + from acs_mail_multiparts + where multipart_id = object_id; + if v_check_multipart_id <> 0 then + return 't'; + else + return 'f'; + end if; +end; +' language 'pgplsql'; + + -- Add content at a specific index. If the sequence number is null, + -- below one, or higher than the highest item already available, + -- adds at the end. Otherwise, inserts and renumbers others. + +create function acs_mail_multipart__add_content (integer,integer) +returns integer as ' +declare + multipart_id alias for $1; + 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; + select nvl(max(sequence_number),0) into v_max_num + from acs_mail_multipart_parts + where multipart_id = add_content.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); +end; +' language 'pgplsql'; + +--end acs_mail_multipart; + +--create or replace package body acs_mail_link__ +create function acs_mail_link__new (integer,integer,integer,date, +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' + v_object_id integer; + 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 + ); + insert into acs_mail_links ( mail_link_id, body_id ) + values ( v_object_id, body_id ); + return v_object_id; +end; +' language 'pgplsql'; + +create function acs_mail_link__delete (integer) +returns integer as ' +declare + 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); + return 1; +end; +' language 'pgplsql'; + +create function acs_mail_link__link_p (integer) +returns char as ' +declare + object_id alias for $1; + v_check_link_id integer; +begin + select decode(count(mail_link_id),0,0,1) into v_check_link_id + from acs_mail_links + where mail_link_id = object_id; + if v_check_link_id <> 0 then + return 't'; + else + return 'f'; + end if; +end; -- link_p +' language 'pgplsql'; + +--end acs_mail_link; + Index: openacs-4/packages/acs-mail/sql/postgres/acs-mail-queue-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgres/Attic/acs-mail-queue-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-mail/sql/postgres/acs-mail-queue-create.sql 15 May 2001 05:56:50 -0000 1.1 @@ -0,0 +1,99 @@ +-- +-- packages/acs-mail/sql/acs-mail-queue-create.sql +-- +-- @author John Prevost +-- @creation-date 2001-01-08 +-- @cvs-id $Id: acs-mail-queue-create.sql,v 1.1 2001/05/15 05:56:50 jong Exp $ +-- + +begin + acs_object_type__create_type ( + 'acs_mail_queue_message', + 'Queued Message', + 'Queued Messages', + 'acs_mail_link', + 'ACS_MESSAGES_QUEUE_MESSAGE', + 'MESSAGE_ID', + null, + 'f', + null, + 'ACS_OBJECT.DEFAULT_NAME' + ); +end; +/ +show errors + +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 +); + +create table acs_mail_queue_incoming ( + message_id integer + constraint acs_mail_queue_in_mlid_pk primary key + constraint acs_mail_queue_in_mlid_fk + references acs_mail_queue_messages, + envelope_from text, + envelope_to text +); + +create table acs_mail_queue_outgoing ( + message_id integer + constraint acs_mail_queue_out_mlid_pk primary key + constraint acs_mail_queue_out_mlid_fk + references acs_mail_queue_messages, + envelope_from text, + envelope_to text +); + +-- API ----------------------------------------------------------------- +--create or replace package body acs_mail_queue_message__ +create function acs_mail_queue_message__new (integer,integer, +integer,date,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' + v_object_id integer; + 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 + ); + insert into acs_mail_queue_messages ( message_id ) + values ( v_object_id ); + return v_object_id; +end; +' language 'pgplsql'; + +create function acs_mail_queue_message__delete (integer) +returns integer as ' +declare + 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); + return 1; +end; +' language 'pgplsql'; +-- end acs_mail_queue_message; + + +-- Needs: +-- Incoming: +-- A way to say "okay, I've accepted this one, go ahead and delete" +-- Outgoing: +-- A way to say "send this message to this person from this person" +-- A way to say "send this message to these people from this person"