Index: openacs-4/packages/acs-messaging/sql/postgres/acs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/acs-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-create.sql 22 May 2001 04:05:28 -0000 1.1 @@ -0,0 +1,317 @@ +-- +-- packages/acs-kernel/sql/acs-create.sql +-- +-- @author rhs@mit.edu +-- @creation-date 2000-08-22 +-- @cvs-id acs-create.sql,v 1.1.2.9 2000/08/24 07:09:18 rhs Exp +-- + +create table acs_magic_objects ( + name varchar(100) + constraint acs_magic_objects_pk primary key, + object_id integer not null constraint acs_magic_objects_object_id_fk + references acs_objects(object_id) +); + +create index acs_mo_object_id_idx on acs_magic_objects (object_id); + +comment on table acs_magic_objects is ' + This table allows us to provide semantic names for certain special + objects like the site-wide organization, and the all users party. +'; + +-- create or replace package acs +-- as +-- +-- function add_user ( +-- user_id in users.user_id%TYPE default null, +-- object_type in acs_objects.object_type%TYPE +-- default 'user', +-- 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, +-- email in parties.email%TYPE, +-- url in parties.url%TYPE default null, +-- first_names in persons.first_names%TYPE, +-- last_name in persons.last_name%TYPE, +-- password in users.password%TYPE, +-- salt in users.salt%TYPE, +-- password_question in users.password_question%TYPE default null, +-- password_answer in users.password_answer%TYPE default null, +-- screen_name in users.screen_name%TYPE default null, +-- email_verified_p in users.email_verified_p%TYPE default 't', +-- member_state in membership_rels.member_state%TYPE default 'approved' +-- ) +-- return users.user_id%TYPE; +-- +-- procedure remove_user ( +-- user_id in users.user_id%TYPE +-- ); +-- +-- function magic_object_id ( +-- name in acs_magic_objects.name%TYPE +-- ) return acs_objects.object_id%TYPE; +-- +-- end acs; + +-- show errors + +-- create or replace package body acs +-- function add_user +create function acs__add_user (integer,varchar,timestamp,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar) +returns integer as ' +declare + user_id alias for $1; -- default null + object_type alias for $2; -- default ''user'' + creation_date alias for $3; -- default now() + creation_user alias for $4; -- default null + creation_ip alias for $5; -- default null + email alias for $6; + url alias for $7; -- default null + first_names alias for $8; + last_name alias for $9; + password alias for $10; + salt alias for $11; + password_question alias for $12; -- default null + password_answer alias for $13; -- default null + screen_name alias for $14; -- default null + email_verified_p alias for $15; -- default ''t'' + member_state alias for $16; -- default ''approved'' + v_user_id users.user_id%TYPE; + v_rel_id membership_rels.rel_id%TYPE; +begin + v_user_id := acs_user__new (user_id, object_type, creation_date, + creation_user, creation_ip, email, + url, first_names, last_name, password, + salt, password_question, password_answer, + screen_name, email_verified_p,null); + + v_rel_id := membership_rel__new ( + null, + ''membership_rel'', + acs__magic_object_id(''registered_users''), + v_user_id, + member_state, + null, + null); + + PERFORM acs_permission__grant_permission ( + v_user_id, + v_user_id, + ''read'' + ); + + PERFORM acs_permission__grant_permission ( + v_user_id, + v_user_id, + ''write'' + ); + + return v_user_id; + +end;' language 'plpgsql'; + + +-- procedure remove_user +create function acs__remove_user (integer) +returns integer as ' +declare + remove_user__user_id alias for $1; +begin + delete from users + where user_id = remove_user__user_id; + + return 0; +end;' language 'plpgsql'; + + +-- function magic_object_id +create function acs__magic_object_id (varchar) +returns integer as ' +declare + magic_object_id__name alias for $1; + magic_object_id__object_id acs_objects.object_id%TYPE; +begin + select object_id + into magic_object_id__object_id + from acs_magic_objects + where name = magic_object_id__name; + + return magic_object_id__object_id; + +end;' language 'plpgsql'; + + + +-- show errors + +-- ****************************************************************** +-- * Community Core API +-- ****************************************************************** + +create view registered_users +as + select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state + from parties p, persons pe, users u, group_member_map m, membership_rels mr + where party_id = person_id + and person_id = user_id + and u.user_id = m.member_id + and m.rel_id = mr.rel_id + and m.group_id = acs__magic_object_id('registered_users') + and mr.member_state = 'approved' + and u.email_verified_p = 't'; + +create view cc_users +as +select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id +from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr +where o.object_id = pa.party_id +and pa.party_id = pe.person_id +and pe.person_id = u.user_id +and u.user_id = m.member_id +and m.group_id = acs__magic_object_id('registered_users') +and m.rel_id = mr.rel_id +and m.container_id = m.group_id; + +----------------------------------- +-- Community Core Initialization -- +----------------------------------- + +-- The very first thing we must do is create the security_context_root +-- object. + +create function inline_0 () +returns integer as ' +declare + root_id integer; +begin + + root_id := acs_object__new ( + 0, + ''acs_object'', + now(), + null, + null, + null + ); + + insert into acs_magic_objects + (name, object_id) + values + (''security_context_root'', 0); + + + return root_id; + +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + + +-- show errors + +begin; + + -------------------------------------------------------------- + -- Some privilege that will be fundamental to all objects. -- + -------------------------------------------------------------- + + select acs_privilege__create_privilege('read', null, null); + select acs_privilege__create_privilege('write', null, null); + select acs_privilege__create_privilege('create', null, null); + select acs_privilege__create_privilege('delete', null, null); + select acs_privilege__create_privilege('admin', null, null); + + --------------------------------------------------------- + -- Administrators can read, write, create, and delete. -- + --------------------------------------------------------- + + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. + + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + + select acs_privilege__add_child('admin', 'read'); + select acs_privilege__add_child('admin', 'write'); + select acs_privilege__add_child('admin', 'create'); + + -- re-enable the trigger before the last insert to force the + -- acs_privilege_hierarchy_index table to be updated. + + create trigger acs_priv_hier_ins_del_tr after insert or delete + on acs_privilege_hierarchy for each row + execute procedure acs_priv_hier_ins_del_tr (); + + select acs_privilege__add_child('admin', 'delete'); + +end; + + +-- show errors + +create function inline_2 () +returns integer as ' +declare + v_object_id integer; +begin + + insert into acs_objects + (object_id, object_type) + values + (-1, ''party''); + + insert into parties + (party_id) + values + (-1); + + insert into acs_magic_objects + (name, object_id) + values + (''the_public'', -1); + + return 0; +end;' language 'plpgsql'; + +select inline_2 (); + +drop function inline_2 (); + + +create function inline_3 () +returns integer as ' +declare + group_id integer; +begin + + group_id := acs_group__new ( + -2, + ''group'', + now(), + null, + null, + null, + null, + ''Registered Users'', + null, + null + ); + + insert into acs_magic_objects + (name, object_id) + values + (''registered_users'', -2); + + return 0; +end;' language 'plpgsql'; + +select inline_3 (); + +drop function inline_3 (); + + +-- show errors Index: openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/acs-messaging-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-create.sql 22 May 2001 04:05:28 -0000 1.1 @@ -0,0 +1,139 @@ +-- +-- packages/acs-messaging/sql/acs-messaging-create.sql +-- +-- @author John Prevost +-- @creation-date 2000-08-27 +-- @cvs-id $Id: acs-messaging-create.sql,v 1.1 2001/05/22 04:05:28 jong Exp $ +-- + +-- Object System Metadata ---------------------------------------------- + +begin; +select acs_object_type__create_type ( + 'acs_message', + 'Message', + 'Messages', + 'content_item', + 'ACS_MESSAGES', + 'MESSAGE_ID', + null, + 'f', + null, + 'ACS_MESSAGE.NAME' + ); + + select acs_object_type__create_type ( + 'acs_message_revision', + 'Message Revision', + 'Message Revisions', + 'content_revision', + 'CR_REVISIONS', + 'REVISION_ID', + null, + 'f', + null, + 'ACS_OBJECT.DEFAULT_NAME' + ); +end; + + +-- Raw Tables and Comments --------------------------------------------- + +create table acs_messages ( -- extends cr_items + message_id integer + constraint acs_messages_message_id_fk + references cr_items (item_id) on delete cascade + constraint acs_messages_pk + primary key, + -- we will need to find a way to make reply_to go to 0 instead of null + -- to improve scalability + reply_to integer + constraint acs_messages_reply_to_fk + references acs_messages (message_id) on delete set null, + sent_date date + constraint acs_messages_sent_date_nn + not null, + sender integer + constraint acs_messages_sender_fk + references parties (party_id), + rfc822_id varchar(250) + constraint acs_messages_rfc822_id_nn + not null + constraint acs_messages_rfc822_id_un + unique +); + +create index acs_messages_reply_to_idx on acs_messages (reply_to); +create index acs_messages_sender_idx on acs_messages (sender); + +comment on table acs_messages is ' + A generic message which may be attached to any object in the system. +'; + +comment on column acs_messages.reply_to is ' + Pointer to a message this message contains a reply to, for threading. +'; + +comment on column acs_messages.sent_date is ' + The date the message was sent (may be distinct from when it was created + or published in the system.) +'; + +comment on column acs_messages.sender is ' + The person who sent the message (may be distinct from the person who + entered the message in the system.) +'; + +comment on column acs_messages.rfc822_id is ' + The RFC822 message-id of this message, for sending email. +'; + +create table acs_messages_outgoing ( + message_id integer + constraint amo_message_id_fk + references acs_messages (message_id) on delete cascade, + to_address varchar(1000) + constraint amo_to_address_nn + not null, + grouping_id integer, + wait_until date + constraint amo_wait_until_nn not null, + constraint acs_messages_outgoing_pk + primary key (message_id, to_address) +); + +comment on table acs_messages_outgoing is ' + Set of messages to be sent to parties. It is assumed that sending a + message either queues it in a real MTA or fails, so no information about + what''s been tried how many times is kept. +'; + +comment on column acs_messages_outgoing.to_address is ' + The email address to send this message to. Note that this will + probably become a party_id again once upgrading a party to a user + is possible. +'; + +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.) +'; + +@@ acs-messaging-views +@@ acs-messaging-packages + +set feedback on Index: openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/acs-messaging-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-drop.sql 22 May 2001 04:05:28 -0000 1.1 @@ -0,0 +1,22 @@ +-- +-- packages/acs-messaging/sql/acs-messaging-drop.sql +-- +-- @author akk@arsdigita.com +-- @creation-date 2000-08-31 +-- @cvs-id $Id: acs-messaging-drop.sql,v 1.1 2001/05/22 04:05:28 jong Exp $ +-- + +begin + acs_object_type.drop_type('acs_message'); +end; +/ +show errors + +drop package acs_message; + +drop table acs_messages_outgoing; + +drop view acs_messages_all; + +drop table acs_messages; + Index: openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/acs-messaging-packages.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-packages.sql 22 May 2001 04:05:28 -0000 1.1 @@ -0,0 +1,552 @@ +-- +-- packages/acs-messaging/sql/acs-messaging-packages.sql +-- +-- @author John Prevost +-- @author Phong Nguyen +-- @creation-date 2000-08-27 +-- @cvs-id $Id: acs-messaging-packages.sql,v 1.1 2001/05/22 04:05:28 jong Exp $ +-- + +--create function acs_message +--as +create function acs_message__new (integer, +returns integer as ' + message_id alias for $1; --default null, + reply_to alias for $2; --default null, + sent_date alias for $3; --default sysdate, + sender alias for $4; --default null, + rfc822_id alias for $5; --default null, + title alias for $6; --default null, + description alias for $7; --default null, + mime_type alias for $8; --default 'text/plain', + text alias for $9; --default null, + data alias for $10; --default null, + parent_id alias for $11; --default 0, + context_id alias for $12; + creation_date alias for $13; --default sysdate, + creation_user alias for $14; --default null, + creation_ip alias for $15; --default null, + object_type alias for $16; --default 'acs_message', + is_live alias for $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;' language 'plpgsql'; + + +--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 Index: openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-views.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/acs-messaging-views.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-views.sql 22 May 2001 04:05:28 -0000 1.1 @@ -0,0 +1,21 @@ +-- +-- packages/acs-messaging/sql/acs-messaging-create.sql +-- +-- @author John Prevost +-- @creation-date 2000-11-15 +-- @cvs-id $Id: acs-messaging-views.sql,v 1.1 2001/05/22 04:05:28 jong Exp $ +-- + +create or replace view acs_messages_all as + select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id, + r.revision_id, r.title, r.mime_type, r.content + from cr_items i, cr_revisions r, acs_messages m + where i.item_id = m.message_id and r.revision_id = i.live_revision; + +create or replace view acs_messages_latest as + select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id, + r.revision_id, r.title, r.mime_type, r.content + from cr_items i, cr_revisions r, acs_messages m + where i.item_id = m.message_id + and r.revision_id = content_item.get_latest_revision(i.item_id); + Index: openacs-4/packages/acs-messaging/sql/postgres/upgrade-4.0-4.0.1a.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/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/postgres/upgrade-4.0-4.0.1a.sql 22 May 2001 04:05:28 -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/22 04:05:28 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/postgres/upgrade-4.0.1-4.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/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/postgres/upgrade-4.0.1-4.1.sql 22 May 2001 04:05:28 -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/22 04:05:28 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/postgres/upgrade-4.0.1a-4.0.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/Attic/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/postgres/upgrade-4.0.1a-4.0.1.sql 22 May 2001 04:05:28 -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/22 04:05:28 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/postgres/upgrade/upgrade-4.0-4.0.1a.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/upgrade/Attic/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/postgres/upgrade/upgrade-4.0-4.0.1a.sql 22 May 2001 04:05:47 -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/22 04:05:47 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/postgres/upgrade/upgrade-4.0.1-4.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/upgrade/Attic/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/postgres/upgrade/upgrade-4.0.1-4.1.sql 22 May 2001 04:05:47 -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/22 04:05:47 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/postgres/upgrade/upgrade-4.0.1a-4.0.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/upgrade/Attic/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/postgres/upgrade/upgrade-4.0.1a-4.0.1.sql 22 May 2001 04:05:47 -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/22 04:05:47 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/postgres/upgrade/upgrade-4.1-4.1.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgres/upgrade/Attic/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/postgres/upgrade/upgrade-4.1-4.1.1.sql 22 May 2001 04:05:47 -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/22 04:05:47 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/22 04:05:47 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 + +