-- Data model to support content repository of the ArsDigita
-- Community System

-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Author: Karl Goldstein (karlg@arsdigita.com)

-- $Id: content-item.sql,v 1.79 2024/09/11 06:15:47 gustafn Exp $

-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html

create or replace view content_item_globals as
select -100 as c_root_folder_id;


--
-- procedure content_item__get_root_folder/1
--
select define_function_args('content_item__get_root_folder','item_id;null');

CREATE OR REPLACE FUNCTION content_item__get_root_folder(
   get_root_folder__item_id integer -- default null

) RETURNS integer AS $$
DECLARE
  v_folder_id                             cr_folders.folder_id%TYPE;
BEGIN

  if get_root_folder__item_id is NULL or get_root_folder__item_id in (-4,-100,-200) then

    select c_root_folder_id from content_item_globals into v_folder_id;

  else

    select i2.item_id into v_folder_id
    from cr_items i1, cr_items i2
    where i2.parent_id = -4
    and i1.item_id = get_root_folder__item_id
    and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey);

    if NOT FOUND then
       raise EXCEPTION ' -20000: Could not find a root folder for item ID %. Either the item does not exist or its parent value is corrupted.', get_root_folder__item_id;
    end if;
  end if;

  return v_folder_id;

END;
$$ LANGUAGE plpgsql stable;


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');

--
-- procedure content_item__new/21 (accepts 19-21 args)
--
CREATE OR REPLACE FUNCTION content_item__new(
   new__name cr_items.name%TYPE,
   new__parent_id cr_items.parent_id%TYPE,              -- default null
   new__item_id acs_objects.object_id%TYPE,             -- default null
   new__locale cr_items.locale%TYPE,                    -- default null
   new__creation_date acs_objects.creation_date%TYPE,   -- default now -- default 'now'
   new__creation_user acs_objects.creation_user%TYPE,   -- default null
   new__context_id acs_objects.context_id%TYPE,         -- default null
   new__creation_ip acs_objects.creation_ip%TYPE,       -- default null
   new__item_subtype acs_object_types.object_type%TYPE, -- default 'content_item'
   new__content_type acs_object_types.object_type%TYPE, -- default 'content_revision'
   new__title cr_revisions.title%TYPE,                  -- default null
   new__description cr_revisions.description%TYPE,      -- default null
   new__mime_type cr_revisions.mime_type%TYPE,          -- default 'text/plain'
   new__nls_language cr_revisions.nls_language%TYPE,    -- default null
   new__text varchar,                                   -- default null
   new__data cr_revisions.content%TYPE,                 -- default null
   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__with_child_rels boolean DEFAULT 't'

) RETURNS integer AS $$
DECLARE
  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_title          cr_revisions.title%TYPE;
  v_revision_id    cr_revisions.revision_id%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;
  v_storage_type   cr_items.storage_type%TYPE;
BEGIN

  -- 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;

  -- use the name of the item if no title is supplied
  if new__title is null or new__title = '' then
    v_title := new__name;
  else
    v_title := new__title;
  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

     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;

     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, v_rel_tag) = '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',
      v_title,
      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 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(
      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;

  if new__data is not null then

    -- call content_revision__new/13

    v_revision_id := content_revision__new(
        v_title,
	new__description,
        now(),              -- publish_date
	new__mime_type,
	new__nls_language,
	new__data,
        v_item_id,
        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(),              -- publish_date
	new__mime_type,
        new__nls_language,
	new__text,
	v_item_id,
        null,               -- revision_id
        new__creation_date,
        new__creation_user,
        new__creation_ip,
	null,               -- content_length
        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;

END;
$$ LANGUAGE plpgsql;

--
-- procedure content_item__new/17 (accepts 16-17 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__text varchar,              -- default null
   new__storage_type cr_items.storage_type%TYPE,
   new__package_id integer default null

) RETURNS integer AS $$
--
-- content_item__new/17 is deprecated, one should call /20
--
DECLARE
BEGIN
	raise NOTICE 'content_item__new/17 is deprecated, call content_item__new/21 instead';

        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/17 (accepts 15-17 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
   new__package_id integer default null,
   new__with_child_rels boolean DEFAULT 't'

) RETURNS integer AS $$
--
-- This version passes "data" as integer (lob version), most other use
-- "text" and "storage_type"
--
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

  -- 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;

  -- use the name of the item if no title is supplied
  if new__title is null or new__title = '' then
    v_title := new__name;
  else
    v_title := new__title;
  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',
      v_title,
      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, 'lob'
  );

  -- if the parent is not a folder, insert into cr_child_rels
  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
      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;

  -- create the revision if data or title is not null

  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(),               -- publish_date
	new__mime_type,
	new__nls_language,
	new__data,
        v_item_id,
        null,                -- revision_id
        new__creation_date,
        new__creation_user,
        new__creation_ip,
        new__package_id
        );

  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(),               -- publish_date
	new__mime_type,
        new__nls_language,
	null,                -- data/text
	v_item_id,
        null,                -- revision_id
        new__creation_date,
        new__creation_user,
        new__creation_ip,
	null,                -- content_length
        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;

END;
$$ LANGUAGE plpgsql;



--
-- 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__title varchar,     -- default null
   new__description text,  -- default null
   new__text text,         -- default null
   new__package_id integer default null
) RETURNS integer AS $$
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,               -- 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',       -- mime_type
                                 null,               -- nls_language
                                 new__text,
                                 null,               -- data
				 null,               -- relation_tag
				 'f',                -- is_live
                                 'text',             -- storage_type
                                 new__package_id,
				 't'                 -- with_child_rels
               );

