-- Data model to support content repository of the ArsDigita Community
-- System
-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Authors: Michael Pih (pihman@arsdigita.com)
-- Karl Goldstein (karlg@arsdigita.com)
-- $Id: content-type.sql,v 1.62.2.1 2019/08/10 18:09:52 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 a trigger to make sure that there will never be more than
-- one default template for a given content type and use context
CREATE OR REPLACE FUNCTION cr_type_template_map_tr () RETURNS trigger AS $$
BEGIN
if new.is_default = 't' then
update
cr_type_template_map
set
is_default = 'f'
where
content_type = new.content_type
and
use_context = new.use_context
and
template_id <> new.template_id
and
is_default = 't';
end if;
return new;
END;
$$ LANGUAGE plpgsql;
create trigger cr_type_template_map_tr before insert on cr_type_template_map
for each row execute procedure cr_type_template_map_tr ();
-- old define_function_args('content_type__create_type','content_type,supertype;content_revision,pretty_name,pretty_plural,table_name,id_column,name_method')
-- new
select define_function_args('content_type__create_type','content_type,supertype;content_revision,pretty_name,pretty_plural,table_name,id_column;XXX,name_method;null');
--
-- procedure content_type__create_type/7
--
CREATE OR REPLACE FUNCTION content_type__create_type(
create_type__content_type varchar,
create_type__supertype varchar, -- default 'content_revision'
create_type__pretty_name varchar,
create_type__pretty_plural varchar,
create_type__table_name varchar,
create_type__id_column varchar, -- default 'XXX'
create_type__name_method varchar -- default null
) RETURNS integer AS $$
DECLARE
v_temp_p boolean;
v_supertype_table acs_object_types.table_name%TYPE;
BEGIN
if (create_type__supertype <> 'content_revision')
and (create_type__content_type <> 'content_revision') then
select count(*) > 0 into v_temp_p
from acs_object_type_supertype_map
where object_type = create_type__supertype
and ancestor_type = 'content_revision';
if not v_temp_p then
raise EXCEPTION '-20000: supertype % must be a subtype of content_revision', create_type__supertype;
end if;
end if;
select count(*) = 0 into v_temp_p
from pg_class
where relname = lower(create_type__table_name);
PERFORM acs_object_type__create_type (
create_type__content_type,
create_type__pretty_name,
create_type__pretty_plural,
create_type__supertype,
create_type__table_name,
create_type__id_column,
null,
'f',
null,
create_type__name_method,
v_temp_p,
'f'
);
PERFORM content_type__refresh_view(create_type__content_type);
return 0;
END;
$$ LANGUAGE plpgsql;
-- old define_function_args('content_type__drop_type','content_type,drop_children_p;f,drop_table_p;f,drop_objects_p;f')
-- new
select define_function_args('content_type__drop_type','content_type,drop_children_p;f,drop_table_p;f,drop_objects_p;f');
--
-- procedure content_type__drop_type/4
--
CREATE OR REPLACE FUNCTION content_type__drop_type(
drop_type__content_type varchar,
drop_type__drop_children_p boolean, -- default 'f'
drop_type__drop_table_p boolean, -- default 'f'
drop_type__drop_objects_p boolean -- default 'f'
) RETURNS integer AS $$
DECLARE
table_exists_p boolean;
v_table_name varchar;
is_subclassed_p boolean;
child_rec record;
attr_row record;
revision_row record;
item_row record;
BEGIN
-- first we'll rid ourselves of any dependent child types, if any ,
-- along with their own dependent grandchild types
select
count(*) > 0 into is_subclassed_p
from
acs_object_types
where supertype = drop_type__content_type;
-- this is weak and will probably break;
-- to remove grand child types, the process will probably
-- require some sort of querying for drop_type
-- methods within the children's packages to make
-- certain there are no additional unanticipated
-- restraints preventing a clean drop
if drop_type__drop_children_p and is_subclassed_p then
for child_rec in select
object_type
from
acs_object_types
where
supertype = drop_type__content_type
LOOP
PERFORM content_type__drop_type(child_rec.object_type, 't', drop_type__drop_table_p, drop_type__drop_objects_p);
end LOOP;
end if;
-- now drop all the attributes related to this type
for attr_row in select
attribute_name
from
acs_attributes
where
object_type = drop_type__content_type
LOOP
PERFORM content_type__drop_attribute(drop_type__content_type,
attr_row.attribute_name,
'f'
);
end LOOP;
-- we'll remove the associated table if it exists
select
table_exists(lower(table_name)) into table_exists_p
from
acs_object_types
where
object_type = drop_type__content_type;
if table_exists_p and drop_type__drop_table_p then
select
table_name into v_table_name
from
acs_object_types
where
object_type = drop_type__content_type;
-- drop the rule and input/output views for the type
-- being dropped.
-- FIXME: this did not exist in the oracle code and it needs to be
-- tested. Thanks to Vinod Kurup for pointing this out.
-- The rule dropping might be redundant as the rule might be dropped
-- when the view is dropped.
-- different syntax for dropping a rule in 7.2 and 7.3 so check which
-- version is being used (olah).
execute 'drop table ' || v_table_name || ' cascade';
end if;
-- If we are dealing with a revision, delete the revision with revision__delete
-- This way the integrity constraint with live revision is dealt with correctly
if drop_type__drop_objects_p then
for revision_row in
select revision_id
from cr_revisions, acs_objects
where revision_id = object_id
and object_type = drop_type__content_type
loop
PERFORM content_revision__delete(revision_row.revision_id);
end loop;
for item_row in
select item_id
from cr_items
where content_type = drop_type__content_type
loop
PERFORM content_item__delete(item_row.item_id);
end loop;
end if;
PERFORM acs_object_type__drop_type(drop_type__content_type, drop_type__drop_objects_p);
return 0;
END;
$$ LANGUAGE plpgsql;
-- don't define function_args twice
--
-- old define_function_args('content_type__drop_type','content_type,drop_children_p;f,drop_table_p;f')
-- new
select define_function_args('content_type__drop_type','content_type,drop_children_p;f,drop_table_p;f,drop_objects_p;f');
--
-- procedure content_type__drop_type/3
--
CREATE OR REPLACE FUNCTION content_type__drop_type(
drop_type__content_type varchar,
drop_type__drop_children_p boolean, -- default 'f'
drop_type__drop_table_p boolean -- default 'f'
) RETURNS integer AS $$
DECLARE
table_exists_p boolean;
v_table_name varchar;
is_subclassed_p boolean;
child_rec record;
attr_row record;
BEGIN
-- first we'll rid ourselves of any dependent child types, if any ,
-- along with their own dependent grandchild types
select
count(*) > 0 into is_subclassed_p
from
acs_object_types
where supertype = drop_type__content_type;
-- this is weak and will probably break;
-- to remove grand child types, the process will probably
-- require some sort of querying for drop_type
-- methods within the children's packages to make
-- certain there are no additional unanticipated
-- restraints preventing a clean drop
if drop_type__drop_children_p and is_subclassed_p then
for child_rec in select
object_type
from
acs_object_types
where
supertype = drop_type__content_type
LOOP
PERFORM content_type__drop_type(child_rec.object_type, 't', 'f');
end LOOP;
end if;
-- now drop all the attributes related to this type
for attr_row in select
attribute_name
from
acs_attributes
where
object_type = drop_type__content_type
LOOP
PERFORM content_type__drop_attribute(drop_type__content_type,
attr_row.attribute_name,
'f'
);
end LOOP;
-- we'll remove the associated table if it exists
select
table_exists(lower(table_name)) into table_exists_p
from
acs_object_types
where
object_type = drop_type__content_type;
if table_exists_p and drop_type__drop_table_p then
select
table_name into v_table_name
from
acs_object_types
where
object_type = drop_type__content_type;
-- drop the rule and input/output views for the type
-- being dropped.
-- FIXME: this did not exist in the oracle code and it needs to be
-- tested. Thanks to Vinod Kurup for pointing this out.
-- The rule dropping might be redundant as the rule might be dropped
-- when the view is dropped.
execute 'drop rule ' || v_table_name || '_r ' || 'on ' || v_table_name || 'i';
execute 'drop view ' || v_table_name || 'x cascade';
execute 'drop view ' || v_table_name || 'i cascade';
execute 'drop table ' || v_table_name;
end if;
PERFORM acs_object_type__drop_type(drop_type__content_type, 'f');
return 0;
END;
$$ LANGUAGE plpgsql;
-- old define_function_args('content_type__create_attribute','content_type,attribute_name,datatype,pretty_name,pretty_plural,sort_order,default_value,column_spec;text')
-- new
select define_function_args('content_type__create_attribute','content_type,attribute_name,datatype,pretty_name,pretty_plural;null,sort_order;null,default_value;null,column_spec;text');
--
-- procedure content_type__create_attribute/8
--
CREATE OR REPLACE FUNCTION content_type__create_attribute(
create_attribute__content_type varchar,
create_attribute__attribute_name varchar,
create_attribute__datatype varchar,
create_attribute__pretty_name varchar,
create_attribute__pretty_plural varchar, -- default null
create_attribute__sort_order integer, -- default null
create_attribute__default_value varchar, -- default null
create_attribute__column_spec varchar -- default 'text'
) RETURNS integer AS $$
DECLARE
v_attr_id acs_attributes.attribute_id%TYPE;
v_table_name acs_object_types.table_name%TYPE;
v_column_exists boolean;
BEGIN
-- add the appropriate column to the table
select table_name into v_table_name from acs_object_types
where object_type = create_attribute__content_type;
if NOT FOUND then
raise EXCEPTION '-20000: Content type % does not exist in content_type.create_attribute', create_attribute__content_type;
end if;
select count(*) > 0 into v_column_exists
from pg_class c, pg_attribute a
where c.relname::varchar = v_table_name
and c.oid = a.attrelid
and a.attname = lower(create_attribute__attribute_name);
v_attr_id := acs_attribute__create_attribute (
create_attribute__content_type,
create_attribute__attribute_name,
create_attribute__datatype,
create_attribute__pretty_name,
create_attribute__pretty_plural,
null,
null,
create_attribute__default_value,
1,
1,
create_attribute__sort_order,
'type_specific',
'f',
not v_column_exists,
null,
null,
null,
null,
null,
create_attribute__column_spec
);
PERFORM content_type__refresh_view(create_attribute__content_type);
return v_attr_id;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__drop_attribute','content_type,attribute_name,drop_column;f');
--
-- procedure content_type__drop_attribute/3
--
CREATE OR REPLACE FUNCTION content_type__drop_attribute(
drop_attribute__content_type varchar,
drop_attribute__attribute_name varchar,
drop_attribute__drop_column boolean -- default 'f'
) RETURNS integer AS $$
DECLARE
v_attr_id acs_attributes.attribute_id%TYPE;
v_table acs_object_types.table_name%TYPE;
BEGIN
-- Get attribute information
select
upper(t.table_name), a.attribute_id
into
v_table, v_attr_id
from
acs_object_types t, acs_attributes a
where
t.object_type = drop_attribute__content_type
and
a.object_type = drop_attribute__content_type
and
a.attribute_name = drop_attribute__attribute_name;
if NOT FOUND then
raise EXCEPTION '-20000: Attribute %:% does not exist in content_type.drop_attribute', drop_attribute__content_type, drop_attribute__attribute_name;
end if;
-- Drop the attribute
PERFORM acs_attribute__drop_attribute(drop_attribute__content_type,
drop_attribute__attribute_name);
-- FIXME: postgresql does not support drop column.
-- Drop the column if necessary
if drop_attribute__drop_column then
execute 'alter table ' || v_table || ' drop column ' ||
drop_attribute__attribute_name || ' cascade';
end if;
PERFORM content_type__refresh_view(drop_attribute__content_type);
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__register_template','content_type,template_id,use_context,is_default;f');
--
-- procedure content_type__register_template/4
--
CREATE OR REPLACE FUNCTION content_type__register_template(
register_template__content_type varchar,
register_template__template_id integer,
register_template__use_context varchar,
register_template__is_default boolean -- default 'f'
) RETURNS integer AS $$
DECLARE
v_template_registered boolean;
BEGIN
select
count(*) > 0 into v_template_registered
from
cr_type_template_map
where
content_type = register_template__content_type
and
use_context = register_template__use_context
and
template_id = register_template__template_id;
-- register the template
if NOT v_template_registered then
insert into cr_type_template_map (
template_id, content_type, use_context, is_default
) values (
register_template__template_id, register_template__content_type,
register_template__use_context, register_template__is_default
);
-- update the registration status of the template
else
-- unset the default template before setting this one as the default
if register_template__is_default then
update cr_type_template_map
set is_default = 'f'
where content_type = register_template__content_type
and use_context = register_template__use_context;
end if;
update cr_type_template_map
set is_default = register_template__is_default
where template_id = register_template__template_id
and content_type = register_template__content_type
and use_context = register_template__use_context;
end if;
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__set_default_template','content_type,template_id,use_context');
--
-- procedure content_type__set_default_template/3
--
CREATE OR REPLACE FUNCTION content_type__set_default_template(
set_default_template__content_type varchar,
set_default_template__template_id integer,
set_default_template__use_context varchar
) RETURNS integer AS $$
DECLARE
BEGIN
update cr_type_template_map
set is_default = 't'
where template_id = set_default_template__template_id
and content_type = set_default_template__content_type
and use_context = set_default_template__use_context;
-- make sure there is only one default template for
-- any given content_type/use_context pair
update cr_type_template_map
set is_default = 'f'
where template_id <> set_default_template__template_id
and content_type = set_default_template__content_type
and use_context = set_default_template__use_context
and is_default = 't';
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__get_template','content_type,use_context');
--
-- procedure content_type__get_template/2
--
CREATE OR REPLACE FUNCTION content_type__get_template(
get_template__content_type varchar,
get_template__use_context varchar
) RETURNS integer AS $$
DECLARE
v_template_id cr_templates.template_id%TYPE;
BEGIN
select
template_id
into
v_template_id
from
cr_type_template_map
where
content_type = get_template__content_type
and
use_context = get_template__use_context
and
is_default = 't';
return v_template_id;
END;
$$ LANGUAGE plpgsql stable strict;
-- old define_function_args('content_type__unregister_template','content_type,template_id,use_context')
-- new
select define_function_args('content_type__unregister_template','content_type;null,template_id,use_context;null');
--
-- procedure content_type__unregister_template/3
--
CREATE OR REPLACE FUNCTION content_type__unregister_template(
unregister_template__content_type varchar, -- default null
unregister_template__template_id integer,
unregister_template__use_context varchar -- default null
) RETURNS integer AS $$
DECLARE
BEGIN
if unregister_template__use_context is null and
unregister_template__content_type is null then
delete from cr_type_template_map
where template_id = unregister_template__template_id;
else if unregister_template__use_context is null then
delete from cr_type_template_map
where template_id = unregister_template__template_id
and content_type = unregister_template__content_type;
else if unregister_template__content_type is null then
delete from cr_type_template_map
where template_id = unregister_template__template_id
and use_context = unregister_template__use_context;
else
delete from cr_type_template_map
where template_id = unregister_template__template_id
and content_type = unregister_template__content_type
and use_context = unregister_template__use_context;
end if; end if; end if;
return 0;
END;
$$ LANGUAGE plpgsql;
-- function trigger_insert_statement
select define_function_args('content_type__trigger_insert_statement','content_type');
--
-- procedure content_type__trigger_insert_statement/1
--
CREATE OR REPLACE FUNCTION content_type__trigger_insert_statement(
trigger_insert_statement__content_type varchar
) RETURNS varchar AS $$
DECLARE
v_table_name acs_object_types.table_name%TYPE;
v_id_column acs_object_types.id_column%TYPE;
cols varchar default '';
vals varchar default '';
attr_rec record;
BEGIN
if trigger_insert_statement__content_type is null then
return exception 'content_type__trigger_insert_statement called with null content_type';
end if;
select
table_name, id_column into v_table_name, v_id_column
from
acs_object_types
where
object_type = trigger_insert_statement__content_type;
for attr_rec in select
attribute_name
from
acs_attributes
where
object_type = trigger_insert_statement__content_type
LOOP
cols := cols || ', ' || attr_rec.attribute_name;
vals := vals || ', p_new.' || attr_rec.attribute_name;
end LOOP;
return 'insert into ' || v_table_name ||
' ( ' || v_id_column || cols || ' ) values (v_revision_id' ||
vals || ')';
END;
$$ LANGUAGE plpgsql stable;
-- FIXME: need to look at this in more detail. This probably can't be made
-- to work reliably in PostgreSQL. Currently we are using a rule to insert
-- into the input view when a new content revision is added. Pg locks the
-- underlying table when the rule is dropped, so the dropping and recreating
-- of the new content revisions seems like it would be reliable, but the
-- possibility of a race condition exists for either the initial creation
-- of dropping of a type. I'm not sure if the possibility of a race condition
-- actually exists in practice. The thing to do here might be to just create
-- a function that dynamically builds the insert strings and does the
-- each time an insert is done on the content_type view. Trade-off being
-- that the inserts would be slower due to the use of dynamic code in pl/psql.
-- More digging required ...
-- DCW, 2001-03-30.
-- Create or replace a trigger on insert for simplifying addition of
-- revisions for any content type
--
-- procedure content_type__refresh_trigger/1
--
select define_function_args('content_type__refresh_trigger','content_type');
CREATE OR REPLACE FUNCTION content_type__refresh_trigger(
refresh_trigger__content_type varchar
) RETURNS integer AS $$
DECLARE
rule_text text default '';
function_text text default '';
v_table_name acs_object_types.table_name%TYPE;
type_rec record;
BEGIN
-- get the table name for the content type (determines view name)
raise NOTICE 'refresh trigger for % ', refresh_trigger__content_type;
-- Since we allow null table name use object type if table name is null so
-- we still can have a view.
select coalesce(table_name,object_type)
into v_table_name
from acs_object_types
where object_type = refresh_trigger__content_type;
--=================== start building rule code =======================
function_text := function_text ||
'create or replace function ' || v_table_name || '_f (p_new '|| v_table_name || 'i)
returns void as ''
declare
v_revision_id integer;
begin
select content_revision__new(
p_new.title,
p_new.description,
p_new.publish_date,
p_new.mime_type,
p_new.nls_language,
case when p_new.text is null
then p_new.data
else p_new.text
end,
content_symlink__resolve(p_new.item_id),
p_new.revision_id,
now(),
p_new.creation_user,
p_new.creation_ip,
null, -- content_length
p_new.object_package_id
) into v_revision_id;
';
-- add an insert statement for each subtype in the hierarchy for this type
for type_rec in select ot2.object_type, tree_level(ot2.tree_sortkey) as level
from acs_object_types ot1, acs_object_types ot2
where ot2.object_type <> 'acs_object'
and ot2.object_type <> 'content_revision'
and ot1.object_type = refresh_trigger__content_type
and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
and ot1.table_name is not null
order by level asc
LOOP
function_text := function_text || content_type__trigger_insert_statement(type_rec.object_type) || ';
';
end loop;
function_text := function_text || '
return;
end;'' language plpgsql;
';
-- end building the rule definition code
-- create the new function
execute function_text;
rule_text := 'create rule ' || v_table_name || '_r as on insert to ' ||
v_table_name || 'i do instead SELECT ' || v_table_name || '_f(new); ' ;
--================== done building rule code =======================
-- drop the old rule
if rule_exists(v_table_name || '_r', v_table_name || 'i') then
execute 'drop rule ' || v_table_name || '_r ' || 'on ' || v_table_name || 'i';
end if;
-- create the new rule for inserts on the content type
execute rule_text;
return null;
END;
$$ LANGUAGE plpgsql;
--
-- procedure content_type__refresh_view/1
--
select define_function_args('content_type__refresh_view','content_type');
CREATE OR REPLACE FUNCTION content_type__refresh_view(
refresh_view__content_type varchar
) RETURNS integer AS $$
DECLARE
cols varchar default '';
tabs varchar default '';
joins varchar default '';
v_table_name varchar;
join_rec record;
BEGIN
for join_rec in select ot2.table_name, ot2.id_column, tree_level(ot2.tree_sortkey) as level
from acs_object_types ot1, acs_object_types ot2
where ot2.object_type <> 'acs_object'
and ot2.object_type <> 'content_revision'
and lower(ot2.table_name) <> 'acs_objects'
and lower(ot2.table_name) <> 'cr_revisions'
and ot1.object_type = refresh_view__content_type
and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
order by ot2.tree_sortkey desc
LOOP
if join_rec.table_name is not null then
cols := cols || ', ' || join_rec.table_name || '.*';
tabs := tabs || ', ' || join_rec.table_name;
joins := joins || ' and acs_objects.object_id = ' ||
join_rec.table_name || '.' || join_rec.id_column;
end if;
end loop;
-- Since we allow null table name use object type if table name is null so
-- we still can have a view.
select coalesce(table_name,object_type) into v_table_name from acs_object_types
where object_type = refresh_view__content_type;
if length(v_table_name) > 57 then
raise exception 'Table name cannot be longer than 57 characters, because that causes conflicting rules when we create the views.';
end if;
-- create the input view (includes content columns)
if table_exists(v_table_name || 'i') then
execute 'drop view ' || v_table_name || 'i' || ' CASCADE';
end if;
-- FIXME: need to look at content_revision__get_content. Since the CR
-- can store data in a lob, a text field or in an external file, getting
-- the data attribute for this view will be problematic.
execute 'create view ' || v_table_name ||
'i as select acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
cr.revision_id, cr.title, cr.item_id,
content_revision__get_content(cr.revision_id) as data,
cr_text.text_data as text,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language' ||
cols ||
' from acs_objects, cr_revisions cr, cr_text' || tabs || ' where
acs_objects.object_id = cr.revision_id ' || joins;
-- create the output view (excludes content columns to enable SELECT *)
if table_exists(v_table_name || 'x') then
execute 'drop view ' || v_table_name || 'x cascade';
end if;
execute 'create view ' || v_table_name ||
'x as select acs_objects.object_id,
acs_objects.object_type,
acs_objects.title as object_title,
acs_objects.package_id as object_package_id,
acs_objects.context_id,
acs_objects.security_inherit_p,
acs_objects.creation_user,
acs_objects.creation_date,
acs_objects.creation_ip,
acs_objects.last_modified,
acs_objects.modifying_user,
acs_objects.modifying_ip,
cr.revision_id, cr.title, cr.item_id,
cr.description, cr.publish_date, cr.mime_type, cr.nls_language,
i.name, i.parent_id' ||
cols ||
' from acs_objects, cr_revisions cr, cr_items i, cr_text' || tabs ||
' where acs_objects.object_id = cr.revision_id
and cr.item_id = i.item_id' || joins;
PERFORM content_type__refresh_trigger(refresh_view__content_type);
-- exception
-- when others then
-- dbms_output.put_line('Error creating attribute view or trigger for'
-- || content_type);
return 0;
END;
$$ LANGUAGE plpgsql;
-- old define_function_args('content_type__register_child_type','parent_type,child_type,relation_tag;generic,min_n;0,max_n')
-- new
select define_function_args('content_type__register_child_type','parent_type,child_type,relation_tag;generic,min_n;0,max_n;null');
-- procedure register_child_type
-- old define_function_args('content_type__register_child_type','parent_type,child_type,relation_tag;generic,min_n;0,max_n')
-- new
select define_function_args('content_type__register_child_type','parent_type,child_type,relation_tag;generic,min_n;0,max_n;null');
--
-- procedure content_type__register_child_type/5
--
CREATE OR REPLACE FUNCTION content_type__register_child_type(
register_child_type__parent_type varchar,
register_child_type__child_type varchar,
register_child_type__relation_tag varchar, -- default 'generic'
register_child_type__min_n integer, -- default 0 -- default '0'
register_child_type__max_n integer -- default null
) RETURNS integer AS $$
DECLARE
v_exists integer;
BEGIN
select count(*) into v_exists
from cr_type_children
where parent_type = register_child_type__parent_type
and child_type = register_child_type__child_type
and relation_tag = register_child_type__relation_tag;
if v_exists = 0 then
insert into cr_type_children (
parent_type, child_type, relation_tag, min_n, max_n
) values (
register_child_type__parent_type, register_child_type__child_type,
register_child_type__relation_tag,
register_child_type__min_n,
register_child_type__max_n
);
else
update cr_type_children set
min_n = register_child_type__min_n,
max_n = register_child_type__max_n
where
parent_type = register_child_type__parent_type
and
child_type = register_child_type__child_type
and
relation_tag = register_child_type__relation_tag;
end if;
return 0;
END;
$$ LANGUAGE plpgsql;
-- old define_function_args('content_type__unregister_child_type','content_type,child_type,relation_tag')
-- new
select define_function_args('content_type__unregister_child_type','parent_type,child_type,relation_tag');
--
-- procedure content_type__unregister_child_type/3
--
CREATE OR REPLACE FUNCTION content_type__unregister_child_type(
unregister_child_type__parent_type varchar,
unregister_child_type__child_type varchar,
unregister_child_type__relation_tag varchar
) RETURNS integer AS $$
DECLARE
BEGIN
delete from
cr_type_children
where
parent_type = unregister_child_type__parent_type
and
child_type = unregister_child_type__child_type
and
relation_tag = unregister_child_type__relation_tag;
return 0;
END;
$$ LANGUAGE plpgsql;
-- old define_function_args('content_type__register_relation_type','content_type,target_type,relation_tag;generic,min_n;0,max_n')
-- new
select define_function_args('content_type__register_relation_type','content_type,target_type,relation_tag;generic,min_n;0,max_n;null');
--
-- procedure content_type__register_relation_type/5
--
CREATE OR REPLACE FUNCTION content_type__register_relation_type(
register_relation_type__content_type varchar,
register_relation_type__target_type varchar,
register_relation_type__relation_tag varchar, -- default 'generic'
register_relation_type__min_n integer, -- default 0 -- default '0'
register_relation_type__max_n integer -- default null
) RETURNS integer AS $$
DECLARE
v_exists integer;
BEGIN
-- check if the relation type exists
select
count(*) into v_exists
from
cr_type_relations
where
content_type = register_relation_type__content_type
and
target_type = register_relation_type__target_type
and
relation_tag = register_relation_type__relation_tag;
-- if the relation type does not exist, insert a row into cr_type_relations
if v_exists = 0 then
insert into cr_type_relations (
content_type, target_type, relation_tag, min_n, max_n
) values (
register_relation_type__content_type,
register_relation_type__target_type,
register_relation_type__relation_tag,
register_relation_type__min_n, register_relation_type__max_n
);
-- otherwise, update the row in cr_type_relations
else
update cr_type_relations set
min_n = register_relation_type__min_n,
max_n = register_relation_type__max_n
where
content_type = register_relation_type__content_type
and
target_type = register_relation_type__target_type
and
relation_tag = register_relation_type__relation_tag;
end if;
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__unregister_relation_type','content_type,target_type,relation_tag;null');
--
-- procedure content_type__unregister_relation_type/3
--
CREATE OR REPLACE FUNCTION content_type__unregister_relation_type(
unregister_relation_type__content_type varchar,
unregister_relation_type__target_type varchar,
unregister_relation_type__relation_tag varchar -- default null
) RETURNS integer AS $$
DECLARE
BEGIN
delete from
cr_type_relations
where
content_type = unregister_relation_type__content_type
and
target_type = unregister_relation_type__target_type
and
relation_tag = unregister_relation_type__relation_tag;
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__register_mime_type','content_type,mime_type');
--
-- procedure content_type__register_mime_type/2
--
CREATE OR REPLACE FUNCTION content_type__register_mime_type(
register_mime_type__content_type varchar,
register_mime_type__mime_type varchar
) RETURNS integer AS $$
DECLARE
v_valid_registration integer;
BEGIN
-- check if this type is already registered
select
count(*) into v_valid_registration
from
cr_mime_types
where
not exists ( select 1
from
cr_content_mime_type_map
where
mime_type = register_mime_type__mime_type
and
content_type = register_mime_type__content_type )
and
mime_type = register_mime_type__mime_type;
if v_valid_registration = 1 then
insert into cr_content_mime_type_map (
content_type, mime_type
) values (
register_mime_type__content_type, register_mime_type__mime_type
);
end if;
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__unregister_mime_type','content_type,mime_type');
--
-- procedure content_type__unregister_mime_type/2
--
CREATE OR REPLACE FUNCTION content_type__unregister_mime_type(
unregister_mime_type__content_type varchar,
unregister_mime_type__mime_type varchar
) RETURNS integer AS $$
DECLARE
BEGIN
delete from cr_content_mime_type_map
where content_type = unregister_mime_type__content_type
and mime_type = unregister_mime_type__mime_type;
return 0;
END;
$$ LANGUAGE plpgsql;
select define_function_args('content_type__is_content_type','object_type');
--
-- procedure content_type__is_content_type/1
--
CREATE OR REPLACE FUNCTION content_type__is_content_type(
is_content_type__object_type varchar
) RETURNS boolean AS $$
DECLARE
v_is_content_type boolean;
BEGIN
if is_content_type__object_type = 'content_revision' then
v_is_content_type := 't';
else
select count(*) > 0 into v_is_content_type
from acs_object_type_supertype_map
where object_type = is_content_type__object_type
and ancestor_type = 'content_revision';
end if;
return v_is_content_type;
END;
$$ LANGUAGE plpgsql stable;
select define_function_args('content_type__rotate_template','template_id,v_content_type,use_context');
--
-- procedure content_type__rotate_template/3
--
CREATE OR REPLACE FUNCTION content_type__rotate_template(
rotate_template__template_id integer,
rotate_template__v_content_type varchar,
rotate_template__use_context varchar
) RETURNS integer AS $$
DECLARE
v_template_id cr_templates.template_id%TYPE;
v_items_val record;
BEGIN
-- get the default template
select
template_id into v_template_id
from
cr_type_template_map
where
content_type = rotate_template__v_content_type
and
use_context = rotate_template__use_context
and
is_default = 't';
if v_template_id is not null then
-- register an item-template to all items without an item-template
for v_items_val in select
item_id
from
cr_items i, cr_type_template_map m
where
i.content_type = rotate_template__v_content_type
and
m.use_context = rotate_template__use_context
and
i.content_type = m.content_type
and
not exists ( select 1
from
cr_item_template_map
where
item_id = i.item_id
and
use_context = rotate_template__use_context )
LOOP
PERFORM content_item__register_template (
v_items_val.item_id,
v_template_id,
rotate_template__use_context
);
end loop;
end if;
-- register the new template as the default template of the content type
if v_template_id != rotate_template__template_id then
PERFORM content_type__register_template(
rotate_template__v_content_type,
rotate_template__template_id,
rotate_template__use_context,
't'
);
end if;
return 0;
END;
$$ LANGUAGE plpgsql;
-- show errors
-- Refresh the attribute views
-- prompt *** Refreshing content type attribute views...
--
-- procedure inline_0/0
--
CREATE OR REPLACE FUNCTION inline_0(
) RETURNS integer AS $$
DECLARE
type_rec record;
BEGIN
for type_rec in select ot.object_type
from acs_object_types ot, acs_object_types ot2
where ot2.object_type = 'content_revision'
and ot.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
order by ot.tree_sortkey
LOOP
PERFORM content_type__refresh_view (type_rec.object_type);
end LOOP;
return 0;
END;
$$ LANGUAGE plpgsql;
select inline_0 ();
drop function inline_0 ();
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Content Repository 5.10.1d5 : content-type.sql
Begin main content
