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

Benjamin Mako Hill || Want to submit a patch?