DB Read-Write-Splitting

/**

  • 实现数据库的主从分离,该类会维护多个数据库的配置:一个主数据库配置,多个从数据库的配置。

  • 具体使用主数据库还是从数据库,使用如下规则:

  • 1、CDbCommandExt的prepare方法会根据sql语句是读还是写,来调用CDbConnectionExt的getPdoInstance方法,来获取主数据库或者从数据库链接,默认使用主数据库

  • 2、如果当前处于一个事务中,那么无视第一条规则,在事务结束前全部使用主数据库

  • 3、如果从数据库的配置为空,则使用主数据库

*/

class CDbConnectionExt extends CDbConnection

{

/**


 * @var string The Data Source Name, or DSN, contains the information required to connect to the database.


 * @see http://www.php.net/manual/en/function.PDO-construct.php


 */


public $connectionString;


/**


 * @var string the username for establishing DB connection. Defaults to empty string.


 */


public $username='';


/**


 * @var string the password for establishing DB connection. Defaults to empty string.


 */


public $password='';





/**


 * @var array 从数据库配置数组,例如:


 * array(


 * 		array('connectionString'=>'mysql:host=192.168.1.100;dbname=McAm;port=8001', 'username'=>'mcam', 'password'=>'123456'),


 * 		array('connectionString'=>'mysql:host=192.168.1.101;dbname=McAm;port=8001', 'username'=>'mcam', 'password'=>'123456'),


 * 		array('connectionString'=>'mysql:host=192.168.1.102;dbname=McAm;port=8001', 'username'=>'mcam', 'password'=>'123456'),


 * )


 */


public $slaveConfig = array();





/**


 * @var integer number of seconds that table metadata can remain valid in cache.


 * Use 0 or negative value to indicate not caching schema.


 * If greater than 0 and the primary cache is enabled, the table metadata will be cached.


 * @see schemaCachingExclude


 */


public $schemaCachingDuration=0;


/**


 * @var array list of tables whose metadata should NOT be cached. Defaults to empty array.


 * @see schemaCachingDuration


 */


public $schemaCachingExclude=array();


/**


 * @var string the ID of the cache application component that is used to cache the table metadata.


 * Defaults to 'cache' which refers to the primary cache application component.


 * Set this property to false if you want to disable caching table metadata.


 * @since 1.0.10


 */


public $schemaCacheID='cache';


/**


 * @var boolean whether the database connection should be automatically established


 * the component is being initialized. Defaults to true. Note, this property is only


 * effective when the CDbConnection object is used as an application component.


 */


public $autoConnect=true;


/**


 * @var string the charset used for database connection. The property is only used


 * for MySQL and PostgreSQL databases. Defaults to null, meaning using default charset


 * as specified by the database.


 */


public $charset;


/**


 * @var boolean whether to turn on prepare emulation. Defaults to false, meaning PDO


 * will use the native prepare support if available. For some databases (such as MySQL),


 * this may need to be set true so that PDO can emulate the prepare support to bypass


 * the buggy native prepare support. Note, this property is only effective for PHP 5.1.3 or above.


 */


public $emulatePrepare=false;


/**


 * @var boolean whether to log the values that are bound to a prepare SQL statement.


 * Defaults to false. During development, you may consider setting this property to true


 * so that parameter values bound to SQL statements are logged for debugging purpose.


 * You should be aware that logging parameter values could be expensive and have significant


 * impact on the performance of your application.


 * @since 1.0.5


 */


public $enableParamLogging=false;


/**


 * @var boolean whether to enable profiling the SQL statements being executed.


 * Defaults to false. This should be mainly enabled and used during development


 * to find out the bottleneck of SQL executions.


 * @since 1.0.6


 */


public $enableProfiling=false;


/**


 * @var string the default prefix for table names. Defaults to null, meaning no table prefix.


 * By setting this property, any token like '{{tableName}}' in {@link CDbCommand::text} will


 * be replaced by 'prefixTableName', where 'prefix' refers to this property value.


 * @since 1.1.0


 */


public $tablePrefix;


/**


 * @var array list of SQL statements that should be executed right after the DB connection is established.


 * @since 1.1.1


 */


public $initSQLs;





private $_attributes=array();


private $_active=false;


private $_pdo;


private $_transaction;


private $_schema;





//主数据库、从数据库链接


private $_pdoMaster = null;


private $_pdoSlave = null;





/**


 * Constructor.


 * Note, the DB connection is not established when this connection


 * instance is created. Set {@link setActive active} property to true


 * to establish the connection.


 * @param string The Data Source Name, or DSN, contains the information required to connect to the database.


 * @param string The user name for the DSN string.


 * @param string The password for the DSN string.


 * @see http://www.php.net/manual/en/function.PDO-construct.php


 */


public function __construct($dsn='',$username='',$password='',$slaveConfig=array())


{


	$this->connectionString=$dsn;


	$this->username=$username;


	$this->password=$password;


	


	//设置从数据库配置


	$this->slaveConfig=$slaveConfig;


}





/**


 * Close the connection when serializing.


 */


public function __sleep()


{


	$this->close();


	return array_keys(get_object_vars($this));


}





/**


 * @return array list of available PDO drivers


 * @see http://www.php.net/manual/en/function.PDO-getAvailableDrivers.php


 */


public static function getAvailableDrivers()


{


	return PDO::getAvailableDrivers();


}





/**


 * Initializes the component.


 * This method is required by {@link IApplicationComponent} and is invoked by application


 * when the CDbConnection is used as an application component.


 * If you override this method, make sure to call the parent implementation


 * so that the component can be marked as initialized.


 */


public function init()


{


	parent::init();


	if($this->autoConnect)


		$this->setActive(true);


}





/**


 * @return boolean whether the DB connection is established


 */


public function getActive()


{


	return $this->_active;


}





/**


 * Open or close the DB connection.


 * @param boolean whether to open or close DB connection


 * @throws CException if connection fails


 */


public function setActive($value)


{


	//不在这边创建数据库链接,在getPdoInstance方法中创建,即在真正需要的时候才创建


	$this->_active=true;


	/*if($value!=$this->_active)


	{


		if($value)


			$this->open();


		else


			$this->close();


	}*/


}





/**


 * Opens DB connection if it is currently not


 * @throws CException if connection fails


 */


protected function open($connectionString='', $username='', $password='')


{


	//如果没有输入数据库配置,则使用主数据库配置


	$connectionString = $connectionString?$connectionString:$this->connectionString;


	$username = $username?$username:$this->username;


	$password = $password?$password:$this->password;


	


	if(empty($connectionString))


		throw new CDbException(Yii::t('yii','CDbConnection.connectionString cannot be empty.'));


	//本次创建出来的pdo链接


	$pdo = null;


	try


	{


		Yii::trace('Opening DB connection','system.db.CDbConnection');


		$pdo=$this->createPdoInstance($connectionString, $username, $password);


		$this->initConnection($pdo);


	}


	catch(PDOException $e)


	{


		if(YII_DEBUG)


		{


			throw new CDbException(Yii::t('yii','CDbConnection failed to open the DB connection: {error}',


				array('{error}'=>$e->getMessage())));


		}


		else


		{


			Yii::log($e->getMessage(),CLogger::LEVEL_ERROR,'exception.CDbException');


			throw new CDbException(Yii::t('yii','CDbConnection failed to open the DB connection.'));


		}


	}


	//返回创建的pdo链接


	return $pdo;


}





/**


 * Closes the currently active DB connection.


 * It does nothing if the connection is already closed.


 */


protected function close()


{


	Yii::trace('Closing DB connection','system.db.CDbConnection');


	$this->_pdo=null;


	$this->_active=false;


	$this->_schema=null;


	


	//删除主数据库链接、从数据库链接


	$this->_pdoMaster=null;


	$this->_pdoSlave=null;


}





/**


 * Creates the PDO instance.


 * When some functionalities are missing in the pdo driver, we may use


 * an adapter class to provides them.


 * @return PDO the pdo instance


 * @since 1.0.4


 */


protected function createPdoInstance($connectionString, $username, $password)


{


	$pdoClass='PDO';


	if(($pos=strpos($connectionString,':'))!==false)


	{


		$driver=strtolower(substr($this->connectionString,0,$pos));


		if($driver==='mssql' || $driver==='dblib')


			$pdoClass='CMssqlPdoAdapter';


	}


	return new $pdoClass($connectionString,$username,$password,$this->_attributes);


}





/**


 * Initializes the open db connection.


 * This method is invoked right after the db connection is established.


 * The default implementation is to set the charset for MySQL and PostgreSQL database connections.


 * @param PDO the PDO instance


 */


protected function initConnection($pdo)


{


	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


	if($this->emulatePrepare && constant('PDO::ATTR_EMULATE_PREPARES'))


		$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);


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


