Yii INSERT … ON DUPLICATE UPDATE

Question

I am working on a Yii project. How can I use the ON DUPLICATE feature of MySQL ( http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html ) when doing a save() on a Yii model?

My MySQL is as follows:

CREATE TABLE `ck_space_calendar_cache` (
  `space_id` int(11) NOT NULL,
  `day` date NOT NULL,
  `available` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `price` decimal(12,2) DEFAULT NULL,
  `offer` varchar(45) DEFAULT NULL,
  `presale_date` date DEFAULT NULL,
  `presale_price` decimal(12,2) DEFAULT NULL,
  `value_x` int(11) DEFAULT NULL,
  `value_y` int(11) DEFAULT NULL,
  PRIMARY KEY (`space_id`,`day`),
  KEY `space` (`space_id`),
  CONSTRAINT `space` FOREIGN KEY (`space_id`) REFERENCES `ck_space` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My PHP is a follows:

$cache = new SpaceCalendarCache();
$cache->attributes = $day; //Some array with attributes              
$cache->save();

If there is a duplicate in my primary key (sapce_id,day), I don't want it to complain, I just want it to update with the latest data.

I know how to do it in raw SQL, I was just wondering if there is a clean Yii way to do it.

Answer 1

You are using models in Yii, its quite simple .. try to load you model where you suspect to have duplicate entries, if you find the entry the model is loaded else null is return. now if your model is null simply create new model. rest is your normal code to insert a new record.

//try to load model with available id i.e. unique key
$model = someModel::model()->findByPk($id);  

//now check if the model is null
if(!$model) $model = new someModel();

//Apply you new changes
$model->attributes = $attributes;

//save
$model->save();

Refer to post controllers update method in sample app Yii blog. I might be wrong with spelling of function names, sorry for that.

Answer 2

I overrode beforeValidate() where I checked if a duplicate exists. If one does, I set $this->setIsNewRecord(false);

Seems to work. Not sure how performant it is.

Answer 3

you have to use try catch like that:

                try{
                    $model->save();
                }
                catch(CDbException $e){
                    $model->isNewRecord = false;
                    $model->save();
                }

Answer 4

I'm repeating two main points from previous answers I think you should keep:

1. Don't (try to) use "on duplicate key update" since its MySQL-only, as txyoji points out.

2. Prefer the select->if not found then insert->else insert demonstrated by Uday Sawant.

There's another point here, though: Concurrency. Although for low traffic applications the probability that you'll get in trouble is minimal (still never zero), I think we always be careful about this.

From a transactional point of view, "INSERT .. ON DUPLICATE UPDATE" is not equivalent to selecting into your application's memory and then inserting or updating. The first is a single transaction, then second is not.

Here's a bad scenario:

1. You do select your record using findByPk() which returns null
2. Some other transaction (from some other user request) inserts a record with the id you just failed to select
3. At the next instant you try to insert it again

In this case you'll either get an exception (if you're working with a unique key, as you do here) or a duplicate entry. Duplicate entries are much harder to pick up (usually nothing seems weird until your users see duplicate records).

The solution here is to set a strict isolation level, for example "serializable", and then begin a transaction.

Here's an example for yii:

Yii::app()->db->createCommand('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

$trn = Yii::app()->db->beginTransaction();

try {
    // Try to load model with available id i.e. unique key
    // Since we're in serializable isolation level, even if
    // the record does not exist the RDBMS will lock this key
    // so nobody can insert it until you commit.
    // The same shold for the (most usual) case of findByAttributes()
    $model = someModel::model()->findByAttributes(array(
        'sapce_id' => $sapceId,
        'day' => $day
    ));  

    //now check if the model is null
    if (!$model) {
        $model = new someModel();
    }

    //Apply you new changes
    $model->attributes = $attributes;

    //save
    $model->save();

    // Commit changes
    $trn->commit();

} catch (Exception $e) {
    // Rollback transaction
    $trn->rollback();

    echo $e->getMessage();
}

You can see more about isolation levels at least in the following links and see what every isolation level has to offer in data integrity in exchange for concurrency

http://technet.microsoft.com/en-us/library/ms173763.aspx

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Answer 5

I agree with @txyoji's analysis of the problem, but I would use a different solution.

You can extend the save() method of the model to look for an existing record, and update it, or insert a new row if it doesn't.

Answer 6

The "On Duplicate Key Update" feature is specific to MySQL's dialect of SQL. Its unlikely to be implemented in any data abstraction layer. ZendDB and Propel don't have an equivalent.

You can simulate the behavior by attempting an insert in a try/catch and update if insert fails with the proper error code. (duplicate key error).