文章摘要
这篇文章介绍了一个用于数据库连接池的PHP类，名为`Connection`，主要用于生成和执行各种SQL语句。该类提供了基本的SQL操作方法（如SELECT、DELETE、UPDATE等），并支持高级功能，如分页查询、加锁、缓存管理等。文章详细描述了类的属性（如连接池大小、是否加锁等）和方法（如`select`、`delete`、`update`等），并解释了其在数据库操作中的应用。文章还提到了一些控制逻辑，如`for_update`、`cache`等，这些功能可以进一步提升数据库操作的效率和安全性。总体来看，该类为数据库开发提供了丰富的工具和方法。
<?php
class Connection
{
protected $union=array();
protected $for_update=false;
protected $cols=array();
protected $from=array();
protected $from_key=-1;
protected $group_by=array();
protected $having=array();
protected $bind_having=array();
protected $paging=10;
protected $bind_values=array();
protected $where=array();
protected $bind_where=array();
protected $order_by=array();
protected $order_asc=true;
protected $limit=0;
protected $offset=0;
protected $flags=array();
protected $table;
protected $last_insert_id_names=array();
protected $col_values;
protected $returning=array();
protected $type=”;
protected $pdo;
protected $sQuery;
protected $settings=array();
protected $parameters=array();
protected $lastSql=”;
protected $success=false;
public function select($cols=’*’)
{
$this->type=’SELECT’;
if (!is_array($cols)) {
$cols=explode(‘,’, $cols);
}
$this->cols($cols);
return $this;
}
public function delete($table)
{
$this->type=’DELETE’;
$this->table=$this->quoteName($table);
$this->fromRaw($this->quoteName($table));
return $this;
}
public function update($table)
{
$this->type=’UPDATE’;
$this->table=$this->quoteName($table);
return $this;
}
public function insert($table)
{
$this->type=’INSERT’;
$this->table=$this->quoteName($table);
return $this;
}
public function calcFoundRows($enable=true)
{
$this->setFlag(‘SQL_CALC_FOUND_ROWS’, $enable);
return $this;
}
public function cache($enable=true)
{
$this->setFlag(‘SQL_CACHE’, $enable);
return $this;
}
public function noCache($enable=true)
{
$this->setFlag(‘SQL_NO_CACHE’, $enable);
return $this;
}
public function straightJoin($enable=true)
{
$this->setFlag(‘STRAIGHT_JOIN’, $enable);
return $this;
}
public function highPriority($enable=true)
{
$this->setFlag(‘HIGH_PRIORITY’, $enable);
return $this;
}
public function smallResult($enable=true)
{
$this->setFlag(‘SQL_SMALL_RESULT’, $enable);
return $this;
}
public function bigResult($enable=true)
{
$this->setFlag(‘SQL_BIG_RESULT’, $enable);
return $this;
}
public function bufferResult($enable=true)
{
$this->setFlag(‘SQL_BUFFER_RESULT’, $enable);
return $this;
}
public function forUpdate($enable=true)
{
$this->for_update=(bool)$enable;
return $this;
}
public function distinct($enable=true)
{
$this->setFlag(‘DISTINCT’, $enable);
return $this;
}
public function lowPriority($enable=true)
{
$this->setFlag(‘LOW_PRIORITY’, $enable);
return $this;
}
public function ignore($enable=true)
{
$this->setFlag(‘IGNORE’, $enable);
return $this;
}
public function quick($enable=true)
{
$this->setFlag(‘QUICK’, $enable);
return $this;
}
public function delayed($enable=true)
{
$this->setFlag(‘DELAYED’, $enable);
return $this;
}
public function __toString()
{
$union=”;
if ($this->union) {
$union=implode(‘ ‘, $this->union) . ‘ ‘;
}
return $union . $this->build();
}
public function setPaging($paging)
{
$this->paging=(int)$paging;
return $this;
}
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”);
}
}
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;
}
protected function addColSELECT($key, $val)
{
if (is_string($key)) {
$this->cols[$val]=$key;
} else {
$this->addColWithAlias($val);
}
}
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);
}
}
public function from($table)
{
return $this->fromRaw($this->quoteName($table));
}
public function fromRaw($table)
{
$this->from[]=array($table);
$this->from_key++;
return $this;
}
public function fromSubSelect($table, $name)
{
$this->from[]=array(“($table) AS ” . $this->quoteName($name));
$this->from_key++;
return $this;
}
public function join($table, $cond=null, $type=”)
{
return $this->joinInternal($type, $table, $cond);
}
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;
}
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;
}
public function innerJoin($table, $cond=null)
{
return $this->joinInternal(‘INNER’, $table, $cond);
}
public function leftJoin($table, $cond=null)
{
return $this->joinInternal(‘LEFT’, $table, $cond);
}
public function rightJoin($table, $cond=null)
{
return $this->joinInternal(‘RIGHT’, $table, $cond);
}
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;
}
public function groupBy(array $cols)
{
foreach ($cols as $col) {
$this->group_by[]=$this->quoteNamesIn($col);
}
return $this;
}
public function having($cond)
{
$this->addClauseCondWithBind(‘having’, ‘AND’, func_get_args());
return $this;
}
public function orHaving($cond)
{
$this->addClauseCondWithBind(‘having’, ‘OR’, func_get_args());
return $this;
}
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;
}
public function union()
{
$this->union[]=$this->build() . ‘ UNION’;
$this->reset();
return $this;
}
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();
}
protected function buildSELECT()
{
return ‘SELECT’
. $this->buildFlags()
. $this->buildCols()
. $this->buildFrom()
. $this->buildWhere()
. $this->buildGroupBy()
. $this->buildHaving()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildForUpdate();
}
protected function buildDELETE()
{
return ‘DELETE’
. $this->buildFlags()
. $this->buildFrom()
. $this->buildWhere()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildReturning();
}
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);
}
protected function buildFrom()
{
if (!$this->from) {
return ”;
}
$refs=array();
foreach ($this->from as $from) {
$refs[]=implode(‘ ‘, $from);
}
return ‘ FROM’ . $this->indentCsv($refs);
}
protected function buildGroupBy()
{
if (!$this->group_by) {
return ”;
}
return ‘ GROUP BY’ . $this->indentCsv($this->group_by);
}
protected function buildHaving()
{
if (!$this->having) {
return ”;
}
return ‘ HAVING’ . $this->indent($this->having);
}
protected function buildForUpdate()
{
if (!$this->for_update) {
return ”;
}
return ‘ FOR UPDATE’;
}
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;
}
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;
}
public function limit($limit)
{
$this->limit=(int)$limit;
return $this;
}
public function offset($offset)
{
$this->offset=(int)$offset;
return $this;
}
public function orderBy(array $cols)
{
return $this->addOrderBy($cols);
}
public function orderByASC(array $cols, $order_asc=true)
{
$this->order_asc=$order_asc;
return $this->addOrderBy($cols);
}
public function orderByDESC(array $cols)
{
$this->order_asc=false;
return $this->addOrderBy($cols);
}
// ————-abstractquery———-
protected function indentCsv(array $list)
{
return ‘ ‘ . implode(‘,’, $list);
}
protected function indent(array $list)
{
return ‘ ‘ . implode(‘ ‘, $list);
}
public function bindValues(array $bind_values)
{
foreach ($bind_values as $key=> $val) {
$this->bindValue($key, $val);
}
return $this;
}
public function bindValue($name, $value)
{
$this->bind_values[$name]=$value;
return $this;
}
protected function buildFlags()
{
if (!$this->flags) {
return ”;
}
return ‘ ‘ . implode(‘ ‘, array_keys($this->flags));
}
protected function setFlag($flag, $enable=true)
{
if ($enable) {
$this->flags[$flag]=true;
} else {
unset($this->flags[$flag]);
}
}
protected function resetFlags()
{
$this->flags=array();
}
protected function addWhere($andor, $conditions)
{
$this->addClauseCondWithBind(‘where’, $andor, $conditions);
return $this;
}
protected function addClauseCondWithBind($clause, $andor, $conditions)
{
$cond=array_shift($conditions);
$cond=$this->quoteNamesIn($cond);
$bind=& $this->