zend framework DB封装类
<?php
abstract class Base_DataBase_Pdo_Abstract{
const CASE_FOLDING = 'caseFolding';
const AUTO_QUOTE_IDENTIFIERS = 'autoQuoteIdentifiers';
protected $_config = array();
protected $_connection = null;
protected $_caseFolding = PDO::CASE_NATURAL;
protected $_autoQuoteIdentifiers = true;
protected $_fetchMode = PDO::FETCH_ASSOC;
protected $_transactionLevel = 0;
protected $_curdb = null;
protected function __construct($connection = 'default'){
global $CONFIG;
$this->_curdb = $connection;
$options = array(
self::CASE_FOLDING => $this->_caseFolding,
self::AUTO_QUOTE_IDENTIFIERS => $this->_autoQuoteIdentifiers
);
$driverOptions = array();
if (array_key_exists('options', $CONFIG->database['database'])) {
foreach ((array) $CONFIG->database['database']['options'] as $key => $value) {
$options[$key] = $value;
}
}
if (array_key_exists('driver_options', $CONFIG->database['database'])) {
if (!empty($CONFIG->database['database']['driver_options'])) {
foreach ((array) $CONFIG->database['database']['driver_options'] as $key => $value) {
$driverOptions[$key] = $value;
}
}
}
$this->_config['options'] = $options;
$this->_config['driver_options'] = $driverOptions;
if (array_key_exists(self::CASE_FOLDING, $options)) {
$case = (int) $options[self::CASE_FOLDING];
switch ($case) {
case PDO::CASE_LOWER:
case PDO::CASE_UPPER:
case PDO::CASE_NATURAL:
$this->_caseFolding = $case;
break;
default:
throw new DBException('Case must be one of the following constants: '
. 'PDO::CASE_NATURAL, PDO::CASE_LOWER, PDO::CASE_UPPER');
}
}
if (array_key_exists(self::AUTO_QUOTE_IDENTIFIERS, $options)) {
$this->_autoQuoteIdentifiers = (bool) $options[self::AUTO_QUOTE_IDENTIFIERS];
}
}
protected function setdatabase($config){
if(!is_array($config)){
throw new DBException('DB exception');
}
$this->_checkRequiredOptions($config);
if (!isset($config['charset'])) {
$config['charset'] = null;
}
$this->_config = array_merge($this->_config, $config);
//设置字符集
$initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
$this->_config['driver_options'][1002] = $initCommand; // 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
}
protected function _checkRequiredOptions($config){
if (! array_key_exists('dbname', $config)) {
throw new DBException("Configuration array must have a key for 'dbname' that names the database instance");
}
if (! array_key_exists('password', $config)) {
throw new DBException("Configuration array must have a key for 'password' that names the database instance");
}
if (! array_key_exists('password', $config)) {
throw new DBException("Configuration array must have a key for 'password' that names the database instance");
}
}
protected function _dsn()
{
$dsn = $this->_config;
unset($dsn['username']);
unset($dsn['password']);
unset($dsn['options']);
unset($dsn['charset']);
unset($dsn['persistent']);
unset($dsn['driver_options']);
foreach ($dsn as $key => $val) {
$dsn[$key] = "$key=$val";
}
$c=$this->_pdoType . ':' . implode(';', $dsn);
return $this->_pdoType . ':' . implode(';', $dsn);
}
protected function _connect(){
global $CONFIG;
if(isset($this->_connection)){
return;
}
$this->setdatabase($CONFIG->database[$this->_curdb]);
$dsn = $this->_dsn();
if(!extension_loaded('pdo')){
new DBException('The PDO extension is required for this adapter but the extension is not loaded');
}
//$drivers = PDO::getAvailableDrivers(); $dricers 共5个 sqlite,mysql,pgsql,sqlite2,oci
if (!in_array($this->_pdoType, PDO::getAvailableDrivers())) {
throw new DBException('The ' . $this->_pdoType . ' driver is not currently installed');
}
if (isset($this->_config['persistent']) && ($this->_config['persistent'] == true)) {
$this->_config['driver_options'][PDO::ATTR_PERSISTENT] = true;
}
if (isset($this->_config['timeout']) ) {
$this->_config['driver_options'][PDO::ATTR_TIMEOUT] = $this->_config['timeout'];
}
try {
$this->_connection = new PDO(
$dsn,
$this->_config['username'],
$this->_config['password'],
$this->_config['driver_options']
);
$this->_connection->setAttribute(PDO::ATTR_CASE, $this->_caseFolding);
$this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
throw new DBException($e->getMessage());
}
}
protected function _beginTransaction()
{
$this->_connect();
$this->_connection->beginTransaction();
}
protected function _commit()
{
$this->_connect();
$this->_connection->commit();
}
protected function _rollBack() {
$this->_connect();
$this->_connection->rollBack();
}
public function getConnection()
{
$this->_connect();
return $this->_connection;
}
public function getConfig()
{
return $this->_config;
}
public function beginTransaction()
{
if ($this->_transactionLevel===0) {
$this->_connect();
$this->_beginTransaction();
}
$this->_transactionLevel++;
return $this;
}
public function commit()
{
if ($this->_transactionLevel===1) {
$this->_connect();
$this->_commit();
}
$this->_transactionLevel--;
return $this;
}
public function rollBack()
{
if ($this->_transactionLevel===1) {
$this->_connect();
$this->_rollBack();
}
$this->_transactionLevel--;
return $this;
}
public function prepare($sql)
{
$this->_connect();
$stmt = $this->_connection->prepare($sql);
$stmt->setFetchMode($this->_fetchMode);
return $stmt;
}
public function query($sql, $bind = array()){
if (!is_array($bind)) {
$bind = array($bind);
}
$this->_connect();
$stmt = $this->prepare($sql);
$stmt->execute($bind);
$stmt->setFetchMode($this->_fetchMode);
return $stmt;
}
//如果插入的记录中有一个或多个是时间的字段,可以将列名的小写放进数组做为第三个参数
//如果有一个时间字段,且名字是daytime可以省去第三个参数。
public function insert($table, array $bind,$datecol = array('daytime'))
{
$cols = array();
$vals = array();
foreach ($bind as $col => $val) {
$cols[] = $this->_quoteIdentifier($col, true);
/* if(in_array($tempcol, array('create_time','update_time','check_time'))){
$vals[] = time();
unset($bind[$col]);
}elseif(in_array($tempcol, array('start_time','end_time'))){
$vals[] = trim($val);
}else{
$vals[] = '?';
}*/
$vals[] = '?';
//$bind[$col] = addcslashes($val, "\n\r\\'\"\032");
}
$sql = "INSERT INTO "
. $this->_quoteIdentifier($table, true)
. ' (' . implode(', ', $cols) . ') '
. 'VALUES (' . implode(', ', $vals) . ')';
$stmt = $this->query($sql, array_values($bind));
$result = $stmt->rowCount();
return $result;
}
public function update($table, array $bind, $where = '')
{
$set = array();
$i = 0;
foreach ($bind as $col => $val) {
$set[] = $this->_quoteIdentifier($col, true) . ' = ?' ;
}
$sql = "UPDATE "
. $this->_quoteIdentifier($table, true)
. ' SET ' . implode(', ', $set)
. (($where) ? " WHERE $where" : '');
$stmt = $this->query($sql, array_values($bind));
$result = $stmt->rowCount();
return $result;
}
public function delete($table, $where = '')
{
// $where = $this->_whereExpr($where);
$sql = "DELETE FROM "
. $this->_quoteIdentifier($table, true)
. (($where) ? " WHERE $where" : '');
$stmt = $this->query($sql);
$result = $stmt->rowCount();
return $result;
}
public function fetchAll($sql, $bind = array(), $fetchMode = null)
{
if ($fetchMode === null) {
$fetchMode = $this->_fetchMode;
}
$stmt = $this->query($sql, $bind);
$result = $stmt->fetchAll($fetchMode);
return $result;
}
public function fetchAssoc($sql, $bind = array())
{
$stmt = $this->query($sql, $bind);
$data = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$tmp = array_values(array_slice($row, 0, 1));
$data[$tmp[0]] = $row;
}
return $data;
}
public function fetchOne($sql, $bind = array())
{
$stmt = $this->query($sql, $bind);
$result = $stmt->fetchColumn(0);
return $result;
}
public function fetchPairs($sql, $bind = array())
{
$stmt = $this->query($sql, $bind);
$data = array();
while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {
$data[$row[0]] = $row[1];
}
return $data;
}
public function fetchCol($sql, $bind = array())
{
$stmt = $this->query($sql, $bind);
$result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
return $result;
}
public function fetchRow($sql, $bind = array(), $fetchMode = null)
{
if ($fetchMode === null) {
$fetchMode = $this->_fetchMode;
}
$stmt = $this->query($sql, $bind);
$result = $stmt->fetch($fetchMode);
return $result;
}
public function exec($sql)
{
try {
$affected = $this->getConnection()->exec($sql);
if ($affected === false) {
$errorInfo = $this->getConnection()->errorInfo();
throw new DBException($errorInfo[2]);
}
return $affected;
} catch (PDOException $e) {
throw new DBException($e->getMessage());
}
}
protected function _quoteIdentifier($value, $auto=false)
{
if ($auto === false || $this->_autoQuoteIdentifiers === true) {
$q = $this->getQuoteIdentifierSymbol();
return ($q . str_replace("$q", "$q$q", $value) . $q);
}
return $value;
}
public function getQuoteIdentifierSymbol()
{
return '"';
}
public function foldCase($key)
{
switch ($this->_caseFolding) {
case PDO::CASE_LOWER:
$value = strtolower((string) $key);
break;
case PDO::CASE_UPPER:
$value = strtoupper((string) $key);
break;
case PDO::CASE_NATURAL:
default:
$value = (string) $key;
}
return $value;
}
public function setFetchMode($mode)
{
if (!extension_loaded('pdo')) {
throw new DBException('The PDO extension is required for this adapter but the extension is not loaded');
}
switch ($mode) {
case PDO::FETCH_LAZY:
case PDO::FETCH_ASSOC:
case PDO::FETCH_NUM:
case PDO::FETCH_BOTH:
case PDO::FETCH_NAMED:
case PDO::FETCH_OBJ:
$this->_fetchMode = $mode;
break;
default:
throw new DBException("Invalid fetch mode '$mode' specified");
break;
}
}
public function isConnected()
{
return ((bool) ($this->_connection instanceof PDO));
}
public function lastInsertId($tableName = null, $primaryKey = null)
{
$this->_connect();
return $this->_connection->lastInsertId();
}
public function closeConnection()
{
$this->_connection = null;
$this->_dbobject['slave'] = null;
$this->_dbobject['master'] = null;
}
protected function _quote($value)
{
if (is_int($value) || is_float($value)) {
return $value;
}
$this->_connect();
return $this->_connection->quote($value);
}
public function getServerVersion()
{
$this->_connect();
try {
$version = $this->_connection->getAttribute(PDO::ATTR_SERVER_VERSION);
} catch (PDOException $e) {
return null;
}
$matches = null;
if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) {
return $matches[1];
} else {
return null;
}
}
public function getEdt(){
return $this->ismaster.'-'.$this->_curtarget;
}
abstract public function listTables();
abstract public function describeTable($tableName, $schemaName = null);
abstract public function limit($sql, $count, $offset = 0);
}