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';