Rem drv: <migrate type="schema_upgrade" version="11.2.0.0"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/masking/dm_schema_upgrade.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $
Rem
Rem dm_schema_upgrade.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates.All rights reserved. 
Rem
Rem    NAME
Rem      dm_schema_upgrade.sql - Data Masking schema definition.
Rem
Rem    DESCRIPTION
Rem      This is a copy of the entries in dm_types.sql and dm_tables.sql,
Rem      which are both located in ../../latest/masking/.  This is used
Rem      when the database is upgraded to 11.2.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kmckeen     01/23/09 - Created
Rem

-- DROP TYPE mgmt_dm_rule_sets;
-- DROP TYPE mgmt_dm_rule_entry;
-- DROP TYPE mgmt_dm_array_list;

CREATE OR REPLACE TYPE mgmt_dm_array_list AS TABLE OF VARCHAR2(128);
/

CREATE OR REPLACE TYPE mgmt_dm_rule_entry AS OBJECT (
      rule_type    VARCHAR2(30), 
      rule_option  VARCHAR2(2),
      rule_low     NUMBER,
      rule_high    NUMBER,
      start_date   DATE,
      end_date     DATE,
      fixed_string VARCHAR2(30), 
      fixed_number NUMBER,
      table_schema VARCHAR2(30),
      table_name   VARCHAR2(30),
      column_name  VARCHAR2(30),
      udf_name     VARCHAR2(512),
      arr_list     mgmt_dm_array_list);
/ 

CREATE OR REPLACE TYPE mgmt_dm_rule_sets AS TABLE OF mgmt_dm_rule_entry;
/

--
-- ER diagram for 10.2.0.5 GC and 11GC
-- 
-- MGMT_DM_SCOPESPECS(SS_GUID) <---* MGMT_DM_SS_COLUMNS
-- MGMT_DM_SS_COLUMNS(SS_GUID,SCHEMA,TABLE,COLUMN)) <---* MGMT_DM_COLUMN_RULES *---> MGMT_DM_RULETEMPLATES(RULE_GUID)
-- MGMT_DM_RULETEMPLATES(RULE_GUID) <---* MGMT_DM_RULEENTRY(RULE_GUID)
-- MGMT_DM_SS_COLUMNS <---* MGMT_DM_INFCONS_COLUMNS
-- MGMT_DM_RULEENTRY <---* MGMT_DM_ALITEMS
-- MGMT_DM_SCOPESPECS(SS_GUID) <---* MGMT_DM_JOB_EXECUTIONS
-- 

/*************************************

drop sequence dm_maptbl_seq;

drop sequence dm_rule_name_seq;

drop sequence dm_column_group_seq;

drop table MGMT_DM_COLUMN_RULES;

drop table MGMT_DM_INFCONS_COLUMNS;

drop table MGMT_DM_SS_COLUMNS;

drop table MGMT_DM_ALITEMS;

drop table MGMT_DM_RULEENTRY;

drop table MGMT_DM_RULETEMPLATES;

drop table MGMT_DM_JOB_EXECUTIONS;

drop table MGMT_DM_SCOPESPECS;

**************************************/

