Index: openacs-4/packages/acs-content-repository/acs-content-repository.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v diff -u -r1.89 -r1.90 --- openacs-4/packages/acs-content-repository/acs-content-repository.info 12 Oct 2009 22:40:44 -0000 1.89 +++ openacs-4/packages/acs-content-repository/acs-content-repository.info 27 Jan 2010 17:34:09 -0000 1.90 @@ -7,7 +7,7 @@ t t - + OpenACS The canonical repository for OpenACS content. 2009-06-19 @@ -20,7 +20,7 @@ GPL 3 - + Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql,v diff -u -r1.53 -r1.54 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 12 Oct 2009 22:42:34 -0000 1.53 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 27 Jan 2010 17:34:09 -0000 1.54 @@ -313,8 +313,8 @@ execute ''drop rule '' || v_table_name || ''_r '' || ''on '' || v_table_name || ''i''; end if; - execute ''drop view '' || v_table_name || ''x''; - execute ''drop view '' || 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; @@ -817,7 +817,7 @@ -- 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''; + execute ''drop view '' || v_table_name || ''x cascade''; end if; execute ''create view '' || v_table_name || Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.6.0d3-5.6.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.6.0d3-5.6.0d4.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.6.0d3-5.6.0d4.sql 27 Jan 2010 17:34:09 -0000 1.1 @@ -0,0 +1,337 @@ +-- +-- +-- +-- @author Dave Bauer (dave@thedesignexperience.org) +-- @creation-date 2010-01-27 +-- @cvs-id $Id: +-- + +create or replace function content_type__drop_type (varchar,boolean,boolean,boolean) +returns integer as ' +declare + drop_type__content_type alias for $1; + drop_type__drop_children_p alias for $2; -- default ''f'' + drop_type__drop_table_p alias for $3; -- default ''f'' + drop_type__drop_objects_p alias for $4; -- default ''f'' + 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 +-- select define_function_args('content_type__drop_type','content_type,drop_children_p;f,drop_table_p;f'); + +create or replace function content_type__drop_type (varchar,boolean,boolean) +returns integer as ' +declare + drop_type__content_type alias for $1; + drop_type__drop_children_p alias for $2; -- default ''f'' + drop_type__drop_table_p alias for $3; -- default ''f'' + 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. + + -- 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; + + 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'; + + +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 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, + 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';