Rem
Rem $Header: rdbms/admin/catxdbtm.sql /main/12 2009/04/06 20:28:14 badeoti Exp $
Rem
Rem catxdbtm.sql
Rem
Rem Copyright (c) 2003, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      catxdbtm.sql - XDB compact xml Token Manager related tables
Rem
Rem    DESCRIPTION
Rem      This script creates the tables required for XDB Compact XML
Rem      token management.         
Rem
Rem    NOTES
Rem      This script should be run as the user "XDB".
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    badeoti     03/21/09 - dbms_csx_admin.guidto32 moved to dbms_csx_int
Rem    bsthanik    05/18/07 - Bug 6054818
Rem    spetride    11/01/07 - token tables not registered for export 
Rem    spetride    01/16/07 - do not drop tables if already exist
Rem    spetride    08/10/06 - check for already existing tok tables
Rem    spetride    03/01/06 - support for multiple token repositories
Rem    nitgupta    02/07/06 - Token MGR uses VARCHAR2 columns
Rem    smukkama    11/19/04 - use even smaller token size (UTF8 ncharset)
Rem    smukkama    10/12/04 - for less than 8K blk sz use smaller token columns
Rem    smukkama    09/30/04 - move xmlidx plsql stuff to catxidx.sql
Rem    smukkama    08/13/04 - add flags column (for attr) to xdb$qname_id
Rem    smukkama    06/23/04 - Remove set echo on
Rem    athusoo     04/06/04 - Add path suffix table function 
Rem    smukkama    02/27/04 - Add reverse path index on xdb$path_id
Rem    smukkama    12/16/03 - Created
Rem

-- delete exp/imp related info
declare
  stmtdrop       varchar2(2000);
