Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v
diff -u -N -r1.72 -r1.73
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql	3 May 2015 12:32:28 -0000	1.72
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql	7 Aug 2017 23:47:47 -0000	1.73
@@ -49,18 +49,11 @@
 END;
 $$ LANGUAGE plpgsql stable;
 
--- new 19 param version of content_item__new (now its 20 with package_id)
 
+select define_function_args('content_item__new','name,parent_id;null,item_id;null,locale;null,creation_date;now,creation_user;null,context_id;null,creation_ip;null,item_subtype;content_item,content_type;content_revision,title;null,description;null,mime_type;text/plain,nls_language;null,text;null,data;null,relation_tag;null,is_live;f,storage_type;null,package_id;null,with_child_rels;t');
 
--- old define_function_args('content_item__new','name,parent_id,item_id,locale,creation_date;now,creation_user,context_id,creation_ip,item_subtype;content_item,content_type;content_revision,title,description,mime_type;text/plain,nls_language,text,data,relation_tag,is_live;f,storage_type;null,package_id')
--- new
-select define_function_args('content_item__new','name,parent_id;null,item_id;null,locale;null,creation_date;now,creation_user;null,context_id;null,creation_ip;null,item_subtype;content_item,content_type;content_revision,title;null,description;null,mime_type;text/plain,nls_language;null,text;null,data;null,relation_tag;null,is_live;f,storage_type;null,package_id;null');
-
-
-
-
 --
--- procedure content_item__new/20
+-- procedure content_item__new/21 (accepts 19-21 args)
 --
 CREATE OR REPLACE FUNCTION content_item__new(
    new__name cr_items.name%TYPE,
@@ -82,7 +75,8 @@
    new__relation_tag cr_child_rels.relation_tag%TYPE,   -- default null
    new__is_live boolean,                                -- default 'f'
    new__storage_type cr_items.storage_type%TYPE,        -- default null
-   new__package_id acs_objects.package_id%TYPE          -- default null
+   new__package_id acs_objects.package_id%TYPE default null,
+   new__with_child_rels boolean DEFAULT 't'
 
 ) RETURNS integer AS $$
 DECLARE
@@ -176,7 +170,8 @@
   );
 
   -- if the parent is not a folder, insert into cr_child_rels
