Fisheye: Tag 1.6 refers to a dead (removed) revision in file `openacs-4/packages/acs-mail/sql/postgres/acs-mail-create.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 1.6 refers to a dead (removed) revision in file `openacs-4/packages/acs-mail/sql/postgres/acs-mail-packages-create.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 1.5 refers to a dead (removed) revision in file `openacs-4/packages/acs-mail/sql/postgres/acs-mail-queue-create.sql'. Fisheye: No comparison available. Pass `N' to diff? 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-create.sql 23 May 2001 16:43:30 -0000 1.1 @@ -0,0 +1,246 @@ +-- +-- 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/23 16:43:30 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.) + +select 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' +); + +-- 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. + +select 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' +); + +-- 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. + +select 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' +); + +-- 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. + +select 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' +); + +-- 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 timestamp, + 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 +\i acs-mail-packages-create + +-- Supporting Datamodels ----------------------------------------------- + +-- The mail queue datamodel +\i acs-mail-queue-create 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-packages-create.sql 23 May 2001 16:43:30 -0000 1.1 @@ -0,0 +1,336 @@ +-- +-- 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/23 16:43:30 jong Exp $ +-- + +-- Package Implementations --------------------------------------------- + +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 + 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'; + +create function 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 + +-- 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 + v_object_id 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 + ); + + 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, + header_message_id, header_reply_to, header_subject, header_from, + header_to, content_object_id); + return v_object_id; +end; +' language 'plpgsql'; + + +create function acs_mail_body__delete(integer) +returns integer as ' +declare + body_id alias for $1; +begin + PERFORM acs_mail_gc_object__delete(body_id); + + return 1; +end; +' language 'plpgsql'; + +create function acs_mail_body__body_p(integer) +returns char as ' + 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; + if v_check_body_id <> 0 then + return ''t''; + else + return ''f''; + end if; + 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 + 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 + 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 'plpgsql'; + +-- had to truncate the proc name from +-- acs_mail_body__set_content_object +create function acs_mail_body__set_content_obj (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 'plpgsql'; + +---- +--create or replace package body acs_mail_multipart +create function acs_mail_multipart__new (integer,varchar,varchar, +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 + 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 'plpgsql'; + +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 'plpgsql'; + +create function acs_mail_multipart__multipart_p (integer) +returns boolean as ' +declare + 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 + 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 'plpgsql'; + + -- 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 coalesce(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 'plpgsql'; + +--end acs_mail_multipart; + +--create or replace package body acs_mail_link__ +create function acs_mail_link__new (integer,integer,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 now() + 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 'plpgsql'; + +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 'plpgsql'; + +create function acs_mail_link__link_p (integer) +returns boolean as ' +declare + 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 + 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 '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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-queue-create.sql 23 May 2001 16:43:30 -0000 1.1 @@ -0,0 +1,95 @@ +-- +-- 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/23 16:43:30 jong Exp $ +-- + +select 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' +); + +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,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 + 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 'plpgsql'; + +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 'plpgsql'; +-- 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" Index: openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0-4.0.1a.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0-4.0.1a.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0-4.0.1a.sql 23 May 2001 16:39:23 -0000 1.1 @@ -0,0 +1,242 @@ +-- +-- acs-messaging sql/upgrade-4.0-4.0.1a.sql +-- +-- @author jmp@arsdigita.com +-- @creation-date 2000-11-03 +-- @cvs-id $Id: upgrade-4.0-4.0.1a.sql,v 1.1 2001/05/23 16:39:23 jong Exp $ +-- + +alter table acs_messages add ( + sent_date date + constraint acs_messages_sent_date_nn + not null + disable, + sender integer + constraint acs_messages_sender_fk + references parties (party_id) + disable, + rfc822_id varchar2(250) + constraint acs_messages_rfc822_id_nn + not null + disable + constraint acs_messages_rfc822_id_un + unique + disable +); + +create table acs_mess_up ( + id integer primary key, + sent_date date, + sender integer, + rfc822_id varchar2(250) +); + +insert into acs_mess_up + select m.message_id, + r.publish_date as sent_date, + o.creation_user as sender, + (sysdate || '.' || message_id || '@' + || utl_inaddr.get_host_name||'.hate') as rfc822_id + from acs_objects o, cr_items i, cr_revisions r, acs_messages m + where m.message_id = i.item_id + and m.message_id = o.object_id + and r.revision_id = i.live_revision; + +update acs_messages + set sent_date = (select sent_date from acs_mess_up where id = message_id), + sender = (select sender from acs_mess_up where id = message_id), + rfc822_id = (select rfc822_id from acs_mess_up where id = message_id); + +drop table acs_mess_up; + +alter table acs_messages modify constraint acs_messages_sent_date_nn enable; +alter table acs_messages modify constraint acs_messages_sender_fk enable; +alter table acs_messages modify constraint acs_messages_rfc822_id_nn enable; +alter table acs_messages modify constraint acs_messages_rfc822_id_un enable; + +create or replace view acs_messages_all as + select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id, + r.title, r.mime_type, r.content, o.context_id + from acs_objects o, cr_items i, cr_revisions r, acs_messages m + where o.object_id = m.message_id and i.item_id = m.message_id + and r.revision_id = i.live_revision; + +create table acs_messages_outgoing ( + message_id integer + constraint amo_message_id_fk + references acs_messages (message_id) on delete cascade, + recipient_id integer + constraint amo_recipient_id_fk + references parties (party_id), + grouping_id integer, + wait_until date not null, + constraint acs_messages_outgoing_pk + primary key (message_id, recipient_id) +); + +comment on column acs_messages_outgoing.grouping_id is ' + This identifier is used to group sets of messages to be sent as + digests. When a message is about to be sent, any other messages + with the same grouping_id will be put together with it in a + digest. It is recommended but not required that an object id is + used. Bboard, for example, might use the forum id that the user''s + subscribed to. For instant (non-digest) updates, it would be + appropriate to use null, which is never equal to anything else. +'; + +comment on column acs_messages_outgoing.wait_until is ' + Don''t schedule a send until after this date. If another message with + the same grouping ID is scheduled to be sent, then this message may be + sent at the same time. (So, for example, daily digests would be + achieved by setting the grouping_id to the same value, and the wait_until + value to the end of the current day. As soon as one message in the group + is to be sent, all will be sent.) +'; + +create or replace package acs_message +as + + function new ( + message_id in acs_messages.message_id%TYPE default null, + reply_to in acs_messages.reply_to%TYPE default null, + sent_date in acs_messages.sent_date%TYPE default sysdate, + sender in acs_messages.sender%TYPE default null, + rfc822_id in acs_messages.rfc822_id%TYPE default null, + title in cr_revisions.title%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + text in varchar2 default null, + data in cr_revisions.content%TYPE default null, + context_id in acs_objects.context_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'acs_message' + ) return acs_objects.object_id%TYPE; + + procedure delete ( + message_id in acs_messages.message_id%TYPE + ); + + function message_p ( + message_id in acs_messages.message_id%TYPE + ) return char; + + procedure send ( + message_id in acs_messages.message_id%TYPE, + recipient_id in parties.party_id%TYPE, + grouping_id in integer default NULL, + wait_until in date default SYSDATE + ); + +end acs_message; +/ +show errors + +create or replace package body acs_message +as + + function new ( + message_id in acs_messages.message_id%TYPE default null, + reply_to in acs_messages.reply_to%TYPE default null, + sent_date in acs_messages.sent_date%TYPE default sysdate, + sender in acs_messages.sender%TYPE default null, + rfc822_id in acs_messages.rfc822_id%TYPE default null, + title in cr_revisions.title%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + text in varchar2 default null, + data in cr_revisions.content%TYPE default null, + context_id in acs_objects.context_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'acs_message' + ) return acs_objects.object_id%TYPE + is + v_message_id acs_messages.message_id%TYPE; + v_rfc822_id acs_messages.rfc822_id%TYPE; + v_name cr_items.name%TYPE; + begin + if message_id is null then + select acs_object_id_seq.nextval into v_message_id from dual; + else + v_message_id := message_id; + end if; + + if rfc822_id is null then + v_rfc822_id := sysdate || '.' || v_message_id || '@' || + utl_inaddr.get_host_name || '.hate'; + else + v_rfc822_id := rfc822_id; + end if; + + v_name := v_rfc822_id; + + v_message_id := content_item.new ( + name => v_name, + parent_id => context_id, + item_id => message_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_subtype => object_type, + title => title, + mime_type => mime_type, + text => text, + data => data, + is_live => 't' + ); + + -- I hate you, milkman CR. + -- Fix the broken permissions stuff content_item.new does + update acs_objects set security_inherit_p = 't' + where object_id = v_message_id; + delete from acs_permissions where object_id = v_message_id; + + insert into + acs_messages (message_id, reply_to, sent_date, sender, rfc822_id) + values (v_message_id, reply_to, sent_date, sender, v_rfc822_id); + + return v_message_id; + end new; + + procedure delete ( + message_id in acs_messages.message_id%TYPE + ) + is + begin + delete from acs_messages + where message_id = acs_message.delete.message_id; + content_item.delete(message_id); + end; + + function message_p ( + message_id in acs_messages.message_id%TYPE + ) return char + is + v_check_message_id char(1); + begin + select decode(count(message_id),0,'f','t') into v_check_message_id + from acs_messages + where message_id = message_p.message_id; + return v_check_message_id; + end message_p; + + procedure send ( + message_id in acs_messages.message_id%TYPE, + recipient_id in parties.party_id%TYPE, + grouping_id in integer default NULL, + wait_until in date default SYSDATE + ) + is + v_wait_until date; + begin + v_wait_until := nvl(wait_until, SYSDATE); + insert into acs_messages_outgoing + (message_id, recipient_id, grouping_id, wait_until) + values + (message_id, recipient_id, grouping_id, nvl(wait_until,SYSDATE)); + end; + +end acs_message; +/ +show errors Index: openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0.1-4.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0.1-4.1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0.1-4.1.sql 23 May 2001 16:39:23 -0000 1.1 @@ -0,0 +1,12 @@ +-- +-- acs-messaging sql/upgrade-4.0.1-4.1.sql +-- +-- @author John Prevost +-- @creation-date 2001-01-16 +-- @cvs-id $Id: upgrade-4.0.1-4.1.sql,v 1.1 2001/05/23 16:39:23 jong Exp $ +-- + +-- do all the views and packages in case something changed + +@@ acs-messaging-views +@@ acs-messaging-packages \ No newline at end of file Index: openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0.1a-4.0.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0.1a-4.0.1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.0.1a-4.0.1.sql 23 May 2001 16:39:23 -0000 1.1 @@ -0,0 +1,49 @@ +-- +-- acs-messaging sql/upgrade-4.0.1a-4.0.1.sql +-- +-- @author jmp@arsdigita.com +-- @creation-date 2000-11-15 +-- @cvs-id $Id: upgrade-4.0.1a-4.0.1.sql,v 1.1 2001/05/23 16:39:23 jong Exp $ +-- + +begin + acs_object_type.create_type ( + supertype => 'content_revision', + object_type => 'acs_message_revision', + pretty_name => 'Message Revision', + pretty_plural => 'Message Revisions', + table_name => 'CR_REVISIONS', + id_column => 'REVISION_ID', + name_method => 'ACS_OBJECT.DEFAULT_NAME' + ); +end; +/ +show errors + +alter table acs_messages_outgoing add ( + to_address varchar2(1000) + constraint amo_to_address_nn + not null + disable +); + +update acs_messages_outgoing + set to_address = (select email from parties where party_id = recipient_id); + +alter table acs_messages_outgoing + drop constraint acs_messages_outgoing_pk; + +alter table acs_messages_outgoing + add constraint acs_messages_outgoing_pk + primary key (message_id, to_address); + +alter table acs_messages_outgoing + modify constraint amo_to_address_nn enable; + +alter table acs_messages_outgoing + drop column recipient_id; + +@@ acs-messaging-views +@@ acs-messaging-packages + +set feedback on Index: openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.1-4.1.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.1-4.1.1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgresql/upgrade/upgrade-4.1-4.1.1.sql 23 May 2001 16:39:23 -0000 1.1 @@ -0,0 +1,658 @@ +-- packages/acs-messaging/sql/upgrade/sql/upgrade/upgrade-4.1-4.1.1.sql +-- +-- upgrade script for acs-messaging 4.1 to 4.1.1. +-- @author teeters@arsdigita.com +-- @creation-date 2000-03-06 +-- @cvs-id $Id: upgrade-4.1-4.1.1.sql,v 1.1 2001/05/23 16:39:23 jong Exp $ + +-- Change in acs-messaging/sql/acs-messaging-create.sql +-- content_item name_method changed from 'ACS_OBJECT.DEFAULT_NAME' to 'ACS_MESSAGE.NAME' + +update acs_object_types set name_method = 'ACS_MESSAGE.NAME' where object_type = 'acs_message'; + +-- Added function name to package acs_message; + +-- @../../../acs-messaging/sql/acs-messaging-packages.sql + +-- would like to source file using @, but for some reason source not working +-- have to copy file. + +-- +-- packages/acs-messaging/sql/acs-messaging-packages.sql +-- +-- @author John Prevost +-- @author Phong Nguyen +-- @creation-date 2000-08-27 +-- @cvs-id $Id: upgrade-4.1-4.1.1.sql,v 1.1 2001/05/23 16:39:23 jong Exp $ +-- + +create or replace package acs_message +as + + function new ( + message_id in acs_messages.message_id%TYPE default null, + reply_to in acs_messages.reply_to%TYPE default null, + sent_date in acs_messages.sent_date%TYPE default sysdate, + sender in acs_messages.sender%TYPE default null, + rfc822_id in acs_messages.rfc822_id%TYPE default null, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + text in varchar2 default null, + data in cr_revisions.content%TYPE default null, + parent_id in cr_items.parent_id%TYPE default 0, + context_id in acs_objects.context_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'acs_message', + is_live in char default 't' + ) return acs_objects.object_id%TYPE; + + function edit ( + message_id in acs_messages.message_id%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + text in varchar2 default null, + data in cr_revisions.content%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE; + + procedure delete ( + message_id in acs_messages.message_id%TYPE + ); + + function message_p ( + message_id in acs_messages.message_id%TYPE + ) return char; + + procedure send ( + message_id in acs_messages.message_id%TYPE, + recipient_id in parties.party_id%TYPE, + grouping_id in integer default null, + wait_until in date default sysdate + ); + + procedure send ( + message_id in acs_messages.message_id%TYPE, + to_address in varchar2, + grouping_id in integer default null, + wait_until in date default sysdate + ); + + function first_ancestor ( + message_id in acs_messages.message_id%TYPE + ) return acs_messages.message_id%TYPE; + + -- ACHTUNG! WARNING! ACHTUNG! WARNING! ACHTUNG! WARNING! -- + + -- Developers: Please don't depend on the following functionality + -- to remain in the same place. Chances are very good these + -- functions will migrate to another PL/SQL package or be replaced + -- by direct calls to CR code in the near future. + + function new_file ( + message_id in acs_messages.message_id%TYPE, + file_id in cr_items.item_id%TYPE default null, + file_name in cr_items.name%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE, + content in cr_revisions.content%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE; + + function edit_file ( + file_id in cr_items.item_id%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE, + content in cr_revisions.content%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE; + + procedure delete_file ( + file_id in cr_items.item_id%TYPE + ); + + function new_image ( + message_id in acs_messages.message_id%TYPE, + image_id in cr_items.item_id%TYPE default null, + file_name in cr_items.name%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE, + content in cr_revisions.content%TYPE default null, + width in images.width%TYPE default null, + height in images.height%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE; + + function edit_image ( + image_id in cr_items.item_id%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE, + content in cr_revisions.content%TYPE default null, + width in images.width%TYPE default null, + height in images.height%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE; + + procedure delete_image ( + image_id in cr_items.item_id%TYPE + ); + + function new_extlink ( + name in cr_items.name%TYPE, + extlink_id in cr_extlinks.extlink_id%TYPE default null, + url in cr_extlinks.url%TYPE, + label in cr_extlinks.label%TYPE default null, + description in cr_extlinks.description%TYPE default null, + parent_id in acs_objects.context_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return cr_extlinks.extlink_id%TYPE; + + function edit_extlink ( + extlink_id in cr_extlinks.extlink_id%TYPE, + url in cr_extlinks.url%TYPE, + label in cr_extlinks.label%TYPE default null, + description in cr_extlinks.description%TYPE default null + ) return cr_extlinks.extlink_id%TYPE; + + procedure delete_extlink ( + extlink_id in cr_extlinks.extlink_id%TYPE + ); + + function name ( + message_id in acs_objects.object_id%TYPE + ) return varchar2; + +end acs_message; +/ +show errors + +create or replace package body acs_message +as + + function new ( + message_id in acs_messages.message_id%TYPE default null, + reply_to in acs_messages.reply_to%TYPE default null, + sent_date in acs_messages.sent_date%TYPE default null, + sender in acs_messages.sender%TYPE default null, + rfc822_id in acs_messages.rfc822_id%TYPE default null, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + text in varchar2 default null, + data in cr_revisions.content%TYPE default null, + parent_id in cr_items.parent_id%TYPE default 0, + context_id in acs_objects.context_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'acs_message', + is_live in char default 't' + ) return acs_objects.object_id%TYPE + is + v_message_id acs_messages.message_id%TYPE; + v_rfc822_id acs_messages.rfc822_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + begin + + -- generate a message id now so we can get an rfc822 message-id + if message_id is null then + select acs_object_id_seq.nextval into v_message_id from dual; + else + v_message_id := message_id; + end if; + + -- this needs to be fixed up, but Oracle doesn't give us a way + -- to get the FQDN + if rfc822_id is null then + v_rfc822_id := sysdate || '.' || v_message_id || '@' || + utl_inaddr.get_host_name || '.hate'; + else + v_rfc822_id := rfc822_id; + end if; + + v_message_id := content_item.new ( + name => v_rfc822_id, + parent_id => parent_id, + content_type => 'acs_message_revision', + item_id => message_id, + context_id => context_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + item_subtype => object_type + ); + + insert into acs_messages + (message_id, reply_to, sent_date, sender, rfc822_id) + values + (v_message_id, reply_to, sent_date, sender, v_rfc822_id); + + -- create an initial revision for the new message + v_revision_id := acs_message.edit ( + message_id => v_message_id, + title => title, + description => description, + mime_type => mime_type, + text => text, + data => data, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + is_live => is_live + ); + + return v_message_id; + end new; + + function edit ( + message_id in acs_messages.message_id%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + text in varchar2 default null, + data in cr_revisions.content%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE + is + v_revision_id cr_revisions.revision_id%TYPE; + begin + + -- create a new revision using whichever call is appropriate + if edit.data is not null then + v_revision_id := content_revision.new ( + item_id => message_id, + title => title, + description => description, + data => data, + mime_type => mime_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + elsif title is not null or text is not null then + v_revision_id := content_revision.new ( + item_id => message_id, + title => title, + description => description, + text => text, + mime_type => mime_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + end if; + + -- test for auto approval of revision + if edit.is_live = 't' then + content_item.set_live_revision(v_revision_id); + end if; + + return v_revision_id; + + end edit; + + procedure delete ( + message_id in acs_messages.message_id%TYPE + ) + is + begin + delete from acs_messages + where message_id = acs_message.delete.message_id; + content_item.delete(message_id); + end delete; + + function message_p ( + message_id in acs_messages.message_id%TYPE + ) return char + is + v_check_message_id integer; + begin + select decode(count(message_id),0,0,1) into v_check_message_id + from acs_messages + where message_id = message_p.message_id; + if v_check_message_id <> 0 then + return 't'; + else + return 'f'; + end if; + end message_p; + + procedure send ( + message_id in acs_messages.message_id%TYPE, + to_address in varchar2, + grouping_id in integer default null, + wait_until in date default sysdate + ) + is + v_wait_until date; + begin + v_wait_until := nvl(wait_until, sysdate); + insert into acs_messages_outgoing + (message_id, to_address, grouping_id, wait_until) + values + (message_id, to_address, grouping_id, v_wait_until); + end send; + + procedure send ( + message_id in acs_messages.message_id%TYPE, + recipient_id in parties.party_id%TYPE, + grouping_id in integer default null, + wait_until in date default sysdate + ) + is + v_wait_until date; + begin + v_wait_until := nvl(wait_until, sysdate); + insert into acs_messages_outgoing + (message_id, to_address, grouping_id, wait_until) + select send.message_id, p.email, send.grouping_id, v_wait_until + from parties p + where p.party_id = send.recipient_id; + end send; + + function first_ancestor ( + message_id in acs_messages.message_id%TYPE + ) return acs_messages.message_id%TYPE + is + v_message_id acs_messages.message_id%TYPE; + begin + select message_id into v_message_id + from (select message_id, reply_to + from acs_messages + connect by message_id = prior reply_to + start with message_id = first_ancestor.message_id) ancestors + where reply_to is null; + return v_message_id; + end first_ancestor; + + -- ACHTUNG! WARNING! ACHTUNG! WARNING! ACHTUNG! WARNING! -- + + -- Developers: Please don't depend on the following functionality + -- to remain in the same place. Chances are very good these + -- functions will migrate to another PL/SQL package or be replaced + -- by direct calls to CR code in the near future. + + function new_file ( + message_id in acs_messages.message_id%TYPE, + file_id in cr_items.item_id%TYPE default null, + file_name in cr_items.name%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + content in cr_revisions.content%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE + is + v_file_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + begin + + v_file_id := content_item.new ( + name => file_name, + parent_id => message_id, + item_id => file_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + -- create an initial revision for the new attachment + v_revision_id := edit_file ( + file_id => v_file_id, + title => title, + description => description, + mime_type => mime_type, + content => content, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + is_live => is_live + ); + + return v_file_id; + end new_file; + + function edit_file ( + file_id in cr_items.item_id%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + content in cr_revisions.content%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE + is + v_revision_id cr_revisions.revision_id%TYPE; + begin + + v_revision_id := content_revision.new ( + title => title, + mime_type => mime_type, + data => content, + item_id => file_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + -- test for auto approval of revision + if is_live = 't' then + content_item.set_live_revision(v_revision_id); + end if; + + return v_revision_id; + end edit_file; + + procedure delete_file ( + file_id in cr_items.item_id%TYPE + ) + is + begin + content_item.delete(delete_file.file_id); + end delete_file; + + function new_image ( + message_id in acs_messages.message_id%TYPE, + image_id in cr_items.item_id%TYPE default null, + file_name in cr_items.name%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + content in cr_revisions.content%TYPE default null, + width in images.width%TYPE default null, + height in images.height%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE + is + v_image_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + begin + + v_image_id := content_item.new ( + name => file_name, + parent_id => message_id, + item_id => image_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip + ); + + -- create an initial revision for the new attachment + v_revision_id := edit_image ( + image_id => v_image_id, + title => title, + description => description, + mime_type => mime_type, + content => content, + width => width, + height => height, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + is_live => is_live + ); + + return v_image_id; + end new_image; + + function edit_image ( + image_id in cr_items.item_id%TYPE, + title in cr_revisions.title%TYPE default null, + description in cr_revisions.description%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default 'text/plain', + content in cr_revisions.content%TYPE default null, + width in images.width%TYPE default null, + height in images.height%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + is_live in char default 't' + ) return acs_objects.object_id%TYPE + is + v_revision_id cr_revisions.revision_id%TYPE; + begin + + v_revision_id := content_revision.new ( + title => edit_image.title, + mime_type => edit_image.mime_type, + data => edit_image.content, + item_id => edit_image.image_id, + creation_date => edit_image.creation_date, + creation_user => edit_image.creation_user, + creation_ip => edit_image.creation_ip + ); + + -- insert new width and height values + -- XXX fix after image.new exists + insert into images + (image_id, width, height) + values + (v_revision_id, width, height); + + -- test for auto approval of revision + if edit_image.is_live = 't' then + content_item.set_live_revision(v_revision_id); + end if; + + return v_revision_id; + end edit_image; + + procedure delete_image ( + image_id in cr_items.item_id%TYPE + ) + is + begin + -- XXX fix after image.delete exists + delete from images + where image_id = delete_image.image_id; + content_item.delete(image_id); + end delete_image; + + -- XXX should just call content_extlink.new + function new_extlink ( + name in cr_items.name%TYPE default null, + extlink_id in cr_extlinks.extlink_id%TYPE default null, + url in cr_extlinks.url%TYPE, + label in cr_extlinks.label%TYPE default null, + description in cr_extlinks.description%TYPE default null, + parent_id in acs_objects.context_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return cr_extlinks.extlink_id%TYPE + is + v_extlink_id cr_extlinks.extlink_id%TYPE; + begin + v_extlink_id := content_extlink.new ( + name => new_extlink.name, + url => new_extlink.url, + label => new_extlink.label, + description => new_extlink.description, + parent_id => new_extlink.parent_id, + extlink_id => new_extlink.extlink_id, + creation_date => new_extlink.creation_date, + creation_user => new_extlink.creation_user, + creation_ip => new_extlink.creation_ip + ); + end new_extlink; + + -- XXX should just edit extlink + function edit_extlink ( + extlink_id in cr_extlinks.extlink_id%TYPE, + url in cr_extlinks.url%TYPE, + label in cr_extlinks.label%TYPE default null, + description in cr_extlinks.description%TYPE default null + ) return cr_extlinks.extlink_id%TYPE + is + v_is_extlink char; + begin + v_is_extlink := content_extlink.is_extlink(edit_extlink.extlink_id); + if v_is_extlink = 't' then + update cr_extlinks + set url = edit_extlink.url, + label = edit_extlink.label, + description = edit_extlink.description + where extlink_id = edit_extlink.extlink_id; + end if; + return v_is_extlink; + end edit_extlink; + + procedure delete_extlink ( + extlink_id in cr_extlinks.extlink_id%TYPE + ) is + begin + content_extlink.delete(extlink_id => delete_extlink.extlink_id); + end delete_extlink; + + function name ( + message_id in acs_objects.object_id%TYPE + ) return varchar2 + is + v_message_name acs_messages_all.title%TYPE; + begin + select title into v_message_name + from acs_messages_all + where message_id = name.message_id; + return v_message_name; + end name; + +end acs_message; +/ +show errors + +