-- 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.77.2.2 2023/10/30 16:58:17 antoniop 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 ();
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Content Repository 5.10.1 : content-item.sql
Begin main content
