Rem drv: <migrate type="schema_upgrade" version="10.2.0.3" />
Rem
Rem $Header: bam_tables.sql 20-nov-2006.17:14:22 skkunise Exp $
Rem
Rem bam_tables.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      bam_tables.sql - EM-BAM integration tables
Rem
Rem    DESCRIPTION
Rem      Tables needed for EM-BAM integration
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    skkunise    11/20/06 - Add last sent status value
Rem    skkunise    03/30/06 - Creating indexes 
Rem    skkunise    11/28/05 - Created
Rem

--
-- Table to store Data Hub information
--
-- Columns:
--
--   HUB_GUID [PK]  : Unique GUID to identify Data Hub
--   HUB_NAME       : Name of the Data Hub
--   JNDI_PROVIDER_URL : Jndi provider URL of the Data Hub
--   USERNAME          : Username for the JNDI 
--   PASSWORD          : Password for the JNDI
--
CREATE TABLE MGMT_BAM_DATA_HUBS (
  HUB_GUID RAW (16) DEFAULT SYS_GUID() NOT NULL,
  HUB_NAME  VARCHAR2(64) NOT NULL,
  JNDI_PROVIDER_URL VARCHAR2(200) NOT NULL,
  USERNAME VARCHAR2(64)  NOT NULL,
  PASSWORD VARCHAR2(64)  NOT NULL
) MONITORING;


--
-- Table to store Outbound Data Session information
--
-- Columns:
--
--   SESSION_GUID [PK]  : Guid to uniquely identify the Data Session
--   SESSION_NAME	: Name of the Data Session
--   USERS              : List of remote Users who can access this data
--   HUB_GUID           : Data hub to which the session data is to be sent
--   MESSAFE_FORMAT     : Indicates the message format
--                        0 (default) Normalized
--                        1 Non-normalized
--
CREATE TABLE MGMT_BAM_DATA_OSESSIONS (
  SESSION_GUID RAW (16) DEFAULT SYS_GUID() NOT NULL,
  SESSION_NAME VARCHAR2(64) NOT NULL,
  USERS VARCHAR2 (200) NULL,
  HUB_GUID RAW (16) NOT NULL,
  MESSAGE_FORMAT NUMBER DEFAULT 0
) MONITORING;

--
-- Table to store the last sent timestamps for outbound sessions
--
--  SESSION_GUID        : GUID of the Outbound Data Session
--  DATA_TYPE      	: Indicates the type of data
--      	          0 (default) Metrics
-- 			  1 Target Status
-- 			  2 Alerts
--  LAST_SENT_TIMESTAMP : Last sent timestamp
--  NUMBER_SENT 	: Number of data sent for each type
--  STATUS              : Status of the Outbound Session
--
CREATE TABLE MGMT_BAM_OSESSION_DIAG (
  SESSION_GUID RAW (16) NOT NULL,
  DATA_TYPE NUMBER DEFAULT 0,
  LAST_SENT_TIMESTAMP DATE,
  NUMBER_SENT NUMBER DEFAULT 0,
  STATUS NUMBER DEFAULT 0
) MONITORING;

--
-- This table stores the metrics for which alerts need to be
-- sent for outbound data sessions
-- 
-- Columns:
--
--   SESSION_GUID : GUID of the Data Session
--   TARGET_GUID  : GUID of the target instance
--   METRIC_GUID  : GUID for the metric 
--   KEY_VALUE    : Key value for table columns
--
-- [PK is a combination of all]
--
CREATE TABLE MGMT_BAM_OSESSION_ALERTS (
  SESSION_GUID RAW (16) NOT NULL,
  TARGET_GUID RAW(16) NOT NULL,
  METRIC_GUID RAW (16) NOT NULL,
  KEY_VALUE VARCHAR2 (256) DEFAULT '',
  CONSTRAINT "MGMT_BAM_OSESSION_ALERTS_PK"
    PRIMARY KEY (SESSION_GUID,
	       TARGET_GUID,
	       METRIC_GUID,
               KEY_VALUE)
) 
ORGANIZATION INDEX
MONITORING;


--
-- This table stores the metrics for which alerts have to
-- be sent for outbound data sessions
-- 
-- Columns:
--
--   SESSION_GUID : GUID of the Data Session
--   TARGET_GUID  : GUID of the target instance
--   METRIC_GUID  : GUID for the metric 
--   KEY_VALUE    : Key value for table columns
--
-- [PK is a combination of all]
--
CREATE TABLE MGMT_BAM_OSESSION_METRICS (
  SESSION_GUID RAW (16) NOT NULL,
  TARGET_GUID RAW(16) NOT NULL,
  METRIC_GUID RAW (16) NOT NULL,
  KEY_VALUE VARCHAR2 (256) DEFAULT '',
  CONSTRAINT "MGMT_BAM_OSESSION_METRICS_PK"
  PRIMARY KEY (SESSION_GUID,
	       TARGET_GUID,
	       METRIC_GUID,
               KEY_VALUE)
)
ORGANIZATION INDEX
MONITORING;


