]> projects.mako.cc - scuttle/blob - includes/db/mysqli.php
updated readme with information on a series of bugs I know exist
[scuttle] / includes / db / mysqli.php
1 <?php
2 /** 
3 *
4 * @package dbal_mysqli
5 * @version $Id: mysqli.php,v 1.4 2006/02/10 01:30:19 scronide Exp $
6 * @copyright (c) 2005 phpBB Group 
7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License 
8 *
9 */
10
11 /**
12 * @ignore
13 */
14 if (!defined('SQL_LAYER'))
15 {
16
17 define('SQL_LAYER', 'mysqli');
18
19 /**
20 * @package dbal_mysqli
21 * MySQLi Database Abstraction Layer
22 * Minimum Requirement is MySQL 4.1+ and the mysqli-extension
23 */
24 class sql_db
25 {
26         var $db_connect_id;
27         var $query_result;
28         var $return_on_error = false;
29         var $transaction = false;
30         var $sql_time = 0;
31         var $num_queries = 0;
32         var $open_queries = array();
33
34         var $indexed = 0;
35
36         function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
37         {
38                 $this->persistency = $persistency;
39                 $this->user = $sqluser;
40                 $this->server = $sqlserver . (($port) ? ':' . $port : '');
41                 $this->dbname = $database;
42
43                 $this->db_connect_id = ($this->persistency) ? @mysqli_pconnect($this->server, $this->user, $sqlpassword) : @mysqli_connect($this->server, $this->user, $sqlpassword);
44
45                 if ($this->db_connect_id && $this->dbname != '')
46                 {
47                         if (@mysqli_select_db($this->db_connect_id, $this->dbname))
48                         {
49                                 //Set utf-8
50                                 mysqli_query($this->db_connect_id, 'SET CHARACTER SET utf8');
51                                 return $this->db_connect_id;
52                         }
53                 }
54
55                 return $this->sql_error('');
56         }
57
58         //
59         // Other base methods
60         //
61         function sql_close()
62         {
63                 if (!$this->db_connect_id)
64                 {
65                         return false;
66                 }
67
68                 if ($this->transaction)
69                 {
70                         @mysqli_commit($this->db_connect_id);
71                 }
72
73                 return @mysqli_close($this->db_connect_id);
74         }
75
76         function sql_return_on_error($fail = false)
77         {
78                 $this->return_on_error = $fail;
79         }
80
81         function sql_num_queries()
82         {
83                 return $this->num_queries;
84         }
85
86         function sql_transaction($status = 'begin')
87         {
88                 switch ($status)
89                 {
90                         case 'begin':
91                                 $result = @mysqli_autocommit($this->db_connect_id, false);
92                                 $this->transaction = true;
93                                 break;
94
95                         case 'commit':
96                                 $result = @mysqli_commit($this->db_connect_id);
97                                 @mysqli_autocommit($this->db_connect_id, true);
98                                 $this->transaction = false;
99
100                                 if (!$result)
101                                 {
102                                         @mysqli_rollback($this->db_connect_id);
103                                         @mysqli_autocommit($this->db_connect_id, true);
104                                 }
105                                 break;
106
107                         case 'rollback':
108                                 $result = @mysqli_rollback($this->db_connect_id);
109                                 @mysqli_autocommit($this->db_connect_id, true);
110                                 $this->transaction = false;
111                                 break;
112
113                         default:
114                                 $result = true;
115                 }
116
117                 return $result;
118         }
119
120         // Base query method
121         function sql_query($query = '', $cache_ttl = 0)
122         {
123                 if ($query != '')
124                 {
125                         global $cache;
126
127                         // EXPLAIN only in extra debug mode
128                         if (defined('DEBUG_EXTRA'))
129                         {
130                                 $this->sql_report('start', $query);
131                         }
132
133                         $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
134                         
135                         if (!$this->query_result)
136                         {
137                                 $this->num_queries++;
138
139                                 if (($this->query_result = @mysqli_query($this->db_connect_id, $query)) === false)
140                                 {
141                                         $this->sql_error($query);
142                                 }
143
144                                 if (is_object($this->query_result))
145                                 {
146                                         $this->query_result->cur_index = $this->indexed++;
147                                 }
148
149                                 if (defined('DEBUG_EXTRA'))
150                                 {
151                                         $this->sql_report('stop', $query);
152                                 }
153
154                                 if ($cache_ttl && method_exists($cache, 'sql_save'))
155                                 {
156                                         $cache->sql_save($query, $this->query_result, $cache_ttl);
157                                 }
158                         }
159                         else if (defined('DEBUG_EXTRA'))
160                         {
161                                 $this->sql_report('fromcache', $query);
162                         }
163                 }
164                 else
165                 {
166                         return false;
167                 }
168
169                 return ($this->query_result) ? $this->query_result : false;
170         }
171
172         function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) { 
173                 if ($query != '') {
174             $this->query_result = false; 
175
176                         // only limit the number of rows if $total is greater than 0
177                         if ($total > 0)
178                         $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
179
180                         return $this->sql_query($query, $cache_ttl); 
181                 } else { 
182             return false; 
183                 } 
184         }
185
186         // Idea for this from Ikonboard
187         function sql_build_array($query, $assoc_ary = false)
188         {
189                 if (!is_array($assoc_ary))
190                 {
191                         return false;
192                 }
193
194                 $fields = array();
195                 $values = array();
196                 if ($query == 'INSERT')
197                 {
198                         foreach ($assoc_ary as $key => $var)
199                         {
200                                 $fields[] = $key;
201
202                                 if (is_null($var))
203                                 {
204                                         $values[] = 'NULL';
205                                 }
206                                 elseif (is_string($var))
207                                 {
208                                         $values[] = "'" . $this->sql_escape($var) . "'";
209                                 }
210                                 else
211                                 {
212                                         $values[] = (is_bool($var)) ? intval($var) : $var;
213                                 }
214                         }
215
216                         $query = ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
217                 }
218                 else if ($query == 'UPDATE' || $query == 'SELECT')
219                 {
220                         $values = array();
221                         foreach ($assoc_ary as $key => $var)
222                         {
223                                 if (is_null($var))
224                                 {
225                                         $values[] = "$key = NULL";
226                                 }
227                                 elseif (is_string($var))
228                                 {
229                                         $values[] = "$key = '" . $this->sql_escape($var) . "'";
230                                 }
231                                 else
232                                 {
233                                         $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
234                                 }
235                         }
236                         $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
237                 }
238
239                 return $query;
240         }
241
242         // Other query methods
243         //
244         // NOTE :: Want to remove _ALL_ reliance on sql_numrows from core code ...
245         //         don't want this here by a middle Milestone
246         function sql_numrows($query_id = false)
247         {
248                 if (!$query_id)
249                 {
250                         $query_id = $this->query_result;
251                 }
252
253                 return ($query_id) ? @mysqli_num_rows($query_id) : false;
254         }
255
256         function sql_affectedrows()
257         {
258                 return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false;
259         }
260
261         function sql_fetchrow($query_id = false)
262         {
263                 global $cache;
264
265                 if (!$query_id)
266                 {
267                         $query_id = $this->query_result;
268                 }
269
270                 if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
271                 {
272                         return $cache->sql_fetchrow($query_id);
273                 }
274
275                 return ($query_id) ? @mysqli_fetch_assoc($query_id) : false;
276         }
277
278         function sql_fetchrowset($query_id = false)
279         {
280                 if (!$query_id)
281                 {
282                         $query_id = $this->query_result;
283                 }
284
285                 if ($query_id)
286                 {
287                         $cur_index = (is_object($query_id)) ? $query_id->cur_index : $query_id;
288
289                         unset($this->rowset[$cur_index]);
290                         unset($this->row[$cur_index]);
291                         
292                         $result = array();
293                         while ($this->rowset[$cur_index] = $this->sql_fetchrow($query_id))
294                         {
295                                 $result[] = $this->rowset[$cur_index];
296                         }
297                         return $result;
298                 }
299
300                 return false;
301         }
302
303         function sql_fetchfield($field, $rownum = -1, $query_id = false)
304         {
305                 if (!$query_id)
306                 {
307                         $query_id = $this->query_result;
308                 }
309
310                 if ($query_id)
311                 {
312                         if ($rownum > -1)
313                         {
314                                 @mysqli_data_seek($query_id, $rownum);
315                                 $row = @mysqli_fetch_row($query_id);
316                                 $result = isset($row[$field]) ? $row[$field] : false;
317                         }
318                         else
319                         {
320                                 $cur_index = (is_object($query_id)) ? $query_id->cur_index : $query_id;
321         
322                                 if (empty($this->row[$cur_index]) && empty($this->rowset[$cur_index]))
323                                 {
324                                         if ($this->row[$cur_index] = $this->sql_fetchrow($query_id))
325                                         {
326                                                 $result = $this->row[$cur_index][$field];
327                                         }
328                                 }
329                                 else
330                                 {
331                                         if ($this->rowset[$cur_index])
332                                         {
333                                                 $result = $this->rowset[$cur_index][$field];
334                                         }
335                                         elseif ($this->row[$cur_index])
336                                         {
337                                                 $result = $this->row[$cur_index][$field];
338                                         }
339                                 }
340                         }
341                         return $result;
342                 }
343                 return false;
344         }
345
346         function sql_rowseek($rownum, $query_id = false)
347         {
348                 if (!$query_id)
349                 {
350                         $query_id = $this->query_result;
351                 }
352
353                 return ($query_id) ? @mysqli_data_seek($query_id, $rownum) : false;
354         }
355
356         function sql_nextid()
357         {
358                 return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false;
359         }
360
361         function sql_freeresult($query_id = false)
362         {
363                 if (!$query_id)
364                 {
365                         $query_id = $this->query_result;
366                 }
367
368                 $cur_index = (is_object($query_id)) ? $query_id->cur_index : $query_id;
369
370                 unset($this->rowset[$cur_index]);
371                 unset($this->row[$cur_index]);
372
373                 if (is_object($query_id))
374                 {
375                         $this->indexed--;
376                         return @mysqli_free_result($query_id);
377                 }
378                 else
379                 {
380                         return false;
381                 }
382         }
383
384         function sql_escape($msg) {
385                 if (function_exists('mysqli_real_escape_string')) {
386                         return @mysqli_real_escape_string($this->db_connect_id, $msg);
387                 } else {
388                         return mysqli_escape_string($this->db_connect_id, $msg);
389                 }               
390         }
391         
392         function sql_error($sql = '')
393         {
394                 if (!$this->return_on_error)
395                 {
396                         $this_page = (isset($_SERVER['PHP_SELF']) && !empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF'];
397                         $this_page .= '&' . ((isset($_SERVER['QUERY_STRING']) && !empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : (isset($_ENV['QUERY_STRING']) ? $_ENV['QUERY_STRING'] : ''));
398
399                         $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @mysqli_error($this->db_connect_id) . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
400
401                         if ($this->transaction)
402                         {
403                                 $this->sql_transaction('rollback');
404                         }
405
406                         trigger_error($message, E_USER_ERROR);
407                 }
408
409                 $result = array(
410                         'message'       => @mysqli_error($this->db_connect_id),
411                         'code'          => @mysqli_errno($this->db_connect_id)
412                 );
413
414                 return $result;
415         }
416
417         function sql_report($mode, $query = '')
418         {
419                 if (empty($_GET['explain']))
420                 {
421                         return;
422                 }
423
424                 global $db, $cache, $starttime, $phpbb_root_path;
425                 static $curtime, $query_hold, $html_hold;
426                 static $sql_report = '';
427                 static $cache_num_queries = 0;
428
429                 if (!$query && !empty($query_hold))
430                 {
431                         $query = $query_hold;
432                 }
433
434                 switch ($mode)
435                 {
436                         case 'display':
437                                 if (!empty($cache))
438                                 {
439                                         $cache->unload();
440                                 }
441                                 $db->sql_close();
442
443                                 $mtime = explode(' ', microtime());
444                                 $totaltime = $mtime[0] + $mtime[1] - $starttime;
445
446                                 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";
447                                 echo 'th { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic3.gif\') }' . "\n";
448                                 echo 'td.cat    { background-image: url(\'' . $phpbb_root_path . 'adm/images/cellpic1.gif\') }' . "\n";
449                                 echo '</style><title>' . $msg_title . '</title></head><body>';
450                                 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>';
451                                 echo $sql_report;
452                                 echo '</td></tr></table><br /></body></html>';
453                                 exit;
454                                 break;
455
456                         case 'start':
457                                 $query_hold = $query;
458                                 $html_hold = '';
459
460                                 $explain_query = $query;
461                                 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
462                                 {
463                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
464                                 }
465                                 elseif (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
466                                 {
467                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
468                                 }
469
470                                 if (preg_match('/^SELECT/', $explain_query))
471                                 {
472                                         $html_table = FALSE;
473
474                                         if ($result = @mysqli_query($this->db_connect_id, "EXPLAIN $explain_query"))
475                                         {
476                                                 while ($row = @mysqli_fetch_assoc($result))
477                                                 {
478                                                         if (!$html_table && sizeof($row))
479                                                         {
480                                                                 $html_table = TRUE;
481                                                                 $html_hold .= '<table class="bg" width="100%" cellspacing="1" cellpadding="4" border="0" align="center"><tr>';
482                                                                 
483                                                                 foreach (array_keys($row) as $val)
484                                                                 {
485                                                                         $html_hold .= '<th nowrap="nowrap">' . (($val) ? ucwords(str_replace('_', ' ', $val)) : '&nbsp;') . '</th>';
486                                                                 }
487                                                                 $html_hold .= '</tr>';
488                                                         }
489                                                         $html_hold .= '<tr>';
490
491                                                         $class = 'row1';
492                                                         foreach (array_values($row) as $val)
493                                                         {
494                                                                 $class = ($class == 'row1') ? 'row2' : 'row1';
495                                                                 $html_hold .= '<td class="' . $class . '">' . (($val) ? $val : '&nbsp;') . '</td>';
496                                                         }
497                                                         $html_hold .= '</tr>';
498                                                 }
499                                         }
500
501                                         if ($html_table)
502                                         {
503                                                 $html_hold .= '</table>';
504                                         }
505                                 }
506
507                                 $curtime = explode(' ', microtime());
508                                 $curtime = $curtime[0] + $curtime[1];
509                                 break;
510
511                         case 'fromcache':
512                                 $endtime = explode(' ', microtime());
513                                 $endtime = $endtime[0] + $endtime[1];
514
515                                 $result = @mysqli_query($this->db_connect_id, $query);
516                                 while ($void = @mysqli_fetch_assoc($result))
517                                 {
518                                         // Take the time spent on parsing rows into account
519                                 }
520                                 $splittime = explode(' ', microtime());
521                                 $splittime = $splittime[0] + $splittime[1];
522
523                                 $time_cache = $endtime - $curtime;
524                                 $time_db = $splittime - $endtime;
525                                 $color = ($time_db > $time_cache) ? 'green' : 'red';
526
527                                 $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">';
528
529                                 $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>';
530
531                                 // Pad the start time to not interfere with page timing
532                                 $starttime += $time_db;
533
534                                 @mysqli_free_result($result);
535                                 $cache_num_queries++;
536                                 break;
537
538                         case 'stop':
539                                 $endtime = explode(' ', microtime());
540                                 $endtime = $endtime[0] + $endtime[1];
541
542                                 $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">';
543
544                                 if ($this->query_result)
545                                 {
546                                         if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query))
547                                         {
548                                                 $sql_report .= "Affected rows: <b>" . $this->sql_affectedrows($this->query_result) . '</b> | ';
549                                         }
550                                         $sql_report .= 'Before: ' . sprintf('%.5f', $curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $curtime) . 's</b>';
551                                 }
552                                 else
553                                 {
554                                         $error = $this->sql_error();
555                                         $sql_report .= '<b style="color: red">FAILED</b> - MySQL Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
556                                 }
557
558                                 $sql_report .= '</p>';
559
560                                 $this->sql_time += $endtime - $curtime;
561                                 break;
562                 }
563         }
564 } // class sql_db
565
566 } // if ... define
567
568 ?>

Benjamin Mako Hill || Want to submit a patch?