-  if v_parent_id != -4 and
+  if new__with_child_rels = 't' and
+    v_parent_id != -4 and
     content_folder__is_folder(v_parent_id) = 'f' then
 
     v_rel_id := acs_object__new(
@@ -200,36 +195,42 @@
   end if;
 
   if new__data is not null then
-
+  
+    -- call content_revision__new/13
+    
     v_revision_id := content_revision__new(
         v_title,
 	new__description,
-        now(),
+        now(),              -- publish_date
 	new__mime_type,
 	new__nls_language,
 	new__data,
         v_item_id,
-        null,
+        null,               -- revision_id
         new__creation_date, 
         new__creation_user, 
         new__creation_ip,
+	null,               -- content_length
         new__package_id
         );
 
   elsif new__text is not null or new__title is not null then
 
+    -- call content_revision__new/13
+
     v_revision_id := content_revision__new(
         v_title,
 	new__description,
-        now(),
+        now(),              -- publish_date
 	new__mime_type,
-        null,
+        new__nls_language,
 	new__text,
 	v_item_id,
-        null,
+        null,               -- revision_id
         new__creation_date, 
         new__creation_user, 
         new__creation_ip,
+	null,               -- content_length
         new__package_id
     );
 
@@ -245,56 +246,10 @@
 END;
 $$ LANGUAGE plpgsql;
 
-
-
 --
--- procedure content_item__new/19
+-- procedure content_item__new/17 (accepts 16-17 args)
 --
 CREATE OR REPLACE FUNCTION content_item__new(
-   new__name cr_items.name%TYPE,
-   new__parent_id cr_items.parent_id%TYPE,
-   new__item_id acs_objects.object_id%TYPE,
-   new__locale cr_items.locale%TYPE,
-   new__creation_date acs_objects.creation_date%TYPE,
-   new__creation_user acs_objects.creation_user%TYPE,
-   new__context_id acs_objects.context_id%TYPE,
-   new__creation_ip acs_objects.creation_ip%TYPE,
-   new__item_subtype acs_object_types.object_type%TYPE,
-   new__content_type acs_object_types.object_type%TYPE,
-   new__title cr_revisions.title%TYPE,
-   new__description cr_revisions.description%TYPE,
-   new__mime_type cr_revisions.mime_type%TYPE,
-   new__nls_language cr_revisions.nls_language%TYPE,
-   new__text varchar,
-   new__data cr_revisions.content%TYPE,
-   new__relation_tag cr_child_rels.relation_tag%TYPE,
-   new__is_live boolean,
-   new__storage_type cr_items.storage_type%TYPE
-) RETURNS integer AS $$
---
--- content_item__new/19 maybe obsolete, when we define proper defaults for /20
---
-DECLARE
-  v_item_id        cr_items.item_id%TYPE;
-BEGIN
-  v_item_id := content_item__new (new__name, new__parent_id, new__item_id, new__locale,
-               new__creation_date, new__creation_user, new__context_id, new__creation_ip,
-               new__item_subtype, new__content_type, new__title, new__description,
-               new__mime_type, new__nls_language, new__text, new__data, new__relation_tag,
-               new__is_live, new__storage_type, null);
-
-  return v_item_id;
-
-END;
-$$ LANGUAGE plpgsql;
-
---
-
-
---
--- procedure content_item__new/17
---
-CREATE OR REPLACE FUNCTION content_item__new(
    new__name varchar,
    new__parent_id integer,         -- default null
    new__item_id integer,           -- default null
@@ -310,163 +265,46 @@
    new__mime_type varchar,         -- default 'text/plain'
    new__nls_language varchar,      -- default null
    new__text varchar,              -- default null
-   new__storage_type varchar,      -- check in ('text','file')
-   new__package_id integer         -- default null
+   new__storage_type cr_items.storage_type%TYPE,
+   new__package_id integer default null
 
 ) RETURNS integer AS $$
 --
--- content_item__new/17 maybe obsolete, when we define proper defaults for /20
+-- content_item__new/17 is deprecated, one should call /20
 --
 DECLARE
-  new__relation_tag           varchar default null;
-  new__is_live                boolean default 'f';
-
-  v_parent_id                 cr_items.parent_id%TYPE;
-  v_parent_type               acs_objects.object_type%TYPE;
-  v_item_id                   cr_items.item_id%TYPE;
-  v_revision_id               cr_revisions.revision_id%TYPE;
-  v_title                     cr_revisions.title%TYPE;
-  v_rel_id                    acs_objects.object_id%TYPE;
-  v_rel_tag                   cr_child_rels.relation_tag%TYPE;
-  v_context_id                acs_objects.context_id%TYPE;
 BEGIN
+	raise NOTICE 'content_item__new/17 is deprecated, call content_item__new/21 instead';
 
-  -- place the item in the context of the pages folder if no
-  -- context specified 
-
-  if new__parent_id is null then
-    select c_root_folder_id from content_item_globals into v_parent_id;
-  else
-    v_parent_id := new__parent_id;
-  end if;
-
-  -- Determine context_id
-  if new__context_id is null then
-    v_context_id := v_parent_id;
-  else
-    v_context_id := new__context_id;
-  end if;
-
-  if v_parent_id = -4 or 
-    content_folder__is_folder(v_parent_id) = 't' then
-
-    if v_parent_id != -4 and 
-      content_folder__is_registered(
-        v_parent_id, new__content_type, 'f') = 'f' then
-
-      raise EXCEPTION '-20000: This items content type % is not registered to this folder %', new__content_type, v_parent_id;
-    end if;
-
-  else if v_parent_id != -4 then
-
-     select object_type into v_parent_type from acs_objects
-       where object_id = v_parent_id;
-
-     if NOT FOUND then 
-       raise EXCEPTION '-20000: Invalid parent ID % specified in content_item.new',  v_parent_id;
-     end if;
-
-     if content_item__is_subclass(v_parent_type, 'content_item') = 't' and
-	content_item__is_valid_child(v_parent_id, new__content_type) = 'f' then
-
-       raise EXCEPTION '-20000: This items content type % is not allowed in this container %', new__content_type, v_parent_id;
-     end if;
-
-  end if; end if;
-
-  -- Create the object
-
-  v_item_id := acs_object__new(
-      new__item_id,
-      new__item_subtype, 
-      new__creation_date, 
-      new__creation_user, 
-      new__creation_ip, 
-      v_context_id,
-      't',
-      coalesce(new__title,new__name),
-      new__package_id
-  );
-
-  insert into cr_items (
-    item_id, name, content_type, parent_id, storage_type
-  ) values (
-    v_item_id, new__name, new__content_type, v_parent_id, new__storage_type
-  );
-
-  -- if the parent is not a folder, insert into cr_child_rels
-  if v_parent_id != -4 and
-    content_folder__is_folder(v_parent_id) = 'f' and 
-    content_item__is_valid_child(v_parent_id, new__content_type) = 't' then
-
-    if new__relation_tag is null then
-      v_rel_tag := content_item__get_content_type(v_parent_id) 
-        || '-' || new__content_type;
-    else
-      v_rel_tag := new__relation_tag;
-    end if;
-
-    v_rel_id := acs_object__new(
-      null,
-      'cr_item_child_rel',
-      now(),
-      null,
-      null,
-      v_parent_id,
-      't',
-      v_rel_tag || ': ' || v_parent_id || ' - ' || v_item_id,
-      new__package_id
-    );
-
-    insert into cr_child_rels (
-      rel_id, parent_id, child_id, relation_tag, order_n
-    ) values (
-      v_rel_id, v_parent_id, v_item_id, v_rel_tag, v_item_id
-    );
-
-  end if;
-
-  -- use the name of the item if no title is supplied
-  if new__title is null then
-    v_title := new__name;
-  else
-    v_title := new__title;
-  end if;
-
-  if new__title is not null or 
-     new__text is not null then
-
-    v_revision_id := content_revision__new(
-	v_title,
-	new__description,
-        now(),
-	new__mime_type,
-        null,
-	new__text,
-	v_item_id,
-        null,
-        new__creation_date, 
-        new__creation_user, 
-        new__creation_ip,
-        new__package_id
-    );
-
-  end if;
-
-  -- make the revision live if is_live is true
-  if new__is_live = 't' then
-    PERFORM content_item__set_live_revision(v_revision_id);
-  end if;
-
-  return v_item_id;
- 
+        return content_item__new(new__name,
+                                 new__parent_id,
+                                 new__item_id,
+                                 new__locale,
+                                 new__creation_date,
+                                 new__creation_user,
+				 new__context_id,
+                                 new__creation_ip,
+                                 new__item_subtype,
+                                 new__content_type,
+                                 new__title,
+                                 new__description,
+                                 new__mime_type,
+                                 new__nls_language,
+                                 new__text,
+                                 null,  -- data
+				 null,  -- relation_tag
+				 'f',   -- is_live
+				 new__storage_type,
+                                 new__package_id,
+				 't'    -- with_child_rels
+               );
 END;
 $$ LANGUAGE plpgsql;
 
 
 
 --
--- procedure content_item__new/16
+-- procedure content_item__new/17 (accepts 15-17 args)
 --
 CREATE OR REPLACE FUNCTION content_item__new(
    new__name varchar,
@@ -483,55 +321,16 @@
    new__description varchar,       -- default null
    new__mime_type varchar,         -- default 'text/plain'
    new__nls_language varchar,      -- default null
-   new__text varchar,              -- default null
-   new__storage_type varchar       -- check in ('text''file')
-
-) RETURNS integer AS $$
---
--- content_item__new/16 maybe obsolete, when we define proper defaults for /20
---
-DECLARE
-  v_item_id                   cr_items.item_id%TYPE;
-BEGIN
-  v_item_id := content_item__new (new__name, new__parent_id, new__item_id, new__locale,
-               new__creation_date, new__creation_user, new__context_id, new__creation_ip,
-               new__item_subtype, new__content_type, new__title, new__description,
-               new__mime_type, new__nls_language, new__text, new__storage_type, null::integer);
-
-  return v_item_id;
- 
-END;
-$$ LANGUAGE plpgsql;
-
-
-
---
--- procedure content_item__new/16
---
-CREATE OR REPLACE FUNCTION content_item__new(
-   new__name varchar,
-   new__parent_id integer,         -- default null
-   new__item_id integer,           -- default null
-   new__locale varchar,            -- default null
-   new__creation_date timestamptz, -- default now()
-   new__creation_user integer,     -- default null
-   new__context_id integer,        -- default null
-   new__creation_ip varchar,       -- default null
-   new__item_subtype varchar,      -- default 'content_item'
-   new__content_type varchar,      -- default 'content_revision'
-   new__title varchar,             -- default null
-   new__description varchar,       -- default null
-   new__mime_type varchar,         -- default 'text/plain'
-   new__nls_language varchar,      -- default null
    new__data integer,              -- default null
-   new__package_id integer         -- default null
+   new__package_id integer default null,
+   new__with_child_rels boolean DEFAULT 't'
 
 ) RETURNS integer AS $$
 --
--- content_item__new/16 maybe obsolete, when we define proper defaults for /20
---
+-- This version passes "data" as integer (lob version), most other use
+-- "text" and "storage_type"
+-- 
 DECLARE
--- changed to integer for blob_id
   new__relation_tag           varchar default null;
   new__is_live                boolean default 'f';
 
@@ -616,8 +415,9 @@
   );
 
   -- if the parent is not a folder, insert into cr_child_rels
