CI Master-Slave Database扩展适用于包括SAE在内的主从库

xueliang813 · 发表于 2013-3-7 11:26:10

本帖最后由 xueliang813 于 2013-3-7 11:32 编辑

最近项目需要，用了SAE，而SAE的数据库是一主多从的。现在把我的代码分享一下。

第一步，修改config/database.php

PHP复制代码

$active_group = 'master';
$active_record = TRUE;
//db_msater
$db['master']['hostname'] = 'localhost';
$db['master']['username'] = 'root';
$db['master']['password'] = 'root';
$db['master']['database'] = 'db_master';
$db['master']['port'] = '';
$db['master']['dbdriver'] = 'mysql';
$db['master']['dbprefix'] = '';
$db['master']['pconnect'] = FALSE;
$db['master']['db_debug'] = TRUE;
$db['master']['cache_on'] = FALSE;
$db['master']['cachedir'] = '';
$db['master']['char_set'] = 'utf8';
$db['master']['dbcollat'] = 'utf8_general_ci';
$db['master']['swap_pre'] = '';
$db['master']['autoinit'] = TRUE;
$db['master']['stricton'] = FALSE;

//db_slave
$db['slave']['hostname'] = 'localhost';
$db['slave']['username'] = 'root';
$db['slave']['password'] = 'root';
$db['slave']['database'] = 'db_slave';
$db['slave']['port'] = '';
$db['slave']['dbdriver'] = 'mysql';
$db['slave']['dbprefix'] = '';
$db['slave']['pconnect'] = FALSE;
$db['slave']['db_debug'] = TRUE;
$db['slave']['cache_on'] = FALSE;
$db['slave']['cachedir'] = '';
$db['slave']['char_set'] = 'utf8';
$db['slave']['dbcollat'] = 'utf8_general_ci';
$db['slave']['swap_pre'] = '';
$db['slave']['autoinit'] = TRUE;
$db['slave']['stricton'] = FALSE;

复制代码

说明：一定要设置$db['slave']['pconnect']为FALSE。如果设置了永久连接，就只能连接到第一次使用的数据库

第二步，新建文件 application/core/MY_Model.php

PHP复制代码

<?php

