Rem drv: <create type="tables" params="EM_ECM_DEPOT_TABLESPACE"/>
Rem
Rem $Header: group_tables.sql 12-jul-2006.05:41:37 bram Exp $
Rem
Rem group_tables.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      group_tables.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    bram        07/12/06 - Backport bram_bug-5137855 from main 
Rem    bram        04/06/06 - fix for bug 5137855
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    pmodi       06/19/05 - Bug:4305824 -Removing table mgmt_comp_target_def 
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    dtsao       01/17/05 - Fix column type comment 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    dtsao       12/15/04 - Change limit of abbreviation size to 64 
Rem    yaofeng     11/10/04 - add new columns to mgmt_group_default_chart 
Rem    streddy     11/04/04 - Added MGMT_GROUP_DEFAULT_CHART table 
Rem    vagarwal    09/17/04 - 
Rem    vagarwal    09/09/04 - add table for custom columns 
Rem    ramalhot    08/24/04 - cutover to new assoc tables 
Rem    streddy     09/22/03 - Added flat memberships table 
Rem    streddy     08/04/03 - Disable group history tables 
Rem    streddy     04/08/03 - Add history tables
Rem    skini       07/12/02 - Change in target_name column size
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

rem
rem  PURPOSE
rem
rem     This table holds holds the definition of the 
rem     summary charts for groups.
rem
rem  COLUMNS
rem
rem     COMP_TARGET_GUID
rem            - the guid for a target entry in the MGMT_TARGETS table
rem
rem     COMP_METRIC_GUID
rem            - the guid for a metric entry in the MGMT_METRICS table
rem                  for a metric level entry and not metric column level entry
rem             This will be the same for all group targets. It is present
rem             here to allow it to be used by other target types like system.
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.

CREATE TABLE MGMT_GROUP_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 the history of composite membership changes
REM COLUMNS
REM   COMPOSITE_TARGET_GUID   Composite target guid
REM   START_TIMESTAMP         Timestamp since this membership is valid.
REM   END_TIMESTAMP           Timestamp till which membership is valid.
REM   MEMBER_TARGETS          List of member targets as of this timestamp
REM 
/*
CREATE TABLE MGMT_TARGET_MEMBERSHIPS_HIST
(  composite_target_guid  RAW(16)         NOT NULL,
   start_timestamp        DATE            NOT NULL,
   end_timestamp          DATE,
   member_targets         mgmt_member_guids
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
  NESTED TABLE member_targets STORE AS member_targets_st
  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_GROUP_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_group_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_GROUP_CHART
        (
            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_GROUP_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 em_groups_ui
REM			                  G_COLUMN_TYPE_METRIC := 0;
REM			                  G_COLUMN_TYPE_PROPERTY := 1;
REM			                  G_COLUMN_TYPE_GEN_PROPERTY := 2;
REM			                  G_COLUMN_TYPE_GENERAL := 3;
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_GROUP_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;

@&EM_SQL_ROOT/core/10.2.0.3/group/groups_tables.sql