Re: [fw-db] Db_Table createRow() & duplicate keys

On 8/3/2010 10:11 PM, Brad Waite wrote:
> Is there a time-honored way of handling createRow() and avoiding
> duplicate key exceptions?
>
> My first choice for MySQL is ON DUPLICATE KEY UPDATE, but that's not
> wired into Zend_Db.
>
> So what's left? Use find() and if no results createRow()? That seems
> rather atypically cumbersome in the world of Zend_Db wizardry.

In retrospect, I suspect a bit more detail would have been helpful. I'm
scraping a web site in lieu of an official API and need to record updates.

Given this:

CREATE TABLE `copy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`heading` varchar(255) NOT NULL,
`body` text NOT NULL,
`md5` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `md5_UNIQUE` (`md5`)
)

class Copies extends Zend_Db_Table_Abstract
{
protected $_name = 'copy';
protected $_rowClass = 'Copy';
}

class Copy extends Zend_Db_Table_Row_Abstract
{
}

In my page fetcher, I'm doing something like this:

list($heading, $body) = fetchPage();
$data = array('heading' => $heading,
'body' => $body,
'md5' => md5($body));

$copies = new Copies();
$copy = Copies->createRow($data);
$copy->save();

If the page body is identical to what was fetched previously, save()
will toss a integrity constraint violation on the md5 column.

That stands to reason, since the Row thinks this is new data
($this->_cleanData is empty()). Originally, I thought the Row would
indeed check for duplicate data in the Table's keys and do an update()
instead of an insert(), but there's no such magic after all.

I can always catch and handle the exception on every save(), but since I
know there's going to be duplicate data on subsequent fetches, that's
not an exception and shouldn't be treated as such.

So is the following the best way to do an update?

$where = $copies->select()->where('md5 = ?', md5($body));
$copy = $copies->fetchRow($where);
$copy->setFromArray($data);
$copy->save();