Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="common/common_schema_upgrade.sql+"/> 
Rem
Rem $Header: txnmgmt_schema_upgrade.sql 13-aug-2006.16:11:20 bmallipe Exp $
Rem
Rem txnmgmt_schema_upgrade.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      txnmgmt_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    bmallipe    08/13/06 - fixing for DBControl upgrade errors
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - modify the version number for the header 
Rem    chyu        04/11/05 - removing the duplicate drop statement 
Rem    rmarripa    03/30/05 - add parent to the step 
Rem    chyu        03/29/05 - removing obsoleted trigger 
Rem    mfidanbo    02/18/05 - add columns to mgmt_bcn type 
Rem    rpinnama    02/09/05 - 
Rem    ktlaw       01/13/05 - add repmgr header 
Rem    rmarripa    12/24/04 - rmarripa_webapp_upgrade_changes
Rem    vjraghav    11/03/04 - Adding indices for tables into the migration 
Rem                           code 
Rem    vjraghav    11/02/04 - vjraghav_migration_1
Rem    vjraghav    10/26/04 - Created
Rem

--------------------------------------------------------------------------------
-- DROP TRIGGERS 
--------------------------------------------------------------------------------
DECLARE
    l_count NUMBER := 0;
BEGIN
    SELECT count(*)
    INTO   l_count
    FROM   all_triggers
    WHERE  lower(trigger_name) = 'bcn_target_added';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP trigger BCN_TARGET_ADDED';
    END IF;

    SELECT count(*)
    INTO   l_count
    FROM   all_triggers
    WHERE  lower(trigger_name) = 'bcn_target_deleted';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP trigger BCN_TARGET_DELETED';
    END IF;
END;
/

--------------------------------------------------------------------------------
-- DROP TYPES
--------------------------------------------------------------------------------

DROP TYPE mgmt_bcntxn_array;

DROP TYPE mgmt_bcntxn;

DROP TYPE mgmt_bcntxn_step_array;

DROP TYPE mgmt_bcntxn_step;

DROP TYPE mgmt_bcn_array;

DROP TYPE mgmt_bcn;

DROP TYPE mgmt_bcn_threshold_array;

DROP TYPE mgmt_bcn_threshold;

--------------------------------------------------------------------------------
-- CREATE/EDIT TYPES
--------------------------------------------------------------------------------

-- These type creations have been copied over directly from beacon_types.sql.
-- Any changes made in beacon_types.sql should be reflected here.

CREATE OR REPLACE TYPE mgmt_bcn AS OBJECT
  (
     bcn_guid              VARCHAR2(32),
     bcn_target_name       VARCHAR2(256),
     bcn_target_type       VARCHAR2(64),
     bcn_emd_url           VARCHAR2(2000),
     bcn_version           VARCHAR2(8),
     bcn_is_avail          CHAR(1),
     bcn_is_local          CHAR(1)
  );
/

CREATE OR REPLACE TYPE mgmt_bcn_array AS 
        TABLE OF mgmt_bcn;
/

-- Types for properties and property names

CREATE OR REPLACE TYPE mgmt_bcn_nvpair AS OBJECT 
(
    name                    VARCHAR2(64),
    string_part             NUMBER,
    string_value            VARCHAR2(3936),
    num_value               NUMBER,
    date_value              DATE,
    char_value              CHAR(1),
    prop_type               NUMBER,
    encrypt                 CHAR(1),
    template                CHAR(1)
);
/

CREATE OR REPLACE TYPE mgmt_bcn_nvpair_array AS
        TABLE OF mgmt_bcn_nvpair;
/

CREATE OR REPLACE TYPE mgmt_bcn_pnames_array AS
        TABLE OF VARCHAR2(64);      
/

-- Types for per-beacon properties and property names

CREATE OR REPLACE TYPE mgmt_bcn_perbcn_nvpair AS OBJECT
(        
    bcn_guid                VARCHAR2(32),
    nvpairlist              mgmt_bcn_nvpair_array
);
/

CREATE OR REPLACE TYPE mgmt_bcn_perbcn_nvpair_array AS
        TABLE OF mgmt_bcn_perbcn_nvpair;
/

