| [ Index ] |
PHP Cross Reference of phpBB 3.0 Beta 3 |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * 4 * @package dbal 5 * @version $Id: dbal.php,v 1.46 2006/11/10 13:49:05 acydburn Exp $ 6 * @copyright (c) 2005 phpBB Group 7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License 8 * 9 */ 10 11 /** 12 * Database Abstraction Layer 13 * @package dbal 14 */ 15 class dbal 16 { 17 var $db_connect_id; 18 var $query_result; 19 var $return_on_error = false; 20 var $transaction = false; 21 var $sql_time = 0; 22 var $num_queries = array(); 23 var $open_queries = array(); 24 25 var $curtime = 0; 26 var $query_hold = ''; 27 var $html_hold = ''; 28 var $sql_report = ''; 29 30 var $persistency = false; 31 var $user = ''; 32 var $server = ''; 33 var $dbname = ''; 34 35 // Set to true if error triggered 36 var $sql_error_triggered = false; 37 38 // Holding the last sql query on sql error 39 var $sql_error_sql = ''; 40 41 /** 42 * Current sql layer 43 */ 44 var $sql_layer = ''; 45 46 /** 47 * Constructor 48 */ 49 function dbal() 50 { 51 $this->num_queries = array( 52 'cached' => 0, 53 'normal' => 0, 54 'total' => 0, 55 ); 56 57 // Fill default sql layer based on the class being called. 58 // This can be changed by the specified layer itself later if needed. 59 $this->sql_layer = substr(get_class($this), 5); 60 } 61 62 /** 63 * return on error or display error message 64 */ 65 function sql_return_on_error($fail = false) 66 { 67 $this->sql_error_triggered = false; 68 $this->sql_error_sql = ''; 69 70 $this->return_on_error = $fail; 71 } 72 73 /** 74 * Return number of sql queries and cached sql queries used 75 */ 76 function sql_num_queries($cached = false) 77 { 78 return ($cached) ? $this->num_queries['cached'] : $this->num_queries['normal']; 79 } 80 81 /** 82 * Add to query count 83 */ 84 function sql_add_num_queries($cached = false) 85 { 86 $this->num_queries['cached'] += ($cached) ? 1 : 0; 87 $this->num_queries['normal'] += ($cached) ? 0 : 1; 88 $this->num_queries['total'] += 1; 89 } 90 91 /** 92 * DBAL garbage collection, close sql connection 93 */ 94 function sql_close() 95 { 96 if (!$this->db_connect_id) 97 { 98 return false; 99 } 100 101 if ($this->transaction) 102 { 103 $this->sql_transaction('commit'); 104 } 105 106 if (sizeof($this->open_queries)) 107 { 108 foreach ($this->open_queries as $i_query_id => $query_id) 109 { 110 $this->sql_freeresult($query_id); 111 } 112 } 113 114 return $this->_sql_close(); 115 } 116 117 /** 118 * Fetch all rows 119 */ 120 function sql_fetchrowset($query_id = false) 121 { 122 if ($query_id === false) 123 { 124 $query_id = $this->query_result; 125 } 126 127 if ($query_id !== false) 128 { 129 $result = array(); 130 while ($row = $this->sql_fetchrow($query_id)) 131 { 132 $result[] = $row; 133 } 134 135 return $result; 136 } 137 138 return false; 139 } 140 141 /** 142 * Fetch field 143 * if rownum is false, the current row is used, else it is pointing to the row (zero-based) 144 */ 145 function sql_fetchfield($field, $rownum = false, $query_id = false) 146 { 147 global $cache; 148 149 if ($query_id === false) 150 { 151 $query_id = $this->query_result; 152 } 153 154 if ($query_id !== false) 155 { 156 if ($rownum !== false) 157 { 158 $this->sql_rowseek($rownum, $query_id); 159 } 160 161 if (!is_object($query_id) && isset($cache->sql_rowset[$query_id])) 162 { 163 return $cache->sql_fetchfield($query_id, $field); 164 } 165 166 $row = $this->sql_fetchrow($query_id); 167 return (isset($row[$field])) ? $row[$field] : false; 168 } 169 170 return false; 171 } 172 173 /** 174 * SQL Transaction 175 * @access private 176 */ 177 function sql_transaction($status = 'begin') 178 { 179 switch ($status) 180 { 181 case 'begin': 182 // Commit previously opened transaction before opening another transaction 183 if ($this->transaction) 184 { 185 $this->_sql_transaction('commit'); 186 } 187 188 $result = $this->_sql_transaction('begin'); 189 $this->transaction = true; 190 break; 191 192 case 'commit': 193 $result = $this->_sql_transaction('commit'); 194 $this->transaction = false; 195 196 if (!$result) 197 { 198 $this->_sql_transaction('rollback'); 199 } 200 break; 201 202 case 'rollback': 203 $result = $this->_sql_transaction('rollback'); 204 $this->transaction = false; 205 break; 206 207 default: 208 $result = $this->_sql_transaction($status); 209 break; 210 } 211 212 return $result; 213 } 214 215 /** 216 * Build sql statement from array for insert/update/select statements 217 * 218 * Idea for this from Ikonboard 219 * Possible query values: INSERT, INSERT_SELECT, MULTI_INSERT, UPDATE, SELECT 220 * 221 */ 222 function sql_build_array($query, $assoc_ary = false) 223 { 224 if (!is_array($assoc_ary)) 225 { 226 return false; 227 } 228 229 $fields = $values = array(); 230 231 if ($query == 'INSERT' || $query == 'INSERT_SELECT') 232 { 233 foreach ($assoc_ary as $key => $var) 234 { 235 $fields[] = $key; 236 237 if (is_array($var) && is_string($var[0])) 238 { 239 // This is used for INSERT_SELECT(s) 240 $values[] = $var[0]; 241 } 242 else 243 { 244 $values[] = $this->_sql_validate_value($var); 245 } 246 } 247 248 $query = ($query == 'INSERT') ? ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')' : ' (' . implode(', ', $fields) . ') SELECT ' . implode(', ', $values) . ' '; 249 } 250 else if ($query == 'MULTI_INSERT') 251 { 252 $ary = array(); 253 foreach ($assoc_ary as $id => $sql_ary) 254 { 255 // If by accident the sql array is only one-dimensional we build a normal insert statement 256 if (!is_array($sql_ary)) 257 { 258 return $this->sql_build_array('INSERT', $assoc_ary); 259 } 260 261 $values = array(); 262 foreach ($sql_ary as $key => $var) 263 { 264 $values[] = $this->_sql_validate_value($var); 265 } 266 $ary[] = '(' . implode(', ', $values) . ')'; 267 } 268 269 $query = ' (' . implode(', ', array_keys($assoc_ary[0])) . ') VALUES ' . implode(', ', $ary); 270 } 271 else if ($query == 'UPDATE' || $query == 'SELECT') 272 { 273 $values = array(); 274 foreach ($assoc_ary as $key => $var) 275 { 276 $values[] = "$key = " . $this->_sql_validate_value($var); 277 } 278 $query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values); 279 } 280 281 return $query; 282 } 283 284 /** 285 * Build IN, NOT IN, = and <> sql comparison string. 286 * @access public 287 */ 288 function sql_in_set($field, $array, $negate = false) 289 { 290 if (!sizeof($array)) 291 { 292 // Not optimal, but at least the backtrace should help in identifying where the problem lies. 293 $this->sql_error('No values specified for SQL IN comparison'); 294 } 295 296 if (!is_array($array)) 297 { 298 $array = array($array); 299 } 300 301 if (sizeof($array) == 1) 302 { 303 @reset($array); 304 $var = current($array); 305 306 return $field . ($negate ? ' <> ' : ' = ') . $this->_sql_validate_value($var); 307 } 308 else 309 { 310 return $field . ($negate ? ' NOT IN ' : ' IN ' ) . '(' . implode(', ', array_map(array($this, '_sql_validate_value'), $array)) . ')'; 311 } 312 } 313 314 /** 315 * Run more than one insert statement. 316 * 317 * @param $sql_ary array multi-dimensional array holding the statement data. 318 * @param $table string table name to run the statements on 319 * 320 * @return bool false if no statements were executed. 321 * @access public 322 */ 323 function sql_multi_insert($table, &$sql_ary) 324 { 325 if (!sizeof($sql_ary)) 326 { 327 return false; 328 } 329 330 switch ($this->sql_layer) 331 { 332 case 'mysql': 333 case 'mysql4': 334 case 'mysqli': 335 case 'sqlite': 336 $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('MULTI_INSERT', $sql_ary)); 337 break; 338 339 default: 340 foreach ($sql_ary as $ary) 341 { 342 if (!is_array($ary)) 343 { 344 return false; 345 } 346 347 $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $ary)); 348 } 349 break; 350 } 351 352 return true; 353 } 354 355 /** 356 * Function for validating values 357 * @access private 358 */ 359 function _sql_validate_value($var) 360 { 361 if (is_null($var)) 362 { 363 return 'NULL'; 364 } 365 else if (is_string($var)) 366 { 367 return "'" . $this->sql_escape($var) . "'"; 368 } 369 else 370 { 371 return (is_bool($var)) ? intval($var) : $var; 372 } 373 } 374 375 /** 376 * Build sql statement from array for select and select distinct statements 377 * 378 * Possible query values: SELECT, SELECT_DISTINCT 379 */ 380 function sql_build_query($query, $array) 381 { 382 $sql = ''; 383 switch ($query) 384 { 385 case 'SELECT': 386 case 'SELECT_DISTINCT'; 387 388 $sql = str_replace('_', ' ', $query) . ' ' . $array['SELECT'] . ' FROM '; 389 390 $table_array = array(); 391 foreach ($array['FROM'] as $table_name => $alias) 392 { 393 if (is_array($alias)) 394 { 395 foreach ($alias as $multi_alias) 396 { 397 $table_array[] = $table_name . ' ' . $multi_alias; 398 } 399 } 400 else 401 { 402 $table_array[] = $table_name . ' ' . $alias; 403 } 404 } 405 406 $sql .= $this->_sql_custom_build('FROM', implode(', ', $table_array)); 407 408 if (!empty($array['LEFT_JOIN'])) 409 { 410 foreach ($array['LEFT_JOIN'] as $join) 411 { 412 $sql .= ' LEFT JOIN ' . key($join['FROM']) . ' ' . current($join['FROM']) . ' ON (' . $join['ON'] . ')'; 413 } 414 } 415 416 if (!empty($array['WHERE'])) 417 { 418 $sql .= ' WHERE ' . $this->_sql_custom_build('WHERE', $array['WHERE']); 419 } 420 421 if (!empty($array['GROUP_BY'])) 422 { 423 $sql .= ' GROUP BY ' . $array['GROUP_BY']; 424 } 425 426 if (!empty($array['ORDER_BY'])) 427 { 428 $sql .= ' ORDER BY ' . $array['ORDER_BY']; 429 } 430 431 break; 432 } 433 434 return $sql; 435 } 436 437 /** 438 * display sql error page 439 */ 440 function sql_error($sql = '') 441 { 442 global $auth, $user; 443 444 // Set var to retrieve errored status 445 $this->sql_error_triggered = true; 446 $this->sql_error_sql = $sql; 447 448 $error = $this->_sql_error(); 449 450 if (!$this->return_on_error) 451 { 452 $message = '<u>SQL ERROR</u> [ ' . $this->sql_layer . ' ]<br /><br />' . $error['message'] . ' [' . $error['code'] . ']'; 453 454 // Show complete SQL error and path to administrators only 455 // Additionally show complete error on installation or if extended debug mode is enabled 456 // The DEBUG_EXTRA constant is for development only! 457 if (isset($auth) && ($auth->acl_get('a_') || defined('IN_INSTALL') || defined('DEBUG_EXTRA'))) 458 { 459 // Print out a nice backtrace... 460 $backtrace = get_backtrace(); 461 462 $message .= ($sql) ? '<br /><br /><u>SQL</u><br /><br />' . htmlspecialchars($sql) : ''; 463 $message .= ($backtrace) ? '<br /><br /><u>BACKTRACE</u><br />' . $backtrace : ''; 464 $message .= '<br />'; 465 } 466 else 467 { 468 // If error occurs in initiating the session we need to use a pre-defined language string 469 // This could happen if the connection could not be established for example (then we are not able to grab the default language) 470 if (!isset($user->lang['SQL_ERROR_OCCURRED'])) 471 { 472 $message .= '<br /><br />An sql error occurred while fetching this page. Please contact an administrator if this problem persists.'; 473 } 474 else 475 { 476 $message .= '<br /><br />' . $user->lang['SQL_ERROR_OCCURRED']; 477 } 478 } 479 480 if ($this->transaction) 481 { 482 $this->sql_transaction('rollback'); 483 } 484 485 if (strlen($message) > 1024) 486 { 487 // We need to define $msg_long_text here to circumvent text stripping. 488 global $msg_long_text; 489 $msg_long_text = $message; 490 491 trigger_error(false, E_USER_ERROR); 492 } 493 494 trigger_error($message, E_USER_ERROR); 495 } 496 497 return $error; 498 } 499 500 /** 501 * Explain queries 502 */ 503 function sql_report($mode, $query = '') 504 { 505 global $cache, $starttime, $phpbb_root_path, $user; 506 507 if (empty($_REQUEST['explain'])) 508 { 509 return false; 510 } 511 512 if (!$query && $this->query_hold != '') 513 { 514 $query = $this->query_hold; 515 } 516 517 switch ($mode) 518 { 519 case 'display': 520 if (!empty($cache)) 521 { 522 $cache->unload(); 523 } 524 $this->sql_close(); 525 526 $mtime = explode(' ', microtime()); 527 $totaltime = $mtime[0] + $mtime[1] - $starttime; 528 529 echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 530 <html xmlns="http://www.w3.org/1999/xhtml" dir="ltr"> 531 <head> 532 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> 533 <meta http-equiv="Content-Style-Type" content="text/css" /> 534 <meta http-equiv="imagetoolbar" content="no" /> 535 <title>SQL Report</title> 536 <link href="' . $phpbb_root_path . 'adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" /> 537 </head> 538 <body id="errorpage"> 539 <div id="wrap"> 540 <div id="page-header"> 541 <a href="' . build_url('explain') . '">Return to previous page</a> 542 </div> 543 <div id="page-body"> 544 <div class="panel"> 545 <span class="corners-top"><span></span></span> 546 <div id="content"> 547 <h1>SQL Report</h1> 548 <br /> 549 <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> 550 551 <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> 552 553 <br /><br /> 554 ' . $this->sql_report . ' 555 </div> 556 <span class="corners-bottom"><span></span></span> 557 </div> 558 </div> 559 <div id="page-footer"> 560 Powered by phpBB © ' . date('Y') . ' <a href="http://www.phpbb.com/">phpBB Group</a> 561 </div> 562 </div> 563 </body> 564 </html>'; 565 exit; 566 break; 567 568 case 'stop': 569 $endtime = explode(' ', microtime()); 570 $endtime = $endtime[0] + $endtime[1]; 571 572 $this->sql_report .= ' 573 574 <table cellspacing="1"> 575 <thead> 576 <tr> 577 <th>Query #' . $this->num_queries['total'] . '</th> 578 </tr> 579 </thead> 580 <tbody> 581 <tr> 582 <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> 583 </tr> 584 </table> 585 586 ' . $this->html_hold . ' 587 588 <p style="text-align: center;"> 589 '; 590 591 if ($this->query_result) 592 { 593 if (preg_match('/^(UPDATE|DELETE|REPLACE)/', $query)) 594 { 595 $this->sql_report .= 'Affected rows: <b>' . $this->sql_affectedrows($this->query_result) . '</b> | '; 596 } 597 $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>'; 598 } 599 else 600 { 601 $error = $this->sql_error(); 602 $this->sql_report .= '<b style="color: red">FAILED</b> - ' . $this->sql_layer . ' Error ' . $error['code'] . ': ' . htmlspecialchars($error['message']); 603 } 604 605 $this->sql_report .= '</p><br /><br />'; 606 607 $this->sql_time += $endtime - $this->curtime; 608 break; 609 610 case 'start': 611 $this->query_hold = $query; 612 $this->html_hold = ''; 613 614 $this->_sql_report($mode, $query); 615 616 $this->curtime = explode(' ', microtime()); 617 $this->curtime = $this->curtime[0] + $this->curtime[1]; 618 619 break; 620 621 case 'add_select_row': 622 623 $html_table = func_get_arg(2); 624 $row = func_get_arg(3); 625 626 if (!$html_table && sizeof($row)) 627 { 628 $html_table = true; 629 $this->html_hold .= '<table cellspacing="1"><tr>'; 630 631 foreach (array_keys($row) as $val) 632 { 633 $this->html_hold .= '<th>' . (($val) ? ucwords(str_replace('_', ' ', $val)) : ' ') . '</th>'; 634 } 635 $this->html_hold .= '</tr>'; 636 } 637 $this->html_hold .= '<tr>'; 638 639 $class = 'row1'; 640 foreach (array_values($row) as $val) 641 { 642 $class = ($class == 'row1') ? 'row2' : 'row1'; 643 $this->html_hold .= '<td class="' . $class . '">' . (($val) ? $val : ' ') . '</td>'; 644 } 645 $this->html_hold .= '</tr>'; 646 647 return $html_table; 648 649 break; 650 651 case 'fromcache': 652 653 $this->_sql_report($mode, $query); 654 655 break; 656 657 case 'record_fromcache': 658 659 $endtime = func_get_arg(2); 660 $splittime = func_get_arg(3); 661 662 $time_cache = $endtime - $this->curtime; 663 $time_db = $splittime - $endtime; 664 $color = ($time_db > $time_cache) ? 'green' : 'red'; 665 666 $this->sql_report .= '<table cellspacing="1"><thead><tr><th>Query results obtained from the cache</th></tr></thead><tbody><tr>'; 667 $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>'; 668 $this->sql_report .= '<p style="text-align: center;">'; 669 $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 />'; 670 671 // Pad the start time to not interfere with page timing 672 $starttime += $time_db; 673 674 break; 675 676 default: 677 678 $this->_sql_report($mode, $query); 679 680 break; 681 } 682 683 return true; 684 } 685 } 686 687 /** 688 */ 689 if (!defined('IN_PHPBB')) 690 { 691 exit; 692 } 693 694 /** 695 * This variable holds the class name to use later 696 */ 697 $sql_db = 'dbal_' . $dbms; 698 699 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Nov 22 00:35:05 2006 | Cross-referenced by PHPXref 0.6 |