Rem drv: <migrate type="schema_upgrade" version="10.2.0.0"  params="EM_ECM_DEPOT_TABLESPACE" pos="swlib/swlib_schema_upgrade.sql+"/> 
Rem
Rem $Header: provision_schema_upgrade.sql 08-aug-2005.02:44:43 prayarot Exp $
Rem
Rem provision_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 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    prayarot    08/08/05 - To make identical to BETA 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - modify the version number in header 
Rem    prayarot    07/07/05 - To enforce UNIQUE key constraint for 
Rem                           MGMT_PROV_STAGED_COMPS 
Rem    prayarot    05/26/05 - To fix Bug 4388588 
Rem    rmadampa    05/05/05 - data model enhancements 1 
Rem    jhazra      04/21/05 - Add assignment specific hw table 
Rem    prayarot    04/19/05 - To fix bug#4305533 
Rem    ashwikum    04/20/05 - Reflect changes to net config module 
Rem    chyu        03/17/05 - adding the missing column to the 
Rem                           provision_assignment table 
Rem    ashwikum    03/09/05 - Adding Network Configuration Files 
Rem    prayarot    03/02/05 - prayarot_upgrade
Rem    prayarot    03/02/05 - Created
Rem

CREATE TABLE MGMT_PROVISION_STATUS
(
    STATUS_GUID                      RAW(16) not null,
    NAME                             VARCHAR2(255) default null,
    DESCRIPTION                      VARCHAR2(255) default null,
    TARGET_TYPE                      VARCHAR2(30),
    CURRENT_PROVISION_GUID           RAW(16) default null,
    IMAGE                            VARCHAR2(255) default null,
    NETWORK                          VARCHAR2(255),
    STATUS                           VARCHAR2(30),
    CONSTRAINT MGMT_PROVISION_STATUS_PK PRIMARY KEY(STATUS_GUID)
) MONITORING;


CREATE TABLE MGMT_PROVISION_HARDWARE
(
    HW_GUID                          RAW(16) not null,
    HOSTNAME                         VARCHAR2(255) default null,
    NEW_HOSTNAME                     VARCHAR2(255) default null,
    MAC_ADDRESS1                     VARCHAR2(30) ,
    MAC_ADDRESS2                     VARCHAR2(30) ,
    MAC_ADDRESS3                     VARCHAR2(30) ,
    MAC_ADDRESS4                     VARCHAR2(30) ,
    INTERFACE_NAME1                  VARCHAR2(128),
    INTERFACE_NAME2                  VARCHAR2(128),
    INTERFACE_NAME3                  VARCHAR2(128),
    INTERFACE_NAME4                  VARCHAR2(128),
    SERIAL_NUMBER                    VARCHAR2(255) default null,
    RF_ID                            VARCHAR2(255) default null,
    PURPOSE                          VARCHAR2(30),
    CONSTRAINT MGMT_PROVISION_HARDWARE_PK PRIMARY KEY(HW_GUID)
) MONITORING;


CREATE TABLE MGMT_PROVISION_OPERATION
(
    OP_GUID                     RAW(16)   NOT NULL,
    OP_TIMESTAMP                TIMESTAMP with time zone,
    FRACTION_COMPLETE           FLOAT,
    STATUS_MSG                  VARCHAR2(255),
    OP_TYPE                     VARCHAR2(30),
    JOB_ID                      VARCHAR2(255) default null,
    CONSTRAINT MGMT_PROVISION_OPERATION_PK PRIMARY KEY (OP_GUID)
) MONITORING;