-  if v_parent_id != -4 and
-    content_folder__is_folder(v_parent_id) = 'f' and 
+  if new__with_child_rels = 't' and
+    v_parent_id != -4 and
+    content_folder__is_folder(v_parent_id) = 'f' and
     content_item__is_valid_child(v_parent_id, new__content_type) = 't' then
 
     if new__relation_tag is null or new__relation_tag = '' then
@@ -651,15 +451,17 @@
 
   if new__data is not null then
 
+    -- call content_revision__new/12 (data is integer)
+    
     v_revision_id := content_revision__new(
         v_title,
 	new__description,
-        now(),
+        now(),               -- publish_date
 	new__mime_type,
 	new__nls_language,
 	new__data,
         v_item_id,
-        null,
+        null,                -- revision_id
         new__creation_date, 
         new__creation_user, 
         new__creation_ip,
@@ -668,18 +470,21 @@
 
   elsif new__title is not null then
 
+    -- call content_revision__new/13 (data is null)
+
     v_revision_id := content_revision__new(
 	v_title,
 	new__description,
-        now(),
+        now(),               -- publish_date
 	new__mime_type,
-        null,
-	null,
+        new_nls_language,
+	null,                -- data/text
 	v_item_id,
-        null,
+        null,                -- revision_id
         new__creation_date, 
         new__creation_user, 
         new__creation_ip,
+	null,                -- content_length
         new__package_id
     );
 
@@ -698,79 +503,43 @@
 
 
 --
--- procedure content_item__new/15
+-- procedure content_item__new/6 (accepts 5-6 args)
 --
 CREATE OR REPLACE FUNCTION content_item__new(
    new__name varchar,
-   new__parent_id integer,         -- default null
-   new__item_id integer,           -- default null
-   new__locale varchar,            -- default null
-   new__creation_date timestamptz, -- default now()
-   new__creation_user integer,     -- default null
-   new__context_id integer,        -- default null
-   new__creation_ip varchar,       -- default null
-   new__item_subtype varchar,      -- default 'content_item'
-   new__content_type varchar,      -- default 'content_revision'
-   new__title varchar,             -- default null
-   new__description varchar,       -- default null
-   new__mime_type varchar,         -- default 'text/plain'
-   new__nls_language varchar,      -- default null
-   new__data integer               -- default null
-
-) RETURNS integer AS $$
---
--- content_item__new/15 maybe obsolete, when we define proper defaults for /20
---
-DECLARE
--- changed to integer for blob_id
-  v_item_id                   cr_items.item_id%TYPE;
-BEGIN
-  v_item_id := content_item__new (new__name, new__parent_id, new__item_id, new__locale,
-               new__creation_date, new__creation_user, new__context_id, new__creation_ip,
-               new__item_subtype, new__content_type, new__title, new__description,
-               new__mime_type, new__nls_language, new__data, null::integer);
-
-  return v_item_id;
- 
-END;
-$$ LANGUAGE plpgsql;
-
-
-
---
--- procedure content_item__new/6
---
-CREATE OR REPLACE FUNCTION content_item__new(
-   new__name varchar,
    new__parent_id integer, -- default null
    new__title varchar,     -- default null
    new__description text,  -- default null
    new__text text,         -- default null
-   new__package_id integer -- default null
-
+   new__package_id integer default null
 ) RETURNS integer AS $$
---
--- content_item__new/6 maybe obsolete, when we define proper defaults for /20
---
 DECLARE
 BEGIN
+	raise NOTICE 'content_item__new/5 is deprecated, call content_item__new/21 instead';
+
+	-- calls content_item__new/20
+
         return content_item__new(new__name,
                                  new__parent_id,
-                                 null,
-                                 null,
-                                 now(),
-                                 null,
-                                 null,
-                                 null,
-                                 'content_item',
-                                 'content_revision',   
+                                 null,               -- item_id
+                                 null,               -- locale
+                                 now(),              -- creation_date 
+                                 null,               -- creation_user
+                                 null,               -- context_id
+                                 null,               -- creation_ip
+                                 'content_item',     -- item_subtype
+                                 'content_revision', -- content_type
                                  new__title,
                                  new__description,
-                                 'text/plain',
-                                 null,
+                                 'text/plain',       -- mime_type
+                                 null,               -- nls_language
                                  new__text,
-                                 'text',
-                                 new__package_id
+                                 null,               -- data
+				 null,               -- relation_tag
+				 'f',                -- is_live				 
+                                 'text',             -- storage_type
+                                 new__package_id,
+				 't'                 -- with_child_rels
                );
 
 END;
@@ -779,39 +548,15 @@
 
 
 --
--- procedure content_item__new/5
+-- procedure content_item__new/2 content_item__new/3
 --
 CREATE OR REPLACE FUNCTION content_item__new(
    new__name varchar,
-   new__parent_id integer, -- default null
-   new__title varchar,     -- default null
-   new__description text,  -- default null
-   new__text text          -- default null
-
-) RETURNS integer AS $$
---
--- content_item__new/5 maybe obsolete, when we define proper defaults for /20
---
-DECLARE
-BEGIN
-        return content_item__new(new__name, new__parent_id, new__title, new__description,
-                                 new__text, null);
-
-END;
-$$ LANGUAGE plpgsql;
-
-
-
---
--- procedure content_item__new/3
---
-CREATE OR REPLACE FUNCTION content_item__new(
-   new__name varchar,
    new__parent_id integer,
-   new__package_id integer
+   new__package_id integer default null
 ) RETURNS integer AS $$
 --
--- content_item__new/3 maybe obsolete, when we define proper defaults for /20
+-- calls content_item__new/6
 --
 DECLARE
 BEGIN
@@ -821,29 +566,9 @@
 
 
 
---
--- procedure content_item__new/2
---
-CREATE OR REPLACE FUNCTION content_item__new(
-   new__name varchar,
-   new__parent_id integer
-) RETURNS integer AS $$
---
--- content_item__new/2 maybe obsolete, when we define proper defaults for /20
---
-DECLARE
-BEGIN
-        return content_item__new(new__name, new__parent_id, null, null, null, null);
-
-END;
-$$ LANGUAGE plpgsql;
-
 -- function new -- sets security_inherit_p to FALSE -DaveB
-
-
-
 --
--- procedure content_item__new/17
+-- procedure content_item__new/17 (accepts 16-17 args)
 --
 CREATE OR REPLACE FUNCTION content_item__new(
    new__item_id integer,            --default null
@@ -857,17 +582,21 @@
    new__is_live boolean,            -- default 'f'
    new__mime_type varchar,
    new__text text,                  -- default null
-   new__storage_type varchar,       -- check in ('text', 'file')
+   new__storage_type cr_items.storage_type%TYPE,
    new__security_inherit_p boolean, -- default 't'
    new__storage_area_key varchar,   -- default 'CR_FILES'
    new__item_subtype varchar,
    new__content_type varchar,
-   new__package_id integer          -- default null
+   new__package_id integer default null
 
 ) RETURNS integer AS $$
 --
--- content_item__new/17 maybe obsolete, when we define proper defaults for /20
---
+-- differs from other content_item__new/17 by
+--    this version has 1st arg item_id vs. 3rd arg (differs as well from /20)
+--    this version does not have a "locale" and "nls_language"
+--    this version has "is_live" (like /20)
+--    this version has "security_inherit_p"
+
 DECLARE
   new__description	      varchar default null;
   new__relation_tag           varchar default null;
@@ -989,18 +718,21 @@
   if new__title is not null or 
      new__text is not null then
 
+    -- call content_revision__new/13
+
     v_revision_id := content_revision__new(
 	v_title,
 	new__description,
-        now(),
+        now(),               -- publish_date
 	new__mime_type,
-        null,
+        null,                -- nls_language,
 	new__text,
 	v_item_id,
-        null,
+        null,                -- revision_id
         new__creation_date, 
         new__creation_user, 
         new__creation_ip,
+	null,                -- content_length
         new__package_id
     );
 
@@ -1017,49 +749,7 @@
 $$ LANGUAGE plpgsql;
 
 
-
---
--- procedure content_item__new/16
---
-CREATE OR REPLACE FUNCTION content_item__new(
-   new__item_id integer,            --default null
-   new__name varchar,
-   new__parent_id integer,          -- default null
-   new__title varchar,              -- default null
-   new__creation_date timestamptz,  -- default now()
-   new__creation_user integer,      -- default null
-   new__context_id integer,         -- default null
-   new__creation_ip varchar,        -- default null
-   new__is_live boolean,            -- default 'f'
-   new__mime_type varchar,
-   new__text text,                  -- default null
-   new__storage_type varchar,       -- check in ('text', 'file')
-   new__security_inherit_p boolean, -- default 't'
-   new__storage_area_key varchar,   -- default 'CR_FILES'
-   new__item_subtype varchar,
-   new__content_type varchar
-
-) RETURNS integer AS $$
---
--- content_item__new/16 maybe obsolete, when we define proper defaults for /20
---
-DECLARE
-  v_item_id                   cr_items.item_id%TYPE;
-BEGIN
-  v_item_id := content_item__new (new__item_id, new__name, new__parent_id, new__title,
-               new__creation_date, new__creation_user, new__context_id, new__creation_ip,
-               new__is_live, new__mime_type, new__text, new__storage_type,
-               new__security_inherit_p, new__storage_area_key, new__item_subtype,
-               new__content_type, null);
-
-  return v_item_id;
-
-END;
-$$ LANGUAGE plpgsql;
-
 select define_function_args('content_item__is_published','item_id');
-
-
 --
 -- procedure content_item__is_published/1
 --
@@ -1083,9 +773,8 @@
 END;
 $$ LANGUAGE plpgsql stable;
 
-select define_function_args('content_item__is_publishable','item_id');
 
-
+select define_function_args('content_item__is_publishable','item_id');
 --
 -- procedure content_item__is_publishable/1
 --
@@ -1110,22 +799,15 @@
 
   -- validate children
   -- make sure the # of children of each type fall between min_n and max_n
-  for v_child_type in select
-                        child_type, min_n, max_n
-                      from
-                        cr_type_children
-                      where
-                        parent_type = v_content_type 
-	                and (min_n is not null or max_n is not null)
+  for v_child_type in select child_type, min_n, max_n
+                      from   cr_type_children
+                      where  parent_type = v_content_type 
+	              and    (min_n is not null or max_n is not null)
   LOOP
-    select
-      count(rel_id) into v_child_count
-    from
-      cr_child_rels
-    where
-      parent_id = is_publishable__item_id
-    and
-      content_item__get_content_type(child_id) = v_child_type.child_type;
+    select count(item_id) into v_child_count
+    from   cr_items
+    where  parent_id = is_publishable__item_id
+    and    content_item__get_content_type(child_id) = v_child_type.child_type;
 
     -- make sure # of children is in range
     if v_child_type.min_n is not null 
@@ -1142,24 +824,16 @@
   -- validate relations
   -- make sure the # of ext links of each type fall between min_n and max_n
   -- only check if one of min_n max_n not null
-  for v_rel_type in select
-                      target_type, min_n, max_n
-                    from
-                      cr_type_relations
-                    where
-                      content_type = v_content_type
-		      and (max_n is not null or min_n is not null)
+  for v_rel_type in select target_type, min_n, max_n
+                    from   cr_type_relations
+                    where  content_type = v_content_type
+		    and    (max_n is not null or min_n is not null)
   LOOP
-    select
-      count(rel_id) into v_rel_count
-    from
-      cr_item_rels i, acs_objects o
-    where
-      i.related_object_id = o.object_id
-    and
-      i.item_id = is_publishable__item_id
-    and
-      coalesce(content_item__get_content_type(o.object_id),o.object_type) = v_rel_type.target_type;
+    select count(rel_id) into v_rel_count
+    from   cr_item_rels i, acs_objects o
+    where  i.related_object_id = o.object_id
+    and    i.item_id = is_publishable__item_id
+    and    coalesce(content_item__get_content_type(o.object_id),o.object_type) = v_rel_type.target_type;
       
     -- make sure # of object relations is in range
     if v_rel_type.min_n is not null 
@@ -1205,9 +879,8 @@
 END;
 $$ LANGUAGE plpgsql stable;
 
-select define_function_args('content_item__is_valid_child','item_id,content_type,relation_tag');
 
-
+select define_function_args('content_item__is_valid_child','item_id,content_type,relation_tag');
 --
 -- procedure content_item__is_valid_child/3
 --
@@ -1226,17 +899,11 @@
   v_is_valid_child := 'f';
 
   -- first check if content_type is a registered child_type
-  select
-    sum(max_n) into v_max_children
-  from
-    cr_type_children
-  where
-    parent_type = content_item__get_content_type(is_valid_child__item_id)
-  and
-    child_type = is_valid_child__content_type
-    and 
-      (is_valid_child__relation_tag is null 
-       or is_valid_child__relation_tag = relation_tag);
+  select sum(max_n) into v_max_children
+  from   cr_type_children
+  where  parent_type = content_item__get_content_type(is_valid_child__item_id)
+  and    child_type = is_valid_child__content_type
+  and    (is_valid_child__relation_tag is null or is_valid_child__relation_tag = relation_tag);
 
   if NOT FOUND then 
       return 'f';
@@ -1246,20 +913,24 @@
   if v_max_children is null then
     return 't';
   end if;
-  
-  -- next check if there are already max_n children of that content type
-  select
-    count(rel_id) into v_n_children
-  from
-    cr_child_rels
-  where
-    parent_id = is_valid_child__item_id
-  and
-    content_item__get_content_type(child_id) = is_valid_child__content_type
-  and 
-    (is_valid_child__relation_tag is null 
-     or is_valid_child__relation_tag = relation_tag);
 
+  --
+  -- Next check if there are already max_n children of that content type.
+  -- Use cr_child_rels only, when a non-null relation_tag is provided.
+  --
+  if is_valid_child__relation_tag is null then
+        select count(item_id) into v_n_children
+        from   cr_items
+        where  parent_id = is_valid_child__item_id
+        and    content_item__get_content_type(child_id) = is_valid_child__content_type;
+  else
+        select count(rel_id) into v_n_children
+        from   cr_child_rels
+        where  parent_id = is_valid_child__item_id
+        and    content_item__get_content_type(child_id) = is_valid_child__content_type
+        and    is_valid_child__relation_tag = relation_tag;
+  end if;
+  
   if NOT FOUND then 
      return 'f';
   end if;
@@ -1284,7 +955,7 @@
    is_valid_child__content_type varchar
 ) RETURNS boolean AS $$
 --
