Rem
Rem $Header: sdo/admin/semu111.sql /st_sdo_11.2.0/3 2011/05/05 13:17:12 sdas Exp $
Rem
Rem semu111.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      semu111.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    05/03/11 - stack call to semu112
Rem    matperry    03/11/11 - add logging for errors
Rem    vkolovsk    04/21/10 - change RL -> OWL2RL
Rem    vkolovsk    04/19/10 - add RL rulebase
Rem    vkolovsk    11/04/09 - add sameas opt. types
Rem    matperry    02/11/09 - add content for OLS and Virtual Models
Rem    matperry    02/11/09 - Created
Rem

------------------------------------------------------------------------------------------
-- The "u" script for each prior release performs the following actions to upgrade      --
-- from release NNN to NNN+1. If NNN+1 is not the current release, then the "u" script  --
-- invokes the script for release NNN+1, so that the upgrade is "stacked.".             --
------------------------------------------------------------------------------------------

set echo off;
alter session set current_schema=MDSYS;

Rem Upgrade SEM from 11.1 to 11.2

Rem Drop obsolete objects (views, packages, etc.) from prior release


Rem ALTER tables to add/change columns and constraints for the new release


Rem Modify static tables for the new release



Rem UPDATE existing columns as needed to reflect new algorithms, etc.

--------------------- Virtual Model support: update view definitons -----------------------
-- only update these views if an rdf network exists --
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 

    begin

      -- modify the existing rdf_model$ view --
      EXECUTE IMMEDIATE '
	CREATE OR REPLACE VIEW MDSYS.rdf_model$ (
	  owner
	, model_id
	, model_name
	, table_name
	, column_name
	, model_tablespace_name
	) AS
	SELECT 
	  au.username
	, rmi.model_id
	, rmi.model_name
	, ao.object_name
	, rmi.column_name
	, rmi.model_tablespace_name
	FROM mdsys.rdf_model_internal$ rmi,all_users au,all_objects ao
	WHERE rmi.owner_id=au.user_id AND ao.owner=au.username AND
	      rmi.table_id=ao.object_id AND rmi.model_id > 0
	UNION ALL
	SELECT
	  au.username
	, rmi.model_id
	, rmi.model_name
	, NULL
	, NULL
	, NULL
	FROM mdsys.rdf_model_internal$ rmi, all_users au
	WHERE rmi.owner_id=au.user_id AND 
	      rmi.model_id < 0
      ';

      -- 11g API
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_MODEL$ AS SELECT * FROM MDSYS.RDF_MODEL$';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_MODEL$ TO PUBLIC';

      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu111: sem_model$: '||SQLERRM);
    end;

    begin

      -- modify rules index views to filter out virtual model info --
      EXECUTE IMMEDIATE '
	create or replace view MDSYS.RDF_RULES_INDEX_INFO (
	  OWNER, INDEX_NAME, INDEX_VIEW_NAME, STATUS, MODEL_COUNT,RULEBASE_COUNT)
	as
	  select p.owner, p.indexname, ''RDFI_'' || p.indexname,
		 DECODE(p.status,
			''INPROGRESS'',
			SUBSTR(MDSYS.RDF_APIS_INTERNAL.TEST_INPROGRESS(
				 ''RULES_INDEX'', p.indexname), 
			       0, 30),
			p.status),
		 SUM(DECODE(rpd.deptyp, ''M'', 1, 0)),
		 SUM(DECODE(rpd.deptyp, ''R'', 1, 0))
	  from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ rpd
	  where p.model_id = rpd.model_id and p.model_id > 0
	  group by p.owner, p.indexname, p.status';

      EXECUTE IMMEDIATE '
	create or replace view MDSYS.RDF_RULES_INDEX_DATASETS (
	  INDEX_NAME, DATA_TYPE, DATA_NAME) 
	as 
	  select p.indexname, ''MODEL'', m.model_name
	  from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ pd, 
	       mdsys.rdf_model_internal$ m
	  where p.model_id = pd.model_id
	    AND pd.deptyp = ''M'' 
	    AND pd.depid = m.model_ID
	    AND p.model_id > 0
	  UNION ALL
	  select p.indexname, ''RULEBASE'', r.name
	  from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ pd, 
	       mdsys.rdf_rulebase$ r
	  where p.model_id = pd.model_id
	    AND pd.deptyp = ''R'' 
	    AND pd.depid = r.id
	    AND p.model_id > 0';

      -- 11g API --
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_RULES_INDEX_INFO AS SELECT * FROM MDSYS.RDF_RULES_INDEX_INFO';
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_RULES_INDEX_DATASETS AS SELECT * FROM MDSYS.RDF_RULES_INDEX_DATASETS';

      -- grant SELECT privileges --
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_RULES_INDEX_INFO     TO PUBLIC';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_RULES_INDEX_DATASETS TO PUBLIC';

      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.RDF_RULES_INDEX_INFO     TO PUBLIC';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.RDF_RULES_INDEX_DATASETS TO PUBLIC';

      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu111: sem_rules_index_info, sem_rules_index_datasets: '||SQLERRM);
      end;

  -- end if network exists --
  end if;