begin
  stmtdrop :=  
    'delete from sys.exppkgact$ where ( schema like ''' || 'XDB' || ''' )';
  execute immediate stmtdrop;
  stmtdrop :=
    'delete from sys.expdepact$ where ( schema like ''' || 'XDB' || ''' )';
  execute immediate stmtdrop;
  exception
       when others then
         -- we are here if this is the first time we call catxdbtm.sql
         -- no error  
         NULL;
end;
/

/*************** Create XDB.XDB$TTSET table *****************/
begin
   execute immediate 
      'create table xdb.xdb$ttset( 
          guid     raw(16), 
          toksuf   varchar(26), 
          flags    number, 
          obj#     number unique)';  
  exception
       when others then
         -- raise no error if table already exists
         NULL;
     
end;
/

/************ Create Token - ID mapping tables *********************/
/* If the block size is less than 8K use smaller token sizes so
 * that index creation doesn't fail. (see bug 3928505)
 *
 * The max index key sizes for various block sizes are:
 *  2K max index key size = 1478 bytes (on Linux)
 *  4K max index key size = 3118 bytes (on Linux)
 *  8K max index key size = 6398 bytes (on Linux)
 *
 * For each of the various token column sizes below, the maximum token
 * length that would permit token-->id index creation was determined and
 * then a value 5% less (to account for any platform specific variance)
 * was picked as the token size.
 *
 */

declare
  bsz number;
  nmspc_tok_chars  number;
  qname_tok_chars  number;
  path_tok_bytes   number;
  guid             raw(16);
  guid32           varchar2(26);
  nmguid           varchar2(34);
  qnguid           varchar2(34);
  ptguid           varchar2(34); 
  qnguid2          varchar2(30);
  regstmt          varchar2(2000);
  stmt1            varchar2(2000);
  stmt2            varchar2(2000);
  stmt3            varchar2(2000);
  tabno            number;
  tsno             number;
begin
    /* figure out block size and use appropriate token size */
   select t.block_size into bsz from user_tablespaces t, user_users u
      where u.default_tablespace = t.tablespace_name;

   if bsz < 4096 then
      nmspc_tok_chars := 464;
      qname_tok_chars := 460;
      path_tok_bytes  := 1395;
   elsif bsz < 8192 then
      nmspc_tok_chars := 984;
      qname_tok_chars := 979;
      path_tok_bytes  := 2000;
   else
      nmspc_tok_chars := 2000;
      qname_tok_chars := 2000;
      path_tok_bytes  := 2000;
   end if;

    -- create the default GUID
   guid := sys_guid();
   guid32 := xdb.dbms_csx_int.guidto32(guid);
   nmguid := 'xdb.x$nm' || guid32;    -- name of the default Qname ID token table
                                  -- used to be XDB.XDB$QNAME_ID

   qnguid := 'xdb.x$qn' || guid32;    -- name of the default URI ID token table
                                  -- used to be XDB.XDB$NMSPC_ID
 
   ptguid := 'xdb.x$pt' || guid32;    -- name of the default Path Id token table  
                                  -- used to be XDB.XDB$PATH_ID

   qnguid2 := 'X$QN' || guid32;


   execute immediate                           -- Namespace URI ID Token Table
      'create table ' || nmguid || ' (
         nmspcuri varchar2(' || nmspc_tok_chars || '), 
         id        raw(8)) ';

   execute immediate                           -- QName ID Token Table
      'create table ' || qnguid || ' (
         nmspcid      raw(8),
         localname    varchar2(' || qname_tok_chars || '),
         flags        raw(4),
         id           raw(8)) ';

   execute immediate                           -- PathID Token Table
      'create table ' || ptguid || ' (
         path         raw(' || path_tok_bytes || '),
         id           raw(8)) ';
   commit;

  -- insert reserved values into default token tables 

  execute immediate 
     'insert into ' || nmguid || ' values(:1, :2)'
       using 'http://www.w3.org/XML/1998/namespace', HEXTORAW('01');
  execute immediate
     'insert into ' || nmguid || ' values(:1, :2)' 
       using 'http://www.w3.org/XML/2000/xmlns', HEXTORAW('02');
  execute immediate
      'insert into ' || nmguid || ' values(:1, :2)'
       using 'http://www.w3.org/2001/XMLSchema-instance', HEXTORAW('03');
  execute immediate 
      'insert into ' || nmguid || ' values(:1, :2)' 
       using  'http://www.w3.org/2001/XMLSchema', HEXTORAW('04');
  execute immediate 
      'insert into ' || nmguid || ' values(:1, :2)' 
       using 'http://xmlns.oracle.com/2004/csx',  HEXTORAW('05');
  execute immediate 
      'insert into ' || nmguid || ' values(:1, :2)' 
       using 'http://xmlns.oracle.com/xdb',  HEXTORAW('06');
  execute immediate
      'insert into ' || nmguid || ' values(:1, :2)' 
       using 'http://xmlns.oracle.com/xdb/nonamespace', HEXTORAW('07');
  execute immediate 
      'insert into ' || nmguid || ' values(:1, :2)' 
       using 'http://www.w3.org/2001/XInclude',  HEXTORAW('08');
  commit;

  execute immediate
     'insert into ' || qnguid || ' values(:1, :2, :3, :4)'
      using  HEXTORAW('01'), 'space', HEXTORAW('01'), HEXTORAW('10');
  execute immediate
     'insert into ' || qnguid || ' values(:1, :2, :3, :4)'
      using HEXTORAW('01'), 'lang', HEXTORAW('01'), HEXTORAW('11');
  execute immediate
     'insert into ' || qnguid || ' values(:1, :2, :3, :4)'
      using HEXTORAW('03'), 'type', HEXTORAW('01'), HEXTORAW('12');
  execute immediate
     'insert into ' || qnguid || ' values(:1, :2, :3, :4)'
      using HEXTORAW('03'), 'nil', HEXTORAW('01'), HEXTORAW('13');
  execute immediate
     'insert into ' || qnguid ||  ' values(:1, :2, :3, :4)'
      using HEXTORAW('03'), 'schemaLocation', HEXTORAW('01'), HEXTORAW('14');
  execute immediate
     'insert into ' || qnguid || ' values(:1, :2, :3, :4)'
      using HEXTORAW('03'), 'noNamespaceSchemaLocation', HEXTORAW('01'), HEXTORAW('15');
  execute immediate 
     'insert into ' || qnguid || ' values(:1, :2, :3, :4)'
      using HEXTORAW('02'), 'xmlns', HEXTORAW('01'), HEXTORAW('16');
  commit;

 
  -- create Indexes on default token tables 

   -- used to be called xdb.xdb$nmspc_id_nmspcuri
  execute immediate
    'create unique index xdb.x$nn' || guid32 || ' on ' ||  nmguid || '(nmspcuri)';
   --used to be called xdb.xdb$nmspc_id_id
  execute immediate
    'create unique index xdb.x$ni' || guid32 || ' on ' || nmguid || '(id) ';
 
   --used to be called xdb.xdb$qname_id_nmspcid
  execute immediate
    'create index xdb.x$qs' || guid32 || ' on ' || qnguid || ' (nmspcid) ';
   --used to be called xdb.xdb$qname_id_qname
  execute immediate
    'create unique index xdb.x$qq' || guid32 || '  on ' || qnguid || 
    '(nmspcid, localname, flags) ';
  --used to be called xdb.xdb$qname_id_id
  execute immediate 
    'create unique index xdb.x$qi' || guid32  || ' on ' || qnguid || '(id) ';

  --used to be called xdb.xdb$path_id_path
  execute immediate
    'create unique index xdb.x$pp' || guid32 || ' on ' || ptguid || '(path) ';
  --used to be called xdb.xdb$path_id_id
  execute immediate
    'create unique index xdb.x$pi' || guid32 || ' on ' || ptguid || '(id) ';
  --used to be called xdb.xdb$path_id_revpath
  execute immediate
    'create unique index xdb.x$pr' || guid32 || ' on ' || ptguid || 
    '(SYS_PATH_REVERSE(path)) ';
  commit;

   -- add entry to XDB.XDB$TTSET
   stmt1 := 'select o.obj# from sys.obj$ o, sys.user$ u where (u.name = ''' || 
            'XDB' || ''') and (o.name = ''' || qnguid2 || 
            ''') and (o.owner# = u.user#)'; 
   execute immediate stmt1 into tabno;
   stmt2 := 'select t.ts# from dba_tables x, sys.ts$ t where (x.table_name = ''' || 
            qnguid2 || ''')  and (x.owner = ''' || 'XDB' || 
            ''') and (x.tablespace_name = t.name)';
   execute immediate stmt2 into tsno;  

   insert into xdb.xdb$ttset values (guid, guid32, 0, tsno);

  -- register DBMS_XDB_EXP_RULES for export
  begin
    stmt1 := 'insert into sys.exppkgact$ values(:1, :2, 3, 2006)';
    execute immediate stmt1 using 'DBMS_CSX_ADMIN', 'XDB';
  end;
  commit;


  exception
       when others then
         -- 1. raise no error if tables/indexes already exist
         -- 2. still need to register the  DBMS_XDB_EXP_RULES for export
         begin
          stmt1 := 'insert into sys.exppkgact$ values(:1, :2, 3, 2006)';
          execute immediate stmt1 using 'DBMS_CSX_ADMIN', 'XDB';           
         exception
           when others then
             NULL;
         end;
         NULL;
end;
/