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 <jmp@arsdigita.com>
+-- @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 <jmp@arsdigita.com>
+-- @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 <jmp@arsdigita.com>
+-- @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"