CREATE TABLE MGMT_PROVISION_ASSIGNMENT
(
    ASSIGNMENT_GUID               RAW(16) NOT NULL,
    NAME                          VARCHAR2(255) default null,
    DESCRIPTION                   VARCHAR2(255) default null,
    ASSIGNMENT_TYPE               VARCHAR2(30),
    STATUS                        VARCHAR2(30) ,
    USERNAME                      VARCHAR2(255),
    IMAGE_URN                     VARCHAR2(255),
    NETWORK_URN                   VARCHAR2(255),
    BOOT_SERVER_URN               VARCHAR2(255),
    STAGE_URN                     VARCHAR2(255),
    START_TIME                    TIMESTAMP with time zone,
    PROPERTIES                    CLOB DEFAULT EMPTY_CLOB(),
    STAGE_USERNAME                VARCHAR2(255),
    STAGE_PASSWORD                VARCHAR2(255),
    LAST_MODIFIED_TIME            TIMESTAMP with time zone,
    TARGET_RESET_TIME             NUMBER,
    DEPENDENT_ON_ASSIGNMENT_GUID  RAW(16),
    SUITE_GUID                    RAW(16),
    CONSTRAINT MGMT_PROVISION_ASSIGNMENT_PK  PRIMARY KEY (ASSIGNMENT_GUID)
) MONITORING
LOB(PROPERTIES) STORE AS ( TABLESPACE &EM_ECM_DEPOT_TABLESPACE );


CREATE TABLE MGMT_PROV_ASN_TARGETS
(
    ASSIGNMENT_GUID               RAW(16) NOT NULL,
    PROV_TGT_GUID                 RAW(16) NOT NULL,
    PROPERTIES                    BLOB DEFAULT EMPTY_BLOB(),
    STATUS                        VARCHAR2(30) ,
    CONSTRAINT MGMT_PROV_ASN_TARGETS_PK  PRIMARY KEY (ASSIGNMENT_GUID,
                                PROV_TGT_GUID)
) MONITORING
LOB(PROPERTIES) STORE AS ( TABLESPACE &EM_ECM_DEPOT_TABLESPACE );


CREATE TABLE MGMT_PROVISION_HISTORY
(
    STATUS_GUID             RAW(16)  NOT NULL,
    OP_GUID                 RAW(16)  NOT NULL,
    ASSIGNMENT_GUID         RAW(16)
) MONITORING;


CREATE TABLE MGMT_PROVISION_BOOTSERVER
(
    BOOT_GUID               RAW(16) NOT NULL,
    BOOT_HOST_NAME          VARCHAR2(255),
    BOOT_CONFIG_DIR         VARCHAR2(255),
    LAST_MODIFIED_TIME      TIMESTAMP with time zone,
    CONSTRAINT MGMT_PROVISION_BOOTSERVER_PK  PRIMARY KEY (BOOT_GUID),
    CONSTRAINT MGMT_PROVISION_BOOTSERVER_UNIQ  UNIQUE (BOOT_HOST_NAME , BOOT_CONFIG_DIR)
) MONITORING;

CREATE TABLE MGMT_PROVISION_DFLT_IMG
(
    DEFAULT_GUID                RAW(16) NOT NULL,
    IP_ADDRESS_PREFIX           VARCHAR2(255),
    LAST_MODIFIED_TIME          TIMESTAMP with time zone,
    CONSTRAINT MGMT_PROVISION_DFLT_IMG_PK  PRIMARY KEY (DEFAULT_GUID),
    CONSTRAINT MGMT_PROVISION_DFLT_IMG_UNIQ  UNIQUE (IP_ADDRESS_PREFIX)
) MONITORING;

CREATE TABLE MGMT_PROVISION_RPM_REP
(
    RPM_GUID                RAW(16) NOT NULL,
    RPM_NAME                VARCHAR2(255),
    RPM_DIR                 VARCHAR2(255),
    LAST_MODIFIED_TIME      TIMESTAMP with time zone,
    CONSTRAINT MGMT_PROVISION_RPM_REP_PK  PRIMARY KEY (RPM_GUID),
    CONSTRAINT MGMT_PROVISION_RPM_REP_UNIQ  UNIQUE (RPM_NAME, RPM_DIR)
) MONITORING;


CREATE TABLE MGMT_STAGING_DIRECTORIES
(
    STAGE_GUID                  RAW(16) NOT NULL,
    STAGE_SERVER_HOSTNAME       VARCHAR2(255),
    NFS_EXPOSED_DIR             VARCHAR2(255),
    BASE_URL                    VARCHAR2(255),
    SIZE_LIMIT                  FLOAT,
    CURRENT_SIZE                FLOAT,
    LAST_MODIFIED_TIME          TIMESTAMP with time zone,
    CONSTRAINT MGMT_STAGING_DIRECTORIES_PK  PRIMARY KEY (STAGE_GUID),
    CONSTRAINT MGMT_STAGING_DIRECTORIES_UNIQ  UNIQUE (STAGE_SERVER_HOSTNAME , NFS_EXPOSED_DIR)
) MONITORING;


