Rem
Rem $Header: beacon_data_upgrade.sql 16-apr-2003.18:33:47 asawant Exp $
Rem
Rem beacon_data_upgrade.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      beacon_data_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This script provides the data upgrade from v400 to v401
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    asawant    04/16/03 - asawant_bug-2819310_main
Rem    asawant    04/14/03 - Creation
Rem

Rem Upgrade the http txn definitions from 4.0.0 to 4.0.1 format (encoding)

SET SERVEROUTPUT ON;

--
-- Decode all the necessary fields from a 4.0.0 txn definition.
--
DECLARE
  CURSOR txn_tbl IS
    SELECT txn_guid, step, url, auth_realm, auth_user, auth_passwd
      FROM mgmt_bcn_txn_http
      FOR UPDATE;
  CURSOR param_tbl IS
    SELECT txn_guid, step, part, postdata
      FROM mgmt_bcn_txn_http_param
      WHERE postdata IS NOT NULL
      FOR UPDATE;
  txn_rec txn_tbl%ROWTYPE;
  param_rec param_tbl%ROWTYPE;
  url_str VARCHAR2(4000);
  auth_user_str VARCHAR2(4000);
  auth_passwd_str VARCHAR2(4000);
  auth_realm_str VARCHAR2(4000);
  postdata_str VARCHAR2(4000);
  need_update BOOLEAN;

  --
  -- MGMT_BCN_UNENCODE_400_STR: converts the input string from a 4.0.0 encoded 
  -- string to an unencoded string.
  --
  FUNCTION mgmt_bcn_unencode_400_str (input IN VARCHAR2)
    RETURN VARCHAR2 IS
    return_str VARCHAR2(4000);  -- Change to the type of the column
    cur_pos    NUMBER(10);      -- Change to fit the max size of string
    lst_pos    NUMBER(10);
    token_num  NUMBER(10);
    token_str  VARCHAR(2);
    token_len  NUMBER(10);
  BEGIN
    cur_pos := INSTR(input, '%');
    IF (cur_pos = 0) OR (cur_pos IS NULL) THEN
      -- No '%' in string (no conversion necessary) or input is NULL
      RETURN(NULL);
    END IF;
    -- Found at least one '%'
  
    lst_pos := 1;
    return_str := '';
  
    LOOP
      return_str := return_str || 
                    SUBSTR(input, lst_pos, cur_pos - lst_pos);
      token_str := SUBSTR(input, cur_pos + 1, 2);
      
      BEGIN
        token_num := TO_NUMBER(token_str);
      EXCEPTION
        WHEN VALUE_ERROR THEN  -- The token_str after '%' is not a number
          token_str := NULL;  -- Force short circuit
      END;
  
      IF (token_str IS NOT NULL) THEN
        token_len := LENGTH(token_str);
        IF(token_len = 2) THEN  -- Could have been 1
          IF (token_num = 0) THEN
            token_str := '#';
            cur_pos := cur_pos + 3;
          ELSIF (token_num = 1) THEN
            token_str := '|';
            cur_pos := cur_pos + 3;
          ELSE
            token_str := '%';
            cur_pos := cur_pos + 1;
          END IF;
        ELSE
          token_str := '%';
          cur_pos := cur_pos + 1;
        END IF;
      ELSE
        token_str := '%';
        cur_pos := cur_pos + 1;
      END IF;
  
      return_str := return_str || token_str;
      lst_pos := cur_pos;
      cur_pos := INSTR(input, '%', cur_pos);
      EXIT WHEN (cur_pos = 0);
    END LOOP;
    return_str := return_str || SUBSTR(input, lst_pos);
  
    RETURN(return_str);
  END mgmt_bcn_unencode_400_str;

BEGIN

  need_update := FALSE;

  FOR txn_rec IN txn_tbl LOOP

    -- Decode URL
    url_str := mgmt_bcn_unencode_400_str(txn_rec.url);
    IF (url_str IS NULL) THEN
      url_str := txn_rec.url;
    END IF;
    IF (url_str IS NOT NULL) THEN
      need_update := TRUE;
    END IF;

    -- Decode auth user
    auth_user_str := mgmt_bcn_unencode_400_str(txn_rec.auth_user);
    IF (auth_user_str IS NULL) THEN
      auth_user_str := txn_rec.auth_user;
    END IF;
    IF (auth_user_str IS NOT NULL) THEN
      auth_user_str := encrypt(auth_user_str);
      need_update := TRUE;
    END IF;

    -- Decode auth password
    auth_passwd_str := mgmt_bcn_unencode_400_str(txn_rec.auth_passwd);
    IF (auth_passwd_str IS NULL) THEN
      auth_passwd_str := txn_rec.auth_passwd;
    END IF;
    IF (auth_passwd_str IS NOT NULL) THEN
      auth_passwd_str := encrypt(auth_passwd_str);
      need_update := TRUE;
    END IF;

    -- Decode auth realm
    auth_realm_str := mgmt_bcn_unencode_400_str(txn_rec.auth_realm);
    IF (auth_realm_str IS NULL) THEN
      auth_realm_str := txn_rec.auth_realm;
    END IF;
    IF (auth_realm_str IS NOT NULL) THEN
      auth_realm_str := encrypt(auth_realm_str);
      need_update := TRUE;
    END IF;

    -- Update if necessary
    IF (need_update) THEN
      UPDATE mgmt_bcn_txn_http
        SET url         = url_str,
            auth_user   = auth_user_str,
            auth_passwd = auth_passwd_str,
            auth_realm  = auth_realm_str
        WHERE txn_guid = txn_rec.txn_guid
          AND step     = txn_rec.step;
      need_update := FALSE;
    END IF;

  END LOOP;

  FOR param_rec IN param_tbl LOOP
    -- postdata cannot be NULL (see cursor's query)
    postdata_str := mgmt_bcn_unencode_400_str(decrypt(param_rec.postdata));
    IF (postdata_str IS NOT NULL) THEN
      UPDATE mgmt_bcn_txn_http_param
        SET postdata = encrypt(postdata_str)
        WHERE txn_guid = param_rec.txn_guid
          AND part     = param_rec.part
          AND step     = param_rec.step;
    END IF;
  END LOOP;
  COMMIT;
END;
/

SHOW ERRORS;