<?php
/**
 * 主數(shù)據(jù)庫(kù) 寫 從數(shù)據(jù)庫(kù)（可多個(gè)）讀
 * 實(shí)現(xiàn)主從數(shù)據(jù)庫(kù) 讀寫分離 主服務(wù)器無法連接 從服務(wù)器可切換寫功能
 * 從務(wù)器無法連接 主服務(wù)器可切換讀功
 * by lmt
 * */
class DbConnectionMan extends CDbConnection {
    public $timeout = 10; //連接超時(shí)時(shí)間
    public $markDeadSeconds = 600; //如果從數(shù)據(jù)庫(kù)連接失敗 600秒內(nèi)不再連接 
    //用 cache 作為緩存全局標(biāo)記
    public $cacheID = 'cache';
 
    /**
     * @var array $slaves.Slave database connection(Read) config array.
     * 配置符合 CDbConnection.
     * @example
     * 'components'=>array(
     *   'db'=>array(
     *    'connectionString'=>'mysql://<master>',
     *    'slaves'=>array(
     *     array('connectionString'=>'mysql://<slave01>'),
     *     array('connectionString'=>'mysql://<slave02>'),
     *    )
     *   )
     * )
     * */
    public $slaves = array();
    /**
     * 
     * 從數(shù)據(jù)庫(kù)狀態(tài) false 則只用主數(shù)據(jù)庫(kù)
     * @var bool $enableSlave
     * */
    public $enableSlave = true;
 
    /**
     * @var slavesWrite 緊急情況主數(shù)據(jù)庫(kù)無法連接 切換從服務(wù)器（讀寫）.
     */
    public $slavesWrite = false;
 
    /**
     * @var masterRead 緊急情況從主數(shù)據(jù)庫(kù)無法連接 切換從住服務(wù)器（讀寫）.
     */
    public $masterRead = false;
 
    /**
     * @var _slave
     */
    private $_slave;
 
    /**
     * @var _disableWrite 從服務(wù)器（只讀）.
     */
    private $_disableWrite = true;
 
    /**
     *
     * 重寫 createCommand 方法,1.開啟從庫(kù) 2.存在從庫(kù) 3.當(dāng)前不處于一個(gè)事務(wù)中 4.從庫(kù)讀數(shù)據(jù)
     * @param string $sql
     * @return CDbCommand
     * */
    public function createCommand($sql = null) {
        if ($this->enableSlave && !emptyempty($this->slaves) && is_string($sql) && !$this->getCurrentTransaction() && self::isReadOperation($sql) && ($slave = $this->getSlave())
        ) {
            return $slave->createCommand($sql);
        } else {
            if (!$this->masterRead) {
                if ($this->_disableWrite && !self::isReadOperation($sql)) {
 
                    throw new CDbException("Master db server is not available now!Disallow write operation on slave server!");
                }
            }
            return parent::createCommand($sql);
        }
    }
 
