H2 database: is it time to migrate to v1.4.x?

Camunda 7 Topics Discussion & Questions
1

I do like the h2 database - it’s lightweight, easy to integrate, and just works… without fuss. A huge advantage is the ease at which the Camunda-engine regenerates the DB. This being a BIG deal for quick turnaround on a fresh database (the “no fuss” advantage). We can also talk about its in-memory capability.

So, without further ingratiating H2, a slight favor to request -

Can the build migrate to the h2 v1.4.x version? Reason for the request is that h2 has since moved to a new data-store naming/format convention. And, attempting to download and build the older version, something easily compatible with the engine’s current (h2-1.3.168.jar) is a little tedious… And, attempting to simply connect the h2 v1.4.x server with Camunda’s DB has a few pitfalls/annoyances - though easily correctable.

In context to the JBoss/Wildfly platform - upgrading to h2 v1.4.x and switching Camunad’s DB integration to a shared, tcp connection is straightforward. This approach then provides access to DB tools (such as eclipse, dbVisualizer, etc) while Camunda is running - avoiding the error, “db is locked by…”. And, though there are other ways around shared access without using the TCP/shared approach, not all the DB tools are copacetic.

Briefly - my workaround:

  1. just download the h2 v1.4.x h2 database
  2. start-up h2, test, etc…

Switch WildFly over to the new h2 version - shutdown the server first:

  1. copy the new h2 jar into the WildFly h2 modules directory

/[wildfly root directory]/modules/com/h2database/h2/main
In my context, I copied in “h2-1.4.193.jar”

  1. update the “modules.xml” found in the same directory

NOTE: new jar name: resource-root path=“h2-1.4.193.jar”

<module xmlns="urn:jboss:module:1.3" name="com.h2database.h2">

    <resources>
        <resource-root path="h2-1.4.193.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>

  1. I removed Camunda’s previous database - letting the BPM engine regenerate using the new h2 version.

  2. Update standalone.xml with the new url

<datasource jta="true" jndi-name="java:jboss/datasources/ProcessEngine" pool-name="ProcessEngine" enabled="true" use-java-context="true" use-ccm="true">
    <connection-url>jdbc:h2:tcp://centosw02//opt/wildfly-1010.camunda.camel/camunda-h2-dbs/process-engine;MVCC=TRUE</connection-url>
    <driver>h2</driver>
    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
    <security>
    <user-name>sa</user-name>
    <password>sa</password>
    </security>
</datasource>
  1. And then startup a database tool and configure - in this example the Eclipse database explorer’s JDBC connection url

jdbc:h2:tcp://centosw02//opt/wildfly-1010.camunda.camel/camunda-h2-dbs/process-engine;MVCC=TRUE

2

Hi @garysamuelson,

we have a feature request about that in our JIRA. https://app.camunda.com/jira/browse/CAM-3311
Would you be interested in providing pull-request with implementation?

Cheers,
Askar

3

Yes - and I appreciate the invitation.

Some background on this topic is that I’m adding a database login-module that hooks into Camunda’s authorization tables. Key feature being we use the built-in user/group management UI to help setup users for extended JAX-RS integration. There’s a previous topic I started on this… realizing the DB login-module required my attention.

I also want to add an optional interceptor for this purpose. Prefer it “optional”, supporting either: (a) interceptor or (b) built-in JAX-RS infrastructure.

And, once we have a DB/Camunda login-module… well, there’s an extended infrastructure that just falls into place.

Note: I’ll review current and future feature requests - sincerly hope the login-module follow-up effort isn’t re-inventing.

1 Like
4

This part is done. Wildfly is a great platform - and the Camunda DB schema inside the H2 database is copacetic with the DBLogin domain:

NOTE: the funky syntax is required to insert doube-quotes, within the XML configs, for access by Wildfly’s DBLogin security domain.

 &quot;

NOTE 2: Required a REPLACE function to trim off the leading ‘{SHA}’ values inserted into the Camunda password field (v7.6)

<security-domain name="DBLogin" cache-type="default">
    <authentication>
    <login-module code="Database" flag="required">
        <module-option name="dsJndiName" value="java:jboss/datasources/ProcessEngine"/>
        <module-option name="principalsQuery" value="SELECT REPLACE(PUBLIC.ACT_ID_USER.PWD_,'{SHA}','') as password FROM PUBLIC.ACT_ID_USER WHERE PUBLIC.ACT_ID_USER.ID_=?"/>
        <module-option name="rolesQuery" value="SELECT ACT_ID_MEMBERSHIP.GROUP_ID_ AS &quot;Role&quot;, 'Roles' AS &quot;RoleGroup&quot; FROM ACT_ID_MEMBERSHIP WHERE ACT_ID_MEMBERSHIP.USER_ID_=?"/>
        <module-option name="hashAlgorithm" value="SHA"/>
        <module-option name="hashEncoding" value="BASE64"/>
    </login-module>
    </authentication>
</security-domain>