DatabasePatches

From RunaWFE
Revision as of 01:07, 25 May 2016 by doc>Dofs
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Database versioning

RunaWFE Free Workflow System (BPMS) Version 4.5.0

© 2003 - 2015, Consulting Group Runa

© 2015 - 2024, "Process Technologies" Ltd, this document is available under GNU FDL license. RunaWFE Free is an open source system distributed under a LGPL license (http://www.gnu.org/licenses/lgpl.html).

# Introduction

Patches are applied only due to system update. In initial installation DDL created from JPA annotations.

During system development changes in database structure occured sometimes. For these cases database patches are used. Patches are applied at WFE Server startup based on database version (can be found in WFE_CONSTANTS).

To add new patch you should do:

  1. Write java patch class implementing ru.runa.wfe.commons.dbpatch.DBPatch. In applyPatch method there are allowed DML instructions and in getDDLQueriesBefore, getDDLQueriesAfter you can return DDL SQL.
  2. Register patch in ru.runa.wfe.commons.logic.InitializerLogic.

# Change history

You can see current version by SQL query.

SELECT VALUE FROM WFE_CONSTANTS WHERE NAME='ru.runa.database_version'
# description
Version 4.0.0
21 AddHierarchyProcess

Subprocess hierarcy info added

Added column BPM_PROCESS.TREE_PATH
22 JbpmRefactoringPatch

DB structure conversion from JBPM_* table to BPM_* tables. Patch is available for MSSQL only.

Version 3 process data conversion
23 TransitionLogPatch

Version 3 process data conversion

Version 4.0.1
24 PerformancePatch401

Reorganized indexes in tables PERMISSION_MAPPING, BPM_VARIABLE

25 TaskEndDateRemovalPatch

Before this patch completed tasks were remain in table BPM_TASK. Now completed tasks are removed on completion.

Removed column BPM_TASK.END_DATE
Version 4.0.3
26 PermissionMappingPatch403

Reorganized indexes in table PERMISSION_MAPPING 

DROP INDEX IX_PERMISSION_BY_IDENTIFIABLE ON PERMISSION_MAPPING
ALTER TABLE PERMISSION_MAPPING ADD CONSTRAINT UQ_MAPPINGS UNIQUE (IDENTIFIABLE_ID, TYPE_ID, MASK, EXECUTOR_ID)
Version 4.0.4
27 NodeTypeChangePatch Changed data in columns BPM_TOKEN.NODE_TYPE, BPM_LOG.CONTENT
Version 4.0.5
28 ExpandDescriptionsPatch

Maximal fields length increased to 1024 symbols 

ALTER TABLE BPM_TASK ALTER COLUMN DESCRIPTION varchar(1024)
ALTER TABLE BPM_PROCESS_DEFINITION ALTER COLUMN DESCRIPTION varchar(1024)
ALTER TABLE EXECUTOR_RELATION ALTER COLUMN DESCRIPTION varchar(1024)
ALTER TABLE EXECUTOR ALTER COLUMN DESCRIPTION varchar(1024)
Version 4.0.6
29 TaskOpenedByExecutorsPatch

Instead of single flag IS_READ adds ability to mark task as read for individual user 

CREATE TABLE BPM_TASK_OPENED (TASK_ID numeric(19,0) NOT NULL, EXECUTOR_ID numeric(19,0) NOT NULL)
ALTER TABLE BPM_TASK_OPENED ADD CONSTRAINT FK_TASK_OPENED_TASK FOREIGN KEY (TASK_ID) REFERENCES BPM_TASK (ID)
ALTER TABLE BPM_TASK DROP COLUMN FIRST_OPEN
Version 4.1.0
30 AddNodeIdToProcessLogPatch

Adds binding log record with process definition node by node id 

ALTER TABLE BPM_LOG ADD NODE_ID varchar(255)
31 AddSubProcessIndexColumn

Adds ability to execute multisubprocesses in cycles in same token 

ALTER TABLE BPM_SUBPROCESS ADD SUBPROCESS_INDEX int
Version 4.1.1
32 AddCreateDateColumns

Adds column CREATE_DATE for audit purposes

After database update you should restart RunaWFE Server.

 Queries for MSSQL: 
sp_rename 'BPM_LOG.LOG_DATE', 'CREATE_DATE', 'COLUMN'
sp_rename 'BPM_PROCESS_DEFINITION.DEPLOYED', 'CREATE_DATE', 'COLUMN'
sp_rename 'SYSTEM_LOG.TIME', 'CREATE_DATE', 'COLUMN'
ALTER TABLE BATCH_PRESENTATION ADD CREATE_DATE datetime
ALTER TABLE BOT ADD CREATE_DATE datetime
ALTER TABLE BOT_STATION ADD CREATE_DATE datetime
ALTER TABLE BOT_TASK ADD CREATE_DATE datetime
ALTER TABLE BPM_JOB ADD CREATE_DATE datetime
ALTER TABLE BPM_SUBPROCESS ADD CREATE_DATE datetime
ALTER TABLE BPM_SWIMLANE ADD CREATE_DATE datetime
ALTER TABLE BPM_VARIABLE ADD CREATE_DATE datetime
ALTER TABLE EXECUTOR ADD CREATE_DATE datetime
ALTER TABLE EXECUTOR_GROUP_MEMBER ADD CREATE_DATE datetime
ALTER TABLE EXECUTOR_RELATION ADD CREATE_DATE datetime
ALTER TABLE EXECUTOR_RELATION_PAIR ADD CREATE_DATE datetime
ALTER TABLE LOCALIZATION ADD CREATE_DATE datetime
ALTER TABLE PROFILE ADD CREATE_DATE datetime
ALTER TABLE SUBSTITUTION ADD CREATE_DATE datetime
ALTER TABLE SUBSTITUTION_CRITERIA ADD CREATE_DATE datetime

For each table: 

UPDATE ${TABLE_NAME} SET CREATE_DATE=:createDate WHERE CREATE_DATE IS NULL
Version 4.2.0
33 AddEmbeddedFileForBotTask

Adds column for binary file for bot task

Queries for MSSQL: 
ALTER TABLE BOT_TASK ADD EMBEDDED_FILE image
34 AddColumnForEmbeddedBotTaskFileName

Adds column for file name in bot task 

ALTER TABLE BOT_TASK ADD EMBEDDED_FILE_NAME varchar(1024)
35 AddSettingsTable

Adds settings table 

CREATE TABLE BPM_SETTING (ID NUMBER NOT NULL, FILE_NAME VARCHAR(1024) NOT NULL, NAME VARCHAR(1024) NOT NULL, VALUE VARCHAR(1024));
CREATE SEQUENCE SEQ_BPM_SETTING;
36 AddSequentialFlagToBot

Adds sequentical flag to bot and bot task 

ALTER TABLE BOT ADD IS_SEQUENTIAL NUMBER;
ALTER TABLE BOT_TASK ADD IS_SEQUENTIAL NUMBER;
UPDATE BOT SET IS_SEQUENTIAL=0 WHERE IS_SEQUENTIAL IS NULL;
UPDATE BOT_TASK SET IS_SEQUENTIAL=0 WHERE IS_SEQUENTIAL IS NULL;
COMMIT;
37 CreateAggregatedLogsTables

Adds extended audit tables

Queries for Oracle: 
CREATE SEQUENCE SEQ_BPM_AGGLOG_ASSIGNMENTS;
CREATE SEQUENCE SEQ_BPM_AGGLOG_PROCESS;
CREATE SEQUENCE SEQ_BPM_AGGLOG_TASKS;
CREATE TABLE BPM_AGGLOG_ASSIGNMENTS (ID NUMBER(19, 0) NOT NULL, DISCRIMINATOR CHAR(1 BYTE) NOT NULL, ASSIGNMENT_OBJECT_ID NUMBER(19, 0) NOT NULL, IDX NUMBER(10, 0) NOT NULL, ASSIGNMENT_DATE DATE NOT NULL, OLD_EXECUTOR_NAME VARCHAR2(1024 BYTE), NEW_EXECUTOR_NAME VARCHAR2(1024 BYTE), CONSTRAINT SYS_C0012885 PRIMARY KEY (ID) USING INDEX (CREATE UNIQUE INDEX SYS_C0012885 ON BPM_AGGLOG_ASSIGNMENTS (ID ASC)) ENABLE);
CREATE INDEX IX_AGGLOG_ASSIGN_DATE ON BPM_AGGLOG_ASSIGNMENTS (ASSIGNMENT_DATE ASC);
CREATE INDEX IX_AGGLOG_ASSIGN_EXECUTOR ON BPM_AGGLOG_ASSIGNMENTS (NEW_EXECUTOR_NAME ASC);
CREATE INDEX IX_AGGLOG_ASSIGN_OBJECT ON BPM_AGGLOG_ASSIGNMENTS (ASSIGNMENT_OBJECT_ID ASC, IDX ASC);
CREATE TABLE BPM_AGGLOG_PROCESS (ID NUMBER(19, 0) NOT NULL, PROCESS_ID NUMBER(19, 0) NOT NULL, PARENT_PROCESS_ID NUMBER(19, 0), START_ACTOR_NAME VARCHAR2(1024 BYTE), CANCEL_ACTOR_NAME VARCHAR2(1024 BYTE), CREATE_DATE DATE NOT NULL, END_DATE DATE, END_REASON NUMBER(10, 0) NOT NULL, CONSTRAINT SYS_C0012897 PRIMARY KEY (ID) USING INDEX (CREATE UNIQUE INDEX SYS_C0012897 ON BPM_AGGLOG_PROCESS (ID ASC)) ENABLE);
CREATE INDEX IX_AGGLOG_PROCESS_CREATE_DATE ON BPM_AGGLOG_PROCESS (CREATE_DATE ASC);
CREATE INDEX IX_AGGLOG_PROCESS_END_DATE ON BPM_AGGLOG_PROCESS (END_DATE ASC);
CREATE INDEX IX_AGGLOG_PROCESS_INSTANCE ON BPM_AGGLOG_PROCESS (PROCESS_ID ASC);
CREATE TABLE BPM_AGGLOG_TASKS (ID NUMBER(19, 0) NOT NULL, TASK_ID NUMBER(19, 0) NOT NULL, PROCESS_ID NUMBER(19, 0) NOT NULL, INITIAL_ACTOR_NAME VARCHAR2(1024 BYTE), COMPLETE_ACTOR_NAME VARCHAR2(1024 BYTE), CREATE_DATE DATE NOT NULL, DEADLINE_DATE DATE, END_DATE DATE, END_REASON NUMBER(10, 0) NOT NULL, TOKEN_ID NUMBER(19, 0) NOT NULL, NODE_ID VARCHAR2(1024 BYTE), TASK_NAME VARCHAR2(1024 BYTE), SWIMLANE_NAME VARCHAR2(1024 BYTE), CONSTRAINT SYS_C0012892 PRIMARY KEY (ID) USING INDEX (CREATE UNIQUE INDEX SYS_C0012892 ON BPM_AGGLOG_TASKS (ID ASC)) ENABLE);
CREATE INDEX IX_AGGLOG_TASKS_CREATE_DATE ON BPM_AGGLOG_TASKS (CREATE_DATE ASC);
CREATE INDEX IX_AGGLOG_TASKS_END_DATE ON BPM_AGGLOG_TASKS (END_DATE ASC);
CREATE INDEX IX_AGGLOG_TASKS_PROCESS ON BPM_AGGLOG_TASKS (PROCESS_ID ASC);
38 TaskCreateLogSeverityChangedPatch

Changes priority Task create operation in process logs from DEBUG to INFO 

UPDATE BPM_LOG SET SEVERITY='INFO' WHERE DISCRIMINATOR='1';
COMMIT;
Version 4.2.1
Retrieved from "https://runawfe.org/index.php?title=DatabasePatches&oldid=842"