database schema ugrade from activiti 5.11

[peter.hac...@googlemail.com]

Hello,

I have just compiled an sql script for upgrading our postgres DB from Activiti 5.11 to Camunda BPM 7.0.0. For this purpose I compared the different versions of the create scripts and had a look at the upgrade scripts that you provide. It would be nice to get some feedback from you on some points that are not 100% clear:

- the line

create index ACT_IDX_HI_ACT_INST_COMP on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_, END_TIME_, ID_);

appears in your upgrade script, but not in your create script. Did you just forget it in the create script?

- I have the following lines in my script, which drop tables/columns:

alter table ACT_RE_DEPLOYMENT drop CATEGORY_;
drop table ACT_RE_MODEL;
alter table ACT_RU_JOB drop PROC_DEF_ID_;

You do not have those lines in your script, but you also don't have the tables/columns that I drop. Is it safe to just drop them? Is the DEPLOYMENT_ID_ column of the ACT_RU_JOB table a renaming of the PROCE_DEF_ID_ column or does it hold something completely different?

Kind regards,
Peter

[peter.hac...@googlemail.com]

BTW, here is the complete script. If you have the time and leisure you might have a look and see whether I did something stupid. But I would be completely happy if you just answer the original question of this task.

Thanks, Peter

alter table act_hi_procinst drop constraint act_hi_procinst_proc_def_id__business_key__key;

create index ACT_IDX_HI_ACT_INST_COMP on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_, END_TIME_, ID_);

alter table ACT_RE_DEPLOYMENT drop CATEGORY_;
drop table ACT_RE_MODEL;

alter table ACT_RU_EXECUTION add ACT_INST_ID_ varchar(64);
alter table ACT_RU_EXECUTION drop constraint act_ru_execution_proc_def_id__business_key__key;

/** populate ACT_INST_ID_ from history */
/** get from history for active activity instances */
UPDATE
ACT_RU_EXECUTION E
SET
ACT_INST_ID_ = (
SELECT
MAX(ID_)
FROM
ACT_HI_ACTINST HAI
WHERE
HAI.EXECUTION_ID_ = E.ID_
AND
END_TIME_ is null
)
WHERE
E.ACT_INST_ID_ is null
AND
E.ACT_ID_ is not null;

/** set act_inst_id for inactive parents of scope executions */
UPDATE
ACT_RU_EXECUTION E
SET
ACT_INST_ID_ = (
SELECT
MIN(HAI.ID_)
FROM
ACT_HI_ACTINST HAI
INNER JOIN
ACT_RU_EXECUTION SCOPE
ON
HAI.EXECUTION_ID_ = SCOPE.ID_
AND
SCOPE.PARENT_ID_ = E.ID_
AND
SCOPE.IS_SCOPE_ = true
WHERE
HAI.END_TIME_ is null
AND
NOT EXISTS (
SELECT
ACT_INST_ID_
FROM
ACT_RU_EXECUTION CHILD
WHERE
CHILD.ACT_INST_ID_ = HAI.ID_
AND
E.ACT_ID_ is not null
)
)
WHERE
E.ACT_INST_ID_ is null;

/** remaining executions get id from parent */
UPDATE
ACT_RU_EXECUTION E
SET
ACT_INST_ID_ = (
SELECT
ACT_INST_ID_ FROM ACT_RU_EXECUTION PARENT
WHERE
PARENT.ID_ = E.PARENT_ID_
AND
PARENT.ACT_ID_ = E.ACT_ID_
)
WHERE
E.ACT_INST_ID_ is null;
/**AND
not exists (
SELECT
ID_
FROM
ACT_RU_EXECUTION CHILD
WHERE
CHILD.PARENT_ID_ = E.ID_
);*/

/** remaining executions use execution id as activity instance id */
UPDATE
ACT_RU_EXECUTION E
SET
ACT_INST_ID_ = E.ID_
WHERE
E.ACT_INST_ID_ is null;

/** mark MI-scope executions in temporary column */
alter table ACT_RU_EXECUTION
add IS_MI_SCOPE_ boolean;


UPDATE
ACT_RU_EXECUTION MI_SCOPE
SET
IS_MI_SCOPE_ = true
WHERE
MI_SCOPE.IS_SCOPE_ = true
AND
MI_SCOPE.ACT_ID_ is not null
AND EXISTS (
SELECT
ID_
FROM
ACT_RU_EXECUTION MI_CONCUR
WHERE
MI_CONCUR.PARENT_ID_ = MI_SCOPE.ID_
AND
MI_CONCUR.IS_SCOPE_ = false
AND
MI_CONCUR.IS_CONCURRENT_ = true
AND
MI_CONCUR.ACT_ID_ = MI_SCOPE.ACT_ID_
);

/** set IS_ACTIVE to false for MI-Scopes: */
UPDATE
ACT_RU_EXECUTION MI_SCOPE
SET
IS_ACTIVE_ = false
WHERE
MI_SCOPE.IS_MI_SCOPE_ = true;

/** set correct root for mi-parallel:
CASE 1: process instance (use ID_) */
UPDATE
ACT_RU_EXECUTION MI_ROOT
SET
ACT_INST_ID_ = MI_ROOT.ID_
WHERE
MI_ROOT.ID_ = MI_ROOT.PROC_INST_ID_
AND EXISTS (
SELECT
ID_
FROM
ACT_RU_EXECUTION MI_SCOPE
WHERE
MI_SCOPE.PARENT_ID_ = MI_ROOT.ID_
AND
MI_SCOPE.IS_MI_SCOPE_ = true
);

