Rem
Rem $Header: sdo/admin/seme112.sql /st_sdo_11.2.0/3 2011/05/05 13:17:12 sdas Exp $
Rem
Rem seme112.sql
Rem
Rem Copyright (c) 2010, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      seme112.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sdas        05/03/11 - add RDFCTX Upgrade
Rem    matperry    03/31/11 - add initial contents
Rem    sravada     05/03/10 - Created
Rem

-----------------------------------------------------------------------------------------------
-- The "e" script reverts the component's data dictionary back from release NNN+1 to release --
-- NNN. Since the cmpdbdwg.sql script calls the script for the downgrade release, these      --
-- scripts are stacked in the reverse order. They are called in the context of the NEW       --
-- server, before the RDBMS itself is downgraded. Set the version to the release version     --
-- (3 digits), not a patch version, in the dbms_registry.downgraded procedure invocation.    --
-----------------------------------------------------------------------------------------------

set echo off;

COLUMN :script_name1 NEW_VALUE comp_file1 NOPRINT
VARIABLE script_name1 VARCHAR2(50)

alter session set current_schema=MDSYS;
set define on;

-- make sure we do the downgrade without errors --
whenever sqlerror continue;

-- First mark that we are downgrading --
begin
  EXECUTE IMMEDIATE '
    UPDATE MDSYS.RDF_PARAMETER
    SET description = :1
    WHERE namespace = :2 and
	  attribute = :3'
  USING 'DOWNGRADING','MDSYS','SEM_VERSION';

  EXCEPTION WHEN OTHERS THEN 
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
      'SEM_INSTALL: seme112: set downgrade in rdf_parameter: '||SQLERRM);
end;
/

declare
  sem_exists number := 0;
begin

  -- if semantics doesn't exist we need a dummy sdo_sem_downgrade package 
  select count(*) into sem_exists
  from all_objects
  where owner = 'MDSYS' and object_name = 'SDO_RDF_TRIPLE_S'; 

  if (sem_exists = 0) then
    begin
      execute immediate '
	create or replace package mdsys.sdo_sem_downgrade_utl as
	  function data_112NG_exists(notify_level in integer)
	  return boolean;
	end sdo_sem_downgrade_utl;';

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: seme112: sdo_sem_downgrade_utl: '||SQLERRM);
    end;
  end if;

end;
/
show errors;

-- Check data compatibility with 11.2.0.1.0 --
declare
  sem_exists number := 0;
begin

  select count(*) into sem_exists
  from all_objects
  where owner = 'MDSYS' and object_name = 'SDO_RDF_TRIPLE_S'; 

  if (sem_exists > 0) then

    if (mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0)) then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'Semantics data is not compatible with version 11.2.0.1.0. Not downgrading to Release 11.2.0.1.0.');
        raise_application_error(-20000, 
          'Semantics data is not compatible with version 11.2.0.1.0');
    end if;

  end if;

end;
/
show errors;

declare
  rdfctx_exists    number;
  sem_installed    number;
  remove_rdfctx    boolean := false;
begin

  -- we need to uninstall RDFCTX if it is installed
  begin

    -- see if semantics is installed
    SELECT count(*) INTO sem_installed FROM ALL_TABLES
    WHERE table_name='RDF_PARAMETER' AND owner='MDSYS';

    if (sem_installed > 0) then
      if (not(mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0))) then
	begin
          rdfctx_exists := 0;
	  execute immediate '
	    select count(*)
	    from mdsys.rdf_parameter
	    where namespace = :1 and
		  attribute = :2'
	  into rdfctx_exists
	  using 'COMPONENT', 'RDFCTX';

	  exception when others then
	    if (sqlcode = -942) then
	      null;
	    else
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: seme112: query rdf_parameter for rdfctx: '||SQLERRM);
	    end if;
	end;

        if (rdfctx_exists is not null and rdfctx_exists > 0) then
          remove_rdfctx := true;
        end if;

      end if;
    end if;

    if (remove_rdfctx) then
      :script_name1 := '@sdordfctx_rm.sql';   
    else
      :script_name1 := dbms_registry.nothing_script;
    end if;

  end;

end;
/

SELECT :script_name1 FROM DUAL;
@&comp_file1

alter session set current_schema=MDSYS;
set define on;

-- DROP packages, views, and classes that were added in the new release
prompt dropping new views ...

declare
  network_exists   number;
  type             cursor_type is ref cursor;
  query_crs        cursor_type;
  mName            varchar2(32);
  clnMName         varchar2(32);