CREATE OR REPLACE TYPE mgmt_bcn_perbcn_pnames AS OBJECT 
(        
    bcn_guid                VARCHAR2(32),
    propnames               mgmt_bcn_pnames_array
);
/

CREATE OR REPLACE TYPE mgmt_bcn_perbcn_pnames_array AS
        TABLE OF mgmt_bcn_perbcn_pnames;
/

-- Types for thresholds
CREATE OR REPLACE TYPE mgmt_bcn_threshold_key AS OBJECT 
(
    bcn_guid                VARCHAR2(64),
    txn_guid                VARCHAR2(64),
    key_part_3              VARCHAR2(64),
    key_part_4              VARCHAR2(64),
    key_part_5              VARCHAR2(64),
    metric_name             VARCHAR2(64),
    metric_column           VARCHAR2(64)
);
/

CREATE OR REPLACE TYPE mgmt_bcn_threshold_key_array AS
        TABLE OF mgmt_bcn_threshold_key;
/

CREATE OR REPLACE TYPE mgmt_bcn_threshold AS OBJECT 
(
    key                     mgmt_bcn_threshold_key,
    warning_threshold       VARCHAR2(256),
    warning_operator        NUMBER,
    critical_threshold      VARCHAR2(256),
    critical_operator       NUMBER,
    num_occurences          NUMBER
);
/

CREATE OR REPLACE TYPE mgmt_bcn_threshold_array AS
        TABLE OF mgmt_bcn_threshold;
/

-- Transaction definition
CREATE OR REPLACE TYPE mgmt_bcn_txn AS OBJECT 
(
    txn_guid                VARCHAR2(32),
    txn_type                VARCHAR2(64),
    name                    VARCHAR2(64),
    description             VARCHAR2(2000),
    is_representative       CHAR(1),
    version                 NUMBER,
    tgt_tz                  VARCHAR2(64), 
    template                CHAR(1)
);
/

CREATE OR REPLACE TYPE mgmt_bcn_txn_with_props AS OBJECT 
(
    txn_defn                mgmt_bcn_txn,
    txn_props               mgmt_bcn_nvpair_array,
    bcn_txn_props           mgmt_bcn_perbcn_nvpair_array
);
/

CREATE OR REPLACE TYPE mgmt_bcn_txnid_array AS
        TABLE OF VARCHAR2(32);
/

-- Step definition
CREATE OR REPLACE TYPE mgmt_bcn_step AS OBJECT 
(
    step_guid               VARCHAR2(32),
    step_number             NUMBER,
    name                    VARCHAR2(64),
    step_type               VARCHAR2(64),
    parent_step_guid        VARCHAR2(32),
    parent_step_name        VARCHAR2(64)
);
/

CREATE OR REPLACE TYPE mgmt_bcn_step_array AS
        TABLE OF mgmt_bcn_step;
/

CREATE OR REPLACE TYPE mgmt_bcn_step_with_props AS OBJECT 
(
    step_defn               mgmt_bcn_step,
    step_props              mgmt_bcn_nvpair_array
);
/

CREATE OR REPLACE TYPE mgmt_bcn_step_with_props_array AS
        TABLE OF mgmt_bcn_step_with_props;
/

-- Step Group Definition
CREATE OR REPLACE TYPE mgmt_bcn_stepid_array AS
        TABLE OF VARCHAR2(64);
/

CREATE OR REPLACE TYPE mgmt_bcn_stepgroup AS OBJECT 
(
    stepgroup_guid          VARCHAR2(32),
    name                    VARCHAR2(64),
    stepgroup_type          VARCHAR2(64),
    steps                   mgmt_bcn_stepid_array
);
/

CREATE OR REPLACE TYPE mgmt_bcn_stepgroup_array AS
        TABLE OF mgmt_bcn_stepgroup;
/

CREATE OR REPLACE TYPE mgmt_bcn_stepgroupid_array AS
        TABLE OF VARCHAR2(32);
/

CREATE OR REPLACE TYPE mgmt_bcn_transaction AS OBJECT
(
    txn_defn_with_props     mgmt_bcn_txn_with_props,
    steps_defn_with_props   mgmt_bcn_step_with_props_array,
    stepgroups_defn         mgmt_bcn_stepgroup_array,
    txn_thresholds          mgmt_bcn_threshold_array,
    step_thresholds         mgmt_bcn_threshold_array,
    stepgroup_thresholds    mgmt_bcn_threshold_array
);
/

