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 -N -r1.1 -r1.2 --- openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-create.sql 22 May 2001 04:05:28 -0000 1.1 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-create.sql 23 May 2001 05:56:50 -0000 1.2 @@ -2,39 +2,39 @@ -- packages/acs-messaging/sql/acs-messaging-create.sql -- -- @author John Prevost +-- @author Jon Griffin -- @creation-date 2000-08-27 --- @cvs-id $Id$ -- +-- @cvs-id $Id$ +-- updated for OpenACS -- 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' - ); + '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; +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' +); -- Raw Tables and Comments --------------------------------------------- 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 -N -r1.1 -r1.2 --- openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-drop.sql 22 May 2001 04:05:28 -0000 1.1 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-drop.sql 23 May 2001 05:56:50 -0000 1.2 @@ -7,16 +7,14 @@ -- begin - acs_object_type.drop_type('acs_message'); + acs_object_type__drop_type('acs_message'); end; -/ -show errors -drop package acs_message; +--drop package acs_message; -drop table acs_messages_outgoing; +--drop table acs_messages_outgoing; -drop view acs_messages_all; +--drop view acs_messages_all; -drop table acs_messages; +--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 -N -r1.1 -r1.2 --- openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-packages.sql 22 May 2001 04:05:28 -0000 1.1 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-packages.sql 23 May 2001 05:56:50 -0000 1.2 @@ -3,58 +3,122 @@ -- -- @author John Prevost -- @author Phong Nguyen +-- @author Jon Griffin -- @creation-date 2000-08-27 -- @cvs-id $Id$ -- +-- updated for OpenACS by Jon Griffin +-- ---create function acs_message ---as -create function acs_message__new (integer, +-- this needs to be first as I don't see a way in +-- plpgsql to have forward declarations or the like. + +create function acs_message__edit (integer,varchar,varchar,varchar, +text,timestamp,integer,varchar,boolean) returns integer as ' +declaration + message_id alias for $1; + title alias for $2; -- default null + description alias for $3; -- default null + mime_type alias for $4; -- default ''text/plain'' + text alias for $5; -- default null + data alias for $6; -- default null + creation_date alias for $7; -- default sysdate + creation_user alias for $8; -- default null + creation_ip alias for $9; -- default null + is_live alias for $10; -- default ''t'' + 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 + ); + else if 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;' language 'plpgsql'; + +---------------- +-- MAJOR NOTE OF NON-COMPLIANCE +-- I am exercising my rights as the porter here! +-- I can only use 16 parameters so I am changing one +-- creation_date will default to sysdate and not be a parameter +-- possibly another function can be made to change that +-- although I really don't see much need for this. +-- Jon Griffin 05-21-2001 +---------------- + +create function acs_message__new (integer,integer,timestamp,integer, +varchar,varchar,varchar,varchar,varchar,text,integer,integer,integer, +varchar,integer,boolean) +returns integer as ' +declare 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', + 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 + creation_date timestamp := now(); -- alias for $13 --default sysdate, + creation_user alias for $13; --default null, + creation_ip alias for $14; --default null, + object_type alias for $15; --default ''acs_message'', + is_live alias for $16; --default ''t'' 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; + select acs_object_id_seq__nextval into v_message_id; else v_message_id := message_id; end if; + -- need to make this mandatory also - jg -- 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 + -- if rfc822_id is null then + -- v_rfc822_id := now || ''.'' || v_message_id || ''@'' || + -- utl_inaddr.get_host_name || ''.hate''; + --else v_rfc822_id := rfc822_id; - end if; + --end if; - v_message_id := content_item.new ( + v_message_id := content_item__new ( name => v_rfc822_id, parent_id => parent_id, - content_type => 'acs_message_revision', + content_type => ''acs_message_revision'', item_id => message_id, context_id => context_id, creation_date => creation_date, @@ -69,7 +133,7 @@ (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 ( + v_revision_id := acs_message__edit ( message_id => v_message_id, title => title, description => description, @@ -83,470 +147,350 @@ ); return v_message_id; - end;' language 'plpgsql'; +end;' language 'plpgsql'; +create function acs_message__delete (integer) +returns integer as ' +declaration + message_id in acs_messages.message_id%TYPE; +begin + delete from acs_messages + where message_id = acs_message.delete.message_id; + content_item.delete(message_id); + return 1; +end;' language 'plpgsql'; ---create or replace package body acs_message ---as +create function acs_message__message_p (integer) +returns boolean as ' +declaration + message_id alias for $1; + v_check_message_id integer; +begin + select (case when count(message_id) = 0 then 0 else 1 end) 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;' language 'plpgsql'; - 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; +create function acs_message__send (integer,varchar,integer,timestamp) +returns integer as ' +declaration + message_id alias for $1; + to_address alias for $2; + grouping_id alias for $3; -- default null + wait_until alias for $4; -- default sysdate + v_wait_until timestamp; +begin + v_wait_until := coalesce(wait_until, now); + insert into acs_messages_outgoing + (message_id, to_address, grouping_id, wait_until) + values + (message_id, to_address, grouping_id, v_wait_until); + return 1; +end;' language 'plpgsql'; - -- 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; +create function acs_message__send (integer,integer,integer,timestamp) +returns integer as ' +declaration + message_id alias for $1; + recipient_id alias for $2; + grouping_id alias for $3; -- default null + wait_until alias for $4; -- default sysdate + v_wait_until timestamp; +begin + v_wait_until := coalesce (wait_until, now()); + 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; + return 1; +end;' language 'plpgsql'; - 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); +-- This needs work as there is no connect by - -- 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 - ); +create function acs_message__first_ancestor (integer) +returns integer as ' +declaration + message_id alias for $1; + 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 new; + return v_message_id; +end;' language 'plpgsql'; - 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 +create function acs_message__new_file (integer,integer,varchar,varchar, +text,varchar,text,timestamp,integer,varchar,boolean) +returns integer as ' + message_id alias for $1; + file_id alias for $2; -- default null + file_name alias for $3; + title alias for $4; -- default null + description alias for $5; -- default null + mime_type alias for $6; -- default ''text/plain'' + content alias for $7; -- default null + creation_date alias for $8; -- default sysdate + creation_user alias for $9; -- default null + creation_ip alias for $10; -- default null + is_live alias for $11; -- default ''t'' + 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 + ); - 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 := acs_file__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 + ); - -- 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;' language 'plpgsql'; - return v_file_id; - end new_file; +create function acs_message__edit_file (integer,varchar,text,varchar, +text,timestamp,integer,varchar,boolean) +returns integer as ' +declare + file_id alias for $1; + title alias for $2; -- default null + description alias for $3; -- default null + mime_type alias for $4; -- default ''text/plain'' + content alias for $5; -- default null + creation_date alias for $6; -- default sysdate + creation_user alias for $7; -- default null + creation_ip alias for $8; -- default null + is_live alias for $9; -- default ''t'' + 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 + ); - 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 + -- test for auto approval of revision + if is_live = ''t'' then + content_item__set_live_revision(v_revision_id); + end if; - 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 - ); + return v_revision_id; +end;' language 'plpgsql'; - -- test for auto approval of revision - if is_live = 't' then - content_item.set_live_revision(v_revision_id); - end if; +create function acs_message__delete_file (integer) +returns integer as ' +declaration + file_id alias for $1; +begin + content_item__delete(delete_file.file_id); + return 1; +end;' language 'plpgsql'; - return v_revision_id; - end edit_file; +drop function acs_message__new_image (integer,integer,varchar,varchar,text,varchar,text,integer,integer,timestamp,integer,varchar,boolean); - procedure delete_file ( - file_id in cr_items.item_id%TYPE - ) - is - begin - content_item.delete(delete_file.file_id); - end delete_file; +create function acs_message__new_image (integer,integer,varchar,varchar, +text,varchar,text,integer,integer,timestamp,integer,varchar,boolean) +returns integer as ' +declaration + message_id alias for $1; + image_id alias for $2; -- default null + file_name alias for $3; + title alias for $4; -- default null + description alias for $5; -- default null + mime_type alias for $6; -- default ''text/plain'' + content alias for $7; -- default null + width alias for $8; -- default null + height alias for $9; -- default null + creation_date alias for $10; -- default sysdate + creation_user alias for $11; -- default null + creation_ip alias for $12; -- default null + is_live alias for $13; -- default ''t'' + 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 + ); - 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 + -- create an initial revision for the new attachment + v_revision_id := acs_message__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 + ); - 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 - ); + return v_image_id; +end;' language 'plpgsql'; - -- 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 - ); +create function acs_message__edit_image (integer,varchar,text,varchar, +text,integer,integer,timestamp,integer,varchar,boolean) +returns integer as ' +declaration + image_id alias for $1; + title alias for $2; -- default null + description alias for $3; -- default null + mime_type alias for $4; -- default ''text/plain'' + content alias for $5; -- default null + width alias for $6; -- default null + height alias for $7; -- default null + creation_date alias for $8; -- default sysdate + creation_user alias for $9; -- default null + creation_ip alias for $10; -- default null + is_live alias for $11; -- default ''t'' + 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 + ); - return v_image_id; - end new_image; + -- 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); - 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 + -- test for auto approval of revision + if edit_image.is_live = ''t'' then + content_item__set_live_revision(v_revision_id); + end if; - 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 - ); + return v_revision_id; +end;' language 'plpgsql'; - -- 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); +create function acs_message__delete_image (integer) +returns integer as ' +declaration + image_id alias for $1; +begin + -- XXX fix after image.delete exists + delete from images + where image_id = delete_image.image_id; + content_item.delete(image_id); + return 1; +end;' language 'plpgsql'; - -- 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; +create function acs_message__new_extlink (varchar,integer,varchar, +varchar,text,integer,timestamp,integer,varchar) +returns integer as ' +declaration + name alias for $1; -- default null + extlink_id alias for $2; -- default null + url alias for $3; + label alias for $4; -- default null + description alias for $5; -- default null + parent_id alias for $6; + creation_date alias for $7; -- default sysdate + creation_user alias for $8; -- default null + creation_ip alias for $9; -- default null + 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;' language 'plpgsql'; - -- 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; +-- XXX should just edit extlink +create function acs_message__edit_extlink (integer,varchar,varchar,text) +returns integer as ' +declaration + extlink_id alias for $1; + url alias for $2; + label alias for $3; -- default null + description alias for $4; -- default null + 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;' language 'plpgsql'; - 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; +create function acs_message__delete_extlink (integer) +returns integer as ' +declaration + extlink_id alias for $1; +begin + content_extlink__delete(extlink_id => delete_extlink.extlink_id); +end;' language 'plpgsql'; - 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; +create function acs_message__name (integer) +returns varchar as ' + message_id alias for $1; + 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;' language 'plpgsql'; -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 -N -r1.1 -r1.2 --- openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-views.sql 22 May 2001 04:05:28 -0000 1.1 +++ openacs-4/packages/acs-messaging/sql/postgres/acs-messaging-views.sql 23 May 2001 05:56:50 -0000 1.2 @@ -2,20 +2,23 @@ -- packages/acs-messaging/sql/acs-messaging-create.sql -- -- @author John Prevost +-- @author Jon Griffin +-- -- @creation-date 2000-11-15 -- @cvs-id $Id$ -- +-- Updated by Jon Griffin for OpenACS -create or replace view acs_messages_all as +create 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 +create 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); + 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 -N --- openacs-4/packages/acs-messaging/sql/postgres/upgrade-4.0-4.0.1a.sql 22 May 2001 04:05:28 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,242 +0,0 @@ --- --- 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 -N --- openacs-4/packages/acs-messaging/sql/postgres/upgrade-4.0.1-4.1.sql 22 May 2001 04:05:28 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,12 +0,0 @@ --- --- 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 -N --- openacs-4/packages/acs-messaging/sql/postgres/upgrade-4.0.1a-4.0.1.sql 22 May 2001 04:05:28 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,49 +0,0 @@ --- --- 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