Basically jBPM supports all databases supported by Hibernate.
Customize the default generated DB schema
jBPM uses the hibernate schema generation tool to generate the DDL scripts to create the database schema. We can't however produce an optimized database schema for all databases that way. Therefore, this page intends to document on how to optimize the default generated schema for the specific databases. Also these pages contain information about database specific issues such as table or page sizes that need to be considered.
Consider your approach to long text columns
One general remark about the text fields. We divided text columns in normal and long length. Normal columns were given the default length (hibernate defined) of 255. The long columns were given the length of 4000. For these long fields we still used varchars because that is the most portable type. Also we just used 2 lengths so that you can do an easy global search and replace.
In case your database and driver do not have a problem with CLOBS, another option for dealing with the long text columns is a custom hibernate type that stores java.lang.Strings into CLOBS. There is already an example hibernate type available in source format that you can copy from http://fisheye.jboss.org/viewrep/~raw,r=8011/Hibernate/trunk/HibernateExt/metadata/src/java/org/hibernate/type/StringClobType.java
You will need to update the schema and change the VARCHAR(4000)'s with your database's CLOB type. Then you need to update the jBPM hibernate mappings and specify your custom StringToClobType. You can find all the occurences by searching the hbm.xml files for '4000'
Indexes
The default generated DDL includes just a few indexes. It is assumed that the databases implicitely generate indexes for primary and foreign keys. You might want to check if this is the case for your database. If not, you might want to create those indexes explicitely. Ony way to do this is by adding an index="..." element in the jbpm hibernate mappings on all many-to-one elements and give the index a proper name. Then the re-generate the schema and your indexes should be created explicitly.
on-delete="cascade"
This is an attribute that can be added to the key element in hibernate collection mappings. It is limited to collections that have inverse="true". This will make process instance deletion more efficient cause hibernate will let the database handle the cascade deletes instead of issueing a delete statement for every entity separately. Here's a list of collections key's that you might want to mark with on-delete="cascade":
org.jbpm.graph.exe.Token.children
org.jbpm.context.exe.ContextInstance.tokenVariableMaps
org.jbpm.context.exe.TokenVariableMap.variableInstances
org.jbpm.taskmgmt.exe.TaskMgmtInstance.taskInstances
org.jbpm.taskmgmt.exe.TaskMgmtInstance.swimlaneInstances
Too many connections exception
There is a common error working with database due to connections that are never closed. So, SQLException is thrown when the createContext method is invoked, due to database configuration parameter that take care of the number of open connections an application can have.
Most of the time the error is produced by the misuse of JbpmContext. The database connections remains OPEN till jbpmContext is closed. So, NEVER FORGET close JbpmContext. Each time the createJbpmContext method is invoked, the jbpmContext.close() MUST be also invoked later.
JbpmContext jbpmContext = this.configuration.createJbpmContext();
try {
...
} finally {
jbpmContext.close();
}
It's very advisable the use of C3P0 pool to improve the application performance. Here the hibernate configuration for the use of C3P0 pool. Also yo must put the c3p0 jar in classpath
Database specific experiences
Please append these wiki pages with your experiences.
Comments