-- 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 <jmp@arsdigita.com>
-- @author Phong Nguyen <phong@arsdigita.com>
-- @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
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Messaging 5.10.1b1 : upgrade-4.1-4.1.1.sql
Begin main content
