Index: openacs-4/packages/news/sql/postgresql/news-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/postgresql/news-package-create.sql,v
diff -u -N -r1.5 -r1.5.2.1
--- openacs-4/packages/news/sql/postgresql/news-package-create.sql	8 Nov 2010 07:24:44 -0000	1.5
+++ openacs-4/packages/news/sql/postgresql/news-package-create.sql	2 Nov 2013 16:08:07 -0000	1.5.2.1
@@ -7,64 +7,62 @@
 -- OpenACS Port: Robert Locke (rlocke@infiniteinfo.com)
 
 -- *** PACKAGE NEWS, plsql to create content_item ***
-select define_function_args ('news__new','item_id,locale,publish_date,text,nls_language,title,mime_type;text/plain,package_id,archive_date,approval_user,approval_date,approval_ip,relation_tag,creation_ip,creation_user,is_live_p;f,lead');
 
-create or replace function news__new (integer,varchar,timestamptz,text,varchar,varchar,
-       varchar,integer,timestamptz,integer,timestamptz,varchar,varchar,
-       varchar,integer,boolean, varchar)
-returns integer as '
-declare
-    p_item_id       alias for $1;  -- default null
-    --
-    p_locale        alias for $2;  -- default null,
-    --
-    p_publish_date  alias for $3;  -- default null
-    p_text          alias for $4;  -- default null
-    p_nls_language  alias for $5;  -- default null
-    p_title         alias for $6;  -- default null
-    p_mime_type     alias for $7;  -- default ''text/plain''
-    --
-    p_package_id    alias for $8;  -- default null,     
-    p_archive_date  alias for $9;  -- default null
-    p_approval_user alias for $10; -- default null
-    p_approval_date alias for $11; -- default null
-    p_approval_ip   alias for $12; -- default null,     
-    --
-    p_relation_tag  alias for $13; -- default null
-    --
-    -- REMOVED: p_item_subtype  alias for $14; -- default ''content_revision''
-    -- REMOVED: p_content_type  alias for $15; -- default ''news''
-    -- REMOVED: p_creation_date alias for $16; -- default current_timestamp
-    p_creation_ip   alias for $14; -- default null
-    p_creation_user alias for $15; -- default null
-    --
-    p_is_live_p     alias for $16; -- default ''f''
-    p_lead          alias for $17;
+-- old define_function_args ('news__new','item_id,locale,publish_date,text,nls_language,title,mime_type;text/plain,package_id,archive_date,approval_user,approval_date,approval_ip,relation_tag,creation_ip,creation_user,is_live_p;f,lead')
+-- new
+select define_function_args('news__new','item_id;null,locale;null,publish_date;null,text;null,nls_language;null,title;null,mime_type;text/plain,package_id;null,archive_date;null,approval_user;null,approval_date;null,approval_ip;null,relation_tag;null,creation_ip;null,creation_user;null,is_live_p;f,lead');
 
+
+
+
+--
+-- procedure news__new/17
+--
+CREATE OR REPLACE FUNCTION news__new(
+   p_item_id integer,           -- default null
+   p_locale varchar,            -- default null,
+   p_publish_date timestamptz,  -- default null
+   p_text text,                 -- default null
+   p_nls_language varchar,      -- default null
+   p_title varchar,             -- default null
+   p_mime_type varchar,         -- default 'text/plain'
+   p_package_id integer,        -- default null,
+   p_archive_date timestamptz,  -- default null
+   p_approval_user integer,     -- default null
+   p_approval_date timestamptz, -- default null
+   p_approval_ip varchar,       -- default null,
+   p_relation_tag varchar,      -- default null
+   p_creation_ip varchar,       -- default null
+   p_creation_user integer,     -- default null
+   p_is_live_p boolean,         -- default 'f'
+   p_lead varchar
+
+) RETURNS integer AS $$
+DECLARE
     v_news_id       integer;
     v_item_id       integer;
     v_id            integer;
     v_revision_id   integer;
     v_parent_id     integer;
     v_name          varchar;
     v_log_string    varchar;
-begin
-    select content_item__get_id(''news'',null,''f'')
+BEGIN
+    select content_item__get_id('news',null,'f')
     into   v_parent_id
     from   dual;
     --
     -- this will be used for 2xClick protection
     if p_item_id is null then
-        select nextval(''t_acs_object_id_seq'')
+        select nextval('t_acs_object_id_seq')
         into   v_id
         from   dual;
     else
         v_id := p_item_id;
     end if;
     --
-    v_name := ''news-'' || to_char(current_timestamp,''YYYYMMDD'') || ''-'' || v_id;
+    v_name := 'news-' || to_char(current_timestamp,'YYYYMMDD') || '-' || v_id;
     --
