How to configure Studio H2 databases case sensitive behavior to get an identical behavior between H2 and PostgreSQL?

Q&A
1

Hi,

We are running into case-sensitivity issues due to the discrepancies between the H2 database used by the Studio and our postgres database in QA and production environment. These issues are not detected in the Studio by our developers because the H2 datasources configuration contains a IGNORECASE=TRUE;

From what I understand, H2 is configured to be case insensitive because we want the search in the Portal to be case insensitive. So all type of RDBMS (PostgreSQL, SQL Server, Oracle) must be configured (or at least bonita schema) to be case insensitive.

In the documentation, it says:  "Also, you are recommended to configure the database(s) to be case-insensitive so that searches in Bonita Portal, Bonita REST API and Bonita Engine API are case-insensitive." : https://documentation.bonitasoft.com/bonita/7.12/database-configuration

But this is a problem: Postgres can not be configured case-insensitive, even though it's the Bonita recommended stack.

When our developers are developing some API extensions, we use direct SQL queries on the BDM.
Using the groovy.sql.Sql.rows (query) then new JsonBuilder (rows) methods, the case of JSON attributes is different between execution on H2 and on PostgreSQL. This is problematic when using JSON afterwards.
The problem could be worked around by forcing aliases for each column in the query, H2 being case sensitive to the provided aliases. However, it doesn't seem like the best practice.

I've found this, but this doesn't answer our use case: https://community.bonitasoft.com/questions-and-answers/how-create-case-insensitive-posgresql-database-bonita

Even if these problems do not seem insurmountable, they have caused us over the months of multiple errors on arrival in acceptance, errors that were not visible on the workstations of the developers.

Do you know if the configuration of the H2 database embedded in the studio can be modified in order to obtain identical behavior between H2 and PostgreSQL?

I have also found the H2 parameters DATABASE_TO_UPPER and DATABASE_TO_LOWER (https://www.h2database.com/javadoc/org/h2/engine/DbSettings.html), which seem to be positioned in the datasource used when creating the database.

Do you have visibility on these parameters and their compatibility with Studio Bonita?


This needs to be checked, but at first glance they could allow finding the behavior observed on PostgreSQL: all the names of tables and columns in lowercase, which should be transmitted as is by hibernate.

Could you please let me know the best practices for these problems?


Thanks

2

Hi, Marielle,

in order to ensure returned keys are always the same accross db vendors, I suggest you this simple trick to collect and lower case return keys

since groovy relays on JDBC driver, we are not sure to have always same result, that why I add this "toLowerCase()"

def rows = params.isEmpty() ? sql.rows(query) : sql.rows(query, params, p*c+1 , c)

 

def products = rows.collect { row ->

row.collectEntries([:]) { k,v -> [k.toLowerCase(), v] }

  }

// Build the JSON answer with the query result

JsonBuilder builder = new JsonBuilder(products)

String table = builder.toPrettyString()

 

return buildResponse(responseBuilder, table)

regards,

 

Laurent

3

You are perfectly right, Bonita official documentation indicates to set the PostgreSQL server in case-insensitive, which cannot be done directly with only server configuration. Documentation should be updated accordingly.

 

we use direct SQL queries on the BDM

This is not precisely recommended. BDM should be queried using the JPQL queries designed in the Studio (or programmatically with Engine APIs).

 

Do you know if the configuration of the H2 database embedded in the studio can be modified in order to obtain identical behavior between H2 and PostgreSQL?

Yes, the parameter IGNORECASE=TRUE can be changed in file BonitaStudioSubscription-<VERSION>/workspace/tomcat/server/conf/Catalina/localhost/bonita.xml so that H2 is case-sensitive.

 

I understand this is not an ideal situation, but regarding PostgreSQL we have not an easy workaround to make it case-insensitive.

Regards,
Emmanuel