Rem drv: <create type="tables"/>
Rem
Rem $Header: gensvc_tables.sql 19-aug-2005.16:47:03 yxie 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    yxie        08/12/05 - fix bug 4535347 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    mfidanbo    05/10/05 - add type column to gensvc_updbcn_test 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    snakai      01/10/05 - 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    snakai      11/11/04 - test availability 
Rem    mfidanbo    11/11/04 - add avail tables 
Rem    snakai      09/30/04 - new update job tables 
Rem    snakai      09/24/04 - snakai_svc_template_1
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;

----------------------------------------------------------------
-- Pending/Past Jobs Details
----------------------------------------------------------------
CREATE TABLE MGMT_GENSVC_JOBS_DETAILS
(   target_guid             RAW(16), 
    operation_guid          RAW(16)         DEFAULT SYS_GUID()   NOT NULL,
    job_id                  RAW(16),
    job_owner               VARCHAR2(256),
    execution_id            RAW(16),
    execution_status        NUMBER,
    submission_timestamp    DATE            NOT NULL,
    last_updated_timestamp  DATE            DEFAULT NULL,
    error_message           VARCHAR2(4000)  DEFAULT ''
)
  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;