Spring R2DBC should support camel case columns in Postgresql out of the box or should provide configuration for supporting it.

[Ray-Neyra]

I have such entity class:

@Table("persons")
class Person {

@Id
Long id;

@Column("firstName")	
private String firstName;

@Column("lastName")
private String lastName; 


}

Unfortunately Spring Boot 2.3.4 with Spring Data R2DBC 1.1.4.RELEASE and Spring Data Relational 2.0.4.RELEASE cant handle such entity.

First problem is org.springframework.data.relational.core.mapping.NamingStrategy.

Default method to get column name looks like that:

/**
	 * Defaults to return the given {@link RelationalPersistentProperty}'s name with the parts of a camel case name
	 * separated by '_';
	 */
	default String getColumnName(RelationalPersistentProperty property) {

		Assert.notNull(property, "Property must not be null.");

		return ParsingUtils.reconcatenateCamelCase(property.getName(), "_");
	}

But it can be fixed by myself by creating my own NamingStrategy, which will override method like that:

@Override
public String getColumnName(RelationalPersistentProperty property) {
    return property.getName();
}

It is not flexible, but it works. I think Spring should provide naming strategy to handle camel case columns in Postgresql.

Unfortunately just creating custom NamingStrategy is not enough. It helps with converting data read from database to entity, so we do not get
entity with nulls but inserts and read queries which uses camel case columns will still fail.
They will fail with io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException and message that reqeusted column do not exist.
For example 'column persons.firstname does not exists'. And it is true. Column 'firstname' does not exist. Column 'firstName' exist.
It means that by default Spring Data is not quoting columns names.
Fortunately from Spring Boot 2.3 in R2dbcMappingContext there is setForceQuote(boolean forceQuote) method provided which set forceQuote in org.springframework.data.relational.core.mapping.RelationalMappingContext
It helps with queries so queries do not throw exceptions that columns do not exist.

Unfortunately after enabling it we are back to square one with reading data. Now after read we got null in our entity.
What causes this are toString() methods in:
org.springframework.data.relational.core.sql.DefaultSqlIdentifier and
org.springframework.data.relational.core.mapping.DerivedSqlIdentifier

and this line in MappingR2dbcConverter

String identifier = prefix + property.getColumnName();

in Object readFrom(Row row, @Nullable RowMetadata metadata, RelationalPersistentProperty property, String prefix) method.

The problem is that when forceQuote is set to true, then DefaultSqlIdentifier toString() method returns column name quoted and DerivedSqlIdentifier toString() returns column name in uppercase and in such case object in this line:

Object value = row.get(identifier);

is equal to null, because column name in Row object is stored in lowercase for DerivedSqlIdentifier case and not quoted camel case for DefaultSqlIdentifier case.

To fix this issue I had to copy DefaultSqlIdentifier and DerivedSqlIdentifier classes to project and change toString() method to:

@Override
public String toString() {
    return this.name;
}

Which is not clean solution.

I am not sure if I should post this issue here or in Spring Data Relational project jira because SqlIdentifier classes are from Spring Data Relational project but MappingR2dbcConverter is from Spring Data R2DBC project, so please direct me correctly. :)

[mp911de]

Can you please provide a reproducer if the generated SQL causes grammar exceptions? Also, can you please elaborate on:

It is not flexible, but it works.

The naming strategy API is intended to customize the default naming according to your needs. We'd like to understand what limitation you encounter.

[Ray-Neyra]

I created example project without SqlIdentifiers fix:
https://github.com/Ray-Neyra/spring-data-r2dbc-issue-483-example

I hope it helps.

Regarding naming strategy, I think I wrongly accused it when problem really lies in: DefaultSqlIdentifier/DerivedSqlIdentifier/MappingR2dbcConverter combo.

I think one possible solution would be provide somehow configuration to turn off normalization in toSql(IdentifierProcessing processing) method in DerivedSqlIdentifier when quoted parameter is set to true.

But I do not know what would be clean solution to problem with DefaultSqlIdentifier and MappingR2dbcConverter, because toSql(IdentifierProcessing processing) method has to return quoted value when creating sql queries but
during read from Row object, unquoted camel case value is needed.

[mp911de]

Thank you. I understand that normalization creates the difficulties. We added normalization to align the naming with what a database does by default. I think we didn't consider that normalization could impose a constraint when someone wants to use a naming strategy that makes use of different letter casing.

That topic is related to Spring Data Relational which is co- maintained with Spring Data JDBC. I'll transfer this issue to Spring Data JDBC on Monday.

[Ray-Neyra]

Good to hear!
But what about DefaultSqlIdentifier and MappingR2dbcConverter issue?

[schauder]

We are gathering various issues related to identifiers, in order to come up with a solution that works for all the different combinations out there.

When you provide your own NamingStrategy and also provide a Dialect that specifies IdentifierProcessing.NONE things should work for you while we try to figure out if we can provide a better solution.

[schauder]

This seems to be similar to spring-projects/spring-data-relational#914

[Ray-Neyra]

Thanks for your answer, but how exactly providing my own Dialect would help me?

Currently I do not need NamingStrategy. I just need override toString() methods and remove normalization from toSql() method.
toString() method in DefaultSqlIdentifier and DerivedSqlIdentifier have hardcoded usage of IdentifierProcessing.ANSI if quoted is set to true.

[schauder]

I just need override toString() methods.
That is normally considered more work than providing a bean, since it would involve rebuilding the whole project.

But anyway where doestoString() get used? Except for things like logging toSql(IdentifierProcessing) should be used, with the IdentifierProcessing coming from the dialect.

[Ray-Neyra]

toString() is used in MappingR2dbcConverter when reading from row object. I wrote details about it in my first post.

Regarding normalization. Quoting in postgresql enables case sensitivity so I think it should not be normalized in DerivedSqlIdentifier toSql() method when quoted is set to true.