-- Upgrade script -- -- Extra mime types for the richtext widget -- Fixed typo in content_keyword__is_assigned -- -- @author Lars Pind <lars@pinds.com> -- @created 2003-01-27 insert into cr_mime_types(label, mime_type, file_extension) values ('Enhanced text', 'text/enhanced', 'etxt'); insert into cr_mime_types(label, mime_type, file_extension) values ('Fixed-width text', 'text/fixed-width', 'ftxt'); create or replace function content_keyword__is_assigned (integer,integer,varchar) returns boolean as ' declare is_assigned__item_id alias for $1; is_assigned__keyword_id alias for $2; is_assigned__recurse alias for $3; -- default ''none'' v_ret boolean; begin -- Look for an exact match if is_assigned__recurse = ''none'' then return count(*) > 0 from cr_item_keyword_map where item_id = is_assigned__item_id and keyword_id = is_assigned__keyword_id; end if; -- Look from specific to general if is_assigned__recurse = ''up'' then return count(*) > 0 where exists (select 1 from (select keyword_id from cr_keywords c, cr_keywords c2 where c2.keyword_id = is_assigned__keyword_id and c.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)) t, cr_item_keyword_map m where t.keyword_id = m.keyword_id and m.item_id = is_assigned__item_id); end if; if is_assigned__recurse = ''down'' then return count(*) > 0 where exists (select 1 from (select k2.keyword_id from cr_keywords k1, cr_keywords k2 where k1.keyword_id = is_assigned__keyword_id and k1.tree_sortkey between k2.tree_sortkey and tree_right(k2.tree_sortkey)) t, cr_item_keyword_map m where t.keyword_id = m.keyword_id and m.item_id = is_assigned__item_id); end if; -- Tried none, up and down - must be an invalid parameter raise EXCEPTION ''-20000: The recurse parameter to content_keyword.is_assigned should be \\\'none\\\', \\\'up\\\' or \\\'down\\\'''; 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 -- select -- table_name, id_column, level -- from -- acs_object_types -- where -- object_type <> ''acs_object'' -- and -- object_type <> ''content_revision'' -- start with -- object_type = refresh_view__content_type -- connect by -- object_type = prior supertype 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 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 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 loop; select table_name into v_table_name from acs_object_types where object_type = refresh_view__content_type; if length(v_table_name) > 25 then raise exception ''Table name cannot be longer than 25 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''; 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.*, 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.*, 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';