Index: openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql,v
diff -u -r1.8 -r1.9
--- openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql	24 Sep 2006 11:37:04 -0000	1.8
+++ openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql	5 Oct 2006 17:04:18 -0000	1.9
@@ -103,106 +103,6 @@
 procedure drop_type (
   content_type		in acs_object_types.object_type%TYPE,
   drop_children_p	in char default 'f',
-  drop_table_p		in char default 'f'
-) is
-
-
-  cursor attribute_cur is
-    select
-      attribute_name
-    from
-      acs_attributes
-    where
-      object_type = drop_type.content_type;
-
-  cursor child_type_cur is 
-    select 
-      object_type
-    from 
-      acs_object_types
-    where
-      supertype = drop_type.content_type;
-     
-  table_exists integer;
-  v_table_name varchar2(50);
-  is_subclassed_p char;
-
-begin
-
-
-  -- first we'll rid ourselves of any dependent child types, if any , along with their
-  -- own dependent grandchild types
-  select 
-    decode(count(*),0,'f','t') 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_children_p = 't' and is_subclassed_p = 't' then
-
-    for child_rec in child_type_cur loop
-      drop_type( 
-        content_type => child_rec.object_type,
-	drop_children_p => 't' );
-    end loop;
-
-  end if;
-
-  -- now drop all the attributes related to this type
-  for attr_row in attribute_cur loop
-    drop_attribute(
-     content_type => drop_type.content_type,
-      attribute_name => attr_row.attribute_name
-    );
-  end loop;
-
-  -- we'll remove the associated table if it exists
-  select 
-    decode(count(*),0,0,1) into table_exists 
-  from 
-    user_tables u, acs_object_types objet
-  where 
-    objet.object_type = drop_type.content_type and
-    u.table_name = upper(objet.table_name);
-
-  if table_exists = 1 and drop_table_p = 't' then
-    select 
-      table_name into v_table_name 
-    from 
-      acs_object_types 
-    where
-      object_type = drop_type.content_type;
-
-    -- drop the input/output views for the type
-    -- being dropped.
-    -- FIXME: does the trigger get dropped when the 
-    -- view is dropped?  This did not exist in the 4.2 release,
-    -- and it needs to be tested.
-
-       
-    execute immediate 'drop view ' || v_table_name || 'x';
-    execute immediate 'drop view ' || v_table_name || 'i';
-
-    execute immediate 'drop table ' || v_table_name;
-
-  end if;
-
-  acs_object_type.drop_type(
-    object_type   => drop_type.content_type
-  );
-
-end drop_type;
-
-procedure drop_type (
-  content_type		in acs_object_types.object_type%TYPE,
-  drop_children_p	in char default 'f',
   drop_table_p		in char default 'f',
   drop_objects_p		in char default 'f'
 ) is
@@ -311,12 +211,12 @@
 
   if drop_objects_p = 't' then
     for revision_row in revision_cur loop