	{


		if(strcasecmp($pdo->getAttribute(PDO::ATTR_DRIVER_NAME),'sqlite'))


			$pdo->exec('SET NAMES '.$pdo->quote($this->charset));


	}


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


	{


		foreach($this->initSQLs as $sql)


			$pdo->exec($sql);


	}


}





/**


 * 真正的创建pdo链接


 * @return PDO the PDO instance, null if the connection is not established yet


 */


public function getPdoInstance($useSlave=false)


{


	//满足这两种情况才使用从库:1、存在从库;2、当前不处于一个事务中


	if(	$useSlave &&


		count($this->slaveConfig) &&


		(!$this->_transaction || !$this->_transaction->getActive()))


	{


		if($this->_pdoSlave)


			$this->_pdo = $this->_pdoSlave;


		else


		{


			//随机选择一个从库


			$randIndex = array_rand($this->slaveConfig);


			list($connectionString, $username, $password) = $this->slaveConfig[$randIndex];


			$this->_pdo = $this->_pdoSlave = $this->open($connectionString, $username, $password);


		}


	}


	else{


		if($this->_pdoMaster)


			$this->_pdo = $this->_pdoMaster;


		else


			//创建主数据库链接


			$this->_pdo = $this->_pdoMaster = $this->open();


	}


	return $this->_pdo;


}





