--
--
--
-- @author Dave Bauer (dave@thedesignexperience.org)
-- @creation-date 2009-10-12
-- @cvs-id $Id: upgrade-5.6.0d2-5.6.0d3.sql,v 1.1 2009/10/12 22:42:34 daveb Exp $
--
create or replace function content_type__refresh_trigger (varchar)
returns integer as '
declare
refresh_trigger__content_type alias for $1;
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,
now(),
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,
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
-- different syntax for dropping a rule in 7.2 and 7.3 so check which
-- version is being used (olah).
if version() like ''%PostgreSQL 7.2%'' then
execute ''drop rule '' || v_table_name || ''_r'';
else
-- 7.3 syntax
execute ''drop rule '' || v_table_name || ''_r '' || ''on '' || v_table_name || ''i'';
end if;
end if;
-- create the new rule for inserts on the content type
execute rule_text;
return null;
end;' language 'plpgsql';
create or replace function content_type__refresh_view (varchar)
returns integer as '
declare
refresh_view__content_type alias for $1;
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,
acs_objects.tree_sortkey,
acs_objects.max_child_sortkey, 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'';
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,
acs_objects.tree_sortkey,
acs_objects.max_child_sortkey, 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';
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Content Repository 5.10.1 : upgrade-5.6.0d2-5.6.0d3.sql
Begin main content
