Rem drv: <migrate type="schema_upgrade" version="10.2.0.1" params="EM_ECM_DEPOT_TABLESPACE" />
Rem $Header: provision_schema_upgrade.sql 08-aug-2007.22:19:42 saurgarg Exp $
Rem
Rem provision_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      provision_schema_upgrade.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    saurgarg    08/08/07 - Correcting the cascade dropping to include the
Rem                           cascade drop index
Rem    rmadampa    08/31/05 - fix suite inst members columns 
Rem    prayarot    08/12/05 - To fix bug 4549757 
Rem    prayarot    08/10/05 - prayarot_upgrd
Rem    prayarot    08/08/05 - Created
Rem

SET ECHO ON

DROP INDEX MGMT_PROVISION_HARDWARE_INDEX1;

DROP INDEX MGMT_PROVISION_STATUS_INDEX1;

DROP INDEX MGMT_PROVISION_ASN_INDEX1;  


Rem ---------------- MGMT_PROV_TGT_STATUS changes starts----------------------

ALTER TABLE MGMT_PROVISION_STATUS RENAME to MGMT_PROV_TGT_STATUS;


ALTER TABLE MGMT_PROV_TGT_STATUS DROP CONSTRAINT MGMT_PROVISION_STATUS_PK CASCADE DROP INDEX;

ALTER TABLE MGMT_PROV_TGT_STATUS RENAME COLUMN STATUS_GUID to PROV_TGT_GUID;

ALTER TABLE MGMT_PROV_TGT_STATUS RENAME COLUMN TARGET_TYPE to PROV_TARGET_TYPE;

ALTER TABLE MGMT_PROV_TGT_STATUS RENAME COLUMN CURRENT_PROVISION_GUID to CURRENT_ASN_GUID;

ALTER TABLE MGMT_PROV_TGT_STATUS RENAME COLUMN IMAGE to COMPONENT_URN;

ALTER TABLE MGMT_PROV_TGT_STATUS RENAME COLUMN NETWORK to NETWORK_URN;

ALTER TABLE MGMT_PROV_TGT_STATUS ADD LAST_SUC_ASN_GUID RAW(16) DEFAULT NULL;

ALTER TABLE MGMT_PROV_TGT_STATUS DROP COLUMN NAME;

ALTER TABLE MGMT_PROV_TGT_STATUS DROP COLUMN DESCRIPTION;

ALTER TABLE MGMT_PROV_TGT_STATUS ADD CONSTRAINT MGMT_PROV_TGT_STATUS_PK PRIMARY KEY(PROV_TGT_GUID);





Rem ---------------- MGMT_PROV_HARDWARE changes starts----------------------

ALTER TABLE MGMT_PROVISION_HARDWARE RENAME to MGMT_PROV_HARDWARE;


ALTER TABLE MGMT_PROV_HARDWARE ADD NAME VARCHAR2(255);

ALTER TABLE MGMT_PROV_HARDWARE ADD DESCRIPTION VARCHAR2(255);

ALTER TABLE MGMT_PROV_HARDWARE RENAME CONSTRAINT MGMT_PROVISION_HARDWARE_PK to MGMT_PROV_HARDWARE_PK;






Rem ---------------- MGMT_PROV_OPERATION changes starts----------------------

ALTER TABLE MGMT_PROVISION_OPERATION RENAME to MGMT_PROV_OPERATION;


ALTER TABLE MGMT_PROV_OPERATION RENAME COLUMN OP_TIMESTAMP to CREATION_TIME;

ALTER TABLE MGMT_PROV_OPERATION ADD LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE;

ALTER TABLE MGMT_PROV_OPERATION RENAME CONSTRAINT MGMT_PROVISION_OPERATION_PK to MGMT_PROV_OPERATION_PK;






Rem ---------------- MGMT_PROV_ASSIGNMENT changes starts----------------------

ALTER TABLE MGMT_PROVISION_ASSIGNMENT RENAME to MGMT_PROV_ASSIGNMENT;


ALTER TABLE MGMT_PROV_ASSIGNMENT RENAME COLUMN IMAGE_URN to COMPONENT_URN;

ALTER TABLE MGMT_PROV_ASSIGNMENT ADD ASSIGNMENT_SUBTYPE VARCHAR2(30) DEFAULT NULL;

ALTER TABLE MGMT_PROV_ASSIGNMENT DROP COLUMN DEPENDENT_ON_ASSIGNMENT_GUID;

ALTER TABLE MGMT_PROV_ASSIGNMENT DROP COLUMN SUITE_GUID;

ALTER TABLE MGMT_PROV_ASSIGNMENT RENAME CONSTRAINT MGMT_PROVISION_ASSIGNMENT_PK to MGMT_PROV_ASSIGNMENT_PK;