-      content_revision.delete( 
+      content_revision.del( 
         revision_id => revision_row.revision_id
       );
     end loop;
     for item_row in item_cur loop
-      content_item.delete( 
+      content_item.del( 
         item_id => item_row.item_id
       );
     end loop;
@@ -325,7 +225,6 @@
   acs_object_type.drop_type(
     object_type   => drop_type.content_type
   );
-
 end drop_type;
 
 function create_attribute (
Index: openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql,v
diff -u -r1.25 -r1.26
--- openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql	4 Jun 2006 00:45:22 -0000	1.25
+++ openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql	5 Oct 2006 17:04:18 -0000	1.26
@@ -44,8 +44,8 @@
   --*/
   content_type		in acs_object_types.object_type%TYPE,
   drop_children_p	in char default 'f',
-  drop_table_p		in char default 'f'
-
+  drop_table_p		in char default 'f',
+  drop_objects_p		in char default 'f'
 );
 
 
Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.3.0d2-5.3.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.3.0d2-5.3.0d3.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.3.0d2-5.3.0d3.sql	24 Sep 2006 11:37:04 -0000	1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.3.0d2-5.3.0d3.sql	5 Oct 2006 17:04:19 -0000	1.2
@@ -1,11 +1,366 @@
+-- package specification
+
+create or replace package content_type AUTHID CURRENT_USER as
+--/** This package is used to manipulate content types and attributes
+--    
+--*/
+
+procedure create_type (
+  --/** Create a new content type. Automatically create the attribute table
+  --    for the type if the table does not already exist.
+  --    @author Karl Goldstein
+  --    @param content_type  The name of the new type
+  --    @param supertype     The supertype, defaults to content_revision
+  --    @param pretty_name   Pretty name for the type, singular
+  --    @param pretty_plural Pretty name for the type, plural
+  --    @param table_name    The name for the attribute table, defaults to
+  --                         the name of the supertype
+  --    @param id_column     The primary key for the table, defaults to 'XXX'
+  --    @param name_method   As in <tt>acs_object_type.create_type</tt>
+  --    @see {acs_object_type.create_type}
+  --*/
+  content_type		in acs_object_types.object_type%TYPE,
+  supertype		in acs_object_types.object_type%TYPE 
+                           default 'content_revision',
+  pretty_name		in acs_object_types.pretty_name%TYPE,
+  pretty_plural	        in acs_object_types.pretty_plural%TYPE,
+  table_name		in acs_object_types.table_name%TYPE default null,
+  id_column		in acs_object_types.id_column%TYPE default 'XXX',
+  name_method           in acs_object_types.name_method%TYPE default null
+);
+
 procedure drop_type (
+  --/** First drops all attributes related to a specific type, then drops type
+  --    the given type.
+  --    @author Simon Huynh
+  --    @param content_type  The content type to be dropped
+  --    @param drop_children_p If 't', then the sub-types
+  --    of the given content type and their associated tables
+  --    are also dropped.
+  --*/
   content_type		in acs_object_types.object_type%TYPE,
   drop_children_p	in char default 'f',
   drop_table_p		in char default 'f',
   drop_objects_p		in char default 'f'
+);
+
+
+function create_attribute (
+  --/** Create a new attribute for the specified type. Automatically create
+  --    the column for the attribute if the column does not already exist.
+  --    @author Karl Goldstein
+  --    @param content_type   The name of the type to alter
+  --    @param attribute_name The name of the attribute to create
+  --    @param pretty_name    Pretty name for the new attribute, singular
+  --    @param pretty_plural  Pretty name for the new attribute, plural
+  --    @param default_value  The default value for the attribute, defaults to null
+  --    @return The id of the newly created attribute
+  --    @see {acs_object_type.create_attribute}, {content_type.create_type}
+  --*/
+  content_type		in acs_attributes.object_type%TYPE,
+  attribute_name	in acs_attributes.attribute_name%TYPE,
+  datatype		in acs_attributes.datatype%TYPE,
+  pretty_name		in acs_attributes.pretty_name%TYPE,
+  pretty_plural	in acs_attributes.pretty_plural%TYPE default null,
+  sort_order		in acs_attributes.sort_order%TYPE default null,
+  default_value	in acs_attributes.default_value%TYPE default null,
+  column_spec           in varchar2  default 'varchar2(4000)'
+) return acs_attributes.attribute_id%TYPE;
+
+procedure drop_attribute (
+  --/** Drop an existing attribute. If you are using CMS, make sure to
+  --    call <tt>cm_form_widget.unregister_attribute_widget</tt> before calling
+  --    this function.
+  --    @author Karl Goldstein
+  --    @param content_type   The name of the type to alter
+  --    @param attribute_name The name of the attribute to drop
+  --    @param drop_column    If 't', will also alter the table and remove
+  --         the column where the attribute is stored. The default is 'f'
+  --         (leaves the table untouched).
+  --    @see {acs_object.drop_attribute}, {content_type.create_attribute},
+  --         {cm_form_widget.unregister_attribute_widget}
+  --*/
+  content_type		in acs_attributes.object_type%TYPE,
+  attribute_name	in acs_attributes.attribute_name%TYPE,
+  drop_column           in varchar2 default 'f'
+);
+
+procedure register_template (
+  --/** Register a template for the content type. This template may be used
+  --    to render all items of that type.
+  --    @author Karl Goldstein
+  --    @param content_type  The type for which the template is to be registered
+  --    @param template_id   The ID of the template to register
+  --    @param use_context   The context in which the template is appropriate, such
+  --                         as 'admin' or 'public'
+  --    @param is_default    If 't', this template becomes the default template for
+  --                         the type, default is 'f'.
+  --    @see {content_item.register_template}, {content_item.unregister_template}, 
+  --         {content_item.get_template}, {content_type.unregister_template},
+  --         {content_type.set_default_template}, {content_type.get_template}
+  --*/
+  content_type  in cr_type_template_map.content_type%TYPE,
+  template_id   in cr_templates.template_id%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE,
+  is_default    in cr_type_template_map.is_default%TYPE default 'f'
+);
+
+procedure set_default_template (
+  --/** Make the registered template a default template. The default template
+  --    will be used to render all items of the type for which no individual
+  --    template is registered.
+  --    @author Karl Goldstein
+  --    @param content_type  The type for which the template is to be made default
+  --    @param template_id   The ID of the template to make default
+  --    @param use_context   The context in which the template is appropriate, such
+  --                         as 'admin' or 'public'
+  --    @see {content_item.register_template}, {content_item.unregister_template}, 
+  --         {content_item.get_template}, {content_type.unregister_template},
+  --         {content_type.register_template}, {content_type.get_template}
+  --*/
+  content_type  in cr_type_template_map.content_type%TYPE,
+  template_id   in cr_templates.template_id%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE
+);
+
+function get_template (
+  --/** Retrieve the appropriate template for rendering items of the specified type.
+  --    @author Karl Goldstein
+  --    @param content_type  The type for which the template is to be retrieved
+  --    @param use_context   The context in which the template is appropriate, such
+  --                         as 'admin' or 'public'
+  --    @return The ID of the template to use
+  --    @see {content_item.register_template}, {content_item.unregister_template}, 
+  --         {content_item.get_template}, {content_type.unregister_template},
+  --         {content_type.register_template}, {content_type.set_default_template}
+  --*/
+  content_type  in cr_type_template_map.content_type%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE
+) return cr_templates.template_id%TYPE;
+
+procedure unregister_template (
+  --/** Unregister a template.  If the unregistered template was the default template,
+  --    the content_type can no longer be rendered in the use_context,
+  --    @author Karl Goldstein
+  --    @param content_type  The type for which the template is to be unregistered
+  --    @param template_id   The ID of the template to unregister
+  --    @param use_context   The context in which the template is to be unregistered
+  --    @see {content_item.register_template}, {content_item.unregister_template}, 
+  --         {content_item.get_template}, {content_type.set_default_template},
+  --         {content_type.register_template}, {content_type.get_template}
+  --*/
+  content_type  in cr_type_template_map.content_type%TYPE default null,
+  template_id   in cr_templates.template_id%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE default null
+);
+
+procedure refresh_view (
+  --/** Create a view for the type which joins all attributes of the type, 
+  --    including the inherited attributes.  The view is named 
+  --    "<table name for content_type>X"
+  --    Called by create_attribute and create_type.
+  --    @author Karl Goldstein
+  --    @param content_type  The type for which the view is to be created.
+  --    @see {content_type.create_type}
+  --*/
+  content_type  in cr_type_template_map.content_type%TYPE
+);
+
+procedure register_relation_type (
+  --/** Register a relationship between a content type and another object
+  --    type.  This may then be used by the content_item.is_valid_relation
+  --    function to validate any relationship between an item and another
+  --    object.
+  --    @author Karl Goldstein
+  --    @param content_type  The type of the item from which the relationship
+  --                          originated.
+  --    @param target_type   The type of the item to which the relationship
+  --                          is targeted.
+  --    @param relation_tag  A simple token used to identify a set of
+  --                          relations.
+  --    @param min_n          The minimun number of relationships of this type
+  --                          which an item must have to go live.
+  --    @param max_n          The minimun number of relationships of this type
+  --                          which an item must have to go live.
+  --    @see {content_type.unregister_relation_type}
+  --*/
+  content_type  in cr_type_relations.content_type%TYPE,
+  target_type   in cr_type_relations.target_type%TYPE,
+  relation_tag  in cr_type_relations.relation_tag%TYPE default 'generic',
+  min_n         in integer default 0,
+  max_n         in integer default null
+);
+
+procedure unregister_relation_type (
+  --/** Unregister a relationship between a content type and another object
+  --    type.
+  --    @author Karl Goldstein
+  --    @param content_type  The type of the item from which the relationship
+  --                          originated.
+  --    @param target_type   The type of the item to which the relationship
+  --                          is targeted.
+  --    @param relation_tag  A simple token used to identify a set of
+  --                          relations.
+  --    @see {content_type.register_relation_type}
+  --*/
+  content_type in cr_type_relations.content_type%TYPE,
+  target_type  in cr_type_relations.target_type%TYPE,
+  relation_tag in cr_type_relations.relation_tag%TYPE default null
+);
+
+procedure register_child_type (
+  --/** Register a parent-child relationship between a content type
+  --    and another object
+  --    type.  This may then be used by the content_item.is_valid_relation
+  --    function to validate the relationship between an item and a potential
+  --    child.
+  --    @author Karl Goldstein
+  --    @param content_type  The type of the item from which the relationship
+  --                          originated.
+  --    @param child_type    The type of the child item.
+  --    @param relation_tag  A simple token used to identify a set of
+  --                          relations.
+  --    @param min_n         The minimun number of parent-child
+  --                          relationships of this type
+  --                          which an item must have to go live.
+  --    @param max_n         The minimun number of relationships of this type
+  --                          which an item must have to go live.
+  --    @see {content_type.register_relation_type}, {content_type.register_child_type}
+  --*/
+  parent_type  in cr_type_children.parent_type%TYPE,
+  child_type    in cr_type_children.child_type%TYPE,
+  relation_tag  in cr_type_children.relation_tag%TYPE default 'generic',
+  min_n         in integer default 0,
+  max_n         in integer default null
+);
+
+procedure unregister_child_type (
+  --/** Register a parent-child relationship between a content type
+  --    and another object
+  --    type.  This may then be used by the content_item.is_valid_relation
+  --    function to validate the relationship between an item and a potential
+  --    child.
+  --    @author Karl Goldstein
+  --    @param parent_type   The type of the parent item.
+  --    @param child_type    The type of the child item.
+  --    @param relation_tag  A simple token used to identify a set of
+  --                          relations.
+  --    @see {content_type.register_relation_type}, {content_type.register_child_type}
+  --*/
+  parent_type  in cr_type_children.parent_type%TYPE,
+  child_type   in cr_type_children.child_type%TYPE,
+  relation_tag in cr_type_children.relation_tag%TYPE default null
+);
+
+procedure register_mime_type (
+  content_type  in cr_content_mime_type_map.content_type%TYPE,
+  mime_type	in cr_content_mime_type_map.mime_type%TYPE
+);
+
+procedure unregister_mime_type (
+  content_type  in cr_content_mime_type_map.content_type%TYPE,
+  mime_type	in cr_content_mime_type_map.mime_type%TYPE
+);
+
+function is_content_type (
+  object_type   in acs_object_types.object_type%TYPE
+) return char;
+
+procedure rotate_template (
+  --/** Sets the default template for a content type and registers all the
+  --    previously existing items of that content type to the original 
+  --    template
+  --    @author Michael Pih
+  --    @param template_id The template that will become the default 
+  --      registered template for the specified content type and use context
+  --    @param v_content_type The content type
+  --    @param use_context The context in which the template will be used
+  --*/
+  template_id     in cr_templates.template_id%TYPE,
+  v_content_type    in cr_items.content_type%TYPE,
+  use_context     in cr_type_template_map.use_context%TYPE
+);
+
+-- Create or replace a trigger on insert for simplifying addition of
+-- revisions for any content type
+
+procedure refresh_trigger (
+  content_type  in acs_object_types.object_type%TYPE
+);
+
+end content_type;
+/
+show errors;
+
+-- package body
+
+create or replace package body content_type is
+
+procedure create_type (
+    content_type	in acs_object_types.object_type%TYPE,
+    supertype		in acs_object_types.object_type%TYPE 
+                           default 'content_revision',
+    pretty_name		in acs_object_types.pretty_name%TYPE,
+    pretty_plural	in acs_object_types.pretty_plural%TYPE,
+    table_name		in acs_object_types.table_name%TYPE default null,
+    id_column		in acs_object_types.id_column%TYPE default 'XXX',
+    name_method           in acs_object_types.name_method%TYPE default null
 ) is
 
+  table_exists integer;
+  v_supertype_table acs_object_types.table_name%TYPE;
+  v_count       integer;
+begin
 
+  if (supertype != 'content_revision') and (content_type != 'content_revision') then
+    select count(*)
+    into  v_count
+    from  acs_object_type_supertype_map
+    where object_type = create_type.supertype
+    and   ancestor_type = 'content_revision';
+
+    if v_count = 0 then
+        raise_application_error(-20000, 'Content types can only be created as subclasses of content_revision or a derivation thereof. ' || supertype || ' is not a subclass oc content_revision.');
+    end if;
+  end if;
+
+
+ -- create the attribute table if not already created
+
+  select decode(count(*),0,0,1) into table_exists from user_tables 
+    where table_name = upper(create_type.table_name);
+
+  if table_exists = 0 then
+    select table_name into v_supertype_table from acs_object_types
+      where object_type = create_type.supertype;
+
+    execute immediate 'create table ' || table_name || ' (' ||
+      id_column  || ' integer primary key references ' || 
+      v_supertype_table || ')';
+  end if;
+
+  acs_object_type.create_type (
+    supertype     => create_type.supertype,
+    object_type   => create_type.content_type,
+    pretty_name   => create_type.pretty_name,
+    pretty_plural => create_type.pretty_plural,
+    table_name    => create_type.table_name,
+    id_column     => create_type.id_column,
+    name_method   => create_type.name_method
+  );
+
+  refresh_view(content_type);
+
+end create_type;
+
+procedure drop_type (
+  content_type		in acs_object_types.object_type%TYPE,
+  drop_children_p	in char default 'f',
+  drop_table_p		in char default 'f',
+  drop_objects_p		in char default 'f'
+) is
+
+
   cursor attribute_cur is
     select
       attribute_name
@@ -109,12 +464,12 @@
 
   if drop_objects_p = 't' then
     for revision_row in revision_cur loop
-      content_revision.delete( 
+      content_revision.del( 
         revision_id => revision_row.revision_id
       );
     end loop;
     for item_row in item_cur loop
-      content_item.delete( 
+      content_item.del( 
         item_id => item_row.item_id
       );
     end loop;
@@ -123,5 +478,746 @@
   acs_object_type.drop_type(
     object_type   => drop_type.content_type
   );
-
 end drop_type;
+
+function create_attribute (
+  content_type		in acs_attributes.object_type%TYPE,
+  attribute_name	in acs_attributes.attribute_name%TYPE,
+  datatype		in acs_attributes.datatype%TYPE,
+  pretty_name		in acs_attributes.pretty_name%TYPE,
+  pretty_plural		in acs_attributes.pretty_plural%TYPE default null,
+  sort_order		in acs_attributes.sort_order%TYPE default null,
+  default_value		in acs_attributes.default_value%TYPE default null,
+  column_spec           in varchar2 default 'varchar2(4000)'
+) return acs_attributes.attribute_id%TYPE is
+
+   v_attr_id	acs_attributes.attribute_id%TYPE;
+   v_table_name acs_object_types.table_name%TYPE;
+   v_column_exists integer;
+
+begin
+
+ -- add the appropriate column to the table
+ begin
+   select upper(table_name) into v_table_name from acs_object_types
+     where object_type = create_attribute.content_type;
+ exception when no_data_found then
+   raise_application_error(-20000, 'Content type ''' || content_type || 
+      ''' does not exist in content_type.create_attribute');
+ end;
+
+ select decode(count(*),0,0,1) into v_column_exists from user_tab_columns
+   where table_name = v_table_name
+   and column_name = upper(attribute_name);
+
+ if v_column_exists = 0 then
+   execute immediate 'alter table ' || v_table_name || ' add ' || 
+      attribute_name || ' ' || column_spec;
+ end if;
+
+ v_attr_id := acs_attribute.create_attribute (
+   object_type => create_attribute.content_type,
+   attribute_name => create_attribute.attribute_name,
+   datatype => create_attribute.datatype,
+   pretty_name => create_attribute.pretty_name,
+   pretty_plural => create_attribute.pretty_plural,
+   sort_order => create_attribute.sort_order,
+   default_value => create_attribute.default_value
+ );
+
+ refresh_view(content_type);
+
+ return v_attr_id;
+
+end create_attribute;
+
+
+procedure drop_attribute (
+  content_type		in acs_attributes.object_type%TYPE,
+  attribute_name	in acs_attributes.attribute_name%TYPE,
+  drop_column           in varchar2 default 'f'
+)
+is
+   v_attr_id acs_attributes.attribute_id%TYPE;
+   v_table   acs_object_types.table_name%TYPE;
+begin
+
+  -- Get attribute information 
+  begin
+    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;
+  exception when no_data_found then
+    raise_application_error(-20000, 'Attribute ' || content_type || ':' || 
+       attribute_name || ' does not exist in content_type.drop_attribute');
+  end;
+
+  -- Drop the attribute
+  acs_attribute.drop_attribute(content_type, attribute_name);
+
+  -- Drop the column if neccessary
+  if drop_column = 't' then
+    begin
+      execute immediate 'alter table ' || v_table || ' drop column ' ||
+	attribute_name;
+    exception when others then
+      raise_application_error(-20000, 'Unable to drop column ' || 
+       v_table || '.' || attribute_name || ' in content_type.drop_attribute');  
+    end;
+  end if;  
+
+  refresh_view(content_type);
+
+end drop_attribute;
+
+procedure register_template (
+  content_type  in cr_type_template_map.content_type%TYPE,
+  template_id   in cr_templates.template_id%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE,
+  is_default	in cr_type_template_map.is_default%TYPE default 'f'
+) is
+  v_template_registered integer;
+begin
+  select 
+    count(*) 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 v_template_registered = 0 then
+    insert into cr_type_template_map (
+      template_id, content_type, use_context, is_default
+    ) values (
+      template_id, content_type, use_context, 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 = 't' 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;
+end register_template;
+
+
+procedure set_default_template (
+  content_type  in cr_type_template_map.content_type%TYPE,
+  template_id   in cr_templates.template_id%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE
+) is
+
+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';
+
+end set_default_template;
+
+function get_template (
+  content_type  in cr_type_template_map.content_type%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE
+) return cr_templates.template_id%TYPE
+is
+  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;
+
+exception
+  when NO_DATA_FOUND then 
+    return null;
+end get_template;
+
+procedure unregister_template (
+  content_type  in cr_type_template_map.content_type%TYPE default null,
+  template_id   in cr_templates.template_id%TYPE,
+  use_context   in cr_type_template_map.use_context%TYPE default null
+) is
+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;
+
+  elsif 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;
+
+  elsif 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 unregister_template;
+
+-- Helper function for refresh_trigger (below) to generate the
+-- insert statement for a particular content type;
+
+function trigger_insert_statement (
+  content_type  in acs_object_types.object_type%TYPE
+) return varchar2 is
+
+  v_table_name acs_object_types.table_name%TYPE;
+  v_id_column acs_object_types.id_column%TYPE;
+
+  cursor attr_cur is
+    select
+      attribute_name
+    from
+      acs_attributes
+    where
+      object_type = trigger_insert_statement.content_type;
+
+  cols varchar2(2000) := '';
+  vals varchar2(2000) := '';
+
+begin
+
+  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 attr_cur loop
+    cols := cols || ', ' || attr_rec.attribute_name;
+    vals := vals || ', :new.' || attr_rec.attribute_name;
+  end loop;
+
+  return 'insert into ' || v_table_name || 
+    ' ( ' || v_id_column || cols || ' ) values ( new_revision_id' ||
+    vals || ')';
+  
+end trigger_insert_statement;
+
+-- Create or replace a trigger on insert for simplifying addition of
+-- revisions for any content type
+
+procedure refresh_trigger (
+  content_type  in acs_object_types.object_type%TYPE
+) is
+
+  tr_text varchar2(10000) := '';
+  v_table_name acs_object_types.table_name%TYPE;
+
+  cursor type_cur is
+    select                                                
+      object_type
+    from                                                
+      acs_object_types                                  
+    where                                               
+      object_type ^= 'acs_object'                       
+    and                                                 
+      object_type ^= 'content_revision'                 
+    connect by                                          
+      prior supertype = object_type                     
+    start with                                          
+      object_type = refresh_trigger.content_type
+    order by
+      level desc;
+
+begin
+
+  -- get the table name for the content type (determines view name)
+
+  select table_name into v_table_name
+  from acs_object_types where object_type = refresh_trigger.content_type;
+
+  -- start building trigger code
+
+  tr_text := '
+
+create or replace trigger ' || v_table_name || 't 
+  instead of insert on ' || v_table_name || 'i 
+  for each row 
+declare
+  new_revision_id integer;
+begin
+
+  if :new.item_id is null then
+    raise_application_error(-20000, ''item_id is required when inserting into ' || 
+    v_table_name || 'i '');
+  end if;
+
+  if :new.text is not null then
+
+    new_revision_id := content_revision.new(
+                   revision_id   => :new.revision_id,
+                   title         => :new.title,
+                   description   => :new.description,
+                   mime_type     => :new.mime_type,
+                   nls_language  => :new.nls_language,
+                   item_id       => content_symlink.resolve(:new.item_id),
+                   creation_ip   => :new.creation_ip,
+                   creation_user => :new.creation_user, 
+                   text          => :new.text,
+                   package_id    => :new.object_package_id
+    );
+
+  else
+
+    new_revision_id := content_revision.new(
+                   revision_id   => :new.revision_id,
+                   title         => :new.title,
+                   description   => :new.description,
+                   mime_type     => :new.mime_type,
+                   nls_language  => :new.nls_language,
+                   item_id       => content_symlink.resolve(:new.item_id),
+                   creation_ip   => :new.creation_ip,
+                   creation_user => :new.creation_user, 
+                   data          => :new.data,
+                   package_id    => :new.object_package_id
+    );
+
+  end if;';
+
+  -- add an insert statement for each subtype in the hierarchy for this type
+
+  for type_rec in type_cur loop
+    tr_text := tr_text || '
+' || trigger_insert_statement(type_rec.object_type) || ';
+';
+
+  end loop;
+
+  -- end building the trigger code
+  tr_text := tr_text || '
+end ' || v_table_name || 't;';
+
+  -- (Re)create the trigger
+  execute immediate tr_text;
+
+end refresh_trigger;
+
+-- Create or replace a view joining all attribute tables
+
+procedure refresh_view (
+  content_type  in cr_type_template_map.content_type%TYPE
+) is
+
+  -- exclude the BLOB column because it will make it impossible
+  -- to do a select *
+
+  cursor join_cur is
+    select
+      distinct lower(table_name) as table_name,
+      id_column, level
+    from
+      acs_object_types
+    where
+      object_type <> 'acs_object'
+    and
+      object_type <> 'content_revision'
+    and lower(table_name) <> 'acs_objects'
+    and lower(table_name) <> 'cr_revisions'
+    start with
+      object_type = refresh_view.content_type
+    connect by
+      object_type = prior supertype;
+
+  cols varchar2(1000);
+  tabs varchar2(1000);
+  joins varchar2(1000) := '';
+ 
+  v_table_name varchar2(40);
+
+begin
+
+  for join_rec in join_cur 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 = content_type;
+
+  -- create the input view (includes content columns)
+
+  execute immediate 'create or replace 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,
+    cr.content as data, cr_text.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 *)
+
+  execute immediate 'create or replace 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;
+
+  refresh_trigger(content_type);
+
+exception
+  when others then
+    dbms_output.put_line('Error creating attribute view or trigger for ' ||
+                         content_type);
+end refresh_view;
+
+procedure register_child_type (
+  parent_type  in cr_type_children.parent_type%TYPE,
+  child_type    in cr_type_children.child_type%TYPE,
+  relation_tag  in cr_type_children.relation_tag%TYPE default 'generic',
+  min_n         in integer default 0,
+  max_n         in integer default null
+) is
+
+  v_exists integer;
+
+begin
+
+  select decode(count(*),0,0,1) 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 (
+      parent_type, child_type, relation_tag, min_n, 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;
+      
+end register_child_type;
+
+procedure unregister_child_type (
+  parent_type  in cr_type_children.parent_type%TYPE,
+  child_type   in cr_type_children.child_type%TYPE,
+  relation_tag in cr_type_children.relation_tag%TYPE default null
+) is
+
+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;
+
+end unregister_child_type;
+
+procedure register_relation_type (
+  content_type  in cr_type_relations.content_type%TYPE,
+  target_type   in cr_type_relations.target_type%TYPE,
+  relation_tag  in cr_type_relations.relation_tag%TYPE default 'generic',
+  min_n         in integer default 0,
+  max_n         in integer default null
+) is
+  v_exists integer;
+begin
+
+  -- check if the relation type exists
+  select 
+    decode(count(*),0,0,1) 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 (
+      content_type, target_type, relation_tag, min_n, 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;
+end register_relation_type;
+
+procedure unregister_relation_type (
+  content_type in cr_type_relations.content_type%TYPE,
+  target_type   in cr_type_relations.target_type%TYPE,
+  relation_tag in cr_type_relations.relation_tag%TYPE default null
+) is
+
+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;
+
+end unregister_relation_type;
+
+procedure register_mime_type (
+  content_type  in cr_content_mime_type_map.content_type%TYPE,
+  mime_type	in cr_content_mime_type_map.mime_type%TYPE
+) is 
+  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;
+
+end register_mime_type;
+
+
+procedure unregister_mime_type (
+  content_type  in cr_content_mime_type_map.content_type%TYPE,
+  mime_type	in cr_content_mime_type_map.mime_type%TYPE
+) is
+begin
+
+  delete from cr_content_mime_type_map
+    where content_type = unregister_mime_type.content_type
+    and mime_type = unregister_mime_type.mime_type;
+
+end unregister_mime_type;
+
+function is_content_type (
+  object_type   in acs_object_types.object_type%TYPE
+) return char is
+
+  v_is_content_type char(1) := 'f';
+
+begin
+
+  if object_type = 'content_revision' then
+
+    v_is_content_type := 't';
+
+  else
+    
+    select decode(count(*),0,'f','t') 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 is_content_type;
+
+
+
+procedure rotate_template ( 
+  template_id       in cr_templates.template_id%TYPE,
+  v_content_type    in cr_items.content_type%TYPE,
+  use_context       in cr_type_template_map.use_context%TYPE
+) is
+  v_template_id cr_templates.template_id%TYPE;
+
+  -- items that have an associated default template but not at the item level
+  cursor c_items_cursor is
+    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 );
+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 c_items_cursor loop
+
+      content_item.register_template ( 
+         item_id     => v_items_val.item_id, 
+         template_id => v_template_id,
+         use_context => 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
+    content_type.register_template(
+        content_type => rotate_template.v_content_type,
+        template_id  => rotate_template.template_id,
+        use_context  => rotate_template.use_context,
+        is_default   => 't'
+    );
+  end if;
+
+end rotate_template;
+
+
+end content_type;
+/
+show errors