Database Read/Write Splitting Implementation

I have written a DbConnectionMan class to extend the Yii database connection,it implements the read/write splitting such basicly but useful.

For database read and write splitting use,this class override the CDbConnection::getCommand method,to detect whether the sql statement is a read operation,if true,this method will use a slave database config construct a new db connection for use.

To use it,put the DbConnectionMan.php under the protected/components/ directory,then in the configuration,declare:




'components'=>array(

'db'=>array(

'class'=>'DbConnectionMan',

'slave'=>array(

//slave database connection config here

)

)

)



Code below:




<?php


/**

 * DbConnectionMan(Database Connection Manager) class is a manager of database connections.

 * for the purpose of database read/write splitting.

 * It override the createCommand method,

 * detect the sql statement to decide which connection will be used.

 * Default it use the master connection.

 * */

class DbConnectionMan extends CDbConnection {


	/**

	 * @var array $slaves.Slave database connection(Read) config array.

	 * The array value's format is the same as CDbConnection.

	 * @example 

	 * 'components'=>array(

	 * 		'db'=>array(

	 * 			'connectionString'=>'mysql://<master>',

	 * 			'slaves'=>array(

	 * 				array('connectionString'=>'mysql://<slave01>'),

	 * 				array('connectionString'=>'mysql://<slave02>'),

	 * 			)

	 * 		)

	 * )

	 * */

	public $slaves=array();





	/**

	 * Whether enable the slave database connection.

	 * Defaut is true.Set this property to false for the purpose of only use the master database.

	 * @var bool $enableSlave 

	 * */

	public $enableSlave=true;

	

	

	/**

	 * @override

	 * @var bool $autoConnect Whether connect while init

	 * */

	public $autoConnect=false;




	/**

	 * @var CDbConnection

	 */

	private $_slave;


	/**

	 * Creates a CDbCommand object for excuting sql statement.

	 * It will detect the sql statement's behavior.

	 * While the sql is a simple read operation.

	 * It will use a slave database connection to contruct a CDbCommand object.

	 * Default it use current connection(master database).

	 * 

	 * @override 

	 * @param string $sql

	 * @return CDbCommand

	 * */

	public function createCommand($sql) {

		if ($this->enableSlave && !$this->getCurrentTransaction() && self::isReadOperation($sql)) {

			return $this->getSlave()->createCommand($sql);

		} else {

			return parent::createCommand($sql);

		}

	}




	/**

	 * Construct a slave connection CDbConnection for read operation.

	 * @return CDbConnection

	 * */

	public function getSlave() {

		if (!isset($this->_slave)) {

			foreach ($this->slaves as $slaveConfig) {

				if (!isset($slaveConfig['class']))

					$slaveConfig['class']='CDbConnection';

				try {

					if ($slave=Yii::createComponent($slaveConfig)) {

						Yii::app()->setComponent('dbslave',$slave);

						$this->_slave=$slave;

						break;

					}

				} catch (Exception $e) {continue;}

			}

			if (!$this->_slave) {

				$this->_slave=clone $this;

				$this->_slave->enableSlave=false;

			}

		}

		return $this->_slave;

	}


	/**

	 * Detect whether the sql statement is just a simple read operation.

	 * Read Operation means this sql will not change any thing ang aspect of the database.

	 * Such as SELECT,DECRIBE,SHOW etc.

	 * On the other hand:UPDATE,INSERT,DELETE is write operation.

	 * */

	public function isReadOperation($sql) {

		return preg_match('/^\s*(SELECT|SHOW|DESC|PRAGMA)\s+/i',$sql);

	}


}




By the way,PHP has no method to upcast a instance to its parent class.

In these codes,

Line:




<?php

 $this->_slave=clone $this;

?>



At the beginning,I want to write as that:




<?php

 $this->_slave=(parent)$this;

//or

 $this->_slave=(CDbConnection)$this;

?>



But it doesn’t work sadly!

PHP use this syntax for type casting

(str)$intvar;

but it doesn’t support user define class.

Uncompletely OOP implementation!Up cast is useful in some cases!

Thank you for the useful extension.

I added the following function in order to implement QueryCaching for slaves.




/**

 * Override cache() function to implement QueryCaching for slaves.

 * @return CDbConnection

 * */	

public function cache($duration, $dependency=null, $queryCount=1)

{

	if ($this->enableSlave) {

		$slave = $this->getSlave();

		$slave->queryCachingDuration=$duration;

		$slave->queryCachingDependency=$dependency;

		$slave->queryCachingCount=$queryCount;

	}

	return parent::cache($duration, $dependency, $queryCount);

}



it’s great!but not support the queir builder method.