- add building multi insert to dbal

- fix sql explain in mysql4 layer git-svn-id: file:///svn/phpbb/trunk@5242 89ea8834-ac86-4346-8a33-228a782c2dd0
author: Meik Sievertsen <acydburn@phpbb.com> 2005-10-02 18:18:52 +0000
committer: Meik Sievertsen <acydburn@phpbb.com> 2005-10-02 18:18:52 +0000
commit: 1d688c6b8de0073e637be7601e0efbd5947bce00 (patch)
tree: c9a3e8e11c224cf3ce0fdfde05e02e12ecdfb19c /phpBB/includes/db
parent: 1a18e36684d74d5a0c2538805a3ff8436036189e (diff)
download: forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar
forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar.gz
forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar.bz2
forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar.xz
forums-1d688c6b8de0073e637be7601e0efbd5947bce00.zip
2 files changed, 117 insertions, 42 deletions
diff --git a/phpBB/includes/db/dbal.php b/phpBB/includes/db/dbal.php
index 70089aa11f..f91f7ef469 100644
--- a/phpBB/includes/db/dbal.php
+++ b/phpBB/includes/db/dbal.php
@@ -22,17 +22,34 @@ class dbal
 	var $num_queries = 0;
 	var $open_queries = array();
 
+	var $curtime = 0;
+	var $query_hold = '';
+	var $html_hold = '';
+	var $sql_report = '';
+	var $cache_num_queries = 0;
+	
+	
+	/**
+	* return on error or display error message
+	*/
 	function sql_return_on_error($fail = false)
 	{
 		$this->return_on_error = $fail;
 	}
 
+	/**
+	* Return number of sql queries used (cached and real queries are counted the same)
+	*/
 	function sql_num_queries()
 	{
 		return $this->num_queries;
 	}
 
-	// Idea for this from Ikonboard
+	/**
+	* Build sql statement from array for insert/update/select statements
+	*
+	* Idea for this from Ikonboard
+	*/
 	function sql_build_array($query, $assoc_ary = false)
 	{
 		if (!is_array($assoc_ary))
@@ -64,6 +81,32 @@ class dbal
 
 			$query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
 		}
+		else if ($query == 'MULTI_INSERT')
+		{
+			$ary = array();
+			foreach ($assoc_ary as $id => $sql_ary)
+			{
+				$values = array();
+				foreach ($sql_ary as $key => $var)
+				{
+					if (is_null($var))
+					{
+						$values[] = 'NULL';
+					}
+					elseif (is_string($var))
+					{
+						$values[] = "'" . $this->sql_escape($var) . "'";
+					}
+					else
+					{
+						$values[] = (is_bool($var)) ? intval($var) : $var;
+					}
+				}
+				$ary[] = '(' . implode(', ', $values) . ')';
+			}
+
+			$query = ' (' . implode(', ', array_keys($assoc_ary[0])) . ') VALUES ' . implode(', ', $ary);
+		}
 		else if ($query == 'UPDATE' || $query == 'SELECT')
 		{
 			$values = array();
@@ -88,6 +131,9 @@ class dbal
 		return $query;
 	}
 
+	/**
+	* display sql error page
+	*/
 	function sql_error($sql = '')
 	{
 		$error = $this->db_sql_error();
@@ -110,21 +156,22 @@ class dbal
 		return $error;
 	}
 
