Rem $Header: paf_tables_102010.sql 25-oct-2005.12:10:40 fantic Exp $
Rem
Rem paf_tables.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      paf_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    fantic      10/25/05 - commenting out re_interviewable column from 
Rem                           mgmt_paf_applications 
Rem    fantic      10/05/05 - adding re_interviewable column to 
Rem                           mgmt_paf_applications 
Rem    ktamtoro    09/16/05 - add note column in states tables
Rem    ktamtoro    09/09/05 - Add secret column to jobtype params table
Rem    rahgupta    09/04/05 - add cols to paf_states for reason id and args
Rem    rahgupta    08/19/05 - DATA_GUID for states
Rem    ktlaw       08/15/05 -
Rem    rahgupta    08/08/05 - Add column to mgmt_paf_states for index
Rem    ktlaw       08/02/05 -
Rem    ktlaw       08/01/05 -
Rem    ktlaw       07/26/05 - remove foreign key
Rem    ktlaw       07/22/05 - ktlaw_paf_schema_upgrade
Rem    ktlaw       07/22/05 - enable upgrade
Rem    ktlaw       07/18/05 - add state tables
Rem    ktlaw       07/13/05 -
Rem    ktlaw       07/08/05 - change LONG to 16 varchar2(4000) columns
Rem    ktlaw       06/21/05 -
Rem    ktlaw       06/15/05 -
Rem    ktlaw       06/14/05 - ktlaw_paf_update_0614
Rem    ktlaw       06/13/05 - Created
Rem

CREATE TABLE MGMT_PAF_TEXTUAL_DATA
(
  DATA_GUID             VARCHAR2(32) NOT NULL,
  DATA_SIZE             INTEGER NOT NULL,
  ENCODING              VARCHAR2(64),
  DATA0                 VARCHAR2(4000),
  DATA1                 VARCHAR2(4000),
  DATA2                 VARCHAR2(4000),
  DATA3                 VARCHAR2(4000),
  DATA4                 VARCHAR2(4000),
  DATA5                 VARCHAR2(4000),
  DATA6                 VARCHAR2(4000),
  DATA7                 VARCHAR2(4000),
  DATA8                 VARCHAR2(4000),
  DATA9                 VARCHAR2(4000),
  DATA10                VARCHAR2(4000),
  DATA11                VARCHAR2(4000),
  DATA12                VARCHAR2(4000),
  DATA13                VARCHAR2(4000),
  DATA14                VARCHAR2(4000),
  DATA15                VARCHAR2(4000),
  CONSTRAINT MGMT_PAF_TEXTUAL_DATA_PK PRIMARY KEY(DATA_GUID)
) MONITORING ;

CREATE TABLE MGMT_PAF_PROCEDURES
(
  PROCEDURE_GUID        VARCHAR2(32) NOT NULL,
  BASE_GUID             VARCHAR2(32) NOT NULL,
  SOURCE_GUID           VARCHAR2(32),
  VERSION               VARCHAR2(32) NOT NULL,
  APPLICATION_NAME      VARCHAR2(128) NOT NULL,
  NAME                  VARCHAR2(128) NOT NULL,
  DESCRIPTION           VARCHAR2(2000),
  LAST_UPDATED          DATE NOT NULL,
  CREATED_BY            VARCHAR2(64) NOT NULL,
  DATA_GUID             VARCHAR2(32) NOT NULL,
  IS_ORACLE             CHAR(1),
  DELETED               CHAR(1),
  CONSTRAINT MGMT_PAF_PROCEDURES_PK PRIMARY KEY(PROCEDURE_GUID),
  CONSTRAINT MGMT_PAF_PROCEDURES_UNIQ UNIQUE (BASE_GUID,VERSION)
) MONITORING ;

CREATE TABLE MGMT_PAF_JOBTYPES
(
  JOBTYPE_GUID          VARCHAR2(32) NOT NULL,
  JOBTYPE               VARCHAR2(256) NOT NULL,
  DESCRIPTION           VARCHAR2(1000),
  LAST_UPDATED          DATE NOT NULL,
  TARGET_LIST_DEPENDENT CHAR(1),
  CONSTRAINT MGMT_PAF_JOBTYPES_PK PRIMARY KEY( JOBTYPE_GUID )
) MONITORING ;

CREATE TABLE MGMT_PAF_COMP_JOBTYPE_MAPPINGS
(
  MAPPING_GUID          VARCHAR2(32) NOT NULL,
  COMPONENT_SUBTYPE     VARCHAR2(256) NOT NULL,
  JOBTYPE_GUID          VARCHAR2(128) NOT NULL,
  LAST_UPDATED          DATE NOT NULL,
  CONSTRAINT MGMT_PAF_CMPJOB_PK PRIMARY KEY( MAPPING_GUID ),
  CONSTRAINT MGMT_PAF_CMPJOB_UNIQ UNIQUE( COMPONENT_SUBTYPE, JOBTYPE_GUID ),
  CONSTRAINT MGMT_PAF_CMPJOB_FK FOREIGN KEY( JOBTYPE_GUID )
        REFERENCES MGMT_PAF_JOBTYPES( JOBTYPE_GUID ) ON DELETE CASCADE
) MONITORING ;

