Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: beacon_data_upgrade.sql 04-oct-2005.14:53:15 afontana Exp $
Rem
Rem beacon_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      beacon_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    10/04/05 - add website 1.0 
Rem    afontana    10/04/05 - update for metrics for generic_service as well 
Rem    afontana    10/04/05 - finalize metadata for website targets 
Rem    chyu        07/18/05 - adding the upgrade header 
Rem    scgrover    07/15/05 - scgrover_add_dbms_job_extended_sql_trace
Rem    scgrover    07/08/05 - Created
Rem

BEGIN
  INSERT INTO MGMT_PARAMETERS
    (parameter_name, parameter_value, parameter_comment)
  VALUES
    ('EST_E2E_ROLLUP', 'OFF', 'Extended SQL trace for EMD_E2E_ROLLUP.ROLLUP() dbms jobs. ' ||
                           'Use EMD_E2E_ROLLUP.DBMSJOB_EXTENDED_SQL_TRACE_ON(boolean) to alter');

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    UPDATE MGMT_PARAMETERS SET parameter_value = 'OFF' WHERE parameter_name = 'EST_E2E_ROLLUP';
    COMMIT;
END;
/


--Try to guarantee that website 2.0 exists (for metric versioning purposes).
BEGIN

  em_service.initialize_service_type(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, FALSE, '1.0');
  em_service.initialize_service_type(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, FALSE, '2.0');

DECLARE

  PROCEDURE add_prop_def(p_service_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_property_name IN VARCHAR2)
  AS
  BEGIN
     dbms_output.put_line('adding props');
     BEGIN
        INSERT INTO mgmt_target_prop_defs(target_type, type_meta_ver,
                         property_name,
                         property_display_name, hidden_flag)
        VALUES(p_service_type, p_type_meta_ver, p_property_name, p_property_name, 1);
     EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            NULL;
     END;
  END; -- PROCEDURE add_prop_def

  PROCEDURE add_svc_prop_defs(p_service_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2)
  AS
  BEGIN
     add_prop_def(p_service_type, p_type_meta_ver, EM_SERVICE.G_SVC_HP_USAGE_METRIC_NAME);
     add_prop_def(p_service_type, p_type_meta_ver, EM_SERVICE.G_SVC_HP_USAGE_METRIC_COL );
     add_prop_def(p_service_type, p_type_meta_ver, EM_SERVICE.G_SVC_HP_USAGE_KEY );
     add_prop_def(p_service_type, p_type_meta_ver, EM_SERVICE.G_SVC_HP_PERF_METRIC_NAME);
     add_prop_def(p_service_type, p_type_meta_ver, EM_SERVICE.G_SVC_HP_PERF_METRIC_COL );
     add_prop_def(p_service_type, p_type_meta_ver, EM_SERVICE.G_SVC_HP_PERF_KEY );
  END; --PROCEDURE add_svc_prop_defs

  BEGIN
     --add property def for website target
     add_svc_prop_defs(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '1.0');
     add_svc_prop_defs(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '2.0');
     COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
  END; -- for previous BEGIN block
END; --
/
show errors;


--finalize website metrics
BEGIN
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '1.0',
                                'MGMT_TARGET_TYPE_VERSIONS');
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '1.0',
                                'MGMT_METRICS');
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '1.0',
                                'MGMT_TARGET_PROP_DEFS');

  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '2.0',
                                'MGMT_TARGET_TYPE_VERSIONS');
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '2.0',
                                'MGMT_METRICS');
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '2.0',
                                'MGMT_TARGET_PROP_DEFS');

  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '3.0',
                                'MGMT_TARGET_TYPE_VERSIONS');
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '3.0',
                              'MGMT_METRICS');
  emd_loader.end_metadata_set(MGMT_GLOBAL.G_WEBSITE_TARGET_TYPE, '3.0',
                                'MGMT_TARGET_PROP_DEFS');


  -- fix inconsistencies in the metric display label
  UPDATE MGMT_METRICS
     SET METRIC_LABEL = 'HTTP Transaction',
         METRIC_LABEL_NLSID = 'website_http'
   WHERE metric_name = 'http_response'
     AND target_type IN ('oracle_beacon', 'website', 'generic_service');

EXCEPTION 
  WHEN OTHERS THEN
   NULL;
END;
/
show errors;

DECLARE
  l_num_versions NUMBER := 0;
BEGIN

  SELECT count(*) INTO l_num_versions
    FROM mgmt_metrics
   WHERE target_type = 'oracle_beacon'
     AND type_meta_ver = '2.0'
     AND metric_name = 'http_response';

  IF (l_num_versions <> 0) THEN
    emd_loader.end_metadata_set(MGMT_GLOBAL.G_BEACON_TARGET_TYPE, '2.0',
                                'MGMT_METRICS');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
show errors;