Go

CREATE TABLE FIPERSYS (
  KEYNAME NVARCHAR(250) NOT NULL,
  VALUESTR NVARCHAR(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 BIGINT,
   VAR9 NVARCHAR(1000),
   VAR8 NVARCHAR(1000),
   VAR7 NVARCHAR(1000),
   VAR6 NVARCHAR(1000),
   VAR5 NVARCHAR(1000),
   VAR4 NVARCHAR(1000),
   VAR3 NVARCHAR(1000),
   VAR2 NVARCHAR(1000),
   VAR1 NVARCHAR(1000),
   VAR0 NVARCHAR(1000),
   NUMPARAMETERS INTEGER,
   COMPVER NVARCHAR(24),
   COMPNAME NVARCHAR(130),
   ISTRINGID INTEGER,
   CLASSNAME NVARCHAR(250),
   SEVERITY INTEGER,
   LOGDTTIME DATETIME2(6),
   CHAINEDEXCEPTION VARBINARY(MAX),
   WORKITEMID NVARCHAR(20),
   MSG NVARCHAR(1000),
   JOBID NVARCHAR(20),
   COMPPATH NVARCHAR(2000),
   SOURCE NVARCHAR(250) NOT NULL,
   PRIMKEY NVARCHAR(20) NOT NULL,
   VERSION INTEGER);

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

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

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

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 NVARCHAR(20) NOT NULL,   
  WORKITEMID NVARCHAR(20) NOT NULL,   
  REFCOMPID NVARCHAR(130) NOT NULL,   
  MODELXML VARBINARY(MAX),
  VERSIONSCOPE VARBINARY(MAX));

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

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

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

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 NVARCHAR(20) NOT NULL,
   COMPTYPE NVARCHAR(250),
   COMPVERSION INTEGER,
   COMPPATH NVARCHAR(2000),
   COMPMODELID INTEGER,
   JOBID NVARCHAR(20),
   WORKFLOWID NVARCHAR(20),
   STATUS INTEGER,
   DISPATCHTARGET NVARCHAR(250),
   CREATEDATE DATETIME2(6),
   DISPATCHDATE DATETIME2(6),
   ENDDATE DATETIME2(6),
   EXECUTEDURATION BIGINT,
   CC INTEGER,
   COMPONENTCRC BIGINT,
   TIMEOUT BIGINT,
   ITERATIONPATH NVARCHAR(2000), 
   DRMTYPE NVARCHAR(130), 
   DRMJOBID NVARCHAR(250), 
   RETRYCOUNT BIGINT,
   MAXRETRY BIGINT,
   COMPVERSTR NVARCHAR(24) NOT NULL,
   COMPMODELIDSTR NVARCHAR(130) NOT NULL,
   REFWIID NVARCHAR(20),
   APPROXWIID NVARCHAR(20),
   EVALTYPE INTEGER,
   LOOKUPREASONCODE INTEGER,
   APPROXORIGID NVARCHAR(15),
   WI_NUM_DB_BYTES BIGINT,
   WI_NUM_VALUES BIGINT,
   WI_NUM_DISK_BYTES BIGINT,
   SF_NUM_DB_BYTES BIGINT,
   SF_NUM_VALUES BIGINT,
   SF_NUM_DISK_BYTES BIGINT,
   AFFINITIES NVARCHAR(20),
   GROUPNAME NVARCHAR(50),
   COSIMGROUPID NVARCHAR(20),
   PENDINGREPORTED SMALLINT,
   NOSAVEDESCENDANTPARMS SMALLINT,
   VERSION BIGINT);

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 NVARCHAR(20) NOT NULL,
   PARENTWORKITEMID NVARCHAR(20),
   STATUS NVARCHAR(10) NOT NULL,
   CC INTEGER,
   ORIGNAME NVARCHAR(250),
   ORIGID NVARCHAR(200),
   JOBID NVARCHAR(20),
   EVALTYPE INTEGER,
   SF_NUM_DB_BYTES BIGINT,
   SF_NUM_VALUES BIGINT,
   SF_NUM_DISK_BYTES BIGINT,
   VERSION BIGINT);

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 NVARCHAR(20) NOT NULL,
   JOBID NVARCHAR(20),
   WORKCONTEXTBYTES VARBINARY(MAX),
   SYNCID BIGINT NOT NULL,
   VERSION BIGINT);

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

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

