New DB API function: db_replace_into()

There are a number of places in the code where because of concurrency issues an UPDATE is attempted and if no records are updated then an INSERT is attempted, muted (in case another thread got there first) by PHP’s error-suppression operator @ (see e.g. http://api.drupal.org/api/function/variable_set/6, also the proposed patch at http://drupal.org/node/213699).

Effectively this replicates MySQL’s REPLACE [INTO] extension to the SQL standard. Technically speaking,

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

(http://dev.mysql.com/doc/refman/5.1/en/replace.html).

Arguments for db_replace_into() (or db_replace_into_query()) could be the same as for http://api.drupal.org/api/function/db_query/7, but omitting the initial REPLACE [INTO]. Implementing for MySQL in Drupal would be easy – i.e. just add “REPLACE” to the front of the $sql.

There is an equivalent for PgSQL: see http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL#.... This would be more complex to implement since db_replace_into ($sql) would need to extract the table name, parse the field names and values and create a WHERE clause using the primary or a unique key (yay schema!), and also create an UPDATE statement.

DBs that don’t support either of these could implement db_replace_into() using the paradigm currently used, i.e. UPDATE conditionally followed by @INSERT. This would also require the field names and values to be parsed from $sql and an UPDATE query would need to be created from these complete with a WHERE clause as above.

Although not specified on the SQL coding standards page (http://drupal.org/node/2497), in core, INSERT INTO always seems to use the syntax in which the list of columns is given, followed by the VALUES, and never the SET column=value syntax, nor the one where no columns are listed and the values for all columns are just listed in the “right” order. If this can be taken as a standard for db_replace_into() then that does at least prevent it from having to work out which syntax is used in $sql.

Does all/any of this seem sensible?