end;
/
show errors;
    
Rem Create new types and tables for the release


------------ sameAs Optimization / incremental inference / SKOS Rulebase -------
declare
  TABLE_ALREADY_EXIST_EXCEPTION      EXCEPTION;   
  PRAGMA EXCEPTION_INIT(TABLE_ALREADY_EXIST_EXCEPTION,  -955);
  stmt_cre_TS varchar2(2000);
  stmt_cre_clique varchar2(2000);
  stmt_cre_delta varchar2(2000);
  vcTableSpaceName varchar2(100);
  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 

    -- NOTE: delaying SKOSCORE rulebase creation until semrelod 
    -- becuase rdf_apis_internal.create_rulebase will be invalid after downgrade

    -- get tablespace name --
    begin
      EXECUTE IMMEDIATE
	'select def_tablespace_name from all_part_tables where table_name = :1 and owner = :2'
      INTO vcTableSpaceName
      USING 'RDF_LINK$', 'MDSYS';
      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu111: def_tablespace_name: '||SQLERRM);
    end;

    --creating clique table...
    stmt_cre_clique := '
      CREATE TABLE rdf_clique$ (
	model_id     NUMBER
      , value_id     NUMBER NOT NULL
      , clique_id    NUMBER NOT NULL
      ) PARTITION BY LIST (model_id) (PARTITION MODEL_0 VALUES (0))
      tablespace ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(vcTableSpaceName, FALSE) || ' 
      compress ';

    begin
      EXECUTE IMMEDIATE stmt_cre_clique;
      exception when TABLE_ALREADY_EXIST_EXCEPTION then null;
                when others then
		  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		    'SEM_INSTALL: semu111: rdf_clique$: '||SQLERRM);
    end;

    --creating timestamp table...
    stmt_cre_TS := '
      CREATE TABLE rdf_TS$ (
	rule_index_id number,
	model_id     NUMBER
      , TS           timestamp NOT NULL
      ) tablespace ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(vcTableSpaceName, FALSE) || ' 
      compress ';

    begin
      EXECUTE IMMEDIATE stmt_cre_TS;
      exception when TABLE_ALREADY_EXIST_EXCEPTION then null;
                when others then
		  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		    'SEM_INSTALL: semu111: rdf_TS$: '||SQLERRM);
    end;

    stmt_cre_delta := 
       'CREATE TABLE rdf_delta$ '
       || ' (model_id number, row_id rowid, TS timestamp) '
       || ' partition by list (model_id) '
--        || ' subpartition by list (scn) '
--        || ' subpartition template (subpartition scn_0 values (0)) '
       || ' (partition  model_0 values (0))'
       || ' tablespace ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(vcTableSpaceName, FALSE)
       || ' compress';
    begin
      execute immediate stmt_cre_delta;
      exception when TABLE_ALREADY_EXIST_EXCEPTION then null;
                when others then
		  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		    'SEM_INSTALL: semu111: rdf_delta$: '||SQLERRM);
    end;

    --unique (inferred model id) index on RDF_ts
    begin
      EXECUTE IMMEDIATE '
          CREATE UNIQUE INDEX rdf_ts_idx ON rdf_ts$ (rule_index_id, model_id)
          NOLOGGING '
      || ' tablespace ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(vcTablespaceName, FALSE);
      exception when others then
		  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		    'SEM_INSTALL: semu111: rdf_ts_idx: '||SQLERRM);
    end;

  end if;

end;
/
show errors;

--sameAs opt. types
begin
  execute immediate 'create or replace TYPE sem_sameas_rec as object (s number, e number)';
exception 
  when others then 
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
      'SEM_INSTALL: semu111: sem_sameas_rec: '||SQLERRM);
end;
/

begin
  execute immediate 'create or replace TYPE sem_sameas_list is TABLE OF sem_sameas_REC';
exception 
  when others then
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
      'SEM_INSTALL: semu111: sem_sameas_list: '||SQLERRM);
end;
/  



---------------- OLS support: types for extensible label generator ------------

/*****************************************************************************/
/*** Type used for Extensible Label Generator for Inferred data            ***/
/*****************************************************************************/
begin
execute immediate '
create or replace type mdsys.rdfsa_resource as object (
  res_id      VARCHAR2(100),
  res_type    NUMBER,
  res_labels  MDSYS.INT_ARRAY,
  constructor function rdfsa_resource (res_id VARCHAR2, res_type number)
                                            return self as result,
  constructor function rdfsa_resource (res_id VARCHAR2,
         res_type number, res_label number) return self as result,
  constructor function rdfsa_resource (res_id VARCHAR2, res_type number,
                res_labels mdsys.int_array) return self as result,
  member function getResource return VARCHAR2,
  member function getLabelCount return number,
  member function getLabel(idx number default 1) return number
)'; 
exception when others then
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
      'SEM_INSTALL: semu111: rdfsa_resource: '||SQLERRM); 
end;
/

show errors; 

-- Dummy type implementation: actual type body will be installed when OLS 
-- is enabled for RDF using sdordfsa.sql 
create or replace type body mdsys.rdfsa_resource as 
  constructor function rdfsa_resource (res_id VARCHAR2, res_type number)
                                            return self as result is 
  begin
    null;
  end; 

  constructor function rdfsa_resource (res_id VARCHAR2, res_type number,
            res_label number) return self as result is 
  begin
    null;
  end;

  constructor function rdfsa_resource (res_id VARCHAR2, res_type number,
            res_labels mdsys.int_array) return self as result is
  begin
    null;
  end; 

  member function getResource return VARCHAR2 is 
  begin
    null;
  end; 

  member function getLabelCount return number is
  begin
    null; 
  end; 

  member function getLabel(idx number default 1) return number is
  begin
    null;
  end;
end;
/

show errors; 