CREATE TABLE JOBMONITOR
  (DEST VARBINARY(MAX),
   JOBID NVARCHAR(20),
   PRIMKEY NVARCHAR(20) NOT NULL,
   VERSION BIGINT);

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

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

CREATE TABLE PARTNERPROFILEBEAN
  (LOCATIONURL NVARCHAR(250),
   CALLBACKURL NVARCHAR(250),
   DISPLAYNAME NVARCHAR(100),
   SOAPTYPE NVARCHAR(10),
   PRIMKEY NVARCHAR(20) NOT NULL,
   VERSION BIGINT);

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

-------------------------------------------------------------------------------  
  
CREATE TABLE SYNCPOINT (
  PRIMKEY NVARCHAR(20) NOT NULL,   
  WFID NVARCHAR(20),
  JOBID NVARCHAR(20),
  COMPPATH NVARCHAR(2000),
  BRANCHES INTEGER,
  TAKEN INTEGER,
  WAITERS INTEGER,
  VERSION BIGINT);

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 NVARCHAR(20),
  WORKFLOWID NVARCHAR(20) NOT NULL,
   GROUPNAME NVARCHAR(50) NOT NULL,
   STATIONNAME NVARCHAR(250),
   VERSION BIGINT);
   
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 NVARCHAR(20) NOT NULL, 
  LOCALID NVARCHAR(100), 
  LOCALMETA VARBINARY(MAX), 
  FOREIGNID NVARCHAR(100), 
  LINKREF NVARCHAR(20),
  VERSION BIGINT);

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 NVARCHAR(20) NOT NULL, 
  FNAME NVARCHAR(1024), 
  JOBID NVARCHAR(20), 
  WFID NVARCHAR(20), 
  WIID NVARCHAR(20),
  PERSIST SMALLINT, 
  VERSION BIGINT);

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 NVARCHAR(20) NOT NULL, 
  JOBID NVARCHAR(20), 
  COMPONENTID NVARCHAR(130), 
  APPROXID NVARCHAR(130), 
  APPROXOBJ VARBINARY(MAX),
  VERSION BIGINT);

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 NVARCHAR(20),
   ACCESSLEVEL NVARCHAR(250),
   ACCESSTYPE NVARCHAR(250),
   RESOURCEID NVARCHAR(250) NOT NULL,
   VERSION BIGINT);

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 NVARCHAR(250) NOT NULL,
   PRIORITY INTEGER NOT NULL,
   ACCESSID NVARCHAR(250),
   ACCESSLEVEL NVARCHAR(250),
   ACCESSTYPE NVARCHAR(250),
   VERSION BIGINT);

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 NVARCHAR(250) NOT NULL,
   HOSTNAME NVARCHAR(250),
   LOGINNAME NVARCHAR(250),
   PASSWORD NVARCHAR(250),
   PROTOCOL NVARCHAR(250),
   HOMEDIRECTORY NVARCHAR(250),
   VERSION BIGINT);

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

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

CREATE TABLE GROUPMASTER
  (GROUPNAME NVARCHAR(250) NOT NULL,
   GROUPDESC NVARCHAR(250),
   VERSION BIGINT);

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

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

