Allow for the foreign dialects to benefit from UPSERT as well

[mipo256]

Hey Team!

Thank you for introducing the UPSERT support, it is really helpful @christophstrobl. However, we're encountering a problem.

At Axelix (Spring Boot debugging and monitoring solution) we're writing our own dialect for SQLite, since this is the default database and Spring Data JDBC does not support it.

And we want to benefit from UPSERT too. We are not asking core Spring Data team to introduce support fro SQLite. Instead, we're asking to make the UPSERT functionality configurable for the end-users.

What I'm talking about is that now the code very strictly assumes via a switch check what dialects can be in the system. And Spring Data JDBC is explicitly designed to be extensible - users can implement JdbcDialect for example to adapt Spring Data JDBC for their own database.

But right now, if we want to introduce support for UPSERT for SQLite we just cannot do that - Spring Data in a hardcoded fashion assumes only certain databases to be able to UPSERT and not the others.

I have decided to reach out to you ASAP, since it is not yet released in GA. I can help with this if you do not mind, especially considering that we need this extensibility. 😄

Have a nice day!

CC: @mp911de

[christophstrobl]

Thank you @mipo256 for getting in touch about this.

With some design decisions regarding dialects (especially the way they contribute behavior) on the horizon, we have decided to limit the feature's extensibility for now. Keeping the public surface small gives us more leeway to make necessary changes. We fully understand your concern and would love to hear your thoughts on Dialect and Statement interaction so we can capture them early for the next iteration.

[mipo256]

Sure @christophstrobl

We fully understand your concern and would love to hear your thoughts on Dialect and Statement interaction so we can capture them early for the next iteration.

If I understood you correctly, the question is how we're expecting the Dialect to affect the actual JDBC Statement. I will outline my thoughts here.

The problem with UPSERT-like semantics is that different databases support slighly different syntax of ANSI SQL MERGE. Although being widely supported (it is supported since Postgres 15 by the way), MySQL does not support it at all, AFAIK.

Right now, the actual SQL used for UPSERT is build up by particular UpsertStatementRenderer implementations.

		UpsertStatementRenderer statementRenderer = UpsertStatementRenderers.from(dialect);
		UpsertRenderingContext renderingContext = UpsertRenderingContext.of(context, columnsAndBindings.bindings()::get);

		String sql = statementRenderer
				.render(
						source.getTable(), new UpsertStatementRenderer.Columns(columnsAndBindings.insertColumns(),
								source.getConflictColumns(), source.getUpdateColumns(), columnsAndBindings.bindings()),
						renderingContext);
		builder.append(sql);

		return Delegation.leave();

Maybe, for people who write their own dialects, Spring Data JDBC will allow to override UpsertStatementRenderer (or similar abstraction, for the sake of not exposing some internal types and limting the capabilities), so that the implementations would be capable to generate any UPSERT SQL that is specific for their DB.

For example, in our case, we specifically want UPSERT for SQLite, and SQLite does not support MERGE. Thus, I think, we should consider the fact that the exact upsert senamtics may vary in custom dialects.