Rem drv: <create type="tables"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/masking/dm_tables.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $
Rem
Rem dm_tables.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dm_tables.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    vgoli       11/05/08 - add parallel_degree column to mgmt_dm_scopespecs
Rem    kmckeen     09/04/08 - Add sequences for rule_name and column_group
Rem    rpattabh    08/26/08 - 
Rem    vgoli       06/18/08 - add columns for advanced options
Rem    rpattabh    06/12/08 - Compound Masking Changes
Rem    rpattabh    05/16/08 - Project 25894: Conditional Masking
Rem    xshen       02/05/08 - 
Rem    rpattabh    01/23/08 - remove basicfile
Rem    rpattabh    01/01/08 - Add post mask script
Rem    rpattabh    11/29/07 - Project 25671: Masking integration with Clone
Rem    xshen       11/27/07 - conditional masking
Rem    xshen       08/17/07 - drop fk constraint
Rem    xshen       12/03/06 - add masking job table
Rem    xshen       09/20/06 - adding arraylist item table
Rem    xshen       07/17/06 - add library flag 
Rem    rpattabh    06/19/06 - add drop statements. 
Rem    lhan        05/24/06 - add sequence
Rem    xshen       05/10/06 - 
Rem    rpattabh    04/20/06 - modify schema
Rem    lhan        04/02/06 - Created
Rem

--
-- 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;