2037 lines
44 KiB
PHP
2037 lines
44 KiB
PHP
<?php
|
||
// +----------------------------------------------------------------------
|
||
// | ShopXO 国内领先企业级B2C免费开源电商系统
|
||
// +----------------------------------------------------------------------
|
||
// | Copyright (c) 2011~2099 http://shopxo.net All rights reserved.
|
||
// +----------------------------------------------------------------------
|
||
// | Licensed ( https://opensource.org/licenses/mit-license.php )
|
||
// +----------------------------------------------------------------------
|
||
// | Author: Devil
|
||
// +----------------------------------------------------------------------
|
||
namespace library;
|
||
|
||
use Exception;
|
||
use PDO;
|
||
use PDOException;
|
||
|
||
/**
|
||
* PDO连接库
|
||
* @author Devil
|
||
* @blog http://gong.gg/
|
||
* @version 1.0.0
|
||
* @date 2020-09-04
|
||
* @desc
|
||
* 1. 数据库连接类，依赖PDO_MYSQL扩展、在 https://github.com/auraphp/Aura.SqlQuery 的基础上修改而成
|
||
* 2. 文档说明地址 https://www.workerman.net/doc/workerman/components/workerman-mysql.html
|
||
*/
|
||
class PDOConnect
|
||
{
|
||
/**
|
||
* SELECT
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $union = array();
|
||
|
||
/**
|
||
* 是否是更新
|
||
*
|
||
* @var bool
|
||
*/
|
||
protected $for_update = false;
|
||
|
||
/**
|
||
* 选择的列
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $cols = array();
|
||
|
||
/**
|
||
* 从哪些表里面 SELECT
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $from = array();
|
||
|
||
/**
|
||
* $from 当前的 key
|
||
*
|
||
* @var int
|
||
*/
|
||
protected $from_key = -1;
|
||
|
||
/**
|
||
* GROUP BY 的列
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $group_by = array();
|
||
|
||
/**
|
||
* HAVING 条件数组.
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $having = array();
|
||
|
||
/**
|
||
* HAVING 语句中绑定的值.
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $bind_having = array();
|
||
|
||
/**
|
||
* 每页多少条记录
|
||
*
|
||
* @var int
|
||
*/
|
||
protected $paging = 10;
|
||
|
||
/**
|
||
* sql 中绑定的值
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $bind_values = array();
|
||
|
||
/**
|
||
* WHERE 条件.
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $where = array();
|
||
|
||
/**
|
||
* WHERE 语句绑定的值
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $bind_where = array();
|
||
|
||
/**
|
||
* ORDER BY 的列
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $order_by = array();
|
||
|
||
/**
|
||
* ORDER BY 的排序方式,默认为升序
|
||
*
|
||
* @var bool
|
||
*/
|
||
protected $order_asc = null;
|
||
/**
|
||
* SELECT 多少记录
|
||
*
|
||
* @var int
|
||
*/
|
||
protected $limit = 0;
|
||
|
||
/**
|
||
* 返回记录的游标
|
||
*
|
||
* @var int
|
||
*/
|
||
protected $offset = 0;
|
||
|
||
/**
|
||
* flags 列表
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $flags = array();
|
||
|
||
/**
|
||
* 操作哪个表
|
||
*
|
||
* @var string
|
||
*/
|
||
protected $table;
|
||
|
||
/**
|
||
* 表.列 和 last-insert-id 映射
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $last_insert_id_names = array();
|
||
|
||
/**
|
||
* INSERT 或者 UPDATE 的列
|
||
*
|
||
* @param array
|
||
*/
|
||
protected $col_values;
|
||
|
||
/**
|
||
* 返回的列
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $returning = array();
|
||
|
||
/**
|
||
* sql 的类型 SELECT INSERT DELETE UPDATE
|
||
*
|
||
* @var string
|
||
*/
|
||
protected $type = '';
|
||
|
||
/**
|
||
* pdo 实例
|
||
*
|
||
* @var PDO
|
||
*/
|
||
protected $pdo;
|
||
|
||
/**
|
||
* PDOStatement 实例
|
||
*
|
||
* @var \PDOStatement
|
||
*/
|
||
protected $sQuery;
|
||
|
||
/**
|
||
* 数据库用户名密码等配置
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $settings = array();
|
||
|
||
/**
|
||
* sql 的参数
|
||
*
|
||
* @var array
|
||
*/
|
||
protected $parameters = array();
|
||
|
||
/**
|
||
* 最后一条直行的 sql
|
||
*
|
||
* @var string
|
||
*/
|
||
protected $lastSql = '';
|
||
|
||
/**
|
||
* 是否执行成功
|
||
*
|
||
* @var bool
|
||
*/
|
||
protected $success = false;
|
||
|
||
/**
|
||
* 选择哪些列
|
||
*
|
||
* @param string|array $cols
|
||
* @return self
|
||
*/
|
||
public function select($cols = '*')
|
||
{
|
||
$this->type = 'SELECT';
|
||
if (!is_array($cols)) {
|
||
$cols = explode(',', $cols);
|
||
}
|
||
$this->cols($cols);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 从哪个表删除
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function delete($table)
|
||
{
|
||
$this->type = 'DELETE';
|
||
$this->table = $this->quoteName($table);
|
||
$this->fromRaw($this->quoteName($table));
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 更新哪个表
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function update($table)
|
||
{
|
||
$this->type = 'UPDATE';
|
||
$this->table = $this->quoteName($table);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 向哪个表插入
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function insert($table)
|
||
{
|
||
$this->type = 'INSERT';
|
||
$this->table = $this->quoteName($table);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
*
|
||
* 设置 SQL_CALC_FOUND_ROWS 标记.
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function calcFoundRows($enable = true)
|
||
{
|
||
$this->setFlag('SQL_CALC_FOUND_ROWS', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 SQL_CACHE 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function cache($enable = true)
|
||
{
|
||
$this->setFlag('SQL_CACHE', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 SQL_NO_CACHE 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function noCache($enable = true)
|
||
{
|
||
$this->setFlag('SQL_NO_CACHE', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 STRAIGHT_JOIN 标记.
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function straightJoin($enable = true)
|
||
{
|
||
$this->setFlag('STRAIGHT_JOIN', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 HIGH_PRIORITY 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function highPriority($enable = true)
|
||
{
|
||
$this->setFlag('HIGH_PRIORITY', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 SQL_SMALL_RESULT 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function smallResult($enable = true)
|
||
{
|
||
$this->setFlag('SQL_SMALL_RESULT', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 SQL_BIG_RESULT 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function bigResult($enable = true)
|
||
{
|
||
$this->setFlag('SQL_BIG_RESULT', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 SQL_BUFFER_RESULT 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function bufferResult($enable = true)
|
||
{
|
||
$this->setFlag('SQL_BUFFER_RESULT', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 FOR UPDATE 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function forUpdate($enable = true)
|
||
{
|
||
$this->for_update = (bool)$enable;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 DISTINCT 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function distinct($enable = true)
|
||
{
|
||
$this->setFlag('DISTINCT', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 LOW_PRIORITY 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function lowPriority($enable = true)
|
||
{
|
||
$this->setFlag('LOW_PRIORITY', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 IGNORE 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function ignore($enable = true)
|
||
{
|
||
$this->setFlag('IGNORE', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 QUICK 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function quick($enable = true)
|
||
{
|
||
$this->setFlag('QUICK', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置 DELAYED 标记
|
||
*
|
||
* @param bool $enable
|
||
* @return self
|
||
*/
|
||
public function delayed($enable = true)
|
||
{
|
||
$this->setFlag('DELAYED', $enable);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 序列化
|
||
*
|
||
* @return string
|
||
*/
|
||
public function __toString()
|
||
{
|
||
$union = '';
|
||
if ($this->union) {
|
||
$union = implode(' ', $this->union) . ' ';
|
||
}
|
||
return $union . $this->build();
|
||
}
|
||
|
||
/**
|
||
* 设置每页多少条记录
|
||
*
|
||
* @param int $paging
|
||
* @return self
|
||
*/
|
||
public function setPaging($paging)
|
||
{
|
||
$this->paging = (int)$paging;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 获取每页多少条记录
|
||
*
|
||
* @return int
|
||
*/
|
||
public function getPaging()
|
||
{
|
||
return $this->paging;
|
||
}
|
||
|
||
/**
|
||
* 获取绑定在占位符上的值
|
||
*/
|
||
public function getBindValues()
|
||
{
|
||
switch ($this->type) {
|
||
case 'SELECT':
|
||
return $this->getBindValuesSELECT();
|
||
case 'DELETE':
|
||
case 'UPDATE':
|
||
case 'INSERT':
|
||
return $this->getBindValuesCOMMON();
|
||
default :
|
||
throw new Exception("type err");
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 获取绑定在占位符上的值
|
||
*
|
||
* @return array
|
||
*/
|
||
public function getBindValuesSELECT()
|
||
{
|
||
$bind_values = $this->bind_values;
|
||
$i = 1;
|
||
foreach ($this->bind_where as $val) {
|
||
$bind_values[$i] = $val;
|
||
$i++;
|
||
}
|
||
foreach ($this->bind_having as $val) {
|
||
$bind_values[$i] = $val;
|
||
$i++;
|
||
}
|
||
return $bind_values;
|
||
}
|
||
|
||
/**
|
||
*
|
||
* SELECT选择哪些列
|
||
*
|
||
* @param mixed $key
|
||
* @param string $val
|
||
* @return void
|
||
*/
|
||
protected function addColSELECT($key, $val)
|
||
{
|
||
if (is_string($key)) {
|
||
$this->cols[$val] = $key;
|
||
} else {
|
||
$this->addColWithAlias($val);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* SELECT 增加选择的列
|
||
*
|
||
* @param string $spec
|
||
*/
|
||
protected function addColWithAlias($spec)
|
||
{
|
||
$parts = explode(' ', $spec);
|
||
$count = count($parts);
|
||
if ($count == 2 && trim($parts[0]) != '' && trim($parts[1]) != '') {
|
||
$this->cols[$parts[1]] = $parts[0];
|
||
} elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
|
||
$this->cols[$parts[2]] = $parts[0];
|
||
} else {
|
||
$this->cols[] = trim($spec);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* from 哪个表
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function from($table)
|
||
{
|
||
return $this->fromRaw($this->quoteName($table));
|
||
}
|
||
|
||
/**
|
||
* from的表
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function fromRaw($table)
|
||
{
|
||
$this->from[] = array($table);
|
||
$this->from_key++;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
*
|
||
* 子查询
|
||
*
|
||
* @param string $table
|
||
* @param string $name The alias name for the sub-select.
|
||
* @return self
|
||
*/
|
||
public function fromSubSelect($table, $name)
|
||
{
|
||
$this->from[] = array("($table) AS " . $this->quoteName($name));
|
||
$this->from_key++;
|
||
return $this;
|
||
}
|
||
|
||
|
||
/**
|
||
* 增加 join 语句
|
||
*
|
||
* @param string $table
|
||
* @param string $cond
|
||
* @param string $type
|
||
* @return self
|
||
* @throws Exception
|
||
*/
|
||
public function join($table, $cond = null, $type = '')
|
||
{
|
||
return $this->joinInternal($type, $table, $cond);
|
||
}
|
||
|
||
/**
|
||
* 增加 join 语句
|
||
*
|
||
* @param string $join inner, left, natural
|
||
* @param string $table
|
||
* @param string $cond
|
||
* @return self
|
||
* @throws Exception
|
||
*/
|
||
protected function joinInternal($join, $table, $cond = null)
|
||
{
|
||
if (!$this->from) {
|
||
throw new Exception('Cannot join() without from()');
|
||
}
|
||
|
||
$join = strtoupper(ltrim("$join JOIN"));
|
||
$table = $this->quoteName($table);
|
||
$cond = $this->fixJoinCondition($cond);
|
||
$this->from[$this->from_key][] = rtrim("$join $table $cond");
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* quote
|
||
*
|
||
* @param string $cond
|
||
* @return string
|
||
*
|
||
*/
|
||
protected function fixJoinCondition($cond)
|
||
{
|
||
if (!$cond) {
|
||
return '';
|
||
}
|
||
|
||
$cond = $this->quoteNamesIn($cond);
|
||
|
||
if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
|
||
return $cond;
|
||
}
|
||
|
||
if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
|
||
return $cond;
|
||
}
|
||
|
||
return 'ON ' . $cond;
|
||
}
|
||
|
||
/**
|
||
* inner join
|
||
*
|
||
* @param string $table
|
||
* @param string $cond
|
||
* @return self
|
||
* @throws Exception
|
||
*/
|
||
public function innerJoin($table, $cond = null)
|
||
{
|
||
return $this->joinInternal('INNER', $table, $cond);
|
||
}
|
||
|
||
/**
|
||
* left join
|
||
*
|
||
* @param string $table
|
||
* @param string $cond
|
||
* @return self
|
||
* @throws Exception
|
||
*/
|
||
public function leftJoin($table, $cond = null)
|
||
{
|
||
return $this->joinInternal('LEFT', $table, $cond);
|
||
}
|
||
|
||
/**
|
||
* right join
|
||
*
|
||
* @param string $table
|
||
* @param string $cond
|
||
* @return self
|
||
* @throws Exception
|
||
*/
|
||
public function rightJoin($table, $cond = null)
|
||
{
|
||
return $this->joinInternal('RIGHT', $table, $cond);
|
||
}
|
||
|
||
/**
|
||
* joinSubSelect
|
||
*
|
||
* @param string $join inner, left, natural
|
||
* @param string $spec
|
||
* @param string $name sub-select 的别名
|
||
* @param string $cond
|
||
* @return self
|
||
* @throws Exception
|
||
*/
|
||
public function joinSubSelect($join, $spec, $name, $cond = null)
|
||
{
|
||
if (!$this->from) {
|
||
throw new \Exception('Cannot join() without from() first.');
|
||
}
|
||
|
||
$join = strtoupper(ltrim("$join JOIN"));
|
||
$name = $this->quoteName($name);
|
||
$cond = $this->fixJoinCondition($cond);
|
||
$this->from[$this->from_key][] = rtrim("$join ($spec) AS $name $cond");
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* group by 语句
|
||
*
|
||
* @param array $cols
|
||
* @return self
|
||
*/
|
||
public function groupBy(array $cols)
|
||
{
|
||
foreach ($cols as $col) {
|
||
$this->group_by[] = $this->quoteNamesIn($col);
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* having 语句
|
||
*
|
||
* @param string $cond
|
||
* @return self
|
||
*/
|
||
public function having($cond)
|
||
{
|
||
$this->addClauseCondWithBind('having', 'AND', func_get_args());
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* or having 语句
|
||
*
|
||
* @param string $cond The HAVING condition.
|
||
* @return self
|
||
*/
|
||
public function orHaving($cond)
|
||
{
|
||
$this->addClauseCondWithBind('having', 'OR', func_get_args());
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置每页的记录数量
|
||
*
|
||
* @param int $page
|
||
* @return self
|
||
*/
|
||
public function page($page)
|
||
{
|
||
$this->limit = 0;
|
||
$this->offset = 0;
|
||
|
||
$page = (int)$page;
|
||
if ($page > 0) {
|
||
$this->limit = $this->paging;
|
||
$this->offset = $this->paging * ($page - 1);
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* union
|
||
*
|
||
* @return self
|
||
*/
|
||
public function union()
|
||
{
|
||
$this->union[] = $this->build() . ' UNION';
|
||
$this->reset();
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* unionAll
|
||
*
|
||
* @return self
|
||
*/
|
||
public function unionAll()
|
||
{
|
||
$this->union[] = $this->build() . ' UNION ALL';
|
||
$this->reset();
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 重置
|
||
*/
|
||
protected function reset()
|
||
{
|
||
$this->resetFlags();
|
||
$this->cols = array();
|
||
$this->from = array();
|
||
$this->from_key = -1;
|
||
$this->where = array();
|
||
$this->group_by = array();
|
||
$this->having = array();
|
||
$this->order_by = array();
|
||
$this->limit = 0;
|
||
$this->offset = 0;
|
||
$this->for_update = false;
|
||
}
|
||
|
||
/**
|
||
* 清除所有数据
|
||
*/
|
||
protected function resetAll()
|
||
{
|
||
$this->union = array();
|
||
$this->for_update = false;
|
||
$this->cols = array();
|
||
$this->from = array();
|
||
$this->from_key = -1;
|
||
$this->group_by = array();
|
||
$this->having = array();
|
||
$this->bind_having = array();
|
||
$this->paging = 10;
|
||
$this->bind_values = array();
|
||
$this->where = array();
|
||
$this->bind_where = array();
|
||
$this->order_by = array();
|
||
$this->limit = 0;
|
||
$this->offset = 0;
|
||
$this->flags = array();
|
||
$this->table = '';
|
||
$this->last_insert_id_names = array();
|
||
$this->col_values = array();
|
||
$this->returning = array();
|
||
$this->parameters = array();
|
||
}
|
||
|
||
/**
|
||
* 创建 SELECT SQL
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildSELECT()
|
||
{
|
||
return 'SELECT'
|
||
. $this->buildFlags()
|
||
. $this->buildCols()
|
||
. $this->buildFrom()
|
||
. $this->buildWhere()
|
||
. $this->buildGroupBy()
|
||
. $this->buildHaving()
|
||
. $this->buildOrderBy()
|
||
. $this->buildLimit()
|
||
. $this->buildForUpdate();
|
||
}
|
||
|
||
/**
|
||
* 创建 DELETE SQL
|
||
*/
|
||
protected function buildDELETE()
|
||
{
|
||
return 'DELETE'
|
||
. $this->buildFlags()
|
||
. $this->buildFrom()
|
||
. $this->buildWhere()
|
||
. $this->buildOrderBy()
|
||
. $this->buildLimit()
|
||
. $this->buildReturning();
|
||
}
|
||
|
||
/**
|
||
* 生成 SELECT 列语句
|
||
*
|
||
* @return string
|
||
* @throws Exception
|
||
*/
|
||
protected function buildCols()
|
||
{
|
||
if (!$this->cols) {
|
||
throw new Exception('No columns in the SELECT.');
|
||
}
|
||
|
||
$cols = array();
|
||
foreach ($this->cols as $key => $val) {
|
||
if (is_int($key)) {
|
||
$cols[] = $this->quoteNamesIn($val);
|
||
} else {
|
||
$cols[] = $this->quoteNamesIn("$val AS $key");
|
||
}
|
||
}
|
||
|
||
return $this->indentCsv($cols);
|
||
}
|
||
|
||
/**
|
||
* 生成 FROM 语句.
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildFrom()
|
||
{
|
||
if (!$this->from) {
|
||
return '';
|
||
}
|
||
|
||
$refs = array();
|
||
foreach ($this->from as $from) {
|
||
$refs[] = implode(' ', $from);
|
||
}
|
||
return ' FROM' . $this->indentCsv($refs);
|
||
}
|
||
|
||
/**
|
||
* 生成 GROUP BY 语句.
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildGroupBy()
|
||
{
|
||
if (!$this->group_by) {
|
||
return '';
|
||
}
|
||
return ' GROUP BY' . $this->indentCsv($this->group_by);
|
||
}
|
||
|
||
/**
|
||
* 生成 HAVING 语句.
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildHaving()
|
||
{
|
||
if (!$this->having) {
|
||
return '';
|
||
}
|
||
return ' HAVING' . $this->indent($this->having);
|
||
}
|
||
|
||
/**
|
||
* 生成 FOR UPDATE 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildForUpdate()
|
||
{
|
||
if (!$this->for_update) {
|
||
return '';
|
||
}
|
||
return ' FOR UPDATE';
|
||
}
|
||
|
||
/**
|
||
* where
|
||
*
|
||
* @param string|array $cond
|
||
* @return self
|
||
*/
|
||
public function where($cond)
|
||
{
|
||
if (is_array($cond)) {
|
||
foreach ($cond as $key => $val) {
|
||
if (is_string($key)) {
|
||
$this->addWhere('AND', array($key, $val));
|
||
} else {
|
||
$this->addWhere('AND', array($val));
|
||
}
|
||
}
|
||
} else {
|
||
$this->addWhere('AND', func_get_args());
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* or where
|
||
*
|
||
* @param string|array $cond
|
||
* @return self
|
||
*/
|
||
public function orWhere($cond)
|
||
{
|
||
if (is_array($cond)) {
|
||
foreach ($cond as $key => $val) {
|
||
if (is_string($key)) {
|
||
$this->addWhere('OR', array($key, $val));
|
||
} else {
|
||
$this->addWhere('OR', array($val));
|
||
}
|
||
}
|
||
} else {
|
||
$this->addWhere('OR', func_get_args());
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* limit
|
||
*
|
||
* @param int $limit
|
||
* @return self
|
||
*/
|
||
public function limit($limit)
|
||
{
|
||
$this->limit = (int)$limit;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* limit offset
|
||
*
|
||
* @param int $offset
|
||
* @return self
|
||
*/
|
||
public function offset($offset)
|
||
{
|
||
$this->offset = (int)$offset;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* orderby.
|
||
*
|
||
* @param array $cols
|
||
* @return self
|
||
*/
|
||
public function orderBy(array $cols)
|
||
{
|
||
$this->order_asc = null;
|
||
return $this->addOrderBy($cols);
|
||
}
|
||
|
||
/**
|
||
* order by ASC OR DESC
|
||
*
|
||
* @param array $cols
|
||
* @param bool $order_asc
|
||
* @return self
|
||
*/
|
||
public function orderByASC(array $cols, $order_asc = true)
|
||
{
|
||
$this->order_asc = $order_asc;
|
||
return $this->addOrderBy($cols);
|
||
}
|
||
|
||
/**
|
||
* order by DESC
|
||
*
|
||
* @param array $cols
|
||
* @return self
|
||
*/
|
||
public function orderByDESC(array $cols)
|
||
{
|
||
$this->order_asc = false;
|
||
return $this->addOrderBy($cols);
|
||
}
|
||
|
||
// -------------abstractquery----------
|
||
/**
|
||
* 返回逗号分隔的字符串
|
||
*
|
||
* @param array $list
|
||
* @return string
|
||
*/
|
||
protected function indentCsv(array $list)
|
||
{
|
||
return ' ' . implode(',', $list);
|
||
}
|
||
|
||
/**
|
||
* 返回空格分隔的字符串
|
||
*
|
||
* @param array $list
|
||
* @return string
|
||
*/
|
||
protected function indent(array $list)
|
||
{
|
||
return ' ' . implode(' ', $list);
|
||
}
|
||
|
||
/**
|
||
* 批量为占位符绑定值
|
||
*
|
||
* @param array $bind_values
|
||
* @return self
|
||
*
|
||
*/
|
||
public function bindValues(array $bind_values)
|
||
{
|
||
foreach ($bind_values as $key => $val) {
|
||
$this->bindValue($key, $val);
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 单个为占位符绑定值
|
||
*
|
||
* @param string $name
|
||
* @param mixed $value
|
||
* @return self
|
||
*/
|
||
public function bindValue($name, $value)
|
||
{
|
||
$this->bind_values[$name] = $value;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 生成 flag
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildFlags()
|
||
{
|
||
if (!$this->flags) {
|
||
return '';
|
||
}
|
||
return ' ' . implode(' ', array_keys($this->flags));
|
||
}
|
||
|
||
/**
|
||
* 设置 flag.
|
||
*
|
||
* @param string $flag
|
||
* @param bool $enable
|
||
*/
|
||
protected function setFlag($flag, $enable = true)
|
||
{
|
||
if ($enable) {
|
||
$this->flags[$flag] = true;
|
||
} else {
|
||
unset($this->flags[$flag]);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 重置 flag
|
||
*/
|
||
protected function resetFlags()
|
||
{
|
||
$this->flags = array();
|
||
}
|
||
|
||
/**
|
||
*
|
||
* 添加 where 语句
|
||
*
|
||
* @param string $andor 'AND' or 'OR
|
||
* @param array $conditions
|
||
* @return self
|
||
*
|
||
*/
|
||
protected function addWhere($andor, $conditions)
|
||
{
|
||
$this->addClauseCondWithBind('where', $andor, $conditions);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 添加条件和绑定值
|
||
*
|
||
* @param string $clause where 、having等
|
||
* @param string $andor AND、OR等
|
||
* @param array $conditions
|
||
*/
|
||
protected function addClauseCondWithBind($clause, $andor, $conditions)
|
||
{
|
||
$cond = array_shift($conditions);
|
||
$cond = $this->quoteNamesIn($cond);
|
||
|
||
$bind =& $this->{"bind_{$clause}"};
|
||
foreach ($conditions as $value) {
|
||
$bind[] = $value;
|
||
}
|
||
|
||
$clause =& $this->$clause;
|
||
if ($clause) {
|
||
$clause[] = "$andor $cond";
|
||
} else {
|
||
$clause[] = $cond;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 生成 where 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildWhere()
|
||
{
|
||
if (!$this->where) {
|
||
return '';
|
||
}
|
||
return ' WHERE' . $this->indent($this->where);
|
||
}
|
||
|
||
/**
|
||
* 增加 order by
|
||
*
|
||
* @param array $spec The columns and direction to order by.
|
||
* @return self
|
||
*/
|
||
protected function addOrderBy(array $spec)
|
||
{
|
||
foreach ($spec as $col) {
|
||
$this->order_by[] = $this->quoteNamesIn($col);
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 生成 order by 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildOrderBy()
|
||
{
|
||
if (!$this->order_by) {
|
||
return '';
|
||
}
|
||
|
||
$r = ' ORDER BY' . $this->indentCsv($this->order_by);
|
||
if(isset($this->order_asc)) {
|
||
$r .= ($this->order_asc)? ' ASC' : ' DESC';
|
||
} else {
|
||
// depend on devloper if $this->order_asc is not set.
|
||
// devloper can call function orderBy() to set $this->order_by.
|
||
}
|
||
return $r;
|
||
}
|
||
|
||
/**
|
||
* 生成 limit 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildLimit()
|
||
{
|
||
$has_limit = $this->type == 'DELETE' || $this->type == 'UPDATE';
|
||
$has_offset = $this->type == 'SELECT';
|
||
|
||
if ($has_offset && $this->limit) {
|
||
$clause = " LIMIT {$this->limit}";
|
||
if ($this->offset) {
|
||
$clause .= " OFFSET {$this->offset}";
|
||
}
|
||
return $clause;
|
||
} elseif ($has_limit && $this->limit) {
|
||
return " LIMIT {$this->limit}";
|
||
}
|
||
return '';
|
||
}
|
||
|
||
/**
|
||
* Quotes
|
||
*
|
||
* @param string $spec
|
||
* @return string|array
|
||
*/
|
||
public function quoteName($spec)
|
||
{
|
||
$spec = trim($spec);
|
||
$seps = array(' AS ', ' ', '.');
|
||
foreach ($seps as $sep) {
|
||
$pos = strripos($spec, $sep);
|
||
if ($pos) {
|
||
return $this->quoteNameWithSeparator($spec, $sep, $pos);
|
||
}
|
||
}
|
||
return $this->replaceName($spec);
|
||
}
|
||
|
||
/**
|
||
* 指定分隔符的 Quotes
|
||
*
|
||
* @param string $spec
|
||
* @param string $sep
|
||
* @param int $pos
|
||
* @return string
|
||
*/
|
||
protected function quoteNameWithSeparator($spec, $sep, $pos)
|
||
{
|
||
$len = strlen($sep);
|
||
$part1 = $this->quoteName(substr($spec, 0, $pos));
|
||
$part2 = $this->replaceName(substr($spec, $pos + $len));
|
||
return "{$part1}{$sep}{$part2}";
|
||
}
|
||
|
||
/**
|
||
* Quotes "table.col" 格式的字符串
|
||
*
|
||
* @param string $text
|
||
* @return string|array
|
||
*/
|
||
public function quoteNamesIn($text)
|
||
{
|
||
$list = $this->getListForQuoteNamesIn($text);
|
||
$last = count($list) - 1;
|
||
$text = null;
|
||
foreach ($list as $key => $val) {
|
||
if (($key + 1) % 3) {
|
||
$text .= $this->quoteNamesInLoop($val, $key == $last);
|
||
}
|
||
}
|
||
return $text;
|
||
}
|
||
|
||
/**
|
||
* 返回 quote 元素列表
|
||
*
|
||
* @param string $text
|
||
* @return array
|
||
*/
|
||
protected function getListForQuoteNamesIn($text)
|
||
{
|
||
$apos = "'";
|
||
$quot = '"';
|
||
return preg_split(
|
||
"/(($apos+|$quot+|\\$apos+|\\$quot+).*?\\2)/",
|
||
$text,
|
||
-1,
|
||
PREG_SPLIT_DELIM_CAPTURE
|
||
);
|
||
}
|
||
|
||
/**
|
||
* 循环 quote
|
||
*
|
||
* @param string $val
|
||
* @param bool $is_last
|
||
* @return string
|
||
*/
|
||
protected function quoteNamesInLoop($val, $is_last)
|
||
{
|
||
if ($is_last) {
|
||
return $this->replaceNamesAndAliasIn($val);
|
||
}
|
||
return $this->replaceNamesIn($val);
|
||
}
|
||
|
||
/**
|
||
* 替换成别名
|
||
*
|
||
* @param string $val
|
||
* @return string
|
||
*/
|
||
protected function replaceNamesAndAliasIn($val)
|
||
{
|
||
$quoted = $this->replaceNamesIn($val);
|
||
$pos = strripos($quoted, ' AS ');
|
||
if ($pos !== false) {
|
||
$bracket = strripos($quoted, ')');
|
||
if ($bracket === false) {
|
||
$alias = $this->replaceName(substr($quoted, $pos + 4));
|
||
$quoted = substr($quoted, 0, $pos) . " AS $alias";
|
||
}
|
||
}
|
||
return $quoted;
|
||
}
|
||
|
||
/**
|
||
* Quotes name
|
||
*
|
||
* @param string $name
|
||
* @return string
|
||
*/
|
||
protected function replaceName($name)
|
||
{
|
||
$name = trim($name);
|
||
if ($name == '*') {
|
||
return $name;
|
||
}
|
||
return '`' . $name . '`';
|
||
}
|
||
|
||
/**
|
||
* Quotes
|
||
*
|
||
* @param string $text
|
||
* @return string|array
|
||
*/
|
||
protected function replaceNamesIn($text)
|
||
{
|
||
$is_string_literal = strpos($text, "'") !== false
|
||
|| strpos($text, '"') !== false;
|
||
if ($is_string_literal) {
|
||
return $text;
|
||
}
|
||
|
||
$word = '[a-z_][a-z0-9_]*';
|
||
|
||
$find = "/(\\b)($word)\\.($word)(\\b)/i";
|
||
|
||
$repl = '$1`$2`.`$3`$4';
|
||
|
||
$text = preg_replace($find, $repl, $text);
|
||
|
||
return $text;
|
||
}
|
||
|
||
// ---------- insert --------------
|
||
/**
|
||
* 设置 `table.column` 与 last-insert-id 的映射
|
||
*
|
||
* @param array $last_insert_id_names
|
||
*/
|
||
public function setLastInsertIdNames(array $last_insert_id_names)
|
||
{
|
||
$this->last_insert_id_names = $last_insert_id_names;
|
||
}
|
||
|
||
/**
|
||
* insert into.
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function into($table)
|
||
{
|
||
$this->table = $this->quoteName($table);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 生成 INSERT 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildINSERT()
|
||
{
|
||
return 'INSERT'
|
||
. $this->buildFlags()
|
||
. $this->buildInto()
|
||
. $this->buildValuesForInsert()
|
||
. $this->buildReturning();
|
||
}
|
||
|
||
/**
|
||
* 生成 INTO 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildInto()
|
||
{
|
||
return " INTO " . $this->table;
|
||
}
|
||
|
||
/**
|
||
* PDO::lastInsertId()
|
||
*
|
||
* @param string $col
|
||
* @return mixed
|
||
*/
|
||
public function getLastInsertIdName($col)
|
||
{
|
||
$key = str_replace('`', '', $this->table) . '.' . $col;
|
||
if (isset($this->last_insert_id_names[$key])) {
|
||
return $this->last_insert_id_names[$key];
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* 设置一列，如果有第二各参数，则把第二个参数绑定在占位符上
|
||
*
|
||
* @param string $col
|
||
* @return self
|
||
*/
|
||
public function col($col)
|
||
{
|
||
return call_user_func_array(array($this, 'addCol'), func_get_args());
|
||
}
|
||
|
||
/**
|
||
* 设置多列
|
||
*
|
||
* @param array $cols
|
||
* @return self
|
||
*/
|
||
public function cols(array $cols)
|
||
{
|
||
if ($this->type == 'SELECT') {
|
||
foreach ($cols as $key => $val) {
|
||
$this->addColSELECT($key, $val);
|
||
}
|
||
return $this;
|
||
}
|
||
return $this->addCols($cols);
|
||
}
|
||
|
||
/**
|
||
* 直接设置列的值
|
||
*
|
||
* @param string $col
|
||
* @param string $value
|
||
* @return self
|
||
*/
|
||
public function set($col, $value)
|
||
{
|
||
return $this->setCol($col, $value);
|
||
}
|
||
|
||
/**
|
||
* 为 INSERT 语句绑定值
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildValuesForInsert()
|
||
{
|
||
return ' (' . $this->indentCsv(array_keys($this->col_values)) . ') VALUES (' .
|
||
$this->indentCsv(array_values($this->col_values)) . ')';
|
||
}
|
||
|
||
// ------update-------
|
||
/**
|
||
* 更新哪个表
|
||
*
|
||
* @param string $table
|
||
* @return self
|
||
*/
|
||
public function table($table)
|
||
{
|
||
$this->table = $this->quoteName($table);
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 生成完整 SQL 语句
|
||
*
|
||
* @return string
|
||
* @throws Exception
|
||
*/
|
||
protected function build()
|
||
{
|
||
switch ($this->type) {
|
||
case 'DELETE':
|
||
return $this->buildDELETE();
|
||
case 'INSERT':
|
||
return $this->buildINSERT();
|
||
case 'UPDATE':
|
||
return $this->buildUPDATE();
|
||
case 'SELECT':
|
||
return $this->buildSELECT();
|
||
}
|
||
throw new Exception("type empty");
|
||
}
|
||
|
||
/**
|
||
* 生成更新的 SQL 语句
|
||
*/
|
||
protected function buildUPDATE()
|
||
{
|
||
return 'UPDATE'
|
||
. $this->buildFlags()
|
||
. $this->buildTable()
|
||
. $this->buildValuesForUpdate()
|
||
. $this->buildWhere()
|
||
. $this->buildOrderBy()
|
||
. $this->buildLimit()
|
||
. $this->buildReturning();
|
||
}
|
||
|
||
/**
|
||
* 哪个表
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildTable()
|
||
{
|
||
return " {$this->table}";
|
||
}
|
||
|
||
/**
|
||
* 为更新语句绑定值
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildValuesForUpdate()
|
||
{
|
||
$values = array();
|
||
foreach ($this->col_values as $col => $value) {
|
||
$values[] = "{$col} = {$value}";
|
||
}
|
||
return ' SET' . $this->indentCsv($values);
|
||
}
|
||
|
||
// ----------Dml---------------
|
||
/**
|
||
* 获取绑定的值
|
||
*
|
||
* @return array
|
||
*/
|
||
public function getBindValuesCOMMON()
|
||
{
|
||
$bind_values = $this->bind_values;
|
||
$i = 1;
|
||
foreach ($this->bind_where as $val) {
|
||
$bind_values[$i] = $val;
|
||
$i++;
|
||
}
|
||
return $bind_values;
|
||
}
|
||
|
||
/**
|
||
* 设置列
|
||
*
|
||
* @param string $col
|
||
* @return self
|
||
*/
|
||
protected function addCol($col)
|
||
{
|
||
$key = $this->quoteName($col);
|
||
$this->col_values[$key] = ":$col";
|
||
$args = func_get_args();
|
||
if (count($args) > 1) {
|
||
$this->bindValue($col, $args[1]);
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置多个列
|
||
*
|
||
* @param array $cols
|
||
* @return self
|
||
*/
|
||
protected function addCols(array $cols)
|
||
{
|
||
foreach ($cols as $key => $val) {
|
||
if (is_int($key)) {
|
||
$this->addCol($val);
|
||
} else {
|
||
$this->addCol($key, $val);
|
||
}
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 设置单列的值
|
||
*
|
||
* @param string $col .
|
||
* @param string $value
|
||
* @return self
|
||
*/
|
||
protected function setCol($col, $value)
|
||
{
|
||
if ($value === null) {
|
||
$value = 'NULL';
|
||
}
|
||
|
||
$key = $this->quoteName($col);
|
||
$value = $this->quoteNamesIn($value);
|
||
$this->col_values[$key] = $value;
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 增加返回的列
|
||
*
|
||
* @param array $cols
|
||
* @return self
|
||
*
|
||
*/
|
||
protected function addReturning(array $cols)
|
||
{
|
||
foreach ($cols as $col) {
|
||
$this->returning[] = $this->quoteNamesIn($col);
|
||
}
|
||
return $this;
|
||
}
|
||
|
||
/**
|
||
* 生成 RETURNING 语句
|
||
*
|
||
* @return string
|
||
*/
|
||
protected function buildReturning()
|
||
{
|
||
if (!$this->returning) {
|
||
return '';
|
||
}
|
||
return ' RETURNING' . $this->indentCsv($this->returning);
|
||
}
|
||
|
||
/**
|
||
* 构造函数
|
||
*
|
||
* @param string $host
|
||
* @param int $port
|
||
* @param string $user
|
||
* @param string $password
|
||
* @param string $db_name
|
||
* @param string $charset
|
||
*/
|
||
public function __construct($host, $port, $user, $password, $db_name, $charset = 'utf8mb4')
|
||
{
|
||
$this->settings = array(
|
||
'host' => $host,
|
||
'port' => $port,
|
||
'user' => $user,
|
||
'password' => $password,
|
||
'dbname' => $db_name,
|
||
'charset' => $charset,
|
||
);
|
||
$this->connect();
|
||
}
|
||
|
||
/**
|
||
* 创建 PDO 实例
|
||
*/
|
||
protected function connect()
|
||
{
|
||
$dsn = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' .
|
||
$this->settings["host"] . ';port=' . $this->settings['port'];
|
||
$this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"],
|
||
array(
|
||
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . (!empty($this->settings['charset']) ?
|
||
$this->settings['charset'] : 'utf8mb4')
|
||
));
|
||
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
||
$this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
|
||
$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
|
||
}
|
||
|
||
/**
|
||
* 关闭连接
|
||
*/
|
||
public function closeConnection()
|
||
{
|
||
$this->pdo = null;
|
||
}
|
||
|
||
/**
|
||
* 执行
|
||
*
|
||
* @param string $query
|
||
* @param string $parameters
|
||
* @throws PDOException
|
||
*/
|
||
protected function execute($query, $parameters = "")
|
||
{
|
||
try {
|
||
if (is_null($this->pdo)) {
|
||
$this->connect();
|
||
}
|
||
$this->sQuery = @$this->pdo->prepare($query);
|
||
if(!method_exists($this->sQuery, 'execute')) {
|
||
throw new \PDOException('connect error not execute', 2006);
|
||
}
|
||
$this->bindMore($parameters);
|
||
if (!empty($this->parameters)) {
|
||
foreach ($this->parameters as $param) {
|
||
$this->sQuery->bindParam($param[0], $param[1]);
|
||
}
|
||
}
|
||
$this->success = $this->sQuery->execute();
|
||
} catch (PDOException $e) {
|
||
$error_code = isset($e->errorInfo[1]) ? $e->errorInfo[1] : $e->getCode();
|
||
if (in_array($error_code, [2006, 2013])) {
|
||
$this->closeConnection();
|
||
$this->connect();
|
||
|
||
try {
|
||
$this->sQuery = $this->pdo->prepare($query);
|
||
$this->bindMore($parameters);
|
||
if (!empty($this->parameters)) {
|
||
foreach ($this->parameters as $param) {
|
||
$this->sQuery->bindParam($param[0], $param[1]);
|
||
}
|
||
}
|
||
$this->success = $this->sQuery->execute();
|
||
} catch (PDOException $ex) {
|
||
$this->rollBackTrans();
|
||
throw $ex;
|
||
}
|
||
} else {
|
||
$this->rollBackTrans();
|
||
$msg = $e->getMessage();
|
||
$err_msg = "SQL:".$this->lastSQL()." ".$msg;
|
||
$exception = new \PDOException($err_msg, (int)$e->getCode());
|
||
throw $exception;
|
||
}
|
||
}
|
||
$this->parameters = array();
|
||
}
|
||
|
||
/**
|
||
* 绑定
|
||
*
|
||
* @param string $para
|
||
* @param string $value
|
||
*/
|
||
public function bind($para, $value)
|
||
{
|
||
if (is_string($para)) {
|
||
$this->parameters[sizeof($this->parameters)] = array(":" . $para, $value);
|
||
} else {
|
||
$this->parameters[sizeof($this->parameters)] = array($para, $value);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 绑定多个
|
||
*
|
||
* @param array $parray
|
||
*/
|
||
public function bindMore($parray)
|
||
{
|
||
if (empty($this->parameters) && is_array($parray)) {
|
||
$columns = array_keys($parray);
|
||
foreach ($columns as $i => &$column) {
|
||
$this->bind($column, $parray[$column]);
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 执行 SQL
|
||
*
|
||
* @param string $query
|
||
* @param array $params
|
||
* @param int $fetchmode
|
||
* @return mixed
|
||
*/
|
||
public function query($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC)
|
||
{
|
||
$query = trim($query);
|
||
if (empty($query)) {
|
||
|
||
$union = '';
|
||
if (! empty($this->union)) {
|
||
$union = implode(PHP_EOL, $this->union) . PHP_EOL;
|
||
}
|
||
|
||
$query = $union . $this->build();
|
||
|
||
if (!$params) {
|
||
$params = $this->getBindValues();
|
||
}
|
||
}
|
||
|
||
$this->resetAll();
|
||
$this->lastSql = $query;
|
||
|
||
$this->execute($query, $params);
|
||
|
||
$rawStatement = explode(" ", $query);
|
||
|
||
$statement = strtolower(trim($rawStatement[0]));
|
||
if ($statement === 'select' || $statement === 'show') {
|
||
return $this->sQuery->fetchAll($fetchmode);
|
||
} elseif ($statement === 'update' || $statement === 'delete' || $statement === 'replace') {
|
||
return $this->sQuery->rowCount();
|
||
} elseif ($statement === 'insert') {
|
||
if ($this->sQuery->rowCount() > 0) {
|
||
return $this->lastInsertId();
|
||
}
|
||
} else {
|
||
return null;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
/**
|
||
* 返回一列
|
||
*
|
||
* @param string $query
|
||
* @param array $params
|
||
* @return array
|
||
*/
|
||
public function column($query = '', $params = null)
|
||
{
|
||
$query = trim($query);
|
||
if (empty($query)) {
|
||
|
||
$union = '';
|
||
if (! empty($this->union)) {
|
||
$union = implode(PHP_EOL, $this->union) . PHP_EOL;
|
||
}
|
||
|
||
$query = $union . $this->build();
|
||
|
||
if (!$params) {
|
||
$params = $this->getBindValues();
|
||
}
|
||
}
|
||
|
||
$this->resetAll();
|
||
$this->lastSql = $query;
|
||
|
||
$this->execute($query, $params);
|
||
$columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);
|
||
$column = null;
|
||
foreach ($columns as $cells) {
|
||
$column[] = $cells[0];
|
||
}
|
||
return $column;
|
||
}
|
||
|
||
/**
|
||
* 返回一行
|
||
*
|
||
* @param string $query
|
||
* @param array $params
|
||
* @param int $fetchmode
|
||
* @return array
|
||
*/
|
||
public function row($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC)
|
||
{
|
||
$query = trim($query);
|
||
if (empty($query)) {
|
||
|
||
$union = '';
|
||
if (! empty($this->union)) {
|
||
$union = implode(PHP_EOL, $this->union) . PHP_EOL;
|
||
}
|
||
|
||
$query = $union . $this->build();
|
||
|
||
if (!$params) {
|
||
$params = $this->getBindValues();
|
||
}
|
||
}
|
||
|
||
$this->resetAll();
|
||
$this->lastSql = $query;
|
||
|
||
$this->execute($query, $params);
|
||
return $this->sQuery->fetch($fetchmode);
|
||
}
|
||
|
||
/**
|
||
* 返回单个值
|
||
*
|
||
* @param string $query
|
||
* @param array $params
|
||
* @return string
|
||
*/
|
||
public function single($query = '', $params = null)
|
||
{
|
||
$query = trim($query);
|
||
if (empty($query)) {
|
||
|
||
$union = '';
|
||
if (! empty($this->union)) {
|
||
$union = implode(PHP_EOL, $this->union) . PHP_EOL;
|
||
}
|
||
|
||
$query = $union . $this->build();
|
||
|
||
if (!$params) {
|
||
$params = $this->getBindValues();
|
||
}
|
||
}
|
||
|
||
$this->resetAll();
|
||
$this->lastSql = $query;
|
||
|
||
$this->execute($query, $params);
|
||
return $this->sQuery->fetchColumn();
|
||
}
|
||
|
||
/**
|
||
* 返回 lastInsertId
|
||
*
|
||
* @return string
|
||
*/
|
||
public function lastInsertId()
|
||
{
|
||
return $this->pdo->lastInsertId();
|
||
}
|
||
|
||
/**
|
||
* 返回最后一条执行的 sql
|
||
*
|
||
* @return string
|
||
*/
|
||
public function lastSQL()
|
||
{
|
||
return $this->lastSql;
|
||
}
|
||
|
||
/**
|
||
* 开始事务
|
||
*/
|
||
public function beginTrans()
|
||
{
|
||
try {
|
||
if (is_null($this->pdo)) {
|
||
$this->connect();
|
||
}
|
||
return $this->pdo->beginTransaction();
|
||
} catch (PDOException $e) {
|
||
// 服务端断开时重连一次
|
||
if (isset($e->errorInfo[1]) && in_array($e->errorInfo[1], [2006, 2013])) {
|
||
$this->closeConnection();
|
||
$this->connect();
|
||
return $this->pdo->beginTransaction();
|
||
} else {
|
||
throw $e;
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 提交事务
|
||
*/
|
||
public function commitTrans()
|
||
{
|
||
return $this->pdo->commit();
|
||
}
|
||
|
||
/**
|
||
* 事务回滚
|
||
*/
|
||
public function rollBackTrans()
|
||
{
|
||
if ($this->pdo->inTransaction()) {
|
||
return $this->pdo->rollBack();
|
||
}
|
||
return true;
|
||
}
|
||
}
|
||
?>
|