Rem Rem $Header: test_metadata_tables.sql 28-jun-2005.22:55:59 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 - Rem Rem DESCRIPTION Rem Tables for test medatada used for remotely monitored services. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem gsbhatia 06/28/05 - Removing repmgr header Rem mfidanbo 02/18/05 - mfidanbo_upgrade_changes1 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;