Rem
Rem $Header: mgw/src/plsql/admin/mgwu102.sql /mgw_main/5 2008/08/24 09:37:36 chall Exp $
Rem
Rem mgwu102.sql
Rem
Rem Copyright (c) 2005, 2008, Oracle. All rights reserved.
Rem
Rem  NAME
Rem    mgwu102.sql - MGW component upgrade script from 10.2.0 (to 11.0.0)
Rem
Rem  DESCRIPTION
Rem    This script must be run AS SYSDBA.
Rem
Rem    This script is intended to be run during component upgrade process;
Rem    typically from mgwdbmig.sql. It should only be called when upgrading
Rem    a 10.2.0 database in which the MGW component is currently loaded.
Rem
Rem    Messaging Gateway agent must be shut down before running this script.
Rem
Rem  NOTES
Rem    TOID Note: Starting with RDBMS 10R1, a fixed OID is assigned for 
Rem    every new MGW type, rather than having one automatically assigned by
Rem    the database. Note however, for any 9.x to NNN upgrade, we will wind
Rem    up with a mixture, fixed ids for any new types but old generated ids 
Rem    for existing types. It is something that we (and the user) has to deal
Rem    with. OJMS and likely other components face the same issue if they
Rem    did not use the OID clause from the start.
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       08/24/08 - 11.2 upgrade/downgrade support
Rem  chall       11/20/06 - switch to dbms_crypto and RAW fields
Rem  chall       08/22/06 - fix ORA-02260; DROP CONSTRAINT before ADD 
Rem                         CONSTRAINT to avoid sql error if already exists
Rem  chall       04/18/06 - comments field added to subscribers table 
Rem  chall       12/13/05 - Upgrade script from 10.2.0 
Rem  chall       12/13/05 - Created
Rem
Rem ==========================================================================

Rem ========================================================================
Rem New Admin Types
Rem ========================================================================


Rem ========================================================================
Rem Internal types modified.
Rem
rem CAUTION: Once a type has evolved by adding a new attribute, widening an
rem attribute, and so on, a CREATE OR REPLACE TYPE or ALTER TYPE REPLACE
rem _cannot_ be used to replace the type. An attempt to do so fails with:
rem   ORA-22308: operation not allowed on an evolved type
rem The error is not displayed if database is opened as STARTUP MIGRATE and
rem it _appears_ the type is successfully replaced, but in fact, it remains
rem unchanged. Just adding/dropping type methods does not evolve the type.
rem
rem NOTE: There are four MGW internal types, mgwi_msglink, mgwi_foreignqueue, 
rem mgw_subscriber, and mgwi_schedule, that are used as in-memory objects.
rem For 9.2 to 10.1 migration, CREATE OR REPLACE is/was used for all four 
rem types during upgrade and downgrade. For 10.1 to 10.2 migration, ALTER TYPE
rem is used for two types, mgwi_msglink and mgwi_foreignqueue, to modify the
rem types rather than replacing the entire type. To workaround the 
rem evolve/replace issue, the mgwu920 and mgwe920 scripts used for 10.2 
rem release must be modified to DROP TYPE before the CREATE OR REPLACE TYPE,
rem and privileges on the types re-granted.
rem
rem From 10.2 onward, MGW should _always_ use ALTER TYPE for any type 
rem modifications and never replace the entire type.
Rem ========================================================================

-- mgwi_msglink
--
alter type sys.mgwi_msglink
  ADD attribute m_agent VARCHAR2(30)
cascade;

Rem ========================================================================
Rem Table modifications
Rem
Rem Starting with 10.2.0, mgwdbmig.sql calls mgwtable.sql so we no longer
Rem need to create new tables in mgwuNNN scripts. Just focus on changes 
Rem to existing tables.
Rem
Rem Table modifications must be able to handle upgrade->downgrade->upgrade
Rem scenarios. For example, add new fields and constraints as seperate DML
Rem operations since downgrade script usually leaves the new fields but 
Rem drops contraints. The second upgrade must be able to re-add constraints 
Rem after making any necessary changes. For example, all rows must have a
Rem non-NULL value before a primary key contsraint can be added otherwise
Rem ADD CONSTRAINT fails with ORA-01449.
Rem
Rem Need to DROP CONSTRAINT before ADD CONSTRAINT to avoid ORA-02260 being
Rem raised if the constraint already exists so upgrade lrgs run cleanly.
Rem MIGRATE mode masks error raised if try to drop a constraint that does
Rem not exist, but does not mask error raised if try to add constraint that
Rem already exists. Issue seen if upgrade script called multiple times.
Rem
Rem ========================================================================

-- mgw$_gateway
--
ALTER TABLE mgw$_gateway
ADD
(
  job_instance_id    varchar2(100),
  agent_name         varchar2(30),
  service            varchar2(64),
  initfile           varchar2(250),
  enc_agent_password raw(2000),
  comments           varchar2(500)
);

-- agent_name must be non-NULL before primary key constraint can be added
UPDATE mgw$_gateway set agent_name='DEFAULT_AGENT' WHERE agent_name IS NULL;
COMMIT;

