diff options
Diffstat (limited to 'phpBB')
| -rw-r--r-- | phpBB/includes/db/db2.php | 417 | ||||
| -rw-r--r-- | phpBB/includes/db/dbal.php | 121 | ||||
| -rw-r--r-- | phpBB/includes/db/firebird.php | 247 | ||||
| -rw-r--r-- | phpBB/includes/db/mssql.php | 195 | ||||
| -rw-r--r-- | phpBB/includes/db/mssql_odbc.php | 286 | ||||
| -rw-r--r-- | phpBB/includes/db/mysql.php | 203 | ||||
| -rw-r--r-- | phpBB/includes/db/mysql4.php | 193 | ||||
| -rw-r--r-- | phpBB/includes/db/mysqli.php | 221 | ||||
| -rw-r--r-- | phpBB/includes/db/oracle.php | 198 | ||||
| -rw-r--r-- | phpBB/includes/db/postgres.php | 248 | ||||
| -rw-r--r-- | phpBB/includes/db/sqlite.php | 145 | ||||
| -rw-r--r-- | phpBB/posting.php | 2 |
12 files changed, 903 insertions, 1573 deletions
diff --git a/phpBB/includes/db/db2.php b/phpBB/includes/db/db2.php deleted file mode 100644 index b248b82b35..0000000000 --- a/phpBB/includes/db/db2.php +++ /dev/null @@ -1,417 +0,0 @@ -<?php -/** -* -* @package dbal -* @version $Id$ -* @copyright (c) 2005 phpBB Group -* @license http://opensource.org/licenses/gpl-license.php GNU Public License -* -*/ - -/** -* @ignore -*/ -if(!defined("SQL_LAYER")) -{ - -define("SQL_LAYER","db2"); - -/** -* @package dbal -* DB2 Database Abstraction Layer - not finished yet -*/ -class dbal_db2 extends dbal -{ - - var $db_connect_id; - var $query_result; - var $query_resultset; - var $query_numrows; - var $next_id; - var $row = array(); - var $rowset = array(); - var $row_index; - var $num_queries = 0; - - // - // Constructor - // - function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true) - { - $this->persistency = $persistency; - $this->user = $sqluser; - $this->password = $sqlpassword; - $this->dbname = $database; - - $this->server = $sqlserver; - - if($this->persistency) - { - $this->db_connect_id = odbc_pconnect($this->server, "", ""); - } - else - { - $this->db_connect_id = odbc_connect($this->server, "", ""); - } - - if($this->db_connect_id) - { - @odbc_autocommit($this->db_connect_id, off); - - return $this->db_connect_id; - } - else - { - return false; - } - } - // - // Other base methods - // - function sql_close() - { - if($this->db_connect_id) - { - if($this->query_result) - { - @odbc_free_result($this->query_result); - } - $result = @odbc_close($this->db_connect_id); - return $result; - } - else - { - return false; - } - } - - - // - // Query method - // - function sql_query($query = "", $transaction = FALSE) - { - // - // Remove any pre-existing queries - // - unset($this->query_result); - unset($this->row); - if($query != "") - { - $this->num_queries++; - - if(!eregi("^INSERT ",$query)) - { - if(eregi("LIMIT", $query)) - { - preg_match("/^(.*)LIMIT ([0-9]+)[, ]*([0-9]+)*/s", $query, $limits); - - $query = $limits[1]; - if($limits[3]) - { - $row_offset = $limits[2]; - $num_rows = $limits[3]; - } - else - { - $row_offset = 0; - $num_rows = $limits[2]; - } - - $query .= " FETCH FIRST ".($row_offset+$num_rows)." ROWS ONLY OPTIMIZE FOR ".($row_offset+$num_rows)." ROWS"; - - $this->query_result = odbc_exec($this->db_connect_id, $query); - - $query_limit_offset = $row_offset; - $this->result_numrows[$this->query_result] = $num_rows; - } - else - { - $this->query_result = odbc_exec($this->db_connect_id, $query); - - $row_offset = 0; - $this->result_numrows[$this->query_result] = 5E6; - } - - $result_id = $this->query_result; - if($this->query_result && eregi("^SELECT", $query)) - { - - for($i = 1; $i < odbc_num_fields($result_id)+1; $i++) - { - $this->result_field_names[$result_id][] = odbc_field_name($result_id, $i); - } - - $i = $row_offset + 1; - $k = 0; - while(odbc_fetch_row($result_id, $i) && $k < $this->result_numrows[$result_id]) - { - - for($j = 1; $j < count($this->result_field_names[$result_id])+1; $j++) - { - $this->result_rowset[$result_id][$k][$this->result_field_names[$result_id][$j-1]] = odbc_result($result_id, $j); - } - $i++; - $k++; - } - - $this->result_numrows[$result_id] = $k; - $this->row_index[$result_id] = 0; - } - else - { - $this->result_numrows[$result_id] = @odbc_num_rows($result_id); - $this->row_index[$result_id] = 0; - } - } - else - { - if(eregi("^(INSERT|UPDATE) ", $query)) - { - $query = preg_replace("/\\\'/s", "''", $query); - } - - $this->query_result = odbc_exec($this->db_connect_id, $query); - - if($this->query_result) - { - $sql_id = "VALUES(IDENTITY_VAL_LOCAL())"; - - $id_result = odbc_exec($this->db_connect_id, $sql_id); - if($id_result) - { - $row_result = odbc_fetch_row($id_result); - if($row_result) - { - $this->next_id[$this->query_result] = odbc_result($id_result, 1); - } - } - } - - odbc_commit($this->db_connect_id); - - $this->query_limit_offset[$this->query_result] = 0; - $this->result_numrows[$this->query_result] = 0; - } - - return $this->query_result; - } - else - { - return false; - } - } - - // - // Other query methods - // - function sql_numrows($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - return $this->result_numrows[$query_id]; - } - else - { - return false; - } - } - function sql_affectedrows($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - return $this->result_numrows[$query_id]; - } - else - { - return false; - } - } - function sql_numfields($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - $result = count($this->result_field_names[$query_id]); - return $result; - } - else - { - return false; - } - } - function sql_fieldname($offset, $query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - $result = $this->result_field_names[$query_id][$offset]; - return $result; - } - else - { - return false; - } - } - function sql_fieldtype($offset, $query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - $result = @odbc_field_type($query_id, $offset); - return $result; - } - else - { - return false; - } - } - function sql_fetchrow($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - if($this->row_index[$query_id] < $this->result_numrows[$query_id]) - { - $result = $this->result_rowset[$query_id][$this->row_index[$query_id]]; - $this->row_index[$query_id]++; - return $result; - } - else - { - return false; - } - } - else - { - return false; - } - } - function sql_fetchrowset($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - $this->row_index[$query_id] = $this->result_numrows[$query_id]; - return $this->result_rowset[$query_id]; - } - else - { - return false; - } - } - function sql_fetchfield($field, $row = -1, $query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - if($row < $this->result_numrows[$query_id]) - { - if($row == -1) - { - $getrow = $this->row_index[$query_id]-1; - } - else - { - $getrow = $row; - } - - return $this->result_rowset[$query_id][$getrow][$this->result_field_names[$query_id][$field]]; - - } - else - { - return false; - } - } - else - { - return false; - } - } - function sql_rowseek($offset, $query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - $this->row_index[$query_id] = 0; - return true; - } - else - { - return false; - } - } - function sql_nextid($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - return $this->next_id[$query_id]; - } - else - { - return false; - } - } - function sql_freeresult($query_id = 0) - { - if(!$query_id) - { - $query_id = $this->query_result; - } - if($query_id) - { - $result = @odbc_free_result($query_id); - return $result; - } - else - { - return false; - } - } - function sql_error($query_id = 0) - { -// $result['code'] = @odbc_error($this->db_connect_id); -// $result['message'] = @odbc_errormsg($this->db_connect_id); - - return ""; - } - -} // class sql_db - -} // if ... define - -?>
\ No newline at end of file diff --git a/phpBB/includes/db/dbal.php b/phpBB/includes/db/dbal.php index f91f7ef469..7a7d2e2a00 100644 --- a/phpBB/includes/db/dbal.php +++ b/phpBB/includes/db/dbal.php @@ -11,6 +11,7 @@ /** * @package dbal * Database Abstraction Layer +* Minimum Requirement PHP 4.3.3 */ class dbal { @@ -46,6 +47,56 @@ class dbal } /** + * DBAL garbage collection, close sql connection + */ + function sql_close() + { + if (!$this->db_connect_id) + { + return false; + } + + if ($this->transaction) + { + $this->sql_transaction('commit'); + } + + if (sizeof($this->open_queries)) + { + foreach ($this->open_queries as $i_query_id => $query_id) + { + $this->sql_freeresult($query_id); + } + } + + return $this->_sql_close(); + } + + /** + * Fetch all rows + */ + function sql_fetchrowset($query_id = false) + { + if (!$query_id) + { + $query_id = $this->query_result; + } + + if ($query_id) + { + $result = array(); + while ($row = $this->sql_fetchrow($query_id)) + { + $result[] = $row; + } + + return $result; + } + + return false; + } + + /** * Build sql statement from array for insert/update/select statements * * Idea for this from Ikonboard @@ -136,7 +187,7 @@ class dbal */ function sql_error($sql = '') { - $error = $this->db_sql_error(); + $error = $this->_sql_error(); if (!$this->return_on_error) { @@ -245,7 +296,73 @@ class dbal $this->sql_report .= '</p>'; $this->sql_time += $endtime - $this->curtime; - break; + break; + + case 'start': + $this->query_hold = $query; + $this->html_hold = ''; + + $this->_sql_report($mode, $query); + + $this->curtime = explode(' ', microtime()); + $this->curtime = $this->curtime[0] + $this->curtime[1]; + + break; + + case 'add_select_row': + + $html_table = func_get_arg(2); + $row = func_get_arg(3); + + if (!$html_table && sizeof($row)) + { + $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) + { + $this->html_hold .= '<th nowrap="nowrap">' . (($val) ? ucwords(str_replace('_', ' ', $val)) : ' ') . '</th>'; + } + $this->html_hold .= '</tr>'; + } + $this->html_hold .= '<tr>'; + + $class = 'row1'; + foreach (array_values($row) as $val) + { + $class = ($class == 'row1') ? 'row2' : 'row1'; + $this->html_hold .= '<td class="' . $class . '">' . (($val) ? $val : ' ') . '</td>'; + } + $this->html_hold .= '</tr>'; + + return $html_table; + + break; + + case 'fromcache': + + $this->_sql_report($mode, $query); + + $this->cache_num_queries++; + + break; + + case 'record_fromcache': + + $endtime = func_get_arg(2); + $splittime = func_get_arg(3); + + $time_cache = $endtime - $this->curtime; + $time_db = $splittime - $endtime; + $color = ($time_db > $time_cache) ? 'green' : 'red'; + + $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">'; + $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; + + break; default: diff --git a/phpBB/includes/db/firebird.php b/phpBB/includes/db/firebird.php index 797772e5e3..7f57f33ff2 100644 --- a/phpBB/includes/db/firebird.php +++ b/phpBB/includes/db/firebird.php @@ -26,6 +26,9 @@ class dbal_firebird extends dbal { var $last_query_text = ''; + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -38,32 +41,9 @@ class dbal_firebird extends dbal return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if ($this->transaction) - { - @ibase_commit($this->db_connect_id); - } - - if (sizeof($this->open_queries)) - { - foreach ($this->open_queries as $i_query_id => $query_id) - { - @ibase_free_query($query_id); - } - } - - return @ibase_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -94,7 +74,9 @@ class dbal_firebird extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -121,8 +103,13 @@ class dbal_firebird extends dbal if ($cache_ttl && method_exists($cache, 'sql_save')) { + $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); } + else if (strpos($query, 'SELECT') !== false && $this->query_result) + { + $this->open_queries[(int) $this->query_result] = $this->query_result; + } } } else @@ -133,6 +120,9 @@ class dbal_firebird extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -149,21 +139,34 @@ class dbal_firebird extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { return false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { - // TODO: hmm, maybe doing something similar as in mssql_odbc.php? - return ($this->query_result) ? true : false; + // PHP 5+ function + if (function_exists('ibase_affected_rows')) + { + return ($this->query_result) ? @ibase_affected_rows($this->query_result) : false; + } + else + { + return ($this->query_result) ? true : false; + } } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -190,10 +193,15 @@ class dbal_firebird extends dbal { $row[strtolower($key)] = trim(str_replace("\\0", "\0", str_replace("\\n", "\n", $value))); } - return ($query_id) ? $row : false; + + return (sizeof($row)) ? $row : false; } - function sql_fetchrowset($query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -202,80 +210,41 @@ class dbal_firebird extends dbal if ($query_id) { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = get_object_vars(@ibase_fetch_object($query_id, IBASE_TEXT))) + if ($rownum !== false) { - $result[] = $this->rowset[$query_id]; + $this->sql_rowseek($rownum, $query_id); } - - return $result; - } - else - { - return false; + + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } + + return false; } - function sql_fetchfield($field, $rownum = -1, $query_id = 0) + /** + * Seek to given row number + * rownum is zero-based + */ + function sql_rowseek($rownum, $query_id = false) { if (!$query_id) { $query_id = $this->query_result; } - if ($query_id) - { - if ($rownum > -1) - { - // erm... ok, my bad, we always use zero. :/ - for ($i = 0; $i <= $rownum; $i++) - { - $row = $this->sql_fetchrow($query_id); - } +/* + $this->sql_freeresult($query_id); + $query_id = $this->sql_query($this->last_query_text); - return $row[$field]; - } - else - { - if (empty($this->row[$query_id]) && empty($this->rowset[$query_id])) - { - if ($this->row[$query_id] = $this->sql_fetchrow($query_id)) - { - $result = $this->row[$query_id][$field]; - } - } - else - { - if ($this->rowset[$query_id]) - { - $result = $this->rowset[$query_id][$field]; - } - else if ($this->row[$query_id]) - { - $result = $this->row[$query_id][$field]; - } - } - } - - return $result; - } - else - { - return false; - } - } - - function sql_rowseek($rownum, $query_id = 0) - { if (!$query_id) { - $query_id = $this->query_result; + return false; } +*/ - for($i = 1; $i < $rownum; $i++) + // We do not fetch the row for rownum == 0 because then the next resultset would be the second row + for ($i = 0; $i < $rownum; $i++) { if (!$this->sql_fetchrow($query_id)) { @@ -286,24 +255,37 @@ class dbal_firebird extends dbal return true; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { - if ($this->query_result && preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename)) + $query_id = $this->query_result; + + if ($query_id && $this->last_query_text != '') { - $query = "SELECT GEN_ID('" . $tablename[1] . "_gen', 0) AS new_id - FROM RDB\$DATABASE"; - if (!($temp_q_id = @ibase_query($this->db_connect_id, $query))) + if ($this->query_result && preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename)) { - return false; - } + $query = "SELECT GEN_ID('" . $tablename[1] . "_gen', 0) AS new_id + FROM RDB\$DATABASE"; + if (!($temp_q_id = @ibase_query($this->db_connect_id, $query))) + { + return false; + } - $temp_result = @ibase_fetch_object($temp_q_id); - $this->sql_freeresult($temp_q_id); + $temp_result = @ibase_fetch_object($temp_q_id); + @ibase_free_result($temp_q_id); - return ($temp_result) ? $temp_result->last_value : false; + return ($temp_result) ? $temp_result->last_value : false; + } } + + return false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -313,18 +295,31 @@ class dbal_firebird extends dbal if (!$this->transaction && $query_id) { - @ibase_commit(); + $this->sql_transaction('commit'); + } + + if (isset($this->open_queries[(int) $query_id])) + { + unset($this->open_queries[(int) $query_id]); + return @ibase_free_result($query_id); } - return ($query_id) ? @ibase_free_result($query_id) : false; + return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return (@ini_get('magic_quotes_sybase') || strtolower(@ini_get('magic_quotes_sybase')) == 'on') ? str_replace('\\\'', '\'', addslashes($msg)) : str_replace('\'', '\'\'', stripslashes($msg)); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @ibase_errmsg(), @@ -332,22 +327,25 @@ class dbal_firebird extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @ibase_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; - - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); @@ -358,23 +356,14 @@ class dbal_firebird extends dbal { // Take the time spent on parsing rows into account } + @ibase_freeresult($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $curtime; - $time_db = $splittime - $endtime; - $color = ($time_db > $time_cache) ? 'green' : 'red'; + $this->sql_report('record_fromcache', $query, $endtime, $splittime); - $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>'; - - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - @ibase_freeresult($result); - $cache_num_queries++; - break; + break; } } diff --git a/phpBB/includes/db/mssql.php b/phpBB/includes/db/mssql.php index cb6b5123c0..636ce1e90c 100644 --- a/phpBB/includes/db/mssql.php +++ b/phpBB/includes/db/mssql.php @@ -24,7 +24,9 @@ if (!defined('SQL_LAYER')) */ class dbal_mssql extends dbal { - + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -46,29 +48,9 @@ class dbal_mssql extends dbal return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); } - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if ($this->transaction) - { - @mssql_query('COMMIT', $this->db_connect_id); - } - - if (sizeof($this->open_queries)) - { - foreach ($this->open_queries as $i_query_id => $query_id) - { - @mssql_free_result($query_id); - } - } - - return @mssql_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -100,7 +82,9 @@ class dbal_mssql extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -133,7 +117,6 @@ class dbal_mssql extends dbal { $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); - // sql_freeresult called within sql_save() } else if (strpos($query, 'SELECT') !== false && $this->query_result) { @@ -153,6 +136,9 @@ class dbal_mssql extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -178,10 +164,10 @@ class dbal_mssql extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -189,15 +175,20 @@ class dbal_mssql extends dbal $query_id = $this->query_result; } -// return (isset($this->limit_offset[$query_id])) ? @mssql_num_rows($query_id) - $this->limit_offset[$query_id] : @mssql_num_rows($query_id); return ($query_id) ? @mssql_num_rows($query_id) : false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { return ($this->db_connect_id) ? @mssql_rows_affected($this->db_connect_id) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -214,6 +205,7 @@ class dbal_mssql extends dbal $row = @mssql_fetch_array($query_id, MSSQL_ASSOC); + // I hope i am able to remove this later... hopefully only a PHP or MSSQL bug if ($row) { foreach ($row as $key => $value) @@ -225,30 +217,11 @@ class dbal_mssql extends dbal return $row; } - function sql_fetchrowset($query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - if ($query_id) - { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) - { - $result[] = $this->rowset[$query_id]; - } - return $result; - } - - return false; - } - - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -257,41 +230,22 @@ class dbal_mssql extends dbal if ($query_id) { - if ($rownum > -1) + if ($rownum !== false) { -// (!empty($this->limit_offset[$query_id])) ? @mssql_data_seek($query_id, ($this->limit_offset[$query_id] + $rownum)) : @mssql_data_seek($query_id, $rownum); - @mssql_data_seek($query_id, $rownum); - $row = @mssql_fetch_array($query_id, MSSQL_ASSOC); - $result = isset($row[$field]) ? $row[$field] : false; + $this->sql_rowseek($rownum, $query_id); } - else - { - if (empty($this->row[$query_id]) && empty($this->rowset[$query_id])) - { - if ($this->row[$query_id] = $this->sql_fetchrow($query_id)) - { - $result = $this->row[$query_id][$field]; - } - } - else - { - if ($this->rowset[$query_id]) - { - $result = $this->rowset[$query_id][$field]; - } - elseif ($this->row[$query_id]) - { - $result = $this->row[$query_id][$field]; - } - } - } - - return $result; + + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } - + return false; } + /** + * Seek to given row number + * rownum is zero-based + */ function sql_rowseek($rownum, $query_id = false) { if (!$query_id) @@ -299,16 +253,12 @@ class dbal_mssql extends dbal $query_id = $this->query_result; } - if (isset($this->current_row[$query_id])) - { -// (!empty($this->limit_offset[$query_id])) ? @mssql_data_seek($query_id, ($this->limit_offset[$query_id] + $rownum)) : @mssql_data_seek($query_id, $rownum); - @mssql_data_seek($query_id, $rownum); - return true; - } - - return false; + return ($query_id) ? @mssql_data_seek($query_id, $rownum) : false; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { $result_id = @mssql_query('SELECT @@IDENTITY', $this->db_connect_id); @@ -316,13 +266,19 @@ class dbal_mssql extends dbal { if (@mssql_fetch_array($result_id, MSSQL_ASSOC)) { - return @mssql_result($result_id, 1); + $id = @mssql_result($result_id, 1); + @mssql_free_result($result_id); + return $id; } + @mssql_free_result($result_id); } return false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -333,20 +289,25 @@ class dbal_mssql extends dbal if (isset($this->open_queries[$query_id])) { unset($this->open_queries[$query_id]); - unset($this->result_rowset[$query_id]); - return @mssql_free_result($query_id); } return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return str_replace("'", "''", str_replace('\\', '\\\\', $msg)); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @mssql_get_last_message($this->db_connect_id), @@ -354,22 +315,25 @@ class dbal_mssql extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @mssql_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; - - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); @@ -380,23 +344,14 @@ class dbal_mssql extends dbal { // Take the time spent on parsing rows into account } + @mssql_free_result($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $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">'; - - $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('record_fromcache', $query, $endtime, $splittime); - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - @mssql_free_result($result); - $cache_num_queries++; - break; + break; } } diff --git a/phpBB/includes/db/mssql_odbc.php b/phpBB/includes/db/mssql_odbc.php index cb2b05c937..e9b2a9b03e 100644 --- a/phpBB/includes/db/mssql_odbc.php +++ b/phpBB/includes/db/mssql_odbc.php @@ -19,17 +19,17 @@ if (!defined('SQL_LAYER')) /** * @package dbal -* MSSQL ODBC Database Abstraction Layer for MSSQL -* Minimum Requirement is Version 2000+ +* Unified ODBC functions +* Unified ODBC functions support any database having ODBC driver, for example Adabas D, IBM DB2, iODBC, Solid, Sybase SQL Anywhere... +* Here we only support MSSQL Server 2000+ because of the provided schema */ class dbal_mssql_odbc extends dbal { - var $result_rowset = array(); - var $field_names = array(); - var $field_types = array(); - var $num_rows = array(); - var $current_row = array(); + var $last_query_text = ''; + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -42,41 +42,9 @@ class dbal_mssql_odbc extends dbal return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if ($this->transaction) - { - @odbc_commit($this->db_connect_id); - } - - if (sizeof($this->result_rowset)) - { - unset($this->result_rowset); - unset($this->field_names); - unset($this->field_types); - unset($this->num_rows); - unset($this->current_row); - } - - if (sizeof($this->open_queries)) - { - foreach ($this->open_queries as $i_query_id => $query_id) - { - @odbc_free_result($query_id); - } - } - - return @odbc_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -111,7 +79,9 @@ class dbal_mssql_odbc extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -124,13 +94,14 @@ class dbal_mssql_odbc extends dbal $this->sql_report('start', $query); } + $this->last_query_text = $query; $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; if (!$this->query_result) { $this->num_queries++; - if (($this->query_result = $this->_odbc_execute_query($query)) === false) + if (($this->query_result = @odbc_exec($this->db_connect_id, $query)) === false) { $this->sql_error($query); } @@ -144,7 +115,6 @@ class dbal_mssql_odbc extends dbal { $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); - // odbc_free_result called within sql_save() } else if (strpos($query, 'SELECT') !== false && $this->query_result) { @@ -164,76 +134,9 @@ class dbal_mssql_odbc extends dbal return ($this->query_result) ? $this->query_result : false; } - function _odbc_execute_query($query) - { - $result = false; - - if (eregi("^SELECT ", $query)) - { - $result = @odbc_exec($this->db_connect_id, $query); - - if ($result) - { - if (empty($this->field_names[$result])) - { - for ($i = 1, $j = @odbc_num_fields($result) + 1; $i < $j; $i++) - { - $this->field_names[$result][] = @odbc_field_name($result, $i); - $this->field_types[$result][] = @odbc_field_type($result, $i); - } - } - - $this->current_row[$result] = 0; - $this->result_rowset[$result] = array(); - - $row_outer = (isset($row_offset)) ? $row_offset + 1 : 1; - $row_outer_max = (isset($num_rows)) ? $row_offset + $num_rows + 1 : 1E9; - $row_inner = 0; - - while (@odbc_fetch_row($result, $row_outer) && $row_outer < $row_outer_max) - { - for ($i = 0, $j = sizeof($this->field_names[$result]); $i < $j; $i++) - { - $this->result_rowset[$result][$row_inner][$this->field_names[$result][$i]] = stripslashes(@odbc_result($result, $i + 1)); - } - - $row_outer++; - $row_inner++; - } - - $this->num_rows[$result] = sizeof($this->result_rowset[$result]); - } - } - else if (eregi("^INSERT ", $query)) - { - $result = @odbc_exec($this->db_connect_id, $query); - - if ($result) - { - $result_id = @odbc_exec($this->db_connect_id, 'SELECT @@IDENTITY'); - if ($result_id) - { - if (@odbc_fetch_row($result_id)) - { - $this->next_id[$this->db_connect_id] = @odbc_result($result_id, 1); - $this->affected_rows[$this->db_connect_id] = @odbc_num_rows($result); - } - } - } - } - else - { - $result = @odbc_exec($this->db_connect_id, $query); - - if ($result) - { - $this->affected_rows[$this->db_connect_id] = @odbc_num_rows($result); - } - } - - return $result; - } - + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -259,10 +162,10 @@ class dbal_mssql_odbc extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -270,14 +173,20 @@ class dbal_mssql_odbc extends dbal $query_id = $this->query_result; } - return ($query_id) ? @$this->num_rows($query_id) : false; + return ($query_id) ? @odbc_num_rows($query_id) : false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { - return ($this->affected_rows[$this->db_connect_id]) ? $this->affected_rows[$this->db_connect_id] : false; + return ($this->db_connect_id) ? @odbc_num_rows($this->query_result) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -291,21 +200,15 @@ class dbal_mssql_odbc extends dbal { return $cache->sql_fetchrow($query_id); } - - return ($this->num_rows[$query_id] && $this->current_row[$query_id] < $this->num_rows[$query_id]) ? $this->result_rowset[$query_id][$this->current_row[$query_id]++] : false; - } - - function sql_fetchrowset($query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - return ($this->num_rows[$query_id]) ? $this->result_rowset[$query_id] : false; + + return ($query_id) ? @odbc_fetch_array($result_id) : false; } - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -314,17 +217,22 @@ class dbal_mssql_odbc extends dbal if ($query_id) { - if ($rownum < $this->num_rows[$query_id]) + if ($rownum !== false) { - $getrow = ($rownum == -1) ? $this->current_row[$query_id] - 1 : $rownum; - - return $this->result_rowset[$query_id][$getrow][$this->field_names[$query_id][$field]]; + $this->sql_rowseek($rownum, $query_id); } + + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } return false; } + /** + * Seek to given row number + * rownum is zero-based + */ function sql_rowseek($rownum, $query_id = false) { if (!$query_id) @@ -332,20 +240,50 @@ class dbal_mssql_odbc extends dbal $query_id = $this->query_result; } - if (isset($this->current_row[$query_id])) + $this->sql_freeresult($query_id); + $query_id = $this->sql_query($this->last_query_text); + + if (!$query_id) + { + return false; + } + + // We do not fetch the row for rownum == 0 because then the next resultset would be the second row + for ($i = 0; $i < $rownum; $i++) { - $this->current_row[$query_id] = $rownum; - return true; + if (!$this->sql_fetchrow($query_id)) + { + return false; + } } - return false; + return true; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { - return ($this->next_id[$this->db_connect_id]) ? $this->next_id[$this->db_connect_id] : false; + $result_id = @odbc_exec($this->db_connect_id, 'SELECT @@IDENTITY'); + + if ($result_id) + { + if (@odbc_fetch_array($result_id)) + { + $id = @odbc_result($result_id, 1); + @odbc_free_result($result_id); + return $id; + } + @odbc_free_result($result_id); + } + + return false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -356,24 +294,25 @@ class dbal_mssql_odbc extends dbal if (isset($this->open_queries[(int) $query_id])) { unset($this->open_queries[(int) $query_id]); - unset($this->num_rows[$query_id]); - unset($this->current_row[$query_id]); - unset($this->result_rowset[$query_id]); - unset($this->field_names[$query_id]); - unset($this->field_types[$query_id]); - return @odbc_free_result($query_id); } return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return str_replace("'", "''", str_replace('\\', '\\\\', $msg)); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @odbc_errormsg(), @@ -381,46 +320,43 @@ class dbal_mssql_odbc extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @odbc_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; - - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); $endtime = $endtime[0] + $endtime[1]; - $result = $this->_odbc_execute_query($query); + $result = @odbc_exec($this->db_connect_id, $query); + while ($void = @odbc_fetch_array($result)) + { + // Take the time spent on parsing rows into account + } + @odbc_free_result($result); $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $curtime; - $time_db = $splittime - $endtime; - $color = ($time_db > $time_cache) ? 'green' : 'red'; + $this->sql_report('record_fromcache', $query, $endtime, $splittime); - $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>'; - - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - @odbc_free_result($result); - $cache_num_queries++; - break; + break; } } diff --git a/phpBB/includes/db/mysql.php b/phpBB/includes/db/mysql.php index caf4300421..182ed52fdd 100644 --- a/phpBB/includes/db/mysql.php +++ b/phpBB/includes/db/mysql.php @@ -24,7 +24,10 @@ if (!defined('SQL_LAYER')) */ class dbal_mysql extends dbal { - + /** + * Connect to server + * @public + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -45,27 +48,9 @@ class dbal_mysql extends dbal return $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if (sizeof($this->open_queries)) - { - foreach ($this->open_queries as $i_query_id => $query_id) - { - @mysql_free_result($query_id); - } - } - - return @mysql_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -97,7 +82,9 @@ class dbal_mysql extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -130,7 +117,6 @@ class dbal_mysql extends dbal { $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); - // mysql_free_result called within sql_save() } else if (strpos($query, 'SELECT') !== false && $this->query_result) { @@ -150,6 +136,9 @@ class dbal_mysql extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -172,10 +161,10 @@ class dbal_mysql extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -186,11 +175,17 @@ class dbal_mysql extends dbal return ($query_id) ? @mysql_num_rows($query_id) : false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { return ($this->db_connect_id) ? @mysql_affected_rows($this->db_connect_id) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -208,30 +203,11 @@ class dbal_mysql extends dbal return ($query_id) ? @mysql_fetch_assoc($query_id) : false; } - function sql_fetchrowset($query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - if ($query_id) - { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) - { - $result[] = $this->rowset[$query_id]; - } - return $result; - } - - return false; - } - - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -240,38 +216,24 @@ class dbal_mysql extends dbal if ($query_id) { - if ($rownum > -1) + if ($rownum === false) { - $result = @mysql_result($query_id, $rownum, $field); + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } else { - if (empty($this->row[$query_id]) && empty($this->rowset[$query_id])) - { - if ($this->row[$query_id] = $this->sql_fetchrow($query_id)) - { - $result = $this->row[$query_id][$field]; - } - } - else - { - if ($this->rowset[$query_id]) - { - $result = $this->rowset[$query_id][$field]; - } - elseif ($this->row[$query_id]) - { - $result = $this->row[$query_id][$field]; - } - } + return @mysql_result($query_id, $rownum, $field); } - - return $result; } return false; } + /** + * Seek to given row number + * rownum is zero-based + */ function sql_rowseek($rownum, $query_id = false) { if (!$query_id) @@ -282,11 +244,17 @@ class dbal_mysql extends dbal return ($query_id) ? @mysql_data_seek($query_id, $rownum) : false; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { return ($this->db_connect_id) ? @mysql_insert_id($this->db_connect_id) : false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -303,12 +271,19 @@ class dbal_mysql extends dbal return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return mysql_escape_string($msg); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @mysql_error(), @@ -316,98 +291,76 @@ class dbal_mysql extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @mysql_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; $explain_query = $query; if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) { $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; } - elseif (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) + else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) { $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; } if (preg_match('/^SELECT/', $explain_query)) { - $html_table = FALSE; + $html_table = false; - if ($result = mysql_query("EXPLAIN $explain_query", $this->db_connect_id)) + if ($result = @mysql_query("EXPLAIN $explain_query", $this->db_connect_id)) { - while ($row = mysql_fetch_assoc($result)) + while ($row = @mysql_fetch_assoc($result)) { - if (!$html_table && sizeof($row)) - { - $html_table = TRUE; - $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)) : ' ') . '</th>'; - } - $html_hold .= '</tr>'; - } - $html_hold .= '<tr>'; - - $class = 'row1'; - foreach (array_values($row) as $val) - { - $class = ($class == 'row1') ? 'row2' : 'row1'; - $html_hold .= '<td class="' . $class . '">' . (($val) ? $val : ' ') . '</td>'; - } - $html_hold .= '</tr>'; + $html_table = $this->sql_report('add_select_row', $query, $html_table, $row); } } + @mysql_free_result($result); if ($html_table) { - $html_hold .= '</table>'; + $this->html_hold .= '</table>'; } } - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); $endtime = $endtime[0] + $endtime[1]; - $result = mysql_query($query, $this->db_connect_id); - while ($void = mysql_fetch_assoc($result)) + $result = @mysql_query($query, $this->db_connect_id); + while ($void = @mysql_fetch_assoc($result)) { // Take the time spent on parsing rows into account } + @mysql_free_result($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $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">'; - - $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('record_fromcache', $query, $endtime, $splittime); - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - mysql_free_result($result); - $cache_num_queries++; - break; + break; } } + } } // if ... define diff --git a/phpBB/includes/db/mysql4.php b/phpBB/includes/db/mysql4.php index 6d3438006f..bde53beab6 100644 --- a/phpBB/includes/db/mysql4.php +++ b/phpBB/includes/db/mysql4.php @@ -24,9 +24,8 @@ if (!defined('SQL_LAYER')) */ class dbal_mysql4 extends dbal { - /** - * Connect to sql server + * Connect to server */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { @@ -48,27 +47,9 @@ class dbal_mysql4 extends dbal return $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if (sizeof($this->open_queries)) - { - foreach ($this->open_queries as $i_query_id => $query_id) - { - @mysql_free_result($query_id); - } - } - - return @mysql_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -100,7 +81,9 @@ class dbal_mysql4 extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -133,7 +116,6 @@ class dbal_mysql4 extends dbal { $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); - // mysql_free_result called within sql_save() } else if (strpos($query, 'SELECT') !== false && $this->query_result) { @@ -153,6 +135,9 @@ class dbal_mysql4 extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -175,10 +160,10 @@ class dbal_mysql4 extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -189,11 +174,17 @@ class dbal_mysql4 extends dbal return ($query_id) ? @mysql_num_rows($query_id) : false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { return ($this->db_connect_id) ? @mysql_affected_rows($this->db_connect_id) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -211,30 +202,11 @@ class dbal_mysql4 extends dbal return ($query_id) ? @mysql_fetch_assoc($query_id) : false; } - function sql_fetchrowset($query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - if ($query_id) - { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) - { - $result[] = $this->rowset[$query_id]; - } - return $result; - } - - return false; - } - - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -243,38 +215,24 @@ class dbal_mysql4 extends dbal if ($query_id) { - if ($rownum > -1) + if ($rownum === false) { - $result = @mysql_result($query_id, $rownum, $field); + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } else { - if (empty($this->row[$query_id]) && empty($this->rowset[$query_id])) - { - if ($this->row[$query_id] = $this->sql_fetchrow($query_id)) - { - $result = $this->row[$query_id][$field]; - } - } - else - { - if ($this->rowset[$query_id]) - { - $result = $this->rowset[$query_id][$field]; - } - elseif ($this->row[$query_id]) - { - $result = $this->row[$query_id][$field]; - } - } + return @mysql_result($query_id, $rownum, $field); } - - return $result; } return false; } + /** + * Seek to given row number + * rownum is zero-based + */ function sql_rowseek($rownum, $query_id = false) { if (!$query_id) @@ -285,11 +243,17 @@ class dbal_mysql4 extends dbal return ($query_id) ? @mysql_data_seek($query_id, $rownum) : false; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { return ($this->db_connect_id) ? @mysql_insert_id($this->db_connect_id) : false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -306,12 +270,19 @@ class dbal_mysql4 extends dbal return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return mysql_escape_string($msg); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @mysql_error(), @@ -319,22 +290,31 @@ class dbal_mysql4 extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; + return @mysql_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $this->query_hold = $query; - $this->html_hold = ''; $explain_query = $query; if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) { $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; } - elseif (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) + else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) { $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; } @@ -343,32 +323,14 @@ class dbal_mysql4 extends dbal { $html_table = false; - if ($result = mysql_query("EXPLAIN $explain_query", $this->db_connect_id)) + if ($result = @mysql_query("EXPLAIN $explain_query", $this->db_connect_id)) { - while ($row = mysql_fetch_assoc($result)) + while ($row = @mysql_fetch_assoc($result)) { - if (!$html_table && sizeof($row)) - { - $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) - { - $this->html_hold .= '<th nowrap="nowrap">' . (($val) ? ucwords(str_replace('_', ' ', $val)) : ' ') . '</th>'; - } - $this->html_hold .= '</tr>'; - } - $this->html_hold .= '<tr>'; - - $class = 'row1'; - foreach (array_values($row) as $val) - { - $class = ($class == 'row1') ? 'row2' : 'row1'; - $this->html_hold .= '<td class="' . $class . '">' . (($val) ? $val : ' ') . '</td>'; - } - $this->html_hold .= '</tr>'; + $html_table = $this->sql_report('add_select_row', $query, $html_table, $row); } } + @mysql_free_result($result); if ($html_table) { @@ -376,36 +338,25 @@ class dbal_mysql4 extends dbal } } - $this->curtime = explode(' ', microtime()); - $this->curtime = $this->curtime[0] + $this->curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); $endtime = $endtime[0] + $endtime[1]; - $result = mysql_query($query, $this->db_connect_id); - while ($void = mysql_fetch_assoc($result)) + $result = @mysql_query($query, $this->db_connect_id); + while ($void = @mysql_fetch_assoc($result)) { // Take the time spent on parsing rows into account } + @mysql_free_result($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $this->curtime; - $time_db = $splittime - $endtime; - $color = ($time_db > $time_cache) ? 'green' : 'red'; + $this->sql_report('record_fromcache', $query, $endtime, $splittime); - $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">'; - - $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); - $this->cache_num_queries++; - break; + break; } } } diff --git a/phpBB/includes/db/mysqli.php b/phpBB/includes/db/mysqli.php index 9dcb5ec7c0..0b10d01679 100644 --- a/phpBB/includes/db/mysqli.php +++ b/phpBB/includes/db/mysqli.php @@ -26,6 +26,9 @@ class dbal_mysqli extends dbal { var $indexed = 0; + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -46,24 +49,9 @@ class dbal_mysqli extends dbal return $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if ($this->transaction) - { - @mysqli_commit($this->db_connect_id); - } - - return @mysqli_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -98,7 +86,9 @@ class dbal_mysqli extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -122,11 +112,6 @@ class dbal_mysqli extends dbal $this->sql_error($query); } - if (is_object($this->query_result)) - { - $this->query_result->cur_index = $this->indexed++; - } - if (defined('DEBUG_EXTRA')) { $this->sql_report('stop', $query); @@ -134,8 +119,13 @@ class dbal_mysqli extends dbal if ($cache_ttl && method_exists($cache, 'sql_save')) { + $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); } + else if (strpos($query, 'SELECT') !== false && $this->query_result) + { + $this->open_queries[(int) $this->query_result] = $this->query_result; + } } else if (defined('DEBUG_EXTRA')) { @@ -150,6 +140,9 @@ class dbal_mysqli extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -172,10 +165,10 @@ class dbal_mysqli extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -186,11 +179,17 @@ class dbal_mysqli extends dbal return ($query_id) ? @mysqli_num_rows($query_id) : false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -208,7 +207,11 @@ class dbal_mysqli extends dbal return ($query_id) ? @mysqli_fetch_assoc($query_id) : false; } - function sql_fetchrowset($query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -217,65 +220,22 @@ class dbal_mysqli extends dbal if ($query_id) { - $cur_index = (is_object($query_id)) ? $query_id->cur_index : $query_id; - - unset($this->rowset[$cur_index]); - unset($this->row[$cur_index]); - - $result = array(); - while ($this->rowset[$cur_index] = $this->sql_fetchrow($query_id)) + if ($rownum !== false) { - $result[] = $this->rowset[$cur_index]; + $this->sql_rowseek($rownum, $query_id); } - return $result; + + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } return false; } - function sql_fetchfield($field, $rownum = -1, $query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - if ($query_id) - { - if ($rownum > -1) - { - @mysqli_data_seek($query_id, $rownum); - $row = @mysqli_fetch_assoc($query_id); - $result = isset($row[$field]) ? $row[$field] : false; - } - else - { - $cur_index = (is_object($query_id)) ? $query_id->cur_index : $query_id; - - if (empty($this->row[$cur_index]) && empty($this->rowset[$cur_index])) - { - if ($this->row[$cur_index] = $this->sql_fetchrow($query_id)) - { - $result = $this->row[$cur_index][$field]; - } - } - else - { - if ($this->rowset[$cur_index]) - { - $result = $this->rowset[$cur_index][$field]; - } - elseif ($this->row[$cur_index]) - { - $result = $this->row[$cur_index][$field]; - } - } - } - return $result; - } - return false; - } - + /** + * Seek to given row number + * rownum is zero-based + */ function sql_rowseek($rownum, $query_id = false) { if (!$query_id) @@ -286,11 +246,17 @@ class dbal_mysqli extends dbal return ($query_id) ? @mysqli_data_seek($query_id, $rownum) : false; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -298,28 +264,28 @@ class dbal_mysqli extends dbal $query_id = $this->query_result; } - $cur_index = (is_object($query_id)) ? $query_id->cur_index : $query_id; - - unset($this->rowset[$cur_index]); - unset($this->row[$cur_index]); - - if (is_object($query_id)) + if (isset($this->open_queries[(int) $query_id])) { - $this->indexed--; + unset($this->open_queries[(int) $query_id]); return @mysqli_free_result($query_id); } - else - { - return false; - } + + return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return @mysqli_real_escape_string($this->db_connect_id, $msg); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @mysqli_error($this->db_connect_id), @@ -327,69 +293,55 @@ class dbal_mysqli extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @mysqli_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; $explain_query = $query; if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) { $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; } - elseif (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) + else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m)) { $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2]; } if (preg_match('/^SELECT/', $explain_query)) { - $html_table = FALSE; + $html_table = false; if ($result = @mysqli_query($this->db_connect_id, "EXPLAIN $explain_query")) { while ($row = @mysqli_fetch_assoc($result)) { - if (!$html_table && sizeof($row)) - { - $html_table = TRUE; - $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)) : ' ') . '</th>'; - } - $html_hold .= '</tr>'; - } - $html_hold .= '<tr>'; - - $class = 'row1'; - foreach (array_values($row) as $val) - { - $class = ($class == 'row1') ? 'row2' : 'row1'; - $html_hold .= '<td class="' . $class . '">' . (($val) ? $val : ' ') . '</td>'; - } - $html_hold .= '</tr>'; + $html_table = $this->sql_report('add_select_row', $query, $html_table, $row); } } + @mysqli_free_result($result); if ($html_table) { - $html_hold .= '</table>'; + $this->html_hold .= '</table>'; } } - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); @@ -400,23 +352,14 @@ class dbal_mysqli extends dbal { // Take the time spent on parsing rows into account } + @mysqli_free_result($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $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('record_fromcache', $query, $endtime, $splittime); - $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>'; - - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - @mysqli_free_result($result); - $cache_num_queries++; - break; + break; } } } diff --git a/phpBB/includes/db/oracle.php b/phpBB/includes/db/oracle.php index e281140b73..d51c928f5e 100644 --- a/phpBB/includes/db/oracle.php +++ b/phpBB/includes/db/oracle.php @@ -25,6 +25,9 @@ class dbal_oracle extends dbal { var $last_query_text = ''; + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -37,32 +40,9 @@ class dbal_oracle extends dbal return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if ($this->transaction) - { - @ocicommit($this->db_connect_id); - } - - if (sizeof($this->open_queries)) - { - foreach ($this->open_queries as $i_query_id => $query_id) - { - @ocifreestatement($query_id); - } - } - - return @ocilogoff($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -94,7 +74,9 @@ class dbal_oracle extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -168,6 +150,9 @@ class dbal_oracle extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -184,10 +169,10 @@ class dbal_oracle extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -196,6 +181,7 @@ class dbal_oracle extends dbal } $result = @ocifetchstatement($query_id, $this->rowset); + // OCIFetchStatment kills our query result so we have to execute the statment again // if we ever want to use the query_id again. @ociexecute($query_id, OCI_DEFAULT); @@ -203,13 +189,17 @@ class dbal_oracle extends dbal return $result; } + /** + * Return number of affected rows + */ function sql_affectedrows() { - $query_id = $this->query_result; - - return ($query_id) ? @ocirowcount($query_id) : false; + return ($this->query_result) ? @ocirowcount($this->query_result) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -243,12 +233,15 @@ class dbal_oracle extends dbal $result_row[strtolower($key)] = $value; } - $this->row[$query_id] = $result_row; - return $this->row[$query_id]; + return ($query_id) ? $result_row : false; } - function sql_fetchrowset($query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -257,78 +250,52 @@ class dbal_oracle extends dbal if ($query_id) { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) + if ($rownum !== false) { - $result[] = $this->rowset[$query_id]; + $this->sql_rowseek($rownum, $query_id); } - return $result; + + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } return false; } - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Seek to given row number + * rownum is zero-based + */ + function sql_rowseek($rownum, $query_id = false) { if (!$query_id) { $query_id = $this->query_result; } - if ($query_id) - { - if ($rownum > -1) - { - // Reset the internal rownum pointer. - @ociexecute($query_id, OCI_DEFAULT); - for ($i = 0; $i < $rownum; $i++) - { - // Move the interal pointer to the row we want - @ocifetch($query_id); - } - - // Get the field data. - $result = @ociresult($query_id, strtoupper($field)); - } - else - { - // The internal pointer should be where we want it - // so we just grab the field out of the current row. - $result = @ociresult($query_id, strtoupper($field)); - } - - return $result; - } - - return false; - } - - function sql_rowseek($rownum, $query_id = false) - { if (!$query_id) { - $query_id = $this->query_result; + return false; } - if ($query_id) - { - @ociexecute($query_id, OCI_DEFAULT); + // Reset internal pointer + @ociexecute($query_id, OCI_DEFAULT); - for ($i = 0; $i < $rownum; $i++) + // We do not fetch the row for rownum == 0 because then the next resultset would be the second row + for ($i = 0; $i < $rownum; $i++) + { + if (!$this->sql_fetchrow($query_id)) { - @ocifetch($query_id); + return false; } - $result = @ocifetch($query_id); - - return $result; } - - return false; + + return true; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { $query_id = $this->query_result; @@ -339,9 +306,10 @@ class dbal_oracle extends dbal { $query = 'SELECT ' . $tablename[1] . '_id_seq.currval FROM DUAL'; $stmt = @ociparse($this->db_connect_id, $query); - @ociexecute($stmt,OCI_DEFAULT ); + @ociexecute($stmt, OCI_DEFAULT ); $temp_result = @ocifetchinto($stmt, $temp_result, OCI_ASSOC + OCI_RETURN_NULLS); + @ocifreestatement($stmt); if ($temp_result) { @@ -357,6 +325,9 @@ class dbal_oracle extends dbal return false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) @@ -373,12 +344,19 @@ class dbal_oracle extends dbal return false; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { return str_replace("'", "''", str_replace('\\', '\\\\', $msg)); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { $error = @ocierror(); $error = (!$error) ? @ocierror($this->query_result) : $error; @@ -396,22 +374,25 @@ class dbal_oracle extends dbal return $error; } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @ocilogoff($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; - - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); @@ -421,27 +402,18 @@ class dbal_oracle extends dbal $success = @ociexecute($result, OCI_DEFAULT); $row = array(); - while ($void = @ocifetchinto($query_id, $row, OCI_ASSOC + OCI_RETURN_NULLS)) + while (@ocifetchinto($query_id, $row, OCI_ASSOC + OCI_RETURN_NULLS)) { // Take the time spent on parsing rows into account } + @ocifreestatement($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $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">'; - - $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('record_fromcache', $query, $endtime, $splittime); - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - @ocifreestatement($result); - $cache_num_queries++; - break; + break; } } diff --git a/phpBB/includes/db/postgres.php b/phpBB/includes/db/postgres.php index f16ecff2c6..72590d6330 100644 --- a/phpBB/includes/db/postgres.php +++ b/phpBB/includes/db/postgres.php @@ -24,7 +24,11 @@ if (!defined('SQL_LAYER')) */ class dbal_postgres extends dbal { - + var $last_query_text = ''; + + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->connect_string = ''; @@ -73,24 +77,9 @@ class dbal_postgres extends dbal return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - if ($this->transaction) - { - @pg_exec($this->db_connect_id, 'COMMIT'); - } - - return @pg_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -122,7 +111,9 @@ class dbal_postgres extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -135,12 +126,12 @@ class dbal_postgres extends dbal $this->sql_report('start', $query); } + $this->last_query_text = $query; $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; if (!$this->query_result) { $this->num_queries++; - $this->last_query_text = $query; if (($this->query_result = @pg_exec($this->db_connect_id, $query)) === false) { @@ -154,8 +145,13 @@ class dbal_postgres extends dbal if ($cache_ttl && method_exists($cache, 'sql_save')) { + $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); } + else if (strpos($query, 'SELECT') !== false && $this->query_result) + { + $this->open_queries[(int) $this->query_result] = $this->query_result; + } } else if (defined('DEBUG_EXTRA')) { @@ -170,6 +166,9 @@ class dbal_postgres extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -192,10 +191,10 @@ class dbal_postgres extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -206,16 +205,17 @@ class dbal_postgres extends dbal return ($query_id) ? @pg_numrows($query_id) : false; } - function sql_affectedrows($query_id = false) + /** + * Return number of affected rows + */ + function sql_affectedrows() { - if (!$query_id) - { - $query_id = $this->query_result; - } - - return ($query_id) ? @pg_cmdtuples($query_id) : false; + return ($this->query_result) ? @pg_cmdtuples($this->query_result) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -225,134 +225,56 @@ class dbal_postgres extends dbal $query_id = $this->query_result; } - if (!isset($this->rownum[$query_id])) - { - $this->rownum[$query_id] = 0; - } - if (isset($cache->sql_rowset[$query_id])) { return $cache->sql_fetchrow($query_id); } - $result = @pg_fetch_array($query_id, NULL, PGSQL_ASSOC); - - if ($result) - { - $this->rownum[$query_id]++; - } - - return $result; + return ($query_id) ? @pg_fetch_array($query_id, NULL, PGSQL_ASSOC) : false; } - function sql_fetchrowset($query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { $query_id = $this->query_result; } - $result = array(); - if ($query_id) { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) + if ($rownum !== false) { - $result[] = $this->rowset[$query_id]; + $this->sql_rowseek($rownum, $query_id); } - return $result; + + $row = $this->sql_fetchrow($query_id); + return isset($row[$field]) ? $row[$field] : false; } return false; } - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Seek to given row number + * rownum is zero-based + */ + function sql_rowseek($rownum, $query_id = false) { if (!$query_id) { $query_id = $this->query_result; } - if ($query_id) - { - if ($rownum > -1) - { - if (@function_exists('pg_result_seek')) - { - @pg_result_seek($query_id, $rownum); - $row = @pg_fetch_assoc($query_id); - $result = isset($row[$field]) ? $row[$field] : false; - } - else - { - $this->sql_rowseek($offset, $query_id); - $row = $this->sql_fetchrow($query_id); - $result = isset($row[$field]) ? $row[$field] : false; - } - } - else - { - if (empty($this->row[$query_id]) && empty($this->rowset[$query_id])) - { - if ($this->row[$query_id] = $this->sql_fetchrow($query_id)) - { - $result = $this->row[$query_id][$field]; - } - } - else - { - if ($this->rowset[$query_id]) - { - $result = $this->rowset[$query_id][$field]; - } - elseif ($this->row[$query_id]) - { - $result = $this->row[$query_id][$field]; - } - } - } - return $result; - } - return false; - } - - function sql_rowseek($offset, $query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - if ($query_id) - { - if ($offset > -1) - { - if (@function_exists('pg_result_seek')) - { - @pg_result_seek($query_id, $rownum); - } - else - { - for ($i = $this->rownum[$query_id]; $i < $offset; $i++) - { - $this->sql_fetchrow($query_id); - } - } - return true; - } - else - { - return false; - } - } - - return false; + return ($query_id) ? @pg_result_seek($query_id, $rownum) : false; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { $query_id = $this->query_result; @@ -369,6 +291,7 @@ class dbal_postgres extends dbal } $temp_result = @pg_fetch_array($temp_q_id, NULL, PGSQL_ASSOC); + @pg_freeresult($query_id); return ($temp_result) ? $temp_result['last_value'] : false; } @@ -377,22 +300,37 @@ class dbal_postgres extends dbal return false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { if (!$query_id) { $query_id = $this->query_result; } - - return (is_resource($query_id)) ? @pg_freeresult($query_id) : false; + + if (isset($this->open_queries[(int) $query_id])) + { + unset($this->open_queries[(int) $query_id]); + return @pg_freeresult($query_id); + } } + /** + * Escape string used in sql query + */ function sql_escape($msg) { - return str_replace("'", "''", str_replace('\\', '\\\\', $msg)); + // Do not use for bytea values + return pg_escape_string($msg); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @pg_errormessage(), @@ -400,22 +338,25 @@ class dbal_postgres extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @pg_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; - - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); @@ -426,23 +367,14 @@ class dbal_postgres extends dbal { // Take the time spent on parsing rows into account } + @pg_freeresult($result); + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $curtime; - $time_db = $splittime - $endtime; - $color = ($time_db > $time_cache) ? 'green' : 'red'; + $this->sql_report('record_fromcache', $query, $endtime, $splittime); - $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>'; - - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - @pg_freeresult($result); - $cache_num_queries++; - break; + break; } } diff --git a/phpBB/includes/db/sqlite.php b/phpBB/includes/db/sqlite.php index 1b20822284..4c8edeb4d5 100644 --- a/phpBB/includes/db/sqlite.php +++ b/phpBB/includes/db/sqlite.php @@ -23,7 +23,9 @@ if (!defined('SQL_LAYER')) */ class dbal_sqlite extends dbal { - + /** + * Connect to server + */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; @@ -42,19 +44,9 @@ class dbal_sqlite extends dbal return ($this->db_connect_id) ? true : array('message' => $error); } - // - // Other base methods - // - function sql_close() - { - if (!$this->db_connect_id) - { - return false; - } - - return @sqlite_close($this->db_connect_id); - } - + /** + * sql transaction + */ function sql_transaction($status = 'begin') { switch ($status) @@ -86,7 +78,9 @@ class dbal_sqlite extends dbal return $result; } - // Base query method + /** + * Base query method + */ function sql_query($query = '', $cache_ttl = 0) { if ($query != '') @@ -119,8 +113,13 @@ class dbal_sqlite extends dbal if ($cache_ttl && method_exists($cache, 'sql_save')) { + $this->open_queries[(int) $this->query_result] = $this->query_result; $cache->sql_save($query, $this->query_result, $cache_ttl); } + else if (strpos($query, 'SELECT') !== false && $this->query_result) + { + $this->open_queries[(int) $this->query_result] = $this->query_result; + } } else if (defined('DEBUG_EXTRA')) { @@ -135,6 +134,9 @@ class dbal_sqlite extends dbal return ($this->query_result) ? $this->query_result : false; } + /** + * Build LIMIT query + */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { if ($query != '') @@ -157,10 +159,10 @@ class dbal_sqlite extends dbal } } - // Other query methods - // - // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ... - // don't want this here by a middle Milestone + /** + * Return number of rows + * Not used within core code + */ function sql_numrows($query_id = false) { if (!$query_id) @@ -171,11 +173,17 @@ class dbal_sqlite extends dbal return ($query_id) ? @sqlite_num_rows($query_id) : false; } + /** + * Return number of affected rows + */ function sql_affectedrows() { return ($this->db_connect_id) ? @sqlite_changes($this->db_connect_id) : false; } + /** + * Fetch current row + */ function sql_fetchrow($query_id = false) { global $cache; @@ -193,30 +201,11 @@ class dbal_sqlite extends dbal return ($query_id) ? @sqlite_fetch_array($query_id, SQLITE_ASSOC) : false; } - function sql_fetchrowset($query_id = false) - { - if (!$query_id) - { - $query_id = $this->query_result; - } - - if ($query_id) - { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - - $result = array(); - while ($this->rowset[$query_id] = $this->sql_fetchrow($query_id)) - { - $result[] = $this->rowset[$query_id]; - } - return $result; - } - - return false; - } - - function sql_fetchfield($field, $rownum = -1, $query_id = false) + /** + * Fetch field + * if rownum is false, the current row is used, else it is pointing to the row (zero-based) + */ + function sql_fetchfield($field, $rownum = false, $query_id = false) { if (!$query_id) { @@ -225,21 +214,24 @@ class dbal_sqlite extends dbal if ($query_id) { - if ($rownum > -1) + if ($rownum === false) { - $result = (@sqlite_seek($query_id, $rownum)) ? @sqlite_column($query_id, $field) : false; + return @sqlite_column($query_id, $field); } else { - $result = @sqlite_column($query_id, $field); + @sqlite_seek($query_id, $rownum); + return @sqlite_column($query_id, $field); } - - return $result; } return false; } + /** + * Seek to given row number + * rownum is zero-based + */ function sql_rowseek($rownum, $query_id = false) { if (!$query_id) @@ -250,22 +242,35 @@ class dbal_sqlite extends dbal return ($query_id) ? @sqlite_seek($query_id, $rownum) : false; } + /** + * Get last inserted id after insert statement + */ function sql_nextid() { return ($this->db_connect_id) ? @sqlite_last_insert_rowid($this->db_connect_id) : false; } + /** + * Free sql result + */ function sql_freeresult($query_id = false) { return true; } + /** + * Escape string used in sql query + */ function sql_escape($msg) { - return @sqlite_escape_string(stripslashes($msg)); + return @sqlite_escape_string($msg); } - function db_sql_error() + /** + * return sql error array + * @private + */ + function _sql_error() { return array( 'message' => @sqlite_error_string(@sqlite_last_error($this->db_connect_id)), @@ -273,22 +278,25 @@ class dbal_sqlite extends dbal ); } - function _sql_report($mode, $query = '') + /** + * Close sql connection + * @private + */ + function _sql_close() { - global $cache, $starttime, $phpbb_root_path; - static $curtime, $query_hold, $html_hold; - static $sql_report = ''; - static $cache_num_queries = 0; + return @sqlite_close($this->db_connect_id); + } + /** + * Build db-specific report + * @private + */ + function _sql_report($mode, $query = '') + { switch ($mode) { case 'start': - $query_hold = $query; - $html_hold = ''; - - $curtime = explode(' ', microtime()); - $curtime = $curtime[0] + $curtime[1]; - break; + break; case 'fromcache': $endtime = explode(' ', microtime()); @@ -299,22 +307,13 @@ class dbal_sqlite extends dbal { // Take the time spent on parsing rows into account } + $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; - $time_cache = $endtime - $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">'; - - $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('record_fromcache', $query, $endtime, $splittime); - // Pad the start time to not interfere with page timing - $starttime += $time_db; - - $cache_num_queries++; - break; + break; } } diff --git a/phpBB/posting.php b/phpBB/posting.php index 81556660e6..c79757809a 100644 --- a/phpBB/posting.php +++ b/phpBB/posting.php @@ -1662,7 +1662,7 @@ function submit_post($mode, $subject, $username, $topic_type, &$poll, &$data, $u case 'mysql': case 'mysql4': case 'mysqli': - $db->sql_query('INSERT INTO ' . POLL_OPTIONS_TABLE . ' ' . $db->sql_build_array('MULTI_INSERT', $sql_insert_ary); + $db->sql_query('INSERT INTO ' . POLL_OPTIONS_TABLE . ' ' . $db->sql_build_array('MULTI_INSERT', $sql_insert_ary)); break; default: |