--
-- $Header: ocmrepcre.sql 26-dec-00.06:53:17 shuberma Exp $
--
-- ocmrepcre.sql
--
--  Copyright (c) Oracle Corporation 2000. All Rights Reserved.
--
--    NAME
--      ocmrepcre.sql - Change Manager repostiory creation script.
--
--    DESCRIPTION
--      This is the main file for creating the repository objects for the latest
--  version of change manager.
--
--    NOTES
--      <other useful comments, qualifications, etc.>
--
--    MODIFIED   (MM/DD/YY)
--    shuberma    12/26/00 - Naming constraints.
--    lhan        12/13/00 - Fix bug 1459896 - add COMMIT_INTERVAL option
--    dholail     10/25/00 - Adding an index on the history table
--    shuberma    09/18/00 -
--    shuberma    09/15/00 - New Create script.
--    shuberma    09/15/00 - Created
--
CREATE SEQUENCE "VBZ$DB_OBJ_NAMES_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
CREATE SEQUENCE "VBZ$HISTORY_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
CREATE SEQUENCE "VBZ$REP_OBJ_ID_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

CREATE TABLE "VBZ$CHANGE_PLANS" 
(
	"CP_ID" NUMBER NOT NULL, 
	"CP_USER_NAME" VARCHAR2(256) NOT NULL, 
	"CP_NAME" VARCHAR2(50) NOT NULL, 
	"CP_SOURCE_DB" VARCHAR2(100), 
	"CP_DESCRIPTION" VARCHAR2(2000), 
	"CP_CREATE_DATE" DATE, 
	"CP_OPTIONS" NUMBER DEFAULT 0 NOT NULL, 
	"CP_TYPE" NUMBER DEFAULT 0, 
	"CP_LATEST_VERSION_NO" NUMBER, 
	"CP_SCOPE" LONG RAW, 
	CONSTRAINT CP_PK PRIMARY KEY("CP_ID"),
	CONSTRAINT CP_UK UNIQUE("CP_NAME", "CP_TYPE", "CP_USER_NAME")
) ;

CREATE TABLE "VBZ$COMPARISONS"
(
	"COMPARISON_ID" NUMBER,
	"USER_NAME" VARCHAR2(256),
	"NAME" VARCHAR2(50),
	"LEFT_NAME" VARCHAR2(50),
	"LEFT_OWNER" VARCHAR2(40),
	"LEFT_VERSION_NO" NUMBER,
	"RIGHT_NAME" VARCHAR2(50),
	"RIGHT_OWNER" VARCHAR2(40),
	"RIGHT_VERSION_NO" NUMBER,
	"OPTIONS" NUMBER,
	"LATEST_VERSION_NO" NUMBER,
	"SCOPE" LONG RAW,
	"DESCRIPTION" VARCHAR2(2000),
	CONSTRAINT CMP_PK UNIQUE("COMPARISON_ID"),
	CONSTRAINT CMP_UK UNIQUE("USER_NAME", "NAME")
);



CREATE TABLE "VBZ$COMPARISON_RESULTS"
(
	"COMPARISON_ID" NUMBER NOT NULL,
	"VERSION_NO" NUMBER NOT NULL,
	"HISTORY_ID" NUMBER,
	"LAST_MOD_DATE" DATE,
	"COMPARE_DATA" LONG RAW,
    "LEFT_VERSION" NUMBER,
    "RIGHT_VERSION" NUMBER,
	CONSTRAINT CMP_RES_PK PRIMARY KEY("COMPARISON_ID", "VERSION_NO"),
	CONSTRAINT CMP_RES_UK UNIQUE("HISTORY_ID"),
	CONSTRAINT CMP_RES_CMP_FK FOREIGN KEY("COMPARISON_ID") REFERENCES "VBZ$COMPARISONS"("COMPARISON_ID") ON DELETE CASCADE
) ;

