--
-- Copyright (C) 2002-2004 Red Hat Inc. All Rights Reserved.
--
-- The contents of this file are subject to the CCM Public
-- License (the "License"); you may not use this file except in
-- compliance with the License. You may obtain a copy of the
-- License at http://www.redhat.com/licenses/ccmpl.html.
--
-- Software distributed under the License is distributed on an
-- "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
-- or implied. See the License for the specific language governing
-- rights and limitations under the License.
--
-- $Id: //cms/dev/sql/ccm-cms/oracle-se/upgrade/5.1.0-5.2.0/denormalize-cms_items.sql#3 $
-- $DateTime: 2004/04/07 16:07:11 $

alter table cms_items add (ancestors varchar(4000));

create or replace procedure updateCMSItemsHelpTEMP(v_item_id INTEGER)
as 
   cursor parents is 
             select item_id
               from cms_items
                    start with item_id = v_item_id
                    connect by item_id = prior parent_id;
   v_parent_id integer;
begin
   update cms_items set ancestors = item_id || '/'
          where item_id = v_item_id;
   open parents;
   loop
      FETCH parents INTO v_parent_id;
      EXIT WHEN parents%NOTFOUND;
      
      if (v_parent_id != v_item_id) then
         update cms_items set ancestors = v_parent_id || '/' || ancestors where item_id = v_item_id;
      end if;
   end loop;
end;
/
show errors

create or replace procedure updateCMSItemsTEMP
as 
   cursor items is select item_id from cms_items;
   v_item_id integer;
begin
   open items;
   loop
      FETCH items INTO v_item_id;
      EXIT WHEN items%NOTFOUND;
      updateCMSItemsHelpTEMP(v_item_id);
   end loop;
end;
/
show errors

begin
  updateCMSItemsTEMP;
end;
/

drop procedure updateCMSItemsTEMP;
drop procedure updateCMSItemsHelpTEMP;

alter table cms_items modify (ancestors not null);
