MySQL has a non-standard extension called INSERT ... ON DUPLICATE KEY UPDATE. When you’re building a RESTful application, it really rocks, because you can use it quite easily to have just the right PUT semantics. For example, you can have queries such as
INSERT INTO Book
(id, deleted, a, b, c)
VALUES (#id#, 0, #a#, #b#, #c#)
ON DUPLICATE KEY UPDATE
a = #a#, b = #b#, c=#c#,
deleted = 0,
revision = revision + 1
which you might call from java (example uses iBatis) using something like
/* PUT a book, return an HTTP status code or throw an exception */
public int putBookPart(final BookPart bookPart) throws SQLException {
final int affected = sqlMapClient.update("putBook", book);
assert affected == 1 || affected == 2;
return 202 - affected;
}
Someone at MySQL was clever enough to hijack the affected value – it will be 1 if this was an insert (resulting in 201 Created) or 2 if it was an update (resulting in 200 OK)). I don’t know about you, but to me that looks pretty sweet!
In this simple case, there is only one SQL query that happens to do this PUT, and so I don’t even have to bother with transactions.
Oh, just realized there’s one other trick in this example: the revision column is int(11) UNSIGNED NOT NULL DEFAULT 1 which is the basis of my ETag.
LSD::RELOAD 