/**
* A base model with a series of CRUD functions (powered by CI's query builder),
* validation-in-model support, event callbacks and more.
*
* @link http://github.com/jamierumbelow/codeigniter-base-model
* @copyright Copyright (c) 2012, Jamie Rumbelow <http://jamierumbelow.net>
*/
class MY_Model extends CI_Model {
/* --------------------------------------------------------------
* VARIABLES
* ------------------------------------------------------------ */

/**
* This model's default database table. Automatically
* guessed by pluralising the model name.
*/
protected $_table;

/**
* Database conn object; will use default connection
* unless overridden
*/
protected $_db;

/**
* Master-Slave Database conn object;
*/
public $db_master;
public $db_slave;

/**
* This model's default primary key or unique identifier.
* Used by the get(), update() and delete() functions.
*/
protected $primary_key = 'id';

/**
* Support for soft deletes and this model's 'deleted' key
*/
protected $soft_delete = FALSE;
protected $soft_delete_key = 'deleted';
protected $_temporary_with_deleted = FALSE;

/**
* The various callbacks available to the model. Each are
* simple lists of method names (methods will be run on $this).
*/
protected $before_create = array();
protected $after_create = array();
protected $before_update = array();
protected $after_update = array();
protected $before_get = array();
protected $after_get = array();
protected $before_delete = array();
protected $after_delete = array();
protected $callback_parameters = array();

/**
* Protected, non-modifiable attributes
*/
protected $protected_attributes = array();

/**
* Relationship arrays. Use flat strings for defaults or string
* => array to customise the class name and primary key
*/
protected $belongs_to = array();
protected $has_many = array();
protected $_with = array();

/**
* An array of validation rules. This needs to be the same format
* as validation rules passed to the Form_validation library.
*/
protected $validate = array();

/**
* Optionally skip the validation. Used in conjunction with
* skip_validation() to skip data validation for any future calls.
*/
protected $skip_validation = FALSE;

/**
* By default we return our results as objects. If we need to override
* this, we can, or, we could use the `as_array()` and `as_object()` scopes.
*/
protected $return_type = 'object';
protected $_temporary_return_type = NULL;

/* --------------------------------------------------------------
* GENERIC METHODS
* ------------------------------------------------------------ */

/**
* Initialise the model, tie into the CodeIgniter superobject and
* try our best to guess the table name.
*/
public function __construct() {
parent::__construct();

$this->load->helper('inflector');

$this->_set_database();
$this->_fetch_table();

array_unshift($this->before_create, 'protect_attributes');
array_unshift($this->before_update, 'protect_attributes');

$this->_temporary_return_type = $this->return_type;
}

/* --------------------------------------------------------------
* CRUD INTERFACE
* ------------------------------------------------------------ */

/**
* Fetch a single record based on the primary key. Returns an object.
* @param int $primary_value
* @return type
*
* @example $this->your_model->get(1)
*/
public function get($primary_value) {
$this->trigger('before_get');

if ($this->soft_delete && $this->_temporary_with_deleted !== TRUE) {
$this->db_slave->where($this->soft_delete_key, FALSE);
}

$row = $this->db_slave->where($this->primary_key, $primary_value)
->get($this->_table)
->{$this->_return_type()}();
$this->_temporary_return_type = $this->return_type;

$row = $this->trigger('after_get', $row);

$this->_with = array();
return $row;
}

/**
* Fetch a single record based on an arbitrary WHERE call. Can be
* any valid value to $this->db_slave->where().
*
* @example $this->your_model->get_by('title','title')
*/
public function get_by() {
$where = func_get_args();
$this->_set_slave_where($where);

if ($this->soft_delete && $this->_temporary_with_deleted !== TRUE) {
$this->db_slave->where($this->soft_delete_key, FALSE);
}

$this->trigger('before_get');

$row = $this->db_slave->get($this->_table)
->{$this->_return_type()}();
$this->_temporary_return_type = $this->return_type;

$row = $this->trigger('after_get', $row);

$this->_with = array();
return $row;
}

/**
* Fetch an array of records based on an array of primary values.
* @param array $values
* @return type
*
* @example $this->your_model->get_many(array(1,2,3))
*/
public function get_many($values) {
if ($this->soft_delete && $this->_temporary_with_deleted !== TRUE) {
$this->db_slave->where($this->soft_delete_key, FALSE);
}

$this->db_slave->where_in($this->primary_key, $values);

return $this->get_all();
}

/**
* Fetch an array of records based on an arbitrary WHERE call.
*
* @example $this->your_model->get_many_by('title','title')
*/
public function get_many_by() {
$where = func_get_args();
$this->_set_slave_where($where);

if ($this->soft_delete && $this->_temporary_with_deleted !== TRUE) {
$this->db_slave->where($this->soft_delete_key, FALSE);
}

return $this->get_all();
}

/**
* Fetch all the records in the table. Can be used as a generic call
* to $this->db_slave->get() with scoped methods.
*/
public function get_all() {
$this->trigger('before_get');

if ($this->soft_delete && $this->_temporary_with_deleted !== TRUE) {
$this->db_slave->where($this->soft_delete_key, FALSE);
}

$result = $this->db_slave->get($this->_table)
->{$this->_return_type(1)}();
$this->_temporary_return_type = $this->return_type;

foreach ($result as $key => &$row) {
$row = $this->trigger('after_get', $row, ($key == count($result) - 1));
}

$this->_with = array();
return $result;
}

/**
* Insert a new row into the table. $data should be an associative array
* of data to be inserted. Returns newly created ID.
*
* @param array $data
* @param bool $skip_validation
* @return int
* @example $this->your_model->insert(array('title'=>'title','body'=>'body'),true) description
*/
public function insert($data, $skip_validation = FALSE) {
$valid = TRUE;

if ($skip_validation === FALSE) {
$data = $this->validate($data);
}

if ($data !== FALSE) {
$data = $this->trigger('before_create', $data);

$this->db_master->insert($this->_table, $data);
$insert_id = $this->db_master->insert_id();

$this->trigger('after_create', $insert_id);

return $insert_id;
} else {
return FALSE;
}
}

/**
* Insert multiple rows into the table. Returns an array of multiple IDs.
* @param array $data
* @param bool $skip_validation
* @return array
* @example $this->your_model->insert_many(
* array(array('title'=>'title1', 'body'=>'body1), array('title'=>'title2', 'body'=>'body2')),
* true
* )
*/
public function insert_many($data, $skip_validation = FALSE) {
$ids = array();

foreach ($data as $key => $row) {
$ids[] = $this->insert($row, $skip_validation, ($key == count($data) - 1));
}

return $ids;
}

/**
* Updated a record based on the primary value.
* @param int $primary_value
* @param array $data
* @param bool $skip_validation
* @return int
* @example $this->your_model->update(1, array('title'=>'newtitle','body'=>'body'),true)
*/
public function update($primary_value, $data, $skip_validation = FALSE) {
$valid = TRUE;

$data = $this->trigger('before_update', $data);

if ($skip_validation === FALSE) {
$data = $this->validate($data);
}

if ($data !== FALSE) {
$result = $this->db_master->where($this->primary_key, $primary_value)
->set($data)
->update($this->_table);

$this->trigger('after_update', array($data, $result));

return $result;
} else {
return FALSE;
}
}

/**
* Update many records, based on an array of primary values.
* @param array $primary_values
* @param array $data
* @param bool $skip_validation
* @return boolean
*
* @example $this->your_model->update_many(array(1,2,3),array('role','1'))
*/
public function update_many($primary_values, $data, $skip_validation = FALSE) {
$data = $this->trigger('before_update', $data);

if ($skip_validation === FALSE) {
$data = $this->validate($data);
}

if ($data !== FALSE) {
$result = $this->db_master->where_in($this->primary_key, $primary_values)
->set($data)
->update($this->_table);

$this->trigger('after_update', array($data, $result));

return $result;
} else {
return FALSE;
}
}

/**
* Updated a record based on an arbitrary WHERE clause.
* @param array $where
* @param array $data
* @example $this->your_model->update_by(array('title'=>'title'),array('body'=>'body'))
*/
public function update_by() {
$args = func_get_args();
$data = array_pop($args);
$this->_set_master_where($args);

$data = $this->trigger('before_update', $data);

if ($this->validate($data) !== FALSE) {
$result = $this->db_master->set($data)
->update($this->_table);
$this->trigger('after_update', array($data, $result));

return $result;
} else {
return FALSE;
}
}

/**
* Update all records
* @param array $data
* @example $this->your_model->update_all(array('published'=>'1'))
*/
public function update_all($data) {
$data = $this->trigger('before_update', $data);
$result = $this->db_master->set($data)
->update($this->_table);
$this->trigger('after_update', array($data, $result));

return $result;
}

/**
* Delete a row from the table by the primary value
* @param int $id id
*/
public function delete($id) {
$this->trigger('before_delete', $id);

$this->db_master->where($this->primary_key, $id);

if ($this->soft_delete) {
$result = $this->db_master->update($this->_table, array($this->soft_delete_key => TRUE));
} else {
$result = $this->db_master->delete($this->_table);
}

$this->trigger('after_delete', $result);

return $result;
}

/**
* Delete a row from the database table by an arbitrary WHERE clause
* @example $this->your_model->delete_by(array('title'=>'gaivn'))
*/
public function delete_by() {
$where = func_get_args();
$this->_set_master_where($where);

$where = $this->trigger('before_delete', $where);

if ($this->soft_delete) {
$result = $this->db_master->update($this->_table, array($this->soft_delete_key => TRUE));
} else {
$result = $this->db_master->delete($this->_table);
}

$this->trigger('after_delete', $result);

return $result;
}

/**
* Delete many rows from the database table by multiple primary values
* @param array $primary_values 主键数组
* @example $this->your_model->delete_many(array(1,2,3))
*/
public function delete_many($primary_values) {
$primary_values = $this->trigger('before_delete', $primary_values);

$this->db_master->where_in($this->primary_key, $primary_values);

if ($this->soft_delete) {
$result = $this->db_master->update($this->_table, array($this->soft_delete_key => TRUE));
} else {
$result = $this->db_master->delete($this->_table);
}

$this->trigger('after_delete', $result);

return $result;
}

/**
* Truncates the table 清空表
*/
public function truncate() {
$result = $this->db_master->truncate($this->_table);

return $result;
}

/* --------------------------------------------------------------
* RELATIONSHIPS
* ------------------------------------------------------------ */

public function with($relationship) {
$this->_with[] = $relationship;

if (!in_array('relate', $this->after_get)) {
$this->after_get[] = 'relate';
}

return $this;
}

public function relate($row) {
foreach ($this->belongs_to as $key => $value) {
if (is_string($value)) {
$relationship = $value;
$options = array('primary_key' => $value . '_id', 'model' => $value . '_model');
} else {
$relationship = $key;
$options = $value;
}

if (in_array($relationship, $this->_with)) {
$this->load->model($options['model']);
if (is_object($row)) {
$row->{$relationship} = $this->{$options['model']}->get($row->{$options['primary_key']});
} else {
$row[$relationship] = $this->{$options['model']}->get($row[$options['primary_key']]);
}
}
}

foreach ($this->has_many as $key => $value) {
if (is_string($value)) {
$relationship = $value;
$options = array('primary_key' => singular($this->_table) . '_id', 'model' => singular($value) . '_model');
} else {
$relationship = $key;
$options = $value;
}

if (in_array($relationship, $this->_with)) {
$this->load->model($options['model']);
if (is_object($row)) {
$row->{$relationship} = $this->{$options['model']}->get_many_by($options['primary_key'], $row->{$this->primary_key});
} else {
$row[$relationship] = $this->{$options['model']}->get_many_by($options['primary_key'], $row[$this->primary_key]);
}
}
}

return $row;
}

/* --------------------------------------------------------------
* UTILITY METHODS
* ------------------------------------------------------------ */

/**
* Retrieve and generate a form_dropdown friendly array
*/
function dropdown() {
$args = func_get_args();

if (count($args) == 2) {
list($key, $value) = $args;
} else {
$key = $this->primary_key;
$value = $args[0];
}

$this->trigger('before_dropdown', array($key, $value));

if ($this->soft_delete && $this->_temporary_with_deleted !== TRUE) {
$this->db_slave->where($this->soft_delete_key, FALSE);
}

$result = $this->db_slave->select(array($key, $value))
->get($this->_table)
->result();

$options = array();

foreach ($result as $row) {
$options[$row->{$key}] = $row->{$value};
}

$options = $this->trigger('after_dropdown', $options);

return $options;
}

/**
* Fetch a count of rows based on an arbitrary WHERE call.
*/
public function count_by() {
$where = func_get_args();
$this->_set_slave_where($where);

return $this->db_slave->count_all_results($this->_table);
}

/**
* Fetch a total count of rows, disregarding any previous conditions
*/
public function count_all() {
return $this->db_slave->count_all($this->_table);
}

/**
* Tell the class to skip the insert validation
*/
public function skip_validation() {
$this->skip_validation = TRUE;
return $this;
}

/**
* Get the skip validation status
*/
public function get_skip_validation() {
return $this->skip_validation;
}

/**
* Return the next auto increment of the table. Only tested on MySQL.
*/
public function get_next_id() {
return (int) $this->db_slave->select('AUTO_INCREMENT')
->from('information_schema.TABLES')
->where('TABLE_NAME', $this->_table)
->where('TABLE_SCHEMA', $this->db_slave->database)->get()->row()->AUTO_INCREMENT;
}

/**
* Getter for the table name
*/
public function table() {
return $this->_table;
}

/* --------------------------------------------------------------
* GLOBAL SCOPES
* ------------------------------------------------------------ */

/**
* Return the next call as an array rather than an object
*/
public function as_array() {
$this->_temporary_return_type = 'array';
return $this;
}

/**
* Return the next call as an object rather than an array
*/
public function as_object() {
$this->_temporary_return_type = 'object';
return $this;
}

/**
* Don't care about soft deleted rows on the next call
*/
public function with_deleted() {
$this->_temporary_with_deleted = TRUE;
return $this;
}

/* --------------------------------------------------------------
* OBSERVERS
* ------------------------------------------------------------ */

/**
* MySQL DATETIME created_at and updated_at
*/
public function created_at($row) {
if (is_object($row)) {
$row->created_at = date('Y-m-d H:i:s');
} else {
$row['created_at'] = date('Y-m-d H:i:s');
}

return $row;
}

public function updated_at($row) {
if (is_object($row)) {
$row->updated_at = date('Y-m-d H:i:s');
} else {
$row['updated_at'] = date('Y-m-d H:i:s');
}

return $row;
}

/**
* Serialises data for you automatically, allowing you to pass
* through objects and let it handle the serialisation in the background
*/
public function serialize($row) {
foreach ($this->callback_parameters as $column) {
$row[$column] = serialize($row[$column]);
}

return $row;
}

public function unserialize($row) {
foreach ($this->callback_parameters as $column) {
if (is_array($row)) {
$row[$column] = unserialize($row[$column]);
} else {
$row->$column = unserialize($row->$column);
}
}

return $row;
}

/**
* Protect attributes by removing them from $row array
*/
public function protect_attributes($row) {
foreach ($this->protected_attributes as $attr) {
if (is_object($row)) {
unset($row->$attr);
} else {
unset($row[$attr]);
}
}

return $row;
}

/* --------------------------------------------------------------
* QUERY BUILDER DIRECT ACCESS METHODS
* ------------------------------------------------------------ */

/**
* A wrapper to $this->db_slave->order_by()
*/
public function order_by($criteria, $order = 'ASC') {
if (is_array($criteria)) {
foreach ($criteria as $key => $value) {
$this->db_slave->order_by($key, $value);
}
} else {
$this->db_slave->order_by($criteria, $order);
}
return $this;
}

/**
* A wrapper to $this->db_slave->limit()
*/
public function limit($limit, $offset = 0) {
$this->db_slave->limit($limit, $offset);
return $this;
}

/* --------------------------------------------------------------
* INTERNAL METHODS
* ------------------------------------------------------------ */

/**
* Trigger an event and call its observers. Pass through the event name
* (which looks for an instance variable $this->event_name), an array of
* parameters to pass through and an optional 'last in interation' boolean
*/
public function trigger($event, $data = FALSE, $last = TRUE) {
if (isset($this->$event) && is_array($this->$event)) {
foreach ($this->$event as $method) {
if (strpos($method, '(')) {
preg_match('/([a-zA-Z0-9\_\-]+)($([a-zA-Z0-9\_\-\., ]+)$)?/', $method, $matches);

$method = $matches[1];
$this->callback_parameters = explode(',', $matches[3]);
}

$data = call_user_func_array(array($this, $method), array($data, $last));
}
}

return $data;
}

/**
* Run validation on the passed data
*/
public function validate($data) {
if ($this->skip_validation) {
return $data;
}

if (!empty($this->validate)) {
foreach ($data as $key => $val) {
$_POST[$key] = $val;
}

$this->load->library('form_validation');

if (is_array($this->validate)) {
$this->form_validation->set_rules($this->validate);

if ($this->form_validation->run() === TRUE) {
return $data;
} else {
return FALSE;
}
} else {
if ($this->form_validation->run($this->validate) === TRUE) {
return $data;
} else {
return FALSE;
}
}
} else {
return $data;
}
}

/**
* Guess the table name by pluralising the model name
*/
private function _fetch_table() {
if ($this->_table == NULL) {
$this->_table = plural(preg_replace('/(_m|_model)?$/', '', strtolower(get_class($this))));
}
}

/* --------------------------------------------------------------
* MULTIPLE DB LOADING
* ------------------------------------------------------------ */

private function _set_database() {
$this->db_master = $this->load->database('master', TRUE);
$this->db_slave = $this->load->database('slave', TRUE);
}

/**
* Set read WHERE parameters
*/
protected function _set_slave_where($params) {
if (count($params) == 1) {
$this->db_slave->where($params[0]);
} else {
$this->db_slave->where($params[0], $params[1]);
}
}

/**
* Set write WHERE parameters
*/
protected function _set_master_where($params) {
if (count($params) == 1) {
$this->db_master->where($params[0]);
} else {
$this->db_master->where($params[0], $params[1]);
}
}

/**
* Return the method name for the current return type
*/
protected function _return_type($multi = FALSE) {
$method = ($multi) ? 'result' : 'row';
return $this->_temporary_return_type == 'array' ? $method . '_array' : $method;
}

}

