Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: severity_data_upgrade.sql 26-aug-2005.05:24:56 neearora Exp $
Rem
Rem severity_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_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    08/26/05 - Bug 4531777. Change the value of 
Rem                           violation_duration from 0 to null 
Rem    jsadras     08/17/05 - Bug:4548286, fix null violation type 
Rem    gan         08/05/05 - add paramter for repo sev 
Rem    chyu        07/18/05 - adding the upgrade header 
Rem    jsadras     07/16/05 - jsadras_bug-4339821
Rem    jsadras     07/14/05 - Add cfg columns data upgrade
Rem

DECLARE 
TYPE rowidtab IS TABLE OF ROWID INDEX BY BINARY_INTEGER ;
TYPE varchartab IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER ;
l_rowids rowidtab ;
l_coll_names varchartab ;
  -- get violations which had matching key configuration
  -- note in 10.1 only direct equals was allowed, no wild card specs
  CURSOR matched_keys_cur IS
    SELECT curr_viol.rowid,
           cfg.coll_name
      FROM mgmt_current_violation curr_viol,
           mgmt_policy_assoc_cfg_params cfg
     WHERE curr_viol.target_guid = cfg.object_guid
       AND curr_viol.policy_guid = cfg.policy_guid
       AND curr_viol.key_value   = cfg.key_value
       AND curr_viol.cfg_key_value IS NULL ;

 -- get all other violations which did not have matching configuration
 -- cfg_key_value is not null ensures we skip all rows updated by matched keys
  CURSOR blank_matched_cur IS
    SELECT curr_viol.rowid,
           cfg.coll_name
      FROM mgmt_current_violation curr_viol,
           mgmt_policy_assoc_cfg_params cfg
     WHERE curr_viol.target_guid = cfg.object_guid
       AND curr_viol.policy_guid = cfg.policy_guid
       AND cfg.key_value         = ' '
       AND cfg.key_operator      = 0
       AND curr_viol.cfg_key_value IS NOT NULL ;
l_commit_frequency NUMBER ;
BEGIN
  BEGIN
    SELECT parameter_value
      INTO l_commit_frequency
      FROM mgmt_parameters
     WHERE parameter_name = 'upgrade.upgrade_severities_batch_size' ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_commit_frequency := 10000 ;
  END ;
  -- Update all keys which had matching key configuration
  OPEN matched_keys_cur ;
  LOOP
    FETCH matched_keys_cur 
    BULK COLLECT INTO l_rowids,l_coll_names
    LIMIT l_commit_frequency ;

    EXIT WHEN matched_keys_cur%NOTFOUND ;

    FORALL i IN l_rowids.FIRST..l_rowids.LAST
      UPDATE mgmt_current_violation
         SET cfg_coll_name = l_coll_names(i),
             cfg_key_value = key_value,
             cfg_key_operator = 0
       WHERE rowid = l_rowids(i) ; 

    COMMIT ;

    EXIT WHEN l_rowids.COUNT < l_commit_frequency ;
  END LOOP ; 
  CLOSE matched_keys_cur ;
  -- update all keys for all else case 
  OPEN blank_matched_cur ;
  LOOP
    FETCH blank_matched_cur 
    BULK COLLECT INTO l_rowids,l_coll_names
    LIMIT l_commit_frequency ;

    EXIT WHEN blank_matched_cur%NOTFOUND ;

    FORALL i IN l_rowids.FIRST..l_rowids.LAST
      UPDATE mgmt_current_violation
         SET cfg_coll_name = l_coll_names(i),
             cfg_key_value = ' ',
             cfg_key_operator = 0
       WHERE rowid = l_rowids(i) ; 

    COMMIT ;

    EXIT WHEN l_rowids.COUNT < l_commit_frequency ;
  END LOOP ; 
  CLOSE blank_matched_cur ;

END ;
/
--
-- if there are any violations in which do not have violation type
-- set them to 0. They are annotation records
--
BEGIN
  UPDATE mgmt_violations
     SET violation_guid = NVL(violation_guid,SYS_GUID()),
         violation_type = 0
   WHERE violation_type IS NULL ;
 COMMIT ;
END ;
/
show errors;

-- insert REPO_SEV_ENABLE_OPT into mgmt_parameters table
BEGIN
  INSERT into mgmt_parameters (parameter_name, parameter_value, parameter_comment,
                              internal_flag)
          VALUES('REPO_SEV_ENABLE_OPT', '1', 'Enable (1) repository target severity calculation optimization',  1);
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;
/
show errors;


DECLARE
  type rawtab IS TABLE OF RAW(16) index by binary_integer ;
  l_viol_guids rawtab ;
  l_commit_frequency NUMBER := 50000 ;
  l_viol_counter NUMBER := 0;

  l_prev_target_guid mgmt_violations.target_guid%TYPE := '0000000000000000';
  l_prev_policy_guid mgmt_violations.policy_guid%TYPE := '0000000000000000';
  l_prev_key_value   mgmt_violations.key_value%TYPE := '0000000000000000';

  CURSOR avail_violations_cur IS
    SELECT target_guid,policy_guid, key_value, collection_timestamp,violation_guid
     FROM mgmt_violations
    WHERE violation_duration=0
     ORDER BY target_guid,policy_guid, key_value, collection_timestamp DESC;

BEGIN
  BEGIN
    SELECT parameter_value
      INTO l_commit_frequency
      FROM mgmt_parameters
     WHERE parameter_name = 'viol_duration_upgrade_batchsize' ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_commit_frequency := 50000 ;
  END ;

  FOR cur_viol in avail_violations_cur LOOP
    IF(NOT (cur_viol.target_guid = l_prev_target_guid AND
            cur_viol.policy_guid = l_prev_policy_guid AND
            cur_viol.key_value   = l_prev_key_value))
      THEN
        l_viol_counter := l_viol_counter + 1;
        l_viol_guids(l_viol_counter) := cur_viol.violation_guid;
       END IF;

    l_prev_target_guid :=  cur_viol.target_guid;
    l_prev_policy_guid :=  cur_viol.policy_guid;
    l_prev_key_value   :=  cur_viol.key_value;

    IF(l_viol_counter = l_commit_frequency) THEN
      FORALL i IN l_viol_guids.FIRST..l_viol_guids.LAST
         UPDATE mgmt_violations
            SET violation_duration = null
         WHERE violation_guid = l_viol_guids(i);
         COMMIT;

      l_viol_guids.DELETE;  -- Clear the temporary row guid list.

      l_viol_counter := 0;
    END IF;
  END LOOP;

  IF(l_viol_counter > 0) THEN
      FORALL i IN l_viol_guids.FIRST..l_viol_guids.LAST
         UPDATE mgmt_violations
            SET violation_duration = null
         WHERE violation_guid = l_viol_guids(i);
         COMMIT;
  END IF;
END;
/
show errors;