PHP RedBean store bean if not exist one

Question

I am a little confused. I actively use PHP RedBean as ORM within my direct mail service and I run into curious situation - I have a table with unique key constraint (i.e. subscriber_id, delivery_id) and two scripts that is writing data into this table. There is source code that is inserting or updating table:

public static function addOpenPrecedent($nSubscriberId, $nDeliveryId)
{
    $oOpenStatBean = \R::findOrDispense('open_stat', 'delivery_id = :did AND subscriber_id = :sid', array(':did' => $nDeliveryId, ':sid' => $nSubscriberId));

    $oOpenStatBean = array_values($oOpenStatBean);
    if (1 !== count($oOpenStatBean)) {
        throw new ModelOpenStatException(
            "Ошибка при обновлении статистики открытий: пара (delivery_id,
            subscriber_id) не является уникальной: ($nDeliveryId, $nSubscriberId).");
    }

    $oOpenStatBean = $oOpenStatBean[0];
    if (!empty($oOpenStatBean->last_add_dt)) {
        $oOpenStatBean->precedent++;
    } else {
        $oOpenStatBean->delivery_id   = $nDeliveryId;
        $oOpenStatBean->subscriber_id = $nSubscriberId;
    }

    $oOpenStatBean->last_add_dt = time('Y-m-d H:i:s');
    \R::store($oOpenStatBean);
}

It is called both from two scripts. And I have issues with corruption unique constraint on this table periodically, because race conditions occurs. I know about SQL "INSERT on duplicate key update" feature. But how can I obtain same result purely using my ORM?

Answer 1

Current, that I know if, Redbean will not issue an

INSERT ON DUPLICATE KEY UPDATE

as the discussion of this cited in the comments above indicates that Redbean's developer considers upsert to be a business logic thing that would pollute the ORM's interphase. This being said, it is most likely achievable if one were to extend Redbean with a custom Query Writer or plugin per the Documentation. I haven't tried this because the method below easily achieves this behavior without messing with the internals and plugins of the ORM, however, it does require that you use transactions and models and a couple of extra queries.

Basically, start your transaction with either R::transaction() or R::begin() before your call to R::store(). Then in your "FUSE"d model, use the "update" FUSE method to run a query that checks for duplication and retrieves the existing id while locking the necessary rows (i.e. SELECT FOR UPDATE). If no id is returned, you are good and just let your regular model validation (or lack thereof) continue as usual and return. If an id is found, simply set $this->bean->id to the returned value and Redbean will UPDATE rather than INSERT. So, with a model like this:

class Model_OpenStat extends RedBean_SimpleModel{
  function update(){
     $sql = 'SELECT * FROM `open_stat` WHERE `delivery_id`=? AND 'subscriber_id'=? LIMIT 1 FOR UPDATE';
     $args = array( $this->bean->deliver_id, $this->bean->subscriber_id );
     $dupRow = R::getRow( $sql, $args );
     if( is_array( $dupRow ) && isset( $dupRow['id'] ) ){
        foreach( $this->bean->getProperties() as $property => $value ){
          #set your criteria here for which fields
          #should be from the one in the database and which should come from this copy
          #this version simply takes all unset values in the current and sets them
          #from the one in the database
          if( !isset( $value ) && isset( $dupRow[$property] ) )
            $this->bean->$property = $dupRow[$property];
        }
        $this->bean->id = $dupId['id']; #set id to the duplicates id
     }
     return true;
  }
}

You would then modify the R::store() call like so:

\R::begin();
\R::store($oOpenStatBean);
\R::commit();

or

\R::transaction( function() use ( $oOpenStatBean ){ R::store( $oOpenStatBean ); } );

The transaction will cause the "FOR UPDATE" clause to lock the found row or, in the event that no row was found, to lock the places in the index where your new row will go so that you don't have concurrency issues.

Now this will not solve one user's update of the record clobbering another, but that is a whole different topic.