Databases - redhat-consulting/jbpm-ee GitHub Wiki

jBPM leverages several sub-systems to work properly: two databases (jBPM Quartz and jBPM) as well as JMS. Because all three of these resources need to be coordinated from a transactional perspective, jBPM-EE leverages Container Managed Transactions (CMT) provided by Java EE Application Servers (including JBoss and Websphere).

In order to participate in Container Managed Transactions, the database must support two phase commits. This ensures that the writes / commits from one resource (say JMS) and another resources (like the Quartz Timers) are coordinated. If one write fails, all writes / commits roll back. If all succeed, the commit succeeds.

The following databases are supported by jBPM-EE:

  • Oracle (10G, 11G)
  • DB2 (v7.x)
  • MS SQL Server (2007+)
  • MySQL InnoDB (5.5+)
  • Postgres

##Server Configuration## To switch database configurations, simply set the system property: jbpm.ee.database.configuration.type, shown below.

<system-properties>
        <property name="jbpm.ee.database.configuration.type" value="[DATABASE_TYPE]" />
</system-properties>

<!-- EXAMPLES: Select One: 
        <property name="jbpm.ee.database.configuration.type" value="Oracle" />
        <property name="jbpm.ee.database.configuration.type" value="DB2" />
        <property name="jbpm.ee.database.configuration.type" value="SQLServer" />
        <property name="jbpm.ee.database.configuration.type" value="MySQL" />
        <property name="jbpm.ee.database.configuration.type" value="Postgres" />
-->

The following Database Types are supported:

  • Oracle
  • DB2
  • SQLServer
  • MySQL
  • Postgres

##Database Setup## jBPM-EE requires two database schemas: one to support the main jBPM functionality, and one to support persistent timers.

####jBPM Main Schema#### Setting up the jBPM database schemas requires that you run preconfigured SQL script for your specific Database setup, linked below:

Database SQL File
Oracle jbpm-oracle.sql
DB2 jbpm-db2.sql
SQLServer jbpm-mssql.sql
MySQL jbpm-mysql.sql
Postgres jbpm-postgres.sql

####jBPM Timer Schema#### First, download Quartz 1.8.x.

Unzip the Quartz download, and find the SQL scripts within the docs/dbTables folder.

Database SQL File
Oracle tables_oracle.sql
DB2 tables_db2_v8.sql
SQLServer tables_sqlServer.sql
MySQL tables_mysql_innodb.sql
Postgres tables_postgres.sql

##Database Permissions## The following permissions are needed in both the jBPM and jBPM Timer schemas:

  • Insert
  • Update
  • Delete
  • Select

##Application Server Datasource Configuration## Several datasources are needed to connect the Application Server (JBoss, Websphere) to the database schemas.

Database JNDI Location XA Required
jBPM Main java:/jdbc/jbpm-main X
jBPM Timer java:/jdbc/jbpm-timers-xa X
jBPM Timer Non-XA java:/jdbc/jbpm-timers-non-xa

##Database Setup Checklist##