/**
CASE 2: scopes below process instance (use ACT_INST_ID_ from parent) */
UPDATE
ACT_RU_EXECUTION MI_ROOT
SET
ACT_INST_ID_ = (
SELECT
ACT_INST_ID_
FROM
ACT_RU_EXECUTION PARENT
WHERE
PARENT.ID_ = MI_ROOT.PARENT_ID_
)
WHERE
MI_ROOT.ID_ != MI_ROOT.PROC_INST_ID_
AND EXISTS (
SELECT
ID_
FROM
ACT_RU_EXECUTION MI_SCOPE
WHERE
MI_SCOPE.PARENT_ID_ = MI_ROOT.ID_
AND
MI_SCOPE.IS_MI_SCOPE_ = true
);

alter table ACT_RU_EXECUTION
drop IS_MI_SCOPE_;

alter table ACT_RU_JOB drop PROC_DEF_ID_;

alter table ACT_RU_JOB add DEPLOYMENT_ID_ varchar(64);

alter table ACT_RE_PROCDEF drop DESCRIPTION_;

create table ACT_RU_INCIDENT (
ID_ varchar(64) not null,
INCIDENT_TIMESTAMP_ timestamp not null,
INCIDENT_MSG_ varchar(4000),
INCIDENT_TYPE_ varchar(255) not null,
EXECUTION_ID_ varchar(64),
ACTIVITY_ID_ varchar(255),
PROC_INST_ID_ varchar(64),
PROC_DEF_ID_ varchar(64),
CAUSE_INCIDENT_ID_ varchar(64),
ROOT_CAUSE_INCIDENT_ID_ varchar(64),
CONFIGURATION_ varchar(255),
primary key (ID_)
);

create table ACT_RU_AUTHORIZATION (
ID_ varchar(64) not null,
REV_ integer not null,
TYPE_ integer not null,
GROUP_ID_ varchar(255),
USER_ID_ varchar(255),
RESOURCE_TYPE_ integer not null,
RESOURCE_ID_ varchar(64),
PERMS_ integer,
primary key (ID_)
);

create index ACT_IDX_INC_CONFIGURATION on ACT_RU_INCIDENT(CONFIGURATION_);

alter table ACT_RU_INCIDENT
add constraint ACT_FK_INC_EXE
foreign key (EXECUTION_ID_)
references ACT_RU_EXECUTION (ID_);

alter table ACT_RU_INCIDENT
add constraint ACT_FK_INC_PROCINST
foreign key (PROC_INST_ID_)
references ACT_RU_EXECUTION (ID_);

alter table ACT_RU_INCIDENT
add constraint ACT_FK_INC_PROCDEF
foreign key (PROC_DEF_ID_)
references ACT_RE_PROCDEF (ID_);

alter table ACT_RU_INCIDENT
add constraint ACT_FK_INC_CAUSE
foreign key (CAUSE_INCIDENT_ID_)
references ACT_RU_INCIDENT (ID_);

alter table ACT_RU_INCIDENT
add constraint ACT_FK_INC_RCAUSE
foreign key (ROOT_CAUSE_INCIDENT_ID_)
references ACT_RU_INCIDENT (ID_);

alter table ACT_RU_AUTHORIZATION
add constraint ACT_UNIQ_AUTH_USER
unique (TYPE_,USER_ID_,RESOURCE_TYPE_,RESOURCE_ID_);

alter table ACT_RU_AUTHORIZATION
add constraint ACT_UNIQ_AUTH_GROUP
unique (TYPE_,GROUP_ID_,RESOURCE_TYPE_,RESOURCE_ID_);

update ACT_GE_PROPERTY set VALUE_ = 'fox' where NAME_ = 'schema.version';
update ACT_GE_PROPERTY set VALUE_ = 'create(fox)' where NAME_ = 'schema.version';

[Christian Lipphardt]

Hi Peter,

Daniel is unavailable the next couple of days, so I will try to help you.

Although I was not able to check your update script totally, we do not need the statement "create index ACT_IDX_HI_ACT_INST_COMP on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_, END_TIME_, ID_);" any longer, because the way the history works in the engine was rewritten. It seems the statement was not removed from the 6.2 to 7.0 update scripts, my apologies.

I'll try to have a more detailed look into your scripts today/tomorrow.

Cheers

Christian

 

[Christian Lipphardt]

Hi Peter,

it should be safe to drop columns which are not contained in the create scripts of camunda bpm engine.

The DEPLOYMENT_ID_ column of ACT_RU_JOBS is not a renaming of the PROC_DEF_ID column, it is required for the job_executor to work in heterogenous cluster environments, where not every cluster node has the same process applications deployed.

Cheers

Christian

[Jaap Sperling]

Hi,

It might be a good idea to update the documentation on the migration from activiti. It currently states "Upgrade to activiti 5.11". Might have to asterisk this with "some items created during this upgrade with disappear again after upgrading to camunda 7.0.0".

It's not difficult, but it would definitely have been worth knowing, because it's causing me a few headaches in creating upgrade scripts.

Thanks
Jaap