Rem
Rem $Header: sdo/admin/semdbmig.sql /main/3 2009/07/18 18:38:58 matperry Exp $
Rem
Rem semdbmig.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      semdbmig.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    matperry    07/17/09 - remove set echo off
Rem    matperry    02/11/09 - Created
Rem

------------------------------------------------------------------------------------
-- This script performs the upgrade of the semantic component for all prior       --
-- releases supported for upgrade                                                 --
--                                                                                --
-- No Previous version  -- Do nothing                                             --
-- Release 10.2:                                                                  --
--   No Network         -- Uninstall Semantics                                    -- 
--   Network            -- Make RDF_Match Unusable                                --
-- Release 11.1:        -- Do upgrade                                             --
--                      -- If data migration is needed, RDF_Match will be         --
--                         made unusable.                                         -- 
-- Release 11.2:        -- Do nothing                                             --
------------------------------------------------------------------------------------

WHENEVER SQLERROR CONTINUE;

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

COLUMN :script_name2 NEW_VALUE comp_file2 NOPRINT
VARIABLE script_name2 VARCHAR2(50)

alter session set current_schema=MDSYS; 

-- get the appropriate scripts for the upgrade --
declare
  type_102_exists NUMBER;
  type_11_exists  NUMBER;
  type_112_exists NUMBER;
  network_exists  NUMBER;
begin

  -- determine if the semantic component has been installed and if so which version --

  -- see if 10.2 SDO_RDF_TRIPLE_S type is present in DB
  SELECT count(*) INTO type_102_exists FROM ALL_TYPE_ATTRS
  WHERE type_name='SDO_RDF_TRIPLE_S' AND attr_name='RDF_T_ID' 
        AND owner='MDSYS';

  -- see if 11 SDO_RDF_TRIPLE_S type is present in DB
  SELECT count(*) INTO type_11_exists FROM ALL_TYPE_ATTRS
  WHERE type_name='SDO_RDF_TRIPLE_S' AND attr_name='RDF_C_ID' 
        AND owner='MDSYS';

  -- see if 11.2 is already installed
  SELECT count(*) INTO type_112_exists 
  FROM ALL_OBJECTS
  WHERE object_name='RDF_PARAMETER' AND owner='MDSYS';

  -- see if a semantic network exists --
  select count(*) into network_exists
  from all_objects
  where object_name = 'RDF_VALUE$' and owner = 'MDSYS';

  -- if semantic component has not been installed then do nothing --
  if (type_102_exists = 0 and type_11_exists = 0) then
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
       'Not upgrading Semantic Technologies: it is not installed');

    :script_name1 := dbms_registry.nothing_script;
    :script_name2 := dbms_registry.nothing_script;

  -- if 10.2 is installed --
  elsif (type_102_exists > 0) then

    -- if there is no network then uninstall --
    if (network_exists = 0) then
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	 'Uninstalling Semantic Technologies Release 10.2. No semantic network present.');
      :script_name1 := '@semremov.sql';
    else
    -- if there is a network then make rdf_match unusable --
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	 'ERROR: Not upgrading to Oracle Semantic Technologies Release 11.2. ' ||
	 'A semantic network from Release 10.2 exists and requires data migration. ' ||
	 'Please run the catsem10i.sql script to perform data migration and complete the ' ||
	 'upgrade to Release 11.2.');

      execute immediate 
	'CREATE OR REPLACE FUNCTION MDSYS.RDF_MATCH(query     varchar2,
						    models    MDSYS.RDF_Models,
						    rulebases MDSYS.RDF_Rulebases,
						    aliases   MDSYS.RDF_Aliases,
						    filter    varchar2,
						    index_status varchar2 default NULL,
						    options      varchar2 default NULL
						   )
	  RETURN SYS.ODCINumberList
	  AS
	  BEGIN
	    raise_application_error(-20000, 
	      ''Invalid semantic data exists from Release 10.2 exists. '' ||
	      ''Please run the catsem10i.sql script to perform data migration and complete the '' ||
	      ''upgrade to Release 11.2.''); 
	  END;';

      :script_name1 := dbms_registry.nothing_script;
    end if;

    :script_name2 := dbms_registry.nothing_script;

  -- if 11.2 is installed then do nothing --
  elsif (type_112_exists > 0) then

    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
       'Not upgrading Semantic Technologies: Release 11.2 is already installed');

    :script_name1 := dbms_registry.nothing_script;
    :script_name2 := dbms_registry.nothing_script;

  -- if 11.1 is installed then upgrade installation --
  elsif (type_11_exists > 0) then

    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
       'Upgrading Semantic Technologies from Release 11.1 to Release 11.2');

    -- update types, tables, views --
    :script_name1 := '@semu111.sql';
    -- load 11.2 code --
    :script_name2 := '@semrelod.sql';    

  end if;

end;
/

-- run the appropriate scripts --
alter session set current_schema=MDSYS;

SELECT :script_name1 FROM DUAL;
@&comp_file1

SELECT :script_name2 FROM DUAL;
@&comp_file2

alter session set current_schema=SYS;