END;
$$ LANGUAGE plpgsql;



--
-- procedure content_item__new/2 content_item__new/3
--
CREATE OR REPLACE FUNCTION content_item__new(
   new__name varchar,
   new__parent_id integer,
   new__package_id integer default null
) RETURNS integer AS $$
--
-- calls content_item__new/6
--
DECLARE
BEGIN
        return content_item__new(new__name, new__parent_id, null, null, null, new__package_id);
END;
$$ LANGUAGE plpgsql;



-- function new -- sets security_inherit_p to FALSE -DaveB
--
-- procedure content_item__new/17 (accepts 16-17 args)
--
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 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

) RETURNS integer AS $$
--
-- 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;
  new__nls_language	      varchar default null;
  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

  -- 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;

  -- use the name of the item if no title is supplied
  if new__title is null or new__title = '' then
    v_title := new__name;
  else
    v_title := new__title;
  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,
      new__security_inherit_p,
      v_title,
      new__package_id
  );

  insert into cr_items (
    item_id, name, content_type, parent_id, storage_type, storage_area_key
  ) values (
    v_item_id, new__name, new__content_type, v_parent_id, new__storage_type,
    new__storage_area_key
  );

  -- 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',
      new__creation_date,
      null,
      null,
      v_parent_id,
      'f',
      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;

  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(),               -- publish_date
	new__mime_type,
        null,                -- nls_language,
	new__text,
	v_item_id,
        null,                -- revision_id
        new__creation_date,
        new__creation_user,
        new__creation_ip,
	null,                -- content_length
        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;

END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__is_published','item_id');
--
-- procedure content_item__is_published/1
--
CREATE OR REPLACE FUNCTION content_item__is_published(
   is_published__item_id integer
) RETURNS boolean AS $$
DECLARE
BEGIN

  return
    count(*) > 0
  from
    cr_items
  where
    live_revision is not null
  and
    publish_status = 'live'
  and
    item_id = is_published__item_id;

END;
$$ LANGUAGE plpgsql stable;


select define_function_args('content_item__is_publishable','item_id');
--
-- procedure content_item__is_publishable/1
--
CREATE OR REPLACE FUNCTION content_item__is_publishable(
   is_publishable__item_id integer
) RETURNS boolean AS $$
DECLARE
  v_child_count                          integer;
  v_rel_count                            integer;
  v_content_type			 varchar;
  v_template_id                          cr_templates.template_id%TYPE;
  v_child_type                           record;
  v_rel_type                             record;
  -- v_pub_wf                               record;
BEGIN
  -- check valid item_id
  select content_item__get_content_type(is_publishable__item_id) into v_content_type;

  if v_content_type is null then
	raise exception 'content_item__is_publishable item_id % invalid',is_publishable__item_id;
  end if;

  -- 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)
  LOOP
    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
      and v_child_count < v_child_type.min_n then
      return 'f';
    end if;
    if v_child_type.max_n is not null
      and v_child_count > v_child_type.max_n then
      return 'f';
    end if;

  end LOOP;

  -- 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)
  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;

    -- make sure # of object relations is in range
    if v_rel_type.min_n is not null
      and v_rel_count < v_rel_type.min_n then
      return 'f';
    end if;
    if v_rel_type.max_n is not null
      and v_rel_count > v_rel_type.max_n then
      return 'f';
    end if;
  end loop;

  -- validate publishing workflows
  -- make sure any 'publishing_wf' associated with this item are finished
  -- KG: logic is wrong here.  Only the latest workflow matters, and even
  -- that is a little problematic because more than one workflow may be
  -- open on an item.  In addition, this should be moved to CMS.

  -- Removed this as having workflow stuff in the CR is just plain wrong.
  -- DanW, Aug 25th, 2001.

  --   for v_pub_wf in  select
  --                      case_id, state
  --                    from
  --                      wf_cases
  --                    where
  --                      workflow_key = 'publishing_wf'
  --                    and
  --                      object_id = is_publishable__item_id
  --
  --   LOOP
  --     if v_pub_wf.state != 'finished' then
  --        return 'f';
  --     end if;
  --   end loop;

  -- if NOT FOUND then
  --   return 'f';
  -- end if;

  return 't';

END;
$$ LANGUAGE plpgsql stable;


select define_function_args('content_item__is_valid_child','item_id,content_type,relation_tag');
--
-- procedure content_item__is_valid_child/3
--
CREATE OR REPLACE FUNCTION content_item__is_valid_child(
   is_valid_child__item_id integer,
   is_valid_child__content_type varchar,
   is_valid_child__relation_tag varchar
) RETURNS boolean AS $$
DECLARE
  v_is_valid_child                       boolean;
  v_max_children                         cr_type_children.max_n%TYPE;
  v_n_children                           integer;
  v_null_exists				 boolean;
BEGIN

  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);

  if NOT FOUND then
      return 'f';
  end if;

  -- if the max is null then infinite number is allowed
  if v_max_children is null then
    return 't';
  end if;

  --
  -- 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;

  if v_n_children < v_max_children then
    v_is_valid_child := 't';
  end if;

  return v_is_valid_child;

END;
$$ LANGUAGE plpgsql stable;




--
-- procedure content_item__is_valid_child/2
--
CREATE OR REPLACE FUNCTION content_item__is_valid_child(
   is_valid_child__item_id integer,
   is_valid_child__content_type varchar
) RETURNS boolean AS $$
--
-- variant without relation_tag
--
DECLARE
  v_is_valid_child                       boolean;
  v_max_children                         cr_type_children.max_n%TYPE;
  v_n_children                           integer;