/* End of file MY_Model.php */
/* Location: ./system/core/MY_Model.php */

复制代码

说明：基于国外同行分享的base_model修改的，类中的方法跟原作者保持一致，如果有已经用了这个base_model的朋友可以直接使用，不影响。详细说明见代码中的@example或者 http://github.com/jamierumbelow/codeigniter-base-model

第三步在模型中继承MY_Model, 例如 page_model.php

PHP复制代码

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Page_model extends MY_Model {

public $_table = 'cms_pages';//自定义表名

function __construct() {
parent::__construct();
}

}

复制代码

控制器中的使用或者模型中自定义方法就没有说了。

kuailewang · 发表于 2013-3-7 11:46:28

不错，值得学习

浪迹天涯 · 发表于 2013-3-7 14:39:42

很好啊！收藏再详细看看

xueliang813 · 发表于 2013-3-8 10:09:13

谢谢两位的支持，之前我也基本都是只从开源社区索取而很少贡献代码，最近感觉中国的污染都污染到根了很是伤心，我认为跟我们大家只索取不贡献的心态很有关系。作为一个多年的PHP程序员，享受开源的好处已久可并没有对开源社区做多少贡献，很是惭愧，所以以后只要有时间就把自己的代码/app或者国外的资源翻译翻译贡献到社区。个人能力有限，希望大家多多指教。

Hex · 发表于 2013-3-8 10:25:51

支持楼主的分享精神，大家每个人分享一点，分享的人多了，就是一股强大的力量~

0377 · 发表于 2014-3-20 17:14:13

如果我没有建模型，需要咋办呢？直接在控制器中和视图中进行的

		自动登录	找回密码
密码			入住 CI 中国社区

[核心代码 Core] CI Master-Slave Database扩展适用于包括SAE在内的主从库

评分