Always quote identifiers [DATAJDBC-386]

[spring-projects-issues]

Johannes Edmeier opened DATAJDBC-386 and commented

When mapping a Collection like this:

@MappedCollection(idColumn = "PARENT_ID", keyColumn = "ORDER")
private final List<Items> items;

an invalid statement is generated

SELECT item.type AS type, item.scope AS scope, item.ORDER AS ORDER FROM item WHERE item.PARENT_ID = ? ORDER BY ORDER

---

Affects: 1.1 M4 (Moore)

Issue Links:

• DATAJDBC-380 Add back quote ( ` ) around column name when generate SQL for MySQL
  ("is duplicated by")

• DATAJDBC-381 Using backticks in column names leads to failure during INSERT with MySQL

• DATAJDBC-407 Escaping case-sensitive columns

• DATAJDBC-487 Default IdentifierProcessing should respect JDBC driver's setting

Referenced from: pull request #182

[spring-projects-issues]

Jens Schauder commented

Interesting jOOQ issue with the relevant default behaviour of many databases jOOQ/jOOQ#9238

[spring-projects-issues]

Jens Schauder commented

Relevant MySql documentation about how to quote identifiers https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
Note that MySql can be run in different "modes" which change its behaviour

And the documentation about case sensitivity: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

I think we go with the following defaults for MySQL
quoting: ```
default case: lower.

 

[spring-projects-issues]

Jens Schauder commented

Quoting in HSQLDB: http://hsqldb.org/doc/guide/guide.html#sgc_standards

Case sensitivity: http://hsqldb.org/doc/guide/guide.html#N11013

Quoting character: "

Default case: Upper

[spring-projects-issues]

Jens Schauder commented

Postgres: https://www.postgresql.org/docs/9.1/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Quoting: "
Default case: lower

 

[spring-projects-issues]

Jens Schauder commented

SQL Server: http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/

Quoting: "

Default case: upper (SQL Server makes case sensitivity highly configurable and I didn't find anything useful so I'll go with the ANSI standard).

 

[spring-projects-issues]

Jens Schauder commented

I do apply the letter casing standardization to all identifiers, because otherwise it will be next to impossible to write database independent tests as soon as you specify one table or column name

[spring-projects-issues]

lseeker commented

Jens Schauder Hello,

I met problem with this case sensitivity changes on MySQL.

I have MySQL DB in case-sensitive system(linux) with uppercased table names. Testing update data-jdbc 2.0 milestone, my application broken by lowercased table name on queries.

As mentioned on document, MySQL have option for case sensitivity, but it cannot changed after database initialization, so cannot change option on product database.

And this changes also dose not respect org.springframework.data.relational.core.mapping.NamingStrategy what I used for uppercased table name.

I think default case should AS_IS on MySQL dialect.

Thanks.

[spring-projects-issues]

Jens Schauder commented

lseeker you can register your own Dialect as bean and it should get picked up.

I'm not in principle against changing the default for MySQL, but we would need a more general argument why this would be the right choice.
If you still would make that argument, please create a separate issue

[spring-projects-issues]

Jens Schauder commented

lseeker Ignore my last question, It was caused because I missed the issue you created for this: DATAJDBC-487