--- content_item__is_valid_child/2 maybe obsolete, when we define proper defaults for /3
+-- variant without relation_tag
 --
 DECLARE
   v_is_valid_child                       boolean;       
@@ -1295,14 +966,10 @@
   v_is_valid_child := 'f';
 
   -- first check if content_type is a registered child_type
-  select
-    sum(max_n) into v_max_children
-  from
-    cr_type_children
-  where
-    parent_type = content_item__get_content_type(is_valid_child__item_id)
-  and
-    child_type = is_valid_child__content_type;
+  select sum(max_n) into v_max_children
+  from   cr_type_children
+  where  parent_type = content_item__get_content_type(is_valid_child__item_id)
+  and    child_type = is_valid_child__content_type;
 
   if NOT FOUND then 
      return 'f';
@@ -1314,14 +981,10 @@
   end if;
 
   -- next check if there are already max_n children of that content type
-  select
-    count(rel_id) into v_n_children
-  from
-    cr_child_rels
-  where
-    parent_id = is_valid_child__item_id
-  and
-    content_item__get_content_type(child_id) = is_valid_child__content_type;
+  select count(item_id) into v_n_children
+  from   cr_items
+  where  parent_id = is_valid_child__item_id
+  and    content_item__get_content_type(child_id) = is_valid_child__content_type;
 
   if NOT FOUND then 
      return 'f';
