/**
* Drop a MySQL table
*
* @param string $sTable
* @return bool
*/
public function drop($sTable = '')
{
// if table name not pass
if (!empty($sTable)) {
// create count query
$this->sSql = "DROP TABLE `{$sTable}`;";
// pdo prepare statement
$this->_oSTH = $this->prepare($this->sSql);
try {
if ($this->_oSTH->execute()) {
// close pdo
$this->_oSTH->closeCursor();
// return number of count
return true;
} else {
self::error($this->_oSTH->errorInfo());
}
} catch (PDOException $e) {
// get pdo error and pass on error method
self::error($e->getMessage() . ': ' . __LINE__);
}
} else {
self::error('Table name not found..');
}
}
/**
* 执行sql查询
*
* @param string sql语句
* @param array 参数数组
* @param string 返回结果绑定到的对象
* @param boolean 是否输出调试语句
* @return void
*/
public function query($sql, $params = array(), $class = 'stdClass', $debug = FALSE)
{
// 预处理绑定语句
try {
$this->stmt = $this->db->prepare($sql);
if (!$this->stmt) {
\core\Handler::appException('pdo prepare error with:' . $sql);
throw new \Exception("system error", '49903');
}
// 参数绑定
!$params or $this->bindValue($params);
// 输出调试
!$debug or $this->debug($sql, $params);
// 执行一条sql语句
if ($this->stmt->execute()) {
// 设置解析模式
$this->stmt->setFetchMode(\PDO::FETCH_CLASS, $class);
} else {
throw new \Exception('system error', '49904');
// 获取数据库错误信息
\core\Handler::appException($this->stmt->errorInfo()[2]);
}
} catch (\Exception $e) {
\core\Handler::appException($e->getMessage());
throw new \Exception('system error', '49902');
}
}
/**
* Get Total Number Of Records in Requested Table
*
* @param string $sTable
* @return number
*/
public function count($sTable = '')
{
// if table name not pass
if (!empty($sTable)) {
// create count query
$this->sSql = "SELECT COUNT(*) AS NUMROWS FROM {$sTable};";
// pdo prepare statement
$this->_oSTH = $this->prepare($this->sSql);
try {
if ($this->_oSTH->execute()) {
// fetch array result
$this->aResults = $this->_oSTH->fetch();
// close pdo
$this->_oSTH->closeCursor();
// return number of count
return $this->aResults['NUMROWS'];
} else {
self::error($this->_oSTH->errorInfo());
}
} catch (PDOException $e) {
// get pdo error and pass on error method
self::error($e->getMessage() . ': ' . __LINE__);
}
} else {
self::error('Table name not found..');
}
}
/**
* 执行操作的底层接口
*
* @param string $sql
* @param array $params
* @return PDO Statement
*/
protected function _autoExecute($sql, $params = array())
{
try {
$this->_getChoiceDbConnect();
if (!$this->_db) {
exit('DB connection lost.');
}
// 调试模式打印SQL信息
$explain = array();
if (Com_Db::enableLogging() && DEBUG_EXPLAIN_SQL) {
$explain = $this->_explain($sql, $params);
}
$sqlStartTime = microtime(true);
// 预编译 SQL
$stmt = $this->_db->prepare($sql);
if (!$stmt) {
exit(implode(',', $this->_db->errorInfo()));
}
// 绑定参数
$params = $params ? (array) $params : array();
// 执行 SQL
if (!$stmt->execute($params)) {
exit(implode(',', $stmt->errorInfo()));
}
$sqlCostTime = microtime(true) - $sqlStartTime;
// 调试模式打印SQL信息
if (Com_Db::enableLogging()) {
Com_Db::sqlLog($this->_formatLogSql($sql, $params), $sqlCostTime, $explain);
}
return $stmt;
} catch (Exception $e) {
Com_Db_Exception::process($e, '[SQL Failed]', $this->_formatLogSql($sql, $params));
return false;
}
}
/**
* 执行SQL
*
* @param string $sSQL
* @param array $aParam
* @param boolean $bStrictMaster
* @param boolean $bIsADU
* @return unknown
*/
protected function _exectue($sSQL, $aParam, $bStrictMaster, $bIsADU = false)
{
$iStartTime = microtime(true);
++self::$_iQueryCnt;
self::$_aSQLs[] = $this->_sLastSQL = $sSQL;
$db = $bStrictMaster ? $this->_getMasterDB() : $this->_getSlaveDB();
$this->_oSth = $db->prepare($sSQL);
if (!empty($aParam)) {
$this->_bindParams($aParam);
}
$bRet = $this->_oSth->execute();
if (false === $bRet) {
$sMsg = 'SQL Error: ' . $this->_formatSQL($sSQL, $aParam) . "\n";
$sMsg .= join("\n", $this->_oSth->errorInfo());
throw new Exception($sMsg);
return 0;
}
$iUseTime = round((microtime(true) - $iStartTime) * 1000, 2);
self::$_iUseTime += $iUseTime;
$iAffectedRows = $this->_oSth->rowCount();
if ($this->_oDebug) {
$this->_oDebug->debug('[DB->' . $this->_sDbName . ']: ' . $this->_formatSQL($sSQL, $aParam) . ' AffectedRows:' . $iAffectedRows . ' Use Time:' . $iUseTime . '毫秒');
}
// 记录增删改日志
if ($bIsADU) {
self::_addADUSQL('[DB->' . $this->_sDbName . ']: ' . $this->_formatSQL($sSQL, $aParam) . ' AffectedRows:' . $iAffectedRows . ' Use Time:' . $iUseTime . '毫秒');
}
if ($iAffectedRows > 0 && $bIsADU) {
$this->clearWhereCache();
}
return $iAffectedRows;
}
/**
* Prepare and execute query.
*
* @param string $sql SQL string to query.
* @param array $params Omit to do a normal query instead of a PDO prepared query.
*
* @return resource
*/
public function query($sql, $params = null)
{
if (!$params) {
$this->resultset = $this->dbh->query($sql);
} else {
$stmt = $this->dbh->prepare($sql);
if (!empty($params)) {
foreach ($params as $column => $param) {
if (is_int($params[$column])) {
$type = PDO::PARAM_INT;
} else {
if (is_null($params[$column]) || $params[$column] === null) {
$type = PDO::PARAM_NULL;
} else {
$type = PDO::PARAM_STR;
}
}
$stmt->bindValue(':' . $column, $params[$column], $type);
}
}
$stmt->execute();
$this->resultset = $stmt;
if ($this->debug) {
var_dump($params);
}
}
if ($this->debug) {
echo '<strong style="color: #dd0000;">' . $sql . '</strong> :: ' . implode(', ', $this->dbh->errorInfo()) . '<br /><br />';
}
return $this->resultset;
}
/**
* Repairs and reindexes the table. This might take a long time on a very large table.
* @var string $tableName The name of the table.
* @return boolean Return true if the table has integrity after the method is complete.
*/
public function checkAndRepairTable($tableName = null)
{
$ok = true;
if (!SapphireTest::using_temp_db() && !self::$checked_and_repaired) {
$this->alterationMessage("Checking database integrity", "repaired");
if ($msgs = $this->query('PRAGMA integrity_check')) {
foreach ($msgs as $msg) {
if ($msg['integrity_check'] != 'ok') {
Debug::show($msg['integrity_check']);
$ok = false;
}
}
}
if (self::$vacuum) {
$this->query('VACUUM', E_USER_NOTICE);
if ($this instanceof SQLitePDODatabase) {
$msg = $this->dbConn->errorInfo();
$msg = isset($msg[2]) ? $msg[2] : 'no errors';
} else {
$msg = $this->dbConn->lastErrorMsg();
}
if (preg_match('/authoriz/', $msg)) {
$this->alterationMessage('VACUUM | ' . $msg, "error");
} else {
$this->alterationMessage("VACUUMing", "repaired");
}
}
self::$checked_and_repaired = true;
}
return $ok;
}
/**
* Return an error string.
*
* @param resource The query resource.
* @return int The error string of the current error.
*/
function error_string($query)
{
if (!is_object($query) || !method_exists($query, "errorInfo")) {
return $this->db->errorInfo();
}
return $query->errorInfo();
}
/**
* 获取数据库错误描述信息
*
* @access public
*
* @param PDOStatement $query
*
* @return string
*
* @example
* 例一：
* $erronInfo = $this->lastError();
*
* 例二：
* $sth = $this->_dbLink->prepare('select * from tablename');
* $sth->execute();
*
* $erronInfo = $this->lastError($sth);
*
*/
public function lastError(PDOStatement $query = null)
{
$error = !$query ? $this->_dbLink->errorInfo() : $query->errorInfo();
if (!$error[2]) {
return null;
}
return $error[1] . ': ' . $error[2];
}
public static function rollBack()
{
try {
if (!self::$instance instanceof \PDO) {
throw new \PDOException(self::$exception['no-instance']);
}
if (!self::$instance->rollBack()) {
throw new \PDOException(current(self::$instance->errorInfo()) . ' ' . end(self::$instance->errorInfo()));
}
} catch (\PDOException $e) {
self::stackTrace($e);
}
}
/**
* Reads user data from the given data source
* If only $handle is given, it will read the data
* from the first user with that handle and return
* true on success.
* If $handle and $passwd are given, it will try to
* find the first user with both handle and password
* matching and return true on success (this allows
* multiple users having the same handle but different
* passwords - yep, some people want this).
* if only an auth_user_id is passed it will try to read the data based on the id
* If no match is found, false is being returned.
*
* @param string user handle
* @param string user password
* @param bool|int if the user data should be read using the auth user id
* @return bool true on success or false on failure
*
* @access public
*/
function readUserData($handle = '', $passwd = '', $auth_user_id = false)
{
$fields = array();
foreach ($this->tables['users']['fields'] as $field => $req) {
$fields[] = $this->alias[$field] . ' AS ' . $field;
}
// Setting the default query.
$query = 'SELECT ' . implode(',', $fields) . '
FROM ' . $this->prefix . $this->alias['users'] . '
WHERE ';
if ($auth_user_id) {
$query .= $this->alias['auth_user_id'] . '=' . $this->dbc->quote($auth_user_id);
} else {
if (!is_array($this->handles) || empty($this->handles)) {
$this->stack->push(LIVEUSER_ERROR_CONFIG, 'exception', array('reason' => 'No handle set in storage config.'));
return false;
}
$handles = array();
foreach ($this->handles as $field) {
$handles[] = $this->alias[$field] . '=' . $this->dbc->quote($handle);
}
$query .= '(' . implode(' OR ', $handles) . ')';
if (!is_null($this->tables['users']['fields']['passwd'])) {
// If $passwd is set, try to find the first user with the given
// handle and password.
$query .= ' AND ' . $this->alias['passwd'] . '=' . $this->dbc->quote($this->encryptPW($passwd));
}
}
// Query database
$res = $this->dbc->query($query);
if ($res === false) {
$error_info = $this->dbc->errorInfo();
$this->stack->push(LIVEUSER_ERROR, 'exception', array('reason' => $error_info[2], 'debug' => $query));
return false;
}
$result = $res->fetch(PDO::FETCH_ASSOC);
if ($result === false && $this->dbc->errorCode() != '00000') {
$this->stack->push(LIVEUSER_ERROR, 'exception', array('reason' => $this->dbc->errorCode(), 'debug' => $this->dbc->errorInfo()));
return false;
}
if (!is_array($result)) {
return null;
}
// User was found, read data into class variables and set return value to true
if (array_key_exists('lastlogin', $result) && !empty($result['lastlogin'])) {
$result['lastlogin'] = strtotime($result['lastlogin']);
}
$this->propertyValues = $result;
return true;
}
/**
* 执行SQL语句
* @param string $sql 要执行的sql语句
* @param array | boolean $param 当执行参数化查询时需要的参数
* @see PDO::exec()
*/
public function execute($sql, $param = null)
{
$this->queries++;
if (empty($sql) && $this->debug) {
debug_print_backtrace();
exit;
}
$this->lastSql[$this->lastSqlNameSpace] = $sql;
$this->lastSqlParam[$this->lastSqlNameSpace] = $param;
if (is_null($param)) {
$this->rows = $this->pdo->exec($sql);
if ($this->rows === false) {
$this->errors = $this->pdo->errorInfo();
// if (in_array($this->errors[1], $this->logSqlcodes)) {
// $this->errors['sql'] = $sql;
// $this->errors['params'] = $param;
// }
$this->errors($this->errors);
return false;
} else {
return true;
}
} else {
$this->stmt = $this->pdo->prepare($sql);
if (is_array($param)) {
$paramList = array();
foreach ($param as $k => $v) {
$paramList[$k] = $v;
}
$param = $paramList;
$result = $this->stmt->execute($param);
} else {
$result = $this->stmt->execute();
}
if (false === $result) {
$this->errors = $this->stmt->errorInfo();
$this->errors['sql'] = $sql;
$this->errors['params'] = $param;
$this->rows = 0;
} else {
$this->errors = null;
$this->rows = $this->stmt->rowCount();
}
return $result;
}
}
/**
* 执行sql查询
* @param string sql语句
* @param array 参数数组
* @return void
*/
public function query($sql, $params = array())
{
// 预处理绑定语句
$this->stmt = $this->pdo->prepare($sql);
// 参数绑定
!$params or $this->bindValue($params);
// 输出调试
!defined('SQL_ECHO') or $this->debug($sql, $params);
// 执行一条sql语句
if ($this->stmt->execute()) {
// 设置解析模式
$this->stmt->setFetchMode(\PDO::FETCH_CLASS, 'stdClass');
} else {
// 抛出一个pdo错误
throw new \PDOException($this->stmt->errorInfo()[2]);
}
}
/**
* Update Password
*
* Run a query to update a password.
*
* @param int $user_account_password The data value
* @param int $user_account_id The data value
* @param string $emailed_hash The data value
* @return null|boolean True/False
*/
public function update_password($user_account_password = false, $user_account_id = false, $emailed_hash = false)
{
$updated = false;
if ($user_account_id && $emailed_hash) {
// UPDATE the emailed_hash in the user_account table.
$statement = $this->db->prepare("UPDATE user_account\n SET user_account_password = :user_account_password, active = 1\n WHERE user_account_id = :user_account_id\n AND emailed_hash = :emailed_hash");
$statement->bindValue(":user_account_password", $user_account_password, PDO::PARAM_STR);
$statement->bindValue(":user_account_id", $user_account_id, PDO::PARAM_INT);
$statement->bindValue(":emailed_hash", $emailed_hash, PDO::PARAM_STR);
$statement->execute();
$error = $this->db->errorInfo();
if ($error[0] != "00000") {
die('The UPDATE user_account password query failed.');
}
$updated = true;
}
return $updated;
}
/**
* Useful for searching.
*
* @param string $table is the table name
* @param array $where $where is an optional array
* of parameters to be feed the where statement
* example: array('name'=>'john')
* @return array of rows
*/
public function findQuery($table, array $where)
{
$query = "SELECT * FROM {$table} WHERE ";
try {
if (!empty($where)) {
foreach ($where as $field => $value) {
$query .= " {$field} LIKE :{$field} OR";
}
$query = substr($query, 0, -3);
}
$stmt = $this->connection->prepare($query);
if ($stmt->execute($where)) {
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
return array();
} catch (\PDOException $ex) {
$this->setErrors($this->connection->errorInfo()[2]);
return array();
}
}
/**
* Exécute une requète sur la base de données
*
* @param string $query
*
* @access public
* @return mixed
*/
function query($query)
{
if ($this->result instanceof PDOStatement) {
$this->result->closeCursor();
}
$curtime = array_sum(explode(' ', microtime()));
$result = $this->pdo->query($query);
$endtime = array_sum(explode(' ', microtime()));
$this->sqltime += $endtime - $curtime;
$this->queries++;
if (!$result) {
$tmp = $this->pdo->errorInfo();
$this->errno = $tmp[1];
$this->error = $tmp[2];
$this->lastQuery = $query;
$this->result = null;
try {
$this->rollBack();
} catch (PDOException $e) {
}
} else {
$this->errno = 0;
$this->error = '';
$this->lastQuery = '';
$this->result = $result;
if (in_array(strtoupper(substr($query, 0, 6)), array('INSERT', 'UPDATE', 'DELETE'))) {
$this->_affectedRows = $result->rowCount();
$result = true;
} else {
$result = new WadbResult_sqlite_pdo($result);
}
}
return $result;
}
/**
* Get PDO error
*
* @param object $stmt Statement
* @param object $op Op
*
* @return string
*/
private static function _getPDOError($stmt, $op)
{
$errorInfo = $stmt->errorInfo();
$error = <<<END
Error encountered while executing {$op}
Error information from PDOStatement->errorInfo() :
SQLSTATE error code: {$errorInfo['0']}
Driver-specific error code: {$errorInfo['1']}
Driver-specific error message: {$errorInfo['2']}
END;
return $error;
}
/**
* 执行预处理语句
*
* @param object $stmt PDOStatement
* @param bool $clearBindParams
*
* @return bool
*/
public function execute($stmt, $clearBindParams = true)
{
//empty($param) && $param = $this->bindParams;
$this->conf['log_slow_sql'] && ($startQueryTimeStamp = microtime(true));
if (!$stmt->execute()) {
$error = $stmt->errorInfo();
throw new \InvalidArgumentException('Pdo execute Sql error!,【Sql : ' . $this->buildDebugSql() . '】,【Error:' . $error[2] . '】');
}
$slow = 0;
if ($this->conf['log_slow_sql']) {
$queryTime = microtime(true) - $startQueryTimeStamp;
if ($queryTime > $this->conf['log_slow_sql']) {
if (Plugin::hook('cml.mysql_query_slow', ['sql' => $this->buildDebugSql(), 'query_time' => $queryTime]) !== false) {
Log::notice('slow_sql', ['sql' => $this->buildDebugSql(), 'query_time' => $queryTime]);
}
$slow = $queryTime;
}
}
if (Cml::$debug) {
$this->debugLogSql($slow > 0 ? Debug::SQL_TYPE_SLOW : Debug::SQL_TYPE_NORMAL, $slow);
}
$this->currentSql = '';
$clearBindParams && $this->clearBindParams();
return true;
}
/**
* 抛出错误异常信息
* @access private
* @param object $sth
* @return void
*/
private function _throwError($sth = '')
{
$err_arr = array();
if ($sth != '') {
$err_arr = $sth->errorInfo();
} else {
$err_arr = $this->errorInfo();
}
//debug_print_backtrace();
if (isset($err_arr[2])) {
trigger_error($err_arr[2], E_USER_ERROR);
}
//写入查询日志
$GLOBALS['gSqlArr'][] = $this->lastSql . ' Second:' . ($this->_microTime() - $this->startTime);
}
/**
* 获取错误信息
* @return string
*/
public function errorInfo()
{
return json_encode($this->db->errorInfo());
}
/**
* 执行预处理语句
*
* @param object $stmt PDOStatement
* @param bool $clearBindParams
*
* @return bool
*/
private function execute($stmt, $clearBindParams = true)
{
//empty($param) && $param = $this->bindParams;
if (!$stmt->execute()) {
$bindParams = $this->bindParams;
foreach ($bindParams as $key => $val) {
$bindParams[$key] = str_replace('\\\\', '\\', addslashes($val));
}
$error = $stmt->errorInfo();
\Cml\throwException('Pdo execute Sql error!,【Sql : ' . vsprintf(str_replace('%s', "'%s'", $this->currentSql), $bindParams) . '】,【Error:' . $error[2] . '】');
}
$this->currentSql = '';
$clearBindParams && ($this->bindParams = array());
return true;
}
/**
* 获取数据库错误描述信息
*
* @access public
* @return string
*/
public function error()
{
$info = $this->db_link->errorInfo();
return $info[2];
}
/**
* retrieves the errorInfo from the PDO statement which holds the standard error infos
*
* @return object
*/
protected function getErrorInfo()
{
return $this->statement->errorInfo();
}
/**
* 执行预处理语句
*
* @param object $stmt PDOStatement
* @param bool $clearBindParams
*
* @return bool
*/
private function execute($stmt, $clearBindParams = true)
{
//empty($param) && $param = $this->bindParams;
$clearBindParams && ($this->bindParams = array());
if (!$stmt->execute()) {
$error = $stmt->errorInfo();
\Cml\throwException($error[2]);
}
return true;
}
/**
* @return array
*/
public function errorInfo()
{
return $this->pdoConn->errorInfo();
}
/**
* {@inheritdoc}
*/
public function error()
{
return $this->dbHandle->errorInfo();
}
/**
* Executes a prepared statement
*
* @param array $input_parameters - An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.
* @return boolean
*/
public function execute(array $input_parameters = array())
{
$queryString = $this->queryString;
if (count($input_parameters) > 0) {
if (count($this->parameters) > 0 || count($this->parametersBound) > 0) {
throw new LikePDOException("There is have parameters bound");
return false;
} else {
foreach ($input_parameters as $key => $param) {
$value = is_int($param) ? intval($param) : $this->pdo->quote($param, LikePDO::PARAM_STR);
if (is_int($key)) {
$position = strpos($queryString, "?");
if (!strstr($queryString, "?")) {
throw new LikePDOException("Statement: Too few parameters");
return false;
}
$queryString = substr_replace($queryString, $value, $position, 1);
} else {
$queryString = str_replace($key, $value, $queryString);
}
}
}
}
if (count($this->parametersBound) > 0) {
foreach ($this->parametersBound as $key => $param) {
$value = $this->pdo->quote($param['variable'], $param['data_type']);
if (is_int($key)) {
$position = strpos($queryString, "?");
if (!strstr($queryString, "?")) {
throw new LikePDOException("Statement: Too few parameters");
return false;
}
$queryString = substr_replace($queryString, $value, $position, 1);
} else {
$queryString = str_replace($key, $value, $queryString);
}
}
}
if (count($this->parameters) > 0) {
foreach ($this->parameters as $key => $param) {
$value = $this->pdo->quote($param['value'], $param['data_type']);
if (is_int($key)) {
$position = strpos($queryString, "?");
if (!strstr($queryString, "?")) {
throw new LikePDOException("Statement: Too few parameters");
return false;
}
$queryString = substr_replace($queryString, $value, $position, 1);
} else {
$queryString = str_replace($key, $value, $queryString);
}
}
}
$this->resource = $this->pdo->driver->query($queryString);
$this->executed = true;
$this->rowsAffected = $this->pdo->driver->getRowsAffected();
$this->columnCount = $this->pdo->driver->getNumFields($this->resource);
if (!$this->resource) {
$this->failed = true;
$this->errorCode = $this->pdo->errorCode();
$this->errorInfo = $this->pdo->errorInfo();
new LikePDOException("Failed to execute the query " . $this->queryString);
return false;
}
return true;
}
/**
* PDO Execute SQL Query.
*
* @return object Execute SQL Query.
*/
public function execute()
{
self::$data->execute() or die(print_r(self::$data->errorInfo(), true));
}
/**
* Log an error that occurred on a handle (database or statement)
*
* @param object $handle PDO handle
*/
private function logErrorInfo($handle, $label = '')
{
$errorInfo = $handle->errorInfo();
$this->errorCode = $errorInfo[1];
$this->errorMsg = $errorInfo[2];
if ($this->useErrorLog) {
error_log('DB CLASS ERROR:(' . $this->errorCode . ') ' . $this->errorMsg . ' :: ' . $this->lastQuery . ' :: URL ' . $_SERVER['REQUEST_URI'] . ' M/S:' . self::$connectParamsMaster['host'] . '/' . $this->connectParams['host'], 0);
}
self::$errorCntr++;
// Check for too many errors
if (self::$errorCntr < 10) {
self::$errors[] = array('connection' => $label, 'code' => $errorInfo[1], 'msg' => $errorInfo[2]);
} elseif (self::$errorCntr == 10) {
self::$errors[] = array('connection' => 'n/a', 'code' => 0, 'msg' => 'Too many errors logged');
}
if ($this->debug > 0) {
echo 'DB ERROR: ' . $errorInfo[1] . '-' . $errorInfo[2] . "\n";
}
return 1;
}