Rem
Rem $Header: mgwtable.sql 20-nov-2006.15:09:53 chall Exp $
Rem
Rem mgwtable.sql
Rem
Rem Copyright (c) 2001, 2006, Oracle. All rights reserved.  
Rem
Rem  NAME
Rem    mgwtable.sql - MGW table creation
Rem
Rem  DESCRIPTION
Rem    This script creates tables used by Oracle Messaging Gateway.
Rem
Rem    According to component guidelines, this script should only be run
Rem    when the MGW component is first loaded (i.e., from catmgw.sql).
Rem    It is not run for upgrades, downgrades, or patches. Any table 
Rem    modifications done using ALTER TABLE should be done by component
Rem    "u" script (for upgrades) and "e" script (for downgrades). As such,
Rem    this script should use CREATE TABLE and define the entire table as 
Rem    it is used for the current release; i.e., it need not use 
Rem    CREATE TABLE to create an initial version of the table and then
Rem    ALTER TABLE for modifications made for subsequent MGW/RDBMS releases.
Rem
Rem  NOTES
Rem    Run this script AS SYSDBA
Rem
Rem    All tables are owned by SYS.
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       11/20/06 - add RAW fields for dbms_crypto encrypted data
Rem  chall       09/27/06 - add mgw$_agent_options table
Rem  chall       08/01/06 - add flag fields to mgw_config table
Rem  chall       06/30/06 - add ajob_repeat_ivl to mgw$_config table 
Rem  chall       04/18/06 - comments field added to subscribers table 
Rem  chall       03/09/06 - propagation job support (11g feature) 
Rem  chall       12/13/05 - multiple agent feature 
Rem  chall       05/19/04 - MSMQ support 
Rem  chall       12/16/03 - remove obsolete (commented out) MSMQ code 
Rem  chall       07/14/03 - RAC shutdown/startup trigger issues (bug 2999689)
Rem  chall       11/04/02 - no ALTER TABLE done by this script
Rem  chall       08/28/02 - table modifications for 10iR1
Rem
Rem ==========================================================================

-- mgw$_config
--
-- Stores general MGW configuration data. Information associated with a
-- specific MGW agent is stored in MGW$_GATEWAY.
--
-- COLUMNS:
-- 
-- flags          : flags bitmask field
-- initdir        : base directory for initialization files
--                  if NULL, MGW external proc assumes $OH/mgw/admin
-- job_repeat_ivl : repeat interval for agent Scheduler jobs
--                  if NULL, internal default value used
--
CREATE TABLE mgw$_config
(
  flags          INTEGER DEFAULT 0 NOT NULL,
  initdir        VARCHAR2(250),
  job_repeat_ivl VARCHAR2(250)
);

grant select, update on mgw$_config to MGW_AGENT_ROLE;

-- Set the initial/default configuration.
-- Insert row only if nothing yet configured (ie, table just created).
declare
  v_count number := 0;
begin
  select count(*) into v_count from mgw$_config;
  if v_count = 0 then
    insert into mgw$_config (initdir, job_repeat_ivl) values(NULL, NULL);
    commit;
  end if;
end;
/

-- Set the initial/default configuration.
-- Insert row only if nothing yet configured (ie, table just created).
declare
  v_count number := 0;
begin
  select count(*) into v_count from mgw$_config;
  if v_count = 0 then
    insert into mgw$_config (initdir, job_repeat_ivl) values(NULL, NULL);
    commit;
  end if;
end;
/