@@ -1360,26 +1023,15 @@
    delete__item_id integer
 ) RETURNS integer AS $$
 DECLARE
-  v_symlink_val                  record;
-  v_revision_val                 record;
-  v_rel_val                      record;
+  v_revision_val record;
+  v_child_val record;
 BEGIN
   --
-  -- Delete all symlinks to this item
-  --
-  for v_symlink_val in select symlink_id
-                       from   cr_symlinks
-                       where  target_id = delete__item_id 
-  LOOP
-    PERFORM content_symlink__delete(v_symlink_val.symlink_id);
-  end loop;
-
-  --
   -- Delete all revisions of this item
   --
   -- The following loop could be dropped / replaced by a cascade
   -- operation, when proper foreign keys are used along the
-  -- inheritence path.
+  -- inheritance path.
   --
   for v_revision_val in select revision_id 
                         from   cr_revisions
@@ -1389,35 +1041,19 @@
   end loop;
 
   --
-  -- Delete all relations on this item
+  -- Delete all children of this item via a recursive call.
   --
-  for v_rel_val in select rel_id
-                   from   cr_item_rels
-                   where  item_id = delete__item_id
-                   or     related_object_id = delete__item_id 
-  LOOP
-    PERFORM acs_rel__delete(v_rel_val.rel_id);
-  end loop;  
-
-  for v_rel_val in select rel_id
-                   from   cr_child_rels
-                   where  child_id = delete__item_id 
-  LOOP
-    PERFORM acs_rel__delete(v_rel_val.rel_id);
-  end loop;  
-
-  for v_rel_val in select rel_id, child_id
-                   from   cr_child_rels
-                   where  parent_id = delete__item_id 
-  LOOP
-    PERFORM acs_rel__delete(v_rel_val.rel_id);
-    PERFORM content_item__delete(v_rel_val.child_id);
-  end loop;  
-
+  -- The following loop is just needed to delete the revisions of
+  -- child items. It could be removed, when proper foreign keys are
+  -- used along the inheritance path of cr_content_revisions (which is
+  -- not enforced and not always the case).
   --
