超轻的 PHP 数据库工具包（2）

时间:2014-07-22 14:52来源: 作者: 点击: 次

分享到：

一个 SQL 构造器，用于构造 SQL 语句。配合 TableMetadata（表元信息）使用，简单的 SQL 基本能应付了。 br / 不过，这个组件依赖上次分享的 a href=http://www.oschina.net/code/snippet_74928_4038 rel=nof

一个 SQL 构造器，用于构造 SQL 语句。配合 TableMetadata（表元信息）使用，简单的 SQL 基本能应付了。
不过，这个组件依赖上次分享的 QuickQuery ……
这货其实是为一个 DataMapper 而设计的，但是 DataMapper 尚未写完，所以先把这个送出来了……的确，单独使用也可以，就是挺丑的。

// 在框架的前端控制器初始化时，注册数据表元信息
new TableMetadata('Tags', array(
	'id' => 'TagID',
	'word' => 'TagWord',
	'created' => 'CreatedDateTime'
));
new TableMetadata('Users', array(
	'id' => 'UserID',
	'name' => 'UserName',
	'password' => 'Password',
	'created' => 'CreatedDateTime'
));
new TableMetadata('Topics', array(
	'id' => 'TopicID',
	'title' => 'TopicTitle',
	'content' => 'TopicContent',
	'created' => 'CreatedDateTime',
	'creater' => 'CreaterUserID',
	'reply_to' => 'ReplyToTopicID'
));
new TableMetadata('Topics_Tags', array(
	'topic_id' => 'TopicID',
	'tag_id' => 'TagID'
));

// 使用，这里测试了 select，delete、update 神马的也同理
$query = new QuickQuery($conn);

$user = TableMetadata::getInstance('Users');
$topic = TableMetadata::getInstance('Topics');
$user->select('id', 'name', 'password', 'created')
     ->join(array($topic['title']), 'Topics', "${topic['creater']} = ${user['id']}")
     ->where("${user['id']} > :lt_id AND ${user['id']} < :st_id", array(':lt_id', ':st_id'))
     ->prepare($query)
     ->execute(array(':lt_id'=>5, ':st_id'=8));

// 上面也可以不执行 prepare，而执行 count，返回记录条数

foreach($query as $row) {
	var_dump($row);
}

2. [代码]Persistence/DbAccess/QueryBuilder.php 跳至 [1] [2] [3] [全屏预览]

<?php
namespace Persistence\DbAccess;
use Exception, InvalidArgumentException, BadMethodCallException;

/**
 * 查询构造器，以对象操作的方式构造查询 SQL
 * 
 * @version 0.1
 * @author tonyseek
 * @link http://stu.szu.edu.cn
 * @license   http://www.opensource.org/licenses/mit-license.html
 * @copyright StuCampus Development Team, Shenzhen University
 *
 */
class QueryBuilder
{
	const MODE_SELECT = 1;
	const MODE_INSERT = 2;
	const MODE_UPDATE = 3;
	const MODE_DELETE = 4;
	
	const JOIN_INNER = 'INNER';
	const JOIN_LEFT  = 'LEFT';
	const JOIN_RIGHT = 'RIGHT';
	const JOIN_FULL  = 'FULL';
	
	const ORDER_ASC  = 'ASC';
	const ORDER_DESC = 'DESC';
	
	// 可用操作模式
	static private $enableMode = array(
		self::MODE_DELETE, 
		self::MODE_INSERT, 
		self::MODE_SELECT, 
		self::MODE_UPDATE
	);
	
	private $mode = null; // 操作模式
	private $columns = array(); // 字段
	private $params = array(); // 查询参数
	private $table = ''; // 表
	
	private $where = array();
	private $order = array();
	private $limitSize   = null;
	private $limitOffset = null;
	private $join = array();
	private $hasUsedPerfix = false;
	
	/**
	 * 构造查询构造器
	 * 
	 * @param array  $columns 字段，格式为"字段key=>字段字符串形式"
	 * @param string $table   涉及的表
	 * @param int    $mode    操作模式（select、update...）
	 */
	public function __construct(array $columns, $table, $mode)
	{
		// 参数有效性检查
		if (!in_array($mode, self::$enableMode)) {
			throw new InvalidArgumentException('参数 $mode 无效');
		}
		
		// 添加引号
		self::quoteField($table); 
		array_walk($columns, array(get_class($this), 'quoteField')); // 添加引号
		
		$this->columns = $columns;
		$this->table = $table;
		$this->mode = $mode;
	}
	
