Database change management for the camunda bpm platform

[Michael Oberwasserlechner]

Hi,

I would like to start a discussion on database change management for the camunda bpm platform.

As we embed the camunda bpm engine, we would like to get the database changes from the engine/platform and excute it in our own database change management module.

We're using Liquibase for about 3 years and it has proven very effective and reduces the pain of writing sql statement in many dialects to a minimum. (http://www.liquibase.org/)

The camunda BPM platform supports 5 databases. Using liquibase you would write the change only once and Liquibase generates the dbms specific ddl for you. Liquibase also integrates with ANT and Maven so it would be possible to create the ddl statements for the distribution package as well but also deliver it through the engine's api.

Right now I can only think of a couple of tasks which are necassary:

• Migrate existing dbms specific sql to the liquibase format
  
• Generate dbms specific sql for distribution package (zip) in build process
  
• "Process engine configuration database change mode" (manual, standalone, embedded). (especially needed in SpringConfig)
  
• manual: The engine requires database change to be present on startup time
• standalone: The engine is aware of changes and executes the changes itself and then initialise.
• embedded: The engine is aware of changes, delivers these changes to the host application, which executes the change and starts the engine afterwards.

As we have a lot of experience in this area and have a great interest in this, we're happy to help/contribute on this.

What do you think?

Cheers,
Michael

[Daniel Meyer]

Hi Michael,

 

We briefly discussed this internally: we think that adding Liquibase is a good idea but we are a bit afraid of the effort it’s going to take to migrate all the test suites and stuff.

 

We would very much welcome a contribution on this but won’t be able to put too much effort in it ourselves. Not before September, anyway.

If you would be interested in working on this we would be happy to support you as much as we can!

 

Cheers,

Daniel Meyer

Project Lead

www.camunda.org/community/team.html

 

 

[Michael Oberwasserlechner]

Hi Daniel,

perfect :)

I hope that we are able to start working on those features after the 5th of August.

Just a few questions:

• Should we plan and describe everything in your Jira?
  
• Can you or anyone tell me which features of the existing database management feature is used?

We will have to get a closer look into the modules and the platform itself. Any hints or starting points are very welcome. e.g. the db test suites.

Cheers,

Michael

2013/7/18 Daniel Meyer <daniel...@camunda.com>

[Nico Rehwaldt]

Hi Michael, 

I created the issue CAM-1038 to plan, describe and track progress on the contribution. 

We get back to you soon with important starting points for your contribution.

Cheers, 

Nico

[Christian Lipphardt]

Hi Michael,

I wrote a small summary giving an overview of the current database change management implementation of camunda bpm and the involved qa steps.

First, we run tests against following databases:

• db2 9.7
• mysql 5.x
• mssql 2008 and 2012
• postgresql 9
• oracle 10g and 11g
• h2

Overview of maven modules using/testing database integration and their invocation using maven:

ENGINE

engine/pom.xml mvn clean install -Ddatabase=${database}

ENGINE-IT-XA

qa/pom.xml mvn clean install -P${runtime},${database},engine-integration,clean-db -Ddatabase.host=xxxx -Ddatabase.port=xxxx -Ddatabase.name=xxxx -Ddatabase.user=xxx -Ddatabase.password=xxxx

WEBAPPS-UNIT

webapps/camunda-webapp/webapp/pom.xml mvn clean install -Ddatabase=${database}

WEBAPPS-IT

qa/pom.xml mvn clean install -P${runtime},${database},webapps-integration,clean-db -Ddatabase.host=xxxx -Ddatabase.port=xxxx -Ddatabase.name=xxxx -Ddatabase.user=xxx -Ddatabase.password=xxxx

DB-INSTANCE-MIGRATION

qa/test-db-instance-migration/pom.xml mvn clean install -Pupgrade,${database}

DB-UPGRADE-TEST

qa/test-db-upgrade/pom.xml mvn clean install -Pupgrade,70,${database} -Dtest.version.old=6.2.4 -Dtest.version.old.major=6.2

DISTRO-SQL-SCRIPTS

distro/sql-script/pom.xml mvn clean install

Example for maven parameters:

-Ddatabase.host=localhost

-Ddatabase.port=3306

-Ddatabase.name=myEngineDb

-Ddatabase.user=myUser

-Ddatabase.password=myPassword

Allowed values for placeholders above (see corresponding maven pom for actual values):

• ${database}
• db2
• mysql
• mssql
• mssql12
• postgresql
• oracle10g
• oracle
• h2
• ${runtime}
• glassfish
• jboss
• tomcat

ENGINE

The Engine module is the core of camunda-bpm-platform. It contains the process engine but also the core bpm-platform implementation.

The org.camunda.bpm.engine.ProcessEngineConfiguration class and it's implementation org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl contains several contants for specifying the database schema mechanism:

  DB_SCHEMA_UPDATE_FALSE = "false";   // skip schema creation but check if version specified in ProcessEngine.VERSION matches with the one in database table 'ACT_GE_PROPERTY'

  DB_SCHEMA_UPDATE_CREATE_DROP = "create-drop"; // create schema on startup and drop it on shutdown

  DB_SCHEMA_UPDATE_TRUE = "true"; // update schema - NOT USED ATM

  DB_SCHEMA_UPDATE_CREATE = "create"; // create schema on startup

  DB_SCHEMA_UPDATE_DROP_CREATE = "drop-create"; // drop schema on startup and recreate it afterwards

