【www.bbyears.com--mysql教程】
实现cakePHP读写分离,首先配置数据库连接
app/Config/database.php
class DATABASE_CONFIG {
//可读可写权限
public $default = array (
"datasource" => "Database/Mysql",
"persistent" => false, // 是否使用持久化
"host" => "localhost",
"login" => "root",
"password" => "1234",
"database" => "shop",
// "prefix" => "app_",
"encoding" => "utf8"
);
//只读权限
public $readonly = array (
"datasource" => "Database/Mysql",
"persistent" => false, // 是否使用持久化
"host" => "localhost",
"login" => "root2",
"password" => "123456",
"database" => "shop",
// "prefix" => "app_",
"encoding" => "utf8"
);
}
然后修改model的父文件 app\Model\AppModel.php,
/**
* Application model for Cake.
*
* This file is application-wide model file. You can put all
* application-wide model-related methods here.
*
* PHP 5
*
* CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
* Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
*
* Licensed under The MIT License
* Redistributions of files must retain the above copyright notice.
*
* @copyright Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
* @link http://cakephp.org CakePHP(tm) Project
* @package app.Model
* @since CakePHP(tm) v 0.2.9
* @license MIT License (http://www.opensource.org/licenses/mit-license.php)
*/
App::uses("Model", "Model");
/**
* Application model for Cake.
*
* Add your application-wide methods in the class below, your models
* will inherit them.
*
* @package app.Model
*/
class AppModel extends Model {
//合并为一个sql 插入数据库
//(如果是字符串类型,必须提前用单引号把内容引起来,否则当成了int类型处理)
public function createMany($data){
$ca_sql="INSERT INTO ".$this->useTable;
$ca_sql_arr=array();
$keys=array();
foreach($data as $k=>$v){
if(empty($keys)){
$a_keys = array_keys($v);
foreach($a_keys as $kv){
$keys[]="`".$kv."`";
}
}
$ca_sql_arr[]="(".implode(",", $v).")";
}
if(!empty($ca_sql_arr)){
$sql=$ca_sql." (".implode(",", $keys).") VALUES".implode(",", $ca_sql_arr);
$this->query($sql);
}
}
/**
* 直接执行sql语句,如果是已select开头,则默认使用只读实例
* */
public function query($sql) {
$sindex = stripos($sql,"select");
if($sindex ===0){
$this->beforeFind(null);
}
$params = func_get_args();
$db = $this->getDataSource();
$results= call_user_func_array(array(&$db, "query"), $params);
return $this->afterFind($results);
}
/**查询数据之前,切换为只读数据库**/
public function beforeFind($queryData) {
$this->useDbConfig="readonly";
return true;
}
/**查询数据之后,还原为可读可写,防止之后的查询权限不够**/
public function afterFind($results, $primary = false) {
$this->useDbConfig="default";
return $results;
}
/**强制设置为可读可写权限,防止权限不够**/
public function beforeSave($options = array()) {
$this->useDbConfig="default";
return true;
}
/**强制设置为可读可写权限,防止权限不够**/
public function beforeDelete($cascade = true) {
$this->useDbConfig="default";
return true;
}
}
上面的操作仅限单表操作,如果模型中,包含 belongsTo hasOne hasMany hasAndBelongsToMany,在查询的时候,无法连接(join)查询
因为在lib\Cake\Model\Datasource\DboSource.php 中的read 方法中判断了两个的数据库是否一致,
foreach ($_associations as $type) {
foreach ($model->{$type} as $assoc => $assocData) {
$linkModel = $model->{$assoc};
$external = isset($assocData["external"]);
$linkModel->getDataSource();
if ($model->useDbConfig === $linkModel->useDbConfig) {//这里判断,主model和关联model的数据库一致,才关联查询
if ($bypass) {
$assocData["fields"] = false;
}
if (true === $this->generateAssociationQuery($model, $linkModel, $type, $assoc, $assocData, $queryData, $external, $null)) {
$linkedModels[$type . "/" . $assoc] = true;
}
}
}
}
因此,解决数据库不一致才是关键
由于有这种关联关系的model,他们必定是同一个数据库中的数据,因此,可以关联设置model的数据源
foreach ($_associations as $type) {
foreach ($model->{$type} as $assoc => $assocData) {
$linkModel = $model->{$assoc};
$external = isset($assocData["external"]);
//@2015-12-10 修改如果关联模型的数据库不一致,则默认设置为主数据库
if($model->useDbConfig !== $linkModel->useDbConfig){
$linkModel->setDataSource($model->useDbConfig);//强制设置关联model的数据源跟主model一致
}
$linkModel->getDataSource();
if ($model->useDbConfig === $linkModel->useDbConfig) {
if ($bypass) {
$assocData["fields"] = false;
}
if (true === $this->generateAssociationQuery($model, $linkModel, $type, $assoc, $assocData, $queryData, $external, $null)) {
$linkedModels[$type . "/" . $assoc] = true;
}
}
}
}
修改lib/Cake/Model/Model.php 中save方法
由于在save之前,系统会主动查询一次数据库,所以把useDbConfig切换成了readonly
public function save($data = null, $validate = true, $fieldList = array()) {
$defaults = array("validate" => true, "fieldList" => array(), "callbacks" => true);
$_whitelist = $this->whitelist;
$fields = array();
if (!is_array($validate)) {
$options = array_merge($defaults, compact("validate", "fieldList", "callbacks"));
} else {
$options = array_merge($defaults, $validate);
}
if (!empty($options["fieldList"])) {
if (!empty($options["fieldList"][$this->alias]) && is_array($options["fieldList"][$this->alias])) {
$this->whitelist = $options["fieldList"][$this->alias];
} else {
$this->whitelist = $options["fieldList"];
}
} elseif ($options["fieldList"] === null) {
$this->whitelist = array();
}
$this->set($data);
if (empty($this->data) && !$this->hasField(array("created", "updated", "modified"))) {
return false;
}
foreach (array("created", "updated", "modified") as $field) {
$keyPresentAndEmpty = (
isset($this->data[$this->alias]) &&
array_key_exists($field, $this->data[$this->alias]) &&
$this->data[$this->alias][$field] === null
);
if ($keyPresentAndEmpty) {
unset($this->data[$this->alias][$field]);
}
}
$exists = $this->exists();//执行了一次查询操作,数据库已经切换为readonly,
$dateFields = array("modified", "updated");
if (!$exists) {
$dateFields[] = "created";
}
if (isset($this->data[$this->alias])) {
$fields = array_keys($this->data[$this->alias]);
}
if ($options["validate"] && !$this->validates($options)) {
$this->whitelist = $_whitelist;
return false;
}
$this->beforeSave(null);//强制切换为默认数据库(可读可写)
$db = $this->getDataSource();
foreach ($dateFields as $updateCol) {
if ($this->hasField($updateCol) && !in_array($updateCol, $fields)) {
$default = array("formatter" => "date");
$colType = array_merge($default, $db->columns[$this->getColumnType($updateCol)]);
if (!array_key_exists("format", $colType)) {
$time = strtotime("now");
} else {
$time = $colType["formatter"]($colType["format"]);
}
if (!empty($this->whitelist)) {
$this->whitelist[] = $updateCol;
}
$this->set($updateCol, $time);
}
}
if ($options["callbacks"] === true || $options["callbacks"] === "before") {
$event = new CakeEvent("Model.beforeSave", $this, array($options));
list($event->break, $event->breakOn) = array(true, array(false, null));
$this->getEventManager()->dispatch($event);
if (!$event->result) {
$this->whitelist = $_whitelist;
return false;
}
}
if (empty($this->data[$this->alias][$this->primaryKey])) {
unset($this->data[$this->alias][$this->primaryKey]);
}
$fields = $values = array();
foreach ($this->data as $n => $v) {
if (isset($this->hasAndBelongsToMany[$n])) {
if (isset($v[$n])) {
$v = $v[$n];
}
$joined[$n] = $v;
} else {
if ($n === $this->alias) {
foreach (array("created", "updated", "modified") as $field) {
if (array_key_exists($field, $v) && empty($v[$field])) {
unset($v[$field]);
}
}
foreach ($v as $x => $y) {
if ($this->hasField($x) && (empty($this->whitelist) || in_array($x, $this->whitelist))) {
list($fields[], $values[]) = array($x, $y);
}
}
}
}
}
$count = count($fields);
if (!$exists && $count > 0) {
$this->id = false;
}
$success = true;
$created = false;
if ($count > 0) {
$cache = $this->_prepareUpdateFields(array_combine($fields, $values));
if (!empty($this->id)) {
$success = (bool)$db->update($this, $fields, $values);
} else {
$fInfo = $this->schema($this->primaryKey);
$isUUID = ($fInfo["length"] == 36 &&
($fInfo["type"] === "string" || $fInfo["type"] === "binary")
);
if (empty($this->data[$this->alias][$this->primaryKey]) && $isUUID) {
if (array_key_exists($this->primaryKey, $this->data[$this->alias])) {
$j = array_search($this->primaryKey, $fields);
$values[$j] = String::uuid();
} else {
list($fields[], $values[]) = array($this->primaryKey, String::uuid());
}
}
if (!$db->create($this, $fields, $values)) {
$success = $created = false;
} else {
$created = true;
}
}
if ($success && !empty($this->belongsTo)) {
$this->updateCounterCache($cache, $created);
}
}
if (!empty($joined) && $success === true) {
$this->_saveMulti($joined, $this->id, $db);
}
if ($success && $count > 0) {
if (!empty($this->data)) {
$success = $this->data;
if ($created) {
$this->data[$this->alias][$this->primaryKey] = $this->id;
}
}
if ($options["callbacks"] === true || $options["callbacks"] === "after") {
$event = new CakeEvent("Model.afterSave", $this, array($created, $options));
$this->getEventManager()->dispatch($event);
}
if (!empty($this->data)) {
$success = Set::merge($success, $this->data);
}
$this->data = false;
$this->_clearCache();
$this->validationErrors = array();
}
$this->whitelist = $_whitelist;
return $success;
}
同理,预防在savemany之前查询了数据库,现在强制切换数据库连接
public function saveMany($data = null, $options = array()) {
if (empty($data)) {
$data = $this->data;
}
$options = array_merge(array("validate" => "first", "atomic" => true, "deep" => false), $options);
$this->validationErrors = $validationErrors = array();
if (empty($data) && $options["validate"] !== false) {
$result = $this->save($data, $options);
return !empty($result);
}
if ($options["validate"] === "first") {
$validates = $this->validateMany($data, $options);
if ((!$validates && $options["atomic"]) || (!$options["atomic"] && in_array(false, $validates, true))) {
return $validates;
}
$options["validate"] = true;
}
if ($options["atomic"]) {
$this->beforeSave(null);//强制设置数据库连接
$db = $this->getDataSource();
$transactionBegun = $db->begin();
}
$return = array();
foreach ($data as $key => $record) {
$validates = $this->create(null) !== null;
$saved = false;
if ($validates) {
if ($options["deep"]) {
$saved = $this->saveAssociated($record, array_merge($options, array("atomic" => false)));
} else {
$saved = $this->save($record, $options);
}
}
$validates = ($validates && ($saved === true || (is_array($saved) && !in_array(false, $saved, true))));
if (!$validates) {
$validationErrors[$key] = $this->validationErrors;
}
if (!$options["atomic"]) {
$return[$key] = $validates;
} elseif (!$validates) {
break;
}
}
$this->validationErrors = $validationErrors;
if (!$options["atomic"]) {
return $return;
}
if ($validates) {
if ($transactionBegun) {
return $db->commit() !== false;
} else {
return true;
}
}
$db->rollback();
return false;
}