Rem
Rem $Header: sdo/admin/semrelod111.sql /st_sdo_11.2.0/1 2011/04/29 12:19:52 sdas Exp $
Rem
Rem semrelod111.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      semrelod111.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    04/05/11 - add model view and trigger re-creation
Rem    matperry    05/26/09 - Created
Rem

set echo off
WHENEVER SQLERROR EXIT FAILURE;

---------------------------------------------------------
-- This procedure reloads 11.1 PL/SQL packages.        --
-- It should be run after running seme111 in an 11.2   --
-- database and then downgrading from 11.2 to 11.1     --
---------------------------------------------------------

prompt check privileges
Rem check user
begin
  if ('SYS' = sys_context('userenv', 'current_user') and 
      'TRUE' = sys_context('userenv', 'isdba')) then
    null; -- ok to go ahead
  else
    raise_application_error(-20000, 
    'Cannot proceed. Only SYS with DBA privilege can perform this operation.');
  end if;
end;
/
show errors;

-- Check database and raise appropriate errors --
-- We can only do reload if 11.1 types are present and 10.2 and
-- 11.2 components are not present
declare
  type_102_exists    NUMBER;
  type_11_exists     NUMBER;
  type_112_exists    NUMBER;
begin
  -- 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 components are present --
  SELECT count(*) INTO type_112_exists FROM ALL_OBJECTS
   WHERE object_name='RDF_PARAMETER' AND owner='MDSYS';

  if (type_102_exists > 0) then
    raise_application_error(-20000, 
      'Cannot proceed. Types exist from Semantic Technologies Release 10.2. ' ||
      'Please use catsem10i.sql to upgrade to Semantic Technologies Release 11.1.');
  elsif (type_11_exists = 0) then
    raise_application_error(-20000, 
      'Cannot proceed. Semantic Technologies has not been installed. ' ||
      'Please use catsem11i.sql to install Semantic Technologies Release 11.1.');
  elsif (type_112_exists > 0) then
    raise_application_error(-20000, 
      'Cannot proceed. Semantic Technologies Release 11.2 components exist.');
  end if;
end;
/
show errors;



set echo off
alter session set current_schema=MDSYS;

WHENEVER SQLERROR CONTINUE;

prompt Reload 11.1 Semantic Technologies ...

Rem RDF Match stuff
@@sdordfa.sql

Rem Load RDF types and packages
@@sdordfh.sql
@@sdordfxh.sql
-- do not reload types
-- @@sdordfty.sql
@@prvrdftp.plb


Rem Load SEM context 
@@sdosemctxh.sql

Rem Load SEM log 
@@sdosemlogh.sql

Rem Load SEM utility 
@@sdosemutlh.sql

Rem Load SEM owlfast inference 
@@sdoseminfh.sql
@@sdoseminfhh.sql
@@sdoseminfih.sql

Rem Load SEM owlfast validation 
@@sdosemvalh.sql

Rem MS related
prompt sdoseminfmh
@@sdoseminfmh.plb

prompt sdordfai
@@sdordfai.plb

Rem MS related
prompt sdoseminfmr
@@sdoseminfmr.plb

show errors;

prompt sdordfm
@@sdordfm.plb

show errors;

prompt sdordfrb
@@sdordfrb.sql

Rem Load RDF package body
@@sdordfb.plb
@@sdordfxb.plb

Rem Add context: 
CREATE OR REPLACE CONTEXT SDO_SEM_CTX using MDSYS.SDO_SEM_CTX accessed globally;


Rem Load context implementations
@@sdosemctxb.plb

Rem Load Log implementations
@@sdosemlogb.plb

Rem Load SEM implementations
@@sdosemutlb.plb
@@sdoseminfb.plb
@@sdoseminfhb.plb
@@sdoseminfib.plb
@@sdosemvalb.plb

Rem SEM operators
@@sdosemopih.plb
@@sdosemopib.plb
@@sdosemop.plb
@@sdosemopst.plb

Rem SEM Triggers
@@sdosemtrig.plb

