namespace iphp\core;useiphp\App;/**
* 数据库操作基类 基于pdo
* @author xuen
* 支持链式操作，支持参数绑定
* 说明1 只有在绑定了参数后，pdo才会自动处理单引号。
* 说明2 关闭连接时，要设置pdo null
* 说明3 在脚本处理完后，最好手动关闭连接。不然mysql并不一定会关闭连接。
*@example
* $res=$db->findAll(array(
‘field’=>’ID,albumName,albumImage,
mainActor,directors,tags,info,area,
keywords,wflag,year,mod_version,totalDuration’,
‘where’=>$where,
‘order’=>’flag desc,hit_count desc’,
‘limit’=>”{$limit[‘offset’]},{$limit[‘pagesize’]}”
));
$res=$db->field(‘ID,stitle,jisu,liuchang,gaoqing,chaoqing,
totalDuration,bTime,eTime’)
->where($where)
->limit(“{$limit[‘offset’]},{$limit[‘pagesize’]}”)
->findAll();*/
final classMyDb
{private $pdo;public $tableName;//表名
private $sql;//当前执行的SQL语句
public $error=”;//当前执行sql的错误消息
public $prefix=”;//前缀
public $charset=’utf8′;public $sqlQuery=array(‘field’=>’*’,
‘where’=>’1’,
‘join’=>”,
‘group’=>”,
‘having’=>”,
‘order’=>”,
‘limit’=>”,
‘union’=>”,
‘params’=>array()
);public $fields=array();//得到当前表所有的字段名称
private static $_instance=array();protected $dbName;//用户名
protected $username;private $k;//密码
protected $password;//主机名
protected $host;//端口号
protected $port;//一个时间戳，表示当前链接在什么时候过期，过期后，将重建一个对象。
protected $expireTime;//PDO链接属性数组
protected $attr=array(//这个超时参数，实际上mysql服务器上的配置为准的。这里用于什么时候重建对象
//说明如是设置了这个参数，如果不显式的将pdo设为null，可能造成连接资源在mysql上不被释放。
\PDO::ATTR_TIMEOUT=>30,\PDO::ATTR_ERRMODE=>\PDO::ERRMODE_SILENT,\PDO::ATTR_ORACLE_NULLS=>\PDO::NULL_NATURAL,
//如果设置了这个参数为true,inndob，需要手动commit
//\PDO::ATTR_AUTOCOMMIT=>false,
\PDO::ATTR_DEFAULT_FETCH_MODE=>\PDO::FETCH_ASSOC,\PDO::ATTR_PERSISTENT=>FALSE);/**
* 构造方法
* @param array $config 配置文件
* @param array $attr 数组选项*/
private function __construct($config,$attr)
{//索引数组合并不能使用merge
foreach ($this->attr as $key=>$row)
{if(isset($attr[$key]))$this->attr[$key]=$attr[$key];
}$this->pdo=new \PDO($config[‘dsn’], $config[‘username’],
$config[‘password’],$this->attr);if(isset($config[‘prefix’]) && $config[‘prefix’]!=”)$this->prefix=$config[‘prefix’];if(isset($config[‘charset’]) && $config[‘charset’]!=”)$this->charset=$config[‘charset’];$this->pdo->exec(“set names {$this->charset}”);//保存当前数据库名称，主机，端口。
preg_match(‘/dbname=(\w+)/’, $config[‘dsn’],$ma);preg_match(‘/host=(.*?);/’, $config[‘dsn’],$ma1);preg_match(‘/port=(\w+)/’, $config[‘dsn’],$ma2);$this->dbName=$ma[1];$this->host=$ma1[1];$this->port=$ma2[1]?$ma2[1]:3306;$this->username=$config[‘username’];$this->password=$config[‘password’];//设置链接过期时间
$timeout=$this->attr[\PDO::ATTR_TIMEOUT];$this->expireTime=time()+$timeout;
}private function__clone(){}/**
* @param $config
* @param array $attr
* @return \iphp\core\MyDb*/
static public function getInstance($config,$attr=array())
{if(!is_array($config))$config=App::getApp()->getConfig($config);$k=md5(implode(”, $config));//如果连接没有创建，或者连接已经失效
if( !(static::$_instance[$k] instanceof self))
{static::$_instance[$k]=new self($config,$attr);static::$_instance[$k]->k=$k;
}//如果连接超时。
elseif(time()>static::$_instance[$k]->expireTime)
{static::$_instance[$k]->close();static::$_instance[$k]=new self($config,$attr);static::$_instance[$k]->k=$k;
}return static::$_instance[$k];
}/**
*
* @param unknown_type $tableName
* @return $this*/
public function tableName($tableName)
{$this->tableName=$this->prefix.$tableName;//$this->setField();
return $this;
}/**
* @return \PDO*/
public functiongetPdo()
{return $this->pdo;
}/**
* 得到当前sql语句，并完成参数替换
* @return string*/
public functiongetSql()
{return $this->sql;
}/**
* @param string $sql
* @return bool*/
public function findAll($sql=”)
{$stmt=$this->query($sql);if(!$stmt)return false;return $stmt->fetchAll();
}/**
* @param string $sql
* @return bool*/
public function findOne($sql=”)
{$this->sqlQuery[‘limit’]=1;$stmt=$this->query($sql);if($stmt===false)return false;return $stmt->fetch();
}/**
* 根据主键查找记录
* @param mixed $ids
* @return 返回一维或二维数组*/
public function find($ids)
{$num=count(explode(‘,’, $ids));$this->setField();if($num==1)
{$res= $this->where(“{$this->fields[$this->tableName][‘pk’]}='{$ids}'”)->findOne();
}else{//如果是字符串主键，要加引号
$tmp=explode(‘,’, $ids);$tmp=array_map(function($item){return “‘”.$item.”‘”;
}, $tmp);$ids=implode(‘,’, $tmp);$res= $this->where(“{$this->fields[$this->tableName][‘pk’]} in ({$ids})”)->findAll();
}return $res;
}/**
* 插入数据的方法，自动完成参数绑定
* @param array $data 一维数组 array(Field=>value)
* @return boolean | int*/
public function insert($data)
{$this->setField();$params=array();$field=array();$placeholder=array();foreach($data as $key=>$row)
{//删除非法字段信息
if(!in_array($key, $this->fields[$this->tableName]))continue;$params[‘:’.$key]=$row;$field[]=$key;$placeholder[]=’:’.$key;
}//插入当前记录
$sql=”insert into {$this->tableName} (“.implode(‘, ‘, $field).’) values (‘.
implode(‘, ‘, $placeholder).’)’;$this->sqlQuery[‘params’]=$params;$this->sql=$sql;return $this->exec($sql,$this->sqlQuery[‘params’]);
}/**
* 删除记录
* @param string $where where条件
* @param array $params 绑定参数
* @return bool*/
public function delete($where = ”,$params = array())
{if($where!=”)$this->sqlQuery[‘where’]=$where;if($params!=”)$this->sqlQuery[‘params’]=$params;$sql=”delete from {$this->tableName} where {$this->sqlQuery[‘where’]}”;$this->sql=$sql;return $this->exec($sql,$this->sqlQuery[‘params’]);
}/**
* 简化的delete()方法，基于主键的删除*/
public function del($ids)
{$this->setField();$tmp=explode(‘,’, $ids);$tmp=array_map(function($item){return “‘”.$item.”‘”;
}, $tmp);$ids=implode(‘,’, $tmp);$sql=”delete from {$this->tableName} where {$this->fields[$this->tableName][‘pk’]}”.
” in ($ids)”;$this->sql=$sql;return $this->exec($sql);
}/**
* 得到插入的最后ID号*/
public functionlastId()
{return $this->pdo->lastInsertId();
}/**
* 修改数据 update 支持参数绑定 只支持where参数
* @param array $data 要改变的列的值数组 array(列名=>值)
* @param string $where where条件
* @param array $params 绑定参数
* @return boolean ｜ int 受影响的行数*/
public function update($data,$where=”,$params= array())
{$this->setField();if(!is_array($data))return false;if($where!=”)$this->sqlQuery[‘where’]=$where;if($params!=”)$this->sqlQuery[‘params’]=$params;$updateField=array();foreach($data as $key=>$value)
{//不合法的字段不要
if(!in_array($key, $this->fields[$this->tableName]))continue;$updateField[]=”{$key}=:{$key}”;$this->sqlQuery[‘params’][“:{$key}”]=$value;
}$sql=”update {$this->tableName} set “.implode(‘,’, $updateField).” where {$this->sqlQuery[‘where’]}”;$this->sql=$sql;return $this->exec($sql,$this->sqlQuery[‘params’]);
}/**
* 得到数据表的所有字段信息*/
public functionsetField()
{if(is_array($this->fields[$this->tableName]))return;$sql=”desc {$this->tableName} “;$res=$this->findAll($sql);foreach ($res as $row)
{if($row[‘Key’]==’PRI’)$this->fields[$this->tableName][‘pk’]=$row[‘Field’];$this->fields[$this->tableName][]=$row[‘Field’];
}
}//得到当前操作表的字段信息
public functiongetField()
{if(!$this->fields[$this->tableName])$this->setField();return $this->fields[$this->tableName];
}//得到记录总数
public function count($sql=”)
{$this->sqlQuery[‘field’]=’count(*) as c’;$stmt=$this->query($sql);if(!$stmt)return false;$res=$stmt->fetch();//执行完之后要重置查询字段
return $res[‘c’];
}//得到sql执行错误
public functiongetError()
{return $this->error;
}public function setError($error)
{$this->error=$error;
}/**
* 扫行有结果集的查询，支持参数绑定
* 如果你需要遍历数据库，请使用query方法，然后foreach 返回的stmt对象便可。
* @param mixed $sql
* @return boolean|PDOStatement*/
public function query($sql=”)
{$sql=$this->joinSql($sql);$stmt=$this->pdo->prepare($sql);$errorInfo=$stmt->errorInfo();$stmt->setFetchMode(\PDO::FETCH_ASSOC);$stmt->execute($this->sqlQuery[‘params’]);//清除sql条件值，desc类部执行的sql语句，不用清楚缓存
if(strpos($sql,’desc’)!==0)$this->clearSqlQuery();$errorInfo=$stmt->errorInfo();if($errorInfo[0]!=’00000′)
{$this->setError($errorInfo[2]);return false;
}return $stmt;
}/**
* 执行没有结果集的查询,支持参数绑定
* @param string $sql
* @param array $params
* @return 返回受影响行数或false*/
public function exec($sql,$params = array())
{$stmt=$this->pdo->prepare($sql);if($params!=”)$this->sqlQuery[‘params’]=$params;$stmt->execute($this->sqlQuery[‘params’]);$this->clearSqlQuery();$errorInfo=$stmt->errorInfo();if($errorInfo[0]!=’00000′)
{$this->setError($errorInfo[2]);return false;
}return $stmt->rowCount();
}//设定绑定参数
public function params($params)
{$this->sqlQuery[‘params’]=empty($params)?”:$params;return $this;
}/**
* 自动绑定参数
* @param $params
* @return $this*/
public function autoParams($params)
{$this->setField();foreach ($params as $key => $row) {if(in_array($key, $this->fields[$this->tableName])) {$this->sqlQuery[‘params’][“:{$key}”] = $row;
}
}return $this;
}/**
* 组合sql语句
* @param mixed $sql
* @return 返回组合的sql语句*/
public function joinSql($sql)
{if(is_string($sql) && $sql!=”)
{$this->sql=$sql;return $sql;
}elseif(is_array($sql) && $sql!=”)
{foreach ($sql as $key=>$row)
{if(!array_key_exists($key, $this->sqlQuery))continue;$this->sqlQuery[$key]=$row;
}
}else{}$this->sql=”select {$this->sqlQuery[‘field’]} from {$this->tableName}\n”;if($this->sqlQuery[‘join’]!=”)$this->sql.=”{$this->sqlQuery[‘join’]} “;$this->sql.=”where {$this->sqlQuery[‘where’]}\n”;if($this->sqlQuery[‘group’]!=”)$this->sql.=”group by {$this->sqlQuery[‘group’]}\n”;if($this->sqlQuery[‘having’]!=”)$this->sql.=”having {$this->sqlQuery[‘having’]}\n”;if($this->sqlQuery[‘order’]!=”)$this->sql.=”order by {$this->sqlQuery[‘order’]}\n”;if($this->sqlQuery[‘limit’]!=”)$this->sql.=”limit {$this->sqlQuery[‘limit’]}\n”;if($this->sqlQuery[‘union’]!=”)$this->sql.=”union {$this->sqlQuery[‘union’]}\n”;return $this->sql;
}//设定字段的方法
public function field($field)
{$this->sqlQuery[‘field’]=empty($field)?’*’:$field;return $this;
}/**
*
* @param unknown_type $where
* @return \iphp\core\MyDb*/
public function where($where)
{$this->sqlQuery[‘where’]=empty($where)?’1′:$where;return $this;
}/**
* @param $tableName
* @param $condition
* @return $this*/
public function join($tableName,$condition)
{$this->sqlQuery[‘join’].=”join {$tableName} on {$condition}\n”;return $this;
}/**
* @param $tableName
* @param $condition
* @return $this*/
public function leftjoin($tableName,$condition)
{$this->sqlQuery[‘join’].=”left join {$tableName} on {$condition}\n”;return $this;
}/**
* @param $tableName
* @param $condition
* @return $this*/
public function rightjoin($tableName,$condition)
{$this->sqlQuery[‘join’].=”right join {$tableName} on {$condition}\n”;return $this;
}/**
* @param $group
* @return $this*/
public function group($group)
{$this->sqlQuery[‘group’]=empty($group)?”:$group;return $this;
}/**
* @param $having
* @return $this*/
public function having($having)
{$this->sqlQuery[‘having’]=empty($having)?”:$having;return $this;
}/**
* @param $order
* @return $this*/
public function order($order)
{$this->sqlQuery[‘order’]=empty($order)?”:$order;return $this;
}/**
* @param $limit
* @return $this*/
public function limit($limit)
{$this->sqlQuery[‘limit’]=empty($limit)?”:$limit;return $this;
}/**
* @param $union
* @return $this*/
public function union($union)
{$this->sqlQuery[‘union’]=empty($union)?”:$union;return $this;
}/**
* 清除sql缓存*/
public functionclearSqlQuery()
{//清除缓存前，先保存当前sql语句。
if(!empty($this->sqlQuery[‘params’]))
{foreach ($this->sqlQuery[‘params’] as $key=>$param)$this->sql=str_replace($key, ‘”‘.$param.'”‘, $this->sql);
}$this->sql=nl2br($this->sql);foreach ($this->sqlQuery as $key=>$row)
{if($key==’where’)$this->sqlQuery[$key]=’1′;elseif ($key==’field’)$this->sqlQuery[$key]=’*’;elseif ($key==’params’)$this->sqlQuery[$key]=array();else
$this->sqlQuery[$key]=”;
}
}//再执行findone findall方法之前，得到当前要执行的sql语句,
public functiongetSqlCache()
{$sql=$this->joinSql(”);if(!empty($this->sqlQuery[‘params’]))
{foreach ($this->sqlQuery[‘params’] as $key=>$param)$sql=str_replace($key, ‘”‘.$param.'”‘, $sql);
}return $sql;
}/**
* 得到当前数据库名称*/
public functiongetDbName()
{return $this->dbName;
}/**
* 得到用户名*/
public functiongetUser()
{return $this->username;
}/**
* 得到密码*/
public functiongetPass()
{return $this->password;
}public functiongetHost()
{return $this->host;
}public functiongetPort()
{return $this->port;
}/**
* 得到连接相关的详细信息。*/
public functiongetConnInfo()
{return array(‘host’=>$this->host,
‘port’=>$this->port,
‘username’=>$this->username,
‘password’=>$this->password,
‘dbname’=>$this->dbName,);
}/**
* 开启事务，并设置错误模式为异常
* 使用try cacth 来回滚或提交
* beginTransaction()方法将会关闭自动提交(autocommit)模式，
* 直到事务提交或者回滚以后才能恢复为pdo设置的模式*/
public functionbeginTransaction()
{$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);return $this->pdo->beginTransaction();
}/**
* 提交
* @return bool*/
public functioncommit()
{return $this->pdo->commit();
}/**
* 回滚事务
* @return bool*/
public functionrollBack()
{return $this->pdo->rollBack();
}/**
* 关闭连接*/
public functionclose()
{$this->pdo=null;
}/**
* 关闭所有连接*/
public static functioncloseAll()
{foreach(static::$_instance as $o)
{if($oinstanceof self)$o->close();
}
}/**
* 得到当前表的下一次自增长ID*/
public function getNextAutoIncrement($tableName)
{$sql=”show table status where name ='{$tableName}'”;$res=$this->findOne($sql);return $res[‘Auto_increment’];
}/**
* 为一个表增加一个TIMESTAMP字段
* @param $tableName 表名
* @param $name 字段名
* @return bool|int*/
public function addTIMESTAMP($tableName,$name=’utime’)
{$addSql=”alter table {$tableName} add {$name} TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;”;$addSql.=”ALTER TABLE {$tableName} ADD index {$name}($name)”;return $this->exec($addSql);
}
}