Extending Cdbtransaction

I currently use a behavior to implement history logging, which uses afterFind() and beforeSave() to determine any changes and log them to a history table (MySQL archive engine).

The archive table format doesn’t support transactions, so my existing solution could show history for an update that never occurred, as a result of the actual changes being rolled back.

I’ve thought of a way to solve this:

[list=1]

[*]Override CDbTransaction

[*]Add a private array to hold the outstanding history

[*]Add a method to append to the history array (called from the behavior)

[*]Override CDbTransaction::commit() to save all history after calling the parent implementation

[*]Update the behavior to log history to the active transaction if there is one, or output straight to the history table if not

[/list]

This would enable the history to be logged only if the transaction completes successfully.

Unfortunately, the CDbTransaction class is hardcoded in CDbConnection::beginTransaction().

I’m intending to extend CDbConnection and override the beginTransaction() method, so that I can instantiate my new transaction class.

Is this the best way to approach this problem, or is there a more sensible option?

Spots of this kind are the few places where Yii isn’t much extensible.

I’m interested in the solution, too.

I’m about to go ahead and implement this. Any last minute suggestions anyone?

Okay, I don’t like how I’ve had to override a private instance variable, but this implementation seems to work.




class DbConnection extends CDbConnection

{

	private $_transaction; // Override parent

	

	public function beginTransaction() 

	{ 

		Yii::trace('Starting transaction', 'DbConnection'); 

		$this->setActive(true); 

		$this->getPdoInstance()->beginTransaction(); 

		return $this->_transaction=new DbTransaction($this); 

	}

}


class DbTransaction extends CDbTransaction {

	

	private $outstandingHistory = array();

	

	public function appendHistory($history)

	{

		$this->outstandingHistory[] = $history;

	}

	

	public function commit()

	{

		parent::commit();

		

		foreach ($this->outstandingHistory as $history)

			$history->save();

		

		$this->outstandingHistory = array();

	}

}



Each element of outstandingHistory is a History model instance. If there’s an active transaction, the history logging behavior appends the instance to this array. If there’s no active transaction, the behavior simply saves the History model instance.

I figured I’d post it in case anyone else needs similar functionality in the future.

Thanks for starting this discussion Keith. I found myself in the same situation as you. However I think if you are redeclaring [font="Courier New"]$_transaction[/font] in your extended connection class you also need to port the [font="Courier New"]getCurrentTransaction()[/font] method to your extended class as well otherwise any calls to it will return the parents [font="Courier New"]$_transaction[/font].

I wanted to put a SQL comment in our transaction [font="Courier New"]commits[/font] so I ended up with this monstrosity:




// Extended so we can return our own Transaction class

class DbConnection extends CDbConnection

{

	private $_transaction; // CODE SMELL!! Override parent because unfortunately it's private in the parent.




	// carbon-copy of the parent method

	public function getCurrentTransaction() 

	{

		if($this->_transaction!==null)

			if($this->_transaction->getActive())

				return $this->_transaction;

		return null;

	}


	public function beginTransaction() 

	{ 

		Yii::trace('Starting transaction', 'DbConnection'); 

		$this->setActive(true); 

		$this->getPdoInstance()->beginTransaction(); 

		return $this->_transaction=new DbTransaction($this); // Note: we return our extended Transaction class

	}

}


class DbTransaction extends CDbTransaction

{	

	public function commit()

	{

		if ($this->getActive() && $this->getConnection()->getActive()) {


			// Get a random string to use as a ID for this commit

			$comment = substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"), 0, 15);


			$e = new Exception;

			Yii::trace("COMMIT with comment {$comment} - Stack trace:\n".print_r($e->getTraceAsString(),true),'app.components.dbtransaction');


			// Instead of using PDO::commit, use raw COMMIT with a SQL comment so we can marry the slow-log with the server logs.

			$this->getConnection()->createCommand("COMMIT /* {$comment} */")->execute();


			$this->setActive(false);

		} else

			throw new CDbException(Yii::t('yii','CDbTransaction is inactive and cannot perform commit or roll back operations.'));

	}

}



Note: this is not production code, it is only temporary to try to debug some commits that are in our mysql slow-log but we aren’t sure which transaction they relate too.

If you need to support transactions, why not implement history using triggers on tables?