CREATE TABLE GROUPUSERMAP
  (GROUPNAME NVARCHAR(250) NOT NULL,
   ACCESSID NVARCHAR(250) NOT NULL,
   ACCESSLEVEL NVARCHAR(250) NOT NULL,
   VERSION BIGINT);

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 NVARCHAR(20) NOT NULL,
   PACKAGENAME NVARCHAR(250),
   PARENTID NVARCHAR(20),
   VERSION BIGINT);

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 NVARCHAR(20) NOT NULL,
   OBJECTNAME NVARCHAR(250),
   OBJECTTYPE INTEGER,
   CHECKOUTFLAG NVARCHAR(250),
   CHECKOUTBY NVARCHAR(250),
   CHECKOUTDATE DATE,
   PACKAGENAME NVARCHAR(250),
   PARENTPACKAGEID NVARCHAR(20),
   DESCRIPTION NVARCHAR(2000),
   OBJECTSUBTYPE NVARCHAR(250),
   OBJECTPRIVILEGES INTEGER,
   VERSION BIGINT);

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 NVARCHAR(20) NOT NULL,
   OBJECTVERSION NVARCHAR(24) NOT NULL,
   ATTRIBUTENAME NVARCHAR(250) NOT NULL,
   ATTRIBUTEVALUE NVARCHAR(250) NOT NULL,
   OBJECTVERSIONID NVARCHAR(250),
   VERSION BIGINT);

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 NVARCHAR(20) NOT NULL,
   OBJECTID NVARCHAR(20),
   OBJECTVERSION NVARCHAR(24),
   OBJECTREFERENCE NVARCHAR(250),
   REFERENCEVERSIONID NVARCHAR(24),
   OBJECTVERSIONID NVARCHAR(250),
   VERSION BIGINT);

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

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

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

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

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

CREATE TABLE VERSIONCONTROLIMPLEMENTATION
  (PRIMKEY NVARCHAR(20) NOT NULL,
   HIERARCHY NVARCHAR(250) NOT NULL,
   VERSIONCONTROLLER NVARCHAR(250),
   CONFIG NVARCHAR(1000),
   IMPLEMENTATIONCLASS NVARCHAR(250),
   VERSION BIGINT);

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

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

CREATE TABLE VERSIONCONTROLMASTER
  (OBJECTID NVARCHAR(20) NOT NULL,
   VERSIONID INTEGER NOT NULL,
   INTERNALFILENAME NVARCHAR(250),
   CHECKOUTFLAG NVARCHAR(250),
   HIERARCHY NVARCHAR(250),
   VERSION BIGINT);

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

CREATE TABLE DBVERSIONCONTROLLER
  (PRIMKEY NVARCHAR(20) NOT NULL,
   FILECONTENT VARBINARY(MAX),
   VERSION BIGINT);

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

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

CREATE TABLE TCBASICSEARCHPARMS
  (PRIMARYKEY NVARCHAR(250) NOT NULL,
   TEXT NVARCHAR(250),
   OPERATOR NVARCHAR(250),
   USENAMES SMALLINT NOT NULL,
   USEDESCRIPTIONS SMALLINT NOT NULL,
   USEAUTHORS SMALLINT NOT NULL,
   VERSION BIGINT);

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

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

CREATE TABLE TCMODEL
  (PRIMARYKEY NVARCHAR(250) NOT NULL,
   USERID NVARCHAR(250),
   NAME NVARCHAR(250),
   VERSION1 NVARCHAR(24),
   MODELDATA VARBINARY(MAX),
   VERSION BIGINT);

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

CREATE TABLE TCPREFERENCES
  (PRIMARYKEY NVARCHAR(250) NOT NULL,
   USERID NVARCHAR(250) NULL,
   CURRENTMODELNAME NVARCHAR(250),
   CURRENTMODELVERSION NVARCHAR(24),
   CURRENTVIEW NVARCHAR(250),
   IPADDR NVARCHAR(32),
   LASTACCESS BIGINT,
   VIEWDATA NVARCHAR(250),
   RECOVERING INTEGER,
   VERSION BIGINT,
   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 NVARCHAR(20) NOT NULL, 
	 PRIVATEKEY VARBINARY(MAX) NOT NULL, 
	 PUBLICKEY VARBINARY(MAX) NOT NULL, 
	 CREATEDTIME BIGINT,
	 VERSION BIGINT);
	 
ALTER TABLE ACSPKIDESCRIPTOR ADD CONSTRAINT ACSPKIDESCRIPTORPK PRIMARY KEY (PRIMKEY);

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

CREATE TABLE JOBOWNER 
	(JOBID NVARCHAR(20) NOT NULL,
	 JOBOWNERNAME NVARCHAR(255),
	 JOBOWNERMETADATA VARBINARY(MAX) NOT NULL,
	 JOBOWNERDOMAIN NVARCHAR(255),
   JOBOWNERWINNAME NVARCHAR(255),
   JOBOWNERWINMETA VARBINARY(MAX),
   JOBOWNERUNIXNAME NVARCHAR(255),
   JOBOWNERUNIXMETA VARBINARY(MAX),  
	 VERSION BIGINT);
	 