-- mgw$_gateway
--
-- Stores MGW agent configuration and runtime information.
--
-- COLUMNS:
-- 
-- max_connections : Max number of messaging connections by gateway agent
--                   to Oracle DB (obsolete starting with 11gR1).
-- max_memory      : Max heap size (MB) used by the gateway agent.
-- agent_user      : Gateway agent user for connections to Oracle DB.
-- agent_password  : (encrypted) Password of the gateway agent user.
--                   Note: Max length of 30 for unencrypted form.
--                   Note: Column length must be (at least) the next 
--                   multiple of 8 greater than max size of unencrypted text.
--                   note: deprecated starting 11gR1, enc_agent_password used
-- agent_database  : Database (connect string) to be used by gateway agent.
--                   Typically Net8 service name for JDBC/OCI connections.
-- agent_status    : Agent status; see mgwi_const.GWSTATUS_<> values.
-- agent_job       : Job number of job queue job used to start agent.
-- gw_mode         : Gateway started/shutdown mode; see mgwi_const.GWMODE_<>
--                   values.
--                   Indicates the last admin startup/shutdown action.
-- gw_guid         : Unique identifier for this Messaging Gateway.
-- admlock_tmo     : Administration lock timeout (in seconds); if NULL, an
--                   internal default is used.
-- error_time      : Date and time of last gateway agent error.
-- error_message   : Last error message text.
--
-- --- 10gR1 Changes ---
--
-- Added:
-- max_threads      : Max messaging/worker threads used by agent.
-- flags            : Flags bitmask field; see mgwi_const.GW_FLAG_<> values.
-- job_flags        : Job flags bitmask field; see mgwi_const.AJOBF_<> values
-- agent_instance   : Database instance on which current agent job is running.
-- agent_start_time : Start time of current agent job.
-- last_instance    : Database instance of last (previous) agent job.
-- last_start_time  : Start time of last agent job.
-- last_agent_exitcode : return/exit code of agent invoke routine for last job;
--                       NULL if agent job failed due to SQL exception
--
-- --- 11gR1 Changes ---
--
-- Added:
-- job_instance_id    : Identifer used for rogue agent detection.
-- agent_name         : Agent name, DEFAULT_AGENT for default agent.
-- service            : Database service use for Scheduler job.
-- initfile           : Agent initilization file; if NULL, default is used
-- enc_agent_password : encrypted agent password
-- comments           : Optional comments.
-- mgw%_gateway_pk    : primary key constraint
-- mgw$_gateway_u1    : unique constraint
--

CREATE TABLE mgw$_gateway
(
  max_connections     NUMBER,
  max_memory          NUMBER,
  agent_user          VARCHAR2(30),
  agent_password      VARCHAR2(32),
  agent_database      VARCHAR2(128),
  agent_status        INTEGER,
  agent_job           NUMBER,
  gw_mode             INTEGER,
  gw_guid             RAW(16) NOT NULL,
  admlock_tmo         INTEGER,
  error_time          DATE,
  error_message       VARCHAR2(4000),
  max_threads         INTEGER DEFAULT 1,
  flags               INTEGER DEFAULT 0 NOT NULL,
  job_flags           INTEGER DEFAULT 0 NOT NULL,
  agent_instance      NUMBER,
  agent_start_time    TIMESTAMP,
  last_instance       NUMBER,
  last_start_time     TIMESTAMP,
  last_agent_exitcode INTEGER,
  job_instance_id     VARCHAR2(100),
  agent_name          VARCHAR2(30),
  service             VARCHAR2(64),
  initfile            VARCHAR2(250),
  enc_agent_password  RAW(2000),
  comments            VARCHAR2(500),
  CONSTRAINT mgw$_gateway_pk PRIMARY KEY (agent_name),
  CONSTRAINT mgw$_gateway_u1 UNIQUE (gw_guid)
);

grant select, update on mgw$_gateway to MGW_AGENT_ROLE;

-- Set the initial/default configuration.
-- Insert row only if nothing yet configured (ie, table just created).
declare
  v_count number := 0;
begin
  select count(*) into v_count from mgw$_gateway;
  if v_count = 0 then
    insert into mgw$_gateway
      ( max_connections, max_memory, agent_status, gw_mode, gw_guid, 
        agent_name )
      values(1, 64, 0, NULL, sys_guid(), 'DEFAULT_AGENT');
    commit;
  end if;
end;
/

-- mgw$_agent_options
--
-- Stores supplemental MGW agent options and properties.
--
-- COLUMNS:
-- 
-- agent_name     : agent name
-- type           : type/usage
--                  1 = used to set Java System property
-- name           : option name
-- value          : option value
-- enc_value      : encrypted option value
-- encrypted      : indicates whether value is encrypted
--                  0 = value not encrypted, stored in cleartext form
--                  1 = value stored in encrypted form
-- flags          : flags bitmask field
--
CREATE TABLE mgw$_agent_options
(
  agent_name  VARCHAR2(30) NOT NULL,
  type        INTEGER,
  name        VARCHAR2(4000) NOT NULL,
  value       VARCHAR2(4000),
  enc_value   RAW(2000),
  encrypted   INTEGER DEFAULT 0 NOT NULL,
  flags       INTEGER DEFAULT 0 NOT NULL,
  CONSTRAINT mgw$_agent_options_fk1 FOREIGN KEY (agent_name) 
    REFERENCES mgw$_gateway(agent_name) ON DELETE CASCADE
);