CREATE OR REPLACE TYPE mgmt_bcn_transaction_array AS
        TABLE OF mgmt_bcn_transaction;
/

CREATE OR REPLACE TYPE mgmt_bcn_assoc AS OBJECT
(
    bcn_target_name       VARCHAR2(256),
    bcn_target_type       VARCHAR2(64),
    bcn_is_avail          CHAR(1), 
    bcn_is_local          CHAR(1)
);
/

CREATE OR REPLACE TYPE mgmt_bcn_assoc_array AS
        TABLE OF mgmt_bcn_assoc;
/

CREATE OR REPLACE TYPE mgmt_bcn_composite_key AS OBJECT 
(
    bcn_guid                VARCHAR2(64),
    txn_guid                VARCHAR2(64),
    step_group_guid         VARCHAR2(64),
    composite_key           RAW(16)
);
/

CREATE OR REPLACE TYPE mgmt_bcn_composite_key_array AS
        TABLE OF mgmt_bcn_composite_key;
/

CREATE OR REPLACE TYPE MgmtBcnAvailRowType AS OBJECT
    (target_guid                RAW(16),
     severity_guid              RAW(16),
     current_status             NUMBER,
     start_collection_timestamp DATE,
     end_collection_timestamp   DATE);
/
CREATE OR REPLACE TYPE MgmtBcnAvailTableType AS TABLE OF MgmtBcnAvailRowType;
/
CREATE OR REPLACE TYPE mgmt_bcn_txn_avail AS OBJECT
(   
    bcn_guid  RAW(16),
    test_guid RAW(16),
    avail_states MgmtBcnAvailTableType
);
/
CREATE OR REPLACE TYPE mgmt_bcn_txn_avail_array AS
         TABLE OF mgmt_bcn_txn_avail;
/


--------------------------------------------------------------------------------
-- CREATE/EDIT TABLES
--------------------------------------------------------------------------------

ALTER TABLE MGMT_BCN_TXN_DEFN 
    MODIFY ( txn_type VARCHAR2(64) );

-- These table creations have been copied over directly from beacon_tables.sql.
-- and beacon_indexes.sql. Any changes made in these files should be reflected here.

