Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="metric/metric_data_upgrade.sql+"/>
Rem
Rem $Header: notification_data_upgrade.sql 08-sep-2005.02:27:11 neearora Exp $
Rem
Rem notification_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      notification_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    neearora    09/08/05 - Bug 4597635. added is_renderable while fetching 
Rem                           the cursor 
Rem    dcawley     08/15/05 - Add RCA queue 
Rem    dcawley     08/11/05 - Migrate rules with % for metric 
Rem    chyu        07/26/05 - modifying the migrate header position 
Rem    chyu        07/18/05 - updating the new rep manager header 
Rem    rpinnama    05/12/05 - Move common init to v102010/notification_init
Rem    dcawley     02/16/05 - Migrate rule data 
Rem    dcawley     02/11/05 - dcawley_user_model_mig_to_10_2
Rem    dcawley     02/09/05 - Created
Rem

-- Perform v102010 notif init
@@&&EM_SQL_ROOT/core/v102010/notification_init.sql

Rem
Rem Add the RCA queue
Rem
INSERT INTO MGMT_NOTIFY_QUEUES (QNAME, OMS_ID, WINDOWS) 
VALUES ('RCA1', 0, 0);
COMMIT; 

Rem
Rem Migrate the rules to the new version of the notification tables
Rem
DECLARE
   l_metrics MGMT_PREFERENCES.CURSOR_TYPE;
   l_keys MGMT_PREFERENCES.CURSOR_TYPE;
   l_metric_column VARCHAR2(64);
   l_column_label VARCHAR2(64);
   l_column_nlsid VARCHAR2(64);
   l_metric VARCHAR2(64);
   l_metric_label VARCHAR2(64);
   l_metric_nlsid VARCHAR2(64);
   l_key_column VARCHAR2(512);
   l_is_renderable mgmt_metrics.is_renderable%TYPE;
BEGIN

  FOR rule IN (SELECT * FROM MGMT_NOTIFY_RULES_10_1)
  LOOP
    -- Insert rule into new table
    INSERT INTO MGMT_NOTIFY_RULES (rule_name, owner, description, public_rule)
    VALUES (rule.rule_name, rule.owner, rule.description, rule.public_rule);
    FOR config IN (SELECT * FROM MGMT_NOTIFY_RULE_CONFIGS_10_1
                   WHERE rule_name = rule.rule_name and owner = rule.owner)
    LOOP 
      -- Check to see if this is a configuation for all metric
      IF config.metric_name = '%' and config.metric_column = '%'
      THEN 
        -- Get the list of metrics. The keys can be ignored because once
        -- a key is specified the metric list is expanded so there will
        -- be one row per metric so the ELSE part of this IF statement
        -- will be executed for each row
        MGMT_PREFERENCES.GET_METRICS_FOR_RULE(config.target_type, NULL,
                                              l_metrics, l_keys);

        LOOP
          FETCH l_metrics INTO l_metric_column, l_column_label, l_column_nlsid,
                               l_metric, l_metric_label, l_metric_nlsid,
                               l_key_column, l_is_renderable;
          EXIT WHEN l_metrics%NOTFOUND;
          INSERT INTO MGMT_NOTIFY_RULE_CONFIGS
            (rule_name, owner, target_type,
             target_name, metric_name, metric_column,
             key_value, want_clears, want_warnings,
             want_critical_alerts, want_target_up,
             want_target_down, want_target_unreachable_start,
             want_target_unreachable_end,
             want_target_metric_err_start, want_target_metric_err_end,
             want_target_blackout_start, want_target_blackout_end)
          VALUES
            (config.rule_name, config.owner, config.target_type,
             config.target_name, l_metric, l_metric_column,
             config.key_value, rule.want_clears, rule.want_warnings,
             rule.want_critical_alerts, rule.want_target_up,
             rule.want_target_down, rule.want_target_unreachable_start,
             rule.want_target_unreachable_end,
             rule.want_target_metric_err_start, rule.want_target_metric_err_end,
             rule.want_target_blackout_start, rule.want_target_blackout_end);
        END LOOP;
      ELSE
        INSERT INTO MGMT_NOTIFY_RULE_CONFIGS
          (rule_name, owner, target_type,
           target_name, metric_name, metric_column,
           key_value, want_clears, want_warnings,
           want_critical_alerts, want_target_up,
           want_target_down, want_target_unreachable_start,
           want_target_unreachable_end,
           want_target_metric_err_start, want_target_metric_err_end,
           want_target_blackout_start, want_target_blackout_end)
        VALUES
          (config.rule_name, config.owner, config.target_type,
           config.target_name, config.metric_name, config.metric_column,
           config.key_value, rule.want_clears, rule.want_warnings,
           rule.want_critical_alerts, rule.want_target_up,
           rule.want_target_down, rule.want_target_unreachable_start,
           rule.want_target_unreachable_end,
           rule.want_target_metric_err_start, rule.want_target_metric_err_end,
           rule.want_target_blackout_start, rule.want_target_blackout_end);
      END IF;
    END LOOP;
    COMMIT;
  END LOOP;

  -- Drop the old tables
  EXECUTE IMMEDIATE 'DROP TABLE MGMT_NOTIFY_RULE_CONFIGS_10_1';
  EXECUTE IMMEDIATE 'DROP TABLE MGMT_NOTIFY_RULES_10_1';

  -- Recreate the foreign key for the notifyee table
  EXECUTE IMMEDIATE 'ALTER TABLE MGMT_NOTIFY_NOTIFYEES ADD CONSTRAINT MGMT_NOTIFY_NOTIFYEES_FK_RULE FOREIGN KEY (rule_name, owner) REFERENCES MGMT_NOTIFY_RULES (rule_name, owner) ON DELETE CASCADE';

END;
/

COMMIT;