Index: openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 23 May 2001 16:38:19 -0000 1.1 +++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 1 Jun 2001 13:02:40 -0000 1.2 @@ -14,46 +14,46 @@ 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'' + p_message_id alias for $1; + p_title alias for $2; -- default null + p_description alias for $3; -- default null + p_mime_type alias for $4; -- default ''text/plain'' + p_text alias for $5; -- default null + p_data alias for $6; -- default null + p_creation_date alias for $7; -- default sysdate + p_creation_user alias for $8; -- default null + p_creation_ip alias for $9; -- default null + p_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 + if p_data is not null then + v_revision_id := content_revision__new ( + item_id => p_message_id, + title => p_title, + description => p_description, + data => p_data, + mime_type => p_mime_type, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip ); - else if title is not null or text is not null then + else if p_title is not null or p_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 + item_id => p_message_id, + title => p_title, + description => p_description, + text => p_text, + mime_type => p_mime_type, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip ); end if; -- test for auto approval of revision - if edit.is_live = ''t'' then - content_item.set_live_revision(v_revision_id); + if p_is_live = ''t'' then + content_item__set_live_revision(v_revision_id); end if; return v_revision_id; end;' language 'plpgsql'; @@ -73,33 +73,33 @@ 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'', - 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 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'' + p_message_id alias for $1; --default null, + p_reply_to alias for $2; --default null, + p_sent_date alias for $3; --default sysdate, + p_sender alias for $4; --default null, + p_rfc822_id alias for $5; --default null, + p_title alias for $6; --default null, + p_description alias for $7; --default null, + p_mime_type alias for $8; --default ''text/plain'', + p_text alias for $9; --default null, + p_data alias for $10; --default null, + p_parent_id alias for $11; --default 0, + p_context_id alias for $12; + p_creation_date timestamp := current_timestamp; -- alias for $13 --default sysdate, + p_creation_user alias for $13; --default null, + p_creation_ip alias for $14; --default null, + p_object_type alias for $15; --default ''acs_message'', + p_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; + if p_message_id is null then + select acs_object_id_seq.nextval into v_message_id; else - v_message_id := message_id; + v_message_id := p_message_id; end if; -- need to make this mandatory also - jg @@ -109,38 +109,38 @@ -- v_rfc822_id := now || ''.'' || v_message_id || ''@'' || -- utl_inaddr.get_host_name || ''.hate''; --else - v_rfc822_id := rfc822_id; + v_rfc822_id := p_rfc822_id; --end if; v_message_id := content_item__new ( name => v_rfc822_id, - parent_id => parent_id, + parent_id => p_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 + item_id => p_message_id, + context_id => p_context_id, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip, + item_subtype => p_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); + (v_message_id, p_reply_to, p_sent_date, p_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 + title => p_title, + description => p_description, + mime_type => p_mime_type, + text => p_text, + data => p_data, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip, + is_live => p_is_live ); return v_message_id; @@ -149,23 +149,23 @@ create function acs_message__delete (integer) returns integer as ' declaration - message_id in acs_messages.message_id%TYPE; + p_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); + where p_message_id = acs_message.delete.message_id; + content_item.delete(p_message_id); return 1; end;' language 'plpgsql'; create function acs_message__message_p (integer) returns boolean as ' declaration - message_id alias for $1; + p_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; + where message_id = p_message_id; if v_check_message_id <> 0 then return ''t''; else @@ -176,30 +176,30 @@ 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 + p_message_id alias for $1; + p_to_address alias for $2; + p_grouping_id alias for $3; -- default null + p_wait_until alias for $4; -- default sysdate v_wait_until timestamp; begin - v_wait_until := coalesce(wait_until, now); + v_wait_until := coalesce(p_wait_until, current_timestamp); insert into acs_messages_outgoing (message_id, to_address, grouping_id, wait_until) values - (message_id, to_address, grouping_id, v_wait_until); + (p_message_id, p_to_address, p_grouping_id, v_wait_until); return 1; end;' language 'plpgsql'; 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 + p_message_id alias for $1; + p_recipient_id alias for $2; + p_grouping_id alias for $3; -- default null + p_wait_until alias for $4; -- default sysdate v_wait_until timestamp; begin - v_wait_until := coalesce (wait_until, now()); + v_wait_until := coalesce (p_wait_until, current_timestamp); 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 @@ -237,40 +237,40 @@ 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'' + p_message_id alias for $1; + p_file_id alias for $2; -- default null + p_file_name alias for $3; + p_title alias for $4; -- default null + p_description alias for $5; -- default null + p_mime_type alias for $6; -- default ''text/plain'' + p_content alias for $7; -- default null + p_creation_date alias for $8; -- default sysdate + p_creation_user alias for $9; -- default null + p_creation_ip alias for $10; -- default null + p_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 + name => p_file_name, + parent_id => p_message_id, + item_id => p_file_id, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_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 + title => p_title, + description => p_description, + mime_type => p_mime_type, + content => p_content, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip, + is_live => p_is_live ); return v_file_id; @@ -280,25 +280,25 @@ 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'' + p_file_id alias for $1; + p_title alias for $2; -- default null + p_description alias for $3; -- default null + p_mime_type alias for $4; -- default ''text/plain'' + p_content alias for $5; -- default null + p_creation_date alias for $6; -- default sysdate + p_creation_user alias for $7; -- default null + p_creation_ip alias for $8; -- default null + p_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 + title => p_title, + mime_type => p_mime_type, + data => p_content, + item_id => p_file_id, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip ); -- test for auto approval of revision @@ -312,56 +312,54 @@ create function acs_message__delete_file (integer) returns integer as ' declaration - file_id alias for $1; + p_file_id alias for $1; begin - content_item__delete(delete_file.file_id); + content_item__delete(p_file_id); return 1; end;' language 'plpgsql'; -drop function acs_message__new_image (integer,integer,varchar,varchar,text,varchar,text,integer,integer,timestamp,integer,varchar,boolean); - 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'' + p_message_id alias for $1; + p_image_id alias for $2; -- default null + p_file_name alias for $3; + p_title alias for $4; -- default null + p_description alias for $5; -- default null + p_mime_type alias for $6; -- default ''text/plain'' + p_content alias for $7; -- default null + p_width alias for $8; -- default null + p_height alias for $9; -- default null + p_creation_date alias for $10; -- default sysdate + p_creation_user alias for $11; -- default null + p_creation_ip alias for $12; -- default null + p_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 + name => p_file_name, + parent_id => p_message_id, + item_id => p_image_id, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip ); -- 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 + title => p_title, + description => p_description, + mime_type => p_mime_type, + content => p_content, + width => p_width, + height => p_height, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip, + is_live => p_is_live ); return v_image_id; @@ -371,38 +369,38 @@ 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'' + p_image_id alias for $1; + p_title alias for $2; -- default null + p_description alias for $3; -- default null + p_mime_type alias for $4; -- default ''text/plain'' + p_content alias for $5; -- default null + p_width alias for $6; -- default null + p_height alias for $7; -- default null + p_creation_date alias for $8; -- default sysdate + p_creation_user alias for $9; -- default null + p_creation_ip alias for $10; -- default null + p_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 + title => p_title, + mime_type => p_mime_type, + data => p_content, + item_id => p_image_id, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_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); + (v_revision_id, p_width, p_height); -- test for auto approval of revision - if edit_image.is_live = ''t'' then + if p_is_live = ''t'' then content_item__set_live_revision(v_revision_id); end if; @@ -412,12 +410,12 @@ create function acs_message__delete_image (integer) returns integer as ' declaration - image_id alias for $1; + p_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); + where image_id = p_image_id; + content_item__delete(p_image_id); return 1; end;' language 'plpgsql'; @@ -426,68 +424,67 @@ 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 + p_name alias for $1; -- default null + p_extlink_id alias for $2; -- default null + p_url alias for $3; + p_label alias for $4; -- default null + p_description alias for $5; -- default null + p_parent_id alias for $6; + p_creation_date alias for $7; -- default sysdate + p_creation_user alias for $8; -- default null + p_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 + name => p_name, + url => p_url, + label => p_label, + description => p_description, + parent_id => p_parent_id, + extlink_id => p_extlink_id, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip ); end;' language 'plpgsql'; -- 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 + p_extlink_id alias for $1; + p_url alias for $2; + p_label alias for $3; -- default null + p_description alias for $4; -- default null v_is_extlink char; begin - v_is_extlink := content_extlink__is_extlink(edit_extlink.extlink_id); + v_is_extlink := content_extlink__is_extlink(p_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; + set url = p_url, + label = p_label, + description = p_description + where extlink_id = p_extlink_id; end if; return v_is_extlink; end;' language 'plpgsql'; create function acs_message__delete_extlink (integer) returns integer as ' declaration - extlink_id alias for $1; + p_extlink_id alias for $1; begin - content_extlink__delete(extlink_id => delete_extlink.extlink_id); + content_extlink__delete(p_extlink_id); end;' language 'plpgsql'; create function acs_message__name (integer) returns varchar as ' - message_id alias for $1; + p_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; + where message_id = p_message_id; return v_message_name; end;' language 'plpgsql'; -