CREATE TABLE MGMT_BCN_STEP_DEFN
  (
     target_guid           RAW(16)          NOT NULL,
     txn_guid              RAW(16)          NOT NULL,
     step_guid             RAW(16)          NOT NULL,
     step                  NUMBER           NOT NULL,
     name                  VARCHAR2(64)     NOT NULL,
     step_type             VARCHAR2(64)     DEFAULT 'HTTP' NOT NULL, 
     parent_step_guid      RAW(16)          DEFAULT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_STEPGROUP_DEFN
  (
     target_guid           RAW(16)          NOT NULL,
     txn_guid              RAW(16)          NOT NULL,
     stepgroup_guid        RAW(16)          NOT NULL,
     name                  VARCHAR2(64)     NOT NULL,
     stepgroup_type        VARCHAR2(64)     DEFAULT 'HTTP' NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_STEPGROUP_STEPS
  (
     target_guid           RAW(16)          NOT NULL,
     txn_guid              RAW(16)          NOT NULL,
     stepgroup_guid        RAW(16)          NOT NULL,
     step_guid             RAW(16)          NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_TXN_AUDIT
  (
     target_guid       RAW(16)         NOT NULL,
     txn_guid          RAW(16)         NOT NULL,
     audit_timestamp   DATE            NOT NULL,
     change_type       NUMBER          NOT NULL,
     is_version_change CHAR(1)         DEFAULT 'N' NOT NULL,
     version           NUMBER,
     details           VARCHAR2(1024)
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_TXN_PROPS
  (
     target_guid       RAW(16)         NOT NULL,
     txn_guid          RAW(16)         NOT NULL,
     name              VARCHAR2(64)    NOT NULL,
     string_part       NUMBER          DEFAULT 0 NOT NULL,
     string_value      VARCHAR2(4000),
     num_value         NUMBER,
     date_value        DATE,
     char_value        CHAR,
     prop_type         NUMBER          DEFAULT 1 NOT NULL,
     encrypted         CHAR            DEFAULT 'N',
     template          CHAR            DEFAULT 'N'
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_STEP_PROPS
  (
     target_guid       RAW(16)         NOT NULL,
     step_guid         RAW(16)         NOT NULL,
     name              VARCHAR2(64)    NOT NULL,
     string_part       NUMBER          DEFAULT 0 NOT NULL,
     string_value      VARCHAR2(4000),
     num_value         NUMBER,
     date_value        DATE,
     char_value        CHAR,
     prop_type         NUMBER          DEFAULT 1 NOT NULL,
     encrypted         CHAR            DEFAULT 'N',
     template          CHAR            DEFAULT 'N'
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_BCNTXN_PROPS
  (
     target_guid       RAW(16)         NOT NULL,
     txn_guid          RAW(16)         NOT NULL,
     bcn_guid          RAW(16)         NOT NULL,
     name              VARCHAR2(64)    NOT NULL,
     string_part       NUMBER          DEFAULT 0 NOT NULL,
     string_value      VARCHAR2(4000),
     num_value         NUMBER,
     date_value        DATE,
     char_value        CHAR,
     prop_type         NUMBER          DEFAULT 1 NOT NULL,
     encrypted         CHAR            DEFAULT 'N',
     template          CHAR            DEFAULT 'N'
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_BCN_BCNSTEP_PROPS
  (
     target_guid       RAW(16)         NOT NULL,
     step_guid         RAW(16)         NOT NULL,
     bcn_guid          RAW(16)         NOT NULL,
     name              VARCHAR2(64)    NOT NULL,
     string_part       NUMBER          DEFAULT 0 NOT NULL,
     string_value      VARCHAR2(4000),
     num_value         NUMBER,
     date_value        DATE,
     char_value        CHAR,
     prop_type         NUMBER          DEFAULT 1 NOT NULL,
     encrypted         CHAR            DEFAULT 'N',
     template          CHAR            DEFAULT 'N'
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

-- Indices for MGMT_BCN_STEP_DEFN table
ALTER TABLE MGMT_BCN_STEP_DEFN
   ADD CONSTRAINT bcn_txn_step_defn_primary_key
       PRIMARY KEY (target_guid, txn_guid, step_guid, step_type, name)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_STEPGROUP_DEFN table
ALTER TABLE MGMT_BCN_STEPGROUP_DEFN
   ADD CONSTRAINT bcn_group_defn_primary_key
       PRIMARY KEY (target_guid, txn_guid, stepgroup_guid, stepgroup_type, name)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_STEPGROUP_STEPS table
ALTER TABLE MGMT_BCN_STEPGROUP_STEPS
   ADD CONSTRAINT bcn_group_steps_primary_key
       PRIMARY KEY (target_guid, txn_guid, stepgroup_guid, step_guid)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_TXN_AUDIT table
ALTER TABLE MGMT_BCN_TXN_AUDIT
   ADD CONSTRAINT bcn_txn_audit_primary_key
       PRIMARY KEY (target_guid, txn_guid, audit_timestamp)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_TXN_PROPS table
ALTER TABLE MGMT_BCN_TXN_PROPS
   ADD CONSTRAINT bcn_txn_props_primary_key
       PRIMARY KEY (target_guid, txn_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_STEP_PROPS table
ALTER TABLE MGMT_BCN_STEP_PROPS
   ADD CONSTRAINT bcn_step_props_primary_key
       PRIMARY KEY (target_guid, step_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_BCNTXN_PROPS table
ALTER TABLE MGMT_BCN_BCNTXN_PROPS
   ADD CONSTRAINT bcn_bcntxn_props_primary_key
       PRIMARY KEY (target_guid, txn_guid, bcn_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


-- Indices for MGMT_BCN_BCNSTEP_PROPS table
ALTER TABLE MGMT_BCN_BCNSTEP_PROPS
   ADD CONSTRAINT bcn_bcnstep_props_primary_key
       PRIMARY KEY (target_guid, step_guid, bcn_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;