-- -- 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.6 2009/02/14 22:46:55 donb 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 ( supertype => 'acs_object', object_type => 'acs_mail_gc_object', pretty_name => 'ACS Mail Object', pretty_plural => 'ACS Mail Objects', table_name => 'ACS_MAIL_GC_OBJECTS', id_column => 'GC_OBJECT_ID', package_name => 'ACS_MAIL_GC_OBJECT', name_method => 'ACS_OBJECT.DEFAULT_NAME' ); end; / show errors -- 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 ( supertype => 'acs_mail_gc_object', object_type => 'acs_mail_body', pretty_name => 'ACS Mail Body', pretty_plural => 'ACS Mail Bodies', table_name => 'ACS_MAIL_BODIES', id_column => 'BODY_ID', package_name => 'ACS_MAIL_BODY', name_method => 'ACS_OBJECT.DEFAULT_NAME' ); end; / show errors -- 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 ( supertype => 'acs_mail_gc_object', object_type => 'acs_mail_multipart', pretty_name => 'ACS Mail Multipart Object', pretty_plural => 'ACS Mail Multipart Objects', table_name => 'ACS_MAIL_MULTIPARTS', id_column => 'MULTIPART_ID', package_name => 'ACS_MAIL_MULTIPART', name_method => 'ACS_OBJECT.DEFAULT_NAME' ); end; / show errors -- 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 ( supertype => 'acs_object', object_type => 'acs_mail_link', pretty_name => 'ACS Mail Message', pretty_plural => 'ACS Mail Messages', table_name => 'ACS_MAIL_LINKS', id_column => 'MAIL_LINK_ID', package_name => 'ACS_MAIL_LINK', name_method => 'ACS_OBJECT.DEFAULT_NAME' ); end; / show errors -- 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 varchar2(1000) constraint acs_mail_bodies_h_m_id_un unique constraint acs_mail_bodies_h_m_id_nn not null, header_reply_to varchar2(1000), header_subject varchar2(4000), header_from varchar2(4000), header_to varchar2(4000), content_item_id integer constraint acs_mail_bodies_content_oid_fk references acs_objects on delete cascade ); -- RI Indexes create index acs_mail_bodies_item_id_idx ON acs_mail_bodies(content_item_id); create index acs_mail_bodies_body_from_idx ON acs_mail_bodies(body_from); create index acs_mail_bodies_body_reply_idx ON acs_mail_bodies(body_reply_to); 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 varchar2(1000), header_content varchar2(4000) ); 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 varchar2(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 varchar2(1000), mime_disposition varchar2(1000), sequence_number integer, content_item_id integer constraint acs_mail_mp_parts_c_obj_id_fk references cr_items, constraint acs_mail_multipart_parts_pk primary key (multipart_id, sequence_number) ); --RI Index create index acs_mail_mpp_cr_item_id_idx ON acs_mail_multipart_parts(content_item_id); -- Mail Links create table acs_mail_links ( mail_link_id integer constraint aml_mail_links_id_pk primary key constraint aml_mail_links_id_fk references acs_objects on delete cascade, body_id integer constraint acs_mail_links_body_id_nn not null constraint acs_mail_links_body_id_fk references acs_mail_bodies ); --RI Index create index acs_mail_links_body_id_idx ON acs_mail_links(body_id); -- 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 -- The notification package @@ acs-mail-nt-create