Feature Request ON DUPLICATE KEY

[Flowman]

Feature request to extend the database driver ON DUPLICATE KEY functionality

Documentation for each typ of database:

• https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
• https://mariadb.com/kb/en/insert-on-duplicate-key-update/
• https://www.postgresqltutorial.com/postgresql-upsert/

joomla/joomla-cms#32559

[richard67]

MySQL supports statements like INSERT ... ON DUPLICATE KEY UPDATE ... since at leat version 5.6: https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

MariaDB seems to support the same since at least some 7 years: https://mariadb.com/kb/en/insert-on-duplicate-key-update/

PostgreSQL supports statements like INSERT ... ON CONFLICT ... DO UPDATE SET ... since at leat version 9.6: https://www.postgresql.org/docs/9.6/sql-insert.html

So it should be possible to implement support for that in the 2.0-dev branch here for version 4 of the CMS.

[nibra]

We need to define when to use the UPDATE fallback. I'd suggest to use REPLACE (which MySQL supports since ages),
See https://dev.mysql.com/doc/refman/8.0/en/replace.html
The behaviour would be different, though, as the native REPLACE deletes records that are in the way for INSERT, while our implementation would do an UPDATE in case on conflict.
Any objections?

[richard67]

@nibra I don't think there is something like MySQL's REPLACE for PostgreSQL, since it's absolutely not SQL standard.

[alikon]

even if i don't like upsert
I think upsert should be a nice to have feature

but should avoid SQL dialects

[richard67]

@nibra Or did I get you wrong, thinking you suggest to use INSERT while you just were suggesting it as name for the new function? Well, I'd prefer something similar to what @Flowman has suggested in the discussion in the CMS repo: joomla/joomla-cms#32559 (reply in thread)

I'd maybe use something like insertOrUpdateObject and query->onDuplicateKeyUpdate regarding the naming.

[alikon]

maybe it's my MongoDB old memories 😄

[alikon]

a good quick read https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

[nibra]

I'd maybe use something like insertOrUpdateObject and query->onDuplicateKeyUpdate regarding the naming.

I like the names as they are self-explanatory.
The problem I see is that we cannot safely provide this feature for other databases than MySQL (MariaDB) and PostgreSQL.

[richard67]

The problem I see is that we cannot safely provide this feature for other databases than MySQL (MariaDB) and PostgreSQL.

Do we have to support others in the 2.0-dev branch?

[nibra]

Yes, MS SQL Server. To drop it, we need an official decision by the Production Department.

[richard67]

@nibra In Joomla 4 it has been dropped for sure. I thought that's the case here in the 2.0-dev branch, too.

[Llewellynvdm]

@richard67 am I understanding you correctly, that support for MS SQL is dropped in Joomla 4? When did that happen... forgive me for asking but I missed that... not that I mind 👍

[alikon]

on J4 for sure https://downloads.joomla.org/technical-requirements
on https://github.com/joomla-framework/database debatable

[richard67]

@Llewellynvdm Yes, see https://docs.joomla.org/Potential_backward_compatibility_issues_in_Joomla_4 , the top section "Updated System Requirements":

SQL Server support has been dropped.

[Llewellynvdm]

You got me... guilty as charged I did not read. Thanks!