BEGIN

  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;

  if NOT FOUND then
     return 'f';
  end if;

  -- if the max is null then infinite number is allowed
  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(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';
  end if;

  if v_n_children < v_max_children then
    v_is_valid_child := 't';
  end if;

  return v_is_valid_child;

END;
$$ LANGUAGE plpgsql stable;


--
-- Delete a content item
--
-- Technically, the following steps are necessary, some of these are
-- achieved via cascading operations:
--
-- 1) delete all associated workflows
-- 2) delete all symlinks associated with this object
-- 3) delete any revisions for this item
-- 4) unregister template relations
-- 5) delete all permissions associated with this item
-- 6) delete keyword associations
-- 7) delete all associated comments

select define_function_args('content_item__del','item_id');

--
-- procedure content_item__del/1
--
CREATE OR REPLACE FUNCTION content_item__del(
   delete__item_id integer
) RETURNS integer AS $$
BEGIN

  -- Also child relationships must be deleted. On delete cascade would
  -- not help here, as related acs_object would stay.
  PERFORM acs_object__delete(object_id)
    from acs_objects where object_id in
    (select rel_id from cr_child_rels where
         child_id  = delete__item_id or
         parent_id = delete__item_id);

  --
  -- Delete all revisions of this item
  --
  -- On delete cascade should work for us, but not in case of
  -- relationships. Therefore, we call acs_object__delete explicitly
  -- on the revisions. Is is also safer in general, as referential
  -- integrity might not have been enforced every time.
  --
  PERFORM acs_object__delete(revision_id)
    from cr_revisions where item_id = delete__item_id;

  --
  -- Delete all children of this item via a recursive call.
  --
  -- On delete cascade should work for us, but not in case of
  -- relationships. Therefore, we call acs_object__delete explicitly
  -- on the revisions. Is is also safer in general, as referential
  -- integrity might not have been enforced every time.
  --
  PERFORM content_item__delete(item_id)
    from cr_items where parent_id = delete__item_id;

  --
  -- Finally, delete the acs_object of the item.
  --
  PERFORM acs_object__delete(delete__item_id);

  return 0;