+	/**
+	* Explain queries
+	* @child _sql_report
+	*/
 	function sql_report($mode, $query = '')
 	{
+		global $cache, $starttime, $phpbb_root_path;
+
 		if (empty($_GET['explain']))
 		{
 			return;
 		}
 
-		global $cache, $starttime, $phpbb_root_path;
-		static $curtime, $query_hold, $html_hold;
-		static $sql_report = '';
-		static $cache_num_queries = 0;
-
-		if (!$query && !empty($query_hold))
+		if (!$query && $this->query_hold != '')
 		{
-			$query = $query_hold;
+			$query = $this->query_hold;
 		}
 
 		switch ($mode)
@@ -139,13 +186,27 @@ class dbal
 				$mtime = explode(' ', microtime());
 				$totaltime = $mtime[0] + $mtime[1] - $starttime;
 
-				echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8869-1"><meta http-equiv="Content-Style-Type" content="text/css"><link rel="stylesheet" href="' . $phpbb_root_path . 'adm/subSilver.css" type="text/css"><style type="text/css">' . "\n";
-				echo 'th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }' . "\n";
-				echo 'td.cat	{ background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') }' . "\n";
-				echo '</style><title>' . $msg_title . '</title></head><body>';
-				echo '<table width="100%" cellspacing="0" cellpadding="0" border="0"><tr><td><a href="' . htmlspecialchars(preg_replace('/&explain=([^&]*)/', '', $_SERVER['REQUEST_URI'])) . '"><img src="' . $phpbb_root_path . 'adm/images/header_left.jpg" width="200" height="60" alt="phpBB Logo" title="phpBB Logo" border="0"/></a></td><td width="100%" background="' . $phpbb_root_path . 'adm/images/header_bg.jpg" height="60" align="right" nowrap="nowrap"><span class="maintitle">SQL Report</span> &nbsp; &nbsp; &nbsp;</td></tr></table><br clear="all"/><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td height="40" align="center" valign="middle"><b>Page generated in ' . round($totaltime, 4) . " seconds with {$this->num_queries} queries" . (($cache_num_queries) ? " + $cache_num_queries " . (($cache_num_queries == 1) ? 'query' : 'queries') . ' returning data from cache' : '') . '</b></td></tr><tr><td align="center" nowrap="nowrap">Time spent on MySQL queries: <b>' . round($this->sql_time, 5) . 's</b> | Time spent on PHP: <b>' . round($totaltime - $this->sql_time, 5) . 's</b></td></tr></table><table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr><td>';
-				echo $sql_report;
-				echo '</td></tr></table><br /></body></html>';
+				echo '
+					<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8869-1"><meta http-equiv="Content-Style-Type" content="text/css"><link rel="stylesheet" href="' . $phpbb_root_path . 'adm/subSilver.css" type="text/css">
+					<style type="text/css">	th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }	td.cat	{ background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') } </style>
+					<title>Explain</title></head><body>
+					<table width="100%" cellspacing="0" cellpadding="0" border="0"><tr>
+						<td><a href="' . htmlspecialchars(preg_replace('/&explain=([^&]*)/', '', $_SERVER['REQUEST_URI'])) . '"><img src="' . $phpbb_root_path . 'adm/images/header_left.jpg" width="200" height="60" alt="phpBB Logo" title="phpBB Logo" border="0" /></a></td>
+						<td width="100%" background="' . $phpbb_root_path . 'adm/images/header_bg.jpg" height="60" align="right" nowrap="nowrap"><span class="maintitle">SQL Report</span> &nbsp; &nbsp; &nbsp;</td>
+					</tr></table>
+					<br clear="all"/>
+					<table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr>
+						<td height="40" align="center" valign="middle"><b>Page generated in ' . round($totaltime, 4) . " seconds with {$this->num_queries} queries" . (($this->cache_num_queries) ? " + {$this->cache_num_queries} " . (($this->cache_num_queries == 1) ? 'query' : 'queries') . ' returning data from cache' : '') . '</b></td>
+					</tr><tr>
+						<td align="center" nowrap="nowrap">Time spent on MySQL queries: <b>' . round($this->sql_time, 5) . 's</b> | Time spent on PHP: <b>' . round($totaltime - $this->sql_time, 5) . 's</b></td>
+					</tr></table>
+					<table width="95%" cellspacing="1" cellpadding="4" border="0" align="center"><tr>
+						<td>
+				' . $this->sql_report . '</td>
+					</tr></table>
+					<br />
+					</body></html>
+				';
 				exit;
 				break;
 
@@ -153,25 +214,37 @@ class dbal
 				$endtime = explode(' ', microtime());
 				$endtime = $endtime[0] + $endtime[1];
 
-				$sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query #' . $this->num_queries . '</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table> ' . $html_hold . '<p align="center">';
+				$this->sql_report .= '
+					<hr width="100%"/><br />
+
+					<table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0">
+					<tr>
+						<th>Query #' . $this->num_queries . '</th>
+					</tr>
+					<tr>
+						<td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td>
+					</tr>
+					</table> ' . $this->html_hold . '
+					<p align="center">
+				';
 
 				if ($this->query_result)
 				{
 					if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query))
 					{
-						$sql_report .= "Affected rows: <b>" . $this->sql_affectedrows($this->query_result) . '</b> | ';
+						$this->sql_report .= 'Affected rows: <b>' . $this->sql_affectedrows($this->query_result) . '</b> | ';
 					}
-					$sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $curtime) . 's</b>';
+					$this->sql_report .= 'Before: ' . sprintf('%.5f', $this->curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $this->curtime) . 's</b>';
 				}
 				else
 				{
 					$error = $this->sql_error();
-					$sql_report .= '<b style="color: red">FAILED</b> - ' . SQL_LAYER . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
+					$this->sql_report .= '<b style="color: red">FAILED</b> - ' . SQL_LAYER . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
 				}
 
-				$sql_report .= '</p>';
+				$this->sql_report .= '</p>';
 
-				$this->sql_time += $endtime - $curtime;
+				$this->sql_time += $endtime - $this->curtime;
 				break;
 
 			default:
@@ -183,6 +256,8 @@ class dbal
 	}
 }
 