	/**
	 * 根据字段 key，从构造器中删除备选字段
	 * 
	 * @param array $columns 字段 key 集合
	 */
	public function unsetColumns(array $columns)
	{
		foreach($columns as $unsetKey) {
			if (!isset($this->columns[$unsetKey])) {
				throw new InvalidArgumentException("将删除的备选字段[{$unsetKey}]不存在");
			}
			// 直接删除
			unset($this->columns[$unsetKey]);
		}
	}
	
	/**
	 * 批量调用 setParam
	 * 
	 * @param array $params
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function setParams(array $params)
	{
		$this->params = array_merge($this->params, $params);
		
		return $this;
	}
	
	/**
	 * 通过 WHERE 筛选
	 * 
	 * @param string $statement WHERE 子句
	 * @param array  $params    涉及的参数
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function where($statement, array $params = array())
	{
		$this->where[] = $statement;
		$this->setParams($params);
		
		return $this;
	}
	
	/**
	 * 通过 Order By 排序
	 * 
	 * @param string $column  依据字段
	 * @param string $orderBy 排序模式
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function orderBy($column, $orderBy)
	{
		self::quoteField($column);
		$this->order[] = "{$column} {$orderBy}";
		
		return $this;
	}
	
	/**
	 * 通过 Limit 限制结果片段
	 * 
	 * @param int $size   片段尺寸
	 * @param int $offset 片段开始点偏移
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function limit($size, $offset = 0)
	{
		$this->limitSize = $size;
		$this->limitOffset = $offset;
		
		return $this;
	}
	
	/**
	 * 通过 Join 连接其他表
	 * 
	 * @param array  $columns 得到的字段
	 * @param string $table   连接的右表
	 * @param string $on      连接点（条件）
	 * @param string $mode    连接模式（SQL 语法）
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function join(array $columns, $table, $on, $mode=self::JOIN_INNER)
	{
		if ($this->mode != self::MODE_SELECT) {
			throw new BadMethodCallException('只有 MODE_SELECT 下才能使用 JOIN');
		}
		
		// 给主表添加前缀防止重名
		$this->usePerfix();

		// 添加字段
		$perfix = $table;
		self::quoteField($table);
		foreach($columns as $key=>$field) {
			$key = "{$perfix}_{$key}"; // 给 key 添加前缀
			self::quoteField($field);
			$this->columns[$key] = "{$table}.{$field}";
		}

		$this->join[$perfix] = array(
			'on' => $on,
			'mode' => $mode,
			'table' => $table,
		);
		
		return $this;
	}
	
	/**
	 * 构造 QuickQuery 对象
	 * 
	 * @param QuickQuery $query
	 * @return \Persistence\DbAccess\QuickQuery
	 */
	public function prepare(QuickQuery $query)
	{
		$query->prepare($this->buildStatement())
		      ->setParamsCheck($this->params);
		return $query;
	}
	
	/**
	 * 统计查询结果数目
	 * 
	 * @param QuickQuery $query
	 * @param string     $column[default='*']
	 * @param array      $params[default=null]
	 * @throws \PDOException
	 * @return int
	 */
	public function count(QuickQuery $query, array $params=array(), $column='*')
	{
		if ($this->mode != self::MODE_SELECT) {
			throw new BadMethodCallException('只有 MODE_SELECT 可以执行 count');
		}
		
		$query = clone $query;
		$query->prepare($this->buildCount($column))
		      ->setParamsCheck($this->params)
		      ->execute($params);
		$count = $query->getStatement()->fetch();
		$count = $count['count'];
		return $count;
	}
	
	/**
	 * 构造 SQL 语句
	 * 
	 * @return string
	 */
	public function buildStatement()
	{
		switch($this->mode) {
			case self::MODE_SELECT:
				return $this->buildSelect();
			case self::MODE_INSERT:
				return $this->buildInsert();
			case self::MODE_UPDATE:
				return $this->buildUpdate();
			case self::MODE_DELETE:
				return $this->buildDelete();
		}
	}
	