/*****************************************************************************/
/**** Label generator stub for Extensible implementations                  ***/
/***  This type can be extended for custom label generators                ***/
/*****************************************************************************/
create or replace type mdsys.rdfsa_labelgen authid current_user as object (
  gen_option   NUMBER,

  -- 
  --- SETDEPRESOURCES : to set the dependent resources for the label   
  --- generator. Information about these resources will be passed to the
  --- the getNumericLabel method at runtime                              
  ---                                                                    
  --- Usage:  setDepResources(sem_rdfsa.USE_SUBJECT_LABEL+
  ---                         sem_rdfsa.USE_RULE_LABEL) 
  --
  final member procedure setDepResources(useres number),

  -- 
  --- FINDOMINATINGOF : Find a clear dominating label out of the labels 
  --- passed in. -1 is returned if a clear dominating label is not found -- 
  final static function findDominatingOf(labels MDSYS.INT_ARRAY) return number,

  --
  --- GETNUMERICLABEL : Extensible implementations for this type should   
  --- override this method to return a custom label based on the resources 
  --- passed in. The exact list of resources passed in is dependent on    
  --- options passed to the setDepResource method                        
  --
  member function getNumericLabel (subject   rdfsa_resource,
                                   predicate rdfsa_resource,
                                   object    rdfsa_resource,
                                   rule      rdfsa_resource,
                                   anteced   rdfsa_resource)
       return number

) not final
/

show errors;

create or replace type body mdsys.rdfsa_labelgen is

  final member procedure setDepResources(useres number) is
  begin
    null;
  end;

  final static function findDominatingOf(labels MDSYS.INT_ARRAY)
       return number is
  begin
    null;
  end; 

  member function getNumericLabel (subject   rdfsa_resource,
                                   predicate rdfsa_resource,
                                   object    rdfsa_resource,
                                   rule      rdfsa_resource,
                                   anteced   rdfsa_resource)
       return number is
  begin
    null;
  end; 
end; 
/ 

SHOW ERRORS;
-- grant execute on mdsys.rdfsa_resource to public;

begin
  EXECUTE IMMEDIATE 'create table mdsys.rdf_parameter 
        (namespace   VARCHAR2(30), 
         attribute   VARCHAR2(30), 
         value       VARCHAR2(512), 
         description VARCHAR2(100), 
         constraint rdf_parameter_key primary key (namespace, attribute))
       organization index'; 
exception 
  when others then 
    if (SQLCODE != -955) then 
      raise; 
    else
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	'SEM_INSTALL: semu111: rdf_parameter: '||SQLERRM); 
    end if; 
end;
/

