FWD » Core Development » Database

This is meant to finish the work left open in #5491-20:

We need to simplify the configuration of the locale/collation in use for databases, including the _temp database. I would like to apply sensible defaults, so that these don't need to be configured explicitly at all in most cases.

The collation and code page in use for the _temp database currently are driven by the embedded-collation setting in the directory. Other than for this setting, the inclusion of a section for the temp database in the directory is unnecessary in most cases. I would prefer to derive the correct setting at server startup, but I'm not sure yet how we would do this. In the 4GL, the code page, collation, and country are settable through startup parameters and/or runtime attributes. Do we already have comparable settings in the directory (or programmatic defaults) for any of these for any other purpose? If we can detect these settings when the server starts up, we could compose a default value for the embedded-collation setting, avoiding a need for this as an explicit setting in the directory. Or perhaps we need to leave it as it is to enable an overriding setting...I am open to suggestions.

For persistent databases, we need the collation used for the primary database to be consistent with the Java Collator in use for the embedded H2 database used for metadata and for the dirty database. These must sort the same way, or we may have unpredictable behavior. When PostgreSQL is used, the collation behavior for a primary database is picked up by the database server from the OS-level locale on which the database cluster is built. It can be queried with select datcollate from pg_database where datname = '<database_name>'. For example, for a database in a cluster built on top of the en_US.iso885915 locale:

postgres=# select datcollate from pg_database where datname = 'postgres';
   datcollate    
-----------------
 en_US.iso885915
(1 row)

This query (or whatever is appropriate for other dialects) would be encoded into the database-specific Dialect implementation.

The value returned would be the default for the embedded-collation setting for that database, obviating the need to set it in the directory. If we can use the same name to represent the custom locale used by PostgreSQL and the custom Collator used by Java/H2, that would make things easiest. Otherwise, we will have to implement a mapping between the information returned by the dialect-specific query and needed by the H2 set collation ... statement. The only use case I can think of where we would need to override this would be to allow a user to set a value that is not one of the collations FWD provides "out of the box"; i.e., if a user had developed their own custom locale and Collator implementation.

In any event, whether we implement all this or not, we need to update the FWD v4 documentation to reflect the new H2 Collator specifiers that are supported (as en_US_P2J is now obsolete).

For the _temp database, please see my comments in #9699-15 through #9699-21. We should eliminate all hard coded cfg for _temp and calculate the embedded encoding at runtime.

Greg Shah wrote:

For persistent databases, we need the collation used for the primary database to be consistent with the Java Collator in use for the embedded H2 database used for metadata and for the dirty database. These must sort the same way, or we may have unpredictable behavior.

I think this is most important for the dirty database, because results from the dirty database are interleaved with results from the primary database. If these are off, it can lead to errors, as Alexandru recently fixed for #9428.

The temp-table database does not do this, and it does not use the dirty database. So, if the temp-table database collation is slightly out of sync with the persistent database, it won't result in deleted records incorrectly resurrecting (for example).

That being said, I agree that it is best to have the collation for all these databases in sync to the degree possible, and if we have the dirty database collating the same way as the primary database, there is no reason not to apply the same collation to the temp-table database.

One last note: the dirty database will go away when #8388 is ready. In that scenario, "dirty" records automatically will collate the same way as "primary" records, since they will both be in the primary database.

When PostgreSQL is used, the collation behavior for a primary database is picked up by the database server from the OS-level locale on which the database cluster is built. It can be queried with select datcollate from pg_database where datname = '<database_name>'. For example, for a database in a cluster built on top of the en_US.iso885915 locale:

[...]

This query (or whatever is appropriate for other dialects) would be encoded into the database-specific Dialect implementation.

This makes a lot of sense.

The value returned would be the default for the embedded-collation setting for that database, obviating the need to set it in the directory. If we can use the same name to represent the custom locale used by PostgreSQL and the custom Collator used by Java/H2, that would make things easiest. Otherwise, we will have to implement a mapping between the information returned by the dialect-specific query and needed by the H2 set collation ... statement.

Unless the collations reported by the primary database are standardized names, guaranteed to be the same across dialects OR we have a custom Java collation for every collation used by the primary database dialects, we still will need a mapping.

I agree that it is best to have the collation for all these databases in sync to the degree possible, and if we have the dirty database collating the same way as the primary database, there is no reason not to apply the same collation to the temp-table database.

That is not how OE works. The temp-tables use CPINTERNAL and the permanent database is defined externally.

Greg Shah wrote:

I agree that it is best to have the collation for all these databases in sync to the degree possible, and if we have the dirty database collating the same way as the primary database, there is no reason not to apply the same collation to the temp-table database.

That is not how OE works. The temp-tables use CPINTERNAL and the permanent database is defined externally.

Ah, my brain read "temp-table" when you wrote "metadata". Ignore my comment about matching the temp-table database to that of the primary database.

I think we need a way to allow the H2's default collation and ignore completely the embedded-collation for the _temp database in directory.xml.

Completely agreed.

Currently, if that is missing, en_US@iso88591_fwd_basic is used (and the same is used by the _meta and dirty H2 databases).

Indeed. This is the default. But it should honor CPINTERNAL. Is the customer setting CPINTERNAL to UTF-8?

Also, there is an important nuance here: the temporary database, physical database and meta database can have different collations in OE.

• Physical database is set on dev's preference on OE and FWD.
• Temporary database follows CPINTERNAL in OE, but it is hard-coded in FWD to en_US@iso88591_fwd_basic, but can be customized at start-up - eventually to CPINTERNAL (but manually).
• I don't know what collation the meta-database uses in OE ... I suppose the same as the physical database considering it is one per physical database, right?
  • In FWD, we customize that because we do not store the _meta in the physical database, but in a different H2 database that has the same collation as the one set for _temp.
• The dirty database exists only in FWD and it should have the same collation as the physical database. This is a major bug trigger because queries will use mixed collation rules (from physical and from dirty), so records are mixed in a non-deterministic way, merging two collations basically.

Overall, if you set the embedded-collation to the collation of the persistent database, the _meta and _dirty are fully functional. Temporary tables may require another setting or, better, auto-detect from CPINTERNAL.
In fact, both _meta and _dirty could have auto-detected the collation, but this it is a one-time setting that is going to be deprecated anyways (read below).

Why I'm saying this: if the 4GL temp db is UTF-8 (or the underlying physical dbs), then I think we need to leave H2 to use the default collation. I don't know what to set to make H2's collation be utf8-compatible.

You can simply set it to en_US, because it is a Java native collation, which is encoded using UTF-16 (?). Other customers use nl_NL for instance.

This affects an API test in #9457 app - I've changed that if an empty string is set, then use the implicit H2 ( Java? ) collation; but maybe we should use a "h2-implicit" or something like this string instead?

• I did abandon thinking too much about this, because of #8388 that will embed the dirty database within the persistent database.
• For _meta database, there was a discussion about either embedding the _meta in the persistent database or building it ad-hoc. I am not sure if there is a clear decision in #4369 about this.
• For temporary database, there is indeed a bug that we hard-code the collation. We discussed more in #9699

From #9699:

CPINTERNAL is per-session, just like CURRENT-LANGUAGE. CPINTERNAL can only be set from the directory or from bootstrap cfg for the session (our equivalent to startup parameters in OE).

This means it is not dynamically changed by the user (as PROPATH), so technically we can assume one as a workaround to avoid bugs in #9457.

PS: If the customer choose to port database from ISO based codepage to UTF based codepage, then there will be a clear mismatch from OE system and bugs can occur from within business logic. So it should also consider changing the CPINTERNAL. This way, there is a one true value for embedded-collation. If they have different CPINTERNAL and persistent database in the OE system, then indeed #9711 should be highly prioritized.

Questions:

• Is the legacy persistent database en_US.UTF-8? Is it the same in FWD?
• Is the legacy CPINTERNAL also UTF-8 and CURRENT-LANGUAGE en_US? Or is this per-user?

Alex, when talking _meta tables, the nuances you mentioned is a bit more complex.

There is some 'persistent' information (the users, domain-types, domains, tenants and these are just the one we support) and the VST. The former are stored somewhere with the main database and in FWD we do the same. Those tables are silently 'moved' from one database to another. I think both OE and FWD will use the collation/encoding of the persistent database.
The latter is just dynamic information stored as virtual tables. I am guessing these use the CPInternal although it would make sense to also use the same collation as the rest of the primary database. I think we should be able to create some testcases to detect these and avoid guessing.

If I understand correctly, the goal here is to to eliminate the need for manual configuration and hard-coded settings in directory.xml for embedded-collation, like _temp, _meta and dirty database and to determine the correct default collation setting at runtime?

Stefanel Pezamosca wrote:

If I understand correctly, the goal here is to to eliminate the need for manual configuration and hard-coded settings in directory.xml for embedded-collation, like _temp, _meta and dirty database and to determine the correct default collation setting at runtime?

Exactly:

• for _dirty - the database should be queried to retrieve the collation and use that.
• for _meta - assess #9711-11
• for _temp - the database should honor CPINTERNAL

#9699 is quite insightful.