	// 构造 Select 语句
	private function buildSelect()
	{
		$columns = implode(', ', $this->columns);
		
		$stmt = "SELECT {$columns} FROM {$this->table}";
		
		foreach($this->join as $join) {
			$stmt = "{$stmt} {$join['mode']} JOIN {$join['table']} ON {$join['on']}";
		}
		
		if (count($this->where)) {
			$where = implode(' AND ', $this->where);
			$stmt = "{$stmt} WHERE {$where}";
		}
		
		if ($this->limitSize) {
			$stmt = "{$stmt} LIMIT {$this->limitSize} OFFSET {$this->limitOffset}";
		}
		
		if (count($this->order)) {
			$order = implode(', ', $this->order);
			$stmt = "{$stmt} ORDER BY {$order}";
		}
		
		return $stmt;
	}
	
	// 构造 Insert 语句
	private function buildInsert()
	{
		$columns = implode(', ', $this->columns);
		
		// 构造 value 语句段
		$paramFormat = function($x){ return ":{$x}"; };
		$values = array_map($paramFormat, array_keys($this->columns));
		$this->setParams($values); // 设置参数表
		$values = implode(', ', $values);
		
		$stmt = "INSERT INTO {$this->table} ({$columns}) VALUES ($values)";
		
		return $stmt;
	}
	
	// 构造 Update 语句
	private function buildUpdate()
	{
		// 构造 Update 的 SET 语句
		$setters = array_map(function($key, $value){
			return "{$value}=:{$key}";
		}, array_keys($this->columns), $this->columns);
		$setters = implode(', ', $setters);
		
		// 设置参数表
		$paramFormat = function($x){ return ":{$x}"; };
		$this->setParams(array_map($paramFormat, array_keys($this->columns))); 
		
		// 如果使用了 order 或 limit 则改为嵌套子查询的语句
		if (count($this->order) || count($this->limitSize)) {
			return $this->buildUpdateByNest($setters);
		}
		
		$stmt = "UPDATE {$this->table} SET {$setters}";
		
		if (count($this->where)) {
			$where = implode(' AND ', $this->where);
			$stmt = "{$stmt} WHERE {$where}";
		} else {
			throw new Exception('不允许使用没有 Where 子句的 Update');
		}
		
		return $stmt;
	}
	
	// 通过嵌套子查询的 Update 语句
	private function buildUpdateByNest($setters)
	{
		$stmt = $this->buildSelect();
		$columns = implode(', ', $this->columns);
		
		$stmt = "UPDATE {$this->table} SET {$setters} WHERE ({$columns}) IN ({$stmt})";
		
		return $stmt;
	}
	
	// 构造 Delete 语句
	private function buildDelete()
	{
		if (!count($this->where)) {
			throw new Exception('不允许使用没有 Where 子句的 Delete');
		}
		
		$where = implode(' AND ', $this->where);
		$stmt = "DELETE FROM {$this->table} WHERE {$where}";
		
		return $stmt;
	}
	
	// 构造  Count 查询语句
	private function buildCount($column)
	{
		if ($column != '*') {
			self::quoteField($column);
		}
		$stmt = "SELECT count({$column}) as count FROM {$this->table}";
		
		foreach($this->join as $join) {
			$stmt = "{$stmt} {$join['mode']} JOIN {$join['table']} ON {$join['on']}";
		}
		
		if (count($this->where)) {
			$where = implode(' AND ', $this->where);
			$stmt = "{$stmt} WHERE {$where}";
		}
		
		return $stmt;
	}
	
	// 给表添加命名前缀
	private function usePerfix()
	{
		if (!$this->hasUsedPerfix) {
			$mainTable = $this->table;
			
			$this->columns = array_map(function($x) use($mainTable) {
				return "{$mainTable}.{$x}";
			}, $this->columns);
			
			$this->hasUsedPerfix = true;
		}
	}
	
	// 给字段添加引号
	static private function quoteField(&$field)
	{
		$field = '"' . $field . '"';
	}
}

3. [代码]Persistence/DbAccess/TableMetadata .php 跳至 [1] [2] [3] [全屏预览]

<?php
namespace Persistence\DbAccess;
use ArrayAccess, InvalidArgumentException, BadMethodCallException;

/**
 * 数据表元信息（MetaData）
 * 
 * @version 0.1
 * @author tonyseek
 * @link http://stu.szu.edu.cn
 * @license   http://www.opensource.org/licenses/mit-license.html
 * @copyright StuCampus Development Team, Shenzhen University
 */
class TableMetadata implements ArrayAccess
{
	static private $instances = array();
	
	private $columns    = array();
	private $tableName  = '';
	
	/**
	 * @param string $tableName 数据表的名称
	 * @param array  $metadata  元信息关联数组, 格式keyName=>codeName 
	 */
	public function __construct($tableName, array $metadata)
	{
		$this->tableName = $tableName;
		$this->columns = $metadata;
		
		self::$instances[$tableName] = $this;
	}
	
