Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="preferences_schema_upgrade.sql+"/>
Rem $Header: policy_schema_upgrade.sql 26-aug-2005.14:28:58 tthakur Exp $
Rem
Rem policy_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      policy_schema_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    tthakur     08/26/05 - adding the drop components that were obsolete 
Rem                           post beta 
Rem    niramach    08/01/05 - Move the changes between beta and production label 
Rem                           to v102020. 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - modify the version number in header 
Rem    niramach    04/26/05 - Rename policy_*.sql to policy_*_upgrade.sql 
Rem    niramach    04/15/05 - Add upgrade header. 
Rem    rpinnama    02/25/05 - 
Rem    rpinnama    02/06/05 - rpinnama_add_upgrade_scripts
Rem    rpinnama    02/02/05 - Created
Rem

SET ECHO ON

@&EM_SQL_ROOT/core/v102010/policy/policy_types.sql
@&EM_SQL_ROOT/core/v102010/policy/policy_tables.sql
@&EM_SQL_ROOT/core/v102010/policy/policy_indexes.sql


Rem 
Rem Populate data from mgmt_thresholds table to policy tables
Rem 

PROMPT Populating data from mgmt_thresholds table to policy tables
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

DECLARE
  type rawtab IS TABLE OF RAW(16) index by binary_integer ;
  type varchar256tab IS TABLE OF VARCHAR2(256) index by binary_integer ;
  type varchar4ktab IS TABLE OF VARCHAR2(4000) index by binary_integer ;
  type datetab IS TABLE OF DATE index by binary_integer ;
  type numtab is TABLE of NUMBER index by binary_integer;

  l_target_guids   rawtab ;
  l_metric_guids   rawtab ;
  l_coll_names     varchar256tab;
  l_key_values     varchar256tab;
  l_warn_opers     numtab;
  l_warn_thrs      varchar256tab;
  l_crit_opers     numtab;
  l_crit_thrs      varchar256tab;
  l_num_occurs     numtab;
  l_eval_ords      numtab;
  l_fixit_jobs     varchar256tab;
  l_messages       varchar4ktab;
  l_message_nlsids varchar256tab;

  l_commit_frequency NUMBER := 10000 ;

  -- TODO: num_warnings, num_criticals also have to be upgraded
  CURSOR thr_cur IS
    SELECT target_guid, metric_guid, coll_name, key_value, 
           warning_operator, warning_threshold, critical_operator, critical_threshold,
           num_occurences,
           eval_order, fixit_job, message, message_nlsid
      FROM mgmt_metric_thresholds;
BEGIN

  OPEN thr_cur;

  FETCH thr_cur BULK COLLECT INTO
    l_target_guids, l_metric_guids, l_coll_names, l_key_values,
    l_warn_opers, l_warn_thrs, l_crit_opers, l_crit_thrs, l_num_occurs,
    l_eval_ords, l_fixit_jobs, l_messages, l_message_nlsids
    LIMIT l_commit_frequency;

  WHILE ( (l_target_guids IS NOT NULL) AND (l_target_guids.COUNT > 0) )
  LOOP

    FOR i IN l_target_guids.FIRST..l_target_guids.LAST
    LOOP

      -- Add policy assoc
      BEGIN
        INSERT INTO mgmt_policy_assoc 
          (object_guid, policy_guid, coll_name, object_type, policy_type, is_enabled)
         VALUES
          (l_target_guids(i), l_metric_guids(i), l_coll_names(i), 2, 1, 1);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          NULL; -- Ignore PK violation
      END;

      -- Add policy assoc cfg
      -- Default the is_exception, has_active_baseline, prevent_override,
      --    crit_action_job_id, warn_action_job_id, info_action_job_id,
      --    simultaneous_actions, importance, is_push,
      --    clear_message, clear_message_nlsid
      INSERT INTO mgmt_policy_assoc_cfg
        (object_guid, policy_guid, coll_name, key_value, key_operator,
         eval_order, fixit_job, num_occurrences, 
         condition_operator, message, message_nlsid)
       VALUES
        (l_target_guids(i), l_metric_guids(i), l_coll_names(i), l_key_values(i), 0,
         l_eval_ords(i), l_fixit_jobs(i),  l_num_occurs(i),
         l_crit_opers(i), l_messages(i), l_message_nlsids(i) );

      -- Add policy assoc cfg params
      INSERT INTO mgmt_policy_assoc_cfg_params
        (object_guid, policy_guid, coll_name, key_value, key_operator,
         param_name, crit_threshold, warn_threshold, info_threshold)
       VALUES
        (l_target_guids(i), l_metric_guids(i), l_coll_names(i), l_key_values(i), 0,
         ' ', l_crit_thrs(i), l_warn_thrs(i), NULL);

    END LOOP;

    COMMIT;

    FETCH thr_cur BULK COLLECT INTO
      l_target_guids, l_metric_guids, l_coll_names, l_key_values,
      l_warn_opers, l_warn_thrs, l_crit_opers, l_crit_thrs, l_num_occurs,
      l_eval_ords, l_fixit_jobs, l_messages, l_message_nlsids
      LIMIT l_commit_frequency;

  END LOOP;

END ;
/


DROP TABLE mgmt_metric_thresholds;

PROMPT Done populating data to policy tables

SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Drop V1(obsolete) policy types and type bodies
DROP TYPE ECM_POLICY_PARAM_LIST;
DROP TYPE ECM_POLICY_PARAM;
DROP TYPE ECM_POLICY_RULE_CRITERIA;
DROP TYPE ECM_POLICY_RULE_CRITERION;
DROP TYPE ECM_POLICY_TARGET_CRITERIA;
DROP TYPE ECM_POLICY_TARGET_CRITERION;
DROP TYPE BODY ECM_POLICY_RULE;
DROP TYPE ECM_POLICY_RULE;
DROP TYPE BODY ECM_RULE;
DROP TYPE ECM_RULE;
DROP TYPE ECM_RULE_COLUMN_LIST;
DROP TYPE ECM_RULE_COLUMN;
DROP TYPE ECM_RULE_VIOLATION_VALUE_LIST;
DROP TYPE ECM_RULE_PARAM_LIST;
DROP TYPE ECM_RULE_PARAM;

--DROP V1 packages
DROP PACKAGE MGMT_ECM_POLICY;
DROP PACKAGE MGMT_ECM_RULES;

SET ECHO ON
--Drop all the V1(obsolete) policy tables

DROP TABLE MGMT_POLICY_RULE_DEF_COLUMNS;
DROP TABLE MGMT_POLICY_RULE_DEF_PARAMS;
DROP TABLE MGMT_POLICY_RULE;
DROP TABLE MGMT_POLICY_PARAMS;
DROP TABLE MGMT_POLICY_RULE_CRITERIA;
DROP TABLE MGMT_POLICY_TARGET_CRITERIA;
DROP TABLE MGMT_POLICY_VIOLATION_VALUES;
DROP TABLE MGMT_POLICY_VIOLATION_ROWS;
DROP TABLE MGMT_POLICY_VIOLATIONS;
DROP TABLE MGMT_POLICY_SNAPSHOT_CRITERIA;
DROP TABLE MGMT_POLICY_GROUP;
DROP TABLE MGMT_POLICY_ERRORS;