Rem drv: <create type="tables"/>
Rem
Rem $Header: test_metadata_tables.sql 29-jun-2005.01:48:08 gsbhatia Exp $
Rem
Rem test_metadata_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      test_metadata_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Tables for test medatada used for remotely monitored services.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    afontana    11/16/04 - test metadata enhancements 
Rem    afontana    11/11/04 - add threshold operator 
Rem    afontana    10/05/04 - add encrypt field to MGMT_TEST_METRIC_PROPS 
Rem    afontana    09/10/04 - afontana_test_meta_repos
Rem    afontana    09/09/04 - add password attribute to properties 
Rem    afontana    08/30/04 - add levels 
Rem    afontana    08/09/04 - Created
Rem


Rem   TABLE
Rem     MGMT_TEST_METADATA
Rem   PURPOSE
Rem     Stores basic metadata associated with each test type.
Rem   PRIMARY KEY
Rem     TEST_TYPE
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     LABEL                 - NLS display name
Rem     NLSID                 - NLS display id
Rem     COLLECTION_GENERATOR  - Java class of the collection generator
Rem     MIN_BEACON_VER        - Minimum beacon version required for the test
Rem     TEST_VERSION          - Version of the test type
Rem     DEPRECATED            - Set to 'Y' if the test type is deprecated
Rem     AVAIL_TABLE_METRIC    - Name of the beacon-availability table metric
Rem     AVAIL_METRIC_COLUMN   - Name of the beacon-availability metric column
Rem     HELP_ID               - Help ID for the test
Rem     TIP_TEXT              - Tip Text for the test
Rem     TIP_TEXT_NLSID        - NLSID for the tip text
Rem     VALIDATOR             - Class that will validate TxnData
Rem
CREATE TABLE MGMT_TEST
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    LABEL                   VARCHAR2(64)    NOT NULL,
    NLSID                   VARCHAR2(64)    NOT NULL,
    RESOURCE_BUNDLE         VARCHAR2(4000)  NOT NULL,
    COLLECTION_GENERATOR    VARCHAR2(4000)  NOT NULL,
    MIN_BEACON_VER          VARCHAR2(8)     DEFAULT '1.0' NOT NULL,
    TEST_VERSION            VARCHAR2(8)     DEFAULT '1.0' NOT NULL,
    DEPRECATED              CHAR(1)         DEFAULT 'N' NOT NULL,
    AVAIL_METRIC            VARCHAR2(64)    NOT NULL,
    AVAIL_METRIC_COLUMN     VARCHAR2(64),
    HELP_ID                 VARCHAR2(256),
    TIP_TEXT                VARCHAR2(4000),
    TIP_TEXT_NLSID          VARCHAR2(64),
    VALIDATOR               VARCHAR2(4000)    
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;
  