CREATE TABLE MGMT_DM_SCOPESPECS
(
  SS_GUID	RAW(16) DEFAULT SYS_GUID(),
  SS_OWNER	VARCHAR2(30) NOT NULL,  
  SS_NAME	VARCHAR2(40) NOT NULL,
  SOURCE_ID	RAW(16) NOT NULL,  -- Target GUID of database
  SOURCE_NAME	VARCHAR2(256) NOT NULL,  -- Name of database
  DESCRIPTION   VARCHAR2(2000),    -- description of DM scope
  MODIFY_DATE	DATE DEFAULT SYSDATE, -- mask defn last modified date
  SCRIPT_DATE	DATE DEFAULT SYSDATE, -- script generation date
  DISABLE_LOGS        VARCHAR2(1) DEFAULT 'Y' NOT NULL,  -- disable logging during masking ('Y' or 'N')
  REFRESH_STATS       VARCHAR2(1) DEFAULT 'Y' NOT NULL,  -- refresh stats after masing ('Y' or 'N')
  DROP_TEMP_TABLES    VARCHAR2(1) DEFAULT 'Y' NOT NULL,  -- drop temp tables ('Y' or 'N')
  PARALLEL_DEGREE     VARCHAR2(10),      -- parallel execution (DEFAULT, -1=>disabled, nn)
  DM_FLAGS            NUMBER DEFAULT 0,  -- reserved for future use
  POST_MASK_SCRIPT   CLOB,
  FULL_SCRIPT   CLOB,
  CONSTRAINT MGMT_DM_SCOPESPECS_PK PRIMARY KEY (SS_GUID),
  CONSTRAINT MGMT_DM_SCOPESPECS_UC 
    UNIQUE (SS_OWNER,SS_NAME)
) MONITORING
LOB (POST_MASK_SCRIPT) STORE AS ( DISABLE STORAGE IN ROW CHUNK 8192 )
LOB (FULL_SCRIPT) STORE AS ( DISABLE STORAGE IN ROW CHUNK 8192 );

CREATE TABLE MGMT_DM_RULETEMPLATES
(
  RULE_GUID	RAW(16) DEFAULT SYS_GUID(),
  RULE_OWNER	VARCHAR2(30) NOT NULL,  
  RULE_NAME	VARCHAR2(40) NOT NULL,
  DESCRIPTION   VARCHAR2(2000),    -- description of DM rule template
  OUTPUT_TYPE   NUMBER DEFAULT 0 NOT NULL, -- column data type of masked col 
  IS_LIBRARY    NUMBER DEFAULT 0 NOT NULL, -- if the rule is a library template
  RULE_ORDER NUMBER, -- Support conditional masking
  RULE_CONDITION VARCHAR2(2000), -- Support conditional masking
  CONSTRAINT MGMT_DM_RULETEMPLATES_PK PRIMARY KEY (RULE_GUID),
  CONSTRAINT MGMT_DM_RULETEMPLATES_UC 
    UNIQUE (RULE_OWNER, RULE_NAME)
) MONITORING;

-- In 11g the rules are seperately stored hence two parent tables for FK.
CREATE TABLE MGMT_DM_RULEENTRY
(
  RULE_GUID	RAW(16) NOT NULL,
  ENTRY_ORDER   NUMBER NOT NULL, 
  RULE_TYPE     VARCHAR2(30), 
  RULE_OPTION   VARCHAR2(2),
  RULE_LOW      NUMBER,
  RULE_HIGH     NUMBER,
  START_DATE    DATE,
  END_DATE      DATE,
  FIXED_STRING  VARCHAR2(30), 
  FIXED_NUMBER  NUMBER, 
  TABLE_SCHEMA  VARCHAR2(30),
  TABLE_NAME    VARCHAR2(30),
  COLUMN_NAME   VARCHAR2(30),
  UDF_NAME      VARCHAR2(512), -- user defined func name
  CONSTRAINT MGMT_DM_RULEENTRY_PK PRIMARY KEY (RULE_GUID, ENTRY_ORDER),
  CONSTRAINT MGMT_DM_RULEENTRY_FK
    FOREIGN KEY (RULE_GUID) 
    REFERENCES MGMT_DM_RULETEMPLATES (RULE_GUID)
    ON DELETE CASCADE
) MONITORING;

