Rem drv: <migrate type="schema_upgrade" version="10.2.0.1" pos="system_dashboard/system_dashboard_schema_upgrade.sql-"/>
Rem
Rem $Header: customize_schema_upgrade.sql 26-jul-2005.18:27:58 chyu Exp $
Rem
Rem customize_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      customize_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    chyu        07/26/05 - make this file be executed prior to 
Rem                           system_dashboard/system_dashboard_schema_upgrade.sql 
Rem    paachary    07/26/05 - paachary_bug-4493173
Rem    paachary    07/19/05 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

CREATE TABLE MGMT_CSTMZ_SUMMARY_CHART_DEF
    (
        comp_chart_guid    RAW(16)         NOT NULL,
        comp_target_guid   RAW(16)         NOT NULL,
        comp_metric_guid   RAW(16)         NOT NULL,
        min_column         NUMBER(1)   DEFAULT 0,
        max_column         NUMBER(1)   DEFAULT 0,
        avg_column         NUMBER(1)   DEFAULT 0,
        sum_column         NUMBER(1)   DEFAULT 0,
        stdev_column       NUMBER(1)   DEFAULT 0
    )
  MONITORING;

REM
REM PURPOSE
REM  maintain default charts for a group
REM COLUMNS
REM   COMP_TARGET_TYPE        type for the composite target
REM   CHART_TYPE              See definitions in em_groups_ui
REM			                  GROUP_CHART_PEAK_TARGETS := 2;
REM			                  GROUP_CHART_HIGHEST_TARGETS := 3;
REM			                  GROUP_CHART_LOWEST_TARGETS := 4;
REM			                  GROUP_CHART_SUMMARY_METRIC := 5;
REM   MEMBER_TARGET_TYPE      member target type for which 
REM                                we are defining the charts
REM   MEMBER_METRIC_NAME      member metric name for which 
REM                                 we are defining the charts
REM   MEMBER_METRIC_COLUMN    member metric column for which
REM                                 we are defining the charts
REM   TARGETS_COUNT           Number of targets to be displayed 
rem     MIN_COLUMN
rem            - set to 1 if 'minimum' summary metric is being collected.
rem     MAX_COLUMN
rem            - set to 1 if 'max' summary metric is being collected.
rem     AVG_COLUMN
rem            - set to 1 if 'avg' summary metric is being collected.
rem     SUM_COLUMN
rem            - set to 1 if 'sum' summary metric is being collected.
rem     STDEV_COLUMN
rem            - set to 1 if 'stdev' summary metric is being collected.
REM
REM   DISPLAY_ORDER           display order of charts within a composite
REM                      target type+member target type combo

CREATE TABLE MGMT_CSTMZ_DEFAULT_CHART
(
        comp_target_type        VARCHAR2(64)   NOT NULL ,
        chart_type              NUMBER(1)      NOT NULL ,
        member_target_type      VARCHAR2(64)   NOT NULL ,
        member_metric_name      VARCHAR2(64)   NOT NULL ,
        member_metric_column    VARCHAR2(64)   NOT NULL ,
        targets_count           NUMBER(4) ,              
        min_column              NUMBER(1)   DEFAULT 0,
        max_column              NUMBER(1)   DEFAULT 0,
        avg_column              NUMBER(1)   DEFAULT 0,
        sum_column              NUMBER(1)   DEFAULT 0,
        stdev_column            NUMBER(1)   DEFAULT 0,
        display_order           NUMBER(2)   DEFAULT 0,
    CONSTRAINT mgmt_cstmz_default_chart PRIMARY KEY
    (comp_target_type, chart_type, member_target_type, 
     member_metric_name, member_metric_column)
)
MONITORING;

REM
REM PURPOSE
REM  maintain customized charts for a group
REM COLUMNS
REM   COMP_CHART_GUID         Composite chart guid 
REM   COMP_TARGET_GUID        guid for the composite target
REM   CHART_TYPE              See definitions in emd_mntr_comptgt
REM			                  GROUP_CHART_SELECTED_TARGETS := 1;
REM			                  GROUP_CHART_PEAK_TARGETS := 2;
REM			                  GROUP_CHART_HIGHEST_TARGETS := 3;
REM			                  GROUP_CHART_LOWEST_TARGETS := 4;
REM			                  GROUP_CHART_SUMMARY_METRIC := 5;
REM   MEMBER_METRIC_GUID      metric_guid for the metric column to be displayed
REM   DISPLAY_ORDER           display order for the chart
REM   TARGETS_COUNT           Number of targets to be displayed 
REM
CREATE TABLE MGMT_CSTMZ_CHARTS
        (
            comp_chart_guid         RAW(16)        DEFAULT SYS_GUID() NOT NULL  ,
            comp_target_guid        RAW(16)        NOT NULL  ,
            chart_type              NUMBER(1)      NOT NULL  ,
            member_metric_guid      RAW(16)                  ,
            display_order           NUMBER(3)                ,
            targets_count           NUMBER(4)              
        )
MONITORING;


REM
REM PURPOSE
REM  maintains set of targets for the chart 
REM COLUMNS
REM   COMP_CHART_GUID       to uniquely identify the chart
REM   TARGET_GUID           GUID of the target being added to this chart
CREATE TABLE MGMT_CSTMZ_CHART_SELTARGETS
        (
            comp_chart_guid         RAW(16)        NOT NULL  ,
            target_guid             RAW(16)                  
        )
MONITORING;