CREATE TABLE MGMT_PAF_JOBTYPE_PARAMS
(
  JOBTYPE_GUID          VARCHAR2(32) NOT NULL,
  NAME                  VARCHAR2(128),
  DESCRIPTION           VARCHAR2(1000),
  PARAM_NAME            VARCHAR2(64) NOT NULL,
  PARAM_VALUE           VARCHAR2(256),
  IMPLICIT              CHAR(1),
  SECRET_STATUS         VARCHAR2(128),
  TARGET_TYPE           VARCHAR2(128),
  CONSTRAINT MGMT_PAF_JOBPARAMS_PK PRIMARY KEY( JOBTYPE_GUID, PARAM_NAME ),
  CONSTRAINT MGMT_PAF_JOBPARAMS_FK FOREIGN KEY( JOBTYPE_GUID )
        REFERENCES MGMT_PAF_JOBTYPES( JOBTYPE_GUID ) ON DELETE CASCADE
) MONITORING ;

CREATE TABLE MGMT_PAF_APPLICATIONS
(
  APPLICATION_GUID      VARCHAR2(32) NOT NULL,
  NAME                  VARCHAR2(128) NOT NULL,
  DESCRIPTION           VARCHAR2(1000),
  URL                   VARCHAR2(512) NOT NULL,
  RESOURCE_BUNDLE       VARCHAR2(1000),
  DATA_GUID             VARCHAR2(32),
--  RE_INTERVIEWABLE      char(1),
  CONSTRAINT MGMT_PAF_APPS_PK PRIMARY KEY( APPLICATION_GUID ),
  CONSTRAINT MGMT_PAF_APPS_UNIQ UNIQUE( NAME )
) MONITORING ;

CREATE TABLE MGMT_PAF_INSTANCES
(
  INSTANCE_GUID         VARCHAR2(32) NOT NULL,
  INSTANCE_NAME         VARCHAR2(256) NOT NULL,
  PROCEDURE_GUID        VARCHAR2(32) NOT NULL,
  DATA_GUID             VARCHAR2(32) NOT NULL,
  CREATED_BY            VARCHAR2(64),
  CREATED               DATE,
  STARTED               DATE,
  COMPLETED             DATE,
  LAST_UPDATED          DATE,
  DELETED               CHAR(1),
  CONSTRAINT MGMT_PAF_INSTANCES_PK PRIMARY KEY ( INSTANCE_GUID ),
  CONSTRAINT MGMT_PAF_INSTANCES_FK FOREIGN KEY ( PROCEDURE_GUID )
        REFERENCES MGMT_PAF_PROCEDURES( PROCEDURE_GUID ) ON DELETE CASCADE
) MONITORING ;

CREATE TABLE MGMT_PAF_STATES
(
  STATE_GUID            VARCHAR2(32) NOT NULL,
  INSTANCE_GUID         VARCHAR2(32) NOT NULL,
  STEP_GUID             VARCHAR2(32) NOT NULL,
  PARENT_STATE_GUID     VARCHAR2(32),
  STATE_TYPE            INTEGER NOT NULL,
  STATUS                INTEGER,
  REASON_CODE           INTEGER,
  REASON_RSC_ID         VARCHAR2(64),
  REASON_RSC_ARGS       VARCHAR2(1024),
  REASON_EXCEPTION      VARCHAR2(1024),
  -- indicates the index of the state within
  -- the enclosed container.
  STATE_INDEX           INTEGER,
  STARTED               DATE,
  LAST_UPDATED          DATE,
  COMPLETED             DATE,
  TARGET_NAME           VARCHAR2(128),
  TARGET_TYPE           VARCHAR2(128),
  CURRENT_STATE_GUID    VARCHAR2(32),
  -- job related info
  JOB_ID                VARCHAR2(32),
  EXEC_ID                VARCHAR2(32),
  JOB_STATUS            INTEGER,
  NOTE                  VARCHAR2(4000),
  CONSTRAINT MGMT_PAF_STATES_PK PRIMARY KEY ( STATE_GUID ),
  CONSTRAINT MGMT_PAF_STATES_FK FOREIGN KEY ( INSTANCE_GUID )
        REFERENCES MGMT_PAF_INSTANCES( INSTANCE_GUID ) ON DELETE CASCADE
) MONITORING ;