Rem
Rem $Header: cm_tables.sql 24-aug-2005.09:02:20 abodge Exp $
Rem
Rem cm_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      cm_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      This is the table script for change manager in 10.2
Rem      To avoid referencing the latest table script, this is 
Rem      maintained in upgrade and to be in sync with the latest
Rem      up until we ship 10.2.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    abodge      08/24/05 - Fix 10.1->10.2 Release upgrade problems 
Rem    abodge      06/01/05 - Add public synonym support 
Rem    lhan        05/16/05 - add annotation 
Rem    xshen       03/27/05 - xshen_changemgr_initora-0324
Rem    abodge      03/23/05 - Store DB version with baseline versions 
Rem    abodge      03/18/05 - Add mgmt_cm_baseline_params 
Rem    lhan        02/17/05 - fix bug 4192287
Rem    abodge      11/18/04 - Add version_status column to versions tables 
Rem    abodge      10/15/04 - Increase size of OBJECT_NAME col for comp objects
Rem    abodge      10/01/04 - Add unique constraint on version cols 
Rem    xshen       09/23/04 - adding description column to comparison table 
Rem    lhan        09/14/04 - lhan_cm_0914
Rem    lhan        09/14/02 - Created
Rem

Rem
Rem This script requires the following arguments
Rem  1. Name of the ecm_depot tablespace where all LOBs are stored
Rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"

-- Scope specification

CREATE TABLE MGMT_CM_SCOPESPECS
(
  SS_GUID	RAW(16),
  OBJECT_TYPES	NUMBER NOT NULL,
  FLAGS		NUMBER DEFAULT 0 NOT NULL,
  CONSTRAINT MGMT_CM_SCOPESPECS_PK PRIMARY KEY (SS_GUID)
) MONITORING;

