Skip to content

Instantly share code, notes, and snippets.

@bastman

bastman/Micro_Lib_Db_Mysql_Pdo_Exception.php

Created February 1, 2012 18:05
Show Gist options
  • Save bastman/1718370 to your computer and use it in GitHub Desktop.
Save bastman/1718370 to your computer and use it in GitHub Desktop.
Download ZIP
Php Mysql PDO Client (standalone)
Raw
Micro_Lib_Db_Mysql_Pdo_Exception.php
<?php
/**
* Created by JetBrains PhpStorm.
* User: seb
* To change this template use File | Settings | File Templates.
*/
class Micro_Lib_Db_Mysql_Pdo_Exception extends Exception
{
/**
* @var null|string
*/
protected $_message;
/**
* @var null|string
*/
protected $_command;
/**
* @var string|null
*/
protected $_method;
/**
* @var int|null
*/
protected $_methodLine;
/**
* @var string|null
*/
protected $_userMessage;
/**
* @var array
*/
protected $_data;
/**
* @var array|null
*/
protected $_fault;
/**
* @param string|null $message
* @param null $code
* @param null $previous
*
*/
public function __construct($message = null, $code = null, $previous = null)
{
// edit xris: "previous erst ab php 5.3!
parent::__construct($message, $code);
$this->_message = $message;
}
/**
* @param string $message
* @return void
*/
public function setMessage($message)
{
$this->_message = "".$message;
}
/**
* @param string $value
* @return void
*/
public function setCommand($value)
{
if (is_string($value)!==true) {
$value = null;
}
$this->_command = "".$value;
}
/**
* @return string
*/
public function getCommand()
{
return "".$this->_command;
}
/**
* @param int $line
* @return void
*/
public function setMethodLine($line)
{
$this->_methodLine = (int)$line;
}
/**
* override
* @return int
*/
public function getMethodLine()
{
return (int)$this->_methodLine;
}
/**
* @param string|null $message
* @return Lib_Application_Exception
*/
public function setUserMessage($value)
{
if (is_string($value)!==true) {
$value = null;
}
$this->_userMessage = "".$value;
}
/**
* @return null|string
*/
public function getUserMessage()
{
return "".$this->_userMessage;
}
/**
* @return string
*/
public function getMethod()
{
return "".$this->_method;
}
/**
* @param string|null $value
*/
public function setMethod($value)
{
if (is_string($value)!==true) {
$value = null;
}
$this->_method = "".$value;
}
/**
* @return array
*/
public function getData()
{
return (array)$this->_data;
}
/**
* @param array|null $value
*/
public function setData($value)
{
if ($value !== null) {
$value = (array)$value;
}
$this->_data = $value;
}
/**
* @param array|Exception|null $fault
*/
public function setFault($fault)
{
$value = null;
if ($fault === null) {
$this->_fault = null;
return;
}
if ($fault instanceof Exception) {
if ($fault instanceof Micro_Lib_Micro_Exception) {
/**
* @var $fault Micro_Lib_Micro_Exception
*/
$value = $fault->export();
$this->_fault = (array)$value;
return;
} else {
$value = Micro_Lib_Micro_Utils_Exception::export($fault);
$this->_fault = (array)$value;
return;
}
}
$value = (array)$fault;
$this->_fault = $value;
}
/**
* @return array|null
*/
public function getFault()
{
if ($this->_fault === null) {
return null;
}
return (array)$this->_fault;
}
}
Raw
Mirco_Lib_Db_Mysql_Pdo_Client.php
<?php
/**
* Created by JetBrains PhpStorm.
* User: VAIO
* To change this template use File | Settings | File Templates.
*/
class Mirco_Lib_Db_Mysql_Pdo_Client
{
/**
* @var array
*/
protected $_queryHistory;
/**
* @var PDO
*/
protected $_connection;
/**
* @var array
*/
protected $_config = array(
"host" => "localhost",
"port" => "3306",
"username" => "root",
"password" => "",
"database" => "foo",
"socket" => null,
"charset" => "utf8",
"persistent" => false,
"options" => array(),
);
/**
* @param array $config
*/
public function setConfig(array $config)
{
$this->_config = $config;
}
/**
* @return array
*/
public function getConfig()
{
return (array)$this->_config;
}
/**
* @return PDO
*/
public function getConnection()
{
if(!($this->_connection instanceof PDO)) {
$config = $this->getConfig();
$this->_connection = $this->newConnection($config);
}
return $this->_connection;
}
/**
*
*/
public function closeConnection()
{
if($this->_connection instanceof PDO) {
$this->_connection = null;
}
}
/**
* @param array $config
* @return PDO
* @throws Exception
*/
public function newConnection(array $config)
{
$host = $config["host"];
$port = $config["port"];
$database = $config["database"];
$username = $config["username"];
$password = $config["password"];
$charset = "".trim("".strtolower("".$config["charset"]));
$persistent = ($config["persistent"]===true);
$options = (array)$config["options"];
switch($charset) {
case "utf8": {
$options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES utf8";
break;
}
default: {
throw new Exception("Invalid config.charset at ".__METHOD__);
break;
}
}
$options[PDO::ATTR_PERSISTENT] = $persistent;
$options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
$dnsInfo = array(
"host" => $host,
"port" => $port,
"dbname" => $database,
);
$dsn = "mysql:";
foreach($dnsInfo as $key => $value)
{
$dsn.="".$key."=".$value.";";
}
$pdo = new PDO(
$dsn,
$username,
$password,
$options
);
return $pdo;
}
// ++++++++++++ query history +++++++++++++++++++++++++++++++++++++++++++
public function getQueryHistory()
{
if(!is_array($this->_queryHistory)) {
$this->_queryHistory = array();
}
return $this->_queryHistory;
}
/**
* @param array $item
*/
protected function _addQueryHistoryItem( array $item)
{
$history = $this->getQueryHistory();
array_unshift($history, $item);
$this->_queryHistory = $history;
}
// ++++++++++++++++++++ query +++++++++++++++++++++++++++
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @return array
* @throws Exception
*/
public function newQueryInsert($options, $table, array $rowInsert)
{
$result = array(
"sql" => "",
"params" => array(),
);
$sql =
"INSERT
{{options}}
INTO
{{table}}
( {{fields}} )
VALUES
( {{values}} )
;";
$options = "" . trim("" . strtoupper("" . $options));
$options = (array)explode(" ", $options);
$optionsAvailable = array(
"LOW_PRIORITY",
"DELAYED",
"HIGH_PRIORITY",
"IGNORE"
);
$_options = array();
foreach ($options as $option) {
if (!is_string($option)) {
throw new Exception(
"Invalid option type: "
. gettype($option)
. " at " . __METHOD__
);
}
$option = "" . $option;
$option = "" . trim("" . strtoupper("" . $option));
if ($option === "") {
continue;
}
if (!in_array($option, $optionsAvailable, true)) {
throw new Exception(
"Invalid option (is not available) at " . __METHOD__
);
}
}
$options = "" . implode(" ", $_options);
$row = (array)$rowInsert;
$fields = array();
$values = array();
$params = array();
foreach ($row as $key => $value) {
$fieldName = "" . trim("".$key);
$paramName = ":" . $fieldName;
$fields[] = $this->quoteIdentifier($fieldName);
$values[] = $paramName;
$params[$paramName] = $value;
}
$templateData = array(
"{{options}}" => $options,
"{{table}}" => $this->quoteIdentifier($table, true),
"{{fields}}" => implode(" , ", $fields),
"{{values}}" => implode(" , ", $values),
);
$sql = $this->parseTemplate($sql, $templateData);
$result["sql"] = "".$sql;
$result["params"] = (array)$params;
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @return array
* @throws Exception
*/
public function newQueryInsertOnDuplicateKeyUpdate(
$options,
$table,
array $rowInsert,
array $rowUpdate
)
{
$result = array(
"sql" => "",
"params" => array(),
);
$sql =
"INSERT
{{options}}
INTO
{{table}}
( {{fields}} )
VALUES
( {{values}} )
ON DUPLICATE KEY UPDATE
{{set}}
;";
$options = "" . trim("" . strtoupper("" . $options));
$options = (array)explode(" ", $options);
$optionsAvailable = array(
"LOW_PRIORITY",
"DELAYED",
"HIGH_PRIORITY",
"IGNORE"
);
$_options = array();
foreach ($options as $option) {
if (!is_string($option)) {
throw new Exception(
"Invalid option type: "
. gettype($option)
. " at " . __METHOD__
);
}
$option = "" . $option;
$option = "" . trim("" . strtoupper("" . $option));
if ($option === "") {
continue;
}
if (!in_array($option, $optionsAvailable, true)) {
throw new Exception(
"Invalid option (is not available) at " . __METHOD__
);
}
}
$options = "" . implode(" ", $_options);
$row = (array)$rowInsert;
$fields = array();
$values = array();
$params = array();
foreach ($row as $key => $value) {
$fieldName = "" . trim("".$key);
$paramName = ":VALUES_" . $fieldName;
$fields[] = $this->quoteIdentifier($fieldName);
$values[] = $paramName;
$params[$paramName] = $value;
}
$set = array();
foreach ($rowUpdate as $key => $value) {
$fieldName = "" . $key;
$paramName = ":SET_" . $fieldName;
if(in_array($paramName, $params, true)) {
throw new Exception(
"Parameter already used. name=".$paramName
);
}
$setQuery = "{{KEY}} = {{VALUE}}";
$setQuery = $this->parseTemplate(
$setQuery,
array(
"{{KEY}}" => $this->quoteIdentifier($fieldName, true),
"{{VALUE}}" => $paramName,
)
);
$set[] = $setQuery;
$params[$paramName] = $value;
}
$templateData = array(
"{{options}}" => $options,
"{{table}}" => $this->quoteIdentifier($table, true),
"{{fields}}" => implode(" , ", $fields),
"{{values}}" => implode(" , ", $values),
"{{set}}" => implode(" , ", $set),
);
$sql = $this->parseTemplate($sql, $templateData);
$result["sql"] = "".$sql;
$result["params"] = (array)$params;
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowUpdate
* @return array
* @throws Exception
*/
public function newQueryUpdate(
$options,
$table,
array $rowUpdate,
$where,
array $params
)
{
$result = array(
"sql" => "",
"params" => array(),
);
$table = "".trim("".$table);
$where = "".trim("".$where);
$sql =
"UPDATE
{{options}}
{{table}}
SET
{{set}}
WHERE
{{where}}
;";
$options = "" . trim("" . strtoupper("" . $options));
$options = (array)explode(" ", $options);
$optionsAvailable = array(
"LOW_PRIORITY",
"IGNORE"
);
$_options = array();
foreach ($options as $option) {
if (!is_string($option)) {
throw new Exception(
"Invalid option type: "
. gettype($option)
. " at " . __METHOD__
);
}
$option = "" . $option;
$option = "" . trim("" . strtoupper("" . $option));
if ($option === "") {
continue;
}
if (!in_array($option, $optionsAvailable, true)) {
throw new Exception(
"Invalid option (is not available) at " . __METHOD__
);
}
}
$options = "" . implode(" ", $_options);
$_params = array();
foreach($params as $key => $value) {
$fieldName = "" . trim("".$key);
$paramName = ":".$fieldName;
$_params[$paramName] = $value;
}
$set = array();
foreach ($rowUpdate as $key => $value) {
$fieldName = "" . trim($key);
$paramName = ":SET_" . $fieldName;
if(array_key_exists($paramName, $_params)) {
throw new Exception(
"Parameter already used. ".$paramName
);
}
$setQuery = "{{KEY}} = {{VALUE}}";
$setQuery = $this->parseTemplate(
$setQuery,
array(
"{{KEY}}" => $this->quoteIdentifier($fieldName, true),
"{{VALUE}}" => $paramName,
)
);
$set[] = $setQuery;
$_params[$paramName] = $value;
}
$templateData = array(
"{{options}}" => $options,
"{{table}}" => $this->quoteIdentifier($table, true),
"{{set}}" => implode(" , ", $set),
"{{where}}" => "".$where,
);
$sql = $this->parseTemplate($sql, $templateData);
$result["sql"] = "".$sql;
$result["params"] = (array)$_params;
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @return array
* @throws Exception
*/
public function newQueryDelete(
$options,
$table,
$where,
array $params
)
{
$result = array(
"sql" => "",
"params" => array(),
);
$table = "".trim("".$table);
$where = "".trim("".$where);
$sql =
"DELETE
{{options}}
FROM
{{table}}
WHERE
{{where}}
;";
$options = "" . trim("" . strtoupper("" . $options));
$options = (array)explode(" ", $options);
$optionsAvailable = array(
"LOW_PRIORITY",
"QUICK",
"IGNORE",
);
$_options = array();
foreach ($options as $option) {
if (!is_string($option)) {
throw new Exception(
"Invalid option type: "
. gettype($option)
. " at " . __METHOD__
);
}
$option = "" . $option;
$option = "" . trim("" . strtoupper("" . $option));
if ($option === "") {
continue;
}
if (!in_array($option, $optionsAvailable, true)) {
throw new Exception(
"Invalid option (is not available) at " . __METHOD__
);
}
}
$options = "" . implode(" ", $_options);
$_params = array();
foreach($params as $key => $value) {
$fieldName = "" . trim("".$key);
$paramName = ":".$fieldName;
$_params[$paramName] = $value;
}
$templateData = array(
"{{options}}" => $options,
"{{table}}" => $this->quoteIdentifier($table, true),
"{{where}}" => "".$where,
);
$sql = $this->parseTemplate($sql, $templateData);
$result["sql"] = "".$sql;
$result["params"] = (array)$_params;
return $result;
}
/**
* @param string $options
* @param string $table
* @return array
* @throws Exception
*/
public function newQuerySelect(
$select,
array $params
)
{
$result = array(
"sql" => "",
"params" => array(),
);
$select = "".trim("".$select);
$sql =
"
{{select}}
;";
$_params = array();
foreach($params as $key => $value) {
$fieldName = "" . trim("".$key);
$paramName = ":".$fieldName;
$_params[$paramName] = $value;
}
$templateData = array(
"{{select}}" => "".$select,
);
$sql = $this->parseTemplate($sql, $templateData);
$result["sql"] = "".$sql;
$result["params"] = (array)$_params;
return $result;
}
// ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/**
* @param PDOStatement $stmt
* @param array $params
* @throws Exception
*/
protected function _stmtBindParams(PDOStatement $stmt, array $params)
{
$params = (array)$params;
$_params = array();
foreach($params as $key => $value) {
$_params[$key] = $value;
}
$params = $_params;
foreach($params as $key => $value) {
$key = "".$key;
if(strlen(trim($key))<1) {
throw new Exception(
"Invalid parameter name at ".__METHOD__
);
}
$isValidType = ( (is_scalar($value)) || ($value===null) );
if((is_resource($value)) || (is_array($value))) {
$isValidType = false;
}
if(!$isValidType) {
throw new Exception(
"Invalid bind type for parameter="
.$key." type=".gettype($value)
." at ".__METHOD__
);
}
if($value === null) {
$stmt->bindParam($key, $params[$key], PDO::PARAM_NULL);
continue;
}
if(is_bool($value)) {
$stmt->bindParam($key, $params[$key], PDO::PARAM_NULL);
continue;
}
if(is_string($value)) {
$stmt->bindParam($key, $params[$key], PDO::PARAM_STR);
continue;
}
if(is_int($value)) {
$stmt->bindParam($key, $params[$key], PDO::PARAM_INT);
continue;
}
$stmt->bindParam($key, $params[$key]);
}
}
// +++++++++++++++ SQL: INSERT ON DUPLICATE KEY UPDATE ++++++++++++++
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return int
*/
public function insertOnDuplicateKeyUpdate(
$table, array $rowInsert, array $rowUpdate
)
{
$options = "";
$result = $this->_insertOnDuplicateKeyUpdate(
$options, $table, $rowInsert, $rowUpdate
);
return $result;
}
/**
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return int
*/
public function insertOptionsOnDuplicateKeyUpdate(
$options, $table, array $rowInsert, array $rowUpdate
)
{
$result = $this->_insertOnDuplicateKeyUpdate(
$options, $table, $rowInsert, $rowUpdate
);
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return
* @throws Exception|Micro_Lib_Db_Mysql_Pdo_Exception
*/
protected function _insertOnDuplicateKeyUpdate(
$options, $table, array $rowInsert, array $rowUpdate)
{
$result = null;
if($options === null) {
$options = "";
}
if(!is_string($options)) {
throw new Exception("Invalid parameter 'options' at ".__METHOD__);
}
if(!is_string($table)) {
throw new Exception("Invalid parameter 'table' at ".__METHOD__);
}
if(!is_array($rowInsert)) {
throw new Exception("Invalid parameter 'rowInsert' at ".__METHOD__);
}
$query = $this->newQueryInsertOnDuplicateKeyUpdate(
$options, $table, $rowInsert, $rowUpdate
);
$this->_addQueryHistoryItem($query);
$sql = $query["sql"];
$params = $query["params"];
$stmt = null;
$error = null;
$errorType="";
$stmtPrepareError = null;
$stmtBindError = null;
$stmtExecuteError = null;
$stmtResult = null;
$hasError = false;
try {
if(!$hasError) {
$stmt = $this->getConnection()->prepare($sql);
}
}catch(Exception $e) {
$stmtPrepareError = $e;
$error = $e;
$errorType = "STMT_PREPARE";
$hasError = true;
}
try {
if(!$hasError) {
$this->_stmtBindParams($stmt, $params);
}
}catch(Exception $e) {
$stmtBindError = $e;
$error = $e;
$errorType = "STMT_BIND";
$hasError = true;
}
try {
if(!$hasError) {
$stmtResult = $stmt->execute();
}
}catch(Exception $e) {
$stmtExecuteError = $e;
$error = $e;
$errorType = "STMT_EXECUTE";
$hasError = true;
}
if($stmt instanceof PDOStatement) {
if($stmtResult !== true) {
$hasError = true;
}
if (((int)$stmt->errorCode())!==0) {
$hasError = true;
}
}
if(!$hasError) {
return;
}
$fault = array(
"class" => null,
"type" => "".$errorType,
"message" => "",
"query" => array(
"sql" => $sql,
"params" => $params,
),
"pdo" => array(
"errorCode" => null,
"errorInfo" => array(),
),
);
if($error instanceof Exception) {
$fault["class"] = get_class($error);
$fault["message"] = $error->getMessage();
}
if($stmt instanceof PDOStatement) {
$fault["pdo"]["errorCode"] = "".$stmt->errorCode();
$fault["pdo"]["errorInfo"] = (array)$stmt->errorInfo();
}
$exception = new Micro_Lib_Db_Mysql_Pdo_Exception(
"DB: INSERT ON DUPLICATE KEY failed."
);
$exception->setMethod(__METHOD__);
$exception->setFault($fault);
throw $exception;
}
// +++++++ sql: INSERT +++++++++++++++++++++++++++++++++++++++
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param $returnId
* @return int
*/
public function insert(
$table, array $rowInsert, $returnId
)
{
$options = "";
$result = $this->_insert($options, $table, $rowInsert, $returnId);
return $result;
}
/**
* @param string $table
* @param array $rowInsert
* @param $returnId
* @return int
*/
public function insertOptions(
$options, $table, array $rowInsert, $returnId
)
{
$result = $this->_insert($options, $table, $rowInsert, $returnId);
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param bool $returnId
* @return int
* @throws Exception|Micro_Lib_Db_Mysql_Pdo_Exception
*/
protected function _insert(
$options, $table, array $rowInsert, $returnId)
{
$result = 0;
if($options === null) {
$options = "";
}
if(!is_string($options)) {
throw new Exception("Invalid parameter 'options' at ".__METHOD__);
}
if(!is_string($table)) {
throw new Exception("Invalid parameter 'table' at ".__METHOD__);
}
if(!is_array($rowInsert)) {
throw new Exception("Invalid parameter 'rowInsert' at ".__METHOD__);
}
if(!is_bool($returnId)) {
throw new Exception("Invalid parameter 'returnId' at ".__METHOD__);
}
$query = $this->newQueryInsert($options, $table, $rowInsert);
$this->_addQueryHistoryItem($query);
$sql = $query["sql"];
$params = $query["params"];
$stmt = null;
$error = null;
$errorType="";
$stmtPrepareError = null;
$stmtBindError = null;
$stmtExecuteError = null;
$stmtResult = null;
$hasError = false;
try {
if(!$hasError) {
$stmt = $this->getConnection()->prepare($sql);
}
}catch(Exception $e) {
$stmtPrepareError = $e;
$error = $e;
$errorType = "STMT_PREPARE";
$hasError = true;
}
try {
if(!$hasError) {
$this->_stmtBindParams($stmt, $params);
}
}catch(Exception $e) {
$stmtBindError = $e;
$error = $e;
$errorType = "STMT_BIND";
$hasError = true;
}
try {
if(!$hasError) {
$stmtResult = $stmt->execute();
}
}catch(Exception $e) {
$stmtExecuteError = $e;
$error = $e;
$errorType = "STMT_EXECUTE";
$hasError = true;
}
if($stmt instanceof PDOStatement) {
if($stmtResult !== true) {
$hasError = true;
}
if (((int)$stmt->errorCode())!==0) {
$hasError = true;
}
}
if(!$hasError) {
if(!$returnId) {
return $result;
}
$id = (int)$this->getConnection()->lastInsertId(null);
if($id>0) {
return $id;
}
throw new Exception(
"STMT_RESULT Invalid lastId after insert at ".__METHOD__
);
}
$fault = array(
"class" => null,
"type" => "".$errorType,
"message" => "",
"query" => array(
"sql" => $sql,
"params" => $params,
),
"pdo" => array(
"errorCode" => null,
"errorInfo" => array(),
),
);
if($error instanceof Exception) {
$fault["class"] = get_class($error);
$fault["message"] = $error->getMessage();
}
if($stmt instanceof PDOStatement) {
$fault["pdo"]["errorCode"] = "".$stmt->errorCode();
$fault["pdo"]["errorInfo"] = (array)$stmt->errorInfo();
}
$exception = new Micro_Lib_Db_Mysql_Pdo_Exception("DB: INSERT failed.");
$exception->setMethod(__METHOD__);
$exception->setFault($fault);
throw $exception;
}
// +++++++++++++++ SQL: UPDATE ++++++++++++++
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return int
*/
public function update(
$table, array $rowUpdate, $where, array $params
)
{
$options = "";
$result = $this->_update(
$options, $table, $rowUpdate, $where, $params
);
return $result;
}
/**
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return int
*/
public function updateOptions(
$options, $table, array $rowUpdate, $where, array $params
)
{
$result = $this->_update(
$options, $table, $rowUpdate, $where, $params
);
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return
* @throws Exception|Micro_Lib_Db_Mysql_Pdo_Exception
*/
protected function _update(
$options, $table, array $rowUpdate, $where, array $params
)
{
$result = null;
if($options === null) {
$options = "";
}
if(!is_string($options)) {
throw new Exception("Invalid parameter 'options' at ".__METHOD__);
}
if(!is_string($table)) {
throw new Exception("Invalid parameter 'table' at ".__METHOD__);
}
$query = $this->newQueryUpdate(
$options, $table, $rowUpdate, $where, $params
);
$this->_addQueryHistoryItem($query);
$sql = $query["sql"];
$params = $query["params"];
$stmt = null;
$error = null;
$errorType="";
$stmtPrepareError = null;
$stmtBindError = null;
$stmtExecuteError = null;
$stmtResult = null;
$hasError = false;
try {
if(!$hasError) {
$stmt = $this->getConnection()->prepare($sql);
}
}catch(Exception $e) {
$stmtPrepareError = $e;
$error = $e;
$errorType = "STMT_PREPARE";
$hasError = true;
}
try {
if(!$hasError) {
$this->_stmtBindParams($stmt, $params);
}
}catch(Exception $e) {
$stmtBindError = $e;
$error = $e;
$errorType = "STMT_BIND";
$hasError = true;
}
try {
if(!$hasError) {
$stmtResult = $stmt->execute();
}
}catch(Exception $e) {
$stmtExecuteError = $e;
$error = $e;
$errorType = "STMT_EXECUTE";
$hasError = true;
}
if($stmt instanceof PDOStatement) {
if($stmtResult !== true) {
$hasError = true;
}
if (((int)$stmt->errorCode())!==0) {
$hasError = true;
}
}
if(!$hasError) {
return;
}
$fault = array(
"class" => null,
"type" => "".$errorType,
"message" => "",
"query" => array(
"sql" => $sql,
"params" => $params,
),
"pdo" => array(
"errorCode" => null,
"errorInfo" => array(),
),
);
if($error instanceof Exception) {
$fault["class"] = get_class($error);
$fault["message"] = $error->getMessage();
}
if($stmt instanceof PDOStatement) {
$fault["pdo"]["errorCode"] = "".$stmt->errorCode();
$fault["pdo"]["errorInfo"] = (array)$stmt->errorInfo();
}
$exception = new Micro_Lib_Db_Mysql_Pdo_Exception(
"DB: UPDATE failed."
);
$exception->setMethod(__METHOD__);
$exception->setFault($fault);
throw $exception;
}
// +++++++++++++++ SQL: DELETE ++++++++++++++
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return int
*/
public function delete(
$table, $where, array $params
)
{
$options = "";
$result = $this->_delete(
$options, $table, $where, $params
);
return $result;
}
/**
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return int
*/
public function deleteOptions(
$options, $table, $where, array $params
)
{
$result = $this->_delete(
$options, $table, $where, $params
);
return $result;
}
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return
* @throws Exception|Micro_Lib_Db_Mysql_Pdo_Exception
*/
protected function _delete(
$options, $table, $where, array $params
)
{
$result = null;
if($options === null) {
$options = "";
}
if(!is_string($options)) {
throw new Exception("Invalid parameter 'options' at ".__METHOD__);
}
if(!is_string($table)) {
throw new Exception("Invalid parameter 'table' at ".__METHOD__);
}
$query = $this->newQueryDelete(
$options, $table, $where, $params
);
$this->_addQueryHistoryItem($query);
$sql = $query["sql"];
$params = $query["params"];
$stmt = null;
$error = null;
$errorType="";
$stmtPrepareError = null;
$stmtBindError = null;
$stmtExecuteError = null;
$stmtResult = null;
$hasError = false;
try {
if(!$hasError) {
$stmt = $this->getConnection()->prepare($sql);
}
}catch(Exception $e) {
$stmtPrepareError = $e;
$error = $e;
$errorType = "STMT_PREPARE";
$hasError = true;
}
try {
if(!$hasError) {
$this->_stmtBindParams($stmt, $params);
}
}catch(Exception $e) {
$stmtBindError = $e;
$error = $e;
$errorType = "STMT_BIND";
$hasError = true;
}
try {
if(!$hasError) {
$stmtResult = $stmt->execute();
}
}catch(Exception $e) {
$stmtExecuteError = $e;
$error = $e;
$errorType = "STMT_EXECUTE";
$hasError = true;
}
if($stmt instanceof PDOStatement) {
if($stmtResult !== true) {
$hasError = true;
}
if (((int)$stmt->errorCode())!==0) {
$hasError = true;
}
}
if(!$hasError) {
return;
}
$fault = array(
"class" => null,
"type" => "".$errorType,
"message" => "",
"query" => array(
"sql" => $sql,
"params" => $params,
),
"pdo" => array(
"errorCode" => null,
"errorInfo" => array(),
),
);
if($error instanceof Exception) {
$fault["class"] = get_class($error);
$fault["message"] = $error->getMessage();
}
if($stmt instanceof PDOStatement) {
$fault["pdo"]["errorCode"] = "".$stmt->errorCode();
$fault["pdo"]["errorInfo"] = (array)$stmt->errorInfo();
}
$exception = new Micro_Lib_Db_Mysql_Pdo_Exception(
"DB: DELETE failed."
);
$exception->setMethod(__METHOD__);
$exception->setFault($fault);
throw $exception;
}
// +++++++++++++++ SQL: DELETE ++++++++++++++
/**
* @param string $options
* @param string $table
* @param array $rowInsert
* @param array $rowUpdate
* @return array
*/
public function select(
$select, array $params
)
{
$result = $this->_select(
$select, $params
);
return $result;
}
/**
* @return
* @throws Exception|Micro_Lib_Db_Mysql_Pdo_Exception
*/
protected function _select(
$select, array $params
)
{
$result = null;
$query = $this->newQuerySelect(
$select, $params
);
$this->_addQueryHistoryItem($query);
$sql = $query["sql"];
$params = $query["params"];
$stmt = null;
$error = null;
$errorType="";
$stmtPrepareError = null;
$stmtBindError = null;
$stmtExecuteError = null;
$stmtResult = null;
$hasError = false;
$items = array();
try {
if(!$hasError) {
$stmt = $this->getConnection()->prepare($sql);
}
}catch(Exception $e) {
$stmtPrepareError = $e;
$error = $e;
$errorType = "STMT_PREPARE";
$hasError = true;
}
try {
if(!$hasError) {
$this->_stmtBindParams($stmt, $params);
}
}catch(Exception $e) {
$stmtBindError = $e;
$error = $e;
$errorType = "STMT_BIND";
$hasError = true;
}
try {
if(!$hasError) {
$stmtResult = $stmt->execute();
}
}catch(Exception $e) {
$stmtExecuteError = $e;
$error = $e;
$errorType = "STMT_EXECUTE";
$hasError = true;
}
if($stmt instanceof PDOStatement) {
if($stmtResult !== true) {
$hasError = true;
}
if (((int)$stmt->errorCode())!==0) {
$hasError = true;
}
}
if(!$hasError) {
$items = (array)$stmt->fetchAll(PDO::FETCH_ASSOC);
$result = $items;
return $result;
}
$fault = array(
"class" => null,
"type" => "".$errorType,
"message" => "",
"query" => array(
"sql" => $sql,
"params" => $params,
),
"pdo" => array(
"errorCode" => null,
"errorInfo" => array(),
),
);
if($error instanceof Exception) {
$fault["class"] = get_class($error);
$fault["message"] = $error->getMessage();
}
if($stmt instanceof PDOStatement) {
$fault["pdo"]["errorCode"] = "".$stmt->errorCode();
$fault["pdo"]["errorInfo"] = (array)$stmt->errorInfo();
}
$exception = new Micro_Lib_Db_Mysql_Pdo_Exception(
"DB: SELECT failed."
);
$exception->setMethod(__METHOD__);
$exception->setFault($fault);
throw $exception;
}
// +++++++++++++++++ simple text templating +++++++++++++++++++++
/**
* @param string $template
* @param array $replace
* @return string
*/
public function parseTemplate ($template, array $replace) {
// @see: http://www.php.net/manual/en/function.strtr.php#106282
$text = "".$template;
$_replace = array();
foreach($replace as $key => $value) {
$_value = "".$value;
$_replace[$key] = $_value;
}
$replace = (array)$_replace;
$keys = array_keys($replace);
$length = array_combine($keys, array_map('strlen', $keys));
arsort($length);
$array[] = $text;
$count = 1;
reset($length);
while ($key = key($length)) {
if (strpos($text, $key) !== false) {
for ($i = 0; $i < $count; $i += 2) {
if (($pos = strpos($array[$i], $key)) === false) {
continue;
}
$value = $replace[$key];
array_splice(
$array,
$i,
1,
array(
substr($array[$i], 0, $pos),
$value,
substr($array[$i], $pos + strlen($key))
)
);
$count += 2;
}
}
next($length);
}
return implode("", $array);
}
// +++++++++++++++++ quoting from Zend Framework +++++++++++++++++++++++
/**
* Remove parts of a SQL string that contain quoted strings
* of values or identifiers.
*
* @param string $sql
* @return string
*/
/*
public function stripQuoted($sql)
{
// get the character for delimited id quotes,
// this is usually " but in MySQL is `
$d = $this->quoteIdentifier('a');
$d = $d[0];
// get the value used as an escaped delimited id quote,
// e.g. \" or "" or \`
$de = $this->quoteIdentifier($d);
$de = substr($de, 1, 2);
$de = str_replace('\\', '\\\\', $de);
// get the character for value quoting
// this should be '
$q = $this->quote('a');
$q = $q[0];
// get the value used as an escaped quote,
// e.g. \' or ''
$qe = $this->quote($q);
$qe = substr($qe, 1, 2);
$qe = str_replace('\\', '\\\\', $qe);
// get a version of the SQL statement with all quoted
// values and delimited identifiers stripped out
// remove "foo\"bar"
$sql = preg_replace("/$q($qe|\\\\{2}|[^$q])*$q/", '', $sql);
// remove 'foo\'bar'
if (!empty($q)) {
$sql = preg_replace("/$q($qe|[^$q])*$q/", '', $sql);
}
return $sql;
}
*/
/**
* Quote a raw string.
*
* @param mixed $value Raw string
*
* @return string Quoted string
*/
/*
public function quote($value)
{
if (is_int($value) || is_float($value)) {
return $value;
}
//$this->_connect();
return "'" . $this->getConnection()->quote($value) . "'";
}
*/
/**
* Quotes a value and places into a piece of text at a placeholder.
*
* The placeholder is a question-mark; all placeholders will be replaced
* with the quoted value. For example:
*
* <code>
* $text = "WHERE date < ?";
* $date = "2005-01-02";
* $safe = $sql->quoteInto($text, $date);
* // $safe = "WHERE date < '2005-01-02'"
* </code>
*
* @param string $text The text with a placeholder.
* @param mixed $value The value to quote.
* @param string $type OPTIONAL SQL datatype
* @param integer $count OPTIONAL count of placeholders to replace
* @return string An SQL-safe quoted value placed into the original text.
*/
/*
public function quoteInto($text, $value, $type = null, $count = null)
{
if ($count === null) {
return str_replace('?', $this->quote($value, $type), $text);
} else {
while ($count > 0) {
if (strpos($text, '?') !== false) {
$text = substr_replace(
$text,
$this->quote($value, $type), strpos($text, '?')
, 1);
}
--$count;
}
return $text;
}
}
*/
/**
* Quotes an identifier.
*
* Accepts a string representing a qualified indentifier. For Example:
* <code>
* $adapter->quoteIdentifier('myschema.mytable')
* </code>
* Returns: "myschema"."mytable"
*
* Or, an array of one or more identifiers that may form a qualified identifier:
* <code>
* $adapter->quoteIdentifier(array('myschema','my.table'))
* </code>
* Returns: "myschema"."my.table"
*
* The actual quote character surrounding the identifiers may vary depending on
* the adapter.
*
* @param string|array|Zend_Db_Expr $ident The identifier.
* @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
* @return string The quoted identifier.
*/
public function quoteIdentifier($ident, $auto=false)
{
return $this->_quoteIdentifierAs($ident, null, $auto);
}
/**
* Quote a column identifier and alias.
*
* @param string|array|Zend_Db_Expr $ident The identifier or expression.
* @param string $alias An alias for the column.
* @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
* @return string The quoted identifier and alias.
*/
public function quoteColumnAs($ident, $alias, $auto=false)
{
return $this->_quoteIdentifierAs($ident, $alias, $auto);
}
/**
* Quote a table identifier and alias.
*
* @param string|array|Zend_Db_Expr $ident The identifier or expression.
* @param string $alias An alias for the table.
* @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
* @return string The quoted identifier and alias.
*/
public function quoteTableAs($ident, $alias = null, $auto = false)
{
return $this->_quoteIdentifierAs($ident, $alias, $auto);
}
/**
* Quote an identifier and an optional alias.
*
* @param string|array|Zend_Db_Expr $ident The identifier or expression.
* @param string $alias An optional alias.
* @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
* @param string $as The string to add between the identifier/expression and the alias.
* @return string The quoted identifier and alias.
*/
/**
* Quote an identifier and an optional alias.
*
* @param string|array|Zend_Db_Expr $ident The identifier or expression.
* @param string $alias An optional alias.
* @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
* @param string $as The string to add between the identifier/expression and the alias.
* @return string The quoted identifier and alias.
*/
protected function _quoteIdentifierAs(
$ident, $alias = null, $auto = false, $as = ' AS '
)
{
if (is_string($ident)) {
$ident = explode('.', $ident);
}
if (is_array($ident)) {
$segments = array();
foreach ($ident as $segment) {
$segments[] = $this->_quoteIdentifier($segment, $auto);
}
if ($alias !== null && end($ident) == $alias) {
$alias = null;
}
$quoted = implode('.', $segments);
} else {
$quoted = $this->_quoteIdentifier($ident, $auto);
}
if ($alias !== null) {
$quoted .= $as . $this->_quoteIdentifier($alias, $auto);
}
return $quoted;
}
/**
* Quote an identifier.
*
* @param string $value The identifier or expression.
* @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
* @return string The quoted identifier and alias.
*/
protected function _quoteIdentifier($value, $auto=false)
{
$autoQuoteIdentifiers=true;
if ($auto === false || $autoQuoteIdentifiers === true) {
$q = $this->getQuoteIdentifierSymbol();
return ($q . str_replace("$q", "$q$q", $value) . $q);
}
return $value;
}
/**
* Returns the symbol the adapter uses for delimiting identifiers.
*
* @return string
*/
public function getQuoteIdentifierSymbol()
{
return "`";
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment