Verzeichnisstruktur phpBB-3.0.0
- Veröffentlicht
- 12.12.2007
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
dbal.php
001 <?php
002 /**
003 *
004 * @package dbal
005 * @version $Id$
006 * @copyright (c) 2005 phpBB Group
007 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
008 *
009 */
010
011 /**
012 * @ignore
013 */
014 if (!defined('IN_PHPBB'))
015 {
016 exit;
017 }
018
019 /**
020 * Database Abstraction Layer
021 * @package dbal
022 */
023 class dbal
024 {
025 var $db_connect_id;
026 var $query_result;
027 var $return_on_error = false;
028 var $transaction = false;
029 var $sql_time = 0;
030 var $num_queries = array();
031 var $open_queries = array();
032
033 var $curtime = 0;
034 var $query_hold = '';
035 var $html_hold = '';
036 var $sql_report = '';
037
038 var $persistency = false;
039 var $user = '';
040 var $server = '';
041 var $dbname = '';
042
043 // Set to true if error triggered
044 var $sql_error_triggered = false;
045
046 // Holding the last sql query on sql error
047 var $sql_error_sql = '';
048
049 // Holding transaction count
050 var $transactions = 0;
051
052 // Supports multi inserts?
053 var $multi_insert = false;
054
055 /**
056 * Current sql layer
057 */
058 var $sql_layer = '';
059
060 /**
061 * Wildcards for matching any (%) or exactly one (_) character within LIKE expressions
062 */
063 var $any_char;
064 var $one_char;
065
066 /**
067 * Constructor
068 */
069 function dbal()
070 {
071 $this->num_queries = array(
072 'cached' => 0,
073 'normal' => 0,
074 'total' => 0,
075 );
076
077 // Fill default sql layer based on the class being called.
078 // This can be changed by the specified layer itself later if needed.
079 $this->sql_layer = substr(get_class($this), 5);
080
081 // Do not change this please! This variable is used to easy the use of it - and is hardcoded.
082 $this->any_char = chr(0) . '%';
083 $this->one_char = chr(0) . '_';
084 }
085
086 /**
087 * return on error or display error message
088 */
089 function sql_return_on_error($fail = false)
090 {
091 $this->sql_error_triggered = false;
092 $this->sql_error_sql = '';
093
094 $this->return_on_error = $fail;
095 }
096
097 /**
098 * Return number of sql queries and cached sql queries used
099 */
100 function sql_num_queries($cached = false)
101 {
102 return ($cached) ? $this->num_queries['cached'] : $this->num_queries['normal'];
103 }
104
105 /**
106 * Add to query count
107 */
108 function sql_add_num_queries($cached = false)
109 {
110 $this->num_queries['cached'] += ($cached !== false) ? 1 : 0;
111 $this->num_queries['normal'] += ($cached !== false) ? 0 : 1;
112 $this->num_queries['total'] += 1;
113 }
114
115 /**
116 * DBAL garbage collection, close sql connection
117 */
118 function sql_close()
119 {
120 if (!$this->db_connect_id)
121 {
122 return false;
123 }
124
125 if ($this->transaction)
126 {
127 do
128 {
129 $this->sql_transaction('commit');
130 }
131 while ($this->transaction);
132 }
133
134 foreach ($this->open_queries as $query_id)
135 {
136 $this->sql_freeresult($query_id);
137 }
138
139 return $this->_sql_close();
140 }
141
142 /**
143 * Build LIMIT query
144 * Doing some validation here.
145 */
146 function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
147 {
148 if (empty($query))
149 {
150 return false;
151 }
152
153 // Never use a negative total or offset
154 $total = ($total < 0) ? 0 : $total;
155 $offset = ($offset < 0) ? 0 : $offset;
156
157 return $this->_sql_query_limit($query, $total, $offset, $cache_ttl);
158 }
159
160 /**
161 * Fetch all rows
162 */
163 function sql_fetchrowset($query_id = false)
164 {
165 if ($query_id === false)
166 {
167 $query_id = $this->query_result;
168 }
169
170 if ($query_id !== false)
171 {
172 $result = array();
173 while ($row = $this->sql_fetchrow($query_id))
174 {
175 $result[] = $row;
176 }
177
178 return $result;
179 }
180
181 return false;
182 }
183
184 /**
185 * Fetch field
186 * if rownum is false, the current row is used, else it is pointing to the row (zero-based)
187 */
188 function sql_fetchfield($field, $rownum = false, $query_id = false)
189 {
190 global $cache;
191
192 if ($query_id === false)
193 {
194 $query_id = $this->query_result;
195 }
196
197 if ($query_id !== false)
198 {
199 if ($rownum !== false)
200 {
201 $this->sql_rowseek($rownum, $query_id);
202 }
203
204 if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
205 {
206 return $cache->sql_fetchfield($query_id, $field);
207 }
208
209 $row = $this->sql_fetchrow($query_id);
210 return (isset($row[$field])) ? $row[$field] : false;
211 }
212
213 return false;
214 }
215
216 /**
217 * Correctly adjust LIKE expression for special characters
218 * Some DBMS are handling them in a different way
219 *
220 * @param string $expression The expression to use. Every wildcard is escaped, except $this->any_char and $this->one_char
221 * @return string LIKE expression including the keyword!
222 */
223 function sql_like_expression($expression)
224 {
225 $expression = str_replace(array('_', '%'), array("\_", "\%"), $expression);
226 $expression = str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
227
228 return $this->_sql_like_expression('LIKE \'' . $this->sql_escape($expression) . '\'');
229 }
230
231 /**
232 * SQL Transaction
233 * @access private
234 */
235 function sql_transaction($status = 'begin')
236 {
237 switch ($status)
238 {
239 case 'begin':
240 // If we are within a transaction we will not open another one, but enclose the current one to not loose data (prevening auto commit)
241 if ($this->transaction)
242 {
243 $this->transactions++;
244 return true;
245 }
246
247 $result = $this->_sql_transaction('begin');
248
249 if (!$result)
250 {
251 $this->sql_error();
252 }
253
254 $this->transaction = true;
255 break;
256
257 case 'commit':
258 // If there was a previously opened transaction we do not commit yet... but count back the number of inner transactions
259 if ($this->transaction && $this->transactions)
260 {
261 $this->transactions--;
262 return true;
263 }
264
265 $result = $this->_sql_transaction('commit');
266
267 if (!$result)
268 {
269 $this->sql_error();
270 }
271
272 $this->transaction = false;
273 $this->transactions = 0;
274 break;
275
276 case 'rollback':
277 $result = $this->_sql_transaction('rollback');
278 $this->transaction = false;
279 $this->transactions = 0;
280 break;
281
282 default:
283 $result = $this->_sql_transaction($status);
284 break;
285 }
286
287 return $result;
288 }
289
290 /**
291 * Build sql statement from array for insert/update/select statements
292 *
293 * Idea for this from Ikonboard
294 * Possible query values: INSERT, INSERT_SELECT, MULTI_INSERT, UPDATE, SELECT
295 *
296 */
297 function sql_build_array($query, $assoc_ary = false)
298 {
299 if (!is_array($assoc_ary))
300 {
301 return false;
302 }
303
304 $fields = $values = array();
305
306 if ($query == 'INSERT' || $query == 'INSERT_SELECT')
307 {
308 foreach ($assoc_ary as $key => $var)
309 {
310 $fields[] = $key;
311
312 if (is_array($var) && is_string($var[0]))
313 {
314 // This is used for INSERT_SELECT(s)
315 $values[] = $var[0];
316 }
317 else
318 {
319 $values[] = $this->_sql_validate_value($var);
320 }
321 }
322
323 $query = ($query == 'INSERT') ? ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')' : ' (' . implode(', ', $fields) . ') SELECT ' . implode(', ', $values) . ' ';
324 }
325 else if ($query == 'MULTI_INSERT')
326 {
327 $ary = array();
328 foreach ($assoc_ary as $id => $sql_ary)
329 {
330 // If by accident the sql array is only one-dimensional we build a normal insert statement
331 if (!is_array($sql_ary))
332 {
333 return $this->sql_build_array('INSERT', $assoc_ary);
334 }
335
336 $values = array();
337 foreach ($sql_ary as $key => $var)
338 {
339 $values[] = $this->_sql_validate_value($var);
340 }
341 $ary[] = '(' . implode(', ', $values) . ')';
342 }
343
344 $query = ' (' . implode(', ', array_keys($assoc_ary[0])) . ') VALUES ' . implode(', ', $ary);
345 }
346 else if ($query == 'UPDATE' || $query == 'SELECT')
347 {
348 $values = array();
349 foreach ($assoc_ary as $key => $var)
350 {
351 $values[] = "$key = " . $this->_sql_validate_value($var);
352 }
353 $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
354 }
355
356 return $query;
357 }
358
359 /**
360 * Build IN or NOT IN sql comparison string, uses <> or = on single element
361 * arrays to improve comparison speed
362 *
363 * @access public
364 * @param string $field name of the sql column that shall be compared
365 * @param array $array array of values that are allowed (IN) or not allowed (NOT IN)
366 * @param bool $negate true for NOT IN (), false for IN () (default)
367 * @param bool $allow_empty_set If true, allow $array to be empty, this function will return 1=1 or 1=0 then. Default to false.
368 */
369 function sql_in_set($field, $array, $negate = false, $allow_empty_set = false)
370 {
371 if (!sizeof($array))
372 {
373 if (!$allow_empty_set)
374 {
375 // Print the backtrace to help identifying the location of the problematic code
376 $this->sql_error('No values specified for SQL IN comparison');
377 }
378 else
379 {
380 // NOT IN () actually means everything so use a tautology
381 if ($negate)
382 {
383 return '1=1';
384 }
385 // IN () actually means nothing so use a contradiction
386 else
387 {
388 return '1=0';
389 }
390 }
391 }
392
393 if (!is_array($array))
394 {
395 $array = array($array);
396 }
397
398 if (sizeof($array) == 1)
399 {
400 @reset($array);
401 $var = current($array);
402
403 return $field . ($negate ? ' <> ' : ' = ') . $this->_sql_validate_value($var);
404 }
405 else
406 {
407 return $field . ($negate ? ' NOT IN ' : ' IN ') . '(' . implode(', ', array_map(array($this, '_sql_validate_value'), $array)) . ')';
408 }
409 }
410
411 /**
412 * Run more than one insert statement.
413 *
414 * @param string $table table name to run the statements on
415 * @param array &$sql_ary multi-dimensional array holding the statement data.
416 *
417 * @return bool false if no statements were executed.
418 * @access public
419 */
420 function sql_multi_insert($table, &$sql_ary)
421 {
422 if (!sizeof($sql_ary))
423 {
424 return false;
425 }
426
427 if ($this->multi_insert)
428 {
429 $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('MULTI_INSERT', $sql_ary));
430 }
431 else
432 {
433 foreach ($sql_ary as $ary)
434 {
435 if (!is_array($ary))
436 {
437 return false;
438 }
439
440 $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $ary));
441 }
442 }
443
444 return true;
445 }
446
447 /**
448 * Function for validating values
449 * @access private
450 */
451 function _sql_validate_value($var)
452 {
453 if (is_null($var))
454 {
455 return 'NULL';
456 }
457 else if (is_string($var))
458 {
459 return "'" . $this->sql_escape($var) . "'";
460 }
461 else
462 {
463 return (is_bool($var)) ? intval($var) : $var;
464 }
465 }
466
467 /**
468 * Build sql statement from array for select and select distinct statements
469 *
470 * Possible query values: SELECT, SELECT_DISTINCT
471 */
472 function sql_build_query($query, $array)
473 {
474 $sql = '';
475 switch ($query)
476 {
477 case 'SELECT':
478 case 'SELECT_DISTINCT';
479
480 $sql = str_replace('_', ' ', $query) . ' ' . $array['SELECT'] . ' FROM ';
481
482 $table_array = array();
483 foreach ($array['FROM'] as $table_name => $alias)
484 {
485 if (is_array($alias))
486 {
487 foreach ($alias as $multi_alias)
488 {
489 $table_array[] = $table_name . ' ' . $multi_alias;
490 }
491 }
492 else
493 {
494 $table_array[] = $table_name . ' ' . $alias;
495 }
496 }
497
498 $sql .= $this->_sql_custom_build('FROM', implode(', ', $table_array));
499
500 if (!empty($array['LEFT_JOIN']))
501 {
502 foreach ($array['LEFT_JOIN'] as $join)
503 {
504 $sql .= ' LEFT JOIN ' . key($join['FROM']) . ' ' . current($join['FROM']) . ' ON (' . $join['ON'] . ')';
505 }
506 }
507
508 if (!empty($array['WHERE']))
509 {
510 $sql .= ' WHERE ' . $this->_sql_custom_build('WHERE', $array['WHERE']);
511 }
512
513 if (!empty($array['GROUP_BY']))
514 {
515 $sql .= ' GROUP BY ' . $array['GROUP_BY'];
516 }
517
518 if (!empty($array['ORDER_BY']))
519 {
520 $sql .= ' ORDER BY ' . $array['ORDER_BY'];
521 }
522
523 break;
524 }
525
526 return $sql;
527 }
528
529 /**
530 * display sql error page
531 */
532 function sql_error($sql = '')
533 {
534 global $auth, $user, $config;
535
536 // Set var to retrieve errored status
537 $this->sql_error_triggered = true;
538 $this->sql_error_sql = $sql;
539
540 $error = $this->_sql_error();
541
542 if (!$this->return_on_error)
543 {
544 $message = 'SQL ERROR [ ' . $this->sql_layer . ' ]<br /><br />' . $error['message'] . ' [' . $error['code'] . ']';
545
546 // Show complete SQL error and path to administrators only
547 // Additionally show complete error on installation or if extended debug mode is enabled
548 // The DEBUG_EXTRA constant is for development only!
549 if ((isset($auth) && $auth->acl_get('a_')) || defined('IN_INSTALL') || defined('DEBUG_EXTRA'))
550 {
551 // Print out a nice backtrace...
552 $backtrace = get_backtrace();
553
554 $message .= ($sql) ? '<br /><br />SQL<br /><br />' . htmlspecialchars($sql) : '';
555 $message .= ($backtrace) ? '<br /><br />BACKTRACE<br />' . $backtrace : '';
556 $message .= '<br />';
557 }
558 else
559 {
560 // If error occurs in initiating the session we need to use a pre-defined language string
561 // This could happen if the connection could not be established for example (then we are not able to grab the default language)
562 if (!isset($user->lang['SQL_ERROR_OCCURRED']))
563 {
564 $message .= '<br /><br />An sql error occurred while fetching this page. Please contact an administrator if this problem persists.';
565 }
566 else
567 {
568 if (!empty($config['board_contact']))
569 {
570 $message .= '<br /><br />' . sprintf($user->lang['SQL_ERROR_OCCURRED'], '<a href="mailto:' . htmlspecialchars($config['board_contact']) . '">', '</a>');
571 }
572 else
573 {
574 $message .= '<br /><br />' . sprintf($user->lang['SQL_ERROR_OCCURRED'], '', '');
575 }
576 }
577 }
578
579 if ($this->transaction)
580 {
581 $this->sql_transaction('rollback');
582 }
583
584 if (strlen($message) > 1024)
585 {
586 // We need to define $msg_long_text here to circumvent text stripping.
587 global $msg_long_text;
588 $msg_long_text = $message;
589
590 trigger_error(false, E_USER_ERROR);
591 }
592
593 trigger_error($message, E_USER_ERROR);
594 }
595
596 if ($this->transaction)
597 {
598 $this->sql_transaction('rollback');
599 }
600
601 return $error;
602 }
603
604 /**
605 * Explain queries
606 */
607 function sql_report($mode, $query = '')
608 {
609 global $cache, $starttime, $phpbb_root_path, $user;
610
611 if (empty($_REQUEST['explain']))
612 {
613 return false;
614 }
615
616 if (!$query && $this->query_hold != '')
617 {
618 $query = $this->query_hold;
619 }
620
621 switch ($mode)
622 {
623 case 'display':
624 if (!empty($cache))
625 {
626 $cache->unload();
627 }
628 $this->sql_close();
629
630 $mtime = explode(' ', microtime());
631 $totaltime = $mtime[0] + $mtime[1] - $starttime;
632
633 echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
634 <html xmlns="http://www.w3.org/1999/xhtml" dir="ltr">
635 <head>
636 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
637 <meta http-equiv="Content-Style-Type" content="text/css" />
638 <meta http-equiv="imagetoolbar" content="no" />
639 <title>SQL Report</title>
640 <link href="' . $phpbb_root_path . 'adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" />
641 </head>
642 <body id="errorpage">
643 <div id="wrap">
644 <div id="page-header">
645 <a href="' . build_url('explain') . '">Return to previous page</a>
646 </div>
647 <div id="page-body">
648 <div id="acp">
649 <div class="panel">
650 <span class="corners-top"><span></span></span>
651 <div id="content">
652 <h1>SQL Report</h1>
653 <br />
654 <p><b>Page generated in ' . round($totaltime, 4) . " seconds with {$this->num_queries['normal']} queries" . (($this->num_queries['cached']) ? " + {$this->num_queries['cached']} " . (($this->num_queries['cached'] == 1) ? 'query' : 'queries') . ' returning data from cache' : '') . '</b></p>
655
656 <p>Time spent on ' . $this->sql_layer . ' queries: <b>' . round($this->sql_time, 5) . 's</b> | Time spent on PHP: <b>' . round($totaltime - $this->sql_time, 5) . 's</b></p>
657
658 <br /><br />
659 ' . $this->sql_report . '
660 </div>
661 <span class="corners-bottom"><span></span></span>
662 </div>
663 </div>
664 </div>
665 <div id="page-footer">
666 Powered by phpBB © 2000, 2002, 2005, 2007 <a href="http://www.phpbb.com/">phpBB Group</a>
667 </div>
668 </div>
669 </body>
670 </html>';
671
672 exit_handler();
673
674 break;
675
676 case 'stop':
677 $endtime = explode(' ', microtime());
678 $endtime = $endtime[0] + $endtime[1];
679
680 $this->sql_report .= '
681
682 <table cellspacing="1">
683 <thead>
684 <tr>
685 <th>Query #' . $this->num_queries['total'] . '</th>
686 </tr>
687 </thead>
688 <tbody>
689 <tr>
690 <td class="row3"><textarea style="font-family:\'Courier New\',monospace;width:99%" rows="5" cols="10">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td>
691 </tr>
692 </tbody>
693 </table>
694
695 ' . $this->html_hold . '
696
697 <p style="text-align: center;">
698 ';
699
700 if ($this->query_result)
701 {
702 if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query))
703 {
704 $this->sql_report .= 'Affected rows: <b>' . $this->sql_affectedrows($this->query_result) . '</b> | ';
705 }
706 $this->sql_report .= 'Before: ' . sprintf('%.5f', $this->curtime - $starttime) . 's | After: ' . sprintf('%.5f', $endtime - $starttime) . 's | Elapsed: <b>' . sprintf('%.5f', $endtime - $this->curtime) . 's</b>';
707 }
708 else
709 {
710 $error = $this->sql_error();
711 $this->sql_report .= '<b style="color: red">FAILED</b> - ' . $this->sql_layer . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']);
712 }
713
714 $this->sql_report .= '</p><br /><br />';
715
716 $this->sql_time += $endtime - $this->curtime;
717 break;
718
719 case 'start':
720 $this->query_hold = $query;
721 $this->html_hold = '';
722
723 $this->_sql_report($mode, $query);
724
725 $this->curtime = explode(' ', microtime());
726 $this->curtime = $this->curtime[0] + $this->curtime[1];
727
728 break;
729
730 case 'add_select_row':
731
732 $html_table = func_get_arg(2);
733 $row = func_get_arg(3);
734
735 if (!$html_table && sizeof($row))
736 {
737 $html_table = true;
738 $this->html_hold .= '<table cellspacing="1"><tr>';
739
740 foreach (array_keys($row) as $val)
741 {
742 $this->html_hold .= '<th>' . (($val) ? ucwords(str_replace('_', ' ', $val)) : ' ') . '</th>';
743 }
744 $this->html_hold .= '</tr>';
745 }
746 $this->html_hold .= '<tr>';
747
748 $class = 'row1';
749 foreach (array_values($row) as $val)
750 {
751 $class = ($class == 'row1') ? 'row2' : 'row1';
752 $this->html_hold .= '<td class="' . $class . '">' . (($val) ? $val : ' ') . '</td>';
753 }
754 $this->html_hold .= '</tr>';
755
756 return $html_table;
757
758 break;
759
760 case 'fromcache':
761
762 $this->_sql_report($mode, $query);
763
764 break;
765
766 case 'record_fromcache':
767
768 $endtime = func_get_arg(2);
769 $splittime = func_get_arg(3);
770
771 $time_cache = $endtime - $this->curtime;
772 $time_db = $splittime - $endtime;
773 $color = ($time_db > $time_cache) ? 'green' : 'red';
774
775 $this->sql_report .= '<table cellspacing="1"><thead><tr><th>Query results obtained from the cache</th></tr></thead><tbody><tr>';
776 $this->sql_report .= '<td class="row3"><textarea style="font-family:\'Courier New\',monospace;width:99%" rows="5" cols="10">' . preg_replace('/\t(AND|OR)(\W)/', "\$1\$2", htmlspecialchars(preg_replace('/[\s]*[\n\r\t]+[\n\r\s\t]*/', "\n", $query))) . '</textarea></td></tr></tbody></table>';
777 $this->sql_report .= '<p style="text-align: center;">';
778 $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><br /><br />';
779
780 // Pad the start time to not interfere with page timing
781 $starttime += $time_db;
782
783 break;
784
785 default:
786
787 $this->_sql_report($mode, $query);
788
789 break;
790 }
791
792 return true;
793 }
794 }
795
796 /**
797 * This variable holds the class name to use later
798 */
799 $sql_db = (!empty($dbms)) ? 'dbal_' . basename($dbms) : 'dbal';
800
801 ?>