CREATE TABLE "VBZ$DB_OBJ_NAMES"
(
	"CP_ID" NUMBER NOT NULL,
	"DB_OBJ_ID" NUMBER NOT NULL,
	"DB_OBJ_TYPE" NUMBER,
	"DB_OBJ_NAME" VARCHAR2(128) NOT NULL,
	"DB_OBJ_SCHEMA" VARCHAR2(30),
	"DB_OBJ_SCOPE" LONG RAW,
	CONSTRAINT ON_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT ON_PK PRIMARY KEY("DB_OBJ_ID"),
	CONSTRAINT ON_UK UNIQUE("CP_ID", "DB_OBJ_TYPE", "DB_OBJ_NAME", "DB_OBJ_SCHEMA")
) ;

CREATE TABLE "VBZ$DESTINATIONS"
(
	"CP_ID" NUMBER NOT NULL,
	"DB_NAME" VARCHAR2(256) NOT NULL,
	"DEST_DESCRIPTION" VARCHAR2(2000),
	"OPTIONS" NUMBER(4) DEFAULT 0 NOT NULL,
	"SCRATCH_TS" VARCHAR2(30),
	"VERSION_NO" NUMBER,
	"DEPLOYMENT_ID" NUMBER NOT NULL,
	"TRANS_STATUS" NUMBER,
	"TRANS_HISTORY_ID" NUMBER,
	"EXEC_STATUS" NUMBER,
	"EXEC_HISTORY_ID" NUMBER,
	"FLAGS" NUMBER,
	"MOD_DATE" DATE,
  "OPTION3_SINCEVERSION" NUMBER DEFAULT 0,
  "COMMIT_INTERVAL" NUMBER DEFAULT 0,
	CONSTRAINT DEST_PK PRIMARY KEY("DEPLOYMENT_ID"),
	CONSTRAINT DEST_UK UNIQUE("CP_ID", "DB_NAME", "VERSION_NO"),
	CONSTRAINT DEST_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE
);


CREATE TABLE "VBZ$DIRECTIVES"
(
	"CP_ID" NUMBER NOT NULL,
	"DB_OBJ_ID" NUMBER NOT NULL,
	"VERSION_NO" NUMBER NOT NULL,
	"DEF_LENGTH" NUMBER DEFAULT 0,
	"DEFINITION" LONG RAW,
	"FLAGS" NUMBER DEFAULT 0,
	CONSTRAINT DIR_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT DIR_PK PRIMARY KEY("DB_OBJ_ID", "VERSION_NO"),
	CONSTRAINT DIR_ON_FK FOREIGN KEY("DB_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE
);

CREATE TABLE "VBZ$EDITED_SCRIPTS"
(
	"DEPLOYMENT_ID" NUMBER NOT NULL,
	"SCRIPT_LENGTH" NUMBER,
	"SCRIPT_CONTENT" LONG,
	"LINE_NUM" NUMBER DEFAULT -1,
	CONSTRAINT ES_PK PRIMARY KEY("DEPLOYMENT_ID", "LINE_NUM" ),
	CONSTRAINT ES_DEST_FK FOREIGN KEY("DEPLOYMENT_ID") REFERENCES "VBZ$DESTINATIONS"("DEPLOYMENT_ID") ON DELETE CASCADE
);

CREATE TABLE "VBZ$EXEMPLARS"
(
	"CP_ID" NUMBER NOT NULL,
	"DB_OBJ_ID" NUMBER NOT NULL,
	"VERSION_NO" NUMBER NOT NULL,
	"OPTIONS" NUMBER,
	"DEF_LENGTH" NUMBER DEFAULT 0,
	"DEFINITION" LONG RAW,
	"FLAGS" NUMBER DEFAULT 0,
	CONSTRAINT EX_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT EX_PK PRIMARY KEY("DB_OBJ_ID", "VERSION_NO"),
	CONSTRAINT EX_ON_FK FOREIGN KEY("DB_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE
);

CREATE TABLE "VBZ$EX_UPDATES"
(
	"CP_ID" NUMBER NOT NULL,
	"DB_OBJ_ID" NUMBER NOT NULL,
	"VERSION_NO" NUMBER NOT NULL,
	"DEF_LENGTH" NUMBER DEFAULT 0,
	"DEFINITION" LONG RAW,
	"FLAGS" NUMBER DEFAULT 0,
	CONSTRAINT EXUPD_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT EXUPD_PK PRIMARY KEY("DB_OBJ_ID", "VERSION_NO"),
	CONSTRAINT EXUPD_ON_FK FOREIGN KEY("DB_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE
);

CREATE TABLE "VBZ$HISTORY"
(
	"HISTORY_ID" NUMBER NOT NULL,
	"ACTION_OWNER" VARCHAR(256),
	"OBJECT_TYPE" NUMBER,
	"OBJECT_OWNER" VARCHAR2(256),
	"OBJECT_NAME" VARCHAR2(50),
	"OBJECT_VERSION" NUMBER,
	"OPERATION" NUMBER,
	"OPERAND_1" VARCHAR2(64),
	"OPERAND_2" VARCHAR2(64),
	"STATUS" NUMBER,
	"MONITORED" NUMBER,
	"START_DATE" DATE,
	"END_DATE" DATE,
	"REP_OBJ_ID" NUMBER,
	CONSTRAINT HIST_PK PRIMARY KEY("HISTORY_ID")
);


CREATE TABLE "VBZ$IMPACT_LOG"
(
	"DEPLOYMENT_ID" NUMBER NOT NULL,
	"LOG_LINE_NO" NUMBER NOT NULL,
	"LOG_LINE" VARCHAR2(2000) NOT NULL,
	CONSTRAINT IL_PK PRIMARY KEY("DEPLOYMENT_ID", "LOG_LINE_NO"),
	CONSTRAINT IL_DEST_FK FOREIGN KEY("DEPLOYMENT_ID") REFERENCES "VBZ$DESTINATIONS"("DEPLOYMENT_ID") ON DELETE CASCADE
);

CREATE TABLE "VBZ$OBJECT_GRANTS"
(
	"CP_ID" NUMBER NOT NULL,
	"GRANTOR_OBJ_ID" NUMBER,
	"GRANTEE_OBJ_ID" NUMBER,
	"WITH_GRANT_OPT" VARCHAR2(3) NOT NULL,
	"PRIVILEGE" VARCHAR2(50) NOT NULL,
	"TARGET_OBJ_ID" NUMBER,
	"SUBOBJECT_NAME" VARCHAR2(30),
	"VERSION_NO" NUMBER,
	"FLAGS" NUMBER,
	CONSTRAINT OG_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT OG_TEE_ON_FK FOREIGN KEY("GRANTEE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE,
	CONSTRAINT OG_TOR_ON_FK FOREIGN KEY("GRANTOR_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE,
	CONSTRAINT OG_TAR_ON_FK FOREIGN KEY("TARGET_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE,
	CONSTRAINT OG_UK UNIQUE("CP_ID", "GRANTOR_OBJ_ID", "GRANTEE_OBJ_ID", "PRIVILEGE", "TARGET_OBJ_ID", "SUBOBJECT_NAME", "VERSION_NO")
);

CREATE TABLE "VBZ$OUTPUT_LOG"
(
	"DEPLOYMENT_ID" NUMBER NOT NULL,
	"HISTORY_ID" NUMBER NOT NULL,
	"LOG_LINE_NO" NUMBER NOT NULL,
	"LOG_LINE" LONG,
	CONSTRAINT OL_PK PRIMARY KEY("HISTORY_ID", "LOG_LINE_NO")
);

CREATE TABLE "VBZ$ROLE_GRANTS"
(
	"CP_ID" NUMBER NOT NULL,
	"GRANTEE_OBJ_ID" NUMBER,
	"WITH_ADMIN_OPT" VARCHAR2(3) NOT NULL,
	"DEFAULT_FLAG" VARCHAR2(3) NOT NULL,
	"ROLE_OBJ_ID" NUMBER,
	"VERSION_NO" NUMBER,
	"FLAGS" NUMBER,
	CONSTRAINT RG_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT RG_TEE_ON_FK FOREIGN KEY("GRANTEE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE,
	CONSTRAINT RG_OBJ_ON_FK FOREIGN KEY("ROLE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE,
	CONSTRAINT RG_UK UNIQUE("CP_ID", "GRANTEE_OBJ_ID", "ROLE_OBJ_ID", "VERSION_NO")
);

CREATE TABLE "VBZ$SCHEMAMAPS"
(
	"OCM_REP_OBJ_ID" NUMBER NOT NULL,
	"LEFT_SCHEMA" VARCHAR2(30),
	"RIGHT_SCHEMA" VARCHAR2(30)
);

CREATE TABLE "VBZ$SCRIPTS"
(
	"DEPLOYMENT_ID" NUMBER NOT NULL,
	"SCRIPT_LINE_NO" NUMBER NOT NULL,
	"SCRIPT_STEP_NO" NUMBER DEFAULT 0 NOT NULL,
	"SCRIPT_LINE_TYPE" VARCHAR2(15) DEFAULT 'unknown' NOT NULL,
	"SCRIPT_SECTION" VARCHAR2(10) DEFAULT 'unknown' NOT NULL,
	"SCRIPT_LINE" LONG,
	CONSTRAINT SCR_PK PRIMARY KEY("DEPLOYMENT_ID", "SCRIPT_LINE_NO"),
	CONSTRAINT SCR_DEST_FK FOREIGN KEY("DEPLOYMENT_ID") REFERENCES "VBZ$DESTINATIONS"("DEPLOYMENT_ID") ON DELETE CASCADE
);

CREATE TABLE "VBZ$SYS_PRIV_GRANTS"
(
	"CP_ID" NUMBER NOT NULL,
	"GRANTEE_OBJ_ID" NUMBER,
	"WITH_ADMIN_OPT" VARCHAR2(3) NOT NULL,
	"PRIVILEGE" VARCHAR2(50) NOT NULL,
	"VERSION_NO" NUMBER,
	"FLAGS" NUMBER,
	CONSTRAINT SG_CP_FK FOREIGN KEY("CP_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE,
	CONSTRAINT SG_TEE_ON_FK FOREIGN KEY("GRANTEE_OBJ_ID") REFERENCES "VBZ$DB_OBJ_NAMES"("DB_OBJ_ID") ON DELETE CASCADE,
	CONSTRAINT SG_UK UNIQUE("CP_ID", "GRANTEE_OBJ_ID", "PRIVILEGE", "VERSION_NO")
);

CREATE TABLE "VBZ$VERSION"
(
	"OCM_REP_OBJ_ID" NUMBER NOT NULL,
	"VERSION_NO" NUMBER NOT NULL,
	"FLAGS" NUMBER,
	"LAST_MOD_DATE" DATE,
	"DESCRIPTION" VARCHAR2(2000),
	CONSTRAINT VER_PK PRIMARY KEY("OCM_REP_OBJ_ID", "VERSION_NO"),
	CONSTRAINT VER_CP_FK FOREIGN KEY("OCM_REP_OBJ_ID") REFERENCES "VBZ$CHANGE_PLANS"("CP_ID") ON DELETE CASCADE
);

CREATE INDEX "OTS_INDEX" ON "VBZ$HISTORY"
(
    "OBJECT_OWNER",
    "OBJECT_TYPE",
    "STATUS"
) ;

CREATE INDEX "TS_INDEX" ON "VBZ$HISTORY"
(
    "OBJECT_TYPE",
    "STATUS"
);

CREATE INDEX "DATE_INDEX" ON "VBZ$HISTORY"
(
    "START_DATE"
) REVERSE;


CREATE INDEX "TON_INDEX" ON "VBZ$HISTORY"
(
    "OBJECT_TYPE",
    "OBJECT_OWNER",
    "OBJECT_NAME"
) ;

create index vbz$exemplars_cp_id_indx on vbz$exemplars(cp_id);
create index vbz$directives_cp_id_indx on vbz$directives(cp_id);
CREATE INDEX VBZ$OG_GEE_OIDX ON VBZ$OBJECT_GRANTS (GRANTEE_OBJ_ID);
CREATE INDEX VBZ$OG_GOR_OIDX ON VBZ$OBJECT_GRANTS (GRANTOR_OBJ_ID);
CREATE INDEX VBZ$OG_TGT_OIDX ON VBZ$OBJECT_GRANTS (target_obj_id);
CREATE INDEX VBZ$RG_GEE_OIDX ON VBZ$ROLE_GRANTS (grantee_obj_id);
CREATE INDEX VBZ$RG_ROLE_OIDX ON VBZ$ROLE_GRANTS (role_obj_id);
CREATE INDEX VBZ$SG_GEE_OIDX ON VBZ$SYS_PRIV_GRANTS (grantee_obj_id);