grant select, update on mgw$_agent_options to MGW_AGENT_ROLE;

-- mgw$_links
--
-- Stores basic information for the messaging system links. Messaging system
-- specific properties are stored in a table specific to the messaging system.
--
-- COLUMNS:
--
-- guid         : Unique identifier for this link.
-- link_name    : Name of the messaging system link.
-- link_type    : Type of messaging system link; see mgwi_const.<>_MSLINK 
--                values.
-- link_options : Optional properties.
-- link_comment : User-specified comment.
--
-- --- 10gR1 Changes ---
--
-- Added:
-- flags        : Flags bitmask field; see mgwi_const.LINK_FLAG_<> values.
--
-- --- 11gR1 Changes ---
--
-- Added:
-- agent_name : MGW agent name associated with the link.
-- 

CREATE TABLE mgw$_links
(
  guid          RAW(16) NOT NULL,
  link_name     VARCHAR2(30) NOT NULL,
  link_type     INTEGER NOT NULL,
  link_options  sys.mgw_properties,
  link_comment  VARCHAR2(2000),
  flags         INTEGER DEFAULT 0 NOT NULL,
  agent_name    VARCHAR2(30),
  CONSTRAINT mgw$_links_pk PRIMARY KEY (link_name),
  CONSTRAINT mgw$_links_u1 UNIQUE (guid)
);

grant select, update on mgw$_links to MGW_AGENT_ROLE;

-- mgw$_mqseries_links
--
-- Stores link information for IBM WebSphere MQ (MQSeries) messaging systems.
--
-- COLUMNS:
--
-- link_name       : Name of the messaging system link.
-- queue_manager   : Name of MQSeries queue manager.
-- hostname        : MQSeries server host. NULL if local connection (resides
--                   on same machine as gateway agent).
-- port            : Host port.
-- channel         : Channel used to connection to queue manager. Used only
--                   for client connections; when hostname/port are not NULL.
-- interface_type  : Messaging interface used for link.
-- max_connections : Maximum number of messaging connections.
-- username        : Connection user name.
-- password        : (encrypted) Connection password. 
--                   Note: Max length allowed for unencrypted value is 63.
--                   Column length must be (at least) the next multiple of 8
--                   greater than max size of unencrypted value.
--                   Note: deprecated starting 11gR1; enc_password now used
-- inbound_log_queue  : Native queue name of the propagation log queue used
--                      for queues involved in inbound propagation.
-- outbound_log_queue : Native queue name of the propagation log queue used
--                      for queues involved in outbound propagation.
--
-- --- 10gR2 Changes ---
--
-- Size of password field changed from 72 to 64. 64 is minimum size needed
-- to support an max length of 63 for the unencrypted value.
-- Explanation: Prior to 10gR2, the admin logic did not explicitely enforce
-- a max length for the unencrypted password value and the maximum size was
-- 64 (size of mgw_mqseries_properties.password attribute). However, due to
-- how mgwi_msglink was used, a user value (unencrypted) of size 0..63 would
-- have succeeded, but a user value of size 64 would have _always_ failed.
-- Consequently, the maximum size really needed for this field was 64 rather
-- than 72. For 10gR2, the admin enforces a max size of 63 for the user 
-- (unencrypted) value. There should be no migration issues since this table
-- is only created when MGW is loaded and not part of an upgrade. Also, the
-- admin never assumes a given table field size so it is not an issue from
-- an upgrade/downgrade perspective.
--
-- --- 11gR1 Changes ---
--
-- Added:
-- enc_password : encrypted password

CREATE TABLE mgw$_mqseries_links
(
  link_name           VARCHAR2(30) NOT NULL,
  queue_manager       VARCHAR2(64),
  hostname            VARCHAR2(64),
  port                INTEGER,
  channel             VARCHAR2(64),
  interface_type      INTEGER,
  max_connections     INTEGER,
  username            VARCHAR2(64),
  password            VARCHAR2(64),
  inbound_log_queue   VARCHAR2(64),
  outbound_log_queue  VARCHAR2(64),
  enc_password        RAW(2000),
  CONSTRAINT mgw$_mqseries_links_fk1 FOREIGN KEY (link_name) 
    REFERENCES mgw$_links(link_name) ON DELETE CASCADE
);

