Allow CodeIgniter to support database read and write separations

Objective

CodeIgniter default is not support read, write separation, the general practice of online circulation is modified in the Ci_model layer, but there are several problems:

1. First use of CodeIgniter users are used after the discovery is not supported, and then to modify a large number of old code, resulting in more impact;

2. Second, in the model layer modification, if there is code in the controller operation database, will not support read, write separation (although the controller directly manipulate the database is not a good way);

3. Finally, the changes in the Ci_model layer to allow users to use a different database instance, such as Write with $this->write_db->query (), read with $this->read_db->query (), so that the old code basically cannot modify , because the previous code is called the $this->db->query () method, to modify a large number of old code.

Therefore, in the Ci_model layer modification is not a good solution. Because Ci_model and Ci_controller both use DB instances of the Controller class, the method I chose is to introduce two database connections to the controller, dynamically parse the SQL query for the SELECT statement, and, if so, use a read-only connection. Other use of write connection, to achieve read, write separation, the disadvantage is the need to modify the core code, the system upgrade has some side effects.

The environment for this modification is:

• CodeIgniter 2.1.4

• MySQL 5.1+

• PHP 5.5.9

• Nginx 1.6.0

Step One: Modify application/config/database.php

Configuration of database Read and write connection parameters.

$active _record = true;if  (defined (' Environment ')) {switch  (environment) {case  ' Development ': $db [' Default '] [' hostname '] =  ' 127.0.0.1 '; $db [' Default '] [' username '] =  '; $db [ ' Default ' [' Password '] =  '; $db [' Default '] [' database '] =  '; $db [' Default '] [' dbdriver ']  =  ' mysqli '; $db [' Default '] [' dbprefix '] =  '; $db [' Default '] [' pconnect '] = false;$ db[' Default ' [' Db_debug '] = false; $db [' Default '] [' cache_on '] = false; $db [' Default '] [' Cachedir '] = $_server[' document_root ']. ' /cache/'; $db [' Default '] [' char_set '] =  ' UTF8 '; $db [' Default '] [' Dbcollat '] =  ' utf8_general_ci '; $db [' Default '] [' swap_pre '] =  '; $db [' Default '] [' autoinit '] = true; $db [' Default '] [' Stricton '] = false; $db [' Write '] [' hostname '] =  ' 127.0.0.1 '; $db [' Write '] [' username '] =   '; $db [' Write '] [' Password '] =  '; $db [' Write '] [' database '] =  '; $db ['Write ' [' dbdriver '] =  ' mysqli '; $db [' Write '] [' dbprefix '] =  '; $db [' Write '] [' pconnect ']  = FALSE; $db [' Write '] [' db_debug '] = false; $db [' Write '] [' cache_on '] = false; $db [' Write ' [' Cachedir '] = $_server[' document_root ']. ' /cache/'; $db [' Write '] [' char_set '] =  ' UTF8 '; $db [' Write '] [' Dbcollat '] =  ' utf8_general_ci '; $ db[' Write ' [' Swap_pre '] =  '; $db [' Write '] [' autoinit '] = true; $db [' Write '] [' Stricton ']  = FALSE; $db [' Crawl '] [' Stricton '] = false;break;case  ' testing ': $db [' Default '] [' Hostname '] =  ' 127.0.0.1 '; $db [' Default '] [' Port '] =  ' 3306 '; $db [' Default '] [' username '] =   '; $db [' Default '] [' Password '] =  '; $db [' Default '] [' database '] =  '; $db [' Default '] [' Dbdriver '] =  ' mysqli '; $db [' Default '] [' dbprefix '] =  '; $db [' Default '] ' Pconnect '] =  FALSE; $db [' Default '] [' db_debug '] = false; $db [' Default '] [' cache_on '] = false; $db [' Default '] [' cachedir '] = $_server[' document_root ']. ' /cache/'; $db [' Default '] [' char_set '] =  ' UTF8 '; $db [' Default '] [' Dbcollat '] =  ' utf8_general_ci '; $db [' Default '] [' swap_pre '] =  '; $db [' Default '] [' autoinit '] = true; $db [' Default '] [' Stricton '] = false; $db [' Write '] [' hostname '] =  '; $db [' Write '] [' Port '] =  ' 3306 '; $ db[' write ' [' username '] =  '; $db [' Write '] [' Password '] =  '; $db [' Write '] [' Database '] =   ' Caishenquan '; $db [' Write '] [' dbdriver '] =  ' mysqli '; $db [' Write '] [' dbprefix '] =  '; $db [ ' Write ' [' pconnect '] = false; $db [' Write '] [' db_debug '] = false; $db [' Write '] [' cache_on ']  = FALSE; $db [' Write '] [' cachedir '] = $_server[' document_root ']. ' /cache/'; $db [' Write '] [' char_set '] =  ' UTF8 '; $db [' Write '] [' Dbcollat '] =  ' utf8_general_ci '; $ db[' Write ' [' Swap_pre '] =  '; $db [' Write '] [' Autoinit '] =&NBsp TRUE; $db [' Write '] [' Stricton '] = false;break;case  ' production ': $db [' Default '] [' hostname '] =   '; $db [' Default '] [' username '] =  '; $db [' Default '] [' Password '] =  '; $db [' Default '] [' Database '] =  '; $db [' Default '] [' dbdriver '] =  ' mysqli '; $db [' Default '] [' Dbprefix '] =   '; $db [' Default '] [' pconnect '] = false; $db [' Default '] [' db_debug '] = false; $db [' Default ' [' cache_on '] = false; $db [' Default '] [' cachedir '] = $_server[' document_root ']. ' /cache/'; $db [' Default '] [' char_set '] =  ' UTF8 '; $db [' Default '] [' Dbcollat '] =  ' utf8_general_ci '; $db [' Default '] [' swap_pre '] =  '; $db [' Default '] [' autoinit '] = true; $db [' Default '] [' Stricton '] = false; $db [' Write '] [' hostname '] =  '; $db [' Write '] [' username '] =  '; $ db[' write ' [' Password '] =  '; $db [' Write '] [' database '] =  '; $db [' Write '] [' Dbdriver '] =   ' mysqli '; $db [' Write '[' Dbprefix '] =  '; $db [' Write '] [' pconnect '] = false; $db [' Write '] [' Db_debug '] =  FALSE; $db [' Write '] [' cache_on '] = false; $db [' Write '] [' cachedir '] = $_server[' document_root ' ].‘ /cache/'; $db [' Write '] [' char_set '] =  ' UTF8 '; $db [' Write '] [' Dbcollat '] =  ' utf8_general_ci '; $ db[' Write ' [' Swap_pre '] =  '; $db [' Write '] [' autoinit '] = true; $db [' Write '] [' Stricton ']  = FALSE; $db [' Crawl '] [' Stricton '] = false;break;default:exit (' the application  environment is not set correctly. ');}} $active _group =  ' default ';

Step two, modify the system/core/controller.php

Adding two DB instance variables

var $db _write = Null;var $db _read = null;

Step three, modify the system/core/load.php

Modifying a database instance Load method

Public function database ($params  =  ',  $return  = FALSE,  $active _record  = null) {   // grab the super object    $CI  = & get_instance ();    // do we even need to load the  database class?   if  (class_exists (' ci_db ')  AND  $return  ==  false and  $active _record == null and isset ($CI->db)  and is_object ($ CI->DB))    {      return FALSE;   }    require_once (BasePath. ' Database/db.php ');   if  ($return  === true)    {       return db ($params,  $active _record);   }   //  Initialize the db variable.  needed to prevent   // reference errors with some configurations    $CI->db  =  ';   // load the db class    $CI->db = & db ($params,  $active _record);    //read, write separate  by [email protected]     $CI->db_read =  $CI->db;    $CI->db_write = &  db ($this->get_write_dsn (), $active _record);}    /**    *  Get database connection (write mode) dsn    * driver:// username:[email protected]/database    *  @author  [email protected]     */   FUNCTION GET_WRITE_DSN ()    {        if  ( ! defined (' Environment ')  or ! file_exists ($file _ Path = apppath. ' config/'. Environment. ' /database.php '))     &Nbsp;  {           if  ( ! file_ Exists ($file _path = apppath. ' Config/database.php '))            {                show_error (' [get_write_dsn]The  Configuration file database.php does not exist. ');            }       }        include ($file _path);        if   ( ! isset ($db)  or count ($db)  == 0)         {           show_error (' [get_write_dsn]No  database connection settings were found in the database config  file. ');        }        $connection _arguments = array ( );        if (Isset ($db [' write '])        {             $connection _arguments =  $db [' Write '];       }       else        {            $connection _ arguments =  $db [' Default '];       }         $dsn _base =   $connection _arguments[' dbdriver ']. ':/ /'. $connection _arguments[' username '). ': '. $connection _arguments[' password ']. ' @ '. $connection _arguments[' hostname '). ' /'. $connection _arguments[' database '];       unset ($connection _arguments[' Dbdriver ']);       unset ($connection _arguments[' username ');        unset ($connection _ arguments[' password ');        unset ($connection _arguments[' hostname ');        unset ($connection _arguments[' database ');         $dsn _query =  $this->my_build_str ($connection _arguments);        return  $dsn _base '? '. $dsn _query;   }   function my_build_str ($arr)    {         $str = ";        foreach ($arr  as   $key and $val)        {             $str. = ($key. ' = '. Strval ($val). ' & ');       }        $str  =  Preg_replace ('/&$/', ', $str);       return  $str;    } 

Step four, modify the system/database/db_driver.php

Add a database connection to SQL Select Routing feature.

Function simple_query ($sql)    {      //depend on  database.php setting  $db [' Local '] [' Autoinit ']      if  ( !   $this->conn_id)       {           $this->initialize ($sql);       }      // Added by [email protected]      if (  $this->db_debug  ) {         echo  $sql. " <br/><br/>\r\n\r\n ";       }//    return $ This->_execute ($sql);         //read/write separation           $CI  =& get_instance ();         if ( Strstr (Strtolower ($sql), "select"))         {            return  $CI- >db_read->_execute ($sql);        }         else        {             return  $CI->db_write->_execute ($sql);         }   }

Step five, modify the system/core/codeingiter.php

To add the Close database connection feature

if (class_exists (' ci_db ') and Isset ($CI->db)) {$CI->db->close ();   Read/write separation by [email protected] $CI->db_write->close (); $CI->db_read->close ();}

Test

Readers can write a controller to observe the connection information of $this->db_read and $this->db_write instances (specific PHP debugging methods refer to the author's previous blog introduction), or to a master, from the data to operate the actual test results.

Summarize

This scheme in controller layer modification, adapt to the model layer and Controller layer database operation of the read, write separation, the old code basic without modification, add the appropriate adjustment, the connection pool or multi-master multi-Connection support is also possible, the disadvantage is that the code to modify the core functions, affect the upgrade.

Allow CodeIgniter to support database read and write separations