begin

  -- see if RDF Network is present in DB
  SELECT count(*) INTO network_exists FROM ALL_TABLES
  WHERE table_name='RDF_VALUE$' AND owner='MDSYS';

  if (network_exists > 0) then
    if (not(mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0))) then

      ---------- RDF_PRIV$  ----------
      begin
	EXECUTE IMMEDIATE '
	  DROP VIEW MDSYS.RDF_PRIV$
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: rdf_priv$: '||SQLERRM);
      end;

      ------- DTYPE_INDEX_INFO -------
      begin
	EXECUTE IMMEDIATE '
	  DROP VIEW MDSYS.SEM_DTYPE_INDEX_INFO
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: sem_dtype_index_info: '||SQLERRM);
      end;

      begin
	EXECUTE IMMEDIATE '
	  DROP VIEW MDSYS.RDF_DTYPE_INDEX_INFO
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: rdf_dtype_index_info: '||SQLERRM);
      end;

      --------- Drop new stats views ------
      begin
	open query_crs for 'select model_name
			    from mdsys.rdf_model$';

	loop
	begin
	  fetch query_crs into mName;
	  exit when query_crs%NOTFOUND;

	  -- get the model name
	  clnMName := sys.dbms_assert.simple_sql_name(mName);

	  begin
	    EXECUTE IMMEDIATE '
	      DROP VIEW MDSYS.SEMT_' || clnMName || '
	    ';
	    EXCEPTION WHEN OTHERS THEN 
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: seme112: semt stats view: '||SQLERRM);
	  end;

	  begin
	    EXECUTE IMMEDIATE '
	      DROP VIEW MDSYS.SEMP_' || clnMName || '
	    ';
	    EXCEPTION WHEN OTHERS THEN 
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: seme112: semp stats view: '||SQLERRM);
	  end;

	  EXCEPTION WHEN OTHERS THEN 
	    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	      'SEM_INSTALL: seme112: drop stats views: '||SQLERRM);
	end;
	end loop;

	close query_crs;

	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: drop stats views: '||SQLERRM);
          close query_crs;
      end;
    end if;
  end if;
end;
/

prompt ... finished dropping views 

-- DROP force any new types, operators, and indextypes;
prompt dropping new types ...

declare
  network_exists number;
begin
  -- see if RDF Network is present in DB
  SELECT count(*) INTO network_exists FROM ALL_TABLES
  WHERE table_name='RDF_VALUE$' AND owner='MDSYS';

  if (network_exists > 0) then
    if (not(mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0))) then

      -------------- RDF_Graphs ------------
      begin
	EXECUTE IMMEDIATE '
	  DROP TYPE MDSYS.RDF_Graphs FORCE
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: RDF_Graphs: '||SQLERRM);
      end;

    end if;
  end if;
end;
/

prompt ... finished dropping new types

-- ALTER TYPE for existing types back to their prior release definitions
prompt altering types ...

declare
  network_exists number;
begin
  -- see if RDF Network is present in DB
  SELECT count(*) INTO network_exists FROM ALL_TABLES
  WHERE table_name='RDF_VALUE$' AND owner='MDSYS';

  if (network_exists > 0) then
    if (not(mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0))) then

      -------- SDO_RDF_TRIPLE_S ------
      begin
	EXECUTE IMMEDIATE '
	  ALTER TYPE sdo_rdf_triple_s
	  DROP MEMBER FUNCTION get_model RETURN VARCHAR2 
	  DETERMINISTIC CASCADE
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: sdo_rdf_triple_s: '||SQLERRM);
      end;   

    end if;
  end if;
end;
/

prompt ... finished altering types

-- ALTER OPERATOR and INDEXTYPE back to their prior release bindings and properties

-- UPDATE new columns to values appropriate for the old release and for when the database is re-upgraded
prompt altering network tables and rebuilding indexes...

declare
  network_exists number;
  numIdxCols     pls_integer := 0;
  optCol         varchar2(30);
  optComp        varchar2(30);
