Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: test_metadata_data_upgrade.sql 30-sep-2005.12:49:13 afontana Exp $
Rem
Rem test_metadata_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      test_metadata_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    afontana    09/30/05 - fix broken cursor 
Rem    afontana    09/29/05 - fix last for update of clause 
Rem    afontana    09/27/05 - bug 4635818 
Rem    afontana    08/16/05 - afontana_bug-4519196
Rem    afontana    08/11/05 - Created
Rem

-- migrate unchanged DNS defaults
DECLARE

  CURSOR c_props_timeout IS
    SELECT name, num_value
      FROM MGMT_BCN_TXN_PROPS
     WHERE (target_guid, txn_guid, name) IN
           (SELECT p.target_guid, p.txn_guid, p.name
              FROM MGMT_BCN_TXN_PROPS p,
                   MGMT_BCN_TXN_DEFN d
             WHERE p.target_guid = d.target_guid
               AND d.txn_guid = p.txn_guid
               AND d.txn_type = 'DNS'
               AND p.name = 'timeout' 
               AND p.num_value = 60)
       FOR UPDATE OF num_value;

  CURSOR c_props_retry IS
    SELECT name, num_value
      FROM MGMT_BCN_TXN_PROPS
     WHERE (target_guid, txn_guid, name) IN
           (SELECT p.target_guid, p.txn_guid, p.name
              FROM MGMT_BCN_TXN_PROPS p,
                   MGMT_BCN_TXN_DEFN d
             WHERE d.target_guid = p.target_guid
               AND d.txn_guid = p.txn_guid
               AND d.txn_type = 'DNS'
               AND p.name = 'retryinterval' 
               AND p.num_value = 60)
       FOR UPDATE OF num_value;

BEGIN

  FOR v_props_timeout IN c_props_timeout LOOP

    UPDATE MGMT_BCN_TXN_PROPS
       SET num_value = 15
     WHERE CURRENT OF c_props_timeout;

  END LOOP;

  FOR v_props_retry IN c_props_retry LOOP

    UPDATE MGMT_BCN_TXN_PROPS
       SET num_value = 1
     WHERE CURRENT OF c_props_retry;

  END LOOP;


END;
/
show errors;


-- migrate HTTP useCache from "0" / "1" to "no" / "yes"
DECLARE

  CURSOR c_props_usecache_1 IS
    SELECT name, string_value
      FROM MGMT_BCN_TXN_PROPS
     WHERE (target_guid, txn_guid, name) IN
           (SELECT p.target_guid, p.txn_guid, p.name
              FROM MGMT_BCN_TXN_PROPS p,
                   MGMT_BCN_TXN_DEFN d
             WHERE d.target_guid = p.target_guid
               AND d.txn_guid = p.txn_guid
               AND d.txn_type = 'HTTP'
               AND p.name = 'useCache' 
               AND p.string_value = '1')
       FOR UPDATE OF string_value;

  CURSOR c_props_usecache_0 IS
    SELECT name, string_value
      FROM MGMT_BCN_TXN_PROPS
     WHERE (target_guid, txn_guid, name) IN
           (SELECT p.target_guid, p.txn_guid, p.name
              FROM MGMT_BCN_TXN_PROPS p,
                   MGMT_BCN_TXN_DEFN d
             WHERE d.target_guid = p.target_guid
               AND d.txn_guid = p.txn_guid
               AND d.txn_type = 'HTTP'
               AND p.name = 'useCache' 
               AND p.string_value = '0')
       FOR UPDATE OF string_value;

BEGIN

  FOR v_props_usecache_1 IN c_props_usecache_1 LOOP

    UPDATE MGMT_BCN_TXN_PROPS
       SET string_value = 'yes'
     WHERE CURRENT OF c_props_usecache_1;

  END LOOP;

  FOR v_props_usecache_0 IN c_props_usecache_0 LOOP

    UPDATE MGMT_BCN_TXN_PROPS
       SET string_value = 'no'
     WHERE CURRENT OF c_props_usecache_0;

  END LOOP;

END;
/
show errors;


-- migrate unchanged OS timeout default that was created too low
DECLARE

  CURSOR c_props_timeout IS
    SELECT name, num_value
      FROM MGMT_BCN_TXN_PROPS
     WHERE (target_guid, txn_guid, name) IN
           (SELECT p.target_guid, p.txn_guid, p.name
              FROM MGMT_BCN_TXN_PROPS p,
                   MGMT_BCN_TXN_DEFN d
             WHERE p.target_guid = d.target_guid
               AND d.txn_guid = p.txn_guid
               AND d.txn_type = 'OS'
               AND p.name = 'Timeout' 
               AND p.num_value = 1)
       FOR UPDATE OF num_value;

BEGIN

  FOR v_props_timeout IN c_props_timeout LOOP

    UPDATE MGMT_BCN_TXN_PROPS
       SET num_value = 60
     WHERE CURRENT OF c_props_timeout;

  END LOOP;

END;
/
show errors;


-- migrate PING mode from number to string to accomodate choices in UI
-- it seems that watchlist items are still using numbers (not strings)
DECLARE
  CURSOR c_props_mode IS
    SELECT name, num_value, string_value, prop_type
      FROM MGMT_BCN_TXN_PROPS
     WHERE (target_guid, txn_guid, name) IN
           (SELECT p.target_guid, p.txn_guid, p.name
              FROM MGMT_BCN_TXN_PROPS p,
                   MGMT_BCN_TXN_DEFN d,
                   MGMT_TARGETS t
             WHERE p.target_guid = d.target_guid
               AND p.target_guid = t.target_guid
               AND t.target_type <> 'oracle_beacon'
               AND d.txn_guid = p.txn_guid
               AND d.txn_type = 'PING'
               AND p.name = 'mode'
               AND p.num_value IS NOT NULL)
       FOR UPDATE OF num_value, string_value, prop_type;
BEGIN
  FOR v_props_mode IN c_props_mode LOOP
    UPDATE MGMT_BCN_TXN_PROPS
       SET num_value = NULL, 
           string_value = to_char(v_props_mode.num_value),
           prop_type = EMD_BCNTXN.p_string_prop_type
     WHERE CURRENT OF c_props_mode;
  END LOOP;
END;
/
show errors;