grant select, update on mgw$_mqseries_links to MGW_AGENT_ROLE;

-- mgw$_tibrv_links
--
-- Stores link information for TIB/Rendezvous messaging systems.
--
-- COLUMNS:
--
-- link_name : Name of the messaging system link.
-- service   : Name of Rendezvous service.
-- daemon    : Rendezvous daemon to use.
-- network   : Network interface used by Rendezvous.
-- cm_name   : CM correspondent name
-- cm_ledger : CM ledger file name
--

CREATE TABLE mgw$_tibrv_links
(
  link_name  VARCHAR2(30) NOT NULL,
  service    VARCHAR2(128),
  daemon     VARCHAR2(128),
  network    VARCHAR2(256),
  cm_name    VARCHAR2(256),
  cm_ledger  VARCHAR2(256),
  CONSTRAINT mgw$_tibrv_links_fk1 FOREIGN KEY (link_name) 
    REFERENCES mgw$_links(link_name) ON DELETE CASCADE
);

grant select, update on mgw$_tibrv_links to MGW_AGENT_ROLE;

-- mgw$_msmq_links
--
-- Stores link information for Microsoft MSMQ messaging systems.
--
-- COLUMNS:
--
-- link_name       : Name of the messaging system link.
-- transactional   : Transactional nature of messaging operations.
-- username        : Impersonation user name.
-- password        : (encrypted) Impersonation password.
--                   Note: Max length allowed for unencrypted value is 63.
--                   Column length must be (at least) the next multiple of 8
--                   greater than max size of unencrypted value.
--                   Note: deprecated starting 11gR1; enc_password used now
--
-- --- 11gR1 Changes ---
--
-- Added:
-- enc_password : encrypted password
--
CREATE TABLE mgw$_msmq_links
(
  link_name           VARCHAR2(30) NOT NULL,
  transactional       INTEGER,
  username            VARCHAR2(64),
  password            VARCHAR2(64),
  enc_password        RAW(2000),
  CONSTRAINT mgw$_msmq_links_fk1 FOREIGN KEY (link_name) 
    REFERENCES mgw$_links(link_name) ON DELETE CASCADE
);

grant select, update on mgw$_msmq_links to MGW_AGENT_ROLE;

-- mgw$_foreign_queues
--
-- Stores information for queues of foreign (non-Oracle) messaging systems.
--
-- COLUMNS:
--
-- guid           : Unique identifier for this queue.
-- name           : Queue name.
-- link_name      : Name of messaging system link.
-- provider_queue : Native queue name.
-- domain         : Domain type; DOMAIN_QUEUE (single-consumer) or 
--                  DOMAIN_TOPIC (multi-consumer)
-- options        : Optional properties.
-- queue_comment  : User-specified comment.
--
-- --- 10gR1 Changes ---
--
-- Added:
-- flags : Flags bitmask field; see mgwi_const.FQ_FLAG_<> values.
--
-- --- 10gR2 Changes ---
--
-- Increased size of provider_queue from 128 to 2000.
--

CREATE TABLE mgw$_foreign_queues
(
  guid              RAW(16) NOT NULL,
  name              VARCHAR2(30) NOT NULL,
  link_name         VARCHAR2(30) NOT NULL,
  provider_queue    VARCHAR2(2000),
  domain            INTEGER,
  options           sys.mgw_properties,
  queue_comment     VARCHAR2(2000),
  flags             INTEGER DEFAULT 0 NOT NULL,
  CONSTRAINT mgw$_foreign_queues_pk PRIMARY KEY (name, link_name),
  CONSTRAINT mgw$_foreign_queues_u1 UNIQUE (guid)
);

grant select, update on mgw$_foreign_queues to MGW_AGENT_ROLE;