begin

  -- see if RDF Network is present in DB
  SELECT count(*) INTO network_exists FROM ALL_TABLES
  WHERE table_name='RDF_VALUE$' AND owner='MDSYS';

  if (network_exists > 0) then
    if(not(mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0))) then

      --------- See which non-gid unique index we need to create -------------

      -- See if existing index is 5 or 6 columns. 
      -- If 6 columns, then it has been modified for optional components
      begin
	execute immediate '
	  select count(*)
	  from all_ind_columns
	  where index_name = ''RDF_LNK_PCSGM_IDX''
	    and index_owner = ''MDSYS'''
	into numIdxCols;

	exception when others then 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: get num idx columns: '||SQLERRM);
      end;

      -- if we have 6 columns see what optional column is used
      if (numIdxCols = 6) then
	begin
	  -- see what modified index we have
	  execute immediate '
	    select attribute
	    from mdsys.rdf_parameter
	    where value = ''ENABLED''
	      and attribute in (''RDFCTX'',''RDFOLS'',''RDFOWM'')'
	  into optComp;

	  if (optComp = 'RDFOLS') then
	    optCol := 'CTXT1';
	  else
	    optCol := 'SUBSTR(CTXT2,1,600)';
	  end if;

	  exception when others then 
	    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	      'SEM_INSTALL: seme112: get optional unique index column: '||SQLERRM);
	end;
      end if;

      ---------------- drop g_id-based unique index -----------------
      begin
	execute immediate '
	  drop index mdsys.rdf_lnk_pcsgm_idx';
	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: drop graph-based unique index: '||SQLERRM);
      end;

      ---------------- redefine LINK_ID column ----------------------
      -- drop link_id --
      begin
	execute immediate
          'alter table mdsys.rdf_link$ drop (link_id) cascade constraints';
	exception when others then 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: drop link_id from link$: '||SQLERRM);
      end;

      -- add new link_id --
      begin
	execute immediate
	  'alter table rdf_link$ add
	    (link_id as (
	      to_char(model_id,         ''FMXXXXXXXXXXXXXXXX'') ||''_''||
	      to_char(start_node_id,    ''FMXXXXXXXXXXXXXXXX'') ||''_''||
	      to_char(p_value_id,       ''FMXXXXXXXXXXXXXXXX'') ||''_''||
	      to_char(canon_end_node_id,''FMXXXXXXXXXXXXXXXX'')))
	  ';
	exception when others then 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: add link_id to link$: '||SQLERRM);
      end;

      ------------------ drop G_ID column ---------------------------
      begin
	execute immediate
          'alter table mdsys.rdf_link$ drop (g_id) cascade constraints';
	exception when others then 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: drop g_id from link$: '||SQLERRM);
      end;

      -------------- create non-gid unique index --------------------
      -- if only 5 columns then create plain pvidcensnmid unique index
      if (numIdxCols = 5) then
	begin
	  execute immediate '
	    create unique index mdsys.rdf_lnk_pvidcensnmid_idx 
	    on mdsys.rdf_link$ (
	      p_value_id, canon_end_node_id, start_node_id, model_id)
	    compress 2 nologging local parallel';
	  exception when others then 
	    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	      'SEM_INSTALL: seme112: create 4-column index: '||SQLERRM);
	end;

      -- if 6 columns then create modified unique index 
      elsif (numIdxCols = 6) then
	begin
	  -- create new pvidcensnmid index --
	  execute immediate '
	    create unique index mdsys.rdf_lnk_pvidcensnmid_idx 
	    on mdsys.rdf_link$ (
	      p_value_id, canon_end_node_id, start_node_id, model_id, '||optCol||')
	    compress 2 nologging local parallel';

	  exception when others then 
	    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	      'SEM_INSTALL: seme112: create 5-column index: '||SQLERRM);
	end;
      -- unique index is screwed up
      else
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: seme112: Unexpected number of columns in RDF_LNK_PCSGM_IDX index');
      end if;

    end if;
  end if;

end;
/

prompt ... finished altering network tables and rebuilding indexes

-- Undo any modifications that were made to user objects during the upgrade.

-- Drop new tables
prompt dropping new tables ...

declare
  network_exists number;
begin
  -- see if RDF Network is present in DB
  SELECT count(*) INTO network_exists FROM ALL_TABLES
  WHERE table_name='RDF_VALUE$' AND owner='MDSYS';

  if (network_exists > 0) then
    if (not(mdsys.sdo_sem_downgrade_utl.data_112NG_exists(0))) then

      -------- STATS_TABLES ------
      begin
	EXECUTE IMMEDIATE '
	  DROP TABLE MDSYS.RDF_PRED_STATS$
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: rdf_pred_stats$: '||SQLERRM);
      end;    

      begin
	EXECUTE IMMEDIATE '
	  DROP TABLE MDSYS.RDF_TERM_STATS$
	';
	EXCEPTION WHEN OTHERS THEN 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: seme112: rdf_term_stats$: '||SQLERRM);
      end;    

    end if;
  end if;
end;
/

prompt ...finished dropping new tables

alter session set current_schema=SYS;