we used to use a home-brown database migration in our projects which I migrated recently to liquibase. We decided for liquibase because it allows to write schema migrations in a database independent way.
In one of the projects we use camunda bpmn engine. So I started to create liquibase schema updates for camunda which was surprisingly easy:
1 .create an in-memory h2 database and fill the initial script
2. create a diff against an empty database - and get an initial schema.
3. do some manual tunings (e.g. insert initial data)
4. Apply a migration, repeat the steps above
I started with schema from 7.0. At the moment, it’s in a state of “works for me”. I would like to share the code. Preferently in the form of a pull request to camunda. Before I do this work, I’d like to know if this is of interest. I have seen that it was discussed before (~2013) but never finished.
I’d also need to know a few “historical” facts about the schema updates:
Are there special things for certain databases or are the different schema updates identical (except used sql the dialect)?
Which version should we use to start with migrations? It will not be possible to use automatic schema migrations for older versions.
What is the meaning of the minor number of schema updates. For example there’s an sql patch 7.4.2 but there’s neither a 7.4.2 tag nor a maven release. What is the first maven release than can deal with this version?
I found some inconsistencies between schema versions. Example:
h2_engine_7.1_to_7.2.sql contains a new table “ACT_HI_CASEACTINST”. If I create 7.2 from scratch, this table is missing. Its in none of the files “activiti.h2.create.engine.sql”, “activiti.h2.create.history.sql” or “activiti.h2.create.identity.sql” of engine-7.2.0
And also between db types. Example:
db2_engine_7.1_patch_7.1.9_to_7.1.10.sql contains 1 new index
h2_engine_7.1_patch_7.1.4_to_7.1.5.sql contains 4 indices
Both addressing very similar issues.
Shouldn’t be the schemas/indices the same on all databases?
Interesting. I just recently played with camunda setup and flyway. Nice to see there is st. for liquibase as well.
I was wondering what would be a good approach to make this usable in engine configuration.
I guess it would be nice if the whole “DB setup” would be extracted to an interface. The default implementation provided by camunda would still interpret UPDATE_SHEMA flags from configuration, but via extensions (plugins) we could switch the db setup to either use liquibase or flyway …
Nice to hear someone is trying to use Liquibase for the db schema management of Camunda.
I will try to answer you questions and shed some light on some topics.
Regarding
The h2_engine_7.1_to_7.2.sql is the upgrade script from 7.1 to 7.2, it contains the new tables for CMMN, which were introduced in 7.2.0. When you start the process engine from scratch, it won’t create the CMMN tables by default, you have to enable it with a flag. Same goes for DMN if I am not mistaken.
The following quote
shows some learnings we had to go through when supporting lots of different databases regarding the required transaction model of the engine. Basically on every database, we have to have indices on all foreign key contraints, otherwise some databases will do a table scan and lock it down, which in turn leads to a deadlock.
For some time, we have a policy in place that all indices are the same on all databases, also checked in the CI.
Some organizational things:
Camunda offers patches as part of the enterprise support. These patches will be applied to the master-branch and then ported back to our maintenance branches. A patch tag will only be created on the maintenance repositories. The public master only contains the alphas and 7.x.0 releases as tags.
So, for example, when you see a 7.4.2 sql patch, the sql patch will be usable with the next alpha or minor release.
The default implementation provided by camunda would still interpret UPDATE_SHEMA flags from configuration, but via extensions (plugins) we could switch the db setup to either use liquibase or flyway …
Does camunda do any schema upgrades when the flag is set? I didn’t get the impression (when we switched from 7.3 to 7.4 all was broken until I manually updated althougth I set the flag for schema upgrade). And I did not see any upgrade scripts within the delivered jars.
On our Spring/JPA applications I hook into the creation of EntiyManagerFactory to do schema upgrades. Maybe this is a way to do it with camunda as well (replacing some factory). But I only know camunda in spring environments, no idea if it applies for other environments as well.
My idea is 1st to make it wokr outside of camunda and then find a way for integration.
The h2_engine_7.1_to_7.2.sql is the upgrade script from 7.1 to 7.2, it contains the new tables for CMMN, which were introduced in 7.2.0. When you start the process engine from scratch, it won’t create the CMMN tables by default, you have to enable it with a flag. Same goes for DMN if I am not mistaken.
Which flag is this? Shouldn’t there be some SQL script files to create it? It sounds to me that create schema scripts are separated by use but the upgrade scripts are not
Are there any docs about it?
shows some learnings we had to go through when supporting lots of different databases regarding the required transaction model of the engine. Basically on every database, we have to have indices on all foreign key contraints, otherwise some databases will do a table scan and lock it down, which in turn leads to a deadlock.
For some time, we have a policy in place that all indices are the same on all databases, also checked in the CI.
I’m not sure that I understand this right. Should we have identical keys on each db type or can’t we because of issues/deadlocks?
So, for example, when you see a 7.4.2 sql patch, the sql patch will be usable with the next alpha or minor release.
Can I determine the patch version number of an alpha release (maven artifact) somehow?
My fault, seems that I missed activiti.h2.create.case.engine.sql and activiti.h2.create.case.history.sql. With those both, schemas are identical if I do 7.1 + patches + 7.1->7.2 vs. fresh 7.2.