END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__delete','item_id');
--
-- procedure content_item__delete/1
--
CREATE OR REPLACE FUNCTION content_item__delete(
   delete__item_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
   PERFORM content_item__del (delete__item_id);
   return 0;
END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__edit_name','item_id,name');
--
-- procedure content_item__edit_name/2
--
CREATE OR REPLACE FUNCTION content_item__edit_name(
   edit_name__item_id integer,
   edit_name__name varchar
) RETURNS integer AS $$
DECLARE
  exists_id                      integer;
BEGIN
  select
    item_id
  into
    exists_id
  from
    cr_items
  where
    name = edit_name__name
  and
    parent_id = (select
	           parent_id
		 from
		   cr_items
		 where
		   item_id = edit_name__item_id);
  if NOT FOUND then
    update cr_items
      set name = edit_name__name
      where item_id = edit_name__item_id;

    update acs_objects
      set title = edit_name__name
      where object_id = edit_name__item_id;
  else
    if exists_id != edit_name__item_id then
      raise EXCEPTION '-20000: An item with the name % already exists in this directory.', edit_name__name;
    end if;
  end if;

  return 0;
END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__get_id','item_path,root_folder_id;null,resolve_index;f');
--
-- procedure content_item__get_id/3
--
CREATE OR REPLACE FUNCTION content_item__get_id(
   get_id__item_path varchar,
   get_id__root_folder_id integer, -- default null
   get_id__resolve_index boolean   -- default 'f'

) RETURNS integer AS $$
DECLARE
  v_item_path                    varchar;
  v_root_folder_id               cr_items.item_id%TYPE;
  get_id__parent_id              integer;
  child_id                       integer;
  start_pos                      integer default 1;
  end_pos                        integer;
  counter                        integer default 1;
  item_name                      varchar;
BEGIN

  if get_id__root_folder_id is null then
    select c_root_folder_id from content_item_globals into v_root_folder_id;
  else
    v_root_folder_id := get_id__root_folder_id;
  end if;

  -- If the request path is the root, then just return the root folder
  if get_id__item_path = '/' then
    return v_root_folder_id;
  end if;

  -- Remove leading, trailing spaces, leading slashes
  v_item_path := rtrim(ltrim(trim(get_id__item_path), '/'), '/');

  get_id__parent_id := v_root_folder_id;

  -- if parent_id is a symlink, resolve it
  get_id__parent_id := content_symlink__resolve(get_id__parent_id);

  LOOP

    end_pos := instr(v_item_path, '/', 1, counter);

    if end_pos = 0 then
      item_name := substr(v_item_path, start_pos);
    else
      item_name := substr(v_item_path, start_pos, end_pos - start_pos);
      counter := counter + 1;
    end if;

    select
      item_id into child_id
    from
      cr_items
    where
      parent_id = get_id__parent_id
    and
      name = item_name;

    if NOT FOUND then
       return null;
    end if;

    exit when end_pos = 0;

    get_id__parent_id := child_id;

    -- if parent_id is a symlink, resolve it
    get_id__parent_id := content_symlink__resolve(get_id__parent_id);

    start_pos := end_pos + 1;

  end loop;

  if get_id__resolve_index = 't' then

    -- if the item is a folder and has an index page, then return

    if content_folder__is_folder(child_id ) = 't' and
      content_folder__get_index_page(child_id) is not null then

      child_id := content_folder__get_index_page(child_id);
    end if;

  end if;

  return child_id;

END;
$$ LANGUAGE plpgsql stable;


--
-- procedure content_item__get_path/2
--

select define_function_args('content_item__get_path','item_id,root_folder_id;null');

CREATE OR REPLACE FUNCTION content_item__get_path(
   get_path__item_id integer,
   get_path__root_folder_id integer -- default null

) RETURNS varchar AS $$
DECLARE
  v_count                          integer;
  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
  select count(*) into v_count from cr_items where item_id = get_path__item_id;

  if v_count = 0 then
    raise EXCEPTION '-20000: Invalid item ID: %', get_path__item_id;
  end if;

  -- begin walking down the path to the item (from the repository root)

  -- if the root folder is not null then prepare for a relative path

  if get_path__root_folder_id is not null then

    -- if root_folder_id is a symlink, resolve it (child items will point
    -- to the actual folder, not the symlink)

    v_resolved_root_id := content_symlink__resolve(get_path__root_folder_id);

    -- check to see if the item is under or out side the root_id
    PERFORM 1 from cr_items i,
        (select tree_sortkey from cr_items where item_id = v_resolved_root_id) a
    where tree_ancestor_p(a.tree_sortkey, i.tree_sortkey) and i.item_id = get_path__item_id;

    if NOT FOUND then
        -- if not found then we need to go up the folder and append ../ until we have common ancestor

        for v_rec in select i1.name, i1.parent_id, tree_level(i1.tree_sortkey) as tree_level
                 from cr_items i1, (select tree_ancestor_keys(tree_sortkey) as tree_sortkey from cr_items where item_id = v_resolved_root_id) i2,
                 (select tree_sortkey from cr_items where item_id = get_path__item_id) i3
                 where
                 i1.parent_id <> 0
                 and i2.tree_sortkey = i1.tree_sortkey
                 and not tree_ancestor_p(i2.tree_sortkey, i3.tree_sortkey)
                 order by tree_level desc
        LOOP
            v_path := v_path || '../';
        end loop;
        -- lets now assign the new root_id to be the last parent_id on the loop
        v_resolved_root_id := v_rec.parent_id;

    end if;

    -- go downwards the tree and append the name and /
    for v_rec in select i1.name, i1.item_id, tree_level(i1.tree_sortkey) as tree_level
             from cr_items i1, (select tree_sortkey from cr_items where item_id = v_resolved_root_id) i2,
            (select tree_ancestor_keys(tree_sortkey) as tree_sortkey from cr_items where item_id = get_path__item_id) i3
             where
             i1.tree_sortkey = i3.tree_sortkey
             and i1.tree_sortkey > i2.tree_sortkey
             order by tree_level
    LOOP
        v_path := v_path || v_rec.name;
        if v_rec.item_id <> get_path__item_id then
            -- put a / if we are still going down
            v_path := v_path || '/';
        end if;
    end loop;

  else

    -- this is an absolute path so prepend a '/'
    -- loop over the absolute path

    v_current_item_id := get_path__item_id;

    while v_current_item_id <> 0
    LOOP
      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;

  return v_path;

END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__get_virtual_path','item_id,root_folder_id;null');
--
-- procedure content_item__get_virtual_path/2
--
CREATE OR REPLACE FUNCTION content_item__get_virtual_path(
   get_virtual_path__item_id integer,
   get_virtual_path__root_folder_id integer
) RETURNS varchar AS $$
DECLARE
  v_path                                  varchar;
  v_item_id                               cr_items.item_id%TYPE;
  v_is_folder                             boolean;
  v_index                                 cr_items.item_id%TYPE;
BEGIN
  -- first resolve the item
  v_item_id := content_symlink__resolve(get_virtual_path__item_id);

  v_is_folder := content_folder__is_folder(v_item_id);
  v_index := content_folder__get_index_page(v_item_id);

  -- if the folder has an index page
  if v_is_folder = 't' and v_index is not null then
    v_path := content_item__get_path(content_symlink__resolve(v_index), get_virtual_path__root_folder_id);
  else
    v_path := content_item__get_path(v_item_id, get_virtual_path__root_folder_id);
  end if;

  return v_path;

END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__write_to_file','item_id,root_path');
--
-- procedure content_item__write_to_file/2
--
CREATE OR REPLACE FUNCTION content_item__write_to_file(
   item_id integer,
   root_path varchar
) RETURNS integer AS $$
DECLARE
  -- blob_loc               cr_revisions.content%TYPE;
  -- v_revision             cr_items.live_revision%TYPE;
BEGIN

  -- FIXME:
  raise NOTICE 'not implemented for PostgreSQL';
/*
  v_revision := content_item__get_live_revision(item_id);

  select content into blob_loc from cr_revisions
    where revision_id = v_revision;

  if NOT FOUND then
    raise EXCEPTION '-20000: No live revision for content item % in content_item.write_to_file.', item_id;
  end if;

  PERFORM blob_to_file(root_path || content_item__get_path(item_id), blob_loc);
*/
  return 0;
END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__register_template','item_id,template_id,use_context');
--
-- procedure content_item__register_template/3
--
CREATE OR REPLACE FUNCTION content_item__register_template(
   register_template__item_id integer,
   register_template__template_id integer,
   register_template__use_context varchar
) RETURNS integer AS $$
DECLARE

BEGIN

 -- register template if it is not already registered
  insert into cr_item_template_map
  select
    register_template__item_id as item_id,
    register_template__template_id as template_id,
    register_template__use_context as use_context
  from
    dual
  where
    not exists ( select 1
                 from
                   cr_item_template_map
                 where
                   item_id = register_template__item_id
                 and
                   template_id = register_template__template_id
                 and
                   use_context = register_template__use_context );

  return 0;
END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__unregister_template','item_id,template_id;null,use_context;null');
--
-- procedure content_item__unregister_template/3
--
CREATE OR REPLACE FUNCTION content_item__unregister_template(
   unregister_template__item_id integer,
   unregister_template__template_id integer, -- default null
   unregister_template__use_context varchar  -- default null

) RETURNS integer AS $$
DECLARE

BEGIN

  if unregister_template__use_context is null and
     unregister_template__template_id is null then

    delete from cr_item_template_map
      where item_id = unregister_template__item_id;

  else if unregister_template__use_context is null then

    delete from cr_item_template_map
      where template_id = unregister_template__template_id
      and item_id = unregister_template__item_id;

  else if unregister_template__template_id is null then

    delete from cr_item_template_map
      where item_id = unregister_template__item_id
      and use_context = unregister_template__use_context;

  else

    delete from cr_item_template_map
      where template_id = unregister_template__template_id
      and item_id = unregister_template__item_id
      and use_context = unregister_template__use_context;

  end if; end if; end if;

  return 0;
END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__get_template','item_id,use_context');
--
-- procedure content_item__get_template/2
--
CREATE OR REPLACE FUNCTION content_item__get_template(
   get_template__item_id integer,
   get_template__use_context varchar
) RETURNS integer AS $$
DECLARE
  v_template_id                        cr_templates.template_id%TYPE;
  v_content_type                       cr_items.content_type%TYPE;
BEGIN

  -- look for a template assigned specifically to this item
  select
    template_id
  into
     v_template_id
  from
    cr_item_template_map
  where
    item_id = get_template__item_id
  and
    use_context = get_template__use_context;
  -- otherwise get the default for the content type
  if NOT FOUND then
    select
      m.template_id
    into
      v_template_id
    from
      cr_items i, cr_type_template_map m
    where
      i.item_id = get_template__item_id
    and
      i.content_type = m.content_type
    and
      m.use_context = get_template__use_context
    and
      m.is_default = 't';

    if NOT FOUND then
       return null;
    end if;
  end if;

  return v_template_id;

END;
$$ LANGUAGE plpgsql stable strict;


select define_function_args('content_item__get_content_type','item_id');
--
-- procedure content_item__get_content_type/1
--
CREATE OR REPLACE FUNCTION content_item__get_content_type(
   get_content_type__item_id integer
) RETURNS varchar AS $$
DECLARE
  v_content_type                           cr_items.content_type%TYPE;
BEGIN

  select
    content_type into v_content_type
  from
    cr_items
  where
    item_id = get_content_type__item_id;

  return v_content_type;

END;
$$ LANGUAGE plpgsql stable strict;



select define_function_args('content_item__get_live_revision','item_id');
--
-- procedure content_item__get_live_revision/1
--
CREATE OR REPLACE FUNCTION content_item__get_live_revision(
   get_live_revision__item_id integer
) RETURNS integer AS $$
DECLARE
  v_revision_id                             acs_objects.object_id%TYPE;
BEGIN

  select
    live_revision into v_revision_id
  from
    cr_items
  where
    item_id = get_live_revision__item_id;

  return v_revision_id;

END;
$$ LANGUAGE plpgsql stable strict;


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,2,3,4
--
CREATE OR REPLACE FUNCTION content_item__set_live_revision(
   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 $$
DECLARE
BEGIN

  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
    publish_date = p__publish_date
  where
    revision_id = p__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
--
CREATE OR REPLACE FUNCTION content_item__unset_live_revision(
   unset_live_revision__item_id integer
) RETURNS integer AS $$
DECLARE
BEGIN

  update
    cr_items
  set
    live_revision = NULL
  where
    item_id = unset_live_revision__item_id;

  -- if an items publish status is "live", change it to "ready"
  update
    cr_items
  set
    publish_status = 'production'
  where
    publish_status = 'live'
  and
    item_id = unset_live_revision__item_id;

  return 0;
END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__set_release_period','item_id,start_when;null,end_when;null');
--
-- procedure content_item__set_release_period/3
--
CREATE OR REPLACE FUNCTION content_item__set_release_period(
   set_release_period__item_id integer,
   set_release_period__start_when timestamptz, -- default null
   set_release_period__end_when timestamptz    -- default null

) RETURNS integer AS $$
DECLARE
  v_count                                    integer;
BEGIN

  select count(*) into v_count from cr_release_periods
    where item_id = set_release_period__item_id;

  if v_count = 0 then
    insert into cr_release_periods (
      item_id, start_when, end_when
    ) values (
      set_release_period__item_id,
      set_release_period__start_when,
      set_release_period__end_when
    );
  else
    update cr_release_periods
      set start_when = set_release_period__start_when,
      end_when = set_release_period__end_when
    where
      item_id = set_release_period__item_id;
  end if;

  return 0;
END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__get_revision_count','item_id');
--
-- procedure content_item__get_revision_count/1
--
CREATE OR REPLACE FUNCTION content_item__get_revision_count(
   get_revision_count__item_id integer
) RETURNS integer AS $$
DECLARE
  v_count                       integer;
BEGIN

  select
    count(*) into v_count
  from
    cr_revisions
  where
    item_id = get_revision_count__item_id;

  return v_count;

END;
$$ LANGUAGE plpgsql stable;


select define_function_args('content_item__get_context','item_id');
--
-- procedure content_item__get_context/1
--
CREATE OR REPLACE FUNCTION content_item__get_context(
   get_context__item_id integer
) RETURNS integer AS $$
DECLARE
  v_context_id                        acs_objects.context_id%TYPE;
BEGIN

  select
    context_id
  into
    v_context_id
  from
    acs_objects
  where
    object_id = get_context__item_id;

  if NOT FOUND then
     raise EXCEPTION '-20000: Content item % does not exist in content_item.get_context', get_context__item_id;
  end if;

  return v_context_id;

END;
$$ LANGUAGE plpgsql stable;


-- 1) make sure we are not moving the item to an invalid location:
--   that is, the destination folder exists and is a valid folder
-- 2) make sure the content type of the content item is registered
--   to the target folder
-- 3) update the parent_id for the item


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 default null
) RETURNS integer AS $$
DECLARE
BEGIN

  if move__target_folder_id is null then
	raise exception 'attempt to move item_id % to null folder_id', move__item_id;
  end if;

  if content_folder__is_folder(move__item_id) = 't' then

    PERFORM content_folder__move(move__item_id, move__target_folder_id);

  elsif content_folder__is_folder(move__target_folder_id) = 't' then


    if content_folder__is_registered(move__target_folder_id,
          content_item__get_content_type(move__item_id),'f') = 't' and
       content_folder__is_registered(move__target_folder_id,
          content_item__get_content_type(content_symlink__resolve(move__item_id)),'f') = 't'
      then
    -- update the parent_id for the item

    update cr_items
      set parent_id = move__target_folder_id,
          name = coalesce(move__name, name)
      where item_id = move__item_id;
    end if;

    if move__name is not null then
      update acs_objects
        set title = move__name
        where object_id = move__item_id;
    end if;

  end if;

  return 0;