-  -- Delete associated comments
-  --
-  PERFORM journal_entry__delete_for_object(delete__item_id);
+  for v_child_val in select item_id
+                      from   cr_items
+                      where  parent_id = delete__item_id 
+  LOOP     
+     PERFORM content_item__delete(v_child_val.item_id);
+  end loop; 
 
   --
   -- Finally, delete the acs_object of the item.
@@ -1491,13 +1127,7 @@
 $$ LANGUAGE plpgsql;
 
 
--- old define_function_args('content_item__get_id','item_path,root_folder_id,resolve_index;f')
--- new
 select define_function_args('content_item__get_id','item_path,root_folder_id;null,resolve_index;f');
-
-
-
-
 --
 -- procedure content_item__get_id/3
 --
@@ -1606,6 +1236,8 @@
   v_resolved_root_id               integer;       
   v_path                           text    default '';  
   v_rec                            record;
+  v_current_item_id                integer;
+  v_current_name                   text;
 BEGIN
 
   -- check that the item exists
@@ -1671,14 +1303,14 @@
     -- this is an absolute path so prepend a '/'
     -- loop over the absolute path
 
-    for v_rec in select i2.name, tree_level(i2.tree_sortkey) as tree_level
-                 from cr_items i1, cr_items i2
-                 where i2.parent_id <> 0
-                 and i1.item_id = get_path__item_id
-                 and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)
-                 order by tree_level
+    v_current_item_id := get_path__item_id;
+
+    while v_current_item_id <> 0
     LOOP
-      v_path := v_path || '/' || v_rec.name;
+      select parent_id, name into v_current_item_id, v_current_name from cr_items where item_id = v_current_item_id;
+      if FOUND then
+        v_path :=  '/' || v_current_name || v_path;
+      end if;
     end loop;
 
   end if;
@@ -1688,11 +1320,9 @@
 END;
 $$ LANGUAGE plpgsql;
 
+
 -- I hard code the content_item_globals.c_root_folder_id here
 select define_function_args('content_item__get_virtual_path','item_id,root_folder_id;-100');
-
-
-
 --
 -- procedure content_item__get_virtual_path/2
 --
@@ -1729,9 +1359,7 @@
 
 
 
--- added
 select define_function_args('content_item__write_to_file','item_id,root_path');
-
 --
 -- procedure content_item__write_to_file/2
 --
@@ -1762,10 +1390,8 @@
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__register_template','item_id,template_id,use_context');
 
-
-
+select define_function_args('content_item__register_template','item_id,template_id,use_context');
 --
 -- procedure content_item__register_template/3
 --
@@ -1803,12 +1429,7 @@
 
 
 
--- old define_function_args('content_item__unregister_template','item_id,template_id,use_context')
--- new
 select define_function_args('content_item__unregister_template','item_id,template_id;null,use_context;null');
-
-
-
 --
 -- procedure content_item__unregister_template/3
 --
@@ -1853,10 +1474,8 @@
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__get_template','item_id,use_context');
 
-
-
+select define_function_args('content_item__get_template','item_id,use_context');
 --
 -- procedure content_item__get_template/2
 --
@@ -1907,9 +1526,8 @@
 END;
 $$ LANGUAGE plpgsql stable strict;
 
-select define_function_args('content_item__get_content_type','item_id');
 
-
+select define_function_args('content_item__get_content_type','item_id');
 --
 -- procedure content_item__get_content_type/1
 --
@@ -1932,12 +1550,9 @@
 END;
 $$ LANGUAGE plpgsql stable strict;
 
-select define_function_args('content_item__get_live_revision','item_id');
 
-select define_function_args('content_item__get_live_revision','item_id');
 
-
-
+select define_function_args('content_item__get_live_revision','item_id');
 --
 -- procedure content_item__get_live_revision/1
 --
@@ -1960,87 +1575,53 @@
 END;
 $$ LANGUAGE plpgsql stable strict;
 
-select define_function_args('content_item__set_live_revision','revision_id,publish_status;ready');
 
-
+select define_function_args('content_item__set_live_revision','revision_id,publish_status;ready,publish_date;now(),is_latest;f');
 --
--- procedure content_item__set_live_revision/1
+-- procedure content_item__set_live_revision/1,2,3,4
 --
 CREATE OR REPLACE FUNCTION content_item__set_live_revision(
-   set_live_revision__revision_id integer
+   p__revision_id integer,
+   p__publish_status varchar default 'ready',
+   p__publish_date timestamptz default now(),
+   p__is_latest boolean default false
 ) RETURNS integer AS $$
