<?php
namespace iphp\core;
use iphp\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 class MyDb
{
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 function getPdo()
{
return $this->pdo;
}
/**
* 得到当前sql语句，并完成参数替换
* @return string
*/
public function getSql()
{
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 function lastId()
{
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 function setField()
{
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 function getField()
{
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 function getError()
{
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 function clearSqlQuery()
{
//清除缓存前，先保存当前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 function getSqlCache()
{
$sql=$this->joinSql('');
if(!empty($this->sqlQuery['params']))
{
foreach ($this->sqlQuery['params'] as $key=>$param)
$sql=str_replace($key, '"'.$param.'"', $sql);
}
return $sql;
}
/**
* 得到当前数据库名称
*/
public function getDbName()
{
return $this->dbName;
}
/**
* 得到用户名
*/
public function getUser()
{
return $this->username;
}
/**
* 得到密码
*/
public function getPass()
{
return $this->password;
}
public function getHost()
{
return $this->host;
}
public function getPort()
{
return $this->port;
}
/**
* 得到连接相关的详细信息。
*/
public function getConnInfo()
{
return array(
'host'=>$this->host,
'port'=>$this->port,
'username'=>$this->username,
'password'=>$this->password,
'dbname'=>$this->dbName,
);
}
/**
* 开启事务，并设置错误模式为异常
* 使用try cacth 来回滚或提交
* beginTransaction()方法将会关闭自动提交（autocommit）模式，
* 直到事务提交或者回滚以后才能恢复为pdo设置的模式
*/
public function beginTransaction()
{
$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
return $this->pdo->beginTransaction();
}
/**
* 提交
* @return bool
*/
public function commit()
{
return $this->pdo->commit();
}
/**
* 回滚事务
* @return bool
*/
public function rollBack()
{
return $this->pdo->rollBack();
}
/**
* 关闭连接
*/
public function close()
{
$this->pdo=null;
}
/**
* 关闭所有连接
*/
public static function closeAll()
{
foreach(static::$_instance as $o)
{
if($o instanceof 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);
}
}