END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__generic_move','item_id,target_item_id,name');
--
-- procedure content_item__generic_move/3
--
CREATE OR REPLACE FUNCTION content_item__generic_move(
   move__item_id integer,
   move__target_item_id integer,
   move__name varchar
) RETURNS integer AS $$
DECLARE
BEGIN

  if move__target_item_id is null then
	raise exception 'attempt to move item_id % to null folder_id', move__item_id;
  end if;

  if content_folder__is_folder(move__item_id) = 't' then

    PERFORM content_folder__move(move__item_id, move__target_item_id);

  elsif content_folder__is_folder(move__target_item_id) = 't' then

    if content_folder__is_registered(move__target_item_id,
          content_item__get_content_type(move__item_id),'f') = 't' and
       content_folder__is_registered(move__target_item_id,
          content_item__get_content_type(content_symlink__resolve(move__item_id)),'f') = 't'
      then
    end if;
  end if;

  -- update the parent_id for the item

  update cr_items
    set parent_id = move__target_item_id,
        name = coalesce(move__name, name)
    where item_id = move__item_id;

  -- GN: the following "end if" appears to be not needed
  -- end if;

  if move__name is not null then
    update acs_objects
      set title = move__name
      where object_id = move__item_id;
  end if;

  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,
--   and is not the current folder
-- 2) make sure the content type of the content item is registered
--   with the current folder
-- 3) create a new item with no revisions in the target folder
-- 4) copy the latest revision from the original item to the new item (if any)


select define_function_args('content_item__copy2','item_id,target_folder_id,creation_user,creation_ip;null');
--
-- procedure content_item__copy2/4
--
CREATE OR REPLACE FUNCTION content_item__copy2(
   copy2__item_id integer,
   copy2__target_folder_id integer,
   copy2__creation_user integer,
   copy2__creation_ip varchar -- default null

) RETURNS integer AS $$
DECLARE
BEGIN

	perform content_item__copy (
		copy2__item_id,
		copy2__target_folder_id,
		copy2__creation_user,
		copy2__creation_ip,
		null
		);
	return copy2__item_id;

END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__copy','item_id,target_folder_id,creation_user,creation_ip;null,name;null');
--
-- 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

) RETURNS integer AS $$
DECLARE
  v_current_folder_id           cr_folders.folder_id%TYPE;
  v_num_revisions               integer;
  v_name                        cr_items.name%TYPE;
  v_content_type                cr_items.content_type%TYPE;
  v_locale                      cr_items.locale%TYPE;
  v_item_id                     cr_items.item_id%TYPE;
  v_revision_id                 cr_revisions.revision_id%TYPE;
  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_storage_type                cr_items.storage_type%TYPE;
BEGIN

  -- call content_folder.copy if the item is a folder
  if content_folder__is_folder(copy__item_id) = 't' then
    PERFORM content_folder__copy(
        copy__item_id,
        copy__target_folder_id,
        copy__creation_user,
        copy__creation_ip,
	copy__name
    );

  -- call content_symlink.copy if the item is a symlink
  else if content_symlink__is_symlink(copy__item_id) = 't' then
    PERFORM content_symlink__copy(
        copy__item_id,
        copy__target_folder_id,
        copy__creation_user,
        copy__creation_ip,
	copy__name
    );

  -- call content_extlink.copy if the item is a URL
  else if content_extlink__is_extlink(copy__item_id) = 't' then
    PERFORM content_extlink__copy(
        copy__item_id,
        copy__target_folder_id,
        copy__creation_user,
        copy__creation_ip,
	copy__name
    );

  -- make sure the target folder is really a folder
  else if content_folder__is_folder(copy__target_folder_id) = 't' then

    select
      parent_id
    into
      v_current_folder_id
    from
      cr_items
    where
      item_id = copy__item_id;

    select
      content_type, name, locale,
      coalesce(live_revision, latest_revision), storage_type
    into
      v_content_type, v_name, v_locale, v_revision_id, v_storage_type
    from
      cr_items
    where
      item_id = copy__item_id;

    -- 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
      v_is_registered := content_folder__is_registered(
          copy__target_folder_id,
          v_content_type,
          'f'
      );

      if v_is_registered = 't' then
        --
        -- 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,               -- item_id
            v_locale,
            now(),              -- creation_date
            copy__creation_user,
            null,               -- context_id
            copy__creation_ip,
            'content_item',
            v_content_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
          latest_revision, live_revision into v_old_revision_id, v_old_live_revision_id
        from
       	  cr_items
        where
       	  item_id = copy__item_id;
	end if;

        -- copy the latest revision (if any) to the new item
	if v_old_revision_id is not null then
          v_new_revision_id := content_revision__copy (
              v_old_revision_id,
              null,
              v_item_id,
              copy__creation_user,
              copy__creation_ip
          );
        end if;

        -- copy the live revision (if there is one and it differs from the latest) to the new item
	if v_old_live_revision_id is not null then
          if v_old_live_revision_id <> v_old_revision_id then
            v_new_live_revision_id := content_revision__copy (
              v_old_live_revision_id,
              null,
              v_item_id,
              copy__creation_user,
              copy__creation_ip
            );
          else
            v_new_live_revision_id := v_new_revision_id;
          end if;
        end if;

        update cr_items set live_revision = v_new_live_revision_id, latest_revision = v_new_revision_id where item_id = v_item_id;

    end if;

  end if; end if; end if; end if;

  return v_item_id;