-- mgw$_subscribers
--
-- Stores information for the propagation subscribers.
--
-- COLUMNS:
--
-- guid            : Unique identifier for this subscriber.
-- subscriber_id   : User-defined subscriber identifier.
-- prop_type       : Propagation type; see dbms_mgwadm.<>_PROPAGATION values.
-- queue_name      : Propagation source queue.
-- destination     : Propagation destination.
-- rule            : Subscriber rule; NULL if none. Syntax/meaning is dependent
--                   upon the source queue messaging system.
-- transformation  : Message transformation to use. NULL if none.
-- exception_queue : Exception queue. NULL if none.
-- config_state    : Configuration state; see mgwi_const.SUBCS_<> values.
-- num_fails       : Number of failures.
-- err_time        : Date and time of last error.
-- err_message     : Last error message text.
-- curr_prop_msgs  : Number of messages propagated by this subscriber.
--                   Value is reset/cleared each time the MGW agent starts.
-- curr_exq_msgs   : Number of messages moved to subscriber's exception queue.
--                   Value is reset/cleared each time the MGW agent starts.
--
-- --- 10gR1 Changes ---
--
-- Added:
-- flags   : Flags bitmask field; see mgwi_const.SUB_FLAG_<> values.
-- options : Optional properties.
--
-- --- 11gR1 Changes ---
--
-- Added:
-- link_name : Name of messaging link associated with this subscriber.
-- comments  : Optional comments.
--

CREATE TABLE mgw$_subscribers
(
  guid               RAW(16) NOT NULL,
  subscriber_id      VARCHAR2(30) NOT NULL,
  prop_type          INTEGER NOT NULL,
  queue_name         VARCHAR2(61) NOT NULL,
  destination        VARCHAR2(61) NOT NULL,
  rule               VARCHAR2(4000),
  transformation     VARCHAR2(61),
  exception_queue    VARCHAR2(61),
  config_state       INTEGER,
  num_fails          INTEGER,
  err_time           DATE,
  err_message        VARCHAR2(4000),
  curr_prop_msgs     NUMBER,
  curr_exq_msgs      NUMBER,
  flags              INTEGER DEFAULT 0 NOT NULL,
  options            sys.mgw_properties,
  link_name          VARCHAR2(30),
  comments           VARCHAR2(2000),
  CONSTRAINT mgw$_subscribers_pk PRIMARY KEY (subscriber_id),
  CONSTRAINT mgw$_subscribers_u1 UNIQUE (guid),
  CONSTRAINT mgw$_subscribers_u2 UNIQUE (prop_type, queue_name, destination)
);

grant select, update on mgw$_subscribers to MGW_AGENT_ROLE;
grant delete on mgw$_subscribers to MGW_AGENT_ROLE;

-- mgw$_schedules
--
-- Stores information for the propagation schedules.
--
-- COLUMNS:
--
-- guid        : Unique identifier for this schedule.
-- schedule_id : User-defined propagation schedule name.
-- prop_type   : Propagation type; see dbms_mgwadm.<>_PROPAGATION values.
-- source      : Propagation source.
-- destination : Propagation destination.
-- start_time  : Start date and time for the propagation schedule.
-- duration    : Duration of propagation window (seconds).
-- next_time   : Date function used to compute next propagation window.
-- latency     : Propagation window latency (seconds).
-- state       : Schedule state; mgwi_const.SCHED_DISABLED or SCHED_DISABLED.
--
-- --- 10gR1 Changes ---
--
-- Added:
-- flags : Flags bitmask field; see mgwi_const.SCH_FLAG_<> values.
--
-- --- 11gR1 Changes ---
--
-- Added:
-- link_name : Name of messaging link associated with this schedule.
--

CREATE TABLE mgw$_schedules
(
  guid         RAW(16) NOT NULL,
  schedule_id  VARCHAR2(30) NOT NULL,
  prop_type    INTEGER NOT NULL,
  source       VARCHAR2(61) NOT NULL,
  destination  VARCHAR2(61),
  start_time   DATE,
  duration     NUMBER,
  next_time    VARCHAR2(200),
  latency      NUMBER,
  state        INTEGER,
  flags        INTEGER DEFAULT 0 NOT NULL, 
  link_name    VARCHAR2(30),
  CONSTRAINT mgw$_schedules_pk PRIMARY KEY (schedule_id),
  CONSTRAINT mgw$_schedules_u1 UNIQUE (guid),
  CONSTRAINT mgw$_schedules_u2 UNIQUE (prop_type, source, destination)
);

grant select, update on mgw$_schedules to MGW_AGENT_ROLE;


Rem === End file: mgwtable.sql ===============================================