CREATE TABLE MGMT_DM_SS_COLUMNS
(
  SS_GUID	RAW(16) NOT NULL,
  RULE_GUID     RAW(16), -- Deprecated in 11g. Use MGMT_DM_COLUMN_RULES.RULE_GUID
  TABLE_SCHEMA	VARCHAR2(30) NOT NULL, 
  TABLE_NAME	VARCHAR2(128) NOT NULL, -- Do we handle long name???
  COLUMN_NAME	VARCHAR2(128) NOT NULL,
  COLUMN_GROUP  VARCHAR2(30),
  CONSTRAINT MGMT_DM_SS_COLUMNS_FK1
    FOREIGN KEY (SS_GUID) 
    REFERENCES MGMT_DM_SCOPESPECS (SS_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_DM_SS_COLUMNS_UC
    UNIQUE (SS_GUID, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
) MONITORING;

-- This table is for rules that used for columns, not library
CREATE TABLE MGMT_DM_COLUMN_RULES
(
  SS_GUID	RAW(16) NOT NULL,
  TABLE_SCHEMA	VARCHAR2(30) NOT NULL, 
  TABLE_NAME	VARCHAR2(128) NOT NULL, -- Do we handle long name???
  COLUMN_NAME	VARCHAR2(128) NOT NULL,
  RULE_GUID RAW(16) NOT NULL,
  CONSTRAINT MGMT_DM_COLUMN_RULES_FK
    FOREIGN KEY (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
    REFERENCES MGMT_DM_SS_COLUMNS (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_DM_COLUMN_RULES_FK2 
    FOREIGN KEY (RULE_GUID)
    REFERENCES MGMT_DM_RULETEMPLATES (RULE_GUID)
    ON DELETE CASCADE
) MONITORING;

CREATE INDEX MGMT_DM_COLUMN_RULES_IX1 on MGMT_DM_COLUMN_RULES(SS_GUID);
CREATE INDEX MGMT_DM_COLUMN_RULES_IX2 on MGMT_DM_COLUMN_RULES(RULE_GUID);

CREATE TABLE MGMT_DM_INFCONS_COLUMNS
(
  SS_GUID	RAW(16) NOT NULL,
  TABLE_SCHEMA	VARCHAR2(30) NOT NULL, 
  TABLE_NAME	VARCHAR2(128) NOT NULL,
  COLUMN_NAME	VARCHAR2(128) NOT NULL,
  PARENT_SCHEMA	VARCHAR2(30) NOT NULL, 
  PARENT_TABLE	VARCHAR2(128) NOT NULL,
  PARENT_COLUMN	VARCHAR2(128) NOT NULL,
  CONSTRAINT MGMT_DM_INFCONS_COLUMNS_FK1
    FOREIGN KEY (SS_GUID,PARENT_SCHEMA,PARENT_TABLE,PARENT_COLUMN) 
    REFERENCES MGMT_DM_SS_COLUMNS (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_DM_INFCONS_COLUMNS_UC
    UNIQUE (SS_GUID,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
) MONITORING;

CREATE TABLE MGMT_DM_ALITEMS
(
  RULE_GUID	 RAW(16) NOT NULL,
  ENTRY_ORDER    NUMBER NOT NULL,  
  ARRAYLIST_ITEM VARCHAR2(128),  -- do we store array list item order?
  CONSTRAINT MGMT_DM_ALITEMS_PK PRIMARY KEY (RULE_GUID, ENTRY_ORDER, ARRAYLIST_ITEM),
  CONSTRAINT MGMT_DM_ALITEMS_FK
    FOREIGN KEY (RULE_GUID, ENTRY_ORDER) 
    REFERENCES MGMT_DM_RULEENTRY (RULE_GUID, ENTRY_ORDER)
    ON DELETE CASCADE
) MONITORING;

CREATE TABLE MGMT_DM_JOB_EXECUTIONS
(
  SS_GUID	RAW(16),
  EXECUTION_ID	RAW(16),  
  SUBMISSION_TS	DATE DEFAULT SYSDATE,
  CONSTRAINT MGMT_DM_JOB_EXEC_PK PRIMARY KEY (SS_GUID,EXECUTION_ID),
  CONSTRAINT MGMT_DM_JOB_EXEC_FK1 
    FOREIGN KEY (SS_GUID) 
    REFERENCES MGMT_DM_SCOPESPECS (SS_GUID)
    ON DELETE CASCADE
) MONITORING;

CREATE SEQUENCE dm_maptbl_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE SEQUENCE dm_rule_name_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE SEQUENCE dm_column_group_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;