Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 2.5.0b5

Doc Bug #12912 Improve replace() documentation
Submitted: 2008-01-15 21:16 UTC
From: haggholm Assigned: quipo
Status: Closed Package: MDB2 (version 2.4.1)
PHP Version: 5.2.1 OS: Windows XP
Roadmaps: (Not assigned)    
Subscription  


 [2008-01-15 21:16 UTC] haggholm (Petter Häggholm)
Description: ------------ According to the MDB2 documentation, "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." This is not, however, how REPLACE works in e.g. MySQL (which is referred to as implementing it natively). A MySQL REPLACE query, as described in the (accurate) MySQL docs, "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." For new rows they are the same. For existing rows, MDB2 claims that it performs an UPDATE but actually performs a DELETE followed by an INSERT. This means that any rows with foreign key dependencies on the existing row get deleted (on CASCADE) -- I lost hundreds of rows at a time to this (in a testing DB, happily!). To fix this requires only a small change to the documentation. Personally, I think that the alleged functionality is rather nice and wrote a function that does INSERT-or-UPDATE instead of INSERT-or-DELETE-then-INSERT -- I will provide it on request, but I expect that the MDB2 devs can implement it rather better! (Mine sits on top of MDB2 abstractions and is probably slow.)

Comments

 [2008-03-22 08:51 UTC] doconnor (Daniel O'Connor)
Amended docblocks for replace in mysql & mysqli drivers with patches.
 [2008-04-05 14:40 UTC] quipo (Lorenzo Alberton)
Fixed in CVS. The actual behaviour reflects what MySQL and SQLite do natively, i.e. INSERT-or-DELETE-then-INSERT.