[TOC]
* * * * *
# 1 查询构造器
>>查询构造器，用来在数据库查询中构造SQL语句
### $builder->__construct()
>>创建构造器，初始化构造器使用的数据库连接对象
~~~
public function __construct(Connection $connection)
{
$this->connection = $connection;
}
~~~
### $builder->setQuery()
>>设置查询构造所在的查询对象
~~~
public function setQuery(Query $query)
{
$this->query = $query;
}
~~~
# 2 SQL语句构造
>>SQL语句合成入口
>替换掉SQL语句的相关字符串为查询选项信息。
### $builder->select()
>>合成select语句
~~~
public function select($options = [])
{
$sql = str_replace(
['%TABLE%', '%DISTINCT%', '%FIELD%', '%JOIN%', '%WHERE%', '%GROUP%', '%HAVING%', '%ORDER%', '%LIMIT%', '%UNION%', '%LOCK%', '', '%FORCE%'],
[
$this->parseTable($options['table']),
$this->parseDistinct($options['distinct']),
$this->parseField($options['field']),
$this->parseJoin($options['join']),
$this->parseWhere($options['where'], $options),
$this->parseGroup($options['group']),
$this->parseHaving($options['having']),
$this->parseOrder($options['order']),
$this->parseLimit($options['limit']),
$this->parseUnion($options['union']),
$this->parseLock($options['lock']),
$this->parseComment($options['comment']),
$this->parseForce($options['force']),
], $this->selectSql);
return $sql;
}
~~~
~~~
; select表达式模板
protected $selectSql = 'SELECT%DISTINCT% %FIELD% FROM %TABLE%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT% %UNION%%LOCK%';
~~~
### $builder->insert()
>>合成insert语句
~~~
public function insert(array $data, $options = [], $replace = false)
{
// 分析并处理数据
$data = $this->parseData($data, $options);
if (empty($data)) {
return 0;
}
$fields = array_keys($data);
$values = array_values($data);
$sql = str_replace(
['%INSERT%', '%TABLE%', '%FIELD%', '%DATA%', ''],
[
$replace ? 'REPLACE' : 'INSERT',
$this->parseTable($options['table']),
implode(' , ', $fields),
implode(' , ', $values),
$this->parseComment($options['comment']),
], $this->insertSql);
return $sql;
}
~~~
~~~
; insert表达式模板
protected $insertSql = '%INSERT% INTO %TABLE% (%FIELD%) VALUES (%DATA%) ';
~~~
### $builder->inssertAll()
>>合成insertAll语句
~~~
public function insertAll($dataSet, $options)
{
// 获取合法的字段
if ('*' == $options['field']) {
$fields = array_keys($this->query->getFieldsType($options));
} else {
$fields = $options['field'];
}
foreach ($dataSet as &$data) {
foreach ($data as $key => $val) {
if (!in_array($key, $fields, true)) {
if ($options['strict']) {
throw new Exception('fields not exists:[' . $key . ']');
}
unset($data[$key]);
} elseif (is_scalar($val)) {
$data[$key] = $this->parseValue($val, $key);
} else {
// 过滤掉非标量数据
unset($data[$key]);
}
}
$value = array_values($data);
$values[] = 'SELECT ' . implode(',', $value);
}
$fields = array_map([$this, 'parseKey'], array_keys(reset($dataSet)));
$sql = str_replace(
['%TABLE%', '%FIELD%', '%DATA%', ''],
[
$this->parseTable($options['table']),
implode(' , ', $fields),
implode(' UNION ALL ', $values),
$this->parseComment($options['comment']),
], $this->insertAllSql);
return $sql;
}
~~~
### $builder->selectInsert()
>>合成Selectinsert语句
~~~
public function selectInsert($fields, $table, $options)
{
if (is_string($fields)) {
$fields = explode(',', $fields);
}
$fields = array_map([$this, 'parseKey'], $fields);
$sql = 'INSERT INTO ' . $this->parseTable($table) . ' (' . implode(',', $fields) . ') ' . $this->select($options);
return $sql;
}
~~~
~~~
; selectInsert表达式模板
protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) %DATA% ';
~~~
### $builder->update()
>>合成Update语句
~~~
public function update($data, $options)
{
$table = $this->parseTable($options['table']);
$data = $this->parseData($data, $options);
if (empty($data)) {
return '';
}
foreach ($data as $key => $val) {
$set[] = $key . '=' . $val;
}
$sql = str_replace(
['%TABLE%', '%SET%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', ''],
[
$this->parseTable($options['table']),
implode(',', $set),
$this->parseJoin($options['join']),
$this->parseWhere($options['where'], $options),
$this->parseOrder($options['order']),
$this->parseLimit($options['limit']),
$this->parseLock($options['lock']),
$this->parseComment($options['comment']),
], $this->updateSql);
return $sql;
}
~~~
~~~
; update表达式模板
protected $updateSql = 'UPDATE %TABLE% SET %SET% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%';
~~~
### $builder->delete()
>> 合成Delete语句
~~~
public function delete($options)
{
$sql = str_replace(
['%TABLE%', '%USING%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', ''],
[
$this->parseTable($options['table']),
!empty($options['using']) ? ' USING ' . $this->parseTable($options['using']) . ' ' : '',
$this->parseJoin($options['join']),
$this->parseWhere($options['where'], $options),
$this->parseOrder($options['order']),
$this->parseLimit($options['limit']),
$this->parseLock($options['lock']),
$this->parseComment($options['comment']),
], $this->deleteSql);
return $sql;
}
~~~
~~~
; delete表达式模板
protected $deleteSql = 'DELETE FROM %TABLE% %USING% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%';
~~~
# 3 表达式选项解析
### $builder->parseTable()
>>解析选项的 table选项
~~~
protected function parseTable($tables)
{
if (is_array($tables)) {
// 支持别名定义
foreach ($tables as $table => $alias) {
$array[] = !is_numeric($table) ?
$this->parseKey($table) . ' ' . $this->parseKey($alias) :
$this->parseKey($alias);
}
$tables = $array;
} elseif (is_string($tables)) {
$tables = $this->parseSqlTable($tables);
$tables = array_map([$this, 'parseKey'], explode(',', $tables));
}
return implode(',', $tables);
}
~~~
>> 替换__TABLE_NAME__
~~~
protected function parseSqlTable($sql)
{
return $this->query->parseSqlTable($sql);
}
~~~
### $builder->parseDistinct()
~~~
protected function parseDistinct($distinct)
{
return !empty($distinct) ? ' DISTINCT ' : '';
}
~~~
### $builder->parseField()
>>解析 Field 选项
~~~
protected function parseField($fields)
{
if ('*' == $fields || empty($fields)) {
$fieldsStr = '*';
} elseif (is_array($fields)) {
// 支持 'field1'=>'field2' 这样的字段别名定义
$array = [];
foreach ($fields as $key => $field) {
if (!is_numeric($key)) {
$array[] = $this->parseKey($key) . ' AS ' . $this->parseKey($field);
} else {
$array[] = $this->parseKey($field);
}
}
$fieldsStr = implode(',', $array);
}
return $fieldsStr;
}
~~~
### $builder->parseJoin()
>>解析Join选项
~~~
protected function parseJoin($join)
{
$joinStr = '';
if (!empty($join)) {
$joinStr = ' ' . implode(' ', $join) . ' ';
}
return $joinStr;
}
~~~
### $builder->parseWhere()
>>解析where选项入口
~~~
protected function parseWhere($where, $options)
{
$whereStr = $this->buildWhere($where, $options);
return empty($whereStr) ? '' : ' WHERE ' . $whereStr;
}
~~~
>>构造SQL查询条件
~~~
public function buildWhere($where, $options)
{
if (empty($where)) {
$where = [];
}
if ($where instanceof Query) {
return $this->buildWhere($where->getOptions('where'), $options);
}
$whereStr = '';
$binds = $this->query->getFieldsBind($options);
foreach ($where as $key => $val) {
$str = [];
foreach ($val as $field => $value) {
if ($value instanceof \Closure) {
// 使用闭包查询
$query = new Query($this->connection);
call_user_func_array($value, [ & $query]);
$str[] = ' ' . $key . ' ( ' . $this->buildWhere($query->getOptions('where'), $options) . ' )';
} elseif (strpos($field, '|')) {
// 不同字段使用相同查询条件（OR）
$array = explode('|', $field);
$item = [];
foreach ($array as $k) {
$item[] = $this->parseWhereItem($k, $value, '', $options, $binds);
}
$str[] = ' ' . $key . ' ( ' . implode(' OR ', $item) . ' )';
} elseif (strpos($field, '&')) {
// 不同字段使用相同查询条件（AND）
$array = explode('&', $field);
$item = [];
foreach ($array as $k) {
$item[] = $this->parseWhereItem($k, $value, '', $options, $binds);
}
$str[] = ' ' . $key . ' ( ' . implode(' AND ', $item) . ' )';
} else {
// 对字段使用表达式查询
$field = is_string($field) ? $field : '';
$str[] = ' ' . $key . ' ' . $this->parseWhereItem($field, $value, $key, $options, $binds);
}
}
$whereStr .= empty($whereStr) ? substr(implode(' ', $str), strlen($key) + 1) : implode(' ', $str);
}
return $whereStr;
}
~~~
>>where子单元分析
~~~
protected function parseWhereItem($field, $val, $rule = '', $options = [], $binds = [], $bindName = null)
{
// 字段分析
$key = $field ? $this->parseKey($field) : '';
// 查询规则和条件
if (!is_array($val)) {
$val = ['=', $val];
}
list($exp, $value) = $val;
// 对一个字段使用多个查询条件
if (is_array($exp)) {
$item = array_pop($val);
// 传入 or 或者 and
if (is_string($item) && in_array($item, ['AND', 'and', 'OR', 'or'])) {
$rule = $item;
} else {
array_push($val, $item);
}
foreach ($val as $k => $item) {
$bindName = 'where_' . str_replace('.', '_', $field) . '_' . $k;
$str[] = $this->parseWhereItem($field, $item, $rule, $options, $binds, $bindName);
}
return '( ' . implode(' ' . $rule . ' ', $str) . ' )';
}
// 检测操作符
if (!in_array($exp, $this->exp)) {
$exp = strtolower($exp);
if (isset($this->exp[$exp])) {
$exp = $this->exp[$exp];
} else {
throw new Exception('where express error:' . $exp);
}
}
$bindName = $bindName ?: 'where_' . str_replace('.', '_', $field);
$bindType = isset($binds[$field]) ? $binds[$field] : PDO::PARAM_STR;
if (is_scalar($value) && array_key_exists($field, $binds) && !in_array($exp, ['EXP', 'NOT NULL', 'NULL', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN']) && strpos($exp, 'TIME') === false) {
if (strpos($value, ':') !== 0 || !$this->query->isBind(substr($value, 1))) {
if ($this->query->isBind($bindName)) {
$bindName .= '_' . uniqid();
}
$this->query->bind($bindName, $value, $bindType);
$value = ':' . $bindName;
}
}
$whereStr = '';
if (in_array($exp, ['=', '<>', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE'])) {
// 比较运算 及 模糊匹配
$whereStr .= $key . ' ' . $exp . ' ' . $this->parseValue($value, $field);
} elseif ('EXP' == $exp) {
// 表达式查询
$whereStr .= '( ' . $key . ' ' . $value . ' )';
} elseif (in_array($exp, ['NOT NULL', 'NULL'])) {
// NULL 查询
$whereStr .= $key . ' IS ' . $exp;
} elseif (in_array($exp, ['NOT IN', 'IN'])) {
// IN 查询
if ($value instanceof \Closure) {
$whereStr .= $key . ' ' . $exp . ' ' . $this->parseClosure($value);
} else {
$value = is_array($value) ? $value : explode(',', $value);
if (array_key_exists($field, $binds)) {
$bind = [];
$array = [];
foreach ($value as $k => $v) {
$bind[$bindName . '_in_' . $k] = [$v, $bindType];
$array[] = ':' . $bindName . '_in_' . $k;
}
$this->query->bind($bind);
$zone = implode(',', $array);
} else {
$zone = implode(',', $this->parseValue($value, $field));
}
$whereStr .= $key . ' ' . $exp . ' (' . $zone . ')';
}
} elseif (in_array($exp, ['NOT BETWEEN', 'BETWEEN'])) {
// BETWEEN 查询
$data = is_array($value) ? $value : explode(',', $value);
if (array_key_exists($field, $binds)) {
$bind = [
$bindName . '_between_1' => [$data[0], $bindType],
$bindName . '_between_2' => [$data[1], $bindType],
];
$this->query->bind($bind);
$between = ':' . $bindName . '_between_1' . ' AND :' . $bindName . '_between_2';
} else {
$between = $this->parseValue($data[0], $field) . ' AND ' . $this->parseValue($data[1], $field);
}
$whereStr .= $key . ' ' . $exp . ' ' . $between;
} elseif (in_array($exp, ['NOT EXISTS', 'EXISTS'])) {
// EXISTS 查询
if ($value instanceof \Closure) {
$whereStr .= $exp . ' ' . $this->parseClosure($value);
} else {
$whereStr .= $exp . ' (' . $value . ')';
}
} elseif (in_array($exp, ['< TIME', '> TIME', '<= TIME', '>= TIME'])) {
$whereStr .= $key . ' ' . substr($exp, 0, 2) . ' ' . $this->parseDateTime($value, $field, $options, $bindName, $bindType);
} elseif (in_array($exp, ['BETWEEN TIME', 'NOT BETWEEN TIME'])) {
if (is_string($value)) {
$value = explode(',', $value);
}
$whereStr .= $key . ' ' . substr($exp, 0, -4) . $this->parseDateTime($value[0], $field, $options, $bindName . '_between_1', $bindType) . ' AND ' . $this->parseDateTime($value[1], $field, $options, $bindName . '_between_2', $bindType);
}
return $whereStr;
}
~~~
### $builder->parseGroup()
>>解析Group选项
~~~
protected function parseGroup($group)
{
return !empty($group) ? ' GROUP BY ' . $group : '';
}
~~~
### $builder->parseHaving()
>>解析Having选项
~~~
protected function parseHaving($having)
{
return !empty($having) ? ' HAVING ' . $having : '';
}
~~~
### $builder->parseOrder()
>> 解析Order选项
~~~
protected function parseOrder($order)
{
if (is_array($order)) {
$array = [];
foreach ($order as $key => $val) {
if (is_numeric($key)) {
if (false === strpos($val, '(')) {
$array[] = $this->parseKey($val);
} elseif ('[rand]' == $val) {
$array[] = $this->parseRand();
}
} else {
$sort = in_array(strtolower(trim($val)), ['asc', 'desc']) ? ' ' . $val : '';
$array[] = $this->parseKey($key) . ' ' . $sort;
}
}
$order = implode(',', $array);
}
return !empty($order) ? ' ORDER BY ' . $order : '';
}
~~~
### $builder->parseLimit()
>>解析Limit选项
~~~
protected function parseLimit($limit)
{
return (!empty($limit) && false === strpos($limit, '(')) ? ' LIMIT ' . $limit . ' ' : '';
}
~~~
### $builder->parseUnion()
>>解析Union选项
~~~
protected function parseUnion($union)
{
if (empty($union)) {
return '';
}
$type = $union['type'];
unset($union['type']);
foreach ($union as $u) {
if ($u instanceof \Closure) {
$sql[] = $type . ' ' . $this->parseClosure($u, false);
} elseif (is_string($u)) {
$sql[] = $type . ' ' . $this->parseSqlTable($u);
}
}
return implode(' ', $sql);
}
~~~
### $builder->parseLock()
>>解析Lock选项
~~~
protected function parseLock($lock = false)
{
return $lock ? ' FOR UPDATE ' : '';
}
~~~
### $builder->parseComment()
>>解析Comment选项
~~~
protected function parseComment($comment)
{
return !empty($comment) ? ' /* ' . $comment . ' */' : '';
}
~~~
### $builder->parseForce()
>>解析index选项
~~~
protected function parseForce($index)
{
if (empty($index)) {
return '';
}
if (is_array($index)) {
$index = join(",", $index);
}
return sprintf(" FORCE INDEX ( %s ) ", $index);
}
~~~
# 4 解析辅助
### $builder->parseData()
~~~
protected function parseData($data, $options)
{
if (empty($data)) {
return [];
}
// 获取绑定信息
$bind = $this->query->getFieldsBind($options);
if ('*' == $options['field']) {
$fields = array_keys($bind);
} else {
$fields = $options['field'];
}
$result = [];
foreach ($data as $key => $val) {
$item = $this->parseKey($key);
if (!in_array($key, $fields, true)) {
if ($options['strict']) {
throw new Exception('fields not exists:[' . $key . ']');
}
} elseif (isset($val[0]) && 'exp' == $val[0]) {
$result[$item] = $val[1];
} elseif (is_null($val)) {
$result[$item] = 'NULL';
} elseif (is_scalar($val)) {
// 过滤非标量数据
if ($this->query->isBind(substr($val, 1))) {
$result[$item] = $val;
} else {
$this->query->bind($key, $val, isset($bind[$key]) ? $bind[$key] : PDO::PARAM_STR);
$result[$item] = ':' . $key;
}
}
}
return $result;
}
~~~
### $builder->parseKey()
~~~
protected function parseKey($key)
{
return $key;
}
~~~
### $builder->parseValue()
~~~
protected function parseValue($value, $field = '')
{
if (is_string($value)) {
$value = strpos($value, ':') === 0 && $this->query->isBind(substr($value, 1)) ? $value : $this->connection->quote($value);
} elseif (is_array($value)) {
$value = array_map([$this, 'parseValue'], $value);
} elseif (is_bool($value)) {
$value = $value ? '1' : '0';
} elseif (is_null($value)) {
$value = 'null';
}
return $value;
}
~~~
### $builder->parseClosure()
~~~
protected function parseClosure($call, $show = true)
{
$query = new Query($this->connection);
call_user_func_array($call, [ & $query]);
return $query->buildSql($show);
}
~~~
### $builder->parseDateTime()
~~~
protected function parseDateTime($value, $key, $options = [], $bindName = null, $bindType = null)
{
// 获取时间字段类型
$type = $this->query->getFieldsType($options);
if (isset($type[$key])) {
$info = $type[$key];
}
if (isset($info)) {
$value = strtotime($value) ?: $value;
if (preg_match('/(datetime|timestamp)/is', $info)) {
// 日期及时间戳类型
$value = date('Y-m-d H:i:s', $value);
} elseif (preg_match('/(date)/is', $info)) {
// 日期及时间戳类型
$value = date('Y-m-d', $value);
}
}
$bindName = $bindName ?: $key;
$this->query->bind($bindName, $value, $bindType);
return ':' . $bindName;
}
~~~
- 框架简介
- 简介
- 框架目录
- 根目录
- 应用目录
- 核心目录
- 扩展目录
- 其他目录
- 框架流程
- 启动流程
- 请求流程
- 响应流程
- 框架结构
- 应用组织
- 网络请求
- 路由组织
- 数据验证
- 数据模型（M）
- 数据库连接(Connection)
- 数据库(Db)
- 查询构造(Builder)
- 数据库查询(Query)
- 模型(Model)
- 模板视图（V）
- 视图(View)
- 模板引擎(Think)
- 模板标签库(TagLib)
- 控制器（C）
- 网络响应
- 配置与缓存
- 配置操作
- 缓存操作
- cookie与session
- Cookie操作
- Session操作
- 自动加载
- 钩子注册
- 文件上传
- 分页控制
- 控制台
- 自动构建
- 日志异常调试
- 异常处理
- 代码调试
- 日志记录
- 框架使用
- 1 环境搭建(Server)
- 2 网络请求(Request)
- 3 请求路由(Route)
- 4 响应输出(Response)
- 5 业务处理(Controller)
- 6 数据存取(Model)
- 7 Web界面(View)