/*
Company: OptimaJet
Project: WorkflowServer Provider for Oracle
Version: 2.6
File: update_wfs_2_6.sql
*/

ALTER TABLE WORKFLOWPROCESSINSTANCES ADD
(
	RUNTIMEID NVARCHAR2(900) NULL,
    SETTIME DATE NULL
);
/

declare
index_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(index_not_exists, -1418);
begin
    execute immediate 'DROP INDEX IDX_WORKFLOWPROCESSINSTANCES_SR';
exception
    when index_not_exists then null;
end;
/

CREATE INDEX IDX_WORKFLOWPROCESSINSTANCES_SR ON WORKFLOWPROCESSINSTANCES (STATUS, RUNTIMEID)
LOGGING;

UPDATE WORKFLOWPROCESSINSTANCES SET SETTIME = CURRENT_DATE, RUNTIMEID = '00000000-0000-0000-0000-000000000000';

ALTER TABLE WORKFLOWPROCESSINSTANCES MODIFY SETTIME DATE NOT NULL;

ALTER TABLE WORKFLOWPROCESSINSTANCES MODIFY RUNTIMEID  NVARCHAR2(900) NOT NULL;

CREATE TABLE WORKFLOWRUNTIME (
  RUNTIMEID NVARCHAR2(900),
  STATUS NUMBER(3) NOT NULL,
  RESTORERID NVARCHAR2(900),
  LOCKFLAG RAW(16) NOT NULL,
  NEXTTIMERTIME TIMESTAMP NULL,
  NEXTSERVICETIMERTIME TIMESTAMP NULL,
  LASTALIVESIGNAL TIMESTAMP NULL,
  CONSTRAINT PK_WORKFLOWRUNTIME PRIMARY KEY (RUNTIMEID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED ))
LOGGING;

INSERT INTO WORKFLOWRUNTIME (RUNTIMEID,LOCKFLAG,STATUS) VALUES ('00000000-0000-0000-0000-000000000000',sys_guid(),100);


CREATE TABLE WORKFLOWSYNC (
  NAME NVARCHAR2(900),
  LOCKFLAG RAW(16) NOT NULL,
  CONSTRAINT PK_WORKFLOWSYNC PRIMARY KEY (NAME) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED ))
LOGGING;

INSERT INTO WORKFLOWSYNC (Name, LOCKFLAG) VALUES ('Timer', sys_guid());
INSERT INTO WORKFLOWSYNC (Name, LOCKFLAG) VALUES ('ServiceTimer', sys_guid());

ALTER TABLE WORKFLOWPROCESSTIMER ADD  ROOTPROCESSID RAW(16) NULL;

UPDATE WORKFLOWPROCESSTIMER SET ROOTPROCESSID = (SELECT ROOTPROCESSID FROM WORKFLOWPROCESSINSTANCE wpi WHERE wpi.ID = PROCESSID);

ALTER TABLE WORKFLOWPROCESSTIMER MODIFY ROOTPROCESSID RAW(16) NOT NULL;

CREATE TABLE WORKFLOWAPPROVALHISTORY (
ID RAW(16),
PROCESSID RAW(16) NOT NULL,
IDENTITYID NVARCHAR2(256),
ALLOWEDTO CLOB NULL,
TRANSITIONTIME DATE NULL,
SORT NUMBER(19) NULL,
INITIALSTATE NVARCHAR2(1024) NOT NULL,
DESTINATIONSTATE NVARCHAR2(1024) NOT NULL,
TRIGGERNAME NVARCHAR2(1024) NULL,
COMMENTARY CLOB NULL,
CONSTRAINT PK_WORKFLOWAPPROVALHISTORY PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED )
)
LOGGING;

CREATE INDEX IDX_WORKFLOWAPPROVALHISTORY_PID ON WORKFLOWAPPROVALHISTORY (PROCESSID)
LOGGING;

declare
index_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(index_not_exists, -1418);
begin

    execute immediate 'DROP INDEX IDX_WORKFLOWPROCESSINSTANCE_ROOT';
exception
    when index_not_exists then null;
end;
/

CREATE INDEX IDX_WORKFLOWPROCESSINSTANCE_ROOT ON WORKFLOWPROCESSINSTANCE (ROOTPROCESSID)
LOGGING;

ALTER TABLE WORKFLOWPROCESSINSTANCE ADD
(
	STARTINGTRANSITION CLOB NULL
);

UPDATE WORKFLOWPROCESSINSTANCE SET STARTINGTRANSITION = (SELECT STARTINGTRANSITION FROM WORKFLOWPROCESSSCHEME wps WHERE wps.ID = SCHEMEID);

ALTER TABLE WORKFLOWPROCESSTIMER MODIFY NEXTEXECUTIONDATETIME TIMESTAMP;

ALTER TABLE WORKFLOWSCHEME ADD AUTOSTART CHAR(1 BYTE) DEFAULT 0 NOT NULL;

ALTER TABLE WORKFLOWSCHEME ADD DEFAULTFORM NVARCHAR2(1024) NULL;

CREATE TABLE WORKFLOWSERVERLOGS (
  ID RAW(16),
  MESSAGE NCLOB NOT NULL,
  MESSAGETEMPLATE NCLOB NOT NULL,
  TIMESTAMP DATE NOT NULL,
  EXCEPTION NCLOB NULL,
  PROPERTIESJSON NCLOB NULL,
  LOGLEVEL NUMBER(3) NOT NULL,
  RUNTIMEID NVARCHAR2(900) NOT NULL,
  CONSTRAINT PK_WORKFLOWSERVERLOGS PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED ))
LOGGING;
CREATE INDEX IDX_WORKFLOWSERVERLOGS_TIMESTAMP ON WORKFLOWSERVERLOGS (TIMESTAMP)
LOGGING;
CREATE INDEX IDX_WORKFLOWSERVERLOGS_LEVEL ON WORKFLOWSERVERLOGS (LOGLEVEL)
LOGGING;
CREATE INDEX IDX_RUNTIMEID_LEVEL ON WORKFLOWSERVERLOGS (RUNTIMEID)
LOGGING;

COMMIT;