php中connection,Connection.php

namespace Workerman\MySQL;

use Exception;

use PDO;

use PDOException;

/**

* 数据库连接类，依赖 PDO_MYSQL 扩展

* 在 https://github.com/auraphp/Aura.SqlQuery 的基础上修改而成

*/

class Connection

{

/**

* 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 = true;

/**

* 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) {

$this->cols[$parts[1]] = $parts[0];

} elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {

$this->cols[$parts[2]] = $parts[0];

} else {

$this->cols[] = $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)

{

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 '';

}

if ($this->order_asc) {

return ' ORDER BY' . $this->indentCsv($this->order_by) . ' ASC';

} else {

return ' ORDER BY' . $this->indentCsv($this->order_by) . ' DESC';

}

}

/**

* 生成 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) {

$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 = 'utf8')

{

$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'] : 'utf8')

));

$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 {

$this->sQuery = @$this->pdo->prepare($query);

$this->bindMore($parameters);

if (!empty($this->parameters)) {

foreach ($this->parameters as $param) {

$parameters = explode("\x7F", $param);

$this->sQuery->bindParam($parameters[0], $parameters[1]);

}

}

$this->success = $this->sQuery->execute();

} catch (PDOException $e) {

// 服务端断开时重连一次

if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) {

$this->closeConnection();

$this->connect();

try {

$this->sQuery = $this->pdo->prepare($query);

$this->bindMore($parameters);

if (!empty($this->parameters)) {

foreach ($this->parameters as $param) {

$parameters = explode("\x7F", $param);

$this->sQuery->bindParam($parameters[0], $parameters[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)] = ":" . $para . "\x7F" . $value;

} else {

$this->parameters[sizeof($this->parameters)] = $para . "\x7F" . $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)) {

$query = $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') {

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)) {

$query = $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)) {

$query = $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)) {

$query = $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 {

return $this->pdo->beginTransaction();

} catch (PDOException $e) {

// 服务端断开时重连一次

if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 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;

}

}

一键复制

编辑

Web IDE

原始数据

按行查看

历史