-- drop constraint before add constraint to avoid sql error if constraint
-- already exists; for example, if this script called multiple times
ALTER TABLE mgw$_gateway DROP CONSTRAINT mgw$_gateway_pk CASCADE;
ALTER TABLE mgw$_gateway
  ADD CONSTRAINT mgw$_gateway_pk PRIMARY KEY (agent_name);

ALTER TABLE mgw$_gateway DROP CONSTRAINT mgw$_gateway_u1 CASCADE;
ALTER TABLE mgw$_gateway
  ADD CONSTRAINT mgw$_gateway_u1 UNIQUE (gw_guid);

-- mgw$_links
--
ALTER TABLE mgw$_links
ADD
(
  agent_name varchar2(30)
);

-- mgw$_msmq_links
--
ALTER TABLE mgw$_msmq_links
ADD
(
  enc_password raw(2000)
);

-- mgw$_mqseries_links
--
ALTER TABLE mgw$_mqseries_links
ADD
(
  enc_password raw(2000)
);

-- mgw$_subscribers
--
ALTER TABLE mgw$_subscribers
ADD
(
  link_name varchar2(30),
  comments  varchar2(2000)
);

-- mgw$_schedules
--
ALTER TABLE mgw$_schedules
ADD
(
  link_name varchar2(30)
);

Rem ========================================================================
Rem Other Tasks
Rem
Rem 1) Initialize agent_name field for mgw$_links table.
Rem 2) Initialize link_name field for mgw$_subscribers table.
Rem 3) Initialize link_name field for mgw$_schedules table.
Rem 4) Change encrypted data to use new encryption package.
Rem
Rem ========================================================================

-- Determine MGW link referenced by a propagation triplet.
--
create or replace function mgwmig_link_refby (
  p_prop_type   in pls_integer,
  p_source      in varchar2,
  p_destination in varchar2 )
return varchar2
is
  v_link varchar2(30) := null;

  -- parses string for the link portion
  function i_getlink( str in varchar2 )
  return varchar2
  is
    v_atpos pls_integer;
  begin
    v_atpos := instrb(str, '@');
    if v_atpos > 0 then 
      return substrb(str, v_atPos+1);
    else --%return ' ';
      return null;
    end if;
  end i_getlink;

begin

  if p_prop_type = 1 then       -- outbound propagation
    v_link := i_getlink(p_destination);
  elsif p_prop_type = 2 then    -- inbound propagation
    v_link := i_getlink(p_source);
  end if;

  return v_link;

end mgwmig_link_refby;
/

-- Initialize agent_name value for links.
-- Any existing links are associated with default agent.
--
update sys.mgw$_links 
  set agent_name = 'DEFAULT_AGENT' 
  where agent_name is null;
commit;

-- Initialize link_name for subscribers.
--
update sys.mgw$_subscribers 
  set link_name = mgwmig_link_refby(prop_type, queue_name, destination)
  where link_name is null;
commit;

-- Initialize link_name for schedules.
--
update sys.mgw$_schedules
  set link_name = mgwmig_link_refby(prop_type, source, destination)
  where link_name is null;
commit;

drop function mgwmig_link_refby;

rem ========================================================================
rem Change encrypted data to use new encryption package.
rem
rem mgwicry.plb, mgwicryb.plb specify a MGW encryption support package.
rem It is large of 11g but has minimal dependencies and can be loaded during
rem an upgrade as long as the underlaying MGW tables have 11g format.
rem
rem ========================================================================

-- load encryption support package
@@mgwicry.plb
@@mgwicryb.plb

--%exec dbms_output.put_line('------ mgw$_gateway before -----')
--%select agent_name, agent_password, enc_agent_password from sys.mgw$_gateway;

--%exec dbms_output.put_line('------ mgw$_mqseries_links before -----')
--%select link_name, password, enc_password from sys.mgw$_mqseries_links;

-- Modify any encrypted data to use new package.
begin

  -- agent password
  update sys.mgw$_gateway 
    set agent_password = NULL,
        enc_agent_password = 
            sys.mgwi_crypto.migenc_10g_to_11g(agent_password)
    where agent_password IS NOT NULL;

  -- MQSeries link password.
  update sys.mgw$_mqseries_links
    set password = NULL,
        enc_password = sys.mgwi_crypto.migenc_10g_to_11g(password)
    where password IS NOT NULL;

  -- skip sys.mgw$_msmq_links since not implemented for 10g and thus no data

  commit;

exception
   when others then
     rollback;
     raise;
end;
/

--%exec dbms_output.put_line('------ mgw$_gateway after -----')
--%select agent_name, agent_password, enc_agent_password from sys.mgw$_gateway;

--%exec dbms_output.put_line('------ mgw$_mqseries_links after -----')
--%select link_name, password, enc_password from sys.mgw$_mqseries_links;

-- package will be re-loaded later in upgrade
drop package sys.mgwi_crypto;

Rem ========================================================================
Rem Chain to component's next "u" script
Rem ========================================================================

Rem Perform 11.1 to 11.2 upgrade actions
@@mgwu111

Rem === End file: mgwu102.sql ================================================