END;
$$ LANGUAGE plpgsql;


select define_function_args('content_item__get_latest_revision','item_id');
--
-- procedure content_item__get_latest_revision/1
--
CREATE OR REPLACE FUNCTION content_item__get_latest_revision(
   get_latest_revision__item_id integer
) RETURNS integer AS $$
DECLARE
  v_revision_id                               integer;
  v_rec                                       record;
BEGIN
  for v_rec in
  select
    r.revision_id
  from
    cr_revisions r, acs_objects o
  where
    r.revision_id = o.object_id
  and
    r.item_id = get_latest_revision__item_id
  order by
    o.creation_date desc
  LOOP
      v_revision_id := v_rec.revision_id;
      exit;
  end LOOP;

  return v_revision_id;

END;
$$ LANGUAGE plpgsql strict stable;


select define_function_args('content_item__get_best_revision','item_id');
--
-- procedure content_item__get_best_revision/1
--
CREATE OR REPLACE FUNCTION content_item__get_best_revision(
   get_best_revision__item_id integer
) RETURNS integer AS $$
DECLARE
  v_revision_id                             cr_revisions.revision_id%TYPE;
BEGIN

  select
    coalesce(live_revision, latest_revision )
  into
    v_revision_id
  from
    cr_items
  where
    item_id = get_best_revision__item_id;

  return v_revision_id;

END;
$$ LANGUAGE plpgsql stable strict;


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'

) RETURNS varchar AS $$
DECLARE
  v_title                           cr_revisions.title%TYPE;
  v_content_type                    cr_items.content_type%TYPE;
BEGIN

  select content_type into v_content_type from cr_items
    where item_id = get_title__item_id;

  if v_content_type = 'content_folder' then
    select label into v_title from cr_folders
      where folder_id = get_title__item_id;
  else if v_content_type = 'content_symlink' then
    select label into v_title from cr_symlinks
      where symlink_id = get_title__item_id;
  else if v_content_type = 'content_extlink' then
    select label into v_title from cr_extlinks
      where extlink_id = get_title__item_id;
  else
    if get_title__is_live then
      select
	title into v_title
      from
	cr_revisions r, cr_items i
      where
        i.item_id = get_title__item_id
      and
        r.revision_id = i.live_revision;
    else
      select
	title into v_title
      from
	cr_revisions r, cr_items i
      where
        i.item_id = get_title__item_id
      and
        r.revision_id = i.latest_revision;
    end if;
  end if; end if; end if;

  return v_title;

END;
$$ LANGUAGE plpgsql stable;



select define_function_args('content_item__get_publish_date','item_id,is_live;f');
--
-- procedure content_item__get_publish_date/2
--
CREATE OR REPLACE FUNCTION content_item__get_publish_date(
   get_publish_date__item_id integer,
   get_publish_date__is_live boolean -- default 'f'

) RETURNS timestamptz AS $$
DECLARE
  v_revision_id                            cr_revisions.revision_id%TYPE;
  v_publish_date                           cr_revisions.publish_date%TYPE;
BEGIN

  if get_publish_date__is_live then
    select
	publish_date into v_publish_date
    from
	cr_revisions r, cr_items i
    where
      i.item_id = get_publish_date__item_id
    and
      r.revision_id = i.live_revision;
  else
    select
	publish_date into v_publish_date
    from
	cr_revisions r, cr_items i
    where
      i.item_id = get_publish_date__item_id
    and
      r.revision_id = i.latest_revision;
  end if;

  return v_publish_date;

END;
$$ LANGUAGE plpgsql stable;


select define_function_args('content_item__is_subclass','object_type,supertype');
--
-- procedure content_item__is_subclass/2
--
CREATE OR REPLACE FUNCTION content_item__is_subclass(
   is_subclass__object_type varchar,
   is_subclass__supertype varchar
) RETURNS boolean AS $$
DECLARE
  v_subclass_p                        boolean;
  v_inherit_val                       record;
BEGIN
  select count(*) > 0 into v_subclass_p where exists (
	select 1
          from acs_object_types o, acs_object_types o2
         where o2.object_type = is_subclass__supertype
           and o.object_type = is_subclass__object_type
           and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey));

  return v_subclass_p;

