分类：
2009-12-30 18:53:59
/**
* 数据访问层，仅处理MYSQL
* 包括
* by:李勇
* at:2009-01-19
*/
/**
* 指定库中的指定表的访问类
*
* 示例：
* $result=$table->select(array('id','name','birthday'),array('sex'=>1),null,null,'birthday',array(20,500))
* $table->delete(array('sex'=>0))
* $table->update(array('name'=>'tom jerry','weight'=>120),array('id'=>23149))
* $table->insert(array('id'=>uniqid(),'name'=>'mouse cat','height'=>'24cm'))
*/
include_once(rootpath."inc/exception.class.php"); //异常类
final class DalTable{
private $pdo; //数据库连接
private $tableName; //表名
private $meta; //元信息
private $primaryKey;//主键
/**
* 构造方法
*
* @param PDO $pdo
* @param string $tableName
*/
public function __construct(PDO $pdo,$tableName){
if(!is_string($tableName)){
throw new DalException(DalException::DE_TABLE_CONSTRUCTOR_TABLE_WRONG ,$tableName);
}
if(!$tableName) {
throw new DalException(DalException::DE_TABLE_CONSTRUCTOR_TABLE);
}
if(!$pdo){
throw new DalException(DalException::DE_TABLE_CONSTRUCTOR_PDO);
}
$this->pdo = $pdo;
$this->tableName = $tableName;
$this->meta = false;
$this->primaryKey = false;
return $this;
}
/**
* 获取本表的表结构（元数据），失败时返回0
*
* @return 0/array:<列名>=>array(列信息)
*/
private function getMeta(){
if($this->meta!==false)return $this->meta;
$sql = "DESCRIBE ".$this->tableName;
$table = $this->query($sql);
if(!$table or !count($table)){
$this->meta = 0;
throw new DalException(DalException::DE_TABLE_META_NULL,$this->tableName);
}else{
$meta = array();
foreach ($table as $col) {
$meta[$col['Field']] = $col;
}
$this->meta = $meta;
}
return $this->meta;
}
/**
* 获取本表的主键字段，无主键或组合主键时返回0
*
* @return 0/主键字段名
*/
private function getPrimaryKey(){
if($this->primaryKey!==false)return $this->primaryKey;
$sql = "SHOW INDEX FROM ".$this->tableName;
$table = $this->query($sql);
if(!$table or !count($table)){
$this->primaryKey = 0;
}else{
$pkvs = array();
foreach ($table as $row) {
if($row['Key_name'] == 'PRIMARY')$pkvs[] = $row['Column_name'];
}
if(count($pkvs)!=1){
$this->primaryKey = 0;
}else{
$this->primaryKey = $pkvs[0];
}
}
return $this->primaryKey;
}
/**
* 执行查询语句，并根据结果数量分别处理
*
* @param bool $distinct 是否消重
* @param mixed $fields 请参考createFields
* @param mixed $where 请参考createWhere
* @param mixed $groupby 请参考createGroupby
* @param mixed $having 请参考createHaving
* @param mixed $orderby 请参考createOrderby
* @param mixed $limit 请参考createLimit
* @return mixed 单值/一维无键数组/一维有键数组/二维数组(一维无键，二维有键)
*/
private function _select($distinct,$fields = null,$where = null,$groupby = null,$having = null,$orderby = null,$limit = null){
if(is_object($fields))$fields = get_object_vars($fields);
if(!$where and !$groupby and !$having and !$orderby and !$limit and is_array($fields) and isset($fields['fields'])){
if(isset($fields['where'])) $where = $fields['where'];
if(isset($fields['groupby'])) $groupby = $fields['groupby'];
if(isset($fields['having'])) $having = $fields['having'];
if(isset($fields['orderby'])) $orderby = $fields['orderby'];
if(isset($fields['limit'])) $limit = $fields['limit'];
$fields = $fields['fields'];
return $this->_select($distinct,$fields,$where,$groupby,$having,$orderby,$limit);
}
$fields = $this->createFields($fields);
$where = $this->createWhere($where);
$groupby = $this->createGroupby($groupby);
$having = $this->createHaving($having);
$orderby = $this->createOrderby($orderby);
$limit = $this->createLimit($limit);
$sql = 'SELECT ';
if($distinct)$sql .= 'DISTINCT ';
$sql .= $fields;
$sql .= " FROM ".$this->tableName." ";
$sql .= $where.$groupby.$having.$orderby.$limit;
$set = $this->query($sql);
$rows = count($set);
if($rows){
$cols = count($set[0]);
} else {
$cols = 0;
}
if($rows == 1 and $cols == 1){
$col = array();
foreach ($set[0] as $cell) {
$col[] = $cell;
}
return $col[0];
}
if($rows == 1){
return $set[0];
}
if($cols == 1){
$col = array();
foreach ($set as $row) {
$col[] = $row[0];
}
return $col;
}
return $set;
}
/**
* 返回查询结果
* 请参考_select
*/
public function select($fields = null,$where = null,$groupby = null,$having = null,$orderby = null,$limit = null){
return $this->_select(false,$fields,$where,$groupby,$having,$orderby,$limit);
}
/**
* 返回消重后的查询结果
* 请参考_select
*/
public function distinct($fields = null,$where = null,$groupby = null,$having = null,$orderby = null,$limit = null){
return $this->_select(true,$fields,$where,$groupby,$having,$orderby,$limit);
}
/**
* 插入一条数据
*
* @param array[1] $row
* <列名>=><值>
* @return int
*/
public function insert($row){
if(!$row){
throw new DalException(DalException::DE_TABLE_INSERT_NULL,array($this->tableName,$row));
}
if(is_object($row))$row = get_object_vars($row);
if(!is_array($row)){
throw new DalException(DalException::DE_TABLE_INSERT_WRONG,array($this->tableName,$row));
}
$meta = $this->getMeta();
$fields = '';
$values = '';
foreach ($row as $name=>$value){
if(!is_string($name))continue;
if(!isset($meta[$name]))continue;
if($fields)$fields .= ' , ';
$fields .= $name;
if($values)$value .= ' , ';
$values .= "'".mysql_escape_string($value)."'";
}
if(!$fields or !$values)return false;
$sql = "INSERT INTO ".$this->tableName.'('.$fields.') VALUES('.$values.')';
$count = $this->execute($sql);
if(!$count) {
throw new DalException(DalException::DE_TABLE_INSERT_FAILURE,array($this->tableName,$row,$sql));
}
return $this->pdo->lastInsertId();
}
/**
* 删除表中的部分数据
*
* @param mixed $where
* 请参考createWhere
* @return int
*/
public function delete($where){
$condition = trim($this->createWhere($where));
if(!$condition){
throw new DalException(DalException::DE_TABLE_DELETE_NO_WHERE,array($this->tableName,$where));
}
$sql = "DELETE FROM ".$this->tableName." ".$condition;
return $this->execute($sql);
}
/**
* 统计满足条件的记录数
*
* @param mixed $where
* 请参考createWhere
* @return int
*/
public function count($where=null){
$condition = trim($this->createWhere($where));
$sql = "SELECT COUNT(*) AS cnt FROM ".$this->tableName;
if($condition) $sql .= " ".$condition;
return intval($this->_select($sql));
}
/**
* 删除表中全部数据
*
* @return int
*/
public function deleteAll(){
$sql = "DELETE FROM ".$this->tableName;
return $this->execute($sql);
}
/**
* 修改表中的部分数据
*
* @param array[1] $row
* <列名>=><值>
* @param mixed $where
* 请参考createWhere
* @return int
*/
public function update($row,$where){
if(!$row){
throw new DalException(DalException::DE_TABLE_UPDATE_NULL,array($this->tableName,$row));
}
if(is_object($row))$row = get_object_vars($row);
if(!is_array($row)){
throw new DalException(DalException::DE_TABLE_UPDATE_WRONG,array($this->tableName,$row));
}
$meta = $this->getMeta();
$set = '';
foreach ($row as $name=>$value){
if(!is_string($name))continue;
if(!isset($meta[$name]))continue;
if($set)$set .= ' , ';
$set = $name."='".mysql_escape_string($value)."' ";
}
if(!$set){
throw new DalException(DalException::DE_TABLE_UPDATE_INVALID,array($this->tableName,$row));
}
$condition = $this->createWhere($where);
if(!$condition){
throw new DalException(DalException::DE_TABLE_UPDATE_NO_WHERE,array($this->tableName,$where));
}
$sql = "UPDATE ".$this->tableName." SET ".$set." ".$condition;
return $this->execute($sql);
}
/**
* 生成字段列表
*
* @param mixed $fields
* null/''/0 所有字段
* string 直接使用
* object/array
* <列名|计算值>
* <列名|计算值>=><别名>
* @return string
*/
private function createFields($fields = null){
if(!$fields)return '*';
if(is_string($fields)) return trim($fields);
if(is_object($fields))$fields = get_object_vars($fields);
if(!is_array($fields)) {
throw new DalException(DalException::DE_TABLE_FIELD_WRONG,array($this->tableName,$fields));
}
$ret = '';
foreach($fields as $key=>$value){
if(is_int($key)){
if(!is_string($value))continue;
if($ret)$ret .= ' , ';
$ret .= $value;
continue;
}
if(!is_string($value))continue;
if($ret)$ret .= ' , ';
$ret .= $key.' AS '.$value;
}
return $ret;
}
/**
* 生成WHERE子句
*
* @param mixed $where
* 参考createCondition
* @return string
*/
private function createWhere($where){
$condition = $this->createCondition($where);
if(!$condition)return '';
$condition = trim($condition);
if(strpos(strtoupper($condition),'WHERE')===0)return $condition;
return 'WHERE '.$condition;
}
/**
* 生成HAVING子句
*
* @param mixed $having
* 参考createCondition
* @return string
*/
private function createHaving($having){
$condition = $this->createCondition($having);
if(!$condition)return '';
$condition = trim($condition);
if(strpos(strtoupper($condition),'HAVING')===0)return $condition;
return 'HAVING '.$condition;
}
/**
* 生成 GROUP BY 子句
*
* @param mixed $groupby
* 参考createSort
* @return string
*/
private function createGroupby($groupby){
$sort = $this->createSort($groupby);
if(!$sort)return '';
$sort = trim($sort);
if(strpos(strtoupper($sort),'GROUP')===0)return $sort;
return 'GROUP BY '.$sort;
}
/**
* 生成 ORDER BY 子句
*
* @param mixed $orderby
* 参考createSort
* @return string
*/
private function createOrderby($orderby = null){
$sort = $this->createSort($orderby);
if(!$sort)return '';
$sort = trim($sort);
if(strpos(strtoupper($sort),'ORDER')===0) return $sort;
return 'ORDER BY '.$sort;
}
/**
* 生成列排序子句，用于Order By与Group By中
*
* @param mixed $sort
* string 直接使用
* object/arrray
* <列名> ASC|asc|DESC|desc
* [<列名>|<列名>=>,...]
* @return string
*/
private function createSort($sort = null){
if(!$sort)return '';
if(is_string($sort)) return trim($sort);
if(is_object($sort))$sort = get_object_vars($sort);
if(!is_array($sort)){
throw new DalException(DalException::DE_TABLE_SORT_WRONG ,array($this->tableName,$sort));
}
if(count($sort) == 2 and (strtoupper($sort[1]) == 'ASC' or strtoupper($sort[1]) == 'DESC')) {
return $sort[0].' '.strtoupper($sort[1]);
}
$meta = $this->getMeta();
$ret = '';
foreach ($sort as $key=>$value) {
if(is_int($key)){
if(!isset($meta[$value]))continue;
if($ret)$ret .= ' , ';
$ret .= $value;
continue;
}
if(strtoupper($value) == 'ASC' or strtoupper($value) == 'DESC'){
if(!isset($meta[$key]))continue;
if($ret)$ret .= ' , ';
$ret .= $key.' '.strtoupper($value);
continue;
}
continue;
}
return $ret;
}
/**
* 生成分页子句
*
* @param mixed $limit
* string 直接使用，其中可包含LIMIT
* int 只限制行数，不限制偏移
* object/array
* [limit/LIMIT/Limit=><行数>,Offset/offset/OFFSET=><偏移>]
* [<行数>,<偏移>]
* [<行数>]
* @return string
*/
private function createLimit($limit = null){
if(!$limit)return '';
if(is_string($limit)){
$limit = trim($limit);
if(strpos(strtoupper($limit),'LIMIT') ===0) return strtoupper($limit);
return 'LIMIT '.$limit;
}
if(is_int($limit)) return 'LIMIT '.$limit;
if(is_object($limit))$limit = get_object_vars($limit);
if(!is_array($limit)){
throw new DalException(DalException::DE_TABLE_LIMIT_WRONG ,array($this->tableName,$limit));
}
$l = null;
$o = null;
if(isset($limit['limit'])) $l = $limit['limit'];
if(isset($limit['LIMIT'])) $l = $limit['LIMIT'];
if(isset($limit['offset'])) $o = $limit['offset'];
if(isset($limit['OFFSET'])) $o = $limit['OFFSET'];
if($l){
if($o){
return 'LIMIT '.$l.' OFFSET '.$o;
}
return 'LIMIT '.$l;
}
if(count($limit) == 2){
$l = $limit[1];
$o = $limit[0];
return 'LIMIT '.$l.' OFFSET '.$o;
}
if(count($limit) == 1) return 'LIMIT '.$limit[0];
throw new DalException(DalException::DE_TABLE_LIMIT_COUNT ,array($this->tableName,$limit));
}
/**
* 生成条件表达式，用于Where,Having子句中
*
* @param mixed $condition
* string 直接使用
* numeric 主键值
* object/array and组合
* PrimaryKey=>主键值
* 列名=>列值（string/int）
* @return string
*/
private function createCondition($condition = null){
if(is_null($condition)) return '';
if(is_string($condition)) return trim($condition);
if(is_numeric($condition)){
if($this->getPrimaryKey())
return $this->getPrimaryKey()."='".$condition."' ";
throw new DalException(DalException::DE_TABLE_CONDITION_PRIMARYKEY ,array($this->tableName,$condition));
}
if(is_object($condition))$condition = get_object_vars($condition);
if(!is_array($condition)){
throw new DalException(DalException::DE_TABLE_CONDITION_WRONG ,array($this->tableName,$condition));
}
if(isset($condition['PrimaryKey'])){
if($this->getPrimaryKey())
return $this->getPrimaryKey()."='".mysql_escape_string($condition['PrimaryKey'])."'";
throw new DalException(DalException::DE_TABLE_CONDITION_NO_PRIMARYKEY ,array($this->tableName,$condition));
}
$meta = $this->getMeta();
$ret = '';
foreach ($condition as $key=>$value) {
if(!is_int($key)){
if(!isset($meta[$key]))continue;
if(!is_int($value)&&!is_string($value))continue;
if(is_string($value))$value = trim($value);
if($ret)$ret .= ' and ';
$ret .= ' '.$key."='".mysql_escape_string($value)."' ";
continue;
}
}
return $ret;
}
/**
* 执行SQL语句，并返回影响的行数
*
* @param string $sql
* @return int
*/
public function execute($sql){
if(!is_string($sql)) {
throw new DalException(DalException::DE_TABLE_EXECUTE_STRING ,array($this->tableName,$sql));
}
return $this->pdo->exec(trim($sql));
}
/**
* 查询并返回结果集
*
* @param string $sql
* @return array[2]
*/
public function query($sql){
if(!is_string($sql)) {
throw new DalException(DalException::DE_TABLE_QUERY_STRING ,array($this->tableName,$sql));
}
$statement=$this->pdo->query(trim($sql));
return $statement->fetchAll();
}
}