-- Create sql in-place upgrade patch for some up.xml
-- property changes. The sql will be shown (as much as 
-- possible) in-line with associated segment of up.xml.
-- The xml will be set off from the sql by commenting each
-- line with "-- up.xml ->".
--
-- Bug #'s/Properties added
--
--  Bug 8471792 -   
-- 

SET SERVEROUTPUT ON
SET LINESIZE 500
-- ##########################################################################
DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util)
-- ##########################################################################

  SCRIPTID CONSTANT VARCHAR2(40) := 'bug 8471792'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  COLON    CONSTANT VARCHAR2(01) := CHR(58); -- :
  TRACEON  CONSTANT BOOLEAN      := TRUE; -- Provide function level trace output
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled

  
-- **************************************************************************
-- **************************************************************************
--                      Useful functions/procedures
-- **************************************************************************
-- **************************************************************************

-- ==========================================================================
-- put message to output (uses DBMS_OUTPUT)
  PROCEDURE put(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF NOT OUTINITED THEN
      OUTINITED := TRUE;
      DBMS_OUTPUT.ENABLE(200000);
      put('DBMS_OUTPUT Enabled.');
    END IF;
    
    IF (LENGTH(msg) + LENGTH(SCRIPTID) + 2) > 255 THEN
      -- 10.1 database can't handle messages with length > 255
      --DBMS_OUTPUT.put_line('Message length > 255');
      --DBMS_OUTPUT.put_line(SUBSTR(msg, 0, 255));
      -- Break the string up into lines or 255-byte chunks
      DECLARE
        l_msg_line VARCHAR2(255);
        l_eol_index NUMBER;
        l_curr_index NUMBER;
        l_msg_len NUMBER := LENGTH(msg);
        l_loop_index NUMBER := 0;
      BEGIN
        DBMS_OUTPUT.PUT_LINE(SCRIPTID || ':');
        l_eol_index := 1;
        l_curr_index := 1;
        l_loop_index := 0;
        WHILE (l_curr_index < l_msg_len AND l_loop_index < 10) LOOP
          l_eol_index := INSTR(msg, EOL, l_curr_index);
          --DBMS_OUTPUT.put_line('l_eol_index = ' || TO_CHAR(l_eol_index));
          IF (l_eol_index <= 0) OR (l_eol_index - l_curr_index) > 255 THEN
            l_msg_line := SUBSTR(msg, l_curr_index, 255);
            l_curr_index := l_curr_index + 255;
          ELSE 
            l_msg_line := SUBSTR(msg, l_curr_index, l_eol_index - l_curr_index);
            l_curr_index := l_eol_index + 1;
          END IF;
          DBMS_OUTPUT.PUT_LINE(l_msg_line);        
          l_loop_index := l_loop_index + 1;
        END LOOP;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
    ELSE 
      DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg);
    END IF;  

  END;

-- ==========================================================================
-- put message to output controlled by TRACEON variable (uses put)
  PROCEDURE putTrace(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF TRACEON THEN
      put(msg);
    END IF;
  END;


-- ##########################################################################
-- ##########################################################################
BEGIN --    Outer block - utility functions/variables now defined
-- ##########################################################################
-- ##########################################################################  

  -- =======================================================
  DECLARE -- Inner block for utility function/variable usage
          -- aka 'user' declarations
  -- =======================================================
    result_count NUMBER;
	  class_def_id NUMBER;
	  classname_lookup_id NUMBER;
	  agent_id NUMBER;
	
  -- =======================================================
  BEGIN -- start handling data types defined in up.xml
  -- =======================================================
      putTrace('Check if J2EE Runtime defined' || EOL);
      select count(*) into result_count from CMPPrimitiveModelAttribute_r where name = 'J2EERUNTIME' AND value = 'CMPAgentLocation';
      if result_count > 0
      then
        select count(*) into result_count from CMPClassDefinition_r where name = 'oracle.wh.repos.impl.environment.CMPLocation';
        if result_count = 1
        then
            select elementid into class_def_id from CMPClassDefinition_r where name = 'oracle.wh.repos.impl.environment.CMPLocation';
            select count(*) into result_count from CMPPrimitiveModelAttribute_r where modelattributeowner=class_def_id and name='CLASSNAME_LOOKUP';
            if result_count=1
            then
                select elementid into classname_lookup_id from CMPPrimitiveModelAttribute_r where modelattributeowner=class_def_id and name='CLASSNAME_LOOKUP';
                select count(*) into result_count from CMPPrimitiveModelAttribute_r where modelattributeowner=classname_lookup_id AND VALUE='CMPAgentLocation';
                if result_count=1
                then
                    select elementid into agent_id from CMPPrimitiveModelAttribute_r where modelattributeowner=classname_lookup_id AND VALUE='CMPAgentLocation';
                    update CMPPrimitiveModelAttribute_r set LOGICALNAME='CONTROLCENTERAGENT',NAME='CONTROLCENTERAGENT' where elementid=agent_id;
                    update CMPPrimitiveModelAttribute_r set value='Control Center Agent' where value='J2EE Runtime';
                    update CMPLocation_r set loctype='Control Center Agent' where loctype='J2EE Runtime';
                    update CMPLocation_r set type='Control Center Agent' where type='J2EE Runtime';
                    update WB_RT_DEF_STORE_TYPES set store_type_name='Control Center Agent',display_name='Control Center Agent' where store_type_name='J2EE Runtime';
                    update WB_RT_PLATFORM_PROPERTIES set property_path='class.Control Center Agent.0.JRTDeployment' where property_path='class.J2EE Runtime.0.JRTDeployment';
                    update WB_RT_PLATFORM_PROPERTIES set property_path='property.Control Center Agent.0.JRTDeployment.timeout_seconds' where property_path='property.J2EE Runtime.0.JRTDeployment.timeout_seconds';
                    putTrace('Replace J2EE Runtime with Control Center Agent' || EOL);
                end if;
            end if;
        end if;
      else
        putTrace('No J2EE Runtime defined' || EOL);
      end if;
      
      
  
                               
  -- =======================================================
  END; -- Inner block for utility function/variable usage
  -- =======================================================    
  
-- ##########################################################################
END; -- Outer block for utility functions/variables
-- ##########################################################################
/