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