---
--- content_item__set_live_revision/1 maybe obsolete, when we define proper defaults for /2
---
 DECLARE
-  set_live_revision__publish_status cr_items.publish_status%TYPE default 'ready';
 BEGIN
 
-  update
-    cr_items
+  if p__is_latest then
+    update cr_items
+      set
+            live_revision = p__revision_id,
+    	    publish_status = p__publish_status,
+            latest_revision = p__revision_id   
+      where
+	    item_id = (select item_id
+               from   cr_revisions
+               where  revision_id = p__revision_id);
+  else
+    update cr_items
+      set
+            live_revision = p__revision_id,
+    	    publish_status = p__publish_status
+      where
+	    item_id = (select item_id
+               from   cr_revisions
+               where  revision_id = p__revision_id);
+  end if;
+   
+  update cr_revisions
   set
-    live_revision = set_live_revision__revision_id,
-    publish_status = set_live_revision__publish_status
+    publish_date = p__publish_date
   where
-    item_id = (select
-                 item_id
-               from
-                 cr_revisions
-               where
-                 revision_id = set_live_revision__revision_id);
+    revision_id = p__revision_id;
 
-  update
-    cr_revisions
-  set
-    publish_date = now()
-  where
-    revision_id = set_live_revision__revision_id;
-
   return 0; 
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__set_live_revision','revision_id,publish_status;ready');
 
-
---
--- procedure content_item__set_live_revision/2
---
-CREATE OR REPLACE FUNCTION content_item__set_live_revision(
-   set_live_revision__revision_id integer,
-   set_live_revision__publish_status varchar -- default 'ready'
-
-) RETURNS integer AS $$
-DECLARE
-BEGIN
-
-  update
-    cr_items
-  set
-    live_revision = set_live_revision__revision_id,
-    publish_status = set_live_revision__publish_status
-  where
-    item_id = (select
-                 item_id
-               from
-                 cr_revisions
-               where
-                 revision_id = set_live_revision__revision_id);
-
-  update
-    cr_revisions
-  set
-    publish_date = now()
-  where
-    revision_id = set_live_revision__revision_id;
-
-  return 0; 
-END;
-$$ LANGUAGE plpgsql;
-
 select define_function_args('content_item__unset_live_revision','item_id');
-
-
 --
 -- procedure content_item__unset_live_revision/1
 --
@@ -2072,13 +1653,8 @@
 $$ LANGUAGE plpgsql;
 
 
--- old define_function_args('content_item__set_release_period','item_id,start_when,end_when')
--- new
-select define_function_args('content_item__set_release_period','item_id,start_when;null,end_when;null');
 
-
-
-
+select define_function_args('content_item__set_release_period','item_id,start_when;null,end_when;null');
 --
 -- procedure content_item__set_release_period/3
 --
@@ -2115,12 +1691,8 @@
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__get_revision_count','item_id');
 
 select define_function_args('content_item__get_revision_count','item_id');
-
-
-
 --
 -- procedure content_item__get_revision_count/1
 --
@@ -2143,9 +1715,8 @@
 END;
 $$ LANGUAGE plpgsql stable;
 
-select define_function_args('content_item__get_context','item_id');
 
-
+select define_function_args('content_item__get_context','item_id');
 --
 -- procedure content_item__get_context/1
 --
@@ -2182,37 +1753,14 @@
 -- 3) update the parent_id for the item
 
 
---
--- procedure content_item__move/2
---
-CREATE OR REPLACE FUNCTION content_item__move(
-   move__item_id integer,
-   move__target_folder_id integer
-) RETURNS integer AS $$
---
--- content_item__move/2 maybe obsolete, when we define proper defaults for /3
---
-DECLARE
-BEGIN
-  perform content_item__move(
-	move__item_id,
-	move__target_folder_id,
-	NULL
-	);
-return null;
-END;
-$$ LANGUAGE plpgsql;
-
 select define_function_args('content_item__move','item_id,target_folder_id,name');
-
-
 --
 -- procedure content_item__move/3
 --
 CREATE OR REPLACE FUNCTION content_item__move(
    move__item_id integer,
    move__target_folder_id integer,
-   move__name varchar
+   move__name varchar default null
 ) RETURNS integer AS $$
 DECLARE
 BEGIN
@@ -2253,9 +1801,8 @@
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__generic_move','item_id,target_item_id,name');
 
-
+select define_function_args('content_item__generic_move','item_id,target_item_id,name');
 --
 -- procedure content_item__generic_move/3
 --
@@ -2306,31 +1853,6 @@
 $$ LANGUAGE plpgsql;
 
 
-
-
---
--- procedure content_item__copy/4
---
-CREATE OR REPLACE FUNCTION content_item__copy(
-   item_id integer,
-   target_folder_id integer,
-   creation_user integer,
-   creation_ip varchar -- default null
-
-) RETURNS integer AS $$
---
--- content_item__copy/4 maybe obsolete, when we define proper defaults for /5
---
-DECLARE
-  copy_id                cr_items.item_id%TYPE;
-BEGIN
-
-  copy_id := content_item__copy2(item_id, target_folder_id, creation_user, creation_ip);
-
-  return 0; 
-END;
-$$ LANGUAGE plpgsql;
-
 -- copy a content item to a target folder
 -- 1) make sure we are not copying the item to an invalid location:
 --   that is, the destination folder exists, is a valid folder,
@@ -2341,10 +1863,7 @@
 -- 4) copy the latest revision from the original item to the new item (if any)
 
 
-
--- added
 select define_function_args('content_item__copy2','item_id,target_folder_id,creation_user,creation_ip;null');
-
 --
 -- procedure content_item__copy2/4
 --
@@ -2371,21 +1890,16 @@
 $$ LANGUAGE plpgsql;
 
 
--- old define_function_args('content_item__copy','item_id,target_folder_id,creation_user,creation_ip,name')
--- new
 select define_function_args('content_item__copy','item_id,target_folder_id,creation_user,creation_ip;null,name;null');
-
-
-
 --
