Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.1d5-5.9.1d6.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.1d5-5.9.1d6.sql,v
diff -u -N
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.1d5-5.9.1d6.sql	10 Feb 2016 09:14:20 -0000	1.1.2.1
@@ -0,0 +1,352 @@
+--
+-- reduce number of versions of content_revision__new from 7 to 4 by using defaults
+-- commented differences
+-- marking on version of content_revision__new/7 as deprecated
+--
+-- let automatically generated functions call directly content_revision__new/13
+-- remove space from automatically generated functions
+-- updated automatically generated functions for all types of the content repository
+--
+
+-- content_revision__new/13
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,varchar,text,integer,integer,timestamptz,integer,varchar,integer,integer);
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,varchar,text,integer,integer,timestamptz,integer,varchar,integer);
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,varchar,text,integer,integer,timestamptz,integer,varchar);
+
+-- content_revision__new/12
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,varchar,integer,integer,integer,timestamptz,integer,varchar,integer);
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,varchar,integer,integer,integer,timestamptz,integer,varchar);
+
+-- content_revision__new/7
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,text,integer,integer);
+DROP FUNCTION IF EXISTS content_revision__new(varchar,varchar,timestamptz,varchar,text,integer);
+
+
+--
+-- procedure content_revision__new/13
+--
+-- We can't use for the last two argments "default null", since
+-- otherwise calls with provided package_id but no content_length
+-- would lead to a wrong interpretation of the package_id as
+-- content_length.
+--
+CREATE OR REPLACE FUNCTION content_revision__new(
+   new__title varchar,
+   new__description varchar,       -- default null
+   new__publish_date timestamptz,  -- default now()
+   new__mime_type varchar,         -- default 'text/plain'
+   new__nls_language varchar,      -- default null
+   new__text text,                 -- default ' '
+   new__item_id integer,
+   new__revision_id integer,       -- default null
+   new__creation_date timestamptz, -- default now()
+   new__creation_user integer,     -- default null
+   new__creation_ip varchar,       -- default null
+   new__content_length integer,    -- default null
+   new__package_id integer         -- default null
+
+) RETURNS integer AS $$
+DECLARE
+  v_revision_id               integer;       
+  v_package_id                acs_objects.package_id%TYPE;
+  v_content_type              acs_object_types.object_type%TYPE;
+  v_storage_type              cr_items.storage_type%TYPE;
+  v_length                    cr_revisions.content_length%TYPE;
+BEGIN
+
+  v_content_type := content_item__get_content_type(new__item_id);
+
+  if new__package_id is null then
+    v_package_id := acs_object__package_id(new__item_id);
+  else
+    v_package_id := new__package_id;
+  end if;
+
+  v_revision_id := acs_object__new(
+      new__revision_id,
+      v_content_type, 
+      new__creation_date, 
+      new__creation_user, 
+      new__creation_ip, 
+      new__item_id,
+      't',
+      new__title,
+      v_package_id
+  );
+
+  select storage_type into v_storage_type
+    from cr_items
+   where item_id = new__item_id;
+
+  if v_storage_type = 'text' then 
+     v_length := length(new__text);
+  else
+     v_length := coalesce(new__content_length,0);
+  end if;
+
+  -- text data is stored directly in cr_revisions using text datatype.
+
+  insert into cr_revisions (
+    revision_id, title, description, mime_type, publish_date,
+    nls_language, content, item_id, content_length
+  ) values (
+    v_revision_id, new__title, new__description,
+     new__mime_type, 
+    new__publish_date, new__nls_language, 
+    new__text, new__item_id, v_length
+  );
+
+  return v_revision_id;
+ 
+END;
+$$ LANGUAGE plpgsql;
+
+--
+-- procedure content_revision__new/11 content_revision__new/12
+--
+-- text/file version
+--
+CREATE OR REPLACE FUNCTION content_revision__new(
+   new__title varchar,
+   new__description varchar,       -- default null
+   new__publish_date timestamptz,  -- default now()
+   new__mime_type varchar,         -- default 'text/plain'
+   new__nls_language varchar,      -- default null
+   new__text text,                 -- default ' '
+   new__item_id integer,
+   new__revision_id integer,       -- default null
+   new__creation_date timestamptz, -- default now()
+   new__creation_user integer,     -- default null
+   new__creation_ip varchar,       -- default null
+   new__package_id integer default null
+
+) RETURNS integer AS $$   
+DECLARE
+BEGIN
+	raise NOTICE 'content_revision__new/12 is deprecated, call content_revision__new/13 instead';
+
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     new__nls_language,
+                                     new__text,
+                                     new__item_id,
+                                     new__revision_id,
+                                     new__creation_date,
+                                     new__creation_user,
+                                     new__creation_ip,
+                                     null,               -- content_length
+                                     new__package_id
+		);
+END
+$$ LANGUAGE plpgsql;
+
+
+--
+-- procedure content_revision__new/11 content_revision__new/12
+--
+-- lob version
+--
+CREATE OR REPLACE FUNCTION content_revision__new(
+   new__title varchar,
+   new__description varchar,       -- default null
+   new__publish_date timestamptz,  -- default now()
+   new__mime_type varchar,         -- default 'text/plain'
+   new__nls_language varchar,      -- default null
+   new__data integer,
+   new__item_id integer,
+   new__revision_id integer,       -- default null
+   new__creation_date timestamptz, -- default now()
+   new__creation_user integer,     -- default null
+   new__creation_ip varchar,       -- default null
+   new__package_id integer default null
+
+) RETURNS integer AS $$
+DECLARE
+  v_revision_id               integer;       
+  v_package_id                acs_objects.package_id%TYPE;
+  v_content_type              acs_object_types.object_type%TYPE;
+BEGIN
+
+  v_content_type := content_item__get_content_type(new__item_id);
+
+  if new__package_id is null then
+    v_package_id := acs_object__package_id(new__item_id);
+  else
+    v_package_id := new__package_id;
+  end if;
+
+  v_revision_id := acs_object__new(
+      new__revision_id,
+      v_content_type, 
+      new__creation_date, 
+      new__creation_user, 
+      new__creation_ip, 
+      new__item_id,
+      't',
+      new__title,
+      v_package_id
+  );
+
+  -- binary data is stored in cr_revisions using Dons lob hack.
+  -- This routine only inserts the lob id.  It would need to be followed by 
+  -- ns_pg blob_dml from within a tcl script to actually insert the lob data.
+
+  -- After the lob data is inserted, the content_length needs to be updated 
+  -- as well.
+  -- DanW, 2001-05-10.
+
+  insert into cr_revisions (
+    revision_id, title, description, mime_type, publish_date,
+    nls_language, lob, item_id, content_length
+  ) values (
+    v_revision_id, new__title, new__description,
+    new__mime_type, 
+    new__publish_date, new__nls_language, new__data, 
+    new__item_id, 0
+  );
+
+  return v_revision_id;
+
+END;
+$$ LANGUAGE plpgsql;
+
+
+--
+-- procedure content_revision__new/7
+--
+CREATE OR REPLACE FUNCTION content_revision__new(
+   new__title varchar,
+   new__description varchar,      -- default null
+   new__publish_date timestamptz, -- default now()
+   new__mime_type varchar,        -- default 'text/plain'
+   new__text text,                -- default ' '
+   new__item_id integer,
+   new__package_id integer default null
+
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+	raise NOTICE 'content_revision__new/7 is deprecated, call content_revision__new/13 instead';
+
+        return content_revision__new(new__title,
+                                     new__description,
+                                     new__publish_date,
+                                     new__mime_type,
+                                     null,
+                                     new__text,
+                                     new__item_id,
+                                     null,
+                                     now(),
+                                     null,
+                                     null,
+                                     null,
+                                     new__package_id
+               );
+
+END;
+$$ LANGUAGE plpgsql;
+
+--
+-- procedure content_type__refresh_trigger/1
+--
+CREATE OR REPLACE FUNCTION content_type__refresh_trigger(
+   refresh_trigger__content_type varchar
+) RETURNS integer AS $$
+DECLARE
+  rule_text                               text default '';
+  function_text                           text default '';
+  v_table_name                            acs_object_types.table_name%TYPE;
+  type_rec                                record;
+BEGIN
+
+  -- get the table name for the content type (determines view name)
+  raise NOTICE 'refresh trigger for % ', refresh_trigger__content_type;
+
+    -- Since we allow null table name use object type if table name is null so
+  -- we still can have a view.
+  select coalesce(table_name,object_type)
+    into v_table_name
+    from acs_object_types 
+   where object_type = refresh_trigger__content_type;
+
+  --=================== start building rule code =======================
+
+  function_text := function_text ||
+             'create or replace function ' || v_table_name || '_f (p_new '|| v_table_name || 'i)
+             returns void as ''
+             declare
+               v_revision_id integer;
+             begin
+
+               select content_revision__new(
+                                     p_new.title,
+                                     p_new.description,
+                                     p_new.publish_date,
+                                     p_new.mime_type,
+                                     p_new.nls_language,
+                                     case when p_new.text is null 
+                                              then p_new.data 
+                                              else p_new.text
+                                           end,
+                                     content_symlink__resolve(p_new.item_id),
+                                     p_new.revision_id,
+                                     now(),
+                                     p_new.creation_user, 
+                                     p_new.creation_ip,
+                                     null,                    -- content_length
+                                     p_new.object_package_id
+                ) into v_revision_id;
+                ';
+
+  -- add an insert statement for each subtype in the hierarchy for this type
+
+  for type_rec in select ot2.object_type, tree_level(ot2.tree_sortkey) as level
+                  from acs_object_types ot1, acs_object_types ot2
+                  where ot2.object_type <> 'acs_object'                       
+                    and ot2.object_type <> 'content_revision'
+                    and ot1.object_type = refresh_trigger__content_type
+                    and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
+                    and ot1.table_name is not null
+                  order by level asc
+  LOOP
+    function_text := function_text || content_type__trigger_insert_statement(type_rec.object_type) || ';
+    ';
+  end loop;
+
+  function_text := function_text || '
+   return;
+   end;'' language plpgsql; 
+   ';
+  -- end building the rule definition code
+
+  -- create the new function
+  execute function_text;
+
+  rule_text := 'create rule ' || v_table_name || '_r as on insert to ' ||
+               v_table_name || 'i do instead SELECT ' || v_table_name || '_f(new); ' ;
+  --================== done building rule code =======================
+
+  -- drop the old rule
+  if rule_exists(v_table_name || '_r', v_table_name || 'i') then 
+     execute 'drop rule ' || v_table_name || '_r ' || 'on ' || v_table_name || 'i';
+  end if;
+
+  -- create the new rule for inserts on the content type
+  execute rule_text;
+
+  return null; 
+
+END;
+$$ LANGUAGE plpgsql;
+
+-- upgrade types
+
+WITH RECURSIVE cr_types as (
+    select object_type from acs_object_types where object_type = 'content_revision'
+UNION ALL
+    select ot.object_type from acs_object_types ot,cr_types 
+    where ot.supertype = cr_types.object_type
+) select object_type, content_type__refresh_view(object_type) from cr_types;
+
