Postgres SQL schema name inconsistency

[deg]

My dev evironment uses activiti 5.16.3 on a PostGreSQL 9.3
My aciviti tables need to run in a seperate schema.

I lost some time due to the fact that i gave the schema an name in uppercase. Activiti always converts the entire tablename, incuding the database schema prefix, to lowercase when trying to connect.

In the TableDataManager

 if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableName = tableName.toLowerCase();
      }
‍‍‍‍‍

Once i renamed my schema to lowercase, activiti was able to find my schema and the tables.
In my configuration i still have the prefix in uppercase.

However it then crashes during the dbSchemaCheck because there it does not convert the tablenames to lower case.

Caused by: org.activiti.engine.ActivitiException: Activiti database problem: Tables missing for component(s) engine, history, identity
   at org.activiti.engine.impl.db.DbSqlSession.dbSchemaCheckVersion(DbSqlSession.java:892) [activiti-engine-5.16.3.jar:5.16.3]
   at org.activiti.engine.impl.db.DbSqlSession.performSchemaOperationsProcessEngineBuild(DbSqlSession.java:1366) [activiti-engine-5.16.3.jar:5.16.3]
‍‍‍‍‍

This is because in the DbSqlSession the table name is converted to lower case, but the schema name is not.
Which is rather inconsistent.

  public boolean isEngineTablePresent(){
    return isTablePresent("ACT_RU_EXECUTION");
  }
  public boolean isHistoryTablePresent(){
    return isTablePresent("ACT_HI_PROCINST");
  }
  public boolean isIdentityTablePresent(){
    return isTablePresent("ACT_ID_USER");
  }
  
   public boolean isTablePresent(String tableName) {
   ……..
    String schema = this.connectionMetadataDefaultSchema;
      if (dbSqlSessionFactory.getDatabaseSchema() != null && dbSqlSessionFactory.getDatabaseSchema().length() > 0) {
        schema = dbSqlSessionFactory.getDatabaseSchema();
      }
      
    String databaseType = dbSqlSessionFactory.getDatabaseType();
    if ("postgres".equals(databaseType)) {
        tableName = tableName.toLowerCase();
      }
   ……..
   }
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I retrieve the schemaname via JNDI lookup so i will be able to obtain the correct schema name depending on the environment the application runs. But i suspect this is not intended behaviour.

[deg]

In addition to the above remark, in encountered what looks like a bug.
After resolving the upper , lower case problems the isTablePresent method still returns false.
As a result i still get the exception
<code>org.activiti.engine.ActivitiException: Activiti database problem: Tables missing for component(s) engine, history, identity</code>

The reason why this fails lies in the parameters passed to the <code>databaseMetaData.getTables</code> method in the code below:

<code> if ("postgres".equals(databaseType)) {
        tableName = tableName.toLowerCase();
      }
     
      try {
        tables = databaseMetaData.getTables(catalog, schema, tableName, JDBC_METADATA_TABLE_TYPES);
        return tables.next();
      } finally {</code>

The schema name is not passed. The tablename is the concatenation between schema and tablename. The tables are found when i pass the real tablename as a parameter in debugging mode. When i set the schema to the actual schema, instead of null as is now the case, then the tables are still found.
So to summarize:

<code>databaseMetaData.getTables(null, null, "myschema.act_ru_execution", JDBC_METADATA_TABLE_TYPES)</code> finds nothing.

<code>databaseMetaData.getTables(null, null, "act_ru_execution", JDBC_METADATA_TABLE_TYPES)</code> does find the table.

<code>databaseMetaData.getTables(null, "myschema", "act_ru_execution", JDBC_METADATA_TABLE_TYPES)</code> does find the table.


So i think i'll return to an in-memory database for a while…