[Dbix-class] emulating (or using) MySQL's INSERT...ON DUPLICATE KEY UPDATE...

[Mark Hedges]

MySQL has a mod to INSERT where you can do:

    INSERT                      key_field  = '$key_value',
                                some_field = 1
    ON DUPLICATE KEY UPDATE     some_field = some_field + 1

In a multi-connection system, this is really handy for the 
initial insert of some counter row if an attempt at update
said no rows affected, because then concurrent insert attempts
will do the right thing.  

It's also useful in later aggregation of counters if I don't
know whether I already have a row or not.  

I would have to do this I guess:

    my $rs  = Schema->resultset('Thing');
    my $row = $rs->find( $key_value );
    if ($row) {
        $row->some_field( $row->some_field + 1 );
        $row->update;
    }
    else {
        $rs->create({ key_field => $key_value, some_field => 1 });
    }

Because I assume (?) this wouldn't work:

    $rs->update_or_create({
        key_field   => $key_value,
        some_field  => \q{ some_field + 1 },
    });

Or would it?  Oh wild, this SQL:

    INSERT INTO thing SET some_field = some_field + 1;

does work if some_field is not null default 0.  Crazy.

I see from ResultSet.pm that it does try to find the row first,
and then it decides whether to update or create.

So in a multi-connection (possibly multi-server) environment,
update_or_create cannot be used reliably.

For the purpose of my later aggregation it will probably work fine.

This is just food for though I guess, whether it's worth it or
not to attempt to extend vendor-specific statements to that 
level of the DBIC code.

Mark