During the intialization of the process engine, the command 'SchemaOperationsProcessEngineBuild' will always be executed.

When the process engine method 'close' is invoked, the command 'SchemaOperationsProcessEngineClose' will be executed.

They use methods defined in 'org.camunda.bpm.engine.impl.db.DbSqlSession', so it will be a good starting point to investigate the current database change mechanism.

At the moment an automatic 'migration' mechanism is non-existent, because we disabled the activiti upgrade mechanism in the past.

So we only support database schema creation and dropping.

The database create/drop scripts used in the schema operations are located at ${camunda-bpm-platform}/engine/src/main/resources/org/camunda/bpm/engine/db folder.

The activiti upgrade mechanism used a property stored in 'ACT_GE_PROPERTY' to bind the actual database schema to a version.

When upgrade is enabled, the engine checks on start up for a version difference and upgrades to the latest database schema using the provided upgrade scripts.

Hint:

The MyBatis mappings are located in ${camunda-bpm-platform}/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping but I think there is no need to touch them when switching to Liquibase.

ENGINE DATABASE TESTING

The engine maven module contains the profile 'database' and some profiles for including the necessary database drivers.

The 'database' profile requires a java properties file located at ${user.home}/.camunda/jdbc/engine.${database}.properties

It requires the following properties to be set:

• jdbc.url
• jdbc.driver
• jdbc.username
• jdbc.password

A DB2 Example:

jdbc.url=jdbc:db2://localhost:50000/ENGI70

jdbc.driver=com.ibm.db2.jcc.DB2Driver

jdbc.username=myDbUser

jdbc.password=myDbPassword

The location of the file can be overridden by specifying property '-Ddb.properties.file=${pathToPropertiesFile}' when executing maven.

ENGINE-IT-XA

The ENGINE-IT-XA tests the camunda-bpm-platform and it's container integration. We run a test matrix against all supported containers and databases.

WEBAPPS-UNIT

It tests the different webapps shipped with camunda-bpm against the different databases.

Uses the same mechanism to specify the test properties as the ENGINE module.

It requires a properties file placed in '${user.home}/.camunda/jdbc/cockpit.${database}.properties' which contains

jdbc.url=jdbc:db2://db2.camunda.loc:50000/CPIT70

jdbc.driver=com.ibm.db2.jcc.DB2Driver

jdbc.username=cpit70

jdbc.password=CaCockpit70

or the location of the file can be overridden by specifying property '-Ddb.properties.file=${pathToPropertiesFile}' when executing maven.

WEBAPPS-IT

Tests the different webapps using Selenium / simulate user interaction.

DB-INSTANCE-MIGRATION

Tests the migration from camunda-fox to camunda-bpm using a database schema from camunda-fox v6.2 to start some processes.

Then it starts a camunda-bpm engine and tests that the same behaviour is still valid for camunda-bpm v7.0.

ATTENTION: Needs access to camunda-fox enterprise edition nexus repositories.

DB-UPGRADE-TEST

The DB-UPGRADE-TEST tests the migration of the database schema.

It uses the maven-sql-plugin to create the old database schema, executes the ENGINE testsuite on it, then uses the maven-sql-plugin again to execute the corresponding database upgrade script.

ATTENTION: Needs access to camunda-fox enterprise edition nexus repositories.

DISTRO-SQL-SCRIPTS

The DISTRO-SQL-SCRIPTS contains the upgrade scripts for the camunda-bpm-platform.

It uses maven to create a JAR file to bundle the create/drop/upgrade scripts using the scripts in the module and from ENGINE module.

During the build, it merges the engine and history script for each database in a single sql file.

The resulting JAR file is used during the assembly of the distro to include the sql scripts.

WRAP-UP LIQUIBASE INTEGRATION

When you implement the Liquibase solution, you must take care of/maintain compability with:

multi-tenancy (it is a requirement to allow the creation of tables with a user specified prefix, because it is a part of the current multi-tenancy implementation)

allow the user to specify which tables should be created (engine, history, identity), because not every user needs all

Imo with Liquibase we can probably drop the DISTRO-SQL-SCRIPTS module and a lot of magic located in org.camunda.bpm.engine.impl.db.DbSqlSession but this is just a guess by me, you have more experience with Liquibase. We should also drop maven-sql-plugin usage in favor of the maven-liquibase-plugin in all affected modules.

I think the camunda-bpm team responsibility for this is to refactor the DB-INSTANCE-MIGRATION / DB-UPGRADE-TEST modules to the Liquibase integration because they still require credentials to access camunda-fox-ee repository, so this leaves you with 'engine'-module and all the maven modules mentioned above minus the DB-INSTANCE-MIGRATION / DB-UPGRADE-TEST modules.

Cheers,

Christian

[Michael Oberwasserlechner]

Hi Christian,

thanks a lot for this very helpful summary. As my vaccation ended yesterday we will soon start reading into this.

Cheers,

Michael