Rem
Rem $Header: rdbms/admin/xdbeu111.sql /main/4 2009/05/22 13:56:33 spetride Exp $
Rem
Rem xdbeu111.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      xdbeu111.sql - XDB User data downgrade
Rem
Rem    DESCRIPTION
Rem      This script downgrades XDB User Data to 11.1.0
Rem
Rem    NOTES
Rem      It is invoked from the top-level XDB downgrade script (xdbe111.sql)
Rem      and from the 10.2 data downgrade script (xdbeu102.sql)
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    spetride    02/16/09 - remove all Expire mappings in xdbconfig
Rem    atabar      02/06/09 - xdbconfig default-type-mappings downgrade
Rem    spetride    08/07/08 - remove allow-mechanism:custom and allow-authentication-trust
Rem                         - downgrade for app users and roles
Rem    rburns      11/06/07 - 11.1 data downgrade
Rem    rburns      11/06/07 - Created
Rem

Rem ================================================================
Rem BEGIN XDB Data downgrade to 11.2.0
Rem ================================================================

-- uncomment for next release
--@@xdbeu112.sql

Rem ================================================================
Rem END XDB Data downgrade to 11.2.0
Rem ================================================================

Rem ================================================================
Rem BEGIN XDB Data downgrade to 11.1.0
Rem ================================================================


declare
  new_cfg         XMLTYPE;      
  cust_count      NUMBER := 0; 
begin
  select existsNode(dbms_xdb.cfg_get(),
    '/xdbconfig/sysconfig/protocolconfig/httpconfig/authentication/allow-mechanism[text()="custom"]',
    'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') 
  into cust_count from dual;

  if cust_count > 0  then
    select deletexml(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/authentication/allow-mechanism[text()="custom"]',
                     'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    into new_cfg from dual;

    dbms_xdb.cfg_update(new_cfg);  
    commit; 
  end if;
end;
/


declare
  new_cfg         XMLTYPE;      
  cust_count      NUMBER := 0; 
begin
  select existsNode(dbms_xdb.cfg_get(),
    '/xdbconfig/sysconfig/allow-authentication-trust',
    'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') 
  into cust_count from dual;

  if cust_count > 0  then
    select deletexml(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/allow-authentication-trust',
                     'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    into new_cfg from dual;

    dbms_xdb.cfg_update(new_cfg);  
    commit; 
  end if;
end;
/


-- downgrade for Application user and roles support
declare
  stmt    varchar2(4000);
  cnt     number := 0;
begin
  stmt := 'select count(*) from dba_tables where (owner = ''' || 'XDB' ||
          ''') and (table_name = ''' || 'APP_USERS_AND_ROLES' || ''') '; 
  execute immediate stmt into cnt;
  if (cnt > 0) then
    execute immediate 'drop table XDB.APP_USERS_AND_ROLES';
  end if;
  stmt := 'select count(*) from dba_tables where (owner = ''' || 'XDB' ||
          ''') and (table_name = ''' || 'APP_ROLE_MEMBERSHIP' || ''') '; 
  execute immediate stmt into cnt;
  if (cnt > 0) then
    execute immediate 'drop table XDB.APP_ROLE_MEMBERSHIP';
  end if;
end;
/
-- downgrade /xdbconfig/sysconfig/default-type-mappings
declare
  new_cfg         XMLTYPE;      
  node_count      NUMBER; 
begin
  select existsNode(dbms_xdb.cfg_get(),
    '/xdbconfig/sysconfig/default-type-mappings',
    'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') 
  into node_count from dual;

  if node_count > 0  then
    select deletexml(dbms_xdb.cfg_get(), 
                     '/xdbconfig/sysconfig/default-type-mappings',
                     'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    into new_cfg from dual;

    dbms_xdb.cfg_update(new_cfg);  
    commit; 
  end if;
end;
/

declare
  cexists  NUMBER := 0;
  newcfg   XMLType;
  path     VARCHAR2(1000);
begin
  select existsNode(dbms_xdb.cfg_get(), '/xdbconfig/sysconfig/protocolconfig/httpconfig/expire',
   'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') into cexists from dual;
  if (cexists > 0) then
    path := '/xdbconfig/sysconfig/protocolconfig/httpconfig/expire';
    select deletexml(dbms_xdb.cfg_get(), path,
                      'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    into newcfg from dual;

    dbms_xdb.cfg_update(newcfg);      

    commit;
  end if;  
end;
/
Rem ================================================================
Rem END XDB Data downgrade to 11.1.0
Rem ================================================================