-    v_log_string := ''initial submission'';
+    v_log_string := 'initial submission';
     --
     v_item_id := content_item__new(
         v_name,               -- name
@@ -75,8 +73,8 @@
         p_creation_user,      -- creation_user
 	p_package_id,         -- context_id
         p_creation_ip,        -- creation_ip
-        ''content_item'',     -- item_subtype
-        ''news'',             -- content_type
+        'content_item',     -- item_subtype
+        'news',             -- content_type
         p_title,              -- title
 	null,                 -- description
         p_mime_type,          -- mime_type
@@ -85,7 +83,7 @@
 	null,                 -- data
         null,                 -- relation_tag
         p_is_live_p,          -- live_p
-	''text'',	      -- storage_type
+	'text',	      -- storage_type
         p_package_id          -- package_id
     );
 
@@ -120,33 +118,42 @@
          p_approval_date,
          p_approval_ip);
     -- make this revision live when immediately approved
-    if p_is_live_p = ''t'' then
+    if p_is_live_p = 't' then
         update
             cr_items
         set
             live_revision = v_revision_id,
-            publish_status = ''ready''
+            publish_status = 'ready'
         where
             item_id = v_item_id;
     end if;
     v_news_id := v_revision_id;
 
     return v_news_id;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
 
+
 -- deletes a news item along with all its revisions and possible attachements
-create or replace function news__delete (integer)
-returns integer as '
-declare
-    p_item_id alias for $1;
+
+
+-- added
+select define_function_args('news__delete','item_id');
+
+--
+-- procedure news__delete/1
+--
+CREATE OR REPLACE FUNCTION news__delete(
+   p_item_id integer
+) RETURNS integer AS $$
+DECLARE
     v_item_id cr_items.item_id%TYPE;
     v_cm RECORD;
-begin
+BEGIN
     v_item_id := p_item_id;
-    -- dbms_output.put_line(''Deleting associated comments...'');
+    -- dbms_output.put_line('Deleting associated comments...');
     -- delete acs_messages, images, comments to news item
 
     FOR v_cm IN
@@ -169,75 +176,108 @@
                       where  item_id = v_item_id);
     PERFORM content_item__delete(v_item_id);
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
+
 -- (re)-publish a news item out of the archive by nulling the archive_date
 -- this only applies to the currently active revision
-create or replace function news__make_permanent (integer)
-returns integer as '
-declare
-    p_item_id alias for $1;
-begin
+
+
+-- added
+select define_function_args('news__make_permanent','item_id');
+
+--
+-- procedure news__make_permanent/1
+--
+CREATE OR REPLACE FUNCTION news__make_permanent(
+   p_item_id integer
+) RETURNS integer AS $$
+DECLARE
+BEGIN
     update cr_news
     set    archive_date = null
     where  news_id = content_item__get_live_revision(p_item_id);
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
+
 -- archive a news item
 -- this only applies to the currently active revision
-create or replace function news__archive (integer,timestamptz)
-returns integer as '
-declare
-    p_item_id alias for $1;
-    p_archive_date alias for $2; -- default current_timestamp
-begin
+
+
+-- added
+select define_function_args('news__archive','item_id,archive_date;current_timestamp');
+
+--
+-- procedure news__archive/2
+--
+CREATE OR REPLACE FUNCTION news__archive(
+   p_item_id integer,
+   p_archive_date timestamptz -- default current_timestamp
+
+) RETURNS integer AS $$
+DECLARE
+BEGIN
     update cr_news  
     set    archive_date = p_archive_date
     where  news_id = content_item__get_live_revision(p_item_id);
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
+
 -- RAL: an overloaded version using current_timestamp for archive_date
-create or replace function news__archive (integer)
-returns integer as '
-declare
-    p_item_id alias for $1;
-    -- p_archive_date alias for $2; -- default current_timestamp
-begin
+
+
+--
+-- procedure news__archive/1
+--
+CREATE OR REPLACE FUNCTION news__archive(
+   p_item_id integer
+) RETURNS integer AS $$
+DECLARE
+BEGIN
     return news__archive (p_item_id, current_timestamp);
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
+
 -- approve/unapprove a specific revision
 -- approving a revision makes it also the active revision
