这里详细介绍下SugarCRM里的数据库连接相关逻辑,数据库的相关配置在config.php中的dbconfig和dbconfigoption数据项。
'dbconfig' => array (
'db_host_name' => 'localhost',
'db_host_instance' => 'SQLEXPRESS',
'db_user_name' => 'root',
'db_password' => '123456',
'db_name' => 'sugarcrm',
'db_type' => 'mysql',
'db_port' => '',
// 这个配置项,主要是选择当前的数据库连接类型
// ./include/database可以查看
'db_manager' => 'MysqliManager',
),
'dbconfigoption' => array (
'persistent' => false,
'autofree' => false,
'debug' => 0,
'ssl' => false,
'collation' => 'utf8_general_ci',
)
接下来便分析下数据库连接过程
// ./include/entryPoint.php
require_once('include/database/DBManagerFactory.php');
require_once('data/SugarBean.php');
// ./include/entryPoint.php
// 获取数据库实例
$db = DBManagerFactory::getInstance();
// 重置数据库连接次数
$db->resetQueryCount();
/*
./include/database/DBManager.php
public static function resetQueryCount() {
self::$queryCount = 0;
}
*/
// ./include/database/DBManagerFactory.php'
public static function getInstance($instanceName = '') {
global $sugar_config;
static $count = 0, $old_count = 0;
/*
'dbconfig' => array (
'db_host_name' => 'localhost',
'db_host_instance' => 'SQLEXPRESS',
'db_user_name' => 'root',
'db_password' => '123456',
'db_name' => 'sugarcrm',
'db_type' => 'mysql',
'db_port' => '',
'db_manager' => 'MysqliManager',
),
'dbconfigoption' => array (
'persistent' => false,
'autofree' => false,
'debug' => 0,
'ssl' => false,
'collation' => 'utf8_general_ci',
)
*/
if (empty($sugar_config['dbconfig'])) {
return false;
}
// fall back to the default instance name
if (empty($sugar_config['db'][$instanceName])) {
$instanceName = '';
}
if (!isset(self::$instances[$instanceName])) {
$config = $sugar_config['dbconfig'];
$count++;
// 返回具体数据库操作类
self::$instances[$instanceName] = self::getTypeInstance($config['db_type'], $config);
// 设置数据库连接基本信息,主要是字符编码统一为utf8_general_ci
if (!empty($sugar_config['dbconfigoption'])) {
self::$instances[$instanceName]->setOptions($sugar_config['dbconfigoption']);
}
// 连接数据库,并设置此连接相关属性
self::$instances[$instanceName]->connect($config, true);
self::$instances[$instanceName]->count_id = $count;
self::$instances[$instanceName]->references = 0;
// $GLOBALS['db']设置为当前的数据库连接
if (empty($instanceName) && empty($GLOBALS['db'])) {
$GLOBALS['db'] = self::$instances[$instanceName];
}
// 在./include/entryPoint.php中$system_config = Administration::getSettings();
if (empty($instanceName) && !empty($GLOBALS['system_config']) && $GLOBALS['system_config'] instanceof Administration && empty($GLOBALS['system_config']->db)) {
$GLOBALS['system_config']->db = self::$instances[$instanceName];
}
} else {
$old_count++;
self::$instances[$instanceName]->references = $old_count;
}
return self::$instances[$instanceName];
}
// 获取数据库类型实例
/*
$type = 'mysql';
$config = array (
'db_host_name' => 'localhost',
'db_host_instance' => 'SQLEXPRESS',
'db_user_name' => 'root',
'db_password' => '123456',
'db_name' => 'sugarcrm',
'db_type' => 'mysql',
'db_port' => '',
'db_manager' => 'MysqliManager',
);
*/
public static function getTypeInstance($type, $config = array()) {
global $sugar_config;
// MysqliManager
if (empty($config['db_manager'])) {
// standard types
switch ($type) {
case "mysql":
if (empty($sugar_config['mysqli_disabled']) && function_exists('mysqli_connect')) {
$my_db_manager = 'MysqliManager';
} else {
$my_db_manager = "MysqlManager";
}
break;
case "mssql":
if (function_exists('sqlsrv_connect') && (empty($config['db_mssql_force_driver']) || $config['db_mssql_force_driver'] == 'sqlsrv' )) {
$my_db_manager = 'SqlsrvManager';
} elseif (self::isFreeTDS() && (empty($config['db_mssql_force_driver']) || $config['db_mssql_force_driver'] == 'freetds' )) {
$my_db_manager = 'FreeTDSManager';
} else {
$my_db_manager = 'MssqlManager';
}
break;
default:
$my_db_manager = self::getManagerByType($type, false);
if (empty($my_db_manager)) {
echo $type . "\n";
display_stack_trace();
$GLOBALS['log']->fatal("unable to load DB manager for: $type");
sugar_die("Cannot load DB manager");
}
}
} else {
$my_db_manager = $config['db_manager'];
}
// sanitize the name
// 过滤非法字符
$my_db_manager = preg_replace("/[^A-Za-z0-9_-]/", "", $my_db_manager);
// 从配置中获取db_manager_class,默认为空
// 走MysqliManager数据库操作逻辑
// include/database/MysqliManager.php
if (!empty($config['db_manager_class'])) {
$my_db_manager = $config['db_manager_class'];
} else {
SugarAutoLoader::requireWithCustom("include/database/{$my_db_manager}.php");
}
// 实例化MysqliManager类
if (class_exists($my_db_manager)) {
return new $my_db_manager();
} else {
return null;
}
}
./include/database/MysqliManager.php
// MysqlManager又继承了DBManager
class MysqliManager extends MysqlManager {
public function __construct() {
// 执行的是DBManager的__construct方法
parent::__construct();
$this->capabilities["recursive_query"] = true;
$this->capabilities["prepared_statements"] = true;
}
}
./include/database/DBManager.php
abstract class DBManager {
public function __construct() {
// 设置时区,entryPoint.php引入的require_once('include/TimeDate.php');
$this->timedate = TimeDate::getInstance();
// $GLOBALS['log']日志操作类
$this->log = $GLOBALS['log'];
// compatibility,看了DBHelper.php介绍都移到本类了
$this->helper = $this;
// 若是从index.php->entryPoint.php过来的话,ENTRY_POINT_TYPE为gui
if (defined('ENTRY_POINT_TYPE') && constant('ENTRY_POINT_TYPE') == 'api') {
$this->encode = false;
}
}
}
接着着重分析下connect过程,相信看完之后一定有收获
/**
* @see DBManager::connect()
*/
public function connect(array $configOptions = null, $dieOnError = false) {
// 这两步应该是兼容写法,当直接连接时,可以不用传参数,只需在配置文件里配置好
// 数据库信息
global $sugar_config;
if (is_null($configOptions))
$configOptions = $sugar_config['dbconfig'];
// 首次连接肯定没有
if (!isset($this->database)) {
//mysqli connector has a separate parameter for port.. We need to separate it out from the host name
// 这一步可以看出,db_host_name配置可以这么写localhost:3306
$dbhost = $configOptions['db_host_name'];
$dbport = null;
$pos = strpos($configOptions['db_host_name'], ':');
if ($pos !== false) {
$dbhost = substr($configOptions['db_host_name'], 0, $pos);
$dbport = substr($configOptions['db_host_name'], $pos + 1);
}
// 是否持久化连接
if (ini_get('mysqli.allow_persistent') && $this->getOption('persistent')) {
$dbhost = "p:" . $dbhost;
}
// 连接数据库,并放入$this->database中,下次做数据库操作时
// 可以判断下此变量
$this->database = mysqli_connect($dbhost, $configOptions['db_user_name'], $configOptions['db_password'], isset($configOptions['db_name']) ? $configOptions['db_name'] : '', $dbport);
// 数据库连接失败,必须返回错误,并停止程序执行
if (empty($this->database)) {
$GLOBALS['log']->fatal("Could not connect to DB server " . $dbhost . " as " . $configOptions['db_user_name'] . ". port " . $dbport . ": " . mysqli_connect_error());
if ($dieOnError) {
// $GLOBALS['app_strings']存放的是语言包数组
if (isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
} else {
sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
}
} else {
return false;
}
}
}
// 如果连接数据库成功,但是没有数据库认为报错并停止程序执行
if (!empty($configOptions['db_name']) && !@mysqli_select_db($this->database, $configOptions['db_name'])) {
$GLOBALS['log']->fatal("Unable to select database {$configOptions['db_name']}: " . mysqli_connect_error());
if ($dieOnError) {
if (isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
} else {
sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
}
} else {
return false;
}
}
// cn: using direct calls to prevent this from spamming the Logs
// 设置字符集
mysqli_query($this->database, "SET CHARACTER SET utf8");
$names = "SET NAMES 'utf8'";
// 这一步是从self::$instances[$instanceName]->setOptions($sugar_config['dbconfigoption']);
// 获取数据的,也就是调用connect的上一步
$collation = $this->getOption('collation');
if (!empty($collation)) {
$names .= " COLLATE '$collation'";
}
mysqli_query($this->database, $names);
if ($this->checkError('Could Not Connect', $dieOnError))
$GLOBALS['log']->info("connected to db");
$this->connectOptions = $configOptions;
return true;
}
./include/database/DBManager.php
// 获取配置
public function getOption($option) {
if (isset($this->options[$option])) {
return $this->options[$option];
}
return null;
}
// 核对错误,主要是判断数据库是否连接,最后一次数据库操作是否有误
public function checkError($msg = '', $dieOnError = false) {
if (empty($this->database)) {
$this->registerError($msg, "Database Is Not Connected", $dieOnError);
return true;
}
$dberror = $this->lastDbError();
if ($dberror === false) {
$this->last_error = false;
return false;
}
$this->registerError($msg, $dberror, $dieOnError);
return true;
}
// 如果$message不为空,说明最后一次执行sql有误
// 这时再判断$dieOnError是否要终止执行程序
public function registerError($userMessage, $message, $dieOnError = false) {
if (!empty($message)) {
if (!empty($userMessage)) {
$message = "$userMessage: $message";
}
if (empty($message)) {
$message = "Database error";
}
$this->log->fatal($message);
if ($dieOnError || $this->dieOnError) {
if (isset($GLOBALS['app_strings']['ERR_DB_FAIL'])) {
sugar_die($GLOBALS['app_strings']['ERR_DB_FAIL']);
} else {
sugar_die("Database error. Please check sugarcrm.log for details.");
}
} else {
$this->last_error = $message;
}
}
}
看完后,才发现配置文件中的db_port竟然是虚设的,而是在db_host中这样'localhost:3306'配置。是否有很大收获啊,否则 一旦数据库端口不是3306,而又在db_port中配置了端口,结果是死活连接不了数据库,结果废了大半天劲看源码发现问题竟然出现这里。
好了,数据库也连接上了,那么就一口气看看数据库查询的分析吧。
$q = "SELECT id, name, symbol, conversion_rate FROM currencies WHERE status = 'Active' and deleted = 0";
$r = $db->query($q);
./include/database/MysqliManager.php
public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false) {
$result = $this->queryMulti($sql, $dieOnError, $msg, $suppress, $keepResult, false);
return $result;
}
protected function queryMulti($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false, $multiquery = true) {
if (is_array($sql)) {
// queryArray does not support any return sets
// 其实就是批量执行
/*
public function queryArray(array $sqls, $dieOnError = false, $msg = '', $suppress = false) {
$last = true;
foreach ($sqls as $sql) {
if (!($last = $this->query($sql, $dieOnError, $msg, $suppress))) {
break;
}
}
return $last;
}
*/
return $this->queryArray($sql, $dieOnError, $msg, $suppress);
}
static $queryMD5 = array();
// 此两步不执行
parent::countQuery($sql);
$GLOBALS['log']->info('Query:' . $sql);
/*
* 主要是看看数据库是否连接
* 刚刚分析过,数据库后回话信息保持在$this->database中
* 如果没有则重新连接,出错了,那么久终止执行
public function checkConnection() {
$this->last_error = '';
if (!isset($this->database))
$this->connect();
}
*/
$this->checkConnection();
// 记录sql开始执行时间
$this->query_time = microtime(true);
// 记录执行sql
$this->lastsql = $sql;
// 默认执行多条sql语句查询
if ($multiquery) {
$query_result = $suppress ? @mysqli_multi_query($this->database, $sql) : mysqli_multi_query($this->database, $sql);
$result = mysqli_use_result($this->database);
// Clear any remaining recordsets
while (mysqli_next_result($this->database)) {
$tmp_result = mysqli_use_result($this->database);
mysqli_free_result($tmp_result);
}
} else
$result = $suppress ? @mysqli_query($this->database, $sql) : mysqli_query($this->database, $sql);
// 这两步感觉很鸡肋,因为只保存了,但是没有哪个地方用到
$md5 = md5($sql);
if (empty($queryMD5[$md5]))
$queryMD5[$md5] = true;
// 记录sql查询时间
$this->query_time = microtime(true) - $this->query_time;
$GLOBALS['log']->info('Query Execution Time:' . $this->query_time);
// slow query logging
// 如果执行的sql超过了配置文件中的数据,那么便记录下来
// 前提是配种文件中陪了该项,否则不执行
if ($this->dump_slow_queries($sql)) {
$this->track_slow_queries($sql);
}
// This is some heavy duty debugging, leave commented out unless you need this:
/*
$bt = debug_backtrace();
$line['file'] = 'NO_FILE';
$line['line'] = 'NO_LINE';
$line['function'] = 'NO_FUNCTION';
$i = 0;
foreach ( $bt as $i => $tryLine ) {
if ( strpos($tryLine['file'],'include/database') === false && strpos($tryLine['file'],'include/SugarQuery') === false ) {
$line = $tryLine;
// Go function line up to find the real function
if ( isset($bt[($i+1)]['function']) ) {
$line['function'] = $bt[($i+1)]['function'];
}
break;
}
}
$dumpQuery = str_replace(array(' ',' ',' ',' ',' ',"\n","\t","\r"),
array(' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ',),
$sql);
$GLOBALS['log']->fatal("{$line['file']}:{$line['line']} ${line['function']} \nQuery: $dumpQuery\n");
*/
// 默认为false不执行
if ($keepResult) {
$this->lastResult = $result;
}
// 若数据库挂了便写条报警并重新再连接
if ($this->database && mysqli_errno($this->database) == 2006 && $this->retryCount < 1) {
$GLOBALS['log']->fatal('mysqli has gone away, retrying');
$this->retryCount++;
$this->disconnect();
$this->connect();
return $this->query($sql, $dieOnError, $msg, $suppress, $keepResult);
} else {
$this->retryCount = 0;
}
// 查看sql是否有误
$this->checkError($msg . ' Query Failed: ' . $sql, $dieOnError);
// 没有的话便返回查询结果
return $result;
}
./incldue/database/DBManager.php
// 如果执行的sql超过了配置文件中的数据,那么便记录下来
public function dump_slow_queries($query) {
global $sugar_config;
$do_the_dump = isset($sugar_config['dump_slow_queries']) ? $sugar_config['dump_slow_queries'] : false;
$slow_query_time_msec = isset($sugar_config['slow_query_time_msec']) ? $sugar_config['slow_query_time_msec'] : 5000;
if ($do_the_dump) {
if ($slow_query_time_msec < ($this->query_time * 1000)) {
// Then log both the query and the query time
$this->log->fatal('Slow Query (time:' . $this->query_time . "\n" . $query);
return true;
}
}
return false;
}
最后看看针对select查询的处理
while($a = $db->fetchByAssoc($r)) {
$load = array();
$load['name'] = $a['name'];
$load['symbol'] = $a['symbol'];
$load['conversion_rate'] = $a['conversion_rate'];
$this->currencies[$a['id']] = $load;
}
./include/database/DBManager.php
public function fetchByAssoc($result, $encode = true) {
if (empty($result))
return false;
// 这是针对老旧版本的api来了,不用看
if (is_int($encode) && func_num_args() == 3) {
// old API: $result, $rowNum, $encode
$GLOBALS['log']->deprecated("Using row number in fetchByAssoc is not portable and no longer supported. Please fix your code.");
$encode = func_get_arg(2);
}
if ($result instanceof PreparedStatement) {
$row = $result->preparedStatementFetch();
} else {
/*
* 这里就是简单的调用mysqli_fetch_assoc处理
public function fetchRow($result) {
if (empty($result))
return false;
$row = mysqli_fetch_assoc($result);
if ($row == null)
$row = false; //Make sure MySQLi driver results are consistent with other database drivers
return $row;
}
*/
$row = $this->fetchRow($result);
}
// 如果设置了编码,则需转码
/*
public function encodeHTML($string) {
if (empty($string) || !$this->encode) {
return $string;
}
/** Not using ENT_HTML401|ENT_SUBSTITUTE since they are 5.4+ only */
return htmlspecialchars($string, ENT_QUOTES, "UTF-8");
}
*/
if (!empty($row) && $encode && $this->encode) {
return array_map(array($this, "encodeHTML"), $row);
} else {
return $row;
}
}