Rem drv: <migrate type="data_upgrade" version="10.1.0.4"/> 
Rem
Rem $Header: basic_data_upgrade.sql 13-jan-2005.00:57:51 ktlaw Exp $
Rem
Rem basic_data_upgrade.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      basic_data_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    ktlaw       01/13/05 - add repmgr header 
Rem    rpinnama    06/28/04 - rpinnama_bug-3680468
Rem    rpinnama    06/28/04 - Created
Rem

-- Remove flavored Response/Status metrics for host and 
-- replace them with generic Response/Status metric.
-- This is required to fix bug 3680468 

-- Disable triggers on MGMT_METRICS
ALTER TABLE mgmt_metrics DISABLE ALL TRIGGERS;


-- Delete the flavoured metrics and add generic metrics
DECLARE

  CURSOR type_meta_ver_cur ( c_target_type VARCHAR2,
                             c_metric_name VARCHAR2,
			     c_metric_column VARCHAR2) IS
    SELECT distinct(type_meta_ver)
      FROM MGMT_METRICS
     WHERE target_type = c_target_type
       AND metric_name = c_metric_name
       AND metric_column = c_metric_column
     ORDER BY type_meta_ver;

  TYPE typ_meta_ver_arr_v101040 IS TABLE OF VARCHAR2(8) 
    INDEX BY BINARY_INTEGER;

  l_meta_ver_list typ_meta_ver_arr_v101040;
  l_resp_guid      RAW(16);
  l_resp_stat_guid RAW(16);

  l_host_type      VARCHAR2(16);
  l_response_str   VARCHAR2(16);
  l_resp_stat_str  VARCHAR2(16);

BEGIN

    l_host_type     := 'host';
    l_response_str  := 'Response';
    l_resp_stat_str := 'Status';

    l_resp_guid      :=  dbms_obfuscation_toolkit.md5(
                          input => utl_raw.cast_to_raw(l_host_type ||
                            ';'|| l_response_str ));

    l_resp_stat_guid :=  dbms_obfuscation_toolkit.md5(
                          input => utl_raw.cast_to_raw(l_host_type ||
                            ';'|| l_response_str || ';' || l_resp_stat_str));


    -- Read type meta ver list
    OPEN type_meta_ver_cur(l_host_type, l_response_str, l_resp_stat_str);
    FETCH type_meta_ver_cur BULK COLLECT INTO l_meta_ver_list;
    CLOSE type_meta_ver_cur;

    -- Remove Response table metric and Response/Status metric column
    -- for all flavours of avaialbility metric.
    DELETE FROM MGMT_METRICS
     WHERE target_type = l_host_type
       AND metric_name = l_response_str;

    -- Iterate through the type_meta_ver list and create Response/S
    IF (l_meta_ver_list.COUNT > 0) THEN
      FOR i IN l_meta_ver_list.FIRST..l_meta_ver_list.LAST
      LOOP
        -- Instead of API, use a direct insert
        -- As the EMD_SCHEMA API is being obsoleted
        INSERT INTO MGMT_METRICS 
          (target_type, metric_name, metric_column, metric_guid, metric_type, 
	  type_meta_ver, category_prop_1, category_prop_2, category_prop_3, 
	  category_prop_4, category_prop_5,
          metric_label, column_label, short_name, description, unit)
         VALUES
          (l_host_type, l_response_str, ' ', l_resp_guid, 2,
	  l_meta_ver_list(i), ' ', ' ', ' ', ' ', ' ', 
	  l_response_str, ' ', l_response_str, ' ', ' ');

        INSERT INTO MGMT_METRICS 
          (target_type, metric_name, metric_column, metric_guid, metric_type, 
	  type_meta_ver, category_prop_1, category_prop_2, category_prop_3, 
	  category_prop_4, category_prop_5,
          metric_label, column_label, short_name, description, unit)
       VALUES
        (l_host_type, l_response_str, l_resp_stat_str, l_resp_stat_guid, 0,
	l_meta_ver_list(i), ' ', ' ', ' ', ' ', ' ', 
	l_response_str, l_resp_stat_str, l_resp_stat_str, ' ', ' ');

    END LOOP;
    END IF;

    COMMIT;
    
END;
/

-- Enable triggers on MGMT_METRICS
ALTER TABLE mgmt_metrics ENABLE  ALL TRIGGERS;