END;
$$ LANGUAGE plpgsql stable;



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
--
CREATE OR REPLACE FUNCTION content_item__relate(
   relate__item_id integer,
   relate__object_id integer,
   relate__relation_tag varchar, -- default 'generic'
   relate__order_n integer,      -- default null
   relate__relation_type varchar -- default 'cr_item_rel'

) RETURNS integer AS $$
DECLARE
  v_content_type                 cr_items.content_type%TYPE;
  v_object_type                  acs_objects.object_type%TYPE;
  v_is_valid                     integer;
  v_rel_id                       integer;
  v_package_id                   integer;
  v_exists                       integer;
  v_order_n                      cr_item_rels.order_n%TYPE;
BEGIN

  -- check the relationship is valid
  v_content_type := content_item__get_content_type (relate__item_id);
  v_object_type := content_item__get_content_type (relate__object_id);

  select
    count(1) into v_is_valid
  from
    cr_type_relations
  where
    content_item__is_subclass( v_object_type, target_type ) = 't'
  and
    content_item__is_subclass( v_content_type, content_type ) = 't';

  if v_is_valid = 0 then
    raise EXCEPTION '-20000: There is no registered relation type matching this item relation.';
  end if;

  if relate__item_id != relate__object_id then
    -- check that these two items are not related already
    --dbms_output.put_line( 'checking if the items are already related...');

    select
      rel_id, 1 into v_rel_id, v_exists
    from
      cr_item_rels
    where
      item_id = relate__item_id
    and
      related_object_id = relate__object_id
    and
      relation_tag = relate__relation_tag;

    if NOT FOUND then
       v_exists := 0;
    end if;

    v_package_id := acs_object__package_id(relate__item_id);

    -- if order_n is null, use rel_id (the order the item was related)
    if relate__order_n is null then
      v_order_n := v_rel_id;
    else
      v_order_n := relate__order_n;
    end if;


    -- if relationship does not exist, create it
    if v_exists <> 1 then
      --dbms_output.put_line( 'creating new relationship...');
      v_rel_id := acs_object__new(
        null,
        relate__relation_type,
        now(),
        null,
        null,
        relate__item_id,
        't',
        relate__relation_tag || ': ' || relate__item_id || ' - ' || relate__object_id,
        v_package_id
      );

      insert into cr_item_rels (
        rel_id, item_id, related_object_id, order_n, relation_tag
      ) values (
        v_rel_id, relate__item_id, relate__object_id, v_order_n,
        relate__relation_tag
      );

    -- if relationship already exists, update it
    else
      --dbms_output.put_line( 'updating existing relationship...');
      update cr_item_rels set
        relation_tag = relate__relation_tag,
        order_n = v_order_n
      where
        rel_id = v_rel_id;

      update acs_objects set
        title = relate__relation_tag || ': ' || relate__item_id || ' - ' || relate__object_id
      where object_id = v_rel_id;
    end if;

  end if;

  return v_rel_id;

END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__unrelate','rel_id');
--
-- procedure content_item__unrelate/1
--
CREATE OR REPLACE FUNCTION content_item__unrelate(
   unrelate__rel_id integer
) RETURNS integer AS $$
DECLARE
BEGIN

  -- delete the relation object
  PERFORM acs_rel__delete(unrelate__rel_id);

  -- delete the row from the cr_item_rels table
  delete from cr_item_rels where rel_id = unrelate__rel_id;

  return 0;
END;
$$ LANGUAGE plpgsql;



select define_function_args('content_item__is_index_page','item_id,folder_id');
--
-- procedure content_item__is_index_page/2
--
CREATE OR REPLACE FUNCTION content_item__is_index_page(
   is_index_page__item_id integer,
   is_index_page__folder_id integer
) RETURNS boolean AS $$
DECLARE
BEGIN
  if content_folder__get_index_page(is_index_page__folder_id) = is_index_page__item_id then
    return 't';
  else
    return 'f';
  end if;

END;
$$ LANGUAGE plpgsql stable;


select define_function_args('content_item__get_parent_folder','item_id');
--
-- procedure content_item__get_parent_folder/1
--
CREATE OR REPLACE FUNCTION content_item__get_parent_folder(
   get_parent_folder__item_id integer
) RETURNS integer AS $$
DECLARE
  v_folder_id                              cr_folders.folder_id%TYPE;
  v_parent_folder_p                        boolean default 'f';
BEGIN
  v_folder_id := get_parent_folder__item_id;

  while NOT v_parent_folder_p and v_folder_id is not null LOOP

    select
      parent_id, content_folder__is_folder(parent_id)
    into
      v_folder_id, v_parent_folder_p
    from
      cr_items
    where
      item_id = v_folder_id;

  end loop;

  return v_folder_id;

END;
$$ LANGUAGE plpgsql stable strict;



-- Trigger to maintain context_id in acs_objects
CREATE OR REPLACE FUNCTION cr_items_update_tr () RETURNS trigger AS $$
BEGIN

  if new.parent_id <> old.parent_id then
    update acs_objects set context_id = new.parent_id
    where object_id = new.item_id;
  end if;

  return new;
END;
$$ LANGUAGE plpgsql;

create trigger cr_items_update_tr after update on cr_items
for each row execute procedure cr_items_update_tr ();


-- Trigger to maintain publication audit trail
CREATE OR REPLACE FUNCTION cr_items_publish_update_tr () RETURNS trigger AS $$
BEGIN
  if new.live_revision <> old.live_revision or
     new.publish_status <> old.publish_status
  then

    insert into cr_item_publish_audit (
      item_id, old_revision, new_revision, old_status, new_status, publish_date
    ) values (
      new.item_id, old.live_revision, new.live_revision,
      old.publish_status, new.publish_status,
      now()
    );

  end if;

  return new;

END;
$$ LANGUAGE plpgsql;

create trigger cr_items_publish_update_tr before update on cr_items
for each row execute procedure cr_items_publish_update_tr ();