Rem   TABLE
Rem     MGMT_TEST_TARGET_MAP
Rem   PURPOSE
Rem     Associates a list of tests for each target type
Rem   PRIMARY KEY
Rem     TARGET_TYPE, TARGET_META_VER, TEST_TYPE
Rem   COLUMNS
Rem     TARGET_TYPE           - Name of the target type
Rem     TYPE_META_VER         - Meta version of the target type
Rem
CREATE TABLE MGMT_TEST_TARGET_MAP
  (
    TARGET_TYPE             VARCHAR2(64)    NOT NULL,
    TARGET_META_VER         VARCHAR2(8)     NOT NULL,
    CAT_PROP1               VARCHAR2(64)    DEFAULT ' ' NOT NULL,
    CAT_PROP2               VARCHAR2(64)    DEFAULT ' ' NOT NULL,
    CAT_PROP3               VARCHAR2(64)    DEFAULT ' ' NOT NULL,
    CAT_PROP4               VARCHAR2(64)    DEFAULT ' ' NOT NULL,
    CAT_PROP5               VARCHAR2(64)    DEFAULT ' ' NOT NULL,
    TEST_TYPE               VARCHAR2(64)    NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;
  
  

Rem   TABLE
Rem     MGMT_TEST_METRICS
Rem   PURPOSE
Rem     Stores the table metrics associated with each test type
Rem   PRIMARY KEY
Rem     TEST_TYPE, METRIC_NAME, BEACON_VERSION
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     METRIC_NAME           - Name of the table metric
Rem     METRIC_NUMBER         - Order of the metric (for internal use)
Rem     INTERACTIVE           - Metric may be collected interactively
Rem
CREATE TABLE MGMT_TEST_METRICS
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    METRIC_NAME             VARCHAR2(64)    NOT NULL,
    METRIC_NUMBER           NUMBER          NOT NULL,
    INTERACTIVE             CHAR(1)         DEFAULT 'N' NOT NULL,
    LEVEL_NAME              VARCHAR2(64)    NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;
    
  

Rem   TABLE
Rem     MGMT_TEST_MCOLUMNS
Rem   PURPOSE
Rem     Stores the display order of the metric columns
Rem   PRIMARY KEY
Rem     TEST_TYPE, METRIC_NAME, METRIC_COLUMN
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     METRIC_NAME           - Name of the table metric
Rem     METRIC_COLUMN         - Name of the metric column
Rem     DISPLAY_ORDER         - The order of the metric columns
Rem
CREATE TABLE MGMT_TEST_MCOLUMNS
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    METRIC_NAME             VARCHAR2(64)    NOT NULL,
    METRIC_COLUMN           VARCHAR2(64)    NOT NULL,
    DISPLAY_ORDER           NUMBER          NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;



Rem   TABLE
Rem     MGMT_TEST_PROP_UIGROUP
Rem   PURPOSE
Rem     Stores data for grouping properties in the UI
Rem   PRIMARY KEY
Rem     TEST_TYPE, GROUP_NAME
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     GROUP_NAME            - Name of the group
Rem     DISPLAY_ORDER         - Order in which to display this group
Rem     LABEL                 - NLS display name
Rem     NLSID                 - NLS display id
Rem
CREATE TABLE MGMT_TEST_PROP_UIGROUP
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    GROUP_NAME              VARCHAR2(64)    NOT NULL,
    DISPLAY_ORDER           NUMBER          NOT NULL,
    LABEL                   VARCHAR2(64)    NOT NULL,
    NLSID                   VARCHAR2(64)    NOT NULL,
    TIP_TEXT                VARCHAR2(4000),
    TIP_TEXT_NLSID          VARCHAR2(64)
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;
  
  

Rem   TABLE
Rem     MGMT_TEST_PROP
Rem   PURPOSE
Rem     Stores data for test properties
Rem   PRIMARY KEY
Rem     TEST_TYPE, PROP_NAME
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     PROP_NAME             - Property name (internal)
Rem     GROUP_NAME            - Name of the group
Rem     DISPLAY_ORDER         - Order in which to display this property
Rem     LABEL                 - NLS display name
Rem     NLSID                 - NLS display id
Rem     PROPERTY_TYPE         - Property type
Rem     DEFAULT_NUMERIC_VALUE - Default value
Rem     DEFAULT_STRING_VALUE  - Default value
Rem     MAX_VALUE             - Maximum value
Rem     MIN_VALUE             - Minimum value
Rem     HIDDEN                - Property is not for UI if set to 'Y'
Rem     ENCRYPT               - Property requires encryption
Rem     VARIES_PER_BEACON     - Property value may vary per beacon
Rem     TIP_TEXT              - Tip text for the property
Rem     TIP_TEXT_NLSID        - Tip text NLSID for the property
Rem     VALIDATOR             - Validator class for the property
Rem
CREATE TABLE MGMT_TEST_PROP
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    PROP_NAME               VARCHAR2(64)    NOT NULL,
    GROUP_NAME              VARCHAR2(64),
    DISPLAY_ORDER           NUMBER,
    LABEL                   VARCHAR2(64),
    NLSID                   VARCHAR2(64),
    PROPERTY_TYPE           NUMBER          NOT NULL,
    DEFAULT_NUMERIC_VALUE   NUMBER,
    DEFAULT_STRING_VALUE    VARCHAR2(4000),
    MAX_VALUE               NUMBER,
    MIN_VALUE               NUMBER,
    HIDDEN                  CHAR(1)         DEFAULT 'N' NOT NULL,
    ENCRYPT                 CHAR(1)         DEFAULT 'N' NOT NULL,
    PASSWORD                CHAR(1)         DEFAULT 'N' NOT NULL,
    VARIES_PER_BEACON       CHAR(1)         DEFAULT 'N' NOT NULL,
    TIP_TEXT                VARCHAR2(4000),
    TIP_TEXT_NLSID          VARCHAR2(64),
    VALIDATOR               VARCHAR2(4000)
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;


Rem   TABLE
Rem     MGMT_TEST_PROP_LEVEL
Rem   PURPOSE
Rem     Records which levels belong to which properties
Rem   PRIMARY KEY
Rem     TEST_TYPE, PROPERTY, LEVEL
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     PROPERTY              - Property name
Rem     LEVEL                 - Level of the property (e.g. "TXN")
Rem     OPTIONAL              - 'Y' if optional at this level
Rem
CREATE TABLE MGMT_TEST_PROP_LEVEL
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    PROPERTY                VARCHAR2(64)    NOT NULL,
    LEVEL_NAME              VARCHAR2(64)    NOT NULL,
    OPTIONAL                CHAR(1)         DEFAULT 'N' NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

  
  
  
Rem   TABLE
Rem     MGMT_TEST_PROP_CHOICES
Rem   PURPOSE
Rem     Stores data for choice values of a property
Rem   PRIMARY KEY
Rem     TEST_TYPE, PROP_NAME, CHOICE_NAME
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     PROP_NAME             - Name of the property
Rem     CHOICE_NAME           - Internal name of the choice (also the value)
Rem     DISPLAY_ORDER         - Order in which to display this group
Rem     LABEL                 - NLS display name
Rem     NLSID                 - NLS display id
Rem
CREATE TABLE MGMT_TEST_PROP_CHOICES
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    PROP_NAME               VARCHAR2(64)    NOT NULL,
    CHOICE_NAME             VARCHAR2(64)    NOT NULL,
    DISPLAY_ORDER           NUMBER          NOT NULL,
    LABEL                   VARCHAR2(64)    NOT NULL,
    NLSID                   VARCHAR2(64)    NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;
  
  
  
Rem   TABLE
Rem     MGMT_TEST_DEFAULT_PROMOTION
Rem   PURPOSE
Rem     Stores the metadata that describes how data will be promoted
Rem     to the service level by default.
Rem   PRIMARY KEY
Rem     TEST_TYPE, DEST_METRIC, DEST_METRIC_COL, DEST_METRIC_KEY
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     SRC_METRIC_NAME       - Name of the aggregation source table metric
Rem     SRC_METRIC_COLUMN     - Name of the aggregation source metric column
Rem     SRC_METRIC_KEY        - Value of the aggregate source key (after txn,bcn
Rem     EVAL_FUNC             - Name of the evaluation function
Rem     CRITICAL              - Value of the critical threshold
Rem     WARNING               - Value of the warning threshold
Rem     DEST_METRIC           - Name of the aggregation destination metric
Rem     DEST_METRIC_COLUMN    - Name of the agg. destination metric column
Rem     DEST_METRIC_KEY       - Value of the destination metric key part1
Rem     DEFAULT_CHART         - If 'Y' then this is the default chart
Rem
CREATE TABLE MGMT_TEST_DEFAULT_PROMOTION
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    SRC_METRIC_NAME         VARCHAR2(64)    NOT NULL,
    SRC_METRIC_COLUMN       VARCHAR2(64)    NOT NULL,
    SRC_METRIC_KEY1_VALUE   VARCHAR2(64),
    SRC_METRIC_KEY2_VALUE   VARCHAR2(64),
    SRC_METRIC_KEY3_VALUE   VARCHAR2(64),
    SRC_METRIC_KEY4_VALUE   VARCHAR2(64),
    SRC_METRIC_KEY5_VALUE   VARCHAR2(64),
    EVAL_FUNC               VARCHAR2(256)   NOT NULL,
    CRITICAL                NUMBER,
    WARNING                 NUMBER,
    OPERATOR                VARCHAR2(16)    DEFAULT 'GE' NOT NULL,
    NUM_OCCURRENCES         NUMBER,
    DEST_METRIC_NAME        VARCHAR2(64)    NOT NULL,
    DEST_METRIC_COLUMN      VARCHAR2(64)    NOT NULL,
    DEST_METRIC_KEY1_VALUE  VARCHAR2(64),
    DEST_METRIC_KEY2_VALUE  VARCHAR2(64),
    DEST_METRIC_KEY3_VALUE  VARCHAR2(64),
    DEST_METRIC_KEY4_VALUE  VARCHAR2(64),
    DEST_METRIC_KEY5_VALUE  VARCHAR2(64),
    DEFAULT_CHART           CHAR(1)         DEFAULT 'Y' NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;
  
  
  
  
Rem   TABLE
Rem     MGMT_TEST_DEFAULT_THRESHOLDS
Rem   PURPOSE
Rem     Stores the default thresholds on beacon-level metrics.
Rem   PRIMARY KEY
Rem     TEST_TYPE, METRIC_NAME, METRIC_COLUMN, METRIC_KEY
Rem   COLUMNS
Rem     TEST_TYPE             - Name of the test type
Rem     METRIC_NAME           - Name of the beacon table metric
Rem     METRIC_COLUMN         - Name of the beacon metric column
Rem     METRIC_KEY            - Value of the metric key (after txn, bcn)
Rem     CRITICAL              - Value of the critical threshold
Rem     WARNING               - Value of the warning threshold
Rem
CREATE TABLE MGMT_TEST_DEFAULT_THRESHOLDS
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    METRIC_NAME             VARCHAR2(64)    NOT NULL,
    METRIC_COLUMN           VARCHAR2(64)    NOT NULL,
    METRIC_KEY1_VALUE       VARCHAR2(64),
    METRIC_KEY2_VALUE       VARCHAR2(64),
    METRIC_KEY3_VALUE       VARCHAR2(64),
    METRIC_KEY4_VALUE       VARCHAR2(64),
    METRIC_KEY5_VALUE       VARCHAR2(64),
    CRITICAL                NUMBER,
    WARNING                 NUMBER,
    OPERATOR                VARCHAR2(16)    DEFAULT 'GE' NOT NULL,
    NUM_OCCURRENCES         NUMBER
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;



   
Rem   TABLE
Rem     MGMT_TEST_METRIC_PROPS
Rem   PURPOSE
Rem     Stores the metric query descriptor of a metric.
Rem   PRIMARY KEY
Rem     PROPERTY_GUID
Rem   COLUMNS
Rem     PROPERTY_GUID           GUID [target_type, metric_name, property_name]
Rem     TARGET_TYPE             target type of the metric
Rem     TYPE_META_VER           meta version of the target type
Rem     METRIC_NAME             name of the metric (not metric column)
Rem     PROPERTY_NAME           name of the metric property,
Rem     SCOPE                   scope of the property,
Rem     OPTIONAL                property is optional
Rem 
  CREATE TABLE MGMT_TEST_METRIC_PROPS
  (
    PROPERTY_GUID           RAW(16)         NOT NULL,
    TARGET_TYPE             VARCHAR2(64)    NOT NULL,
    TYPE_META_VER           VARCHAR2(8)     NOT NULL,
    METRIC_NAME             VARCHAR2(64)    NOT NULL,
    PROPERTY_NAME           VARCHAR2(64)    NOT NULL,
    SCOPE                   VARCHAR2(64)    DEFAULT 'USER' NOT NULL,
    OPTIONAL                CHAR(1)         DEFAULT 'Y' NOT NULL,
    ENCRYPT                 CHAR(1)         DEFAULT 'Y' NOT NULL
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;


   
Rem   TABLE
Rem     MGMT_TEST_QUALIFIERS
Rem   PURPOSE
Rem     Stores name-value pairs of data associated with a test.
Rem     These will typically refer to things in the gui
Rem     that are not built-in to the metadata
Rem     i.e. "the set of metrics to display on this page for this test..."
Rem     Qualifiers may have either string or numeric values, but 
Rem     they should not have both simultaneously.
Rem   PRIMARY KEY
Rem     TEST_TYPE, QUALIFIER
Rem   COLUMNS
Rem     TEST_TYPE                The id of the test type
Rem     QUALIFIER                The name of the qualifier
Rem     TEXT_VALUE               The value (if it is a string)
Rem     NUMERIC_VALUE            The value (if it is a number)
  CREATE TABLE MGMT_TEST_QUALIFIERS
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    QUALIFIER               VARCHAR2(64)    NOT NULL,    
    TEXT_VALUE              VARCHAR2(4000),
    NUMERIC_VALUE           NUMBER
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;


Rem   TABLE
Rem     MGMT_TEST_PROP_QUALIFIERS
Rem   PURPOSE
Rem     Stores name-value pairs of data associated with a test property.
Rem     These will typically refer to things in the gui
Rem     that are not built-in to the metadata
Rem     i.e. "use this widget of these dimensions for this property"
Rem     Qualifiers may have either string or numeric values, but 
Rem     they should not have both simultaneously.
Rem   PRIMARY KEY
Rem     TEST_TYPE, PROPERTY, QUALIFIER
Rem   COLUMNS
Rem     TEST_TYPE                The id of the test type
Rem     PROPERTY                 The name of the property
Rem     QUALIFIER                The name of the qualifier
Rem     TEXT_VALUE               The value (if it is a string)
Rem     NUMERIC_VALUE            The value (if it is a number)
  CREATE TABLE MGMT_TEST_PROP_QUALIFIERS
  (
    TEST_TYPE               VARCHAR2(64)    NOT NULL,
    PROPERTY                VARCHAR2(64)    NOT NULL,
    QUALIFIER               VARCHAR2(64)    NOT NULL,    
    TEXT_VALUE              VARCHAR2(4000),
    NUMERIC_VALUE           NUMBER
  )
  STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;