--------------- Virtual Model support: create new views ----------------------
-- only create view if rdf network already exists --
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 

    -- Create dummy package for Virtual Model view definitions --
    -- One of the views references a method in this package --
    -- This dummy package will be replaced with the correct one later in the install --
    begin
      EXECUTE IMMEDIATE '
	CREATE OR REPLACE PACKAGE MDSYS.rdf_apis_internal authid definer AS
	  FUNCTION vmUnionViewExists(numModels    in int,
				     numRulebases in int,
				     vmId         in number,
				     vmName       in varchar2)
	  RETURN varchar2;
	END rdf_apis_internal;';

      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu111: rdf_apis_internal: '||SQLERRM); 
    end;

    begin 
      EXECUTE IMMEDIATE '
	create or replace view MDSYS.RDF_VMODEL_DATASETS (
	  VIRTUAL_MODEL_NAME, DATA_TYPE, DATA_NAME) 
	as 
	  WITH rpd2hop as
	    (select rpd1.model_id, rpd2.deptyp, rpd2.depid
	     from mdsys.rdf_precomp_dep$ rpd1,
		  mdsys.rdf_precomp_dep$ rpd2
	     where rpd1.depid = rpd2.model_id
	       AND rpd1.model_id < 0
	       AND rpd1.deptyp = ''X'')
	  select p.indexname, ''MODEL'', m.model_name
	  from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ rpd, 
	       mdsys.rdf_model_internal$ m
	  where p.model_id = rpd.model_id
	    AND rpd.deptyp = ''V'' 
	    AND rpd.depid = m.model_ID
	    AND p.model_id < 0
	  UNION ALL
	  select p.indexname, ''MODEL'', m.model_name
	  from mdsys.rdf_precomp$ p, rpd2hop rpd, 
	       mdsys.rdf_model_internal$ m
	  where p.model_id = rpd.model_id
	    AND rpd.deptyp = ''M'' 
	    AND rpd.depid = m.model_ID
	    AND p.model_id < 0
	  UNION ALL
	  select p.indexname, ''RULEBASE'', r.name
	  from mdsys.rdf_precomp$ p, rpd2hop rpd, 
	       mdsys.rdf_rulebase$ r
	  where p.model_id = rpd.model_id
	    AND rpd.deptyp = ''R'' 
	    AND rpd.depid = r.id
	    AND p.model_id < 0
	  UNION ALL
	  select p1.indexname, ''RULEIDX'', p2.indexname
	  from mdsys.rdf_precomp$ p1, mdsys.rdf_precomp$ p2,
	       mdsys.rdf_precomp_dep$ rpd
	  where p1.model_id = rpd.model_id
	    AND rpd.depid = p2.model_id
	    AND rpd.deptyp = ''X''
	    AND p1.model_id < 0'; 

      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu111: rdf_vmodel_datasets: '||SQLERRM); 
    end;     

    begin
      EXECUTE IMMEDIATE '
	create or replace view MDSYS.RDF_VMODEL_INFO (
	  OWNER, VIRTUAL_MODEL_NAME, UNIQUE_VIEW_NAME, DUPLICATE_VIEW_NAME, 
	  STATUS, MODEL_COUNT, RULEBASE_COUNT, RULES_INDEX_COUNT)
	as
	  select vmi.owner, vmi.indexname, 
		 substr(mdsys.rdf_apis_internal.vmUnionViewExists(vmi.numMods, vmi.numRbs,
			vmi.model_id, vmi.indexname), 1, 30),
		 substr(''SEMV_'' || vmi.indexname, 1, 30), vmi.status, vmi.numMods,
		 vmi.numRbs, vmi.numIdxs
	  from 
	   (select p.owner owner, p.model_id model_id, p.indexname indexname, p.status, 
		 SUM(DECODE(rpd.deptyp, ''M'', 1, 0)) + SUM(DECODE(rpd.deptyp, ''V'', 1, 0)) numMods,
		 SUM(DECODE(rpd.deptyp, ''R'', 1, 0)) numRbs,
		 SUM(DECODE(rpd.deptyp, ''X'', 1, 0)) numIdxs
	    from mdsys.rdf_precomp$ p, 
	       (select rpd1.model_id model_id, rpd1.deptyp deptyp
		from mdsys.rdf_precomp_dep$ rpd1
		where rpd1.model_id < 0 and rpd1.deptyp = ''X'' or
		      rpd1.deptyp = ''V''
		UNION ALL
		select rpd1.model_id model_id, rpd2.deptyp deptyp
		From mdsys.rdf_precomp_dep$ rpd1,
		     mdsys.rdf_precomp_dep$ rpd2
		where rpd1.depid = rpd2.model_id and
		      rpd1.model_id < 0 and rpd1.deptyp = ''X'') rpd
	    where p.model_id = rpd.model_id and p.model_id < 0
	    group by p.owner, p.model_id, p.indexname, p.status) vmi'; 

      -- support both rdf* and sem* versions --
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_VMODEL_DATASETS AS SELECT * FROM MDSYS.RDF_VMODEL_DATASETS';
      EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_VMODEL_INFO AS SELECT * FROM MDSYS.RDF_VMODEL_INFO';

      -- grant select privileges on the new views --
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_VMODEL_DATASETS      TO PUBLIC';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_VMODEL_INFO          TO PUBLIC';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.RDF_VMODEL_DATASETS      TO PUBLIC';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.RDF_VMODEL_INFO          TO PUBLIC';

      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu111: rdf_vmodel_info: '||SQLERRM); 
    end;

  -- end if rdf network exists --
  end if;
end;
/
show errors;


Rem ALTER existing types to add/change attributes and methods for the new release



Rem GRANT any additional privileges required by MYCSYS for the new release

-- upgrade for patchset too
@@semu112.sql

alter session set current_schema=SYS;