/**
* execute method
*
* If there is no query or the query is true, execute has probably been called as part of a
* db-agnostic process which does not have a mongo equivalent, don't do anything.
*
* @param mixed $query
* @param array $params array()
* @return void
* @access public
*/
public function execute($query, $params = array()) {
if (!$this->isConnected()) {
return false;
}
if (!$query || $query === true) {
return;
}
$this->_prepareLogQuery($Model); // just sets a timer
$return = $this->_db
->execute($query, $params);
if ($this->fullDebug) {
if ($params) {
$this->logQuery(":query, :params",
compact('query', 'params')
);
} else {
$this->logQuery($query);
}
}
if ($return['ok']) {
return $return['retval'];
}
return $return;
}
/**
* Gets the tables list
* @return array
* @uses $connection
*/
protected function _tables()
{
$tables = $this->connection->execute(sprintf('SHOW TABLES;'))->fetchAll();
return array_map(function ($table) {
return firstValue($table);
}, $tables);
}
/**
* Make a query.
*
* Should be used for insert and update queries, but also can be used as iterator for select queries.
*
* @example models\Db::query('INSERT INTO posts (title) VALUES (?)', ['New post title'], 'pgsql1');
* @example $query = models\Db::query('SELECT * FROM posts', null, 'pgsql1');<br />
* foreach ($query as $item)<br />
* {<br />
* // Do something with the $item<br />
* }
* @access public
* @static
* @param string $query
* @param mixed[] $data (default: null)
* @param string $name (default: 'default')
* @return PDOStatement Returns statement created by query.
*/
public static function query($query, $data = null, $name = 'default')
{
$db_link =& self::$db_links[$name]['link'];
if (empty($query)) {
return null;
}
if (empty($db_link)) {
throw new \Exception('No connection to database');
}
// Do request
if (!empty(self::$db_links[$name]['config']['debug'])) {
Load::startTimer();
}
self::$last_statement = $db_link->prepare($query);
self::$last_statement->execute((array) $data);
if (!empty(self::$db_links[$name]['config']['debug'])) {
$log = $query;
if (!empty($data)) {
$log_data = array_map(function ($item) {
return is_integer($item) == true ? $item : "'" . $item . "'";
}, (array) $data);
$log = str_replace(array_pad([], substr_count($query, '?'), '?'), $log_data, $query);
}
Load::stopTimer($log);
}
// Return last statement
return self::$last_statement;
}
/**
* Inserts $message to the currently open database. Calls open(),
* if necessary. Also passes the message along to any Log_observer
* instances that are observing this Log.
*
* @param mixed $message String or object containing the message to log.
* @param string $priority The priority of the message. Valid
* values are: PEAR_LOG_EMERG, PEAR_LOG_ALERT,
* PEAR_LOG_CRIT, PEAR_LOG_ERR, PEAR_LOG_WARNING,
* PEAR_LOG_NOTICE, PEAR_LOG_INFO, and PEAR_LOG_DEBUG.
* @return boolean True on success or false on failure.
* @access public
*/
function log($message, $priority = null)
{
/* If a priority hasn't been specified, use the default value. */
if ($priority === null) {
$priority = $this->_priority;
}
/* Abort early if the priority is above the maximum logging level. */
if (!$this->_isMasked($priority)) {
return false;
}
/* If the connection isn't open and can't be opened, return failure. */
if (!$this->_opened && !$this->open()) {
return false;
}
/* If we don't already have a statement object, create one. */
if (!is_object($this->_statement) && !$this->_prepareStatement()) {
return false;
}
/* Extract the string representation of the message. */
$message = $this->_extractMessage($message);
/* Build our set of values for this log entry. */
$values = array('id' => $this->_db->nextId($this->_sequence), 'logtime' => MDB2_Date::mdbNow(), 'ident' => $this->_ident, 'priority' => $priority, 'message' => $message);
/* Execute the SQL query for this log entry insertion. */
$this->_db->expectError(MDB2_ERROR_NOSUCHTABLE);
$result =& $this->_statement->execute($values);
$this->_db->popExpect();
/* Attempt to handle any errors. */
if (PEAR::isError($result)) {
/* We can only handle MDB2_ERROR_NOSUCHTABLE errors. */
if ($result->getCode() != MDB2_ERROR_NOSUCHTABLE) {
return false;
}
/* Attempt to create the target table. */
if (!$this->_createTable()) {
return false;
}
/* Recreate our prepared statement resource. */
$this->_statement->free();
if (!$this->_prepareStatement()) {
return false;
}
/* Attempt to re-execute the insertion query. */
$result = $this->_statement->execute($values);
if (PEAR::isError($result)) {
return false;
}
}
$this->_announce(array('priority' => $priority, 'message' => $message));
return true;
}
/**
* execute method
*
* @param mixed $query
* @param array $params array()
* @return void
* @access public
*/
public function execute($query, $params = array())
{
$this->_prepareLogQuery($Model);
// just sets a timer
$result = $this->_db->execute($query, $params);
if ($this->fullDebug) {
if ($params) {
$this->logQuery(":query, :params", compact('query', 'params'));
} else {
$this->logQuery($query);
}
}
if ($result['ok']) {
return $result['retval'];
}
return $result;
}
/**
* DBに接続する
*
* @return boolean DB接続の処理結果(true/false)
* @access public
*/
function connect()
{
if ($this->_conn != null) {
return true;
}
if (!$this->_dsn) {
return false;
}
$option = "";
$prefix = "?";
foreach ($this->_options as $key => $value) {
if (is_bool($value)) {
$option .= "{$prefix}{$key}";
} else {
$option .= "{$prefix}{$key}={$value}";
}
if ($prefix == "?") {
$prefix = "&";
}
}
$this->_conn =& NewADOConnection($this->_dsn . $option);
if (!is_object($this->_conn)) {
return false;
}
$this->_conn->SetFetchMode(ADODB_FETCH_ASSOC);
if (strstr($this->_dsn, "mysql")) {
$server_info = $this->_conn->ServerInfo();
//if($server_info["version"] > )
if (floatval($server_info["version"]) >= 4.01) {
$result = $this->_conn->execute("SET NAMES " . DATABASE_CHARSET . ";");
if (!$result) {
$log =& LogFactory::getLog();
$log->error("文字コード変換に失敗しました", "DbObjectAdodb#connect");
}
}
// クライアントの文字セットを設定する。
// mysql_set_charset は PHP 5.2.3、MySQL 5.0.7 以降
// http://php.net/manual/ja/function.mysql-set-charset.php
if (version_compare(phpversion(), '5.2.3', '>=') && $server_info['version'] >= '5.0.7') {
mysql_set_charset(DATABASE_CHARSET);
}
}
//$this->_conn->SetCharSet(DATABASE_CHARSET);
return true;
}
/**
* testOr method
*
* @return void
* @access public
*/
public function testOr()
{
$mongoVersion = $this->mongodb->execute('db.version()');
$shouldSkip = version_compare($mongoVersion, '1.5.3', '<');
if ($this->skipIf($shouldSkip, '$or tests require at least version mongo version 1.5.3, currently using ' . $mongoVersion . ' %s')) {
return;
}
$MongoArticle = ClassRegistry::init('MongoArticle');
$MongoArticle->create();
for ($i = 1; $i <= 20; $i++) {
$data = array('title' => "Article {$i}", 'subtitle' => "Sub Article {$i}");
$saveData['MongoArticle'] = $data;
$MongoArticle->create();
$MongoArticle->save($saveData);
}
$expected = $MongoArticle->find('all', array('conditions' => array('title' => array('$in' => array('Article 1', 'Article 10'))), 'order' => array('number' => 'ASC')));
$this->assertTrue(count($expected), 2);
$result = $MongoArticle->find('all', array('conditions' => array('$or' => array(array('title' => 'Article 1'), array('title' => 'Article 10'))), 'order' => array('number' => 'ASC')));
$this->assertEqual($result, $expected);
}
/**
* Execute prepared query
* Warning : this function is buggy when it used with serialised datas into an insert or an update statement
* Use it only for select.
*
* @param string $sql : the prepared query to execute
* @param array $params : the parameters for the query
* @return void
* @access private
*/
public function executePreparedQuery($sql, $params)
{
$this->_sql = trim($sql);
$this->_result = $this->_db->prepare($this->_sql);
if ($this->_result) {
if ($this->_result->execute($params)) {
//the last inserted id only has a sense if it's an insert query
if (preg_match("#^insert#i", $this->_sql)) {
$this->_lastInsertedID = $this->_db->lastInsertId();
}
return true;
}
}
$clean_sql = str_replace("\n", "", $this->_sql);
$clean_sql = preg_replace("#\t+#", " ", $clean_sql);
$errorInfos = $this->_db->errorInfo();
$errorInfo = isset($errorInfos[2]) ? $errorInfos[2] : 'no error returned';
$this->raiseError('Prepared query failed : ' . $errorInfo . "\nQuery : " . $clean_sql . "\nParameters : " . print_r($params, true));
return false;
}
/**
* 创建数据表
* @param resource $db 数据库连接资源
*/
function create_tables($db, $prefix = '')
{
//读取SQL文件
$sql = file_get_contents(MODULE_PATH . 'Data/install.sql');
$sql = str_replace("\r", "\n", $sql);
$sql = explode(";\n", $sql);
//替换表前缀
$orginal = C('ORIGINAL_TABLE_PREFIX');
$sql = str_replace(" `{$orginal}", " `{$prefix}", $sql);
//开始安装
show_msg('开始安装数据库...');
foreach ($sql as $value) {
$value = trim($value);
if (empty($value)) {
continue;
}
if (substr($value, 0, 12) == 'CREATE TABLE') {
$name = preg_replace("/^CREATE TABLE IF NOT EXISTS `(\\w+)` .*/s", "\\1", $value);
$msg = "创建数据表{$name}";
if (false !== $db->execute($value)) {
show_msg($msg . '...成功');
} else {
show_msg($msg . '...失败！', 'error');
session('error', true);
}
} else {
$db->execute($value);
}
}
}
/**
* 更新数据表
* @param resource $db 数据库连接资源
* @author lyq <*****@*****.**>
*/
function update_tables($db, $prefix = '')
{
//读取SQL文件
$sql = file_get_contents(MODULE_PATH . 'Data/update.sql');
$sql = str_replace("\r", "\n", $sql);
$sql = explode(";\n", $sql);
//替换表前缀
$sql = str_replace(" `wp_", " `{$prefix}", $sql);
//开始安装
show_msg('开始升级数据库...');
foreach ($sql as $value) {
$value = trim($value);
if (empty($value)) {
continue;
}
if (substr($value, 0, 12) == 'CREATE TABLE') {
$name = preg_replace("/^CREATE TABLE `(\\w+)` .*/s", "\\1", $value);
$msg = "创建数据表{$name}";
if (false !== $db->execute($value)) {
show_msg($msg . '...成功');
} else {
show_msg($msg . '...失败！', 'error');
session('error', true);
}
} else {
if (substr($value, 0, 8) == 'UPDATE `') {
$name = preg_replace("/^UPDATE `(\\w+)` .*/s", "\\1", $value);
$msg = "更新数据表{$name}";
} else {
if (substr($value, 0, 11) == 'ALTER TABLE') {
$name = preg_replace("/^ALTER TABLE `(\\w+)` .*/s", "\\1", $value);
$msg = "修改数据表{$name}";
} else {
if (substr($value, 0, 11) == 'INSERT INTO') {
$name = preg_replace("/^INSERT INTO `(\\w+)` .*/s", "\\1", $value);
$msg = "写入数据表{$name}";
}
}
}
if ($db->execute($value) !== false) {
show_msg($msg . '...成功');
} else {
show_msg($msg . '...失败！', 'error');
session('error', true);
}
}
}
}
/**
* Copies the contents of from_table in the first database into the
* to a to_table of suitable schema in a second database. It assumes the
* table exists in both databases
*
* @param string $from_table name of the table to be copied from
* @param resource $from_dbm database resource for the from table
* @param resource $to_table name of the table to be copied to
* @param resource $to_dbm database resource for the to table
*/
static function copyTable($from_table, $from_dbm, $to_table, $to_dbm)
{
$sql = "SELECT * FROM {$from_table}";
if (($result = $from_dbm->execute($sql)) === false) {
return false;
}
while ($row = $from_dbm->fetchArray($result)) {
$statement = "INSERT INTO {$to_table} VALUES (";
$comma = "";
foreach ($row as $col => $value) {
$statement .= $comma . " '" . $to_dbm->escapeString($value) . "'";
$comma = ",";
}
$statement .= ")";
if ($to_dbm->execute($statement) === false) {
return false;
}
}
return true;
}
/**
* Performs an UPDATE on a single table
* @param string table name
* @param array associative of field_name => type
* @param string (optional) SQL where clause
* @param array associative (optional) of field_name => value
* @return boolean true on success, false on failure
* @access public
*/
function update($table, $fields, $where = NULL, $extrafields = NULL)
{
$query = 'UPDATE ' . $table . $this->buildAssignmentSQL($fields, $extrafields, 'update');
if (!is_null($where)) {
$query .= ' WHERE ' . $where;
}
return (bool) $this->Connection->execute($query);
}
/**
* 创建数据表
* @param resource $db 数据库连接资源
*/
function create_tables($db, $prefix = '')
{
//读取SQL文件
$sql = file_get_contents(MODULE_PATH . 'Data/install.sql');
show_msg(write_install_log('读取安装所需的sql文件：' . MODULE_PATH . 'Data/install.sql'));
if (file_exists(MODULE_PATH . 'Data/data.sql')) {
$sql .= "\n" . file_get_contents(MODULE_PATH . 'Data/data.sql');
show_msg(write_install_log('自动读取检测到的表数据文件：' . MODULE_PATH . 'Data/data.sql'));
}
if (file_exists(MODULE_PATH . 'Data/patch.sql')) {
$sql .= "\n" . file_get_contents(MODULE_PATH . 'Data/patch.sql');
show_msg(write_install_log('自动读取检测到的补丁sql文件：' . MODULE_PATH . 'Data/patch.sql'));
}
$other_patch = glob(MODULE_PATH . 'Data/patch_*.sql');
if ($other_patch) {
foreach ($other_patch as $value) {
$sql .= "\n" . file_get_contents($value);
show_msg(write_install_log('自动读取检测到的补丁sql文件：' . $value));
}
}
//替换表前缀
$orginal = C('ORIGINAL_TABLE_PREFIX');
$sql = str_replace(" `{$orginal}", " `{$prefix}", $sql);
$sql = str_replace("\r", "\n", $sql);
$sql = explode(";\n", $sql);
//开始安装
show_msg(write_install_log('开始安装数据库...'));
foreach ($sql as $value) {
$value = trim($value);
if (empty($value)) {
continue;
}
$value = preg_replace('/[\\r\\n]+[ \\t]*--[^\\n]*\\n/', '', $value);
$value = preg_replace('/^[ \\t]*--[^\\n]*\\n/', '', $value);
$value = trim($value);
if (empty($value)) {
continue;
}
if (substr($value, 0, 12) == 'CREATE TABLE') {
$name = preg_replace("/^CREATE TABLE IF NOT EXISTS `(\\w+)` .*/s", "\\1", $value);
$msg = "创建数据表{$name}";
if (false !== $db->execute($value)) {
show_msg(write_install_log($msg . '...成功'));
} else {
show_msg(write_install_log($msg . '...失败！'), 'error');
session('error', true);
}
} else {
if (false !== $db->execute($value)) {
write_install_log($value . '...成功');
} else {
write_install_log($value . '...失败');
}
}
}
}
/**
* Execute the SQL statement.
*
* @return mixed A database cursor resource on success, boolean false on failure.
*
* @since 1.0
* @throws \Exception
* @throws \RuntimeException
*/
public function execute()
{
static $isReconnecting = false;
$this->connect();
if (!is_object($this->connection)) {
throw new \RuntimeException($this->errorMsg, $this->errorNum);
}
// Take a local copy so that we don't modify the original query and cause issues later
$sql = $this->replacePrefix((string) $this->sql);
if ($this->limit > 0 || $this->offset > 0) {
// @TODO
$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
}
// Increment the query counter.
$this->count++;
// If debugging is enabled then let's log the query.
if ($this->debug) {
// Add the query to the object queue.
$this->log[] = $sql;
}
// Reset the error values.
$this->errorNum = 0;
$this->errorMsg = '';
// Execute the query.
$this->executed = false;
if ($this->prepared instanceof \PDOStatement) {
// Bind the variables:
if ($this->sql instanceof ADatabaseQueryPreparable) {
$bounded =& $this->sql->getBounded();
foreach ($bounded as $key => $obj) {
$this->prepared->bindParam($key, $obj->value, $obj->dataType, $obj->length, $obj->driverOptions);
}
}
$this->executed = $this->prepared->execute();
}
// If an error occurred handle it.
if (!$this->executed) {
// Get the error number and message before we execute any more queries.
$errorNum = (int) $this->connection->errorCode();
$errorMsg = (string) 'SQL: ' . implode(", ", $this->connection->errorInfo());
// Check if the server was disconnected.
if (!$this->connected() && !$isReconnecting) {
$isReconnecting = true;
try {
// Attempt to reconnect.
$this->connection = null;
$this->connect();
} catch (\RuntimeException $e) {
// Get the error number and message.
$this->errorNum = (int) $this->connection->errorCode();
$this->errorMsg = (string) 'SQL: ' . implode(", ", $this->connection->errorInfo());
// Throw the normal query exception.
throw new \RuntimeException($this->errorMsg, $this->errorNum);
}
// Since we were able to reconnect, run the query again.
$result = $this->execute();
$isReconnecting = false;
return $result;
} else {
// Get the error number and message from before we tried to reconnect.
$this->errorNum = $errorNum;
$this->errorMsg = $errorMsg;
// Throw the normal query exception.
throw new \RuntimeException($this->errorMsg, $this->errorNum);
}
}
return $this->prepared;
}
/**
* This function does several execute() calls on the same statement handle.
* $params must be an array indexed numerically from 0, one execute call is
* done for every 'row' in the array.
*
* If an error occurs during execute(), executeMultiple() does not execute
* the unfinished rows, but rather returns that error.
*
* @param resource $stmt query handle from prepare()
* @param array $params numeric array containing the
* data to insert into the query
* @return mixed a result handle or MDB2_OK on success, a MDB2 error on failure
* @access public
* @see prepare(), execute()
*/
function executeMultiple(&$stmt, $params = null)
{
for ($i = 0, $j = count($params); $i < $j; $i++) {
$stmt->bindParamArray($params[$i]);
$result = $stmt->execute();
if (PEAR::isError($result)) {
return $result;
}
}
return MDB2_OK;
}
/**
* 执行写入
*
* @access public
* @param $sql
* @return int|false
*/
public function execute($sql)
{
Super('Log')->record($sql, 'mysql');
return $this->_db->execute($sql);
}
/**
* 创建数据表
* @param resource $db 数据库连接资源
*/
public function create_tables($db, $prefix = '')
{
//读取SQL文件
$sql = file_get_contents(dirname(dirname(MODULE_PATH)) . '/Public/Install/boss.sql');
$sql = str_replace("\r", "\n", $sql);
$sql = explode(";\n", $sql);
// \n 后面不可有任何空格
//替换表前缀
$orginal = C('ORIGINAL_TABLE_PREFIX');
$sql = str_replace(" `{$orginal}", " `{$prefix}", $sql);
//开始安装
// $this->show('<br>... 开始导入 SQL 文件到数据库 ...<br>');
//$sql = trim($sql);
//print_r($sql);
foreach ($sql as $value) {
$value = trim($value);
if (empty($value)) {
continue;
}
if (substr($value, 0, 12) == 'CREATE TABLE') {
$name = preg_replace("/^CREATE TABLE `(\\w+)` .*/s", "\\1", $value);
if (false !== $db->execute($value)) {
// $this->show("<br>... 创建数据表: （ {$name} ） ... 成功<br>");
} else {
// $this->show("<br><font sytle='color:red'>... 创建数据表: （ {$name} ） ... 失败</font><br>", 'error');
session('error', true);
}
} else {
$r = $db->execute($value);
}
}
// echo "<a href='" . U('User/index') . "'><button style='padding:10px;margin:20px 100px;font-size:20px;'>跳 转</button></a>";
}
/**
* Does several execute() calls on the same statement handle
*
* @link http://pear.php.net/package/MDB2/docs/2.5.0b3/MDB2/MDB2_Extended.html#methodexecuteMultiple
* @param resource $stmt Statement handle
* @param array $params numeric array containing the data to insert into the query
*/
public function executeMultiple($stmt, $params)
{
foreach ($params as $values) {
$stmt->execute($values);
}
}
/**
* Execute the SQL statement.
*
* @return mixed A database cursor resource on success, boolean false on failure.
*
* @since 1.0
* @throws \Exception
* @throws \RuntimeException
*/
public function execute()
{
$this->connect();
// Take a local copy so that we don't modify the original query and cause issues later
$sql = $this->replacePrefix((string) $this->sql);
if ($this->limit > 0 || $this->offset > 0) {
// @TODO
$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
}
// Increment the query counter.
$this->count++;
// If debugging is enabled then let's log the query.
if ($this->debug) {
// Add the query to the object queue.
$this->log(Log\LogLevel::DEBUG, '{sql}', array('sql' => $sql, 'category' => 'databasequery', 'trace' => debug_backtrace()));
}
// Reset the error values.
$this->errorNum = 0;
$this->errorMsg = '';
// Execute the query.
$this->executed = false;
if ($this->prepared instanceof \PDOStatement) {
// Bind the variables:
if ($this->sql instanceof PreparableInterface) {
$bounded =& $this->sql->getBounded();
foreach ($bounded as $key => $obj) {
$this->prepared->bindParam($key, $obj->value, $obj->dataType, $obj->length, $obj->driverOptions);
}
}
$this->executed = $this->prepared->execute();
}
// If an error occurred handle it.
if (!$this->executed) {
// Get the error number and message before we execute any more queries.
$errorNum = (int) $this->connection->errorCode();
$errorMsg = (string) 'SQL: ' . implode(", ", $this->connection->errorInfo());
// Check if the server was disconnected.
if (!$this->connected()) {
try {
// Attempt to reconnect.
$this->connection = null;
$this->connect();
} catch (ConnectionFailureException $e) {
// Get the error number and message.
$this->errorNum = (int) $this->connection->errorCode();
$this->errorMsg = (string) 'SQL: ' . implode(", ", $this->connection->errorInfo());
$this->log(Log\LogLevel::ERROR, 'Database query failed (error #{code}): {message}; Failed query: {sql}', array('code' => $this->errorNum, 'message' => $this->errorMsg, 'sql' => $sql));
throw new ExecutionFailureException($sql, $this->errorMsg, $this->errorNum);
}
// Since we were able to reconnect, run the query again.
return $this->execute();
} else {
// Get the error number and message from before we tried to reconnect.
$this->errorNum = $errorNum;
$this->errorMsg = $errorMsg;
// Throw the normal query exception.
$this->log(Log\LogLevel::ERROR, 'Database query failed (error #{code}): {message}; Failed query: {sql}', array('code' => $this->errorNum, 'message' => $this->errorMsg, 'sql' => $sql));
throw new ExecutionFailureException($sql, $this->errorMsg, $this->errorNum);
}
}
return $this->prepared;
}
/**
* 创建数据表
* @param resource $db 数据库连接资源
*/
private function create_tables($db, $prefix = '', $tb_id = "")
{
//读取SQL文件
$sql = file_get_contents(dirname(dirname(MODULE_PATH)) . '/Public/Install/tb_three.sql');
$sql = str_replace("\r", "\n", $sql);
$sql = explode(";\n", $sql);
// \n 后面不可有任何空格
//替换表前缀
$orginal = C('ORIGINAL_TABLE_PREFIX');
$tb = $prefix . $tb_id . "_";
$sql = str_replace(" `{$prefix}", " `{$tb}", $sql);
//$sql = str_replace(" `{$orginal}", " `{$prefix}", $sql);
//开始安装
// $this->show('<br>... 开始导入 SQL 文件到数据库 ...<br>');
//$sql = trim($sql);
//print_r($sql);
foreach ($sql as $value) {
$value = trim($value);
if (empty($value)) {
continue;
}
if (substr($value, 0, 12) == 'CREATE TABLE') {
$name = preg_replace("/^CREATE TABLE `(\\w+)` .*/s", "\\1", $value);
// $name = $name . $tb_id;
if (false !== $db->execute($value)) {
// $this->show("<br>... 创建数据表: （ {$name} ） ... 成功<br>");
} else {
// $this->show("<br><font sytle='color:red'>... 创建数据表: （ {$name} ） ... 失败</font><br>", 'error');
session('error', true);
}
} else {
$r = $db->execute($value);
}
}
}