Rem ---------------- MGMT_PROV_HISTORY changes starts----------------------

ALTER TABLE MGMT_PROVISION_HISTORY RENAME to MGMT_PROV_HISTORY;


ALTER TABLE MGMT_PROV_HISTORY RENAME COLUMN STATUS_GUID to PROV_TGT_GUID;

ALTER TABLE MGMT_PROV_HISTORY ADD HOSTNAME VARCHAR2(255);






Rem ---------------- MGMT_PROV_BOOTSERVER changes starts----------------------

ALTER TABLE MGMT_PROVISION_BOOTSERVER RENAME to MGMT_PROV_BOOTSERVER;


ALTER TABLE MGMT_PROV_BOOTSERVER RENAME CONSTRAINT MGMT_PROVISION_BOOTSERVER_PK to MGMT_PROV_BOOTSERVER_PK;

ALTER TABLE MGMT_PROV_BOOTSERVER RENAME CONSTRAINT MGMT_PROVISION_BOOTSERVER_UNIQ to MGMT_PROV_BOOTSERVER_UNIQ;







Rem ---------------- MGMT_PROV_DEFAULT_IMAGE changes starts----------------------

ALTER TABLE MGMT_PROVISION_DFLT_IMG RENAME to MGMT_PROV_DEFAULT_IMAGE;


ALTER TABLE MGMT_PROV_DEFAULT_IMAGE ADD NAME VARCHAR2(255);

ALTER TABLE MGMT_PROV_DEFAULT_IMAGE ADD DESCRIPTION VARCHAR2(255);

ALTER TABLE MGMT_PROV_DEFAULT_IMAGE RENAME CONSTRAINT MGMT_PROVISION_DFLT_IMG_UNIQ to MGMT_PROV_DEFAULT_IMAGE_UNIQ;






Rem ---------------- MGMT_PROV_RPM_REP changes starts----------------------

ALTER TABLE MGMT_PROVISION_RPM_REP RENAME to MGMT_PROV_RPM_REP;


ALTER TABLE MGMT_PROV_RPM_REP RENAME CONSTRAINT MGMT_PROVISION_RPM_REP_PK to MGMT_PROV_RPM_REP_PK;

ALTER TABLE MGMT_PROV_RPM_REP RENAME CONSTRAINT MGMT_PROVISION_RPM_REP_UNIQ to MGMT_PROV_RPM_REP_UNIQ;







Rem ---------------- MGMT_PROV_STAGING_DIRS changes starts----------------------

ALTER TABLE MGMT_STAGING_DIRECTORIES RENAME to MGMT_PROV_STAGING_DIRS;


ALTER TABLE MGMT_PROV_STAGING_DIRS RENAME CONSTRAINT MGMT_STAGING_DIRECTORIES_PK to MGMT_PROV_STAGING_DIRS_PK;

ALTER TABLE MGMT_PROV_STAGING_DIRS RENAME CONSTRAINT MGMT_STAGING_DIRECTORIES_UNIQ to MGMT_PROV_STAGING_DIRS_UNIQ;







Rem ---------------- MGMT_PROV_STAGED_COMPS changes starts----------------------


ALTER TABLE MGMT_STAGED_COMPONENTS RENAME to MGMT_PROV_STAGED_COMPS;


ALTER TABLE MGMT_PROV_STAGED_COMPS ADD CONSTRAINT MGMT_PROV_STAGED_COMPS_UNIQ UNIQUE (COMP_URN);







Rem ---------------- MGMT_PROV_NET_CONFIG changes starts----------------------


ALTER TABLE MGMT_PROVISION_NET_CONFIG RENAME to MGMT_PROV_NET_CONFIG;


ALTER TABLE MGMT_PROV_NET_CONFIG RENAME CONSTRAINT MGMT_PROVISION_NET_CONFIG_PK to MGMT_PROV_NET_CONFIG_PK;

ALTER TABLE MGMT_PROV_NET_CONFIG RENAME CONSTRAINT MGMT_PROVISION_NET_CONFIG_UK to MGMT_PROV_NET_CONFIG_UK;








Rem ---------------- MGMT_PROV_IP_RANGE changes starts----------------------

ALTER TABLE MGMT_PROVISION_IP_RANGE RENAME to MGMT_PROV_IP_RANGE;



ALTER TABLE MGMT_PROV_IP_RANGE RENAME CONSTRAINT MGMT_PROVISION_IP_RANGE_PK to MGMT_PROV_IP_RANGE_PK;

ALTER TABLE MGMT_PROV_IP_RANGE RENAME CONSTRAINT MGMT_PROVISION_IP_RANGE_FK to MGMT_PROV_IP_RANGE_FK;

ALTER TABLE MGMT_PROV_IP_RANGE RENAME CONSTRAINT MGMT_PROVISION_IP_RANGE_POS to MGMT_PROV_IP_RANGE_POS;







