Rem
Rem $Header: webapp_upgrade_test.sql 24-dec-2004.01:09:35 rmarripa Exp $
Rem
Rem webapp_upgrade_test.sql
Rem
Rem Copyright (c) 2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      webapp_upgrade_test.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    rmarripa    12/24/04 - rmarripa_webapp_upgrade_changes
Rem    rmarripa    12/23/04 - Created
Rem



DECLARE
  v_properties MGMT_TARGET_PROPERTY_LIST;
  v_host VARCHAR2(256);
  v_website_guid RAW(16);
  v_beacon_guid RAW(16);
  v_beacon VARCHAR(256);
  v_website VARCHAR2(256);
  v_key1 RAW(16);
  v_metric RAW(16);
  v_meta_ver VARCHAR2(8);
BEGIN

  v_beacon := 'ASLM_10GR2_TEST_UPGRADE_BEACON';
  v_website := 'ASLM_10GR2_TEST_UPGRADE_WEBSITE';


  SELECT TARGET_NAME
    INTO v_host
    FROM MGMT_TARGETS
   WHERE TARGET_TYPE = 'host'
     AND rownum = 1;

  mgmt_target.add_target(p_target_name => v_beacon,
                         p_target_type => 'oracle_beacon',
                         p_host_name => v_host,
                         p_properties => MGMT_TARGET_PROPERTY_LIST(),
                         p_groups =>  NULL,
                         p_type_meta_ver => '3.0');

  SELECT TARGET_GUID
    INTO v_beacon_guid
    FROM MGMT_TARGETS
   WHERE TARGET_NAME = v_beacon;

  v_properties := MGMT_TARGET_PROPERTY_LIST(
                   MGMT_TARGET_PROPERTY(webapp_upgrade_job.UPGRADE_ASSOC_BEACON,
                                        'INSTANCE',
                                        RAWTOHEX(v_beacon_guid)));


  mgmt_target.add_target(p_target_name => v_website,
                         p_target_type => 'website',
                         p_host_name => v_host,
                         p_properties => v_properties,
                         p_groups =>  NULL,
                         p_type_meta_ver => '3.0',
                         p_repository_only_target => 1);

  SELECT TARGET_GUID, TYPE_META_VER
    INTO v_website_guid, v_meta_ver
    FROM MGMT_TARGETS
   WHERE TARGET_NAME = v_website;

  SELECT METRIC_GUID
    INTO v_metric
    FROM MGMT_METRICS
   WHERE TARGET_TYPE = 'website'
     AND TYPE_META_VER = v_meta_ver
     AND METRIC_NAME = 'http_response'
     AND METRIC_COLUMN = 'status';

  v_key1 := mgmt_global.get_composite_key_guid(
                 SMP_EMD_STRING_ARRAY('TXN1', '-'));

  INSERT INTO MGMT_METRICS_COMPOSITE_KEYS
             (COMPOSITE_KEY,
              TARGET_GUID,
              KEY_PART1_VALUE,
              KEY_PART2_VALUE,
              KEY_PART3_VALUE,
              KEY_PART4_VALUE,
              KEY_PART5_VALUE)
  VALUES (v_key1,
          v_website_guid,
          'TXN1',
          '-',
          NULL,
          NULL,
          NULL);

  INSERT INTO MGMT_METRICS_RAW
   (TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP, VALUE)
  VALUES (v_website_guid, v_metric, v_key1, SYSDATE, 1);

  INSERT INTO MGMT_METRICS_1DAY
   (TARGET_GUID, METRIC_GUID, KEY_VALUE, ROLLUP_TIMESTAMP, 
    SAMPLE_COUNT, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV)
  VALUES(v_website_guid, v_metric, v_key1, SYSDATE,
         1, 1, 1, 1, 0);

  INSERT INTO MGMT_METRICS_1HOUR
   (TARGET_GUID, METRIC_GUID, KEY_VALUE, ROLLUP_TIMESTAMP, 
    SAMPLE_COUNT, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV)
  VALUES(v_website_guid, v_metric, v_key1, SYSDATE,
         1, 1, 1, 1, 0);




END;
/

SELECT TARGET_NAME, TARGET_TYPE
FROM MGMT_TARGETS
WHERE TARGET_NAME IN 
('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE');

