$wpdb->replace is not supported. is it planned?

[SamTyurenkov]

Tried to use built-in wordpress method $wpdb->replace() and it throws Invalid or unsupported SQL statement, which leads me to this method below, as I can see it is not in preg_match variants yet, is it planned?

function createSQLRewriter(string $sql): AbstractSQLRewriter
{
    $sql = trim($sql);
    if (preg_match('/^(SELECT|INSERT|UPDATE|DELETE|DESCRIBE|ALTER TABLE|CREATE TABLE|DROP TABLE|SHOW INDEX|SHOW VARIABLES|SHOW TABLES|OPTIMIZE TABLE|SET NAMES|SHOW FULL COLUMNS)\b/i', $sql, $matches)) {
        // Convert to a format suitable for class names (e.g., "SHOW TABLES" becomes "ShowTables")
        $type = str_replace(' ', '', ucwords(str_replace('_', ' ', strtolower($matches[1]))));
        $className = $type . 'SQLRewriter';

        if (class_exists($className)) {
            return new $className($sql);
        } else {
            throw new Exception("No class defined to handle SQL type: " . $type);
        }
    }
    throw new Exception("Invalid or unsupported SQL statement.");
}

[mattbucci]

We should be able to add support for this

It looks like it generates something like

REPLACE INTO `$table` ($fields) VALUES ($formats)

If you can share an example SQL statement we can add it to the test suite and add functionality for it.

MySQL Docs: https://dev.mysql.com/doc/refman/8.0/en/replace.html Postgresql Docs: No Equivalent

Say you have some statement today

REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');

We will have to automatically rewrite it to:

INSERT INTO test2 (column1, column2, column3)
VALUES (1, 'Old', '2014-08-20 18:47:00')
ON CONFLICT (column1) 
DO UPDATE SET 
    column2 = EXCLUDED.column2, 
    column3 = EXCLUDED.column3;

For a generic MySQL to Postgres, The tricky part is that we don't know the name of the columns in the first statement, so we may need a second query to get that info.

However, in wordpress we get the full set of column names using $fields and values syntax which makes this a simpler problem to solve.

The wordpress wpdb->replace method is here: https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/23da9c0dd2808877270838ad13d8da852b212f9e/wp-includes/class-wpdb.php#L2584

the key part being

        $sql = "$type INTO `$table` ($fields) VALUES ($formats)";

        $this->check_current_query = false;
        return $this->query( $this->prepare( $sql, $values ) );

If we do end up needing to issue a second query to create a lookup table we can do so via the query method here: https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/23da9c0dd2808877270838ad13d8da852b212f9e/pg4wp/driver_pgsql.php#L481

We have some other similar use cases where we will need to do something similar and split 1 query into 2 such as here: https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/23da9c0dd2808877270838ad13d8da852b212f9e/pg4wp/driver_pgsql.php#L1103

[mattbucci]

PR Added: https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/pull/103

[mattbucci]

@SamTyurenkov if you have some example REPLACE INTO statements I'd love to add them into the test suite, I only added my basic example listed above

[SamTyurenkov]

@mattbucci I think I rewrote it to INSERT ON DUPLICATE KEY UPDATE, couldnt find an example anymore.

Basically, as I understand the difference right, REPLACE will increment the auto-increment key, otherwise its the same as INSERT ON DUPLICATE KEY UPDATE

[mattbucci]

Yes that's what my PR transforms them to. Glad you were able to find a workaround.

This will be merged into a future build. Thanks for reporting the issue. https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/pull/103