<?php
/** 
 * 主數據庫 寫 從數據庫（可多個）讀 
 * 實現主從數據庫 讀寫分離 主服務器無法連接 從服務器可切換寫功能 
 * 從務器無法連接 主服務器可切換讀功 
 * by lmt 
 * */ 
class DbConnectionMan extends CDbConnection { 
    public $timeout = 10; //連接超時時間 
    public $markDeadSeconds = 600; //如果從數據庫連接失敗 600秒內不再連接  
    //用 cache 作為緩存全局標記 
    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(); 
    /** 
     *  
     * 從數據庫狀態 false 則只用主數據庫 
     * @var bool $enableSlave 
     * */ 
    public $enableSlave = true; 
 
    /** 
     * @var slavesWrite 緊急情況主數據庫無法連接 切換從服務器（讀寫）. 
     */ 
    public $slavesWrite = false; 
 
    /** 
     * @var masterRead 緊急情況從主數據庫無法連接 切換從住服務器（讀寫）. 
     */ 
    public $masterRead = false; 
 
    /** 
     * @var _slave 
     */ 
    private $_slave; 
 
    /** 
     * @var _disableWrite 從服務器（只讀）. 
     */ 
    private $_disableWrite = true; 
 
    /** 
     * 
     * 重寫 createCommand 方法,1.開啟從庫 2.存在從庫 3.當前不處于一個事務中 4.從庫讀數據 
     * @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); 
        } 
    } 
 
    /** 
     * 獲得從服務器連接資源 
     * @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(); 
            } 
        } 
    } 
 
    /** 
     * 檢測讀操作 sql 語句 
     *  
     * 關鍵字： 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; 
    } 
 
    /** 
     * 檢測從服務器是否被標記 失敗. 
     */ 
    private function _isDeadServer($c) { 
        $cache = Yii::app()->{$this->cacheID}; 
        if ($cache && $cache->get('DeadServer::' . $c) == 1) { 
            return true; 
        } 
        return false; 
    } 
 
    /** 
     * 標記失敗的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',//擴展路徑 
        'connectionString' => 'mysql:host=192.168.1.128;dbname=db_xcpt',//主數據庫 寫 
        'emulatePrepare' => true, 
        'username' => 'root', 
        'password' => 'root', 
        'charset' => 'utf8', 
        'tablePrefix' => 'xcpt_', //表前綴 
        'enableSlave'=>true,//從數據庫啟用 
   'urgencyWrite'=>true,//緊急情況 主數據庫無法連接 啟用從數據庫 寫功能 
    'masterRead'=>true,//緊急情況 從數據庫無法連接 啟用主數據庫 讀功能 
        'slaves'=>array(//從數據庫 
            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_', //表前綴 
            ), 
 
        ), 
),