--- procedure content_item__copy/5
+-- procedure content_item__copy/5 (accepts 3-5 args)
 --
 CREATE OR REPLACE FUNCTION content_item__copy(
    copy__item_id integer,
    copy__target_folder_id integer,
    copy__creation_user integer,
-   copy__creation_ip varchar, -- default null
-   copy__name varchar         -- default null
+   copy__creation_ip varchar default null,
+   copy__name varchar default null
 
 ) RETURNS integer AS $$
 DECLARE
@@ -2399,8 +1913,8 @@
   v_is_registered               boolean;
   v_old_revision_id             cr_revisions.revision_id%TYPE;
   v_new_revision_id             cr_revisions.revision_id%TYPE;
-  v_old_live_revision_id             cr_revisions.revision_id%TYPE;
-  v_new_live_revision_id             cr_revisions.revision_id%TYPE;
+  v_old_live_revision_id        cr_revisions.revision_id%TYPE;
+  v_new_live_revision_id        cr_revisions.revision_id%TYPE;
   v_storage_type                cr_items.storage_type%TYPE;
 BEGIN
 
@@ -2456,8 +1970,8 @@
     where
       item_id = copy__item_id;
 
--- copy to a different folder, or allow copy to the same folder
--- with a different name
+    -- copy to a different folder, or allow copy to the same folder
+    -- with a different name
 
     if copy__target_folder_id != v_current_folder_id  or ( v_name != copy__name and copy__name is not null ) then
       -- make sure the content type of the item is registered to the folder
@@ -2468,24 +1982,31 @@
       );
 
       if v_is_registered = 't' then
-        -- create the new content item
+        --
+        -- create the new content item via content_item__new/21
+	--
         v_item_id := content_item__new(
             coalesce (copy__name, v_name),
             copy__target_folder_id,
-            null,
+            null,               -- item_id
             v_locale,
-            now(),
+            now(),              -- creation_date
             copy__creation_user,
-            null,
+            null,               -- context_id
             copy__creation_ip,
             'content_item',            
             v_content_type,
-            null,
-            null,
-            'text/plain',
-            null,
-            null,
-            v_storage_type
+            null,               -- title
+            null,               -- description
+            'text/plain',       -- mime_type
+            null,               -- nls_language
+            null,               -- text
+            null,               -- data
+            null,               -- relation_tag
+            'f',                -- is_live	    
+            v_storage_type,
+	    null,               -- package_id
+	    't'                 -- with_child_rels
         );
 
 	select
@@ -2534,10 +2055,7 @@
 $$ LANGUAGE plpgsql;
 
 
-
 select define_function_args('content_item__get_latest_revision','item_id');
-
-
 --
 -- procedure content_item__get_latest_revision/1
 --
@@ -2569,9 +2087,8 @@
 END;
 $$ LANGUAGE plpgsql strict stable;
 
-select define_function_args('content_item__get_best_revision','item_id');
 
-
+select define_function_args('content_item__get_best_revision','item_id');
 --
 -- procedure content_item__get_best_revision/1
 --
@@ -2596,15 +2113,14 @@
 END;
 $$ LANGUAGE plpgsql stable strict;
 
-select define_function_args('content_item__get_title','item_id,is_live;f');
 
-
+select define_function_args('content_item__get_title','item_id,is_live;f');
 --
 -- procedure content_item__get_title/2
 --
 CREATE OR REPLACE FUNCTION content_item__get_title(
    get_title__item_id integer,
-   get_title__is_live boolean -- default 'f'
+   get_title__is_live boolean default 'f'
 
 ) RETURNS varchar AS $$
 DECLARE
@@ -2653,27 +2169,7 @@
 
 
 
-
---
--- procedure content_item__get_title/1
---
-CREATE OR REPLACE FUNCTION content_item__get_title(
-   get_title__item_id integer
-) RETURNS varchar AS $$
---
--- content_item__get_title/1 maybe obsolete, when we define proper defaults for /2
---
-DECLARE
-BEGIN
-  
-  return content_item__get_title(get_title__item_id, 'f');
-
-END;
-$$ LANGUAGE plpgsql stable strict;
-
 select define_function_args('content_item__get_publish_date','item_id,is_live;f');
-
-
 --
 -- procedure content_item__get_publish_date/2
 --
@@ -2712,9 +2208,8 @@
 END;
 $$ LANGUAGE plpgsql stable;
 
-select define_function_args('content_item__is_subclass','object_type,supertype');
 
-
+select define_function_args('content_item__is_subclass','object_type,supertype');
 --
 -- procedure content_item__is_subclass/2
 --
@@ -2739,12 +2234,8 @@
 $$ LANGUAGE plpgsql stable;
 
 
--- old define_function_args('content_item__relate','item_id,object_id,relation_tag;generic,order_n,relation_type;cr_item_rel')
--- new
-select define_function_args('content_item__relate','item_id,object_id,relation_tag;generic,order_n;null,relation_type;cr_item_rel');
 
-
-
+select define_function_args('content_item__relate','item_id,object_id,relation_tag;generic,order_n;null,relation_type;cr_item_rel');
 --
 -- procedure content_item__relate/5
 --
@@ -2855,12 +2346,9 @@
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__unrelate','rel_id');
 
-select define_function_args('content_item__unrelate','rel_id');
 
-
-
+select define_function_args('content_item__unrelate','rel_id');
 --
 -- procedure content_item__unrelate/1
 --
@@ -2880,12 +2368,9 @@
 END;
 $$ LANGUAGE plpgsql;
 
-select define_function_args('content_item__is_index_page','item_id,folder_id');
 
-select define_function_args('content_item__is_index_page','item_id,folder_id');
 
-
-
+select define_function_args('content_item__is_index_page','item_id,folder_id');
 --
 -- procedure content_item__is_index_page/2
 --
@@ -2904,10 +2389,8 @@
 END;
 $$ LANGUAGE plpgsql stable;
 
-select define_function_args('content_item__get_parent_folder','item_id');
 
-
-
+select define_function_args('content_item__get_parent_folder','item_id');
 --
 -- procedure content_item__get_parent_folder/1
 --
