Possibility of specifying table and column name mappings dynamically via interface implementation

[Balázs Zsoldos]

There is a possibility in the Configuration class to override table and column names. This is a great feature. However, it would be really nice, implement a renaming algorithm in a custom class.

Use-cases:

Atlassian Jira

For example in Atlassian Jira, some tables are created with no quotes and tables beginning with AO prefix are created with upper-case. The implementation would check this and

• if the table starts with AO, it would replace always the table and column names to be upper-case
• otherwise it would replace the table name to have the same case as it is specified by the database (e.g.: upper-case in Oracle, lower-case in postgres, ...)

Resolving table names from database metadata

We get all table and column names from the database connection. We expect that there are no two tables with the same name but different mixture of upper- and lower-case letters. When the name-mapping implementation is called, it always replaces the table and column names to the ones that exist in the database.

Conclusion

We use quoting in our products with upper-case letters only to make querydsl work in every database. E.g.:

• Oracle does not work with lower-case letters in special queries even if quoting is used
• Postgresql JDBC driver quotes table names always if insert-with-key is used. Therefore we must provide exactly the same name as it is used in the database.

We cannot use our naming strategy in case of 3rd products (like Jira, Liferay, Alfresco, ...) as some of them do not use quoting. However, name-mapping could be solved very easily with a primitive implementation of an interface in these situations.

If you agree, I would provide a patch that

• Extends the Configuration class with an addDynamicNameMapping(DynamicNameMapping) function. This function could be called several times and multiple name mapping could be added
• When a table name is searched, first the current nameMapping Map would be checked, and then the dynamicNameMapping objects would be called until a mapping is found

[timowest]

I think what you are asking for is already doable via the NamingStrategy (normalizeSchemaName and normalizeTableName)

[Balázs Zsoldos]

NamingStrategy is for code generation as much as I remember. I am talking about runtime table and column name resolving via the Configuration object that can be passed to SQLQuery, SQLUpdateClause and SQLInsertClause.

An real-life example:

Imagine that you want to write inserts for an existing database schema that is on Oracle and also on PostgreSQL. The one who populated the Database schema did not use quotes. That means that the table and column names will be upper-case in Oracle and lower-case in PostgreSQL. You will face the issue that you cannot generate the Q classes for Querydsl that work in every database. If you generate them with lower-case names, some queries will not work in Oracle. If you generate them with upper-case letters, some queries will not work in PostgreSQL.

The class Configuration already contains the possibility to rename the tables and columns at runtime (when the SQL clause is generated). However, we have to add the mapping of all tables that we will use to the Configuration object one-by-one. In 99% of the cases a simple mapping rule could solve the problem like: Use lower-case in case of PostgreSQL and upper-case in case of Oracle.

As I said, there are more complex issues in case of other systems like Jira. In case of Jira no DDL contains quotes except for the tables that start with the AO_ prefix.

[timowest]

I see, could you create a GitHub issue for it?

[google...@sql-workbench.net]

Am

Imagine that you want to write inserts for an existing database schema that is on Oracle and also on PostgreSQL. The one who populated the Database schema did not use quotes. That means that the table and column names will be upper-case in Oracle and lower-case in PostgreSQL. You will face the issue that you cannot generate the Q classes for Querydsl that work in every database. If you generate them with lower-case names, some queries will not work in Oracle. If you generate them with upper-case letters, some queries will not work in PostgreSQL.

All you need to do is to never use quotes.

So all you need is an option to prevent quoting objects but from my experience QueryDSL doesn't quote identifiers anyway.



 

[Balázs Zsoldos]

It is possible not to use quotes with Querydsl. It is possible to set it via the SQLTemplates instance. However, not using quotes does not solve the issue. See the examples I wrote.

E,g,: In existing systems like Jira, some tables are generated with quotes and upper-case, some of them without quotes. Oracle uses upper-case without quotes so this is not an issue there. However, it is an issue in PostgreSQL as it thinks that tables have lower-case names if quotes are not used.

Another example: Statement.executeUpdate(String sql, String columnNames[]) works only if the column and table has upper-case names in Oracle. It is not possible at all to make this work with lower-case names. The same command works with PostgreSQL in the way that the column names are quoted for sure. Therefore, if you do not use quotes in your SQL statements you will have issues here.

@timowest: I will create the GitHub issue soon and also send a PR.

[Balázs Zsoldos]

Could you, please, have a look on the following pull request somewhen? https://github.com/querydsl/querydsl/pull/2016

It would be nice having this or a similar solution in the next version. May I ask, when the next version will be released?