CREATE TABLE MGMT_STAGED_COMPONENTS
(
    STAGE_GUID          RAW(16) NOT NULL,
    COMP_URN            VARCHAR2(255),
    COMP_DIR            VARCHAR2(255)
) MONITORING;

CREATE TABLE MGMT_PROVISION_NET_CONFIG
(
    NET_CONFIG_GUID             RAW(16),
    LAST_MODIFIED_TIME          TIMESTAMP with time zone,
    NET_CONFIG_NAME             VARCHAR2(255),
    NET_DOMAIN_NAME             VARCHAR2(255),
    NET_SUBNET_MASK             VARCHAR2(255),
    NET_GATEWAY_ADDRS           VARCHAR2(512),
    NET_DNS_ADDRS               VARCHAR2(512),
    CONSTRAINT MGMT_PROVISION_NET_CONFIG_PK PRIMARY KEY (NET_CONFIG_GUID),
    CONSTRAINT MGMT_PROVISION_NET_CONFIG_UK UNIQUE(NET_CONFIG_NAME)
) MONITORING;

CREATE TABLE MGMT_PROVISION_IP_RANGE
(
    IP_RANGE_GUID               RAW(16),
    LAST_MODIFIED_TIME          TIMESTAMP with time zone,
    IP_RANGE_FIRST              VARCHAR2(255),
    IP_RANGE_LAST               VARCHAR2(255),
    IP_RANGE_COUNT              INTEGER,
    IP_RANGE_NAME_PATTERN       VARCHAR2(255),
    IP_RANGE_START_VALUE        INTEGER,
    IP_RANGE_STATE              VARCHAR2(32),
    NET_CONFIG_GUID             RAW(16),
    CONSTRAINT MGMT_PROVISION_IP_RANGE_PK PRIMARY KEY (IP_RANGE_GUID),
    CONSTRAINT MGMT_PROVISION_IP_RANGE_FK FOREIGN KEY (NET_CONFIG_GUID)
        REFERENCES MGMT_PROVISION_NET_CONFIG(NET_CONFIG_GUID)
        ON DELETE CASCADE,
    CONSTRAINT MGMT_PROVISION_IP_RANGE_POS CHECK (IP_RANGE_COUNT >= 0)
) MONITORING;

CREATE TABLE MGMT_PROVISION_IP_RESERVED
(
    IP_ADDR                     VARCHAR2(255),
    IP_OWNER_URN                VARCHAR2(255),
    IP_RANGE_GUID               RAW(16),
    HOSTNAME                    VARCHAR2(255),
    NET_CONFIG_GUID             RAW(16),
    CONSTRAINT MGMT_PROVISION_IP_RESERVED_PK PRIMARY KEY (IP_ADDR),
    CONSTRAINT MGMT_PROVISION_IP_RESERVED_FK1 FOREIGN KEY (IP_RANGE_GUID)
        REFERENCES MGMT_PROVISION_IP_RANGE(IP_RANGE_GUID)
        ON DELETE CASCADE,
    CONSTRAINT MGMT_PROVISON_IP_RESERVED_FK2 FOREIGN KEY (NET_CONFIG_GUID)
        REFERENCES MGMT_PROVISION_NET_CONFIG(NET_CONFIG_GUID)
        ON DELETE CASCADE,
    CONSTRAINT MGMT_PROVISION_IP_RESERVED_UNQ UNIQUE(HOSTNAME)
) MONITORING;

CREATE TABLE prov_collection
(
    collected_time      VARCHAR2(255)
) MONITORING;


CREATE INDEX MGMT_PROVISION_HARDWARE_INDEX1 ON
  MGMT_PROVISION_HARDWARE(HOSTNAME, MAC_ADDRESS1, SERIAL_NUMBER);

CREATE INDEX MGMT_PROVISION_STATUS_INDEX1 ON
  MGMT_PROVISION_STATUS(NAME, IMAGE, NETWORK, STATUS);

CREATE INDEX MGMT_PROVISION_ASN_INDEX1 ON
  MGMT_PROVISION_ASSIGNMENT(IMAGE_URN, NETWORK_URN, STAGE_URN);