CREATE TABLE MGMT_CM_SCOPESPEC_NAMES
(
  SS_GUID	RAW(16) NOT NULL,
  MATCH_TYPES	NUMBER DEFAULT 0 NOT NULL,
  SCHEMA        VARCHAR2(30),  -- For specific objects
  NAME		VARCHAR2(30) NOT NULL,
  LONGNAME	CLOB,  -- For objects such as Java classes
  CONSTRAINT MGMT_CM_SCOPESPEC_NAMES_FK
    FOREIGN KEY (SS_GUID) REFERENCES MGMT_CM_SCOPESPECS (SS_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_SCOPESPEC_NAMES_UC 
    UNIQUE (SS_GUID, MATCH_TYPES, SCHEMA, NAME)
)
  LOB (LONGNAME) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

-- Baselines

CREATE TABLE MGMT_CM_BASELINES
(
  BASELINE_GUID	RAW(16) DEFAULT SYS_GUID(),
  BASELINE_OWNER	VARCHAR2(30) NOT NULL,  -- or GUID?
  BASELINE_NAME	VARCHAR2(40) NOT NULL,  -- Need 40 chars to fit temp BL name
  BASELINE_SS	RAW(16) NOT NULL,
  SOURCE_ID	RAW(16) NOT NULL,  -- Target GUID of database
  SOURCE_NAME	VARCHAR2(256) NOT NULL,  -- Name of database
  JOB_ID        RAW(16),
  DESCRIPTION   VARCHAR2(2000),    -- description of baseline
  TEMP_BL       NUMBER(1) DEFAULT 0,  -- 0: permanent, 1: temporary
  CONSTRAINT MGMT_CM_BASELINES_PK PRIMARY KEY (BASELINE_GUID),
  CONSTRAINT MGMT_CM_BASELINES_UC 
    UNIQUE (BASELINE_OWNER, BASELINE_NAME)
) MONITORING;

CREATE TABLE MGMT_CM_BASELINE_VERSIONS
(
  BASELINE_GUID	RAW(16) NOT NULL,
  BASELINE_VERSION	NUMBER NOT NULL,
  CAPTURE_TIME          DATE,
  TIMEZONE_REGION       VARCHAR2(64),
  DB_VERSION            VARCHAR2(20),
  JOB_EXECUTION_ID      RAW(16),
  VERSION_STATUS        VARCHAR(3),
  DDL                   BLOB,
  DDL_STATUS            NUMBER DEFAULT -999 NOT NULL,
  DDL_TIME              DATE,
  CONSTRAINT MGMT_CM_BASELINE_VERSIONS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_VERSIONS_UC 
    UNIQUE(BASELINE_GUID, BASELINE_VERSION)
)
  LOB (DDL) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_OBJECTS
(
  BASELINE_GUID	RAW(16) NOT NULL,
  OBJECT_TYPE	NUMBER NOT NULL,
  OBJECT_SCHEMA	VARCHAR2(30),
  OBJECT_NAME	VARCHAR2(128) NOT NULL,
  OBJECT_LONGNAME	CLOB,
  LAST_DDL_TIME         DATE,
  HASH_VALUE    CHAR(32),
  FIRST_VERSION	NUMBER NOT NULL,
  LAST_VERSION	NUMBER DEFAULT 99999 NOT NULL,
  DEFINITION	BLOB, -- NOT NULL,
  CONSTRAINT MGMT_CM_BASELINE_OBJECTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_OBJECT_UC
    UNIQUE (BASELINE_GUID, OBJECT_TYPE, 
            OBJECT_SCHEMA, OBJECT_NAME, FIRST_VERSION)
)
  LOB (OBJECT_LONGNAME) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING,
  LOB (DEFINITION) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

-- dependents such as comments, object grants, etc.
CREATE TABLE MGMT_CM_BASELINE_DEPENDENTS
(
  BASELINE_GUID         RAW(16) NOT NULL,
  BASE_OBJECT_TYPE	NUMBER NOT NULL,
  BASE_OBJECT_SCHEMA	VARCHAR2(30),
  BASE_OBJECT_NAME	VARCHAR2(128) NOT NULL,
  DEPENDENT_TYPE        NUMBER NOT NULL,
  HASH_VALUE            CHAR(32),
  FIRST_VERSION         NUMBER NOT NULL,
  LAST_VERSION          NUMBER DEFAULT 99999 NOT NULL,
  DEFINITION            BLOB, -- NOT NULL,
  CONSTRAINT MGMT_CM_BASELINE_DEPENDENTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_DEPEND_UC
    UNIQUE (BASELINE_GUID, BASE_OBJECT_TYPE, BASE_OBJECT_SCHEMA,
            BASE_OBJECT_NAME, DEPENDENT_TYPE, HASH_VALUE, FIRST_VERSION)
)
  LOB (DEFINITION) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_OBJGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  OBJ_OWNER     VARCHAR2(30) NOT NULL,
  OBJ_NAME      VARCHAR2(30) NOT NULL,
  COL_NAME      VARCHAR2(30),
  GRANTOR_NAME  VARCHAR2(30) NOT NULL,
  PRIVILEGE     VARCHAR2(40) NOT NULL,
  FLAG          NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_OBJGRANTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_OBJGRANTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, OBJ_OWNER, 
         OBJ_NAME, COL_NAME, GRANTOR_NAME, PRIVILEGE, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_SYSGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  PRIVILEGE     VARCHAR2(40) NOT NULL,
  FLAG          NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_SYSGRANTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_SYSGRANTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, PRIVILEGE, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_ROLEGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  ROLEPRIV      VARCHAR2(30) NOT NULL,
  FLAG          NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_ROLEGRANTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_ROLEGRANTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, ROLEPRIV, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_QUOTAGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  TABLESPACE    VARCHAR2(30) NOT NULL,
  MAXBYTES      NUMBER,
  CONSTRAINT MGMT_CM_BASELINE_QUOTGRNNTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_QUOTGRNNTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, TABLESPACE, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_PROXYGRANTS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  PROXY_USER    VARCHAR2(30) NOT NULL,
  DIRECTION     NUMBER,  -- MGMT_CM.PROXY_USER or PROXIED_FOR_USER
  CONSTRAINT MGMT_CM_BASELINE_PROXGRNTS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_PROXGRNTS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, PROXY_USER, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_CONS_GROUPS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  GRANTEE_NAME  VARCHAR2(30) NOT NULL,
  CONS_GROUP    VARCHAR2(30) NOT NULL,
  FLAG          NUMBER,  
  CONSTRAINT MGMT_CM_BASELINE_CONSGRPS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_CONSGRPS_UC
    UNIQUE (BASELINE_GUID, GRANTEE_NAME, CONS_GROUP, FIRST_VERSION)
) MONITORING
;

CREATE TABLE MGMT_CM_BASELINE_INIT_PARAMS
(
  BASELINE_GUID RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  PARAM_NAME    VARCHAR2(64) NOT NULL,
  PARAM_VALUE   VARCHAR2(512),
  CONSTRAINT MGMT_CM_BASELINE_PARAMS_FK
    FOREIGN KEY (BASELINE_GUID) 
    REFERENCES MGMT_CM_BASELINES (BASELINE_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_BASELINE_PARAMS_UC
    UNIQUE (BASELINE_GUID, PARAM_NAME, FIRST_VERSION)
) MONITORING
;

-- Comparisons

CREATE TABLE MGMT_CM_COMPARISONS
(
  COMPARISON_GUID	RAW(16) DEFAULT SYS_GUID(),
  COMPARISON_OWNER	VARCHAR2(30) NOT NULL,  -- or GUID?
  COMPARISON_NAME	VARCHAR2(30) NOT NULL,
  COMPARISON_SS	RAW(16) NOT NULL,
  DESCRIPTION           VARCHAR2(2000),    -- description of comparison
  JOB_ID                RAW(16),
  LEFT_TYPE		VARCHAR2(1) NOT NULL, -- 'B' OR 'D'
  LEFT_ID		RAW(16) NOT NULL,
  LEFT_NAME		VARCHAR2(256) NOT NULL,
  LEFT_VERSION	NUMBER, -- When source is a baseline
  RIGHT_TYPE		VARCHAR2(1) NOT NULL, -- 'B' OR 'D'
  RIGHT_ID		RAW(16) NOT NULL,
  RIGHT_NAME		VARCHAR2(256) NOT NULL,
  RIGHT_VERSION	NUMBER, -- When source is a baseline
  CONSTRAINT MGMT_CM_COMPARISONS_PK PRIMARY KEY (COMPARISON_GUID),
  CONSTRAINT MGMT_CM_COMPARISONS_UC 
    UNIQUE (COMPARISON_OWNER, COMPARISON_NAME)
) MONITORING;

CREATE TABLE MGMT_CM_COMPARISON_VERSIONS
(
  COMPARISON_GUID		RAW(16) NOT NULL,
  COMPARISON_VERSION	NUMBER NOT NULL, -- Comparison version
  JOB_EXECUTION_ID      RAW(16),
  VERSION_STATUS        VARCHAR(3),
  LEFT_VERSION		NUMBER,  -- Actual baseline version used
  RIGHT_VERSION		NUMBER,  -- Actual baseline version used
  COMPARISON_TIME	DATE,
  TIMEZONE_REGION       VARCHAR2(64),
  CONSTRAINT MGMT_CM_COMPARISON_VERSIONS_FK
    FOREIGN KEY (COMPARISON_GUID) 
    REFERENCES MGMT_CM_COMPARISONS (COMPARISON_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_COMPARISON_VERSIONS_UC 
    UNIQUE(COMPARISON_GUID, COMPARISON_VERSION)
) MONITORING;

CREATE TABLE MGMT_CM_COMPARISON_OBJECTS
(
  COMPARISON_GUID	RAW(16) NOT NULL,
  OBJECT_TYPE		NUMBER NOT NULL,
  OBJECT_SCHEMA         VARCHAR2(30),  -- Left-side schema
  OBJECT_NAME		VARCHAR2(128) NOT NULL,  -- Left-side name
  OBJECT_LONGNAME	CLOB,
  FIRST_VERSION         NUMBER NOT NULL,
  LAST_VERSION          NUMBER DEFAULT 99999 NOT NULL,
  LAST_LEFT_DDL_TIME    DATE,
  LEFT_HASH_VALUE       CHAR(32),
  LAST_RIGHT_DDL_TIME   DATE,
  RIGHT_HASH_VALUE      CHAR(32),
  GRANTS_DIFF_HASH      CHAR(32),
  STATE	                NUMBER NOT NULL,
  DIFFERENCES		BLOB,
  CONSTRAINT MGMT_CM_COMPARISON_OBJECTS_FK
    FOREIGN KEY (COMPARISON_GUID) 
    REFERENCES MGMT_CM_COMPARISONS (COMPARISON_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_COMPARISON_OBJECT_UC
    UNIQUE (COMPARISON_GUID, OBJECT_TYPE, 
            OBJECT_SCHEMA, OBJECT_NAME, FIRST_VERSION)
)
  LOB (OBJECT_LONGNAME) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING,
  LOB (DIFFERENCES) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE ENABLE STORAGE IN ROW) MONITORING
;

CREATE TABLE MGMT_CM_COMPARISON_INIT_PRMS
(
  COMPARISON_GUID	RAW(16) NOT NULL,
  FIRST_VERSION NUMBER NOT NULL,
  LAST_VERSION  NUMBER DEFAULT 99999 NOT NULL,
  PARAM_NAME    VARCHAR2(64) NOT NULL,
  STATE			NUMBER NOT NULL,
  LEFT_PARAM_VALUE   VARCHAR2(512),
  RIGHT_PARAM_VALUE   VARCHAR2(512),
  CONSTRAINT MGMT_CM_COMPARISON_INITP_FK
    FOREIGN KEY (COMPARISON_GUID) 
    REFERENCES MGMT_CM_COMPARISONS (COMPARISON_GUID)
    ON DELETE CASCADE,
  CONSTRAINT MGMT_CM_COMPARISON_INITP_UC
    UNIQUE (COMPARISON_GUID, PARAM_NAME, FIRST_VERSION)
)
;
 
CREATE TABLE MGMT_CM_SCHEMA_MAPS
(
  OWNER_ID		RAW(16) NOT NULL,
  LEFT_SCHEMA		VARCHAR2(30) NOT NULL,
  RIGHT_SCHEMA	VARCHAR2(30) NOT NULL,
  CONSTRAINT MGMT_CM_SCHMPS_LEFT_UC UNIQUE (OWNER_ID, LEFT_SCHEMA),
  CONSTRAINT MGMT_CM_SCHMPS_RIGHT_UC UNIQUE (OWNER_ID, RIGHT_SCHEMA)
) MONITORING;