--
-- This table stores the targets for which status has
-- to be sent per Outbound Data Session
--
-- Columns
--    SESSION_GUID      : GUID of the data session
--    TARGET_GUID       : GUID of the target instance
--    LAST_SENT_STATUS  : NUMBER, last sent status value
-- 
-- [PK is a combination of both]
--
CREATE TABLE MGMT_BAM_OSESSION_STATUS (
  SESSION_GUID RAW (16) NOT NULL,
  TARGET_GUID RAW(16) NOT NULL,
  LAST_SENT_STATUS  NUMBER DEFAULT -99,
  CONSTRAINT "MGMT_BAM_OSESSION_STATUS_PK"
    PRIMARY KEY (SESSION_GUID,
	         TARGET_GUID)
)
ORGANIZATION INDEX
MONITORING;


--
-- Table to store Inbound Data Session information
--
-- Columns:
--
--   SESSION_GUID [PK]  : Guid to uniquely identify the Data Session
--   SESSION_NAME	: Name of the Data Session
--   HUB_GUID           : Data hub to which the session data is to be sent
--
CREATE TABLE MGMT_BAM_DATA_ISESSIONS (
  SESSION_GUID RAW (16) DEFAULT SYS_GUID() NOT NULL,
  SESSION_NAME VARCHAR2(200 byte) NOT NULL,
  HUB_GUID RAW (16) NOT NULL
) MONITORING;

--
-- Table to store Inbound Data Session Source information
--
-- Columns:
--
--   SESSION_GUID    [PK]         : Guid of the Data Session
--   SOURCE_NAME [PK]         	  : Name of the Data Object
--   DS_NAMESPACE          	  : Namespace of the Data Object
--   JMS_TOPIC_CONNECTION_FACTORY : Jms Topic Connection Factory  
--   JMS_TOPIC_CONNECTION_FACTORY : Jms Topic on which events are sent
--   JMS_SUBSCRIBER_ID            : Option subscriber ID for the events
--   JMS_USERNAME                 : Optional Username for the JMS Connections
--   JMS_PASSWORD                 : Optional Password for the JMS Connections
--   TARGET_GUID        	  : Target/Service instance guid to which the data is written
--   SOURCE_TYPE		  : 0 for Business Indicator Sources
--                                : 1 for Business Events
--
CREATE TABLE MGMT_BAM_ISESSION_DATASOURCE (
   SESSION_GUID RAW (16)  NOT NULL,
   DATASOURCE_NAME VARCHAR2 (64) NOT NULL,
   DS_NAMESPACE VARCHAR2 (256) NULL,
   JMS_TOPIC_CONNECTION_FACTORY  VARCHAR2(200) NOT NULL,
   JMS_TOPIC_NAME VARCHAR2 (200) NOT NULL,
   JMS_SUBSCRIBER_ID VARCHAR2 (100) NULL,
   JMS_USERNAME VARCHAR2(64) NULL,
   JMS_PASSWORD VARCHAR2 (64) NULL,
   TARGET_GUID RAW (16) NULL,
   SOURCE_TYPE NUMBER DEFAULT 0
) MONITORING;


--
-- Table to store Inbound Data Session KPI information
--
-- Columns:
--
--   SESSION_GUID    [PK]         : Guid of the Data Session
--   SOURCE_NAME [PK]         : Name of the Data Object
--   KPI_NAME        [PK]         : KPI name

CREATE TABLE MGMT_BAM_ISESSION_KPIS (
   SESSION_GUID RAW (16)  NOT NULL,
   DATASOURCE_NAME VARCHAR2 (64) NOT NULL,
   KPI_NAME  VARCHAR2(64) NOT NULL
) MONITORING;


--
-- Table to store the last sent timestamps for inbound sessions
--
--  SESSION_GUID            : GUID of the Inbound Data Session
--  DATA_TYPE      	    : Indicates the type of data
--      	              0 for Business Indicator Sources
-- 			      1 for Business Event
--  LAST_RECEIVED_TIMESTAMP : Last received timestamp
--  NUMBER_RECEIVED         : Number of data received for each type
--  STATUS                  : Status of the Inbound Session
--
CREATE TABLE MGMT_BAM_ISESSION_DIAG (
  SESSION_GUID RAW (16) NOT NULL,
  DATA_TYPE NUMBER DEFAULT 0,
  LAST_RECEIVED_TIMESTAMP DATE,
  NUMBER_RECEIVED NUMBER DEFAULT 0,
  STATUS NUMBER DEFAULT 0
) MONITORING;