CREATE TABLE FIPERSYS (
  KEYNAME VARCHAR(250) NOT NULL,
  VALUESTR VARCHAR(250),
  VALUEINT INTEGER);
ALTER TABLE FIPERSYS ADD CONSTRAINT FIPERSYSPK PRIMARY KEY (KEYNAME);


-- This defines the version of the table structures below
INSERT INTO FIPERSYS(KEYNAME, VALUESTR, VALUEINT) VALUES('SchemaVersion', NULL, 52300);

-------------------------------------------------------------------------------  

CREATE TABLE JOBLOGDATABEAN
  (SERVERTIME NUMBER(38, 0),
   VAR9 VARCHAR2(1000),
   VAR8 VARCHAR2(1000),
   VAR7 VARCHAR2(1000),
   VAR6 VARCHAR2(1000),
   VAR5 VARCHAR2(1000),
   VAR4 VARCHAR2(1000),
   VAR3 VARCHAR2(1000),
   VAR2 VARCHAR2(1000),
   VAR1 VARCHAR2(1000),
   VAR0 VARCHAR2(1000),
   NUMPARAMETERS INTEGER,
   COMPVER VARCHAR2(24),
   COMPNAME VARCHAR2(130),
   ISTRINGID INTEGER,
   CLASSNAME VARCHAR2(250),
   SEVERITY INTEGER,
   LOGDTTIME TIMESTAMP,
   CHAINEDEXCEPTION BLOB,
   WORKITEMID VARCHAR2(20),
   MSG VARCHAR2(1000),
   JOBID VARCHAR2(20),
   COMPPATH VARCHAR2(2000),
   SOURCE VARCHAR2(250) NOT NULL,
   PRIMKEY VARCHAR2(20) NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE JOBLOGDATABEAN ADD CONSTRAINT JOBLOGDATABEANPK PRIMARY KEY (PRIMKEY);
CREATE INDEX JLOG_INDEX2 ON JOBLOGDATABEAN (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE JOBBEAN
  (PRIMKEY VARCHAR2(20) NOT NULL,
   JOBDESC VARCHAR2(250),
   STARTDATE TIMESTAMP,
   ENDDATE TIMESTAMP,
   PURGEDATE TIMESTAMP,
   STATUS INTEGER,
   CC INTEGER,
   USERNAME VARCHAR2(250),
   MODELNAME VARCHAR2(130),
   MODELVERSION VARCHAR2(24),
   MODELID VARCHAR2(130),
   SEVERITY INTEGER,
   LICENSE VARCHAR2(2000),
   PARTNERID VARCHAR2(20),
   REMOTEUSERID VARCHAR2(250),
   REMOTEDOMAIN VARCHAR2(250),
   REMOTECORRELATIONID VARCHAR2(250),
   PARENTJOBID VARCHAR2(20),
   SEEDVALUE NUMBER(38,0),
   SUBMISSIONHOSTNAME VARCHAR2(250),
   EXACTLOOKUPMODE INTEGER,
   EXACTLOOKUPIGNOREFILES INTEGER,
   PENDING INTEGER NOT NULL,
   LICENSECONCURRENCY INTEGER NOT NULL,
   MAXSUBFLOWBATCHSIZE INTEGER NOT NULL,
   LICENSETYPE INTEGER DEFAULT ON NULL 0,
   VERSION NUMBER(38,0));

ALTER TABLE JOBBEAN ADD CONSTRAINT JOBBEANPK PRIMARY KEY (PRIMKEY);

-- Do not re-use indexes JOB_IDX1 and JOB_IDX2, they will be deleted by the migrate code
-- Dashboard queries
CREATE INDEX JOB_IDX3 ON JOBBEAN (ENDDATE, PRIMKEY);
-- Dashboard queries
CREATE INDEX JOB_IDX4 ON JOBBEAN (STATUS, PRIMKEY); 
-- Client and webtop queries
CREATE INDEX JOB_IDX5 ON JOBBEAN (USERNAME); 
CREATE INDEX JOB_IDX6 ON JOBBEAN (MODELNAME); 
CREATE INDEX JOB_IDX7 ON JOBBEAN (JOBDESC); 
CREATE INDEX JOB_IDX8 ON JOBBEAN (STARTDATE);

-------------------------------------------------------------------------------  

CREATE TABLE JOBMODEL  (
  JOBID VARCHAR2(20) NOT NULL,   
  WORKITEMID VARCHAR(20) NOT NULL,   
  REFCOMPID VARCHAR(130) NOT NULL,   
  MODELXML BLOB,
  VERSIONSCOPE BLOB);

ALTER TABLE JOBMODEL ADD CONSTRAINT JOBMODELPK PRIMARY KEY (JOBID, WORKITEMID, REFCOMPID);

-------------------------------------------------------------------------------  

CREATE TABLE STATIONBEAN
  (PRIMKEY VARCHAR2(20) NOT NULL,
   SNAME VARCHAR2(250),
   STATUS INTEGER,
   STARTED NUMBER(38, 0),
   STOPPED NUMBER(38, 0),
   EXPIRETIME NUMBER(38, 0),
   LEASEINTERVAL NUMBER(38, 0),
   USECOUNT INTEGER,
   AFFINITYHOST VARCHAR2(60),
   AFFINITYOS VARCHAR2(30),
   AFFINITYOSNAME VARCHAR2(30),
   AFFINITYOSVER VARCHAR2(60),
   AFFINITYOSARCH VARCHAR2(30),
   AFFINITYOTHERS VARCHAR2(250),
   USERNAME VARCHAR2(64) NOT NULL,
   CONCURRENCY INTEGER NOT NULL,
   STATIONHOSTNAME VARCHAR2(250) NOT NULL,
   DESCRIPTION VARCHAR2(250),
   DRMMODE VARCHAR2(10) NOT NULL,
   ALLOWEDUSERS VARCHAR2(250),
   PUBLICKEYDESCRIPTOR BLOB,
   VERSION NUMBER(38,0));

ALTER TABLE STATIONBEAN ADD CONSTRAINT STATIONBEANPK PRIMARY KEY (PRIMKEY);
  
CREATE INDEX STATION_IDX1 ON STATIONBEAN (EXPIRETIME); 
CREATE INDEX SB_INDEX1 ON STATIONBEAN (SNAME);
  
-------------------------------------------------------------------------------  

CREATE TABLE WORKITEMBEAN
  (PRIMKEY VARCHAR2(20) NOT NULL,
   COMPTYPE VARCHAR2(250),
   COMPVERSION INTEGER,
   COMPPATH VARCHAR2(2000),
   COMPMODELID INTEGER,
   JOBID VARCHAR2(20),
   WORKFLOWID VARCHAR2(20),
   STATUS INTEGER,
   DISPATCHTARGET VARCHAR2(250),
   CREATEDATE TIMESTAMP,
   DISPATCHDATE TIMESTAMP,
   ENDDATE TIMESTAMP,
   EXECUTEDURATION NUMBER(38, 0),
   CC INTEGER,
   COMPONENTCRC NUMBER(38, 0),
   TIMEOUT NUMBER(38,0),
   ITERATIONPATH VARCHAR2(2000), 
   DRMTYPE VARCHAR(130), 
   DRMJOBID VARCHAR(250), 
   RETRYCOUNT NUMBER(38,0),
   MAXRETRY NUMBER(38,0),
   COMPVERSTR VARCHAR2(24) NOT NULL,
   COMPMODELIDSTR VARCHAR2(130) NOT NULL,
   REFWIID VARCHAR2(20),
   APPROXWIID VARCHAR2(20),
   EVALTYPE INTEGER,
   LOOKUPREASONCODE INTEGER,
   APPROXORIGID VARCHAR2(15),
   WI_NUM_DB_BYTES NUMBER(38,0),
   WI_NUM_VALUES NUMBER(38,0),
   WI_NUM_DISK_BYTES NUMBER(38,0),
   SF_NUM_DB_BYTES NUMBER(38,0),
   SF_NUM_VALUES NUMBER(38,0),
   SF_NUM_DISK_BYTES NUMBER(38,0),
   AFFINITIES VARCHAR2(20),
   GROUPNAME VARCHAR2(50),
   COSIMGROUPID VARCHAR(20),
   PENDINGREPORTED SMALLINT,
   NOSAVEDESCENDANTPARMS SMALLINT,
   VERSION NUMBER(38,0));

ALTER TABLE WORKITEMBEAN ADD CONSTRAINT WORKITEMBEANPK PRIMARY KEY (PRIMKEY);

CREATE INDEX WIB_INDEX1 ON WORKITEMBEAN (JOBID, STATUS);
CREATE INDEX WIB_INDEX2 ON WORKITEMBEAN (WORKFLOWID, STATUS);
CREATE INDEX WIB_INDEX3 ON WORKITEMBEAN (DISPATCHTARGET, STATUS);
CREATE INDEX WIB_INDEX4 ON WORKITEMBEAN (JOBID);
CREATE INDEX WIB_INDEX5 ON WORKITEMBEAN (WORKFLOWID);
CREATE INDEX WIB_INDEX6 ON WORKITEMBEAN (EVALTYPE, WORKFLOWID);
CREATE INDEX WIB_INDEX7 ON WORKITEMBEAN (AFFINITIES);
CREATE INDEX WIB_INDEX8 ON WORKITEMBEAN (STATUS, PENDINGREPORTED);
CREATE INDEX WIB_INDEX9 ON WORKITEMBEAN (COSIMGROUPID);
CREATE INDEX WORKITEMBEAN_AC1 ON WORKITEMBEAN (JOBID,STATUS,AFFINITIES);

-------------------------------------------------------------------------------  

CREATE TABLE WORKFLOWBEAN
  (PRIMKEY VARCHAR2(20) NOT NULL,
   PARENTWORKITEMID VARCHAR2(20),
   STATUS VARCHAR2(10) NOT NULL,
   CC INTEGER,
   ORIGNAME VARCHAR2(250),
   ORIGID VARCHAR2(200),
   JOBID VARCHAR2(20),
   EVALTYPE INTEGER,
   SF_NUM_DB_BYTES NUMBER(38,0),
   SF_NUM_VALUES NUMBER(38,0),
   SF_NUM_DISK_BYTES NUMBER(38,0),
   VERSION NUMBER(38,0));

ALTER TABLE WORKFLOWBEAN ADD CONSTRAINT WORKFLOWBEANPK PRIMARY KEY (PRIMKEY);

CREATE INDEX WFB_INDEX1 ON WORKFLOWBEAN (ORIGID, PARENTWORKITEMID);
CREATE INDEX WFB_INDEX2 ON WORKFLOWBEAN (JOBID, ORIGID);
CREATE INDEX WFB_INDEX3 ON WORKFLOWBEAN (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE WORKCONTEXTBEAN
  (PRIMKEY VARCHAR2(20) NOT NULL,
   JOBID VARCHAR2(20),
   WORKCONTEXTBYTES BLOB,
   SYNCID NUMBER(38,0) NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE WORKCONTEXTBEAN ADD CONSTRAINT WORKCONTEXTBEANPK PRIMARY KEY (PRIMKEY);
CREATE INDEX WCB_INDEX1 ON WORKCONTEXTBEAN (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE JOBMONITOR
  (DEST BLOB,
   JOBID VARCHAR2(20),
   PRIMKEY VARCHAR2(20) NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE JOBMONITOR ADD CONSTRAINT JOBMONITORPK PRIMARY KEY (PRIMKEY);
CREATE INDEX JM_INDEX1 ON JOBMONITOR (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE PARTNERPROFILEBEAN
  (LOCATIONURL VARCHAR2(250),
   CALLBACKURL VARCHAR2(250),
   DISPLAYNAME VARCHAR2(100),
   SOAPTYPE VARCHAR2(10),
   PRIMKEY VARCHAR2(20) NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE PARTNERPROFILEBEAN ADD CONSTRAINT PARTNERPROFILEB1PK PRIMARY KEY (PRIMKEY);

-------------------------------------------------------------------------------  
  
CREATE TABLE SYNCPOINT (
  PRIMKEY VARCHAR(20) NOT NULL,   
  WFID VARCHAR(20),
  JOBID VARCHAR(20),
  COMPPATH VARCHAR(2000),
  BRANCHES INTEGER,
  TAKEN INTEGER,
  WAITERS INTEGER,
  VERSION NUMBER(38,0));

ALTER TABLE SYNCPOINT ADD CONSTRAINT SYNCPOINTPK PRIMARY KEY  (PRIMKEY);
CREATE INDEX SP_INDEX1 ON SYNCPOINT (WFID);
CREATE INDEX SP_INDEX2 ON SYNCPOINT (JOBID);

-------------------------------------------------------------------------------  
  
CREATE TABLE AFFINITYGROUPBEAN
  (JOBID VARCHAR(20),
  WORKFLOWID VARCHAR2(20) NOT NULL,
   GROUPNAME VARCHAR2(50) NOT NULL,
   STATIONNAME VARCHAR2(250),
   VERSION NUMBER(38,0));
   
ALTER TABLE AFFINITYGROUPBEAN ADD CONSTRAINT AFFINITYGROUPPK PRIMARY KEY (WORKFLOWID, GROUPNAME);

CREATE INDEX AFFGRP_IDX2 ON AFFINITYGROUPBEAN (WORKFLOWID, STATIONNAME, GROUPNAME); 
CREATE INDEX AFFGRP_IDX3 ON AFFINITYGROUPBEAN (WORKFLOWID); 
CREATE INDEX AFFGRP_IDX4 ON AFFINITYGROUPBEAN (JOBID); 

-------------------------------------------------------------------------------  
  
CREATE TABLE METAINFO 
  (PRIMKEY VARCHAR(20) NOT NULL, 
  LOCALID VARCHAR(100), 
  LOCALMETA BLOB, 
  FOREIGNID VARCHAR(100), 
  LINKREF VARCHAR(20),
  VERSION NUMBER(38,0));

ALTER TABLE METAINFO ADD CONSTRAINT METAINFOPK PRIMARY KEY (PRIMKEY);
CREATE INDEX MI_INDEX1 ON METAINFO (LINKREF); 
CREATE INDEX MI_INDEX2 ON METAINFO (LINKREF, FOREIGNID); 
  
-------------------------------------------------------------------------------  

CREATE TABLE FILESTORE 
  (PRIMKEY VARCHAR2(20) NOT NULL, 
  FNAME VARCHAR2(1024), 
  JOBID VARCHAR2(20), 
  WFID VARCHAR2(20), 
  WIID VARCHAR2(20),
  PERSIST SMALLINT, 
  VERSION NUMBER(38,0));

ALTER TABLE FILESTORE ADD CONSTRAINT FILESTOREPK PRIMARY KEY (PRIMKEY);
CREATE INDEX FS_IDX1 ON FILESTORE (JOBID, PRIMKEY);
CREATE INDEX FS_IDX2 ON FILESTORE (JOBID);
CREATE INDEX FS_IDX3 ON FILESTORE (WIID);
  
-------------------------------------------------------------------------------  

CREATE TABLE APPROXDATA (
  PRIMKEY VARCHAR(20) NOT NULL, 
  JOBID VARCHAR(20), 
  COMPONENTID VARCHAR2(130), 
  APPROXID VARCHAR2(130), 
  APPROXOBJ BLOB,
  VERSION NUMBER(38,0));

ALTER TABLE APPROXDATA ADD CONSTRAINT APPROXDATAPK PRIMARY KEY (PRIMKEY);
CREATE INDEX APPROXDATA_INDEX1 ON APPROXDATA (JOBID, COMPONENTID);
CREATE INDEX APPROXDATA_INDEX2 ON APPROXDATA (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE UIDGEN 
  (VALUE CHAR(16), 
  NAME CHAR(8));  
  
-------------------------------------------------------------------------------  
  
CREATE TABLE ACCESSCONTROLLIST
  (PRIORITY INTEGER NOT NULL,
   ACCESSID VARCHAR2(20),
   ACCESSLEVEL VARCHAR2(250),
   ACCESSTYPE VARCHAR2(250),
   RESOURCEID VARCHAR2(250) NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE ACCESSCONTROLLIST ADD CONSTRAINT ACCESSCONTROLLI1PK PRIMARY KEY (PRIORITY, RESOURCEID);
CREATE INDEX ACL_INDEX1 ON ACCESSCONTROLLIST (ACCESSID);
CREATE INDEX ACL_INDEX2 ON ACCESSCONTROLLIST (ACCESSID, ACCESSLEVEL);
CREATE INDEX ACL_INDEX3 ON ACCESSCONTROLLIST (RESOURCEID);
CREATE INDEX ACL_INDEX4 ON ACCESSCONTROLLIST (RESOURCEID, ACCESSID, ACCESSLEVEL );

-------------------------------------------------------------------------------  

CREATE TABLE DEFAULTACL
  (IMPLICITACCESSTYPE VARCHAR2(250) NOT NULL,
   PRIORITY INTEGER NOT NULL,
   ACCESSID VARCHAR2(250),
   ACCESSLEVEL VARCHAR2(250),
   ACCESSTYPE VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE DEFAULTACL ADD CONSTRAINT DEFAULTACLPK PRIMARY KEY (IMPLICITACCESSTYPE, PRIORITY);
CREATE INDEX DEFACL_INDEX1 ON DEFAULTACL (ACCESSID);
CREATE INDEX DEFACL_INDEX2 ON DEFAULTACL (ACCESSID, ACCESSLEVEL);
INSERT INTO DEFAULTACL (IMPLICITACCESSTYPE,PRIORITY,ACCESSID,ACCESSLEVEL,ACCESSTYPE,VERSION) VALUES ('SD',1,'*','U','AL',1);

-------------------------------------------------------------------------------  

CREATE TABLE FILESERVERDETAIL
  (HIERARCHY VARCHAR2(250) NOT NULL,
   HOSTNAME VARCHAR2(250),
   LOGINNAME VARCHAR2(250),
   PASSWORD VARCHAR2(250),
   PROTOCOL VARCHAR2(250),
   HOMEDIRECTORY VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE FILESERVERDETAIL ADD CONSTRAINT FILESERVERDETAILPK PRIMARY KEY (HIERARCHY);

-------------------------------------------------------------------------------  

CREATE TABLE GROUPMASTER
  (GROUPNAME VARCHAR2(250) NOT NULL,
   GROUPDESC VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE GROUPMASTER ADD CONSTRAINT GROUPMASTERPK PRIMARY KEY (GROUPNAME);

-------------------------------------------------------------------------------  

CREATE TABLE GROUPUSERMAP
  (GROUPNAME VARCHAR2(250) NOT NULL,
   ACCESSID VARCHAR2(250) NOT NULL,
   ACCESSLEVEL VARCHAR2(250) NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE GROUPUSERMAP ADD CONSTRAINT GROUPUSERMAPPK PRIMARY KEY (GROUPNAME, ACCESSID, ACCESSLEVEL);
CREATE INDEX GUM_INDEX1 ON GROUPUSERMAP (ACCESSID);
CREATE INDEX GUM_INDEX2 ON GROUPUSERMAP (ACCESSID, ACCESSLEVEL);

-------------------------------------------------------------------------------  

CREATE TABLE HIERARCHYMASTER
  (PACKAGEID VARCHAR2(20) NOT NULL,
   PACKAGENAME VARCHAR2(250),
   PARENTID VARCHAR2(20),
   VERSION NUMBER(38,0));

ALTER TABLE HIERARCHYMASTER ADD CONSTRAINT HIERARCHYMASTERPK PRIMARY KEY (PACKAGEID);
CREATE INDEX HM_INDEX1 ON HIERARCHYMASTER (PACKAGENAME, PARENTID);
CREATE INDEX HM_INDEX2 ON HIERARCHYMASTER (PARENTID);

-------------------------------------------------------------------------------  

CREATE TABLE OBJECTMASTER
  (OBJECTID VARCHAR2(20) NOT NULL,
   OBJECTNAME VARCHAR2(250),
   OBJECTTYPE INTEGER,
   CHECKOUTFLAG VARCHAR2(250),
   CHECKOUTBY VARCHAR2(250),
   CHECKOUTDATE DATE,
   PACKAGENAME VARCHAR2(250),
   PARENTPACKAGEID VARCHAR2(20),
   DESCRIPTION VARCHAR2(2000),
   OBJECTSUBTYPE VARCHAR2(250),
   OBJECTPRIVILEGES INTEGER,
   VERSION NUMBER(38,0));

ALTER TABLE OBJECTMASTER ADD CONSTRAINT OBJECTMASTERPK PRIMARY KEY (OBJECTID);
CREATE INDEX OM_INDEX1 ON OBJECTMASTER (PARENTPACKAGEID);
CREATE INDEX OM_INDEX2 ON OBJECTMASTER (OBJECTNAME, PACKAGENAME);
CREATE INDEX OM_INDEX3 ON OBJECTMASTER (PACKAGENAME);

-------------------------------------------------------------------------------  

CREATE TABLE OBJECTNAMEVALUE
  (OBJECTID VARCHAR2(20) NOT NULL,
   OBJECTVERSION VARCHAR2(24) NOT NULL,
   ATTRIBUTENAME VARCHAR2(250) NOT NULL,
   ATTRIBUTEVALUE VARCHAR2(250) NOT NULL,
   OBJECTVERSIONID VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE OBJECTNAMEVALUE ADD CONSTRAINT OBJECTNAMEVALUEPK PRIMARY KEY (OBJECTID, OBJECTVERSION, ATTRIBUTENAME, ATTRIBUTEVALUE);
CREATE INDEX NV_INDEX1 ON OBJECTNAMEVALUE (OBJECTID, OBJECTVERSION);
CREATE INDEX NV_INDEX2 ON OBJECTNAMEVALUE (OBJECTID);

-------------------------------------------------------------------------------  

CREATE TABLE OBJECTREFERENCELIST
  (REFERENCEID VARCHAR2(20) NOT NULL,
   OBJECTID VARCHAR2(20),
   OBJECTVERSION VARCHAR2(24),
   OBJECTREFERENCE VARCHAR2(250),
   REFERENCEVERSIONID VARCHAR2(24),
   OBJECTVERSIONID VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE OBJECTREFERENCELIST ADD CONSTRAINT OBJECTREFERENCE2PK PRIMARY KEY (REFERENCEID);

-------------------------------------------------------------------------------  

CREATE TABLE OBJECTVERSIONMASTER
  (OBJECTID VARCHAR2(20) NOT NULL,
   OBJECTMAJORVERSION INTEGER NOT NULL,
   OBJECTMINORVERSION INTEGER NOT NULL,
   OBJECTINCRVERSION INTEGER NOT NULL,
   INTERNALLOCATIONID VARCHAR2(250),
   OBJECTSIZE INTEGER NOT NULL,
   AUTHORID VARCHAR2(250),
   CREATEDATE DATE,
   DESCRIPTION VARCHAR2(2000),
   OBJECTVERSIONID VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE OBJECTVERSIONMASTER ADD CONSTRAINT OBJECTVERSIONMA3PK PRIMARY KEY (OBJECTID, OBJECTMAJORVERSION, OBJECTMINORVERSION, OBJECTINCRVERSION);
CREATE INDEX OVM_INDEX1 ON OBJECTVERSIONMASTER (INTERNALLOCATIONID);

-------------------------------------------------------------------------------  

CREATE TABLE VERSIONCONTROLIMPLEMENTATION
  (PRIMKEY VARCHAR2(20) NOT NULL,
   HIERARCHY VARCHAR2(250) NOT NULL,
   VERSIONCONTROLLER VARCHAR2(250),
   CONFIG VARCHAR2(1000),
   IMPLEMENTATIONCLASS VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE VERSIONCONTROLIMPLEMENTATION ADD CONSTRAINT VERSIONCONTROLI4PK PRIMARY KEY (PRIMKEY);

-------------------------------------------------------------------------------  

CREATE TABLE VERSIONCONTROLMASTER
  (OBJECTID VARCHAR2(20) NOT NULL,
   VERSIONID INTEGER NOT NULL,
   INTERNALFILENAME VARCHAR2(250),
   CHECKOUTFLAG VARCHAR2(250),
   HIERARCHY VARCHAR2(250),
   VERSION NUMBER(38,0));

ALTER TABLE VERSIONCONTROLMASTER ADD CONSTRAINT VERSIONCONTROLM5PK PRIMARY KEY (OBJECTID, VERSIONID);
  
-------------------------------------------------------------------------------  

CREATE TABLE DBVERSIONCONTROLLER
  (PRIMKEY VARCHAR(20) NOT NULL,
   FILECONTENT BLOB,
   VERSION NUMBER(38,0));

ALTER TABLE DBVERSIONCONTROLLER ADD CONSTRAINT DBVERSIONCONTROLPK PRIMARY KEY (PRIMKEY);

-------------------------------------------------------------------------------  

CREATE TABLE TCBASICSEARCHPARMS
  (PRIMARYKEY VARCHAR(250) NOT NULL,
   TEXT VARCHAR(250),
   OPERATOR VARCHAR(250),
   USENAMES SMALLINT NOT NULL,
   USEDESCRIPTIONS SMALLINT NOT NULL,
   USEAUTHORS SMALLINT NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE TCBASICSEARCHPARMS ADD CONSTRAINT PK_TCBASICSEARCHP2 PRIMARY KEY (PRIMARYKEY);

-------------------------------------------------------------------------------  

CREATE TABLE TCMODEL
  (PRIMARYKEY VARCHAR(250) NOT NULL,
   USERID VARCHAR(250),
   NAME VARCHAR(250),
   VERSION1 VARCHAR2(24),
   MODELDATA BLOB,
   VERSION NUMBER(38,0));

ALTER TABLE TCMODEL ADD CONSTRAINT PK_TCMODEL PRIMARY KEY (PRIMARYKEY);
CREATE INDEX TCM_INDEX1 ON TCMODEL (USERID);
  
-------------------------------------------------------------------------------  

CREATE TABLE TCPREFERENCES
  (PRIMARYKEY VARCHAR2(250) NOT NULL,
   USERID VARCHAR2(250) NULL,
   CURRENTMODELNAME VARCHAR2(250),
   CURRENTMODELVERSION VARCHAR2(24),
   CURRENTVIEW VARCHAR2(250),
   IPADDR VARCHAR2(32),
   LASTACCESS NUMBER,
   VIEWDATA VARCHAR2(250),
   RECOVERING INTEGER,
   VERSION NUMBER(38,0),
   LOGLEVEL INTEGER);

ALTER TABLE TCPREFERENCES ADD CONSTRAINT PK_TCPREFERENCES PRIMARY KEY (PRIMARYKEY);
CREATE INDEX TCPREF_INDEX1 ON TCPREFERENCES (LASTACCESS);
CREATE INDEX TCPREF_INDEX2 ON TCPREFERENCES (USERID);

-------------------------------------------------------------------------------  

CREATE TABLE ACSPKIDESCRIPTOR 
	(PRIMKEY VARCHAR2(20) NOT NULL, 
	 PRIVATEKEY BLOB NOT NULL, 
	 PUBLICKEY BLOB NOT NULL, 
	 CREATEDTIME NUMBER(38,0),
	 VERSION NUMBER(38,0));
	 
ALTER TABLE ACSPKIDESCRIPTOR ADD CONSTRAINT ACSPKIDESCRIPTORPK PRIMARY KEY (PRIMKEY);

-------------------------------------------------------------------------------  

CREATE TABLE JOBOWNER 
	(JOBID VARCHAR(20) NOT NULL,
	 JOBOWNERNAME VARCHAR2(255),
	 JOBOWNERMETADATA BLOB NOT NULL,
	 JOBOWNERDOMAIN VARCHAR2(255),
   JOBOWNERWINNAME VARCHAR2(255),
   JOBOWNERWINMETA BLOB,
   JOBOWNERUNIXNAME VARCHAR2(255),
   JOBOWNERUNIXMETA BLOB,  
	 VERSION NUMBER(38,0));
	 
ALTER TABLE JOBOWNER ADD CONSTRAINT PK_JOBOWNER PRIMARY KEY (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE RESULTREQUESTREGISTRY
   (PRIMARYKEY VARCHAR2(20) NOT NULL,
   JOBID VARCHAR2(20) NOT NULL,
   COMPONENTPATH VARCHAR2(2000) NOT NULL,
   RESULTREQUEST BLOB NOT NULL,
   JMSTOPIC BLOB NOT NULL,
   VERSION NUMBER(38,0));

ALTER TABLE RESULTREQUESTREGISTRY ADD CONSTRAINT PK_RESREQREG PRIMARY KEY (PRIMARYKEY);
CREATE INDEX RRR_INDEX1 ON RESULTREQUESTREGISTRY (JOBID);

-------------------------------------------------------------------------------  

CREATE TABLE DATA_TYPES(
    DATA_TYPE_ABBREV VARCHAR(4) NOT NULL,
    METAMODEL_TYPE VARCHAR2(130) NOT NULL,
    MAJOR_VERSION_NUM INTEGER NOT NULL,
    CONSTRAINT PDATA_TYPES PRIMARY KEY (DATA_TYPE_ABBREV));

CREATE UNIQUE INDEX DATA_TYPES_INDEX ON DATA_TYPES(METAMODEL_TYPE, MAJOR_VERSION_NUM);

-------------------------------------------------------------------------------  

CREATE TABLE MASTERMODEL(
    MODEL_KEY VARCHAR(20) NOT NULL,
    MODEL_ID VARCHAR2(130) NOT NULL,
    MODEL_NAME VARCHAR2(130) NOT NULL,
    SCHEMA_VERSION INTEGER NOT NULL,
    BASE_TABLE_NAME VARCHAR(10) NOT NULL,
    CONSTRAINT PMASTERMODEL PRIMARY KEY (MODEL_KEY));

CREATE UNIQUE INDEX MASTERMODELINDEX ON MASTERMODEL(MODEL_ID, MODEL_NAME);
    
-------------------------------------------------------------------------------  

CREATE TABLE IMPORTDETAILS  (
  PRIMKEY VARCHAR(20) NOT NULL,   
  ORIG_USERID VARCHAR(250),   
  PERCENT_COMPLETE INTEGER,   
  IMPORTED_DATE DATE,   
  ORIG_JOBID VARCHAR(20),   
  ORIG_SYSID VARCHAR(130),   
  CANCELLED SMALLINT );

ALTER TABLE IMPORTDETAILS ADD CONSTRAINT IMPORTDTPK PRIMARY KEY (PRIMKEY);

-------------------------------------------------------------------------------  

CREATE TABLE WORKITEMSUMMARY  (
  PRIMKEY VARCHAR(20) NOT NULL,   
  COMPPATHKEY INTEGER NOT NULL,   
  STATUS INTEGER NOT NULL,   
  CC INTEGER NOT NULL,   
  EVALTYPE INTEGER NOT NULL,   
  COMPPATH VARCHAR(2000),   
  WORKITEM_COUNT INTEGER,   
  MIN_DURATION NUMBER(38,0),
  MAX_DURATION NUMBER(38,0),
  SUM_DURATION NUMBER(38,0),
  WI_NUM_DB_BYTES NUMBER(38,0),
  WI_NUM_VALUES NUMBER(38,0),
  WI_NUM_DISK_BYTES NUMBER(38,0),
  SF_NUM_DB_BYTES NUMBER(38,0),
  SF_NUM_VALUES NUMBER(38,0),
  SF_NUM_DISK_BYTES NUMBER(38,0));

ALTER TABLE WORKITEMSUMMARY ADD CONSTRAINT WORKITEMSUMPK PRIMARY KEY (PRIMKEY, COMPPATHKEY, STATUS, CC, EVALTYPE);
CREATE INDEX WORKITEMSUM_IDX1 ON WORKITEMSUMMARY (PRIMKEY);

-------------------------------------------------------------------------------  

CREATE TABLE FIPERLOCK  (
  LOCKNAME VARCHAR(500) NOT NULL,   
  LOCKHOLDER VARCHAR(250));

--- THE CONTRAINT NAME MUST REMAIN (P)TABLENAME 
ALTER TABLE FIPERLOCK ADD CONSTRAINT PFIPERLOCK PRIMARY KEY (LOCKNAME);

-------------------------------------------------------------------------------  

CREATE TABLE JOBVIEWDATA  (
  JOBID VARCHAR(20) NOT NULL,   
  CONFIGNAME VARCHAR(130) NOT NULL,   
  CONFIGXML BLOB NOT NULL);
  
ALTER TABLE JOBVIEWDATA ADD CONSTRAINT JOBVIEWDATAPK PRIMARY KEY (JOBID, CONFIGNAME);

-------------------------------------------------------------------------------  
CREATE TABLE AFFINITIES (
  PRIMKEY VARCHAR(20) NOT NULL,
  OS VARCHAR(50) NOT NULL,
  OSNAME VARCHAR(50) NOT NULL,
  OSVER VARCHAR(50) NOT NULL,
  OSARCH VARCHAR(50) NOT NULL,
  STATIONS VARCHAR(100) NOT NULL,
  HOSTNAME VARCHAR(250) NOT NULL,
  OTHERS VARCHAR(250) NOT NULL,
  VERSION NUMBER(38,0),
  CONSTRAINT PAFFINITIES PRIMARY KEY (PRIMKEY) );

CREATE UNIQUE INDEX AFFINITIES_IDX1 ON AFFINITIES(OS, OSNAME, OSVER, OSARCH, STATIONS, HOSTNAME, OTHERS);
CREATE INDEX AFFINITIES_IDX2 ON AFFINITIES (OS);
CREATE INDEX AFFINITIES_IDX3 ON AFFINITIES (OS, OSNAME);
CREATE INDEX AFFINITIES_IDX4 ON AFFINITIES (OS, OSNAME, OSVER, OSARCH, HOSTNAME);

-------------------------------------------------------------------------------  

CREATE TABLE BulletinBoard (
  NoteID VARCHAR2(20) NOT NULL, 
  JobID VARCHAR2(20), 
  ScopeID VARCHAR2(20), 
  Body VARCHAR2(1000),
  PubDate NUMBER, 
  ScopeType NUMBER, 
  SrcCompName VARCHAR2(130), 
  SrcHost VARCHAR2(250), 
  SrcWIID VARCHAR2(20), 
  Topic VARCHAR2(100), 
  PRIMARY KEY (NoteID));
  
CREATE INDEX BB_IDX1 ON BulletinBoard(JobID);
CREATE INDEX BB_IDX2 ON BulletinBoard(ScopeID);
  
-------------------------------------------------------------------------------  
CREATE TABLE PARAMHISTORYBEAN
  (PRIMKEY VARCHAR2(20) NOT NULL,
   JOBID VARCHAR2(20),
   COMPPATH VARCHAR2(2000),
   PARAMHISTORYDATA BLOB,
   ROWINDEX NUMBER
   );
   

CREATE SEQUENCE pramhist_seq
START WITH     1
INCREMENT BY   1
NOCACHE
NOCYCLE;

CREATE OR REPLACE TRIGGER T_PARAMHIST_ROWINDEX
BEFORE INSERT 
ON PARAMHISTORYBEAN
REFERENCING NEW AS NEW
FOR EACH ROW
WHEN (new.ROWINDEX IS NULL OR new.ROWINDEX = 0)
BEGIN

  SELECT pramhist_seq.nextval
  INTO :new.ROWINDEX
  FROM dual;

END;
/
  
QUIT;