	/**
	 * 获取一个已经被实例化的元信息
	 * 
	 * @param string $tableName
	 * @return \Persistence\DbAccess\TableMetadata
	 */
	static public function getInstance($tableName)
	{
		if (!isset(self::$instances[$tableName])) {
			throw new InvalidArgumentException("元信息中不存在 {$tableName} 表");
		}
		
		return self::$instances[$tableName];
	}
	
	/**
	 * 获取字段的字符串 CodeName
	 * 
	 * @param string $keyName
	 * @param bool   $withTableName
	 * @param bool   $quote
	 * @return string
	 */
	public function getColumn($keyName, $withTableName=true, $quote=true)
	{
		if (!isset($this->columns[$keyName])) {
			throw new InvalidArgumentException("元信息中不存在 {$keyName} 字段");
		}
		
		$quote = $quote ? '"' : '';
		
		$codeName = "{$quote}{$this->columns[$keyName]}{$quote}";
		if ($withTableName) {
			$table = "{$quote}{$this->tableName}{$quote}";
			$codeName = "{$table}.{$codeName}";
		}
		
		return $codeName;
	}
	
	/**
	 * 批量获取字段的字符串 CodeName
	 * 
	 * @param array $keyNames
	 * @param bool  $withTableName
	 * @param bool  $quote
	 * @return array
	 */
	public function getColumns(array $keyNames, $withTableName=true, $quote=true)
	{
		$columns = array();
		foreach($keyNames as $keyName) {
			$columns[$keyName] = $this->getColumn($keyName, $withTableName, $quote);
		}
		
		return $columns;
	}
	
	/**
	 * 返回不含引号的数据表名称
	 * 
	 * @return string
	 */
	public function getTableName()
	{
		return $this->tableName;
	}
	
	/**
	 * @see ArrayAccess::offsetExists()
	 */
	public function offsetExists($offset)
	{
		return isset($this->columns[$offset]);
	}
	
	/**
	 * @see ArrayAccess::offsetGet()
	 */
	public function offsetGet($offset)
	{
		return sprintf('"%s"."%s"', $this->tableName, $this->columns[$offset]);
	}
	
	/**
	 * @see ArrayAccess::offsetSet()
	 * @throws BadMethodCallException
	 */
	public function offsetSet($offset, $value)
	{
		throw new BadMethodCallException('TableMetadata 是只读对象');
	}
	
	/**
	 * @see ArrayAccess::offsetUnset()
	 * @throws BadMethodCallException
	 */
	public function offsetUnset($offset)
	{
		throw new BadMethodCallException('TableMetadata 是只读对象');
	}
	
	/**
	 * 由元信息构造 select 查询
	 * 
	 * @param array args
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function select()
	{
		return $this->buildQuery(func_get_args(), QueryBuilder::MODE_SELECT);
	}
	
	/**
	 * 由元信息构造 update 查询
	 * 
	 * @param array args
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function update()
	{
		return $this->buildQuery(func_get_args(), QueryBuilder::MODE_UPDATE);
	}
	
	/**
	 * 由元信息构造 insert 查询
	 * 
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function insert()
	{
		return $this->buildQuery(array(), QueryBuilder::MODE_INSERT);
	}
	
	/**
	 * 由元信息构造 delete 查询
	 * 
	 * @return \Persistence\DbAccess\QueryBuilder
	 */
	public function delete()
	{
		return $this->buildQuery(array(), QueryBuilder::MODE_DELETE);
	}
	
	public function joinInto(QueryBuilder $builder, array $keys, $on, $mode=QueryBuilder::JOIN_INNER)
	{
		$columns = array();
		foreach($keys as $key) {
			$columns[$key] = $this->columns[$key];
		}
		
		$builder->join($columns, $this->tableName, $on, $mode);
	}
	
	public function __toString()
	{
		return '"' . $this->tableName . '"';
	}
	
	// 构造字段范围内的 QueryBuilder
	private function buildQuery(array $range, $mode)
	{
		$columns = count($range) ? array() : $this->columns;
		foreach($range as $keyName) {
			$columns[$keyName] = $this->columns[$keyName];
		}
		
		if ($mode != QueryBuilder::MODE_SELECT && isset($columns['id'])) {
			unset($columns['id']);
		}
		
		return new QueryBuilder($columns, $this->tableName, $mode);
	}
}