SELECT t.TARGET_NAME TARGET, ck.KEY_PART1_VALUE TXN, ck.KEY_PART2_VALUE BCN, r.VALUE VALUE, 'RAW' TAB
  FROM MGMT_METRICS_COMPOSITE_KEYS ck,
       MGMT_TARGETS t,
       MGMT_METRICS_RAW r
 WHERE ck.TARGET_GUID = t.TARGET_GUID
   AND ck.TARGET_GUID = r.TARGET_GUID
   AND ck.COMPOSITE_KEY = r.KEY_VALUE
   AND t.TARGET_NAME IN 
   ('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE')
  UNION
SELECT t.TARGET_NAME TARGET, ck.KEY_PART1_VALUE TXN, ck.KEY_PART2_VALUE BCN, r.SAMPLE_COUNT VALUE, '1DAY' TAB
  FROM MGMT_METRICS_COMPOSITE_KEYS ck,
       MGMT_TARGETS t,
       MGMT_METRICS_1DAY r
 WHERE ck.TARGET_GUID = t.TARGET_GUID
   AND ck.TARGET_GUID = r.TARGET_GUID
   AND ck.COMPOSITE_KEY = r.KEY_VALUE
   AND t.TARGET_NAME IN 
   ('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE')
  UNION
SELECT t.TARGET_NAME TARGET, ck.KEY_PART1_VALUE TXN, ck.KEY_PART2_VALUE BCN, r.SAMPLE_COUNT VALUE, '1HOUR' TAB
  FROM MGMT_METRICS_COMPOSITE_KEYS ck,
       MGMT_TARGETS t,
       MGMT_METRICS_1HOUR r
 WHERE ck.TARGET_GUID = t.TARGET_GUID
   AND ck.TARGET_GUID = r.TARGET_GUID
   AND ck.COMPOSITE_KEY = r.KEY_VALUE
   AND t.TARGET_NAME IN 
   ('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE');

exec webapp_upgrade_job.MIGRATE_METRIC_HISTORY;

SELECT t.TARGET_NAME TARGET, ck.KEY_PART1_VALUE TXN, ck.KEY_PART2_VALUE BCN, r.VALUE VALUE, 'RAW' TAB
  FROM MGMT_METRICS_COMPOSITE_KEYS ck,
       MGMT_TARGETS t,
       MGMT_METRICS_RAW r
 WHERE ck.TARGET_GUID = t.TARGET_GUID
   AND ck.TARGET_GUID = r.TARGET_GUID
   AND ck.COMPOSITE_KEY = r.KEY_VALUE
   AND t.TARGET_NAME IN 
   ('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE')
  UNION
SELECT t.TARGET_NAME TARGET, ck.KEY_PART1_VALUE TXN, ck.KEY_PART2_VALUE BCN, r.SAMPLE_COUNT VALUE, '1DAY' TAB
  FROM MGMT_METRICS_COMPOSITE_KEYS ck,
       MGMT_TARGETS t,
       MGMT_METRICS_1DAY r
 WHERE ck.TARGET_GUID = t.TARGET_GUID
   AND ck.TARGET_GUID = r.TARGET_GUID
   AND ck.COMPOSITE_KEY = r.KEY_VALUE
   AND t.TARGET_NAME IN 
   ('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE')
  UNION
SELECT t.TARGET_NAME TARGET, ck.KEY_PART1_VALUE TXN, ck.KEY_PART2_VALUE BCN, r.SAMPLE_COUNT VALUE, '1HOUR' TAB
  FROM MGMT_METRICS_COMPOSITE_KEYS ck,
       MGMT_TARGETS t,
       MGMT_METRICS_1HOUR r
 WHERE ck.TARGET_GUID = t.TARGET_GUID
   AND ck.TARGET_GUID = r.TARGET_GUID
   AND ck.COMPOSITE_KEY = r.KEY_VALUE
   AND t.TARGET_NAME IN 
   ('ASLM_10GR2_TEST_UPGRADE_BEACON', 'ASLM_10GR2_TEST_UPGRADE_WEBSITE');


DECLARE
BEGIN

  mgmt_admin.delete_target_sync(target_name_in => 'ASLM_10GR2_TEST_UPGRADE_BEACON',
                           	target_type_in => 'oracle_beacon');

  mgmt_admin.delete_target_sync(target_name_in => 'ASLM_10GR2_TEST_UPGRADE_WEBSITE',
                           	target_type_in => 'website');  

END;
/