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.20 -r1.21 --- openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 30 Mar 2013 13:22:22 -0000 1.20 +++ openacs-4/packages/acs-messaging/sql/postgresql/acs-messaging-packages.sql 7 Aug 2017 23:47:57 -0000 1.21 @@ -29,7 +29,6 @@ p_creation_user integer, -- default null p_creation_ip varchar, -- default null p_is_live boolean -- default 't' - ) RETURNS integer AS $$ DECLARE v_revision_id cr_revisions.revision_id%TYPE; @@ -38,28 +37,32 @@ if p_data is not null then -- need to take care of blob? v_revision_id := content_revision__new ( - p_message_id, -- item_id - p_title, -- title - p_description, -- description - p_data, -- data - p_mime_type, -- mime_type - p_creation_date, -- creation_date - p_creation_user, -- creation_user - p_creation_ip -- creation_ip + p_title, -- title + p_description, -- description + now(), -- publish_date + p_mime_type, -- mime_type + null, -- nls_language + p_data, -- data + p_message_id, -- item_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip ); else if p_title is not null or p_text is not null then v_revision_id := content_revision__new ( - p_title, -- title - p_description, -- description - now(), -- publish_date - p_mime_type, -- mime_type - null, -- nls_language - p_text, -- text - p_message_id, -- item_id - null, -- revision_id - p_creation_date, -- creation_date - p_creation_user, -- creation_user - p_creation_ip -- creation_ip + p_title, -- title + p_description, -- description + now(), -- publish_date + p_mime_type, -- mime_type + null, -- nls_language + p_text, -- text + p_message_id, -- item_id + null, -- revision_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip, -- creation_ip + null, -- content_length + null -- package_id ); end if; end if; @@ -108,66 +111,73 @@ p_creation_ip varchar, --default null, p_object_type varchar, --default 'acs_message', p_is_live boolean, --default 't' - p_package_id integer + p_package_id integer default null ) RETURNS integer AS $$ DECLARE p_creation_date timestamptz := current_timestamp; -- alias for $13 --default sysdate, v_message_id acs_messages.message_id%TYPE; - v_rfc822_id acs_messages.rfc822_id%TYPE; v_revision_id cr_revisions.revision_id%TYPE; - v_system_url varchar; - v_domain_name varchar; - v_idx integer; BEGIN - -- generate a message id now so we can get an rfc822 message-id - if p_message_id is null then - select nextval('t_acs_object_id_seq') into v_message_id; - else - v_message_id := p_message_id; - end if; + -- -- generate a message id now so we can get an rfc822 message-id + -- if p_message_id is null then + -- select nextval('t_acs_object_id_seq') into v_message_id; + -- else + -- v_message_id := p_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 + -- -- 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 - -- vk: get SystemURL parameter and use it to extract domain name - select apm__get_value(package_id, 'SystemURL') into v_system_url - from apm_packages where package_key='acs-kernel'; - v_idx := position('http://' in v_system_url); - v_domain_name := trim (substr(v_system_url, v_idx + 7)); + -- -- vk: get SystemURL parameter and use it to extract domain name + -- select apm__get_value(package_id, 'SystemURL') into v_system_url + -- from apm_packages where package_key='acs-kernel'; + -- v_idx := position('http://' in v_system_url); + -- v_domain_name := trim (substr(v_system_url, v_idx + 7)); - if p_rfc822_id is null then - v_rfc822_id := current_date || '.' || v_message_id || '@' || - v_domain_name || '.hate'; - else - v_rfc822_id := p_rfc822_id; + -- if p_rfc822_id is null then + -- v_rfc822_id := current_date || '.' || v_message_id || '@' || + -- v_domain_name || '.hate'; + -- else + -- v_rfc822_id := p_rfc822_id; + -- end if; + + -- Antonio Pisano 2016-09-20 + -- rfc822_id MUST come from the tcl, no more + -- sql tricks to retrieve one if missing. + -- Motivations: + -- 1) duplication. We have same logics in acs_mail_lite::generate_message_id + -- 2) what if SystemURL is https? + -- 3) empty SystemURL would break General Comments + if p_rfc822_id is null then + RAISE null_value_not_allowed; end if; v_message_id := content_item__new ( - v_rfc822_id, -- name - p_parent_id, -- parent_id - p_message_id, -- item_id - null, -- locale - p_creation_date, -- creation_date - p_creation_user, -- creation_user - p_context_id, -- context_id - p_creation_ip, -- creation_ip - p_object_type, -- item_subtype - 'acs_message_revision', -- content_type - null, -- title - null, -- description - 'text/plain', -- mime_type - null, -- nls_language - null, -- text - 'text', -- storage_type - p_package_id + p_rfc822_id, -- 1 name + p_parent_id, -- 2 parent_id + p_message_id, -- 3 item_id + null, -- 4 locale + p_creation_date, -- 5 creation_date + p_creation_user, -- 6 creation_user + p_context_id, -- 7 context_id + p_creation_ip, -- 8 creation_ip + p_object_type, -- 9 item_subtype + 'acs_message_revision', -- 10 content_type + null, -- 11 title + null, -- 12 description + 'text/plain', -- 13 mime_type + null, -- 14 nls_language + null, -- 15 text + 'text', -- 16 storage_type + p_package_id -- 17 package_id ); insert into acs_messages (message_id, reply_to, sent_date, sender, rfc822_id) values - (v_message_id, p_reply_to, p_sent_date, p_sender, v_rfc822_id); + (v_message_id, p_reply_to, p_sent_date, p_sender, p_rfc822_id); -- create an initial revision for the new message v_revision_id := acs_message__edit ( @@ -188,55 +198,6 @@ $$ LANGUAGE plpgsql; - --- --- procedure acs_message__new/16 --- -CREATE OR REPLACE FUNCTION acs_message__new( - p_message_id integer, --default null, - p_reply_to integer, --default null, - p_sent_date timestamptz, --default sysdate, - p_sender integer, --default null, - p_rfc822_id varchar, --default null, - p_title varchar, --default null, - p_description varchar, --default null, - p_mime_type varchar, --default 'text/plain', - p_text text, --default null, - p_data integer, --default null, - p_parent_id integer, --default 0, - p_context_id integer, - p_creation_user integer, --default null, - p_creation_ip varchar, --default null, - p_object_type varchar, --default 'acs_message', - p_is_live boolean --default 't' - -) RETURNS integer AS $$ -DECLARE - p_creation_date timestamptz := current_timestamp; -- alias for $13 --default sysdate, -BEGIN - return acs_message__new (p_message_id, - p_reply_to, - p_sent_date, - p_sender, - p_rfc822_id, - p_title, - p_description, - p_mime_type, - p_text, - p_data, - p_parent_id, - p_context_id, - p_creation_user, - p_creation_ip, - p_object_type, - p_is_live, - null::integer - ); -END; -$$ LANGUAGE plpgsql; - - - -- added select define_function_args('acs_message__delete','message_id'); @@ -370,29 +331,26 @@ -- functions will migrate to another PL/SQL package or be replaced -- by direct calls to CR code in the near future. - - --- added select define_function_args('acs_message__new_file','message_id,file_id;null,file_name,title;null,description;null,mime_type;text/plain,data;null,creation_date;sysdate,creation_user;null,creation_ip;null,is_live;t,storage_type;file,package_id;null'); -- -- procedure acs_message__new_file/13 -- CREATE OR REPLACE FUNCTION acs_message__new_file( p_message_id integer, - p_file_id integer, -- default null + p_file_id integer, -- default null p_file_name varchar, - p_title varchar, -- default null - p_description text, -- default null - p_mime_type varchar, -- default 'text/plain' - p_data integer, -- default null - p_creation_date timestamptz, -- default sysdate - p_creation_user integer, -- default null - p_creation_ip varchar, -- default null - p_is_live boolean, -- default 't' - p_storage_type varchar, -- default 'file' - p_package_id integer -- default null - + p_title varchar, -- default null + p_description text, -- default null + p_mime_type varchar, -- default 'text/plain' + p_data integer, -- default null + p_creation_date timestamptz, -- default sysdate + p_creation_user integer, -- default null + p_creation_ip varchar, -- default null + p_is_live boolean, -- default 't' + p_storage_type cr_items.storage_type%TYPE, -- default 'file' + p_package_id integer default null + ) RETURNS integer AS $$ DECLARE v_file_id cr_items.item_id%TYPE; @@ -407,15 +365,19 @@ p_creation_user, -- creation_user null, -- context_id p_creation_ip, -- creation_ip - 'content_item', -- item_subtype + 'content_item', -- item_subtype 'content_revision', -- content_type null, -- title null, -- description 'text/plain', -- mime_type null, -- nls_language null, -- text + null, -- data + null, -- relation_tag + false, -- is_live p_storage_type, -- storage_type - p_package_id -- package_id + p_package_id, -- package_id + true -- with_child_rels ); -- create an initial revision for the new attachment @@ -437,45 +399,6 @@ --- --- procedure acs_message__new_file/12 --- -CREATE OR REPLACE FUNCTION acs_message__new_file( - p_message_id integer, - p_file_id integer, -- default null - p_file_name varchar, - p_title varchar, -- default null - p_description text, -- default null - p_mime_type varchar, -- default 'text/plain' - p_data integer, -- default null - p_creation_date timestamptz, -- default sysdate - p_creation_user integer, -- default null - p_creation_ip varchar, -- default null - p_is_live boolean, -- default 't' - p_storage_type varchar -- default 'file' - -) RETURNS integer AS $$ -DECLARE -BEGIN - return acs_message__new_file (p_message_id, - p_file_id, - p_file_name, - p_title, - p_description, - p_mime_type, - p_data, - p_creation_date, - p_creation_user, - p_creation_ip, - p_is_live, - p_storage_type, - null - ); -END; -$$ LANGUAGE plpgsql; - - - -- added select define_function_args('acs_message__edit_file','file_id,title;null,description;null,mime_type;text/plain,data;null,creation_date;sysdate,creation_user;null,creation_ip;null,is_live;t'); @@ -548,20 +471,20 @@ -- CREATE OR REPLACE FUNCTION acs_message__new_image( p_message_id integer, - p_image_id integer, -- default null + p_image_id integer, -- default null p_file_name varchar, - p_title varchar, -- default null - p_description text, -- default null - p_mime_type varchar, -- default 'text/plain' - p_data integer, -- default null - p_width integer, -- default null - p_height integer, -- default null - p_creation_date timestamptz, -- default sysdate - p_creation_user integer, -- default null - p_creation_ip varchar, -- default null - p_is_live boolean, -- default 't' - p_storage_type varchar, -- default 'file' - p_package_id integer -- default null + p_title varchar, -- default null + p_description text, -- default null + p_mime_type varchar, -- default 'text/plain' + p_data integer, -- default null + p_width integer, -- default null + p_height integer, -- default null + p_creation_date timestamptz, -- default sysdate + p_creation_user integer, -- default null + p_creation_ip varchar, -- default null + p_is_live boolean, -- default 't' + p_storage_type cr_items.storage_type%TYPE, -- default 'file' + p_package_id integer default null ) RETURNS integer AS $$ DECLARE @@ -581,10 +504,10 @@ 'content_revision', -- content_type null, -- title null, -- description - 'text/plain', -- mime_type + 'text/plain', -- mime_type null, -- nls_language null, -- text - 'file', -- storage_type + p_storage_type, -- storage_type p_package_id -- package_id ); @@ -608,50 +531,6 @@ $$ LANGUAGE plpgsql; - --- --- procedure acs_message__new_image/14 --- -CREATE OR REPLACE FUNCTION acs_message__new_image( - p_message_id integer, - p_image_id integer, -- default null - p_file_name varchar, - p_title varchar, -- default null - p_description text, -- default null - p_mime_type varchar, -- default 'text/plain' - p_data integer, -- default null - p_width integer, -- default null - p_height integer, -- default null - p_creation_date timestamptz, -- default sysdate - p_creation_user integer, -- default null - p_creation_ip varchar, -- default null - p_is_live boolean, -- default 't' - p_storage_type varchar -- default 'file' - -) RETURNS integer AS $$ -DECLARE -BEGIN - return acs_message__new_image (p_message_id, - p_image_id, - p_file_name, - p_title, - p_description, - p_mime_type, - p_data, - p_width, - p_height, - p_creation_date, - p_creation_user, - p_creation_ip, - p_is_live, - p_storage_type, - null - ); -END; -$$ LANGUAGE plpgsql; - - - -- added select define_function_args('acs_message__edit_image','image_id,title;null,description;null,mime_type;text/plain,data;null,width;null,height;null,creation_date;sysdate,creation_user;null,creation_ip;null,is_live;t'); @@ -675,7 +554,7 @@ DECLARE v_revision_id cr_revisions.revision_id%TYPE; BEGIN - -- not sure which __new to use + -- not sure which __new to use v_revision_id := content_revision__new ( p_title, -- title NULL, -- description