Use the new UPSERT capability from SQLite 3.24

Any idea how to test effect? I used to try to measure changes of drush si -y but see no difference

php --ri pdo_sqlite

pdo_sqlite

PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.32.3

The patch looks good to me, just one remark:

+++ b/core/lib/Drupal/Core/Database/Driver/sqlite/Upsert.php
@@ -22,11 +22,21 @@ public function __toString() {
+      $update[] = "$field = EXCLUDED.$field";

Could we add a comment with why this line is done this way and include a link to https://www.sqlite.org/lang_UPSERT.html.

We already have testing for upserts in Drupal/KernelTests/Core/Database/UpsertTest.

Here's a patch with comment and link to official docs (having the link as @see makes it more visible for API site)

All the code changes look good to me.
There already testing for upserts.
The patch has documentation.
For me it is RTBC.

This is a direct conflict with #3107155: Raise SQLite version requirement to 3.26 in Drupal 9 - I think we need to explicitly close that issue as 'by design' if we go ahead here, so tagging.

Since we are now around two weeks away from the first point release of Ubuntu 20.04 (20.04.1) it'll probably be all right to close that issue as 'by design'.

Certain assumptions were made in the other issue about Ubuntu, and I would just like to point out that when a new Ubuntu LTS is released, some cautious users will treat it as a RC-release, and not upgrade until the first point release is out, three to four months later.

9.1 freeze in 4 weeks, so it always can be rolled back or BC shim could appear in contrib

This issue is blocking #2755831: Make keyvalue and config stores for database faster by using upsert OR get() before set(). And that issue will result in a significant perfomance increasement when writing to the key_value tables. We are now using a merge query to write to the key_value tables and I would like to change that to an upsert query. Merge queries use first a select query to determine whether to use an insert query or an update query. Upsert queries do the same in one query.

Or the alternative issue #2547493: Add support for unique / primary key constraints composed of multiple fields for Upsert queries. We are not sure which we will close as a duplicate.

Having indexes (comment #116) could cause performance regressions so would be great to measure/profile install/rebuild

#3107155: Raise SQLite version requirement to 3.26 in Drupal 9 is now officially won't fix, so we can proceed here.

Committed dd164ae and pushed to 9.2.x. Thanks!

Is this change backportable to 9.1.x? (patch applies)