REM RDF upgrade and data migration
@@sdosemdmh.sql
@@sdosemdmb.plb

REM RDF/OWL downgrade preparation package
@@sdosemdgh.sql
@@sdosemdgb.plb
@@sdosemdgu.plb

alter session set current_schema=SYS;

-- load sdordf
call dbms_java.loadjava(
  '-resolve -force -synonym -schema MDSYS -grant PUBLIC md/jlib/sdordf.jar');

-- recreate any link$ views and app table triggers --
alter session set current_schema=MDSYS;

declare
  -- view re-creation vars
  network_exists   number;
  type             cursor_type is ref cursor;
  query_crs        cursor_type;
  mName            varchar2(32);
  mID              number;
  clnMName         varchar2(32);
  dyn_sql_stmt     varchar2(4000);

  -- trigger re-creation vars 
  model                VARCHAR2(25);
  m_id2                NUMBER := NULL;
  rdf_table            VARCHAR2(30);
  rdf_column           VARCHAR2(30);
  user_name            VARCHAR2(30);
  create_trig          VARCHAR2(5000);
  trig_num             NUMBER := 0;

  -- vars to hold security-checked values
  owner                VARCHAR2(32);
  rdf_tab              VARCHAR2(32);
  rdf_col              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

      -------------- models -----------------
      open query_crs for 'select model_name, model_id
			  from mdsys.rdf_model$
			  where model_id > 0';

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

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

	------------- recreate the semm and rdfm views --------------
	dyn_sql_stmt := '
	  CREATE OR REPLACE VIEW RDFM_' || clnMName || '
	    as select * from rdf_link$ partition (model_' || to_char(mID,'TM9') || ') 
	';
	EXECUTE IMMEDIATE dyn_sql_stmt;

	dyn_sql_stmt := '
	  CREATE OR REPLACE VIEW SEMM_' || clnMName || '
	    as select * from rdf_link$ partition (model_' || to_char(mID,'TM9') || ') 
	';
	EXECUTE IMMEDIATE dyn_sql_stmt;
 
        ---------------------- re-create triggers ------------------
	-- validate input --
	model := UPPER(TRIM(DBMS_ASSERT.QUALIFIED_SQL_NAME(mName)));

	-- find the current name of: app table col, app table
	EXECUTE IMMEDIATE '
	  SELECT owner,table_name,column_name,model_id FROM mdsys.rdf_model$
	  WHERE model_name=:1
	' INTO user_name,rdf_table,rdf_column,m_id2 USING model;

	owner := DBMS_ASSERT.ENQUOTE_NAME(user_name, FALSE);
	rdf_tab := DBMS_ASSERT.ENQUOTE_NAME(rdf_table, FALSE);
	rdf_col := DBMS_ASSERT.ENQUOTE_NAME(rdf_column, FALSE);

	-- 1. create trigger on app table to notify of dml to model
	trig_num := 1;
	create_trig := '
    CREATE OR REPLACE TRIGGER ' || model || '_DML 
      BEFORE INSERT OR UPDATE OF ' || rdf_col || ' OR DELETE 
      ON ' || owner || '.' || rdf_tab || ' 
    BEGIN
      IF DELETING THEN
	MDSYS.RDF_APIS_INTERNAL.NOTIFY_MODEL_DML(''' || model || ''', ''DEL'');
      ELSIF INSERTING THEN
	MDSYS.RDF_APIS_INTERNAL.NOTIFY_MODEL_DML(''' || model || ''', ''INS'');
      ELSIF UPDATING THEN
	MDSYS.RDF_APIS_INTERNAL.NOTIFY_MODEL_DML(''' || model || ''', ''UPD'');
      ELSE
	NULL;
      END IF;
    END;
	';
	EXECUTE IMMEDIATE create_trig;

	-- 2. set (uniqueness) constraint on the app table: UNIQUE <m,s,p,c>, count
	--    ON DELETE and UPDATE remove LINK_ID from MDSYS.RDF_LINK$ --
	--    determine number of rows with rdf_t_id (cost); delete if == 1;
	--    decrement cost by 1, if cost > 1
	trig_num := 2;
	create_trig := '
    CREATE OR REPLACE TRIGGER ' || model || '_DEL 
      AFTER UPDATE OF ' || rdf_col || ' OR DELETE 
      ON ' || owner || '.' || rdf_tab || '
      FOR EACH ROW
    BEGIN
      IF :OLD.' || rdf_col || ' IS NULL THEN RETURN; END IF;
      MDSYS.SDO_RDF_INTERNAL.decr_link_cost_del_if_zeroed(
	:OLD.' || rdf_col || '.rdf_m_id
      , :OLD.' || rdf_col || '.rdf_s_id
      , :OLD.' || rdf_col || '.rdf_p_id
      , :OLD.' || rdf_col || '.rdf_c_id
      );
    END;
	';
	EXECUTE IMMEDIATE create_trig ;

	-- 3. Create <model>_INS before ins/upd trigger for
	--    a) ensuring RDF_M_ID (determined using user-specified model_name) is
	--       same as model_id of model assoc'd with target app-table's app-col
	--    b) inserting into RDF_LINK$ partition and RDF_VALUE$ (after
	--       transforming from the intermdiate object value)
	trig_num := 3;
	create_trig := '
    create or replace trigger ' || model || '_INS ' || '
      before insert or update of ' || rdf_col || '
      on ' || owner || '.' || rdf_tab || '
      FOR EACH row
    DECLARE
      cov_id               NUMBER;
      new_sv               BOOLEAN := FALSE;
      new_ov               BOOLEAN := FALSE;
      new_pv               BOOLEAN := FALSE;
      pl_id                NUMBER;
      batch_mode           BOOLEAN := FALSE;
    BEGIN
      if (:new.' || rdf_col || ' IS NULL) THEN RETURN; END IF;

      if abs(:new.' || rdf_col || '.RDF_M_ID) <> ' || to_char(m_id2) || ' THEN
	MDSYS.MDERR.RAISE_MD_ERROR(''MD'',''SDO'',-55316,
	  abs(:new.' || rdf_col || '.RDF_M_ID), ' || to_char(m_id2) || ');
      end if; 

      -- 6. inserts to rdf_link$ table --
      IF (:new.' || rdf_col || '.RDF_M_ID < 0) THEN 
	batch_mode := TRUE; 
	:new.' || rdf_col || '.RDF_M_ID := (-1)* :new.' || rdf_col || '.RDF_M_ID;
      END IF;
      IF (:new.' || rdf_col || '.RDF_S_ID < 0) THEN 
	new_sv := TRUE;
	:new.' || rdf_col || '.RDF_S_ID := (-1)* :new.' || rdf_col || '.RDF_S_ID;
      END IF;
      IF (:new.' || rdf_col || '.RDF_P_ID < 0) THEN 
	new_pv := TRUE;
	:new.' || rdf_col || '.RDF_P_ID := (-1)* :new.' || rdf_col || '.RDF_P_ID;
      END IF;
      IF (:new.' || rdf_col || '.RDF_O_ID < 0) THEN 
	new_ov := TRUE;
	:new.' || rdf_col || '.RDF_O_ID := (-1)* :new.' || rdf_col || '.RDF_O_ID;
      END IF;
      cov_id := :new.' || rdf_col || '.RDF_C_ID;

      pl_id := NULL;
      IF (NOT batch_mode) THEN
	SDO_RDF_INTERNAL.PARSE_TRIPLE (
	  :new.' || rdf_col || '.RDF_M_ID,
	  :new.' || rdf_col || '.RDF_P_ID,
	  :new.' || rdf_col || '.RDF_S_ID,
	  :new.' || rdf_col || '.RDF_O_ID,
	  cov_id, new_sv, new_ov, new_pv, pl_id);
      ELSE
	--EXECUTE IMMEDIATE ''ALTER SESSION SET SQL_TRACE=TRUE'';
	SDO_RDF_INTERNAL.PARSE_TRIPLE_BATCH_MODE (
	  :new.' || rdf_col || '.RDF_M_ID,
	  :new.' || rdf_col || '.RDF_P_ID,
	  :new.' || rdf_col || '.RDF_S_ID,
	  :new.' || rdf_col || '.RDF_O_ID,
	  cov_id, new_sv, new_ov, new_pv, pl_id);
	--EXECUTE IMMEDIATE ''ALTER SESSION SET SQL_TRACE=FALSE'';
      END IF;
    END;
	';
	EXECUTE IMMEDIATE create_trig;

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	     'SEM_INSTALL: semrelod111: model view and trigger recreation: '||SQLERRM);
          close query_crs;
      end;
      end loop;

      close query_crs;

      ---------------- entailments ---------------
      open query_crs for 'select indexname, model_id
			  from mdsys.rdf_precomp$
			  where model_id > 0';

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

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

	-- recreate the semi and rdfi views
        dyn_sql_stmt := 
	  'create or replace view ' || 
	  SYS.DBMS_ASSERT.qualified_sql_name('"MDSYS"."RDFI_' || upper(clnMName) || '"') ||  
	  ' as select * from rdf_link$ partition(MODEL_'||to_char(mID,'TM9')||')';
        EXECUTE IMMEDIATE dyn_sql_stmt;
        
        dyn_sql_stmt := 
	  'create or replace view ' || 
	  SYS.DBMS_ASSERT.qualified_sql_name('"MDSYS"."SEMI_' || upper(clnMName) || '"') || 
	  ' as select * from rdf_link$ partition(MODEL_'||to_char(mID,'TM9')||')';
        EXECUTE IMMEDIATE dyn_sql_stmt;

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	     'SEM_INSTALL: semrelod111: entailment view recreation: '||SQLERRM);
          close query_crs;
      end;
      end loop;

      close query_crs;

    end if;

    exception when others then
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	 'SEM_INSTALL: semrelod111: view/trigger recreation: '||SQLERRM);