    /**
     * 獲得從服務(wù)器連接資源
     * @return CDbConnection
     * */
    public function getSlave() {
        if (!isset($this->_slave)) {
            shuffle($this->slaves);
            foreach ($this->slaves as $slaveConfig) {
                if ($this->_isDeadServer($slaveConfig['connectionString'])) {
                    continue;
                }
                if (!isset($slaveConfig['class']))
                    $slaveConfig['class'] = 'CDbConnection';
 
                $slaveConfig['autoConnect'] = false;
                try {
                    if ($slave = Yii::createComponent($slaveConfig)) {
                        Yii::app()->setComponent('dbslave', $slave);
                        $slave->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout);
                        $slave->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                        $slave->setActive(true);
                        $this->_slave = $slave;
                        break;
                    }
                } catch (Exception $e) {
                    $this->_markDeadServer($slaveConfig['connectionString']);
                    Yii::log("Slave database connection failed!ntConnection string:{$slaveConfig['connectionString']}", 'warning');
 
                    continue;
                }
            }
 
            if (!isset($this->_slave)) {
                $this->_slave = null;
                $this->enableSlave = false;
            }
        }
        return $this->_slave;
    }
 
    public function setActive($value) {
        if ($value != $this->getActive()) {
            if ($value) {
                try {
                    if ($this->_isDeadServer($this->connectionString)) {
                        throw new CDbException('Master db server is already dead!');
                    }
                    //PDO::ATTR_TIMEOUT must set before pdo instance create
                    $this->setAttribute(PDO::ATTR_TIMEOUT, $this->timeout);
                    $this->open();
                } catch (Exception $e) {
                    $this->_markDeadServer($this->connectionString);
                    $slave = $this->getSlave();
                    Yii::log($e->getMessage(), CLogger::LEVEL_ERROR, 'exception.CDbException');
                    if ($slave) {
                        $this->connectionString = $slave->connectionString;
                        $this->username = $slave->username;
                        $this->password = $slave->password;
                        if ($this->slavesWrite) {
                            $this->_disableWrite = false;
                        }
                        $this->open();
                    } else { //Slave also unavailable
                        if ($this->masterRead) {
                            $this->connectionString = $this->connectionString;
                            $this->username = $this->username;
                            $this->password = $this->password;
                            $this->open();
                        } else {
                            throw new CDbException(Yii::t('yii', 'CDbConnection failed to open the DB connection.'), (int) $e->getCode(), $e->errorInfo);
                        }
                    }
                }
            } else {
                $this->close();
            }
        }
    }
 
    /**
     * 檢測(cè)讀操作 sql 語(yǔ)句
     * 
     * 關(guān)鍵字： SELECT,DECRIBE,SHOW ...
     * 寫操作:UPDATE,INSERT,DELETE ...
     * */
    public static function isReadOperation($sql) {
        $sql = substr(ltrim($sql), 0, 10);
        $sql = str_ireplace(array('SELECT', 'SHOW', 'DESCRIBE', 'PRAGMA'), '^O^', $sql); //^O^,magic smile
        return strpos($sql, '^O^') === 0;
    }
 
    /**
     * 檢測(cè)從服務(wù)器是否被標(biāo)記 失敗.
     */
    private function _isDeadServer($c) {
        $cache = Yii::app()->{$this->cacheID};
        if ($cache && $cache->get('DeadServer::' . $c) == 1) {
            return true;
        }
        return false;
    }
 
    /**
     * 標(biāo)記失敗的slaves.
     */
    private function _markDeadServer($c) {
        $cache = Yii::app()->{$this->cacheID};
        if ($cache) {
            $cache->set('DeadServer::' . $c, 1, $this->markDeadSeconds);
        }
    }
}

'db'=>array(
        'class'=>'application.extensions.DbConnectionMan',//擴(kuò)展路徑
        'connectionString' => 'mysql:host=192.168.1.128;dbname=db_xcpt',//主數(shù)據(jù)庫(kù) 寫
        'emulatePrepare' => true,
        'username' => 'root',
        'password' => 'root',
        'charset' => 'utf8',
        'tablePrefix' => 'xcpt_', //表前綴
        'enableSlave'=>true,//從數(shù)據(jù)庫(kù)啟用
   'urgencyWrite'=>true,//緊急情況 主數(shù)據(jù)庫(kù)無法連接 啟用從數(shù)據(jù)庫(kù) 寫功能
    'masterRead'=>true,//緊急情況 從數(shù)據(jù)庫(kù)無法連接 啟用主數(shù)據(jù)庫(kù) 讀功能
        'slaves'=>array(//從數(shù)據(jù)庫(kù)
            array(   //slave1
                'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
                'emulatePrepare' => true,
                'username'=>'root',
                'password'=>'root',
                'charset' => 'utf8',
                'tablePrefix' => 'xcpt_', //表前綴
            ),
   array(   //slave2
                'connectionString'=>'mysql:host=localhost;dbname=db_xcpt',
                'emulatePrepare' => true,
                'username'=>'root',
                'password'=>'root',
                'charset' => 'utf8',
                'tablePrefix' => 'xcpt_', //表前綴
            ),
 
        ),
),