Problems with identifier case handling in databases and libs

[reckart]

Describe the bug We are using several components when interfacing with databases:

• Liquibase to set up and manage the database schema
• Hibernate to validate that the schema corresponds to the Java entity class definitions and to query the DB
• the actual database (MariaDB, MySQL, HSQLDB)
• and somewhere in the mix there is also Spring potentially fiddling around with stuff

Each of these components may have different strategies for handling the case of identifiers, in particular database table names. These strategies may include:

• automatically upper-casing table names
• quoting table names such that they are not modified by lower-level components
• other stuff such as deriving table names from Java class names which we generally turn off by explicitly specifying table names in the Java classes

Problematic is that each of the components and even different versions of the same components might apply different strategies here. There are some ways we can control these things, e.g.

• Spring: spring.jpa.properties.hibernate.globally_quoted_identifiers can be set to true to ensure the DB does not automatically change the table name case
• Liquibase: for Liquibase, maybe https://docs.liquibase.com/parameters/object-quoting-strategy.html

To Reproduce With MySQL 8, schema validation fails when spring.jpa.properties.hibernate.globally_quoted_identifiers is turned off -> https://github.com/inception-project/inception/issues/2967

When we turn on spring.jpa.properties.hibernate.globally_quoted_identifiers is that (existing) HSQLDB schemas no longer validate because the names were previously upper-cased (e.g. ANNOTATION_DOCUMENT) but Hibernate will look for annotation_document during validation (and later during queries) -> https://github.com/inception-project/inception/issues/3000

Expected behavior It would be nice if we somehow got out of the name-casing hell...

Please complete the following information:

• Version and build ID: 23.3