end;
/
show errors;

-- recompile any invalid objects --
alter session set current_schema=MDSYS;

declare
  obj   varchar2(32);
  otype varchar2(19);
  type       cursor_type is REF CURSOR;
  query_crs  cursor_type ;
  stmt varchar2(2000);
begin

  -- try to compile any invalid objects --
  stmt :=
    'select
       OBJECT_NAME, OBJECT_TYPE
     from
       all_objects
     where OWNER = ''MDSYS'' 
       and status=''INVALID''
       and ( 
           object_name like ''RDF_%''     or
           object_name like ''SDO_RDF%''  or
           object_name like ''SDO_SEM%''  or
           object_name like ''SEM_%''     or
           object_name like ''SEMR_%''    or
           object_name like ''RDFR_%''     
       )';

  open query_crs for stmt;
  loop 
  begin
    fetch query_crs into  obj, otype;
    exit when query_crs%NOTFOUND ;

    begin
      if (otype = 'PACKAGE BODY') then
        obj := sys.dbms_assert.enquote_name(obj,FALSE);
        execute immediate ' alter package '|| obj ||' compile body';
      elsif (otype = 'TYPE BODY') then
        obj := sys.dbms_assert.enquote_name(obj,FALSE);
        execute immediate ' alter type '|| obj ||' compile body';
      elsif (otype = 'TRIGGER') then
        obj := sys.dbms_assert.enquote_name(obj,FALSE);
        execute immediate ' alter trigger '|| obj ||' compile';
      elsif (otype = 'VIEW') then
        obj := sys.dbms_assert.enquote_name(obj,FALSE);
        execute immediate ' alter view '|| obj ||' compile';
      else
        null; -- no action for now
      end if;
    exception
      when others then
        null;
    end;
    commit;
  end;
  end loop;
  close query_crs;
end;
/
show errors;

alter session set current_schema=SYS;

prompt Reload 11.1 Semantic Technologies ... DONE