update_wfs_2_9.sql

/*
Company: OptimaJet
Project: WorkflowServer Provider for Oracle
Version: 2.9
File: update_wfs_2_9.sql
*/

ALTER TABLE WORKFLOWPROCESSTRANSITIONH ADD
(
    ACTORNAME NVARCHAR2(256),
    EXECUTORNAME NVARCHAR2(256)
);

CREATE TABLE WORKFLOWPROCESSASSIGNMENT (
    ID RAW(16),
    ASSIGNMENTCODE VARCHAR2(256 BYTE) NOT NULL,
    PROCESSID RAW(16) NOT NULL,
    NAME VARCHAR2(256 BYTE) NOT NULL,
    DESCRIPTION VARCHAR2(256 BYTE),
    STATUSSTATE VARCHAR2(256 BYTE) NOT NULL,
    DATECREATION TIMESTAMP NOT NULL,
    DATESTART TIMESTAMP,
    DATEFINISH TIMESTAMP,
    DEADLINETOSTART TIMESTAMP,
    DEADLINETOCOMPLETE TIMESTAMP,
    EXECUTOR VARCHAR2(256 BYTE) NOT NULL,
    OBSERVERS VARCHAR2(256 BYTE),
    TAGS VARCHAR2(256 BYTE),
    ISACTIVE CHAR(1 BYTE) DEFAULT 0 NOT NULL,
    ISDELETED CHAR(1 BYTE) DEFAULT 0 NOT NULL,
    CONSTRAINT PK_WORKFLOWPROCESSASSIGNMENT PRIMARY KEY (ID) USING INDEX STORAGE ( INITIAL 64K NEXT 1M MAXEXTENTS UNLIMITED )
)                       
LOGGING;

CREATE INDEX IDX_WORKFLOWPROCESSASSIGNMENT_PROCESSID ON WORKFLOWPROCESSASSIGNMENT (PROCESSID)
LOGGING;
CREATE INDEX IDX_WORKFLOWPROCESSASSIGNMENT_ASSIGNMENTCODE ON WORKFLOWPROCESSASSIGNMENT (ASSIGNMENTCODE)
LOGGING;
CREATE INDEX IDX_WORKFLOWPROCESSASSIGNMENT_EXECUTOR ON WORKFLOWPROCESSASSIGNMENT (EXECUTOR)
LOGGING;
CREATE INDEX IDX_WORKFLOWPROCESSASSIGNMENT_PE ON WORKFLOWPROCESSASSIGNMENT (PROCESSID, EXECUTOR)
LOGGING;

CREATE OR REPLACE FUNCTION DropUnusedWorkflowProcessScheme
    RETURN NUMBER
    IS
    st NUMBER := 0;
BEGIN
    DELETE FROM WorkflowProcessScheme
    WHERE WorkflowProcessScheme.IsObsolete = 1
      AND NOT EXISTS (SELECT * FROM WorkflowProcessInstance  WHERE WorkflowProcessInstance.SchemeId = WorkflowProcessScheme.Id );

    SELECT COUNT(*) into st
    FROM WorkflowProcessInstance LEFT OUTER JOIN WorkflowProcessScheme ON WorkflowProcessInstance.SchemeId = WorkflowProcessScheme.Id
    WHERE WorkflowProcessScheme.Id IS NULL;

    RETURN st;
END;

COMMIT;