Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql,v
diff -u -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql	3 Dec 2003 18:45:39 -0000	1.1.2.1
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.0d2-5.0d3.sql	6 Dec 2003 02:48:27 -0000	1.1.2.2
@@ -92,3 +92,105 @@
  
 end;' language 'plpgsql';
 
+--
+-- fix setting of context_id to new item id
+
+create or replace function content_revision__copy (integer,integer,integer,integer,varchar)
+returns integer as '
+declare
+  copy__revision_id            alias for $1;  
+  copy__copy_id                alias for $2;  -- default null  
+  copy__target_item_id         alias for $3;  -- default null
+  copy__creation_user          alias for $4;  -- default null
+  copy__creation_ip            alias for $5;  -- default null
+  v_copy_id                    cr_revisions.revision_id%TYPE;
+  v_target_item_id             cr_items.item_id%TYPE;
+  type_rec                     record;
+begin
+  -- use the specified item_id or the item_id of the original revision 
+  --   if none is specified
+  if copy__target_item_id is null then
+    select item_id into v_target_item_id from cr_revisions 
+      where revision_id = copy__revision_id;
+  else
+    v_target_item_id := copy__target_item_id;
+  end if;
+
+  -- use the copy_id or generate a new copy_id if none is specified
+  --   the copy_id is a revision_id
+  if copy__copy_id is null then
+    select acs_object_id_seq.nextval into v_copy_id from dual;
+  else
+    v_copy_id := copy__copy_id;
+  end if;
+
+  -- create the basic object
+  insert into acs_objects 
+       select 
+         v_copy_id as object_id, 
+         object_type, 
+         v_target_item_id, 
+         security_inherit_p, 
+         copy__creation_user as creation_user, 
+         now() as creation_date, 
+         copy__creation_ip as creation_ip,
+         now() as last_modified, 
+         copy__creation_user as modifying_user, 
+         copy__creation_ip as modifying_ip 
+       from
+         acs_objects 
+       where 
+         object_id = copy__revision_id;
+  
+  -- create the basic revision (using v_target_item_id)
+  insert into cr_revisions 
+      select 
+        v_copy_id as revision_id, 
+        v_target_item_id as item_id, 
+        title, 
+        description, 
+        publish_date, 
+        mime_type, 
+        nls_language, 
+        lob,
+	content,
+        content_length
+      from 
+        cr_revisions 
+      where
+        revision_id = copy__revision_id;
+
+--                  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 = (select object_type 
+--                                     from acs_objects 
+--                                    where object_id = copy__revision_id)
+--                  order by
+--                    level desc 
+
+  -- iterate over the ancestor types and copy attributes
+  for type_rec in select ot2.object_type, tree_level(ot2.tree_sortkey) as level
+                  from acs_object_types ot1, acs_object_types ot2, acs_objects o
+                  where ot2.object_type <> ''acs_object''                       
+                    and ot2.object_type <> ''content_revision''
+                    and o.object_id = copy__revision_id 
+                    and ot1.object_type = o.object_type 
+                    and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
+                  order by level desc
+  LOOP
+    PERFORM content_revision__copy_attributes(type_rec.object_type, 
+                                              copy__revision_id, v_copy_id);
+  end loop;
+
+  return v_copy_id;
+ 
+end;' language 'plpgsql';
