I’m just working on the delete functionality for questions in my app.
A question will have one or more answers associated so I want to ensure
that I delete all associated answers before deleting the question.
Now this seems like an obvious case for a transaction but as I’m new to yii i’m not too clear on the best way to handle it.
I’m thinking of implementing my own beforeDelete and then either have a transaction to just delete the answers then if it succeeds just return true and if it fails rollback and return false. Does that sound right or is their a more ‘correct’ way to handle it in yii?
It is not YII. It is software development. It does not matter what u are using (Yii, CI). DO not use beforeDelete. It does not work for transaction. Transaction should be atomic, but with beforeDelete you can not make it atomic. when you call delete, yii calls before delete so you deleted all answers so now you committed transaction, it is time to delete question itself. But there is not guarantee that it gets deleted correctly. So this question becomes orphan without answers(none can answer LOL). you may it like that:
in model class:
function safeDelete(){
//begin transaction
//delete all items in loop
$this->delete();
//commit transaction
}
There is another way. It is ultimate way for you i think. i would go with this way. On db level just put cascade = delete for foreign key(for answers table) so when you delete question then db itself deletes it is answers according to relationship. This is most secure way.
Isn’t the cascade on delete only for inodb database? Mine isn’t inodb.
I like you’re idea about having a safe delete function. I guess that would
just hold the transaction for question and associated answers. Then instead
of calling the delete() function as it does now in the generated actionDelete
I’d call the safeDelete() function instead.
Thanks
Lux
UPDATE:
SOLVED
public function safeDelete($id){
// delete transaction for
// question and associated answers
$connection = yii::app()->db;
$transaction=$connection->beginTransaction();
try
{
$sql1 = "DELETE FROM answers WHERE question_id = ".$id;
$sql2 = "DELETE FROM questions WHERE id = ".$id;
$connection->createCommand($sql1)->execute();
$connection->createCommand($sql2)->execute();
//.... other SQL executions
$transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
$transaction->rollBack();
}
}
IF it is not innodb engine then even transaction will not work. For checking just just fail by throwing and exception then u will see that rollback does not work. but u can change myIsam to innodb then it supports transaction
I haven’t done much with transactions so bear with me.
So you’re saying that even though the code I have appears to work that if the commit did fail the rollback would not actually kick in because it’s not innodb. So I have to change all the tables to innodb?
I think since the application will be more search orientated and there will be very few updates/inserts I should stick with myisam. I guess I’ll just have to figure out how to handle data integrity on delete operations myself.