/**


 * Creates a command for execution.


 * @param string SQL statement associated with the new command.


 * @return CDbCommand the DB command


 * @throws CException if the connection is not active


 */


public function createCommand($sql)


{


	if($this->getActive()){


		//需要使用CDbCommandExt类,因为该类可以获得sql的读写状态,从而可以选择主从数据库


		return new CDbCommandExt($this,$sql);


		//return new CDbCommand($this,$sql);


	}


	else


		throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


}





/**


 * @return CDbTransaction the currently active transaction. Null if no active transaction.


 */


public function getCurrentTransaction()


{


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


	{


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


			return $this->_transaction;


	}


	return null;


}





/**


 * Starts a transaction.


 * @return CDbTransaction the transaction initiated


 * @throws CException if the connection is not active


 */


public function beginTransaction()


{


	if($this->getActive())


	{


		//获得主数据库,将当前的pdo链接设置为主数据库


		$this->_pdo = $this->getPdoInstance();


		


		if($this->_pdo){


			$this->_pdo->beginTransaction();


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


		}


		else


			throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


	}


	else


		throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


}





/**


 * @return CDbSchema the database schema for the current connection


 * @throws CException if the connection is not active yet


 */


public function getSchema()


{


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


		return $this->_schema;


	else


	{


		if(!$this->getActive())


			throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


		$driver=$this->getDriverName();


		switch(strtolower($driver))


		{


			case 'pgsql':  // PostgreSQL


				return $this->_schema=new CPgsqlSchema($this);


			case 'mysqli': // MySQL


			case 'mysql':


				return $this->_schema=new CMysqlSchema($this);


			case 'sqlite': // sqlite 3


			case 'sqlite2': // sqlite 2


				return $this->_schema=new CSqliteSchema($this);


			case 'mssql': // Mssql driver on windows hosts


			case 'dblib': // dblib drivers on linux (and maybe others os) hosts


				return $this->_schema=new CMssqlSchema($this);


			case 'oci':  // Oracle driver


				return $this->_schema=new COciSchema($this);


			case 'ibm':


			default:


				throw new CDbException(Yii::t('yii','CDbConnection does not support reading schema for {driver} database.',


					array('{driver}'=>$driver)));


		}


	}


}





/**


 * Returns the SQL command builder for the current DB connection.


 * @return CDbCommandBuilder the command builder


 * @since 1.0.4


 */


public function getCommandBuilder()


{


	return $this->getSchema()->getCommandBuilder();


}





/**


 * Returns the ID of the last inserted row or sequence value.


 * @param string name of the sequence object (required by some DBMS)


 * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object


 * @see http://www.php.net/manual/en/function.PDO-lastInsertId.php


 */


public function getLastInsertID($sequenceName='')


{


	//必须使用主数据库链接


	if($this->getActive() && $this->_pdoMaster)


		//return $this->_pdo->lastInsertId($sequenceName);


		return $this->_pdoMaster->lastInsertId($sequenceName);


	else


		throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


}





/**


 * Quotes a string value for use in a query.


 * @param string string to be quoted


 * @return string the properly quoted string


 * @see http://www.php.net/manual/en/function.PDO-quote.php


 */


public function quoteValue($str)


{


	if($this->getActive()){


		//如果当前链接不存在,则创建一个与从数据库之间的链接


		if(!$this->_pdo)


			$this->_pdo = $this->getPdoInstance(true);


		


		//如果创建链接失败,抛出异常


		if(!$this->_pdo)


			throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


		


		return $this->_pdo->quote($str);


	}


	else


		throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


}





/**


 * Quotes a table name for use in a query.


 * @param string table name


 * @return string the properly quoted table name


 */