-create or replace function news__set_approve(integer,varchar,timestamptz,
-       timestamptz,integer,timestamptz,varchar,boolean)
-returns integer as '
-declare
-    p_revision_id     alias for $1;
-    p_approve_p       alias for $2; -- default ''t''
-    p_publish_date    alias for $3; -- default null
-    p_archive_date    alias for $4; -- default null
-    p_approval_user   alias for $5; -- default null
-    p_approval_date   alias for $6; -- default current_timestamp
-    p_approval_ip     alias for $7; -- default null
-    p_live_revision_p alias for $8; -- default ''t''
+
+
+-- added
+select define_function_args('news__set_approve','revision_id,approve_p;t,publish_date;null,archive_date;null,approval_user;null,approval_date;current_timestamp,approval_ip;null,live_revision_p;t');
+
+--
+-- procedure news__set_approve/8
+--
+CREATE OR REPLACE FUNCTION news__set_approve(
+   p_revision_id integer,
+   p_approve_p varchar,         -- default 't'
+   p_publish_date timestamptz,  -- default null
+   p_archive_date timestamptz,  -- default null
+   p_approval_user integer,     -- default null
+   p_approval_date timestamptz, -- default current_timestamp
+   p_approval_ip varchar,       -- default null
+   p_live_revision_p boolean    -- default 't'
+
+) RETURNS integer AS $$
+DECLARE
     v_item_id         cr_items.item_id%TYPE;
-begin
+BEGIN
     select item_id into v_item_id
     from   cr_revisions 
     where  revision_id = p_revision_id;
     -- unapprove an revision (does not mean to knock out active revision)
-    if p_approve_p = ''f'' then
+    if p_approve_p = 'f' then
         update  cr_news 
         set     approval_date = null,
                 approval_user = null,
@@ -262,104 +302,123 @@
         where news_id     = p_revision_id;
         -- 
         -- cannot use content_item.set_live_revision because it sets publish_date to sysdate
-        if p_live_revision_p = ''t'' then
+        if p_live_revision_p = 't' then
             update  cr_items
             set     live_revision = p_revision_id,
-                    publish_status = ''ready''
+                    publish_status = 'ready'
             where   item_id = v_item_id;
         end if;
         --
     end if;
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
+
 -- the status function returns information on the puplish or archive status
 -- it does not make any checks on the order of publish_date and archive_date
-create or replace function news__status (timestamptz, timestamptz)
-returns varchar as '
-declare
-    p_publish_date alias for $1;
-    p_archive_date alias for $2;
-begin
+
+
+-- added
+select define_function_args('news__status','publish_date,archive_date');
+
+--
+-- procedure news__status/2
+--
+CREATE OR REPLACE FUNCTION news__status(
+   p_publish_date timestamptz,
+   p_archive_date timestamptz
+) RETURNS varchar AS $$
+DECLARE
+BEGIN
     if p_publish_date is not null then
         if p_publish_date > current_timestamp then
             -- Publishing in the future
             if p_archive_date is null then 
-                return ''going_live_no_archive'';
+                return 'going_live_no_archive';
             else 
-                return ''going_live_with_archive'';
+                return 'going_live_with_archive';
             end if;  
         else
             -- Published in the past
             if p_archive_date is null then
-                 return ''published_no_archive'';
+                 return 'published_no_archive';
             else
                 if p_archive_date > current_timestamp then
-                     return ''published_with_archive'';
+                     return 'published_with_archive';
                 else 
-                    return ''archived'';
+                    return 'archived';
                 end if;
             end if;
         end if;
     else
         -- publish_date null
-        return ''unapproved'';
+        return 'unapproved';
     end if;
-end;
-' language 'plpgsql';
+END;
 
-create or replace function news__name (integer)
-returns varchar as '
-declare
-    p_news_id alias for $1;
+$$ LANGUAGE plpgsql;
+
+
+
+-- added
+select define_function_args('news__name','news_id');
+
+--
+-- procedure news__name/1
+--
+CREATE OR REPLACE FUNCTION news__name(
+   p_news_id integer
+) RETURNS varchar AS $$
+DECLARE
     v_news_title cr_revisions.title%TYPE;
-begin
+BEGIN
     select title 
     into v_news_title
     from cr_revisions
     where revision_id = p_news_id;
 
     return v_news_title;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
+
 -- 
 -- API for Revision management
 -- 
-create or replace function news__revision_new (integer,timestamptz,text,varchar,text,
-       varchar,integer,timestamptz,integer,timestamptz,varchar,timestamptz,varchar,
-       integer,boolean, varchar)
-returns integer as '
-declare
-    p_item_id                alias for $1;
-    --
-    p_publish_date           alias for $2;  -- default null
-    p_text                   alias for $3;  -- default null
-    p_title                  alias for $4;
-    --
-    -- here goes the revision log
-    p_description            alias for $5;
-    --
-    p_mime_type              alias for $6;  -- default ''text/plain''
-    p_package_id             alias for $7;  -- default null
-    p_archive_date           alias for $8;  -- default null
-    p_approval_user          alias for $9;  -- default null
-    p_approval_date          alias for $10; -- default null
-    p_approval_ip            alias for $11; -- default null
-    --
-    p_creation_date          alias for $12; -- default current_timestamp
-    p_creation_ip            alias for $13; -- default null
-    p_creation_user          alias for $14; -- default null
-    --
-    p_make_active_revision_p alias for $15; -- default ''f''
-    p_lead                   alias for $16;
 
+
+-- added
+select define_function_args('news__revision_new','item_id,publish_date;null,text;null,title,description,mime_type;text/plain,package_id;null,archive_date;null,approval_user;null,approval_date;null,approval_ip;null,creation_date;current_timestamp,creation_ip;null,creation_user;null,make_active_revision_p;f,lead');
+
+--
+-- procedure news__revision_new/16
+--
+CREATE OR REPLACE FUNCTION news__revision_new(
+   p_item_id integer,
+   p_publish_date timestamptz,       -- default null
+   p_text text,                      -- default null
+   p_title varchar,
+   p_description text,
+   p_mime_type varchar,              -- default 'text/plain'
+   p_package_id integer,             -- default null
+   p_archive_date timestamptz,       -- default null
+   p_approval_user integer,          -- default null
+   p_approval_date timestamptz,      -- default null
+   p_approval_ip varchar,            -- default null
+   p_creation_date timestamptz,      -- default current_timestamp
+   p_creation_ip varchar,            -- default null
+   p_creation_user integer,          -- default null
+   p_make_active_revision_p boolean, -- default 'f'
+   p_lead varchar
+
+) RETURNS integer AS $$
+DECLARE
     v_revision_id    integer;
-begin
+BEGIN
     -- create revision
     v_revision_id := content_revision__new(
         p_title,         -- title
@@ -392,31 +451,40 @@
          p_approval_date,
          p_approval_ip);
     -- make active revision if indicated
-    if p_make_active_revision_p = ''t'' then
+    if p_make_active_revision_p = 't' then
         PERFORM news__revision_set_active(v_revision_id);
     end if;
     return v_revision_id;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
-create or replace function news__revision_set_active (integer)
-returns integer as '
-declare
-    p_revision_id alias for $1;
+
+
+
+-- added
+select define_function_args('news__revision_set_active','revision_id');
+
+--
+-- procedure news__revision_set_active/1
+--
+CREATE OR REPLACE FUNCTION news__revision_set_active(
+   p_revision_id integer
+) RETURNS integer AS $$
+DECLARE
     v_news_item_p boolean;
     v_item_id cr_items.item_id%TYPE;
     v_title acs_objects.title%TYPE;
-    -- could be used to check if really a ''news'' item
-begin
+    -- could be used to check if really a 'news' item
+BEGIN
 
     select item_id, title into v_item_id, v_title
     from cr_revisions
     where revision_id = p_revision_id;
 
     update cr_items
     set live_revision = p_revision_id,
-        publish_status = ''ready''
+        publish_status = 'ready'
     where item_id = v_item_id;
 
     -- We update the acs_objects title as well.
@@ -425,20 +493,30 @@
     where object_id = v_item_id and (title != v_title or title is null);
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
 
+
 -- Incomplete for want of blob_to_string() in postgres 16 july 2000
 
-create or replace function news__clone (integer, integer)
-returns integer as '
-declare
- p_old_package_id   alias for $1;   --default null,
- p_new_package_id   alias for $2;   --default null
+
+
+-- added
+select define_function_args('news__clone','old_package_id,new_package_id');
+
+--
+-- procedure news__clone/2
+--
+CREATE OR REPLACE FUNCTION news__clone(
+   p_old_package_id integer, --default null,
+   p_new_package_id integer  --default null
+
+) RETURNS integer AS $$
+DECLARE
  one_news		record;	 
-begin
+BEGIN
         for one_news in select
                             publish_date,
                             cr.content as text,
@@ -497,16 +575,25 @@
 
         end loop;
  return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
+
 -- currently not used, because we want to audit revisions
-create or replace function news__revision_delete (integer)
-returns integer as '
-declare
-    p_revision_id alias for $1;
-begin
+
+
+-- added
+select define_function_args('news__revision_delete','revision_id');
+
+--
+-- procedure news__revision_delete/1
+--
+CREATE OR REPLACE FUNCTION news__revision_delete(
+   p_revision_id integer
+) RETURNS integer AS $$
+DECLARE
+BEGIN
     -- delete from cr_news table
     delete from cr_news
     where  news_id = p_revision_id;
@@ -517,8 +604,9 @@
     );
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
 
 
+