+/**
+*/
 if (!defined('IN_PHPBB'))
 {
 	exit;
diff --git a/phpBB/includes/db/mysql4.php b/phpBB/includes/db/mysql4.php
index df176db329..6d3438006f 100644
--- a/phpBB/includes/db/mysql4.php
+++ b/phpBB/includes/db/mysql4.php
@@ -20,11 +20,14 @@ if (!defined('SQL_LAYER'))
 /**
 * @package dbal
 * MySQL4 Database Abstraction Layer
-* Minimum Requirement is 4.0+/4.1+
+* Minimum Requirement is 4.0+ (4.1+ compatible)
 */
 class dbal_mysql4 extends dbal
 {
 
+	/**
+	* Connect to sql server
+	*/
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
 		$this->persistency = $persistency;
@@ -319,15 +322,12 @@ class dbal_mysql4 extends dbal
 	function _sql_report($mode, $query = '')
 	{
 		global $cache, $starttime, $phpbb_root_path;
-		static $curtime, $query_hold, $html_hold;
-		static $sql_report = '';
-		static $cache_num_queries = 0;
 
 		switch ($mode)
 		{
 			case 'start':
-				$query_hold = $query;
-				$html_hold = '';
+				$this->query_hold = $query;
+				$this->html_hold = '';
 
 				$explain_query = $query;
 				if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
@@ -341,7 +341,7 @@ class dbal_mysql4 extends dbal
 
 				if (preg_match('/^SELECT/', $explain_query))
 				{
-					$html_table = FALSE;
+					$html_table = false;
 
 					if ($result = mysql_query("EXPLAIN $explain_query", $this->db_connect_id))
 					{
@@ -349,35 +349,35 @@ class dbal_mysql4 extends dbal
 						{
 							if (!$html_table && sizeof($row))
 							{
-								$html_table = TRUE;
-								$html_hold .= '<table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0" align="center"><tr>';
+								$html_table = true;
+								$this->html_hold .= '<table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0" align="center"><tr>';
 								
 								foreach (array_keys($row) as $val)
 								{
-									$html_hold .= '<th nowrap="nowrap">' . (($val) ? ucwords(str_replace('_', ' ', $val)) : '&nbsp;') . '</th>';
+									$this->html_hold .= '<th nowrap="nowrap">' . (($val) ? ucwords(str_replace('_', ' ', $val)) : '&nbsp;') . '</th>';
 								}
-								$html_hold .= '</tr>';
+								$this->html_hold .= '</tr>';
 							}
-							$html_hold .= '<tr>';
+							$this->html_hold .= '<tr>';
 
 							$class = 'row1';
 							foreach (array_values($row) as $val)
 							{
 								$class = ($class == 'row1') ? 'row2' : 'row1';
-								$html_hold .= '<td class="' . $class . '">' . (($val) ? $val : '&nbsp;') . '</td>';
+								$this->html_hold .= '<td class="' . $class . '">' . (($val) ? $val : '&nbsp;') . '</td>';
 							}
-							$html_hold .= '</tr>';
+							$this->html_hold .= '</tr>';
 						}
 					}
 
 					if ($html_table)
 					{
-						$html_hold .= '</table>';
+						$this->html_hold .= '</table>';
 					}
 				}
 
-				$curtime = explode(' ', microtime());
-				$curtime = $curtime[0] + $curtime[1];
+				$this->curtime = explode(' ', microtime());
+				$this->curtime = $this->curtime[0] + $this->curtime[1];
 				break;
 
 			case 'fromcache':
@@ -392,19 +392,19 @@ class dbal_mysql4 extends dbal
 				$splittime = explode(' ', microtime());
 				$splittime = $splittime[0] + $splittime[1];
 
-				$time_cache = $endtime - $curtime;
+				$time_cache = $endtime - $this->curtime;
 				$time_db = $splittime - $endtime;
 				$color = ($time_db > $time_cache) ? 'green' : 'red';
 
-				$sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query results obtained from the cache</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table><p align="center">';
+				$this->sql_report .= '<hr width="100%"/><br /><table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0"><tr><th>Query results obtained from the cache</th></tr><tr><td class="row1"><textarea style="font-family:\'Courier New\',monospace;width:100%" rows="5">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></table><p align="center">';
 
-				$sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed [cache]: <b style="color: ' . $color . '">' . sprintf('%.5f', ($time_cache)) . 's</b> | Elapsed [db]: <b>' . sprintf('%.5f', $time_db) . 's</b></p>';
+				$this->sql_report .= 'Before: ' . sprintf('%.5f', $this->curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed [cache]: <b style="color: ' . $color . '">' . sprintf('%.5f', ($time_cache)) . 's</b> | Elapsed [db]: <b>' . sprintf('%.5f', $time_db) . 's</b></p>';
 
 				// Pad the start time to not interfere with page timing
 				$starttime += $time_db;
 
 				mysql_free_result($result);
-				$cache_num_queries++;
+				$this->cache_num_queries++;
 				break;
 		}
 	}
author	Meik Sievertsen <acydburn@phpbb.com>	2005-10-02 18:18:52 +0000
committer	Meik Sievertsen <acydburn@phpbb.com>	2005-10-02 18:18:52 +0000
commit	1d688c6b8de0073e637be7601e0efbd5947bce00 (patch)
tree	c9a3e8e11c224cf3ce0fdfde05e02e12ecdfb19c /phpBB/includes/db
parent	1a18e36684d74d5a0c2538805a3ff8436036189e (diff)
download	forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar.gz forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar.bz2 forums-1d688c6b8de0073e637be7601e0efbd5947bce00.tar.xz forums-1d688c6b8de0073e637be7601e0efbd5947bce00.zip