Bad documentation is worse than no documentation at all

[haggholm]

When you use a database to store a representation of your objects (or if your objects are memory repersentations of database tables, however you prefer to view it), you may be prone to writing things like this, if you use PHP and PEAR::DB or PEAR::MDB2:

$q = "SELECT EXISTS(SELECT * FROM foo WHERE bar)";

if ($db->getOne($q))
{
    $db->exec("INSERT INTO foo ...");
}
else
{
    $db->exec("UPDATE foo SET ... WHERE bar");
}

Well, we just moved to MDB2 and I discovered the glory, so I thought, of the replace method. To quote the documentation's description of what it does, [sic]s and all,

Execute a SQL REPLACE query. A REPLACE query is identical to a INSERT query, except that if there is already a row in the table with the same key field values, the REPLACE query just updates its values instead of inserting a new row.

The REPLACE type of query does not make part of the SQL standards. Since practically only MySQL and SQLite implement it natively, this type of query isemulated through this method for other DBMS using standard types of queries inside a transaction to assure the atomicity of the operation.

Well, this sounds nice. In fact, it sounds really nice—no more conditional code, and a single method call instead of the three different SQL statements (EXISTS, INSERT, and UPDATE). However, there is one teensy little problem. According to the MySQL documentation,

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.

Do you see? In both versions, it's effectively a replace operation, but they are semantically different, and the difference is not academic. The MDB2 docs would have you believe it performs an INSERT or UPDATE. MySQL actually does an INSERT, or a DELETE followed by an INSERT. This is significant, because if you have foreign keys on the table, and if you CASCADE on those foreign keys, then all the dependent rows will get wiped on the REPLACE of a row that already exists. Of course the row will be restored, but the dependent rows are gone forevermore. This would not be the case according to the MDB2 description.

The solution is, of course, quite obvious: Since a function that does what I thought replace does is so very nice, I wrote one. However, I am rather irritated that I lost time and wasted effort on tracking this down due to a blatant error like this. If they'd just said This emulates the MySQL REPLACE, go read about it here I wouldn't have had this problem.