Postgres SQL schema name inconsistency

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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…