public function quoteTableName($name)


{


	return $this->getSchema()->quoteTableName($name);


}





/**


 * Quotes a column name for use in a query.


 * @param string column name


 * @return string the properly quoted column name


 */


public function quoteColumnName($name)


{


	return $this->getSchema()->quoteColumnName($name);


}





/**


 * Determines the PDO type for the specified PHP type.


 * @param string The PHP type (obtained by gettype() call).


 * @return integer the corresponding PDO type


 */


public function getPdoType($type)


{


	static $map=array


	(


		'boolean'=>PDO::PARAM_BOOL,


		'integer'=>PDO::PARAM_INT,


		'string'=>PDO::PARAM_STR,


		'NULL'=>PDO::PARAM_NULL,


	);


	return isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;


}





/**


 * @return mixed the case of the column names


 * @see http://www.php.net/manual/en/pdo.setattribute.php


 */


public function getColumnCase()


{


	return $this->getAttribute(PDO::ATTR_CASE);


}





/**


 * @param mixed the case of the column names


 * @see http://www.php.net/manual/en/pdo.setattribute.php


 */


public function setColumnCase($value)


{


	$this->setAttribute(PDO::ATTR_CASE,$value);


}





/**


 * @return mixed how the null and empty strings are converted


 * @see http://www.php.net/manual/en/pdo.setattribute.php


 */


public function getNullConversion()


{


	return $this->getAttribute(PDO::ATTR_ORACLE_NULLS);


}





/**


 * @param mixed how the null and empty strings are converted


 * @see http://www.php.net/manual/en/pdo.setattribute.php


 */


public function setNullConversion($value)


{


	$this->setAttribute(PDO::ATTR_ORACLE_NULLS,$value);


}





/**


 * @return boolean whether creating or updating a DB record will be automatically committed.


 * Some DBMS (such as sqlite) may not support this feature.


 */


public function getAutoCommit()


{


	return $this->getAttribute(PDO::ATTR_AUTOCOMMIT);


}





/**


 * @param boolean whether creating or updating a DB record will be automatically committed.


 * Some DBMS (such as sqlite) may not support this feature.


 */


public function setAutoCommit($value)


{


	$this->setAttribute(PDO::ATTR_AUTOCOMMIT,$value);


}





/**


 * @return boolean whether the connection is persistent or not


 * Some DBMS (such as sqlite) may not support this feature.


 */


public function getPersistent()


{


	return $this->getAttribute(PDO::ATTR_PERSISTENT);


}





/**


 * @param boolean whether the connection is persistent or not


 * Some DBMS (such as sqlite) may not support this feature.


 */


public function setPersistent($value)


{


	return $this->setAttribute(PDO::ATTR_PERSISTENT,$value);


}





/**


 * @return string name of the DB driver


 */


public function getDriverName()


{


	return $this->getAttribute(PDO::ATTR_DRIVER_NAME);


}





/**


 * @return string the version information of the DB driver


 */


public function getClientVersion()


{


	return $this->getAttribute(PDO::ATTR_CLIENT_VERSION);


}





/**


 * @return string the status of the connection


 * Some DBMS (such as sqlite) may not support this feature.


 */


public function getConnectionStatus()


{


	return $this->getAttribute(PDO::ATTR_CONNECTION_STATUS);


}





/**


 * @return boolean whether the connection performs data prefetching


 */


public function getPrefetch()


{


	return $this->getAttribute(PDO::ATTR_PREFETCH);


}





/**


 * @return string the information of DBMS server


 */


public function getServerInfo()


{


	return $this->getAttribute(PDO::ATTR_SERVER_INFO);


}





/**


 * @return string the version information of DBMS server


 */


public function getServerVersion()


{


	return $this->getAttribute(PDO::ATTR_SERVER_VERSION);


}





/**


 * @return int timeout settings for the connection


 */


public function getTimeout()


{


	return $this->getAttribute(PDO::ATTR_TIMEOUT);


}





/**


 * Obtains a specific DB connection attribute information.


 * @param int the attribute to be queried


 * @return mixed the corresponding attribute information


 * @see http://www.php.net/manual/en/function.PDO-getAttribute.php


 */


public function getAttribute($name)


{


	if($this->getActive()){


		//如果当前链接不存在,则创建一个与从数据库之间的链接


		if(!$this->_pdo)


			$this->_pdo = $this->getPdoInstance(true);


		


		//如果创建链接失败,抛出异常


		if(!$this->_pdo)


			throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


		


		return $this->_pdo->getAttribute($name);


	}


	else


		throw new CDbException(Yii::t('yii','CDbConnection is inactive and cannot perform any DB operations.'));


}





/**


 * Sets an attribute on the database connection.


 * @param int the attribute to be set


 * @param mixed the attribute value


 * @see http://www.php.net/manual/en/function.PDO-setAttribute.php


 */


public function setAttribute($name,$value)


{


	if($this->_pdo instanceof PDO)


		$this->_pdo->setAttribute($name,$value);


	/*else


		$this->_attributes[$name]=$value;*/


	//记录设置数据库属性,在主库与从库之间切换时,数据库属性需要从新设置


	$this->_attributes[$name]=$value;


}





/**


 * Returns the statistical results of SQL executions.


 * The results returned include the number of SQL statements executed and


 * the total time spent.


 * In order to use this method, {@link enableProfiling} has to be set true.


 * @return array the first element indicates the number of SQL statements executed,


 * and the second element the total time spent in SQL execution.


 * @since 1.0.6


 */


public function getStats()


{


	$logger=Yii::getLogger();


	$timings=$logger->getProfilingResults(null,'system.db.CDbCommand.query');


	$count=count($timings);


	$time=array_sum($timings);


	$timings=$logger->getProfilingResults(null,'system.db.CDbCommand.execute');


	$count+=count($timings);


	$time+=array_sum($timings);


	return array($count,$time);


}

}

/**

  • 通过sql来选择主库或者从库

*/

class CDbCommandExt extends CDbCommand

