<?php
/*
* Copyright (c) 2018, 北京博习园教育科技有限公司
* All rights reserved.
*
* 文件名称: xmodel.php
* 摘 要: 模型公共类
* 作 者: 刘泉皓
* $Id$
*/
/**
* 数据库连接类，依赖 PDO_MYSQL 扩展
* walkor:在 https://github.com/auraphp/Aura.SqlQuery 的基础上修改而成
* liuquanhao:在 https://github.com/walkor/mysql 的基础上修改而成
*/
require_once __DIR__ . '/' . '../../../config/config.php';
require_once __DIR__ . '/' . '../function/cache.php';
require_once __DIR__ . '/' . '../function/comm_func.php';
class XModel
{
/**
* 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 $readSettings = array();
protected $writeSettings = array();
/**
* sql 的参数
*
* @var array
*/
protected $parameters = array();
/**
* 最后一条直行的 sql
*
* @var string
*/
protected $lastSql = '';
/**
* 使用memcache
*
* @var object
*/
protected $mem = null;
/**
* memcache key
*
* @var string
*/
protected $memKey = '';
/**
* memcache active time
*
* @var int
*/
protected $memTime = 0;
/**
* log
*
* @var boolean
*/
protected $log = false;
protected $logName = __CLASS__;
/**
* 是否执行成功
*
* @var bool
*/
protected $success = false;
/**
* 构造函数
*/
public function __construct($dbname = '')
{
if (!empty($dbname)) {
$this->database($dbname);
}
// 使用惰性连接
//$this->connect();
}
/*
* 选择数据库
*/
public function database($dbname)
{
switch ($dbname) {
case 'tongzhuo_ask_db':
$this->selectAskHost();
break;
case 'tongzhuonet_db':
$this->selectTznetHost();
break;
case 'tongzhuo_weixin_db':
$this->selectWeixinHost();
break;
case 'tongzhuo_user_db':
$this->selectUserHost();
break;
case 'tongzhuo_examination_db':
$this->selectExaminationHost();
break;
case 'tongzhuo_tv_db':
$this->selectTvHost();
break;
case 'tongzhuo_db':
$this->selectTzHost();
break;
default:
throw new Exception('Not found database!');
}
return $this;
}
/*
* 使用memcache
*/
public function mem($key = '', $activeTime = 0)
{
global $mem;
$this->mem = $mem;
$this->memKey = $key;
$this->memTime = $activeTime;
return $this;
}
/**
* 选择哪些列
*
* @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;
}
/**
* 从哪个表hash删除
*
* @param string $pre
* @param string $salt
* @return self
*/
public function deleteHash($pre, $salt)
{
$table = $this->hashTable($pre, $salt);
return $this->delete($table);
}
/**
* 更新哪个表
*
* @param string $table
* @return self
*/
public function update($table)
{
$this->type = 'UPDATE';
$this->table = $this->quoteName($table);
return $this;
}
/**
* 更新哪个hash表
*
* @param string $pre
* @param string $salt
* @return self
*/
public function updateHash($pre, $salt)
{
$table = $this->hashTable($pre, $salt);
return $this->update($table);
}
/**
* 向哪个表插入
*
* @param string $table
* @return self
*/
public function insert($table)
{
$this->type = 'INSERT';
$this->table = $this->quoteName($table);
return $this;
}
/**
* 向哪个hash表插入
*
* @param string $pre
* @param string $hash
* @return self
*/
public function insertHash($pre, $salt)
{
$table = $this->hashTable($pre, $salt);
return $this->insert($table);
}
/**
* from 哪个表
*
* @param string $table
* @return self
*/
public function from($table)
{
return $this->fromRaw($this->quoteName($table));
}
/**
* from 哪个hash表
*
* @param string $pre
* @param string $salt
* @return self
*/
public function fromHash($pre, $salt)
{
$table = $this->hashTable($pre, $salt);
return $this->from($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;
}
/**
* 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;
}
/*
* 重连tv读写数据库
*/
protected function selectTvHost()
{
$this->readSettings['user'] = $GLOBALS['user_tv_read'];
$this->readSettings['password'] = $GLOBALS['pw_tv_read'];
$this->readSettings['host'] = $GLOBALS['host_tv_read'];
$this->readSettings['port'] = $GLOBALS['port_tv_read'];
$this->readSettings['dbname'] = $GLOBALS['database_tv_read'];
$this->writeSettings['user'] = $GLOBALS['user_tv_write'];
$this->writeSettings['password'] = $GLOBALS['pw_tv_write'];
$this->writeSettings['host'] = $GLOBALS['host_tv_write'];
$this->writeSettings['port'] = $GLOBALS['port_tv_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_tv_write'];
}
/*
* 重连ask读写数据库
*/
protected function selectAskHost()
{
$this->readSettings['user'] = $GLOBALS['user_ask_read'];
$this->readSettings['password'] = $GLOBALS['pw_ask_read'];
$this->readSettings['host'] = $GLOBALS['host_ask_read'];
$this->readSettings['port'] = $GLOBALS['port_ask_read'];
$this->readSettings['dbname'] = $GLOBALS['database_ask_read'];
$this->writeSettings['user'] = $GLOBALS['user_ask_write'];
$this->writeSettings['password'] = $GLOBALS['pw_ask_write'];
$this->writeSettings['host'] = $GLOBALS['host_ask_write'];
$this->writeSettings['port'] = $GLOBALS['port_ask_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_ask_write'];
}
/*
* 重连net读写数据库
*/
protected function selectTznetHost()
{
$this->readSettings['user'] = $GLOBALS['user_tz_read'];
$this->readSettings['password'] = $GLOBALS['pw_tz_read'];
$this->readSettings['host'] = $GLOBALS['host_tz_read'];
$this->readSettings['port'] = $GLOBALS['port_tz_read'];
$this->readSettings['dbname'] = $GLOBALS['database_tz_read'];
$this->writeSettings['user'] = $GLOBALS['user_tz_write'];
$this->writeSettings['password'] = $GLOBALS['pw_tz_write'];
$this->writeSettings['host'] = $GLOBALS['host_tz_write'];
$this->writeSettings['port'] = $GLOBALS['port_tz_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_tz_write'];
}
/*
* 重连weixin读写数据库
*/
protected function selectWeixinHost()
{
$this->readSettings['user'] = $GLOBALS['user_wx_read'];
$this->readSettings['password'] = $GLOBALS['pw_wx_read'];
$this->readSettings['host'] = $GLOBALS['host_wx_read'];
$this->readSettings['port'] = $GLOBALS['port_wx_read'];
$this->readSettings['dbname'] = $GLOBALS['database_wx_read'];
$this->writeSettings['user'] = $GLOBALS['user_wx_write'];
$this->writeSettings['password'] = $GLOBALS['pw_wx_write'];
$this->writeSettings['host'] = $GLOBALS['host_wx_write'];
$this->writeSettings['port'] = $GLOBALS['port_wx_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_wx_write'];
}
/*
* 重连用户日志读写数据库
*/
protected function selectUserHost()
{
$this->readSettings['user'] = $GLOBALS['user_user_read'];
$this->readSettings['password'] = $GLOBALS['pw_user_read'];
$this->readSettings['host'] = $GLOBALS['host_user_read'];
$this->readSettings['port'] = $GLOBALS['port_user_read'];
$this->readSettings['dbname'] = $GLOBALS['database_user_read'];
$this->writeSettings['user'] = $GLOBALS['user_user_write'];
$this->writeSettings['password'] = $GLOBALS['pw_user_write'];
$this->writeSettings['host'] = $GLOBALS['host_user_write'];
$this->writeSettings['port'] = $GLOBALS['port_user_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_user_write'];
}
/*
* 模拟考试读写数据库
*/
protected function selectExaminationHost()
{
$this->readSettings['user'] = $GLOBALS['user_ex_read'];
$this->readSettings['password'] = $GLOBALS['pw_ex_read'];
$this->readSettings['host'] = $GLOBALS['host_ex_read'];
$this->readSettings['port'] = $GLOBALS['port_ex_read'];
$this->readSettings['dbname'] = $GLOBALS['database_ex_read'];
$this->writeSettings['user'] = $GLOBALS['user_user_write'];
$this->writeSettings['password'] = $GLOBALS['pw_ex_write'];
$this->writeSettings['host'] = $GLOBALS['host_ex_write'];
$this->writeSettings['port'] = $GLOBALS['port_ex_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_ex_write'];
}
/*
* 重连tongzhuo读写数据库
*/
protected function selectTzHost()
{
$this->readSettings['user'] = $GLOBALS['user_one_read'];
$this->readSettings['password'] = $GLOBALS['pw_one_read'];
$this->readSettings['host'] = $GLOBALS['host_one_read'];
$this->readSettings['port'] = $GLOBALS['port_one_read'];
$this->readSettings['dbname'] = $GLOBALS['database_one_read'];
$this->writeSettings['user'] = $GLOBALS['user_one_write'];
$this->writeSettings['password'] = $GLOBALS['pw_one_write'];
$this->writeSettings['host'] = $GLOBALS['host_one_write'];
$this->writeSettings['port'] = $GLOBALS['port_one_write'];
$this->writeSettings['dbname'] = $GLOBALS['database_one_write'];
}
/**
* 创建 PDO 实例
*/
protected function connect()
{
if ($this->type == 'SELECT') {
if (
empty($this->readSettings['dbname']) &&
empty($this->readSettings['host']) &&
empty($this->readSettings['port']) &&
empty($this->readSettings['user']) &&
empty($this->readSettings['password'])) {
throw new Exception('Database configuration fail!');
}
$dsn = 'mysql:dbname=' . $this->readSettings['dbname'] .
';host=' . $this->readSettings['host'] . ';port=' . $this->readSettings['port'] .
';charset=utf8';
$this->pdo = new PDO(
$dsn,
$this->readSettings['user'],
$this->readSettings['password']
);
} else {
if (
empty($this->writeSettings['dbname']) &&
empty($this->writeSettings['host']) &&
empty($this->writeSettings['port']) &&
empty($this->writeSettings['user']) &&
empty($this->writeSettings['password'])) {
throw new Exception('Database configuration fail!');
}
$dsn = 'mysql:dbname=' . $this->writeSettings['dbname'] .
';host=' . $this->writeSettings['host'] . ';port=' . $this->writeSettings['port'] .
';charset=utf8';
$this->pdo = new PDO(
$dsn,
$this->writeSettings['user'],
$this->writeSettings['password']
);
}
$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);
}
/*
* 清空本次mem设置信息
*/
protected function resetMem()
{
$this->mem = null;
$this->memKey = '';
$this->memTime = 0;
}
/**
* 关闭连接
*/
public function closeConnection()
{
$this->pdo = null;
}
/**
*
* 设置 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;
}
/**
* 设置 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;
}
/**
* 设置 FOR UPDATE 标记
*
* @param bool $enable
* @return self
*/
public function forUpdate($enable = true)
{
$this->for_update = (bool) $enable;
return $this;
}
/**
* 设置每页多少条记录
*
* @param int $paging
* @return self
*/
public function setPaging($paging)
{
$this->paging = (int) $paging;
return $this;
}
/**
* 获取每页多少条记录
*
* @return int
*/
public function getPaging()
{
return $this->paging;
}
/**
* 序列化
*
* @return string
*/
public function __toString()
{
$union = '';
if ($this->union) {
$union = implode(' ', $this->union) . ' ';
}
return $union . $this->build();
}
/**
* 获取绑定在占位符上的值
*/
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($col, $alias)
{
if (is_string($col)) {
$this->cols[$alias] = $col;
} else {
$this->addColWithAlias($alias);
}
}
/**
* SELECT 增加选择的列
*
* @param string $spec
*/
protected function addColWithAlias($spec)
{
$parts = explode(' ', trim($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;
}
}
/**
* 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);
}
/**
* 设置每页的记录数量
*
* @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 (false !== $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) {
$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 $query
* @param string $parameters
* @throws PDOException
*/
protected function execute($query, $parameters = "")
{
try {
$this->connect();
$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();
try {
$this->connect();
$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;
}
}
if ($this->log) {
$message = sprintf("%s|%s", $query, implode(',', $this->parameters));
comm_log($this->logName, $message);
}
$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();
}
} else {
$this->parseType($query);
}
$this->resetAll();
$this->lastSql = $query;
if ($this->type == 'SELECT' && $this->mem) {
if (empty($this->memKey)) {
$this->memKey = $query . ' [' . implode(',', $params) . ']';
}
$buf = $this->mem->get($this->memKey);
if ($buf !== false) {
$this->resetMem();
return $buf;
}
}
$this->execute($query, $params);
$rawStatement = explode(" ", $query);
$statement = strtolower(trim($rawStatement[0]));
if ($statement === 'select' || $statement === 'show') {
$rs = $this->sQuery->fetchAll($fetchmode);
if ($statement === 'select' && $this->mem) {
$this->mem->set($this->memKey, $rs, 0, $this->memTime);
$this->resetMem();
}
return $rs;
} 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;
if ($this->mem) {
if (empty($this->memKey)) {
$this->memKey = $query . ' [' . implode(',', $params) . ']';
}
$buf = $this->mem->get($this->memKey);
if ($buf !== false) {
$this->resetMem();
return $buf;
}
}
if (empty($this->type)) {
$this->parseType($query);
}
$this->execute($query, $params);
$columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);
$column = null;
foreach ($columns as $cells) {
$column[] = $cells[0];
}
if ($this->mem) {
$this->mem->set($this->memKey, $column, 0, $this->memTime);
$this->resetMem();
}
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;
if ($this->mem) {
if (empty($this->memKey)) {
$this->memKey = $query . ' [' . implode(',', $params) . ']';
}
$buf = $this->mem->get($this->memKey);
if ($buf !== false) {
$this->resetMem();
return $buf;
}
}
if (empty($this->type)) {
$this->parseType($query);
}
$this->execute($query, $params);
$rs = $this->sQuery->fetch($fetchmode);
if ($this->mem) {
$this->mem->set($this->memKey, $rs, 0, $this->memTime);
$this->resetMem();
}
return $rs;
}
/**
* 返回单个值
*
* @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();
if ($this->mem) {
if (empty($this->memKey)) {
$this->memKey = $query . ' [' . implode(',', $params) . ']';
}
$buf = $this->mem->get($this->memKey);
if ($buf !== false) {
$this->resetMem();
return $buf;
}
}
if (empty($this->type)) {
$this->parseType($query);
}
$this->execute($query, $params);
$rs = $this->sQuery->fetchColumn();
if ($this->mem) {
$this->mem->set($this->memKey, $rs, 0, $this->memTime);
$this->resetMem();
}
return $rs;
}
/**
* 返回 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) {
if ($this->pdo->inTransaction()) {
return $this->pdo->rollBack();
}
}
return true;
}
/**
* hash分表
*/
protected function hashTable($pre, $salt)
{
if (empty($salt)) {
return $pre . '0';
}
$salt = strtolower($salt);
if (is_numeric($salt) && strlen($salt) > 1) {
$hash = substr($salt, -2, 2);
} else if (preg_match("/^[0-9a-z]{1}$/", substr($salt, -1, 1))) {
$hash = substr($salt, -1, 1);
} else {
return $pre . '0';
}
return $pre . $hash;
}
public function log($logName = '')
{
$this->log = true;
if (!empty($logName)) {
$this->logName = $logName;
}
return $this;
}
/**
* 没有设置type，也不是select(),update(),insert(),delete()方法，则分析sql语句，设置type
*/
public function parseType($query)
{
$query = trim($query);
if (empty($query)) {
$this->type = 'SELECT';
}
$this->type = strtoupper(explode(' ', $query)[0]);
}
/**
* （废弃，已自动判断）
* SELECT:读库
* 其他：写库
*/
public function setType($type) {
$this->type = strtoupper($type);
}
}