add support for Derby database support

[lojian]

Hi All,

I am not sure whether there is someone working on Derby support.

If not, I could help on this immediately. 🙂 Actually, I had already
generated CREATE/DROP scripts for Derby.

pls let me know…

[jbarrez]

No plans for Derby currently, as this is not a database used for production purposes.
For our testing, we prefer to use the H2 database, as we found it is more flexible compared to Derby and HSQL.

[stan4er]

I realize it's been over a year since the most recent posting. But I'm new to Activiti and curious about choices for databases to support.

You say you've found H2 to be more flexible than Derby and HSQL. But H2 appears to be more narrow focused than the other two. H2 is focused on running embedded. The MVCC and MULTI_THREADED features are only experimentally supported and mutually exclusive. So with H2, if you want to stick with what they have tested, only one statement can execute at a time, and if one session inserts, updates or deletes a single row in a table, then the whole table is locked (even selects are disallowed) from other sessions until the first session commits.

HSQL claims to have full support for MVCC and runs multi-threaded. Derby runs multi-threaded with row-level locking for simple calls like insert/update/delete.

We took the demo setup for Activiti (using H2) and ran tests to see how exceptions encountered during task execution were handled. If you throw something not inheriting from ScriptException in a Script Task, you are left with locked tables (not even able to run select statements). It's certainly arguable that ScriptTask's should throw the right kinds of exceptions. But when you are running a database like H2, the problem is magnified.

Could you elaborate on the benefits you found for using H2 vs the others?

Thanks for your help.

[krraghavan]

I realize this is a super old posting but I got Derby to work by using the DB2 DDL files.  Not sure if I'll have issues in other areas but my unit tests pass.  I'm looking for a production quality embedded DB and am curious about the statement that Derby is not a production quality DB.

public class SpringProcessEngineConfigurationWithDerbySupport extends SpringProcessEngineConfiguration {

    public static final String DERBY = "derby";

    public SpringProcessEngineConfigurationWithDerbySupport() {
        super();
        databaseTypeMappings.put("Apache Derby", DATABASE_TYPE_DB2);
    }
}

[trademak]

So the only thing you added is this code? But will it be the most performant SQL for Derby? Is the Derby SQL so close to the DB2 SQL?

Best regards,

[blezek]

Hi,  We've been using Derby in "production" for some time.  Our use case is a fully contained application with DB baked in, we have experience with Derby and it seems more mature / suitable than H2 (flame suit on).  Here's our configuration.  We map "Apache Derby" and "derby" to the DB2 schema, then mess with the DB2 specific settings.  Not terribly happy with the second part, because it will mess up an actually DB2 install, but our app does not support DB2, so it didn't matter.

Would really love to see official Derby support for Activiti!

-dan

   SpringProcessEngineConfiguration configuration = new SpringProcessEngineConfiguration() {
      {
        databaseTypeMappings.put("Apache Derby", DATABASE_TYPE_DB2);
        databaseTypeMappings.put("derby", DATABASE_TYPE_DB2);
        // Derby helps, override DB2 settings
        DbSqlSessionFactory.databaseSpecificLimitBeforeStatements.put("db2", "");
        DbSqlSessionFactory.databaseSpecificLimitAfterStatements.put("db2", "OFFSET #{firstResult} ROWS FETCH NEXT #{maxResults} ROWS ONLY");
        DbSqlSessionFactory.databaseSpecificLimitBetweenStatements.put("db2", "");
        DbSqlSessionFactory.databaseOuterJoinLimitBetweenStatements.put("db2", "");
        DbSqlSessionFactory.databaseSpecificOrderByStatements.put("db2", "");
        DbSqlSessionFactory.databaseSpecificLimitBeforeNativeQueryStatements.put("db2", null);
      }
    };