{

/**


 * 修改父类的prepare成员函数


 * 当创建一个PDOStatement对象的时候,根据sql是读还是写,来判断使用主数据库还是从数据库


 */


public function prepare()


{


	if($this->_statement==null)


	{


		try


		{


			$useSlave = $this->isReadSql($this->getText());//如果是一条读语句,则使用从数据库


			$this->_statement=$this->getConnection()->getPdoInstance($useSlave)->prepare($this->getText());


			$this->_params=array();


		}


		catch(Exception $e)


		{


			Yii::log('Error in preparing SQL: '.$this->getText(),CLogger::LEVEL_ERROR,'system.db.CDbCommand');


			throw new CDbException(Yii::t('yii','CDbCommand failed to prepare the SQL statement: {error}',


				array('{error}'=>$e->getMessage())));


		}


	}


}





/**


 * 判断一个SQl语句是否为一条读取语句


 * 以SELECT SHOW DESCRIBE DESC打头的语句认为是读语句


 * @return boolean true/false


 */


private function isReadSql($sql){


	static $readSqlPrefix = array('SELECT', 'SHOW', 'DESCRIBE', 'DESC');


	


	//去掉开头的无用的字符


	$sql = ltrim($sql);


	//如果是一个空查询,直接返回


	if(empty($sql)){


		return false;


	}


	//匹配各种情况


	foreach($readSqlPrefix as $prefix){


		$subStr = substr($sql, 0, strlen($prefix)+1);	//多取一个字符,验证这个字符是否为空格回车等


		$subStr = rtrim($subStr);						//去掉末尾的空格回车等


		$subStr = strtoupper($subStr);					//转化成大写


		if($subStr === $prefix)


			return true;


	}


	return false;


}





///////////////////////////////////////////////////////////////////////////////////////////////////


//因为子类不能调用父类的私有成员函数,所以将父类的成员函数和成员变量复制过来,以下是父类的成员函数和成员变量


///////////////////////////////////////////////////////////////////////////////////////////////////





private $_connection;


private $_text='';


private $_statement=null;


private $_params=array();





/**


 * Constructor.


 * @param CDbConnection the database connection


 * @param string the SQL statement to be executed


 */


public function __construct(CDbConnection $connection,$text)


{


	$this->_connection=$connection;


	$this->setText($text);


}





/**


 * Set the statement to null when serializing.


 */


public function __sleep()


{


	$this->_statement=null;


	return array_keys(get_object_vars($this));


}





/**


 * @return string the SQL statement to be executed


 */


public function getText()


{


	return $this->_text;


}





/**


 * Specifies the SQL statement to be executed.


 * Any previous execution will be terminated or cancel.


 * @param string the SQL statement to be executed


 */


public function setText($value)


{


	if($this->_connection->tablePrefix!==null)


		$this->_text=preg_replace('/{{(.*?)}}/',$this->_connection->tablePrefix.'\1',$value);


	else


		$this->_text=$value;


	$this->cancel();


}





/**


 * @return CDbConnection the connection associated with this command


 */


public function getConnection()


{


	return $this->_connection;


}





/**


 * @return PDOStatement the underlying PDOStatement for this command


 * It could be null if the statement is not prepared yet.


 */


public function getPdoStatement()


{


	return $this->_statement;


}





/**


 * Cancels the execution of the SQL statement.


 */


public function cancel()


{


	$this->_statement=null;


}





/**


 * Binds a parameter to the SQL statement to be executed.


 * @param mixed Parameter identifier. For a prepared statement


 * using named placeholders, this will be a parameter name of


 * the form :name. For a prepared statement using question mark


 * placeholders, this will be the 1-indexed position of the parameter.


 * @param mixed Name of the PHP variable to bind to the SQL statement parameter


 * @param int SQL data type of the parameter. If null, the type is determined by the PHP type of the value.


 * @param int length of the data type


 * @return CDbCommand the current command being executed (this is available since version 1.0.<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />


 * @see http://www.php.net/manual/en/function.PDOStatement-bindParam.php


 */


public function bindParam(&#036;name, &amp;&#036;value, &#036;dataType=null, &#036;length=null)


{


	&#036;this-&gt;prepare();


	if(&#036;dataType===null)


		&#036;this-&gt;_statement-&gt;bindParam(&#036;name,&#036;value,&#036;this-&gt;_connection-&gt;getPdoType(gettype(&#036;value)));


	else if(&#036;length===null)


		&#036;this-&gt;_statement-&gt;bindParam(&#036;name,&#036;value,&#036;dataType);


	else


		&#036;this-&gt;_statement-&gt;bindParam(&#036;name,&#036;value,&#036;dataType,&#036;length);


	if(&#036;this-&gt;_connection-&gt;enableParamLogging)


		&#036;this-&gt;_params[&#036;name]=&amp;&#036;value;


	return &#036;this;


}





/**


 * Binds a value to a parameter.


 * @param mixed Parameter identifier. For a prepared statement


 * using named placeholders, this will be a parameter name of


 * the form :name. For a prepared statement using question mark


 * placeholders, this will be the 1-indexed position of the parameter.


 * @param mixed The value to bind to the parameter


 * @param int SQL data type of the parameter. If null, the type is determined by the PHP type of the value.


 * @return CDbCommand the current command being executed (this is available since version 1.0.<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />


 * @see http://www.php.net/manual/en/function.PDOStatement-bindValue.php


 */


public function bindValue(&#036;name, &#036;value, &#036;dataType=null)


{


	&#036;this-&gt;prepare();


	if(&#036;dataType===null)


		&#036;this-&gt;_statement-&gt;bindValue(&#036;name,&#036;value,&#036;this-&gt;_connection-&gt;getPdoType(gettype(&#036;value)));


	else


		&#036;this-&gt;_statement-&gt;bindValue(&#036;name,&#036;value,&#036;dataType);


	if(&#036;this-&gt;_connection-&gt;enableParamLogging)


		&#036;this-&gt;_params[&#036;name]=var_export(&#036;value,true);


	return &#036;this;


}





/**


 * Executes the SQL statement.


 * This method is meant only for executing non-query SQL statement.


 * No result set will be returned.


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return integer number of rows affected by the execution.


 * @throws CException execution failed


 */


public function execute(&#036;params=array())


{


	if(&#036;this-&gt;_connection-&gt;enableParamLogging &amp;&amp; (&#036;pars=array_merge(&#036;this-&gt;_params,&#036;params))&#33;==array())


	{


		&#036;p=array();


		foreach(&#036;pars as &#036;name=&gt;&#036;value)


			&#036;p[&#036;name]=&#036;name.'='.&#036;value;


		&#036;par='. Bind with parameter ' .implode(', ',&#036;p);


	}


	else


		&#036;par='';


	Yii::trace('Executing SQL: '.&#036;this-&gt;getText().&#036;par,'system.db.CDbCommand');


	try


	{


		if(&#036;this-&gt;_connection-&gt;enableProfiling)


			Yii::beginProfile('system.db.CDbCommand.execute('.&#036;this-&gt;getText().')','system.db.CDbCommand.execute');





		&#036;this-&gt;prepare();


		&#036;this-&gt;_statement-&gt;execute(&#036;params===array() ? null : &#036;params);


		&#036;n=&#036;this-&gt;_statement-&gt;rowCount();





		if(&#036;this-&gt;_connection-&gt;enableProfiling)


			Yii::endProfile('system.db.CDbCommand.execute('.&#036;this-&gt;getText().')','system.db.CDbCommand.execute');





		return &#036;n;


	}


	catch(Exception &#036;e)


	{


		if(&#036;this-&gt;_connection-&gt;enableProfiling)


			Yii::endProfile('system.db.CDbCommand.execute('.&#036;this-&gt;getText().')','system.db.CDbCommand.execute');


		Yii::log('Error in executing SQL: '.&#036;this-&gt;getText().&#036;par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');


		throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',


			array('{error}'=&gt;&#036;e-&gt;getMessage())));


	}


}





/**


 * Executes the SQL statement and returns query result.


 * This method is for executing an SQL query that returns result set.


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return CDbDataReader the reader object for fetching the query result


 * @throws CException execution failed


 */


public function query(&#036;params=array())


{


	return &#036;this-&gt;queryInternal('',0,&#036;params);


}





/**


 * Executes the SQL statement and returns all rows.


 * @param boolean whether each row should be returned as an associated array with


 * column names as the keys or the array keys are column indexes (0-based).


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return array all rows of the query result. Each array element is an array representing a row.


 * An empty array is returned if the query results in nothing.


 * @throws CException execution failed


 */


public function queryAll(&#036;fetchAssociative=true,&#036;params=array())


{


	return &#036;this-&gt;queryInternal('fetchAll',&#036;fetchAssociative ? PDO::FETCH_ASSOC : PDO::FETCH_NUM, &#036;params);


}





/**


 * Executes the SQL statement and returns the first row of the result.


 * This is a convenient method of {@link query} when only the first row of data is needed.


 * @param boolean whether the row should be returned as an associated array with


 * column names as the keys or the array keys are column indexes (0-based).


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return array the first row of the query result, false if no result.


 * @throws CException execution failed


 */


public function queryRow(&#036;fetchAssociative=true,&#036;params=array())


{


	return &#036;this-&gt;queryInternal('fetch',&#036;fetchAssociative ? PDO::FETCH_ASSOC : PDO::FETCH_NUM, &#036;params);


}





/**


 * Executes the SQL statement and returns the value of the first column in the first row of data.


 * This is a convenient method of {@link query} when only a single scalar


 * value is needed (e.g. obtaining the count of the records).


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return mixed the value of the first column in the first row of the query result. False is returned if there is no value.


 * @throws CException execution failed


 */


public function queryScalar(&#036;params=array())


{


	&#036;result=&#036;this-&gt;queryInternal('fetchColumn',0,&#036;params);


	if(is_resource(&#036;result) &amp;&amp; get_resource_type(&#036;result)==='stream')


		return stream_get_contents(&#036;result);


	else


		return &#036;result;


}





/**


 * Executes the SQL statement and returns the first column of the result.


 * This is a convenient method of {@link query} when only the first column of data is needed.


 * Note, the column returned will contain the first element in each row of result.


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return array the first column of the query result. Empty array if no result.


 * @throws CException execution failed


 */


public function queryColumn(&#036;params=array())


{


	return &#036;this-&gt;queryInternal('fetchAll',PDO::FETCH_COLUMN,&#036;params);


}





/**


 * @param string method of PDOStatement to be called


 * @param mixed the first parameter to be passed to the method


 * @param array input parameters (name=&gt;value) for the SQL execution. This is an alternative


 * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing


 * them in this way can improve the performance. Note that you pass parameters in this way,


 * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.


 * binding methods and  the input parameters this way can improve the performance.


 * This parameter has been available since version 1.0.10.


 * @return mixed the method execution result


 */


private function queryInternal(&#036;method,&#036;mode,&#036;params=array())


{


	if(&#036;this-&gt;_connection-&gt;enableParamLogging &amp;&amp; (&#036;pars=array_merge(&#036;this-&gt;_params,&#036;params))&#33;==array())


	{


		&#036;p=array();


		foreach(&#036;pars as &#036;name=&gt;&#036;value)


			&#036;p[&#036;name]=&#036;name.'='.&#036;value;


		&#036;par='. Bind with parameter ' .implode(', ',&#036;p);


	}


	else


		&#036;par='';


	Yii::trace('Querying SQL: '.&#036;this-&gt;getText().&#036;par,'system.db.CDbCommand');


	try


	{


		if(&#036;this-&gt;_connection-&gt;enableProfiling)


			Yii::beginProfile('system.db.CDbCommand.query('.&#036;this-&gt;getText().')','system.db.CDbCommand.query');





		&#036;this-&gt;prepare();


		&#036;this-&gt;_statement-&gt;execute(&#036;params===array() ? null : &#036;params);





		if(&#036;method==='')


			&#036;result=new CDbDataReader(&#036;this);


		else


		{


			&#036;result=&#036;this-&gt;_statement-&gt;{&#036;method}(&#036;mode);


			&#036;this-&gt;_statement-&gt;closeCursor();


		}





		if(&#036;this-&gt;_connection-&gt;enableProfiling)


			Yii::endProfile('system.db.CDbCommand.query('.&#036;this-&gt;getText().')','system.db.CDbCommand.query');





		return &#036;result;


	}


	catch(Exception &#036;e)


	{


		if(&#036;this-&gt;_connection-&gt;enableProfiling)


			Yii::endProfile('system.db.CDbCommand.query('.&#036;this-&gt;getText().')','system.db.CDbCommand.query');


		Yii::log('Error in querying SQL: '.&#036;this-&gt;getText().&#036;par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');


		throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',


			array('{error}'=&gt;&#036;e-&gt;getMessage())));


	}


}

}

//日志组件的配置

return array(

'class'=&gt;'CDbConnectionExt',


'emulatePrepare' =&gt; true,


'charset' =&gt; 'utf8',





//主数据库配置


'connectionString' =&gt; 'mysql:host=localhost;dbname=a;port=3306',


'username' =&gt; 'root',


'password' =&gt; '11',





//从数据库配置


'slaveConfig'=&gt;array(


	array('connectionString'=&gt;'mysql:host=localhost;dbname=a;port=3306','username'=&gt;'root','password'=&gt;'11',),


	array('connectionString'=&gt;'mysql:host=localhost;dbname=a;port=3306','username'=&gt;'root','password'=&gt;'11',),


	array('connectionString'=&gt;'mysql:host=localhost;dbname=a;port=3306','username'=&gt;'root','password'=&gt;'11',),


	array('connectionString'=&gt;'mysql:host=localhost;dbname=a;port=3306','username'=&gt;'root','password'=&gt;'11',),


),

);

我觉得随机的选择一个slave还有点问题,假设选择的slave1正好down掉了,整个系统就down了。




    public function getPdoInstance($useSlave=false) {

        $flag = false;

        //满足这两种情况才使用从库:1、存在从库;2、当前不处于一个事务中

        if ($useSlave &&

                count($this->slaveConfig) &&

                (!$this->_transaction || !$this->_transaction->getActive())) {

            if ($this->_pdoSlave)

                $this->_pdo = $this->_pdoSlave;

            else {

                $this->_pdo = $this->selectAvilableSlave($flag);

            }

        } else {

            if ($this->_pdoMaster)

                $this->_pdo = $this->_pdoMaster;

            else

            //创建主数据库链接

                $this->_pdo = $this->_pdoMaster = $this->open();

        }

        return $this->_pdo;

    }


//判断当前的slave库是否可以连接

    public function selectAvilableSlave($flag) {

        while ($i < count($this->slaveConfig) && $flag === false) {

            $randIndex = $this->slaveConfig[$i];

            list($connectionString, $username, $password) = $this->slaveConfig[$randIndex];

            $this->_pdoSlave = $this->open($connectionString, $username, $password);

            if (!empty($this->_pdoSlave)) {

                $flag = true;

            }

            $i++;

        }

        return $this->_pdoSlave;

    }



不错,需要加入从库可用性的判断。

楼主辛苦了,太实用了!

支持一下,很不错!

收下了!回头看!

[color="#006400"]/* moved to Chinese forum */[/color]

把代码帖出来看了一下,这段有点不是很明白,$i应该会被报未定义的变量吧?

这段代码似乎把随机性给取消了.我觉得应该是很随机取一个,然后再来判定.如果随机取的库down掉了,才应该去轮询一个有用的从库才对.

所以稍改了一点点.还没有实际跑,不知道会不会报错.





    /*

     * 判断当前的slave库是否可以连接

     */

    public function selectAvilableSlave($flag) {

        //随机选择一个从库

        $randIndex = array_rand($this->slaveConfig);

        list($connectionString, $username, $password) = $this->slaveConfig[$randIndex];

        $this->_pdo = $this->_pdoSlave = $this->open($connectionString, $username, $password);

        if (empty($this->_pdoSlave)) {

            $i=0;

            while ($i < count($this->slaveConfig) && $flag === false) {

                $randIndex = $this->slaveConfig[$i];

                list($connectionString, $username, $password) = $this->slaveConfig[$randIndex];

                $this->_pdoSlave = $this->open($connectionString, $username, $password);

                if (!empty($this->_pdoSlave)) {

                    $flag = true;

                }

                $i++;

            }

        }

        return $this->_pdoSlave;

    }



不错哦,持续学习中

这里是我很早前写过的一个简单的读写分离,缺陷是会仍然有一个到Master的链接。

优点之一是对down掉的server会在cache中记录一个标识位,10分钟之内不再尝试去连接已经down了的server。

http://www.yiiframework.com/extension/dbreadwritesplitting/

这个比较赞 ! :lol: