Rem
Rem $Header: rdbms/admin/xdbu111.sql /st_rdbms_11.2.0/2 2011/06/07 12:30:50 juding Exp $
Rem
Rem xdbu111.sql
Rem
Rem Copyright (c) 2007, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      xdbu111.sql - XDB Upgrade from 11.1.0
Rem
Rem    DESCRIPTION
Rem      This script performs upgrade actions to upgrade from 11.1.0
Rem      to the current release
Rem
Rem    NOTES
Rem      It is invoked by xdbdbmig.sql and by xdbu102.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    juding      05/26/11 - Backport badeoti_bug-10168805 from main
Rem    bhammers    06/07/10 - bug 9766227, remove 'grant option'
Rem    badeoti     04/22/10 - lrg 4337840, dbms_xmlschema grant option
Rem    thbaby      03/01/10 - add column grppos to xdb.xdb$xidx_imp_t
Rem    badeoti     12/21/09 - set acl/config table access privs
Rem    bhammers    11/10/09 - 8760324, set 'Unstructured Present' flag
Rem                           when upgrading XIDX from 11.1.0.7 to 11.2.0.2
Rem    bhammers    02/12/09 - add indexes to xdb.xdb$element
Rem    spetride    08/07/08 - upgrade for Aplication users and roles
Rem    achoi       11/13/08 - lrg3678340: move xdb_pi_trig to SYS
Rem    sidicula    09/25/08 - 
Rem    sichandr    09/23/08 - create XDB$REPOS tables
Rem    rburns      09/30/07 - add 11.1 schema upgrade
Rem    spetride    10/04/07 - create index XDB.XDB$RESOURCE_ACLOID_IDX 
Rem    rburns      08/22/07 - add 11g XDB up/down scripts
Rem    rburns      08/22/07 - Created
Rem

-- temporarily run "s" script here; move to xdbdbmig with re-org
@@xdbs111.sql

/*-----------------------------------------------------------------------*/
/*  Upgrade XMLIndex type */
/*-----------------------------------------------------------------------*/
drop table XDB.XDB$XIDX_IMP_T;
create global temporary table XDB.XDB$XIDX_IMP_T
                                (index_name VARCHAR2(40), 
                                 schema_name VARCHAR2(40),
                                 id VARCHAR2(40), 
                                 data CLOB,
                                 grppos NUMBER)
       on commit preserve rows;
 grant insert, select, delete on XDB.XDB$XIDX_IMP_T to public;

-- create ximetadata_pkg package
CREATE OR REPLACE PACKAGE XDB.ximetadata_pkg AS 
  FUNCTION getIndexMetadata (idxinfo  IN  sys.ODCIIndexInfo,
                           expver   IN  VARCHAR2,
                           newblock OUT number,
                           idxenv   IN  sys.ODCIEnv) return VARCHAR2;
  FUNCTION getIndexMetadataCallback (idxinfo  IN  sys.ODCIIndexInfo,
                                expver   IN  VARCHAR2,
                                newblock OUT number,
                                idxenv   IN  sys.ODCIEnv) return VARCHAR2;
  FUNCTION utlgettablenames(idxinfo  IN  sys.ODCIIndexInfo) return BOOLEAN;

 END ximetadata_pkg;
/

show errors;

CREATE OR REPLACE PACKAGE BODY XDB.ximetadata_pkg AS  
-- 'iterate' is a package level variable used to maintain state across calls 
-- by export in this session. 
 
iterate NUMBER := 0; 

FUNCTION getIndexMetadata (idxinfo  IN  sys.ODCIIndexInfo,
                           expver   IN  VARCHAR2,
                           newblock OUT number,
                           idxenv   IN  sys.ODCIEnv) return VARCHAR2 IS 
 
BEGIN 
 
-- We are generating only one PL/SQL block consisting of one line of code. 
  newblock := 1; 
 
  IF iterate = 0 
  THEN 
-- Increment iterate so we'll know we're done next time we're called. 
    iterate := iterate + 1; 
 
    RETURN getIndexMetadataCallback (idxinfo, expver, newblock, idxenv);
                              
  ELSE 
-- reset iterate for next index 
    iterate := 0; 
-- Return a 0-length string; we won't be called again for this index. 
    RETURN ''; 
  END IF; 
END getIndexMetadata; 

 function getIndexMetadataCallback (idxinfo  IN  sys.ODCIIndexInfo,
                                    expver   IN  VARCHAR2,
                                    newblock OUT number,
                                    idxenv   IN  sys.ODCIEnv)
         return VARCHAR2
  is language C name "QMIX_XMETADATA" library XDB.XMLINDEX_LIB
     with context
     parameters (
       context,
       idxinfo, idxinfo  INDICATOR struct,
       expver,  expver   INDICATOR,
       newblock,newblock INDICATOR,
       idxenv,  idxenv   INDICATOR struct,
       RETURN OCIString);

 FUNCTION utlgettablenames(idxinfo  IN  sys.ODCIIndexInfo) return BOOLEAN
 is language C name "QMIX_TABLEUTILS" library  XDB.XMLINDEX_LIB
      with context
      parameters (
        context,
        idxinfo, idxinfo  INDICATOR struct,
        RETURN            INDICATOR sb4,
        return);

END ximetadata_pkg; 
/

show errors;

grant execute on XDB.ximetadata_pkg to public;
/  
create or replace public synonym ximetadata_pkg for XDB.ximetadata_pkg;
/

-- alter type in xdb.XMLIndexMethods
-- first drop the old definition
 ALTER TYPE xdb.XMLIndexMethods DROP  static function ODCIIndexGetMetadata(idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, len_newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2;

-- create new definition
 ALTER TYPE xdb.XMLIndexMethods ADD  static function ODCIIndexGetMetadata(idxinfo IN sys.ODCIIndexInfo, expver IN VARCHAR2, newblock OUT number, idxenv IN sys.ODCIEnv) return VARCHAR2;

ALTER TYPE xdb.XMLIndexMethods ADD static function ODCIIndexUtilGetTableNames(ia IN sys.ODCIIndexInfo, read_only IN PLS_INTEGER, version IN varchar2, context OUT PLS_INTEGER) return BOOLEAN;

ALTER TYPE xdb.XMLIndexMethods ADD static procedure ODCIIndexUtilCleanup (context  IN PLS_INTEGER);

-- create body
create or replace type body xdb.XMLIndexMethods
is 
  static function ODCIGetInterfaces(ilist OUT sys.ODCIObjectList) 
    return number is 
  begin 
    ilist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
    return ODCICONST.SUCCESS;
  end ODCIGetInterfaces;

  static function ODCIIndexUpdPartMetadata(ixdxinfo sys.ODCIIndexInfo,
                                           palist   sys.ODCIPartInfoList,
                                           idxenv   sys.ODCIEnv)
         return NUMBER
  is
  BEGIN
   RETURN ODCICONST.SUCCESS;
  END;

  static function ODCIIndexGetMetadata(idxinfo  IN  sys.ODCIIndexInfo,
                                       expver   IN  VARCHAR2,
                                       newblock OUT number,
                                       idxenv   IN  sys.ODCIEnv)
         return VARCHAR2
  is
  begin
    return XDB.ximetadata_pkg.getIndexMetadata(idxinfo, expver, newblock, idxenv);
  end ODCIIndexGetMetadata; 

  -- path table and secondary indexes on it are already exported in schema-mode
  -- this routine should only expose them for Transportable Tablespaces,
  -- via DataPump
  static function ODCIIndexUtilGetTableNames(ia IN sys.ODCIIndexInfo, 
                                             read_only IN PLS_INTEGER, 
                                             version IN varchar2, 
                                             context OUT PLS_INTEGER)
         return BOOLEAN
  is
  begin
    return XDB.ximetadata_pkg.utlgettablenames(ia);
  end ODCIIndexUtilGetTableNames;

  static procedure ODCIIndexUtilCleanup (context  PLS_INTEGER)
  is
  begin
    -- dummy routine
    return;
  end ODCIIndexUtilCleanup;

end;
/

show errors;

----------------------------------------------------------------------------
-- Support new partitioning methods
alter indextype XDB.xmlindex
  using XDB.XMLIndexMethods
  with local partition
  with system managed storage tables;


-- temporarily run first part of reload here (redundantly on earlier upgrades)
@@xdbptrl1.sql


Rem ==================================================================
Rem Upgrade XDB Data from 11.1.0
Rem ==================================================================

/*
 * Updates for XDB DEFAULT CONFIG
 */

-- (Re-)Insert the authentication element into xdbconfig.xml
declare
  auth_count      INTEGER := 0;
  auth_frag xmltype;
  cfg xmltype;
begin
   cfg := dbms_xdb.cfg_get();
   begin
   select 1 into auth_count from dual
    where XMLExists(
       'declare namespace c = "http://xmlns.oracle.com/xdb/xdbconfig.xsd";
        /c:xdbconfig/c:sysconfig/c:protocolconfig/c:httpconfig/c:authentication'
        PASSING cfg);
   exception 
     when no_data_found then null;
   end;
 
   -- enable INSERTXMLBEFORE, APPENDCHILDXML, DELETEXML(4)
   -- Turn on rewrite for updxml/delxml/insertxml over collections(128)
   execute immediate 
     'alter session set events ''19027 trace name context forever, level 132'' ';

   if auth_count = 0 then
     auth_frag := xmltype('<authentication xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"><allow-mechanism>basic</allow-mechanism><digest-auth><nonce-timeout>300</nonce-timeout></digest-auth></authentication>');
   else
     -- extract authentication fragment for later re-insertion
     dbms_output.put_line('authentication fragment existed, deleting');
     auth_frag := cfg.extract('/xdbconfig/sysconfig/protocolconfig/httpconfig/authentication');
     select deletexml (cfg,
        '/c:xdbconfig/c:sysconfig/c:protocolconfig/c:httpconfig/c:authentication',
        'xmlns:c="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
     into cfg from dual;
   end if;

   dbms_output.put_line('inserting authentication fragment');
   select insertchildxml (cfg,
       '/c:xdbconfig/c:sysconfig/c:protocolconfig/c:httpconfig',
       'authentication',
       auth_frag,
       'xmlns:c="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
   into cfg from dual;
   dbms_output.put_line('updating xdbconfig doc');
   dbms_xdb.cfg_update(cfg); 
  end;
/

/*-----------------------------------------------------------------------*/
/*  Set 'UNSTRUCTURED PRESENT' flag for all XML indexes where flag is not */
/*  yet set.  This is valid because there are only unstructured XML  */
/*  indexes in 11.1.0.7. It is also required because on 11.2 we differentiate */
/*  structured and unstructured component using this flag. */
/*-----------------------------------------------------------------------*/
BEGIN
EXECUTE IMMEDIATE 'UPDATE xdb.xdb$dxptab 
                   SET flags = flags + 268435456 
                   WHERE bitand(flags, 268435456) = 0';
EXCEPTION
  WHEN others THEN dbms_output.put_line('XDBNB: xdb$dxptab flag update failed');
end;
/
commit;

create index xdb.xdb$resource_acloid_idx on XDB.XDB$RESOURCE e (e.xmldata.ACLOID);

grant execute on xdb.xdb$nlocks_t to public with grant option;

/*-----------------------------------------------------------------------*/
/*  Create XDB$REPOS table */
/*-----------------------------------------------------------------------*/
CREATE TABLE XDB.XDB$REPOS
(
   obj#         NUMBER NOT NULL,
   flags        NUMBER,
   rootinfo#    NUMBER,
   hindex#      NUMBER,
   hlink#       NUMBER,
   resource#    NUMBER,
   acl#         NUMBER,
   config#      NUMBER,
   dlink#       NUMBER,
   nlocks#      NUMBER,
   stats#       NUMBER,
   checkouts#   NUMBER,
   resconfig#   NUMBER
);

/*-----------------------------------------------------------------------*/
/*  Create XDB$MOUNTS table */
/*-----------------------------------------------------------------------*/
CREATE TABLE XDB.XDB$MOUNTS
(
   dobj#        NUMBER,
   dpath        VARCHAR2(4000),
   sobj#        NUMBER,
   flags        NUMBER
);

begin
  execute immediate 'drop trigger xdb.xdb_pi_trig';
exception
  when others then null;
end;
/

/*-----------------------------------------------------------------------*/
/*  Upgrade for Application user and roles support */
/*-----------------------------------------------------------------------*/
COLUMN :xdbapp_name NEW_VALUE xdbapp_file NOPRINT
VARIABLE xdbapp_name VARCHAR2(50)

declare
  stmt    varchar2(4000);
  cnt     number := 0;
begin
  :xdbapp_name := '@nothing.sql';
  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
    :xdbapp_name := '@catxdbapp.sql';
  end if;

  exception
     when others then
      :xdbapp_name := '@nothing.sql'; 
end;
/
SELECT :xdbapp_name FROM DUAL;
@&xdbapp_file


-- add app users and groups virtual folders
declare
 ret boolean;
begin
  begin
    ret := 
      xdb.dbms_xdbutil_int.createSystemVirtualFolder('/sys/principals/users/application');
    if ret then
      dbms_xdb.setACL('/sys/principals/users/application', 
                      '/sys/acls/bootstrap_acl.xml');
      dbms_output.put_line('added app users');
    end if;
    
    exception
      when others then
        dbms_output.put_line('XDBNB: error adding app users');
  end;  

  begin
    ret := 
      xdb.dbms_xdbutil_int.createSystemVirtualFolder('/sys/principals/groups/application');
    if ret then
      dbms_xdb.setACL('/sys/principals/groups/application', 
                      '/sys/acls/bootstrap_acl.xml');
      dbms_output.put_line('added app groups');
    end if;

    exception
      when others then
        dbms_output.put_line('XDBNB: error adding app groups');
  end;
end;
/
commit;

/*-----------------------------------------------------------------------*/
/*  Add indexes to xdb.xdb$element */
/*-----------------------------------------------------------------------*/
BEGIN
  /* parent_schema */
  execute immediate 
    'CREATE INDEX xdb.xdb$element_ps ON ' ||
    '  xdb.xdb$element e (sys_op_r2o(e.xmldata.property.parent_schema))';   

  /* propref_ref */
  execute immediate
    'CREATE INDEX xdb.xdb$element_pr ON ' || 
    '  xdb.xdb$element e (sys_op_r2o(e.xmldata.property.propref_ref))'; 

  /* type_ref */
  execute immediate
    'CREATE INDEX xdb.xdb$element_tr ON ' ||
    '  xdb.xdb$element e (sys_op_r2o(e.xmldata.property.type_ref))'; 

  /* head_elem_ref */
  execute immediate
    'CREATE INDEX xdb.xdb$element_her ON ' ||
    '  xdb.xdb$element ct (sys_op_r2o(ct.xmldata.head_elem_ref))';

  /* global */
  execute immediate 
    'CREATE  BITMAP index xdb.xdb$element_global ON ' ||
    '  xdb.xdb$element e (e.xmldata.property.global)';

  /* sequence_kid */
  execute immediate
    'CREATE INDEX xdb.xdb$complex_type_sk ON ' ||
    '  xdb.xdb$complex_type ct (sys_op_r2o(ct.xmldata.sequence_kid))'; 

  /* choice_kid */
  execute immediate
    'CREATE INDEX xdb.xdb$complex_type_ck ON ' ||
    '  xdb.xdb$complex_type ct (sys_op_r2o(ct.xmldata.choice_kid))'; 

  /* all_kid */
  execute immediate
    'CREATE INDEX xdb.xdb$complex_type_ak ON ' ||
    '  xdb.xdb$complex_type ct (sys_op_r2o(ct.xmldata.all_kid))';
EXCEPTION
  WHEN others THEN dbms_output.put_line('XDBNB: xdb$element index not created');
END;
/
commit;

/*-----------------------------------------------------------------------*/
/* Explicit grants to DBA,System; "any" privileges are no more applicable for */
/* XDB tables. Listing these specifically since there are certain tables */
/* for which we dont grant full access by default even to DBA & System. */
/* (eg, purely-dictionary tables like XDB$SCHEMA, XDB$TTSET etc.) */
/*-----------------------------------------------------------------------*/
grant all on XDB.XDB$RESOURCE to dba;
grant all on XDB.XDB$RESOURCE to system with grant option;
grant all on XDB.XDB$H_INDEX to dba;
grant all on XDB.XDB$H_INDEX to system with grant option;
grant all on XDB.XDB$H_LINK to dba;
grant all on XDB.XDB$H_LINK to system with grant option;
grant all on XDB.XDB$D_LINK to dba;
grant all on XDB.XDB$D_LINK to system with grant option;
grant all on XDB.XDB$NLOCKS to dba;
grant all on XDB.XDB$NLOCKS to system with grant option;
grant all on XDB.XDB$WORKSPACE to dba;
grant all on XDB.XDB$WORKSPACE to system with grant option;
grant all on XDB.XDB$CHECKOUTS to dba;
grant all on XDB.XDB$CHECKOUTS to system with grant option;
grant all on XDB.XDB$ACL to dba;
grant all on XDB.XDB$ACL to system with grant option;
grant all on XDB.XDB$CONFIG to dba;
grant all on XDB.XDB$CONFIG to system with grant option;
grant all on XDB.XDB$RESCONFIG to dba;
grant all on XDB.XDB$RESCONFIG to system with grant option;

-- ensure that public has limited privileges on acl table
revoke all on XDB.XDB$ACL from public;
grant select, insert, update, delete on XDB.XDB$ACL to public;
commit;

-- lrg 4337840
-- Remove 'with grant option' by revoking rights and
-- re-granting without grant option.
revoke execute on XDB.DBMS_XMLSCHEMA from public;
revoke execute on XDB.DBMS_XMLSCHEMA_INT from public;

-- Both sys and xdb might appear as grantors, revoke again
BEGIN
  EXECUTE IMMEDIATE 'revoke execute on XDB.DBMS_XMLSCHEMA from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on XDB.DBMS_XMLSCHEMA_INT from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on SYS.SYS_IXQAGGSUM from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on SYS.SYS_IXQAGGAVG from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on SYS.XQSEQUENCEFROMXMLTYPE  from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on SYS.SYS_IXQAGG from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on SYS.SYS_IXMLAGG from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'revoke execute on SYS.SYS_XMLAGG from public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/


grant execute on XDB.DBMS_XMLSCHEMA to public;
grant execute on XDB.DBMS_XMLSCHEMA_INT to public;

BEGIN
  EXECUTE IMMEDIATE 'grant execute on SYS.SYS_XMLAGG to public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'grant execute on SYS.SYS_IXMLAGG to public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'grant execute on SYS.SYS_IXQAGG to public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'grant execute on SYS.XQSEQUENCEFROMXMLTYPE to public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'grant execute on SYS.SYS_IXQAGGAVG to public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'grant execute on SYS.SYS_IXQAGGSUM to public';
EXCEPTION
 WHEN OTHERS THEN NULL;
END;
/

Rem ================================================================
Rem END XDB User Data upgrade from 11.1.0
Rem ================================================================

Rem ================================================================
Rem BEGIN XDB User Data upgrade from next release
Rem ================================================================

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

Rem ================================================================
Rem END XDB User Data upgrade from next release
Rem ================================================================