REM
REM PURPOSE
REM  maintain customized columns for a group
REM COLUMNS
REM   COMPOSITE_TARGET_GUID   Composite target guid
REM   COLUMN_TYPE             See definitions in emd_mntr_comptgt
REM			                  GROUP_CUSTOM_COLUMNTYPE_METRIC := 0;
REM			                  GROUP_CUSTOM_COLUMNTYPE_STATUS := 1;
REM			                  GROUP_CUSTOM_COLUMNTYPE_ALERTS := 2;
REM			                  GROUP_CUSTOM_COLUMNTYPE_POLICY := 3;
REM			                  GROUP_CUSTOM_COLUMNTYPE_PROPERTY := 4;
REM   MEMBER_METRIC_GUID      metric_guid for the metric column to be displayed
REM			                  Applicable only if colmn_type=METRIC_COLUMN_TYPE
REM   PROPERTY_NAME	          the name of the target property if the column
REM			                  is a user defined property
REM                           Applicable only if column_type=PROPERTY_COLUMN_TYPE
REM   TARGET_TYPE	          the target type for the specified property.
REM                           Applicable only if column_type=PROPERTY_COLUMN_TYPE
REM                           If the property type is generic (applies to all targets),
REM                           set it to space.
REM                           todo: The (Property Name, Target Type) must be replaced
REM                           by PropertyGUID when the property GUID support
REM                           is available
REM   ABBREVIATION	          64 (Max) letter abbreviation for this column, to be 
REM			                  displayed in the dashboard
REM   DISPLAY_ORDER	          Display order for this column
REM 
REM   VIEW_TYPE 	          for future. the "View By" type in the dropdown
REM 

CREATE TABLE MGMT_CSTMZ_CUSTOM_COLUMNS
        (composite_target_guid    RAW(16)        NOT NULL  ,
         column_type              NUMBER(2)      DEFAULT 0 ,
         member_metric_guid       RAW(16)                  ,
         property_name            VARCHAR2(64)             ,
         target_type              VARCHAR2(64)             ,
         abbreviation             VARCHAR2(64)             ,
         display_order            NUMBER(3)                ,
         view_type                NUMBER(1)      DEFAULT 0
        )
MONITORING;

-- type for custom chart for a group
create or replace type MGMT_CSTMZ_CHART as OBJECT (
    target_type    VARCHAR2(64),
    metric_name    VARCHAR2(64),
    metric_column  VARCHAR2(64),
    chart_type     NUMBER(1),
    display_order  NUMBER(3),
    targets_count  NUMBER(4),
    min_column     NUMBER(1),
    max_column     NUMBER(1),
    avg_column     NUMBER(1),
    sum_column     NUMBER(1),
    stdev_column   NUMBER(1),
    sel_targets    SMP_EMD_NVPAIR_ARRAY
);
/

create or replace type MGMT_CSTMZ_CHART_ARRAY as TABLE of MGMT_CSTMZ_CHART;
/

-- type for custom column for a group
create or replace type MGMT_CSTMZ_COLUMN as OBJECT (
    target_type    VARCHAR2(64),
    metric_name    VARCHAR2(64),
    metric_column  VARCHAR2(64),
    metric_guid    RAW(16),
    column_type    NUMBER(2),
    display_order  NUMBER(3),
    property_name  VARCHAR2(64),
    abbreviation   VARCHAR2(64)
);
/

create or replace type MGMT_CSTMZ_COLUMN_ARRAY as TABLE of MGMT_CSTMZ_COLUMN;
/
-- type for display the custom chart
create or replace type MGMT_CSTMZ_CHART_DISP_OBJ as OBJECT (
    target_guid RAW(16),
    metric_guid RAW(16),
    key_value VARCHAR2(256),
    target_name VARCHAR2(256),
    timezone_region VARCHAR2(64)
);
/

create or replace type MGMT_CSTMZ_CHART_DISP_ARRAY as TABLE of MGMT_CSTMZ_CHART_DISP_OBJ;
/
CREATE OR REPLACE TYPE MGMT_CSTMZ_AGGR_MEMBERS AS OBJECT
(
   source_target_name VARCHAR(256),
   source_target_type VARCHAR(64),
   source_target_guid RAW(16),
   assoc_target_name VARCHAR(256),
   assoc_target_type VARCHAR(64),
   assoc_target_guid RAW(16)
);
/

CREATE OR REPLACE TYPE MGMT_CSTMZ_AGGR_MEMBERS_ARRAY AS TABLE OF MGMT_CSTMZ_AGGR_MEMBERS;
/

CREATE OR REPLACE TYPE CSTMZ_TARGET_DETAILS AS OBJECT
(
   target_guid RAW(16),
   target_name VARCHAR(256),
   target_type VARCHAR(64),
   warning_alerts NUMBER,
   critical_alerts NUMBER,
   information_pv NUMBER,
   warning_pv NUMBER,
   critical_pv NUMBER,
   current_status NUMBER,
   host_name VARCHAR(256),
   metrics_defined NUMBER,
   response_defined NUMBER
);
/

CREATE OR REPLACE TYPE CSTMZ_GUID_ARRAY AS TABLE OF RAW(16);
/
CREATE OR REPLACE TYPE CSTMZ_ROLLED_UP_DETAILS AS OBJECT
(
   target_guid RAW(16),
   target_name VARCHAR(256),
   target_type VARCHAR(64),
   num_members NUMBER,
   num_up NUMBER,
   num_down NUMBER,
   num_blacked_out NUMBER,
   num_unknown NUMBER,
   warning_alerts NUMBER,
   critical_alerts NUMBER,
   information_pv NUMBER,
   warning_pv NUMBER,
   critical_pv NUMBER,
   current_status NUMBER,
   host_name VARCHAR(256),
   mem_count_guids CSTMZ_GUID_ARRAY,
   alerts_pv_guids CSTMZ_GUID_ARRAY
);
/

CREATE OR REPLACE TYPE CSTMZ_ROLLED_UP_DETAILS_ARRAY AS TABLE OF CSTMZ_ROLLED_UP_DETAILS;
/