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);
}
}