ALTER TABLE JOBOWNER ADD CONSTRAINT PK_JOBOWNER PRIMARY KEY (JOBID);

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

CREATE TABLE RESULTREQUESTREGISTRY
   (PRIMARYKEY NVARCHAR(20) NOT NULL,
   JOBID NVARCHAR(20) NOT NULL,
   COMPONENTPATH NVARCHAR(2000) NOT NULL,
   RESULTREQUEST VARBINARY(MAX) NOT NULL,
   JMSTOPIC VARBINARY(MAX) NOT NULL,
   VERSION BIGINT);

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

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

CREATE TABLE DATA_TYPES(
    DATA_TYPE_ABBREV NVARCHAR(4) NOT NULL,
    METAMODEL_TYPE NVARCHAR(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 NVARCHAR(20) NOT NULL,
    MODEL_ID NVARCHAR(130) NOT NULL,
    MODEL_NAME NVARCHAR(130) NOT NULL,
    SCHEMA_VERSION INTEGER NOT NULL,
    BASE_TABLE_NAME NVARCHAR(10) NOT NULL,
    CONSTRAINT PMASTERMODEL PRIMARY KEY (MODEL_KEY));

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

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

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

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

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

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

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

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

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

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

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

-------------------------------------------------------------------------------  
CREATE TABLE AFFINITIES (
  PRIMKEY NVARCHAR(20) NOT NULL,
  OS NVARCHAR(50) NOT NULL,
  OSNAME NVARCHAR(50) NOT NULL,
  OSVER NVARCHAR(50) NOT NULL,
  OSARCH NVARCHAR(50) NOT NULL,
  STATIONS NVARCHAR(100) NOT NULL,
  HOSTNAME NVARCHAR(250) NOT NULL,
  OTHERS NVARCHAR(250) NOT NULL,
  VERSION BIGINT,
  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 NVARCHAR(20) NOT NULL, 
  JobID NVARCHAR(20), 
  ScopeID NVARCHAR(20), 
  Body NVARCHAR(1000),
  PubDate BIGINT, 
  ScopeType BIGINT, 
  SrcCompName NVARCHAR(130), 
  SrcHost NVARCHAR(250), 
  SrcWIID NVARCHAR(20), 
  Topic NVARCHAR(100), 
  PRIMARY KEY (NoteID));
  
CREATE INDEX BB_IDX1 ON BulletinBoard(JobID);
CREATE INDEX BB_IDX2 ON BulletinBoard(ScopeID);
  
-------------------------------------------------------------------------------  
CREATE TABLE PARAMHISTORYBEAN
  (PRIMKEY NVARCHAR(20) NOT NULL,
   JOBID NVARCHAR(20),
   COMPPATH NVARCHAR(2000),
   PARAMHISTORYDATA VARBINARY(MAX),
   ROWINDEX BIGINT
   );
   

CREATE SEQUENCE pramhist_seq
START WITH     1
INCREMENT BY   1
NO CACHE
NO CYCLE;

GO
CREATE TRIGGER T_PARAMHIST_ROWINDEX
   ON PARAMHISTORYBEAN 
   AFTER INSERT
AS

    DECLARE T_PARAMHIST_ROWINDEX_Cursor CURSOR FOR

    SELECT ROWINDEX FROM Inserted

    DECLARE @Data BIGINT

    OPEN T_PARAMHIST_ROWINDEX_Cursor;

    FETCH NEXT FROM T_PARAMHIST_ROWINDEX_Cursor INTO @Data

    WHILE @@FETCH_STATUS = 0

    BEGIN

		UPDATE PARAMHISTORYBEAN SET ROWINDEX = (NEXT VALUE FOR pramhist_seq ) WHERE @Data = 0 OR @Data is NULL;
    
        FETCH NEXT FROM T_PARAMHIST_ROWINDEX_Cursor INTO @Data

    END;

    CLOSE T_PARAMHIST_ROWINDEX_Cursor;

    DEALLOCATE T_PARAMHIST_ROWINDEX_Cursor;
    
    Go


