Rem
Rem $Header: gensvc_tables.sql 13-may-2005.14:23:39 mfidanbo Exp $
Rem
Rem gensvc_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      gensvc_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mfidanbo    05/13/05 - bring up to date 
Rem    mfidanbo    02/17/05 - 
Rem    rmarripa    12/24/04 - rmarripa_webapp_upgrade_changes
Rem    snakai      09/21/04 - Created
Rem


----------------------------------------------------------------
-- Test Collection Update Job
----------------------------------------------------------------

CREATE TABLE MGMT_GENSVC_UPDBCN_JOB
(
    target_guid         RAW(16), 
    operation_guid      RAW(16),
    template_guid       RAW(16),
    beacon_target_guid  RAW(16),
    beacon_name         VARCHAR2(256),
    action_description  VARCHAR2(256)
)
STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_UPDBCN_JOB_TESTS
(
    target_guid         RAW(16), 
    operation_guid      RAW(16),
    test_guid           RAW(16),
    test_name           VARCHAR2(128),
    test_type           VARCHAR2(128),
    action              NUMBER(2)
)
STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

----------------------------------------------------------------
-- Monitoring Templates
----------------------------------------------------------------

CREATE TABLE MGMT_GENSVC_TMPL_VARS
(
   template_guid   RAW(16)        NOT NULL,
   name            VARCHAR2(512)  NOT NULL,
   default_value   VARCHAR2(4000)
)  
STORAGE (FREELISTS 4)
  INITRANS 2
  MONITORING;

----------------------------------------------------------------
-- Service and Test Availabilty
----------------------------------------------------------------

CREATE TABLE MGMT_GENSVC_TEST_AVAIL
    (target_guid                RAW(16)        NOT NULL,
     test_guid                  RAW(16)        NOT NULL,
     severity_guid              RAW(16)        DEFAULT NULL,
     current_status             NUMBER         NOT NULL,
     start_collection_timestamp DATE           NOT NULL,
     end_collection_timestamp   DATE           DEFAULT NULL)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_TEST_CUR_AVAIL
    (target_guid                RAW(16)        NOT NULL,
     test_guid                  RAW(16)        NOT NULL,
     severity_guid              RAW(16)        DEFAULT NULL,
     current_status             NUMBER         NOT NULL,
     start_collection_timestamp DATE           NOT NULL)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_TEST_AVAIL_MARKER
    (target_guid                RAW(16)        NOT NULL,
     test_guid                  RAW(16)        NOT NULL,
     marker_timestamp           DATE           NOT NULL,
     marker_avail_status        NUMBER         NOT NULL)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

-- default OR eval logic
CREATE TABLE MGMT_GENSVC_AVAIL_CONFIG
    (target_guid                RAW(16)        NOT NULL,
     avail_enabled              NUMBER(1)      DEFAULT 0 NOT NULL,
     eval_logic                 NUMBER         DEFAULT 1 NOT NULL)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_AVAIL_TESTS
    (target_guid                RAW(16)        NOT NULL,
     test_guid                  RAW(16)        NOT NULL,
     test_name                  VARCHAR2(64)   NOT NULL,
     test_type                  VARCHAR2(64)   NOT NULL,
     metric_guid                RAW(16)        NOT NULL,
     avail_test                 NUMBER(1),
     monit_status               NUMBER(1))
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_AVAIL_BEACONS
    (target_guid                RAW(16)        NOT NULL,
     beacon_target_guid         RAW(16)        NOT NULL)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_AVAIL_EVENTS
    (target_guid                RAW(16)        NOT NULL,
     test_guid                  RAW(16),
     beacon_target_guid         RAW(16),       
     event_code                 NUMBER(4)      NOT NULL,
     event_description          VARCHAR2(2000),
     event_timestamp            DATE           DEFAULT SYSDATE NOT NULL)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_GENSVC_AVAIL_JOB
    (target_guid                RAW(16)        NOT NULL,
     test_guid                  RAW(16)        NOT NULL,
     next_run                   DATE           NOT NULL,
     in_error                   NUMBER(1)      DEFAULT 0)
  STORAGE
    (FREELISTS 4)
  INITRANS 2
  MONITORING;