Rem ---------------- MGMT_PROV_IP_RESERVED changes starts----------------------

ALTER TABLE MGMT_PROVISION_IP_RESERVED RENAME to MGMT_PROV_IP_RESERVED;


ALTER TABLE MGMT_PROV_IP_RESERVED RENAME CONSTRAINT MGMT_PROVISION_IP_RESERVED_PK to MGMT_PROV_IP_RESERVED_PK;

ALTER TABLE MGMT_PROV_IP_RESERVED RENAME CONSTRAINT MGMT_PROVISION_IP_RESERVED_FK1 to MGMT_PROV_IP_RESERVED_FK1;

ALTER TABLE MGMT_PROV_IP_RESERVED RENAME CONSTRAINT MGMT_PROVISON_IP_RESERVED_FK2 to MGMT_PROV_IP_RESERVED_FK2;

ALTER TABLE MGMT_PROV_IP_RESERVED RENAME CONSTRAINT MGMT_PROVISION_IP_RESERVED_UNQ to MGMT_PROV_IP_RESERVED_UNQ;





Rem ---------------- MGMT_PROV_COLLECTION changes starts----------------------

ALTER TABLE prov_collection RENAME to MGMT_PROV_COLLECTION;








Rem ---------------- New tables----------------------

CREATE TABLE MGMT_PROV_CLUSTER
(
    CLUSTER_GUID                  RAW(16) NOT NULL,
    NAME                          VARCHAR2(255) NOT NULL,
    DESCRIPTION                   VARCHAR2(255) DEFAULT NULL,
    IMAGE_CLUSTER_URN             VARCHAR2(255),
    PURPOSE                       VARCHAR2(30),
    CONSTRAINT MGMT_PROV_CLUSTER_PK PRIMARY KEY(CLUSTER_GUID),
    CONSTRAINT MGMT_PROV_CLUSTER_UNIQ  UNIQUE (NAME)
) MONITORING;

CREATE TABLE MGMT_PROV_CLUSTER_NODES
(
    NODE_GUID                     RAW(16) NOT NULL,
    CLUSTER_GUID                  RAW(16) NOT NULL,
    STATUS                        VARCHAR2(30),
    LAST_MODIFIED_TIME            TIMESTAMP WITH TIME ZONE,
    PROV_ASN_GUID                 RAW(16) DEFAULT NULL,
    CONSTRAINT MGMT_PROV_CLUS_NODES_PK PRIMARY KEY(NODE_GUID, CLUSTER_GUID)
) MONITORING;

CREATE TABLE MGMT_PROV_SUITE_INSTANCE
(
    SUITE_INST_GUID               RAW(16) NOT NULL,
    NAME                          VARCHAR2(255) NOT NULL,
    DESCRIPTION                   VARCHAR2(255) DEFAULT NULL,
    SUITE_URN                     VARCHAR2(255),
    PURPOSE                       VARCHAR2(30),
    CONSTRAINT MGMT_PROV_SUITE_INSTANCE_PK PRIMARY KEY(SUITE_INST_GUID),
    CONSTRAINT MGMT_PROV_SUITE_INSTANCE_UNIQ  UNIQUE (NAME)
) MONITORING;

CREATE TABLE MGMT_PROV_SUITE_INST_MEMBERS
(
    MEMBER_GUID                   RAW(16) NOT NULL,
    SUITE_INST_GUID               RAW(16) NOT NULL,
    MEMBER_TYPE                   VARCHAR2(20),
    MEMBER_COMPONENT_URN          VARCHAR2(255),
    STATUS                        VARCHAR2(30),
    LAST_MODIFIED_TIME            TIMESTAMP WITH TIME ZONE,
    PROV_ASN_GUID                 RAW(16) DEFAULT NULL,
    CONSTRAINT MGMT_PROV_SUITE_MEMBERS_PK PRIMARY KEY(MEMBER_GUID, SUITE_INST_GUID)
) MONITORING;

CREATE TABLE MGMT_PROV_ASN_DEPENDENCIES
(
    PARENT_ASN_GUID               RAW(16) NOT NULL,
    CHILD_ASN_GUID                RAW(16) NOT NULL,
    DEP_ON_ASN_GUID               RAW(16)
)MONITORING;








Rem ---------------- Index modifications----------------------


CREATE INDEX mgmt_prov_hardware_index1 ON
  mgmt_prov_hardware(name, hostname, mac_address1, serial_number);


CREATE INDEX mgmt_prov_status_index1 ON
  mgmt_prov_tgt_status(component_urn, network_urn, status);
  

CREATE INDEX mgmt_prov_asn_index1 ON
  mgmt_prov_assignment(component_urn, network_urn, stage_urn);