1<?php 2/** 3* 4* @package dbal 5* @version $Id$ 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*/ 14if (!defined('IN_PHPBB')) 15{ 16 exit; 17} 18 19include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx); 20 21/** 22* Oracle Database Abstraction Layer 23* @package dbal 24*/ 25class dbal_oracle extends dbal 26{ 27 var $last_query_text = ''; 28 var $connect_error = ''; 29 30 /** 31 * Connect to server 32 */ 33 function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false) 34 { 35 $this->persistency = $persistency; 36 $this->user = $sqluser; 37 $this->server = $sqlserver . (($port) ? ':' . $port : ''); 38 $this->dbname = $database; 39 40 $connect = $database; 41 42 // support for "easy connect naming" 43 if ($sqlserver !== '' && $sqlserver !== '/') 44 { 45 if (substr($sqlserver, -1, 1) == '/') 46 { 47 $sqlserver == substr($sqlserver, 0, -1); 48 } 49 $connect = $sqlserver . (($port) ? ':' . $port : '') . '/' . $database; 50 } 51 52 if ($new_link) 53 { 54 if (!function_exists('ocinlogon')) 55 { 56 $this->connect_error = 'ocinlogon function does not exist, is oci extension installed?'; 57 return $this->sql_error(''); 58 } 59 $this->db_connect_id = @ocinlogon($this->user, $sqlpassword, $connect, 'UTF8'); 60 } 61 else if ($this->persistency) 62 { 63 if (!function_exists('ociplogon')) 64 { 65 $this->connect_error = 'ociplogon function does not exist, is oci extension installed?'; 66 return $this->sql_error(''); 67 } 68 $this->db_connect_id = @ociplogon($this->user, $sqlpassword, $connect, 'UTF8'); 69 } 70 else 71 { 72 if (!function_exists('ocilogon')) 73 { 74 $this->connect_error = 'ocilogon function does not exist, is oci extension installed?'; 75 return $this->sql_error(''); 76 } 77 $this->db_connect_id = @ocilogon($this->user, $sqlpassword, $connect, 'UTF8'); 78 } 79 80 return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); 81 } 82 83 /** 84 * Version information about used database 85 * @param bool $raw if true, only return the fetched sql_server_version 86 * @param bool $use_cache forced to false for Oracle 87 * @return string sql server version 88 */ 89 function sql_server_info($raw = false, $use_cache = true) 90 { 91 /** 92 * force $use_cache false. I didn't research why the caching code below is commented out 93 * but I assume its because the Oracle extension provides a direct method to access it 94 * without a query. 95 */ 96 97 $use_cache = false; 98/* 99 global $cache; 100 101 if (empty($cache) || ($this->sql_server_version = $cache->get('oracle_version')) === false) 102 { 103 $result = @ociparse($this->db_connect_id, 'SELECT * FROM v$version WHERE banner LIKE \'Oracle%\''); 104 @ociexecute($result, OCI_DEFAULT); 105 @ocicommit($this->db_connect_id); 106 107 $row = array(); 108 @ocifetchinto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS); 109 @ocifreestatement($result); 110 $this->sql_server_version = trim($row['BANNER']); 111 112 $cache->put('oracle_version', $this->sql_server_version); 113 } 114*/ 115 $this->sql_server_version = @ociserverversion($this->db_connect_id); 116 117 return $this->sql_server_version; 118 } 119 120 /** 121 * SQL Transaction 122 * @access private 123 */ 124 function _sql_transaction($status = 'begin') 125 { 126 switch ($status) 127 { 128 case 'begin': 129 return true; 130 break; 131 132 case 'commit': 133 return @ocicommit($this->db_connect_id); 134 break; 135 136 case 'rollback': 137 return @ocirollback($this->db_connect_id); 138 break; 139 } 140 141 return true; 142 } 143 144 /** 145 * Oracle specific code to handle the fact that it does not compare columns properly 146 * @access private 147 */ 148 function _rewrite_col_compare($args) 149 { 150 if (sizeof($args) == 4) 151 { 152 if ($args[2] == '=') 153 { 154 return '(' . $args[0] . ' OR (' . $args[1] . ' is NULL AND ' . $args[3] . ' is NULL))'; 155 } 156 else if ($args[2] == '<>') 157 { 158 // really just a fancy way of saying foo <> bar or (foo is NULL XOR bar is NULL) but SQL has no XOR :P 159 return '(' . $args[0] . ' OR ((' . $args[1] . ' is NULL AND ' . $args[3] . ' is NOT NULL) OR (' . $args[1] . ' is NOT NULL AND ' . $args[3] . ' is NULL)))'; 160 } 161 } 162 else 163 { 164 return $this->_rewrite_where($args[0]); 165 } 166 } 167 168 /** 169 * Oracle specific code to handle it's lack of sanity 170 * @access private 171 */ 172 function _rewrite_where($where_clause) 173 { 174 preg_match_all('/\s*(AND|OR)?\s*([\w_.()]++)\s*(?:(=|<[=>]?|>=?|LIKE)\s*((?>\'(?>[^\']++|\'\')*+\'|[\d-.()]+))|((NOT )?IN\s*\((?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]+,? ?)*+\)))/', $where_clause, $result, PREG_SET_ORDER); 175 $out = ''; 176 foreach ($result as $val) 177 { 178 if (!isset($val[5])) 179 { 180 if ($val[4] !== "''") 181 { 182 $out .= $val[0]; 183 } 184 else 185 { 186 $out .= ' ' . $val[1] . ' ' . $val[2]; 187 if ($val[3] == '=') 188 { 189 $out .= ' is NULL'; 190 } 191 else if ($val[3] == '<>') 192 { 193 $out .= ' is NOT NULL'; 194 } 195 } 196 } 197 else 198 { 199 $in_clause = array(); 200 $sub_exp = substr($val[5], strpos($val[5], '(') + 1, -1); 201 $extra = false; 202 preg_match_all('/\'(?>[^\']++|\'\')*+\'|[\d-.]++/', $sub_exp, $sub_vals, PREG_PATTERN_ORDER); 203 $i = 0; 204 foreach ($sub_vals[0] as $sub_val) 205 { 206 // two things: 207 // 1) This determines if an empty string was in the IN clausing, making us turn it into a NULL comparison 208 // 2) This fixes the 1000 list limit that Oracle has (ORA-01795) 209 if ($sub_val !== "''") 210 { 211 $in_clause[(int) $i++/1000][] = $sub_val; 212 } 213 else 214 { 215 $extra = true; 216 } 217 } 218 if (!$extra && $i < 1000) 219 { 220 $out .= $val[0]; 221 } 222 else 223 { 224 $out .= ' ' . $val[1] . '('; 225 $in_array = array(); 226 227 // constuct each IN() clause 228 foreach ($in_clause as $in_values) 229 { 230 $in_array[] = $val[2] . ' ' . (isset($val[6]) ? $val[6] : '') . 'IN(' . implode(', ', $in_values) . ')'; 231 } 232 233 // Join the IN() clauses against a few ORs (IN is just a nicer OR anyway) 234 $out .= implode(' OR ', $in_array); 235 236 // handle the empty string case 237 if ($extra) 238 { 239 $out .= ' OR ' . $val[2] . ' is ' . (isset($val[6]) ? $val[6] : '') . 'NULL'; 240 } 241 $out .= ')'; 242 243 unset($in_array, $in_clause); 244 } 245 } 246 } 247 248 return $out; 249 } 250 251 /** 252 * Base query method 253 * 254 * @param string $query Contains the SQL query which shall be executed 255 * @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache 256 * @return mixed When casted to bool the returned value returns true on success and false on failure 257 * 258 * @access public 259 */ 260 function sql_query($query = '', $cache_ttl = 0) 261 { 262 if ($query != '') 263 { 264 global $cache; 265 266 // EXPLAIN only in extra debug mode 267 if (defined('DEBUG_EXTRA')) 268 { 269 $this->sql_report('start', $query); 270 } 271 272 $this->last_query_text = $query; 273 $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; 274 $this->sql_add_num_queries($this->query_result); 275 276 if ($this->query_result === false) 277 { 278 $in_transaction = false; 279 if (!$this->transaction) 280 { 281 $this->sql_transaction('begin'); 282 } 283 else 284 { 285 $in_transaction = true; 286 } 287 288 $array = array(); 289 290 // We overcome Oracle's 4000 char limit by binding vars 291 if (strlen($query) > 4000) 292 { 293 if (preg_match('/^(INSERT INTO[^(]++)\\(([^()]+)\\) VALUES[^(]++\\((.*?)\\)$/sU', $query, $regs)) 294 { 295 if (strlen($regs[3]) > 4000) 296 { 297 $cols = explode(', ', $regs[2]); 298 299 preg_match_all('/\'(?:[^\']++|\'\')*+\'|[\d-.]+/', $regs[3], $vals, PREG_PATTERN_ORDER); 300 301/* The code inside this comment block breaks clob handling, but does allow the 302 database restore script to work. If you want to allow no posts longer than 4KB 303 and/or need the db restore script, uncomment this. 304 305 306 if (sizeof($cols) !== sizeof($vals)) 307 { 308 // Try to replace some common data we know is from our restore script or from other sources 309 $regs[3] = str_replace("'||chr(47)||'", '/', $regs[3]); 310 $_vals = explode(', ', $regs[3]); 311 312 $vals = array(); 313 $is_in_val = false; 314 $i = 0; 315 $string = ''; 316 317 foreach ($_vals as $value) 318 { 319 if (strpos($value, "'") === false && !$is_in_val) 320 { 321 $vals[$i++] = $value; 322 continue; 323 } 324 325 if (substr($value, -1) === "'") 326 { 327 $vals[$i] = $string . (($is_in_val) ? ', ' : '') . $value; 328 $string = ''; 329 $is_in_val = false; 330 331 if ($vals[$i][0] !== "'") 332 { 333 $vals[$i] = "''" . $vals[$i]; 334 } 335 $i++; 336 continue; 337 } 338 else 339 { 340 $string .= (($is_in_val) ? ', ' : '') . $value; 341 $is_in_val = true; 342 } 343 } 344 345 if ($string) 346 { 347 // New value if cols != value 348 $vals[(sizeof($cols) !== sizeof($vals)) ? $i : $i - 1] .= $string; 349 } 350 351 $vals = array(0 => $vals); 352 } 353*/ 354 355 $inserts = $vals[0]; 356 unset($vals); 357 358 foreach ($inserts as $key => $value) 359 { 360 if (!empty($value) && $value[0] === "'" && strlen($value) > 4002) // check to see if this thing is greater than the max + 'x2 361 { 362 $inserts[$key] = ':' . strtoupper($cols[$key]); 363 $array[$inserts[$key]] = str_replace("''", "'", substr($value, 1, -1)); 364 } 365 } 366 367 $query = $regs[1] . '(' . $regs[2] . ') VALUES (' . implode(', ', $inserts) . ')'; 368 } 369 } 370 else if (preg_match_all('/^(UPDATE [\\w_]++\\s+SET )([\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+)(?:,\\s*[\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+))*+)\\s+(WHERE.*)$/s', $query, $data, PREG_SET_ORDER)) 371 { 372 if (strlen($data[0][2]) > 4000) 373 { 374 $update = $data[0][1]; 375 $where = $data[0][3]; 376 preg_match_all('/([\\w_]++)\\s*=\\s*(\'(?:[^\']++|\'\')*+\'|[\d-.]++)/', $data[0][2], $temp, PREG_SET_ORDER); 377 unset($data); 378 379 $cols = array(); 380 foreach ($temp as $value) 381 { 382 if (!empty($value[2]) && $value[2][0] === "'" && strlen($value[2]) > 4002) // check to see if this thing is greater than the max + 'x2 383 { 384 $cols[] = $value[1] . '=:' . strtoupper($value[1]); 385 $array[$value[1]] = str_replace("''", "'", substr($value[2], 1, -1)); 386 } 387 else 388 { 389 $cols[] = $value[1] . '=' . $value[2]; 390 } 391 } 392 393 $query = $update . implode(', ', $cols) . ' ' . $where; 394 unset($cols); 395 } 396 } 397 } 398 399 switch (substr($query, 0, 6)) 400 { 401 case 'DELETE': 402 if (preg_match('/^(DELETE FROM [\w_]++ WHERE)((?:\s*(?:AND|OR)?\s*[\w_]+\s*(?:(?:=|<>)\s*(?>\'(?>[^\']++|\'\')*+\'|[\d-.]+)|(?:NOT )?IN\s*\((?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]+,? ?)*+\)))*+)$/', $query, $regs)) 403 { 404 $query = $regs[1] . $this->_rewrite_where($regs[2]); 405 unset($regs); 406 } 407 break; 408 409 case 'UPDATE': 410 if (preg_match('/^(UPDATE [\\w_]++\\s+SET [\\w_]+\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]++|:\w++)(?:, [\\w_]+\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]++|:\w++))*+\\s+WHERE)(.*)$/s', $query, $regs)) 411 { 412 $query = $regs[1] . $this->_rewrite_where($regs[2]); 413 unset($regs); 414 } 415 break; 416 417 case 'SELECT': 418 $query = preg_replace_callback('/([\w_.]++)\s*(?:(=|<>)\s*(?>\'(?>[^\']++|\'\')*+\'|[\d-.]++|([\w_.]++))|(?:NOT )?IN\s*\((?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]++,? ?)*+\))/', array($this, '_rewrite_col_compare'), $query); 419 break; 420 } 421 422 $this->query_result = @ociparse($this->db_connect_id, $query); 423 424 foreach ($array as $key => $value) 425 { 426 @ocibindbyname($this->query_result, $key, $array[$key], -1); 427 } 428 429 $success = @ociexecute($this->query_result, OCI_DEFAULT); 430 431 if (!$success) 432 { 433 $this->sql_error($query); 434 $this->query_result = false; 435 } 436 else 437 { 438 if (!$in_transaction) 439 { 440 $this->sql_transaction('commit'); 441 } 442 } 443 444 if (defined('DEBUG_EXTRA')) 445 { 446 $this->sql_report('stop', $query); 447 } 448 449 if ($cache_ttl && method_exists($cache, 'sql_save')) 450 { 451 $this->open_queries[(int) $this->query_result] = $this->query_result; 452 $cache->sql_save($query, $this->query_result, $cache_ttl); 453 } 454 else if (strpos($query, 'SELECT') === 0 && $this->query_result) 455 { 456 $this->open_queries[(int) $this->query_result] = $this->query_result; 457 } 458 } 459 else if (defined('DEBUG_EXTRA')) 460 { 461 $this->sql_report('fromcache', $query); 462 } 463 } 464 else 465 { 466 return false; 467 } 468 469 return $this->query_result; 470 } 471 472 /** 473 * Build LIMIT query 474 */ 475 function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) 476 { 477 $this->query_result = false; 478 479 $query = 'SELECT * FROM (SELECT /*+ FIRST_ROWS */ rownum AS xrownum, a.* FROM (' . $query . ') a WHERE rownum <= ' . ($offset + $total) . ') WHERE xrownum >= ' . $offset; 480 481 return $this->sql_query($query, $cache_ttl); 482 } 483 484 /** 485 * Return number of affected rows 486 */ 487 function sql_affectedrows() 488 { 489 return ($this->query_result) ? @ocirowcount($this->query_result) : false; 490 } 491 492 /** 493 * Fetch current row 494 */ 495 function sql_fetchrow($query_id = false) 496 { 497 global $cache; 498 499 if ($query_id === false) 500 { 501 $query_id = $this->query_result; 502 } 503 504 if (isset($cache->sql_rowset[$query_id])) 505 { 506 return $cache->sql_fetchrow($query_id); 507 } 508 509 if ($query_id !== false) 510 { 511 $row = array(); 512 $result = @ocifetchinto($query_id, $row, OCI_ASSOC + OCI_RETURN_NULLS); 513 514 if (!$result || !$row) 515 { 516 return false; 517 } 518 519 $result_row = array(); 520 foreach ($row as $key => $value) 521 { 522 // Oracle treats empty strings as null 523 if (is_null($value)) 524 { 525 $value = ''; 526 } 527 528 // OCI->CLOB? 529 if (is_object($value)) 530 { 531 $value = $value->load(); 532 } 533 534 $result_row[strtolower($key)] = $value; 535 } 536 537 return $result_row; 538 } 539 540 return false; 541 } 542 543 /** 544 * Seek to given row number 545 * rownum is zero-based 546 */ 547 function sql_rowseek($rownum, &$query_id) 548 { 549 global $cache; 550 551 if ($query_id === false) 552 { 553 $query_id = $this->query_result; 554 } 555 556 if (isset($cache->sql_rowset[$query_id])) 557 { 558 return $cache->sql_rowseek($rownum, $query_id); 559 } 560 561 if ($query_id === false) 562 { 563 return false; 564 } 565 566 // Reset internal pointer 567 @ociexecute($query_id, OCI_DEFAULT); 568 569 // We do not fetch the row for rownum == 0 because then the next resultset would be the second row 570 for ($i = 0; $i < $rownum; $i++) 571 { 572 if (!$this->sql_fetchrow($query_id)) 573 { 574 return false; 575 } 576 } 577 578 return true; 579 } 580 581 /** 582 * Get last inserted id after insert statement 583 */ 584 function sql_nextid() 585 { 586 $query_id = $this->query_result; 587 588 if ($query_id !== false && $this->last_query_text != '') 589 { 590 if (preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename)) 591 { 592 $query = 'SELECT ' . $tablename[1] . '_seq.currval FROM DUAL'; 593 $stmt = @ociparse($this->db_connect_id, $query); 594 @ociexecute($stmt, OCI_DEFAULT); 595 596 $temp_result = @ocifetchinto($stmt, $temp_array, OCI_ASSOC + OCI_RETURN_NULLS); 597 @ocifreestatement($stmt); 598 599 if ($temp_result) 600 { 601 return $temp_array['CURRVAL']; 602 } 603 else 604 { 605 return false; 606 } 607 } 608 } 609 610 return false; 611 } 612 613 /** 614 * Free sql result 615 */ 616 function sql_freeresult($query_id = false) 617 { 618 global $cache; 619 620 if ($query_id === false) 621 { 622 $query_id = $this->query_result; 623 } 624 625 if (isset($cache->sql_rowset[$query_id])) 626 { 627 return $cache->sql_freeresult($query_id); 628 } 629 630 if (isset($this->open_queries[(int) $query_id])) 631 { 632 unset($this->open_queries[(int) $query_id]); 633 return @ocifreestatement($query_id); 634 } 635 636 return false; 637 } 638 639 /** 640 * Escape string used in sql query 641 */ 642 function sql_escape($msg) 643 { 644 return str_replace(array("'", "\0"), array("''", ''), $msg); 645 } 646 647 /** 648 * Build LIKE expression 649 * @access private 650 */ 651 function _sql_like_expression($expression) 652 { 653 return $expression . " ESCAPE '\\'"; 654 } 655 656 function _sql_custom_build($stage, $data) 657 { 658 return $data; 659 } 660 661 function _sql_bit_and($column_name, $bit, $compare = '') 662 { 663 return 'BITAND(' . $column_name . ', ' . (1 << $bit) . ')' . (($compare) ? ' ' . $compare : ''); 664 } 665 666 function _sql_bit_or($column_name, $bit, $compare = '') 667 { 668 return 'BITOR(' . $column_name . ', ' . (1 << $bit) . ')' . (($compare) ? ' ' . $compare : ''); 669 } 670 671 /** 672 * return sql error array 673 * @access private 674 */ 675 function _sql_error() 676 { 677 if (function_exists('ocierror')) 678 { 679 $error = @ocierror(); 680 $error = (!$error) ? @ocierror($this->query_result) : $error; 681 $error = (!$error) ? @ocierror($this->db_connect_id) : $error; 682 683 if ($error) 684 { 685 $this->last_error_result = $error; 686 } 687 else 688 { 689 $error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array(); 690 } 691 } 692 else 693 { 694 $error = array( 695 'message' => $this->connect_error, 696 'code' => '', 697 ); 698 } 699 700 return $error; 701 } 702 703 /** 704 * Close sql connection 705 * @access private 706 */ 707 function _sql_close() 708 { 709 return @ocilogoff($this->db_connect_id); 710 } 711 712 /** 713 * Build db-specific report 714 * @access private 715 */ 716 function _sql_report($mode, $query = '') 717 { 718 switch ($mode) 719 { 720 case 'start': 721 722 $html_table = false; 723 724 // Grab a plan table, any will do 725 $sql = "SELECT table_name 726 FROM USER_TABLES 727 WHERE table_name LIKE '%PLAN_TABLE%'"; 728 $stmt = ociparse($this->db_connect_id, $sql); 729 ociexecute($stmt); 730 $result = array(); 731 732 if (ocifetchinto($stmt, $result, OCI_ASSOC + OCI_RETURN_NULLS)) 733 { 734 $table = $result['TABLE_NAME']; 735 736 // This is the statement_id that will allow us to track the plan 737 $statement_id = substr(md5($query), 0, 30); 738 739 // Remove any stale plans 740 $stmt2 = ociparse($this->db_connect_id, "DELETE FROM $table WHERE statement_id='$statement_id'"); 741 ociexecute($stmt2); 742 ocifreestatement($stmt2); 743 744 // Explain the plan 745 $sql = "EXPLAIN PLAN 746 SET STATEMENT_ID = '$statement_id' 747 FOR $query"; 748 $stmt2 = ociparse($this->db_connect_id, $sql); 749 ociexecute($stmt2); 750 ocifreestatement($stmt2); 751 752 // Get the data from the plan 753 $sql = "SELECT operation, options, object_name, object_type, cardinality, cost 754 FROM plan_table 755 START WITH id = 0 AND statement_id = '$statement_id' 756 CONNECT BY PRIOR id = parent_id 757 AND statement_id = '$statement_id'"; 758 $stmt2 = ociparse($this->db_connect_id, $sql); 759 ociexecute($stmt2); 760 761 $row = array(); 762 while (ocifetchinto($stmt2, $row, OCI_ASSOC + OCI_RETURN_NULLS)) 763 { 764 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row); 765 } 766 767 ocifreestatement($stmt2); 768 769 // Remove the plan we just made, we delete them on request anyway 770 $stmt2 = ociparse($this->db_connect_id, "DELETE FROM $table WHERE statement_id='$statement_id'"); 771 ociexecute($stmt2); 772 ocifreestatement($stmt2); 773 } 774 775 ocifreestatement($stmt); 776 777 if ($html_table) 778 { 779 $this->html_hold .= '</table>'; 780 } 781 782 break; 783 784 case 'fromcache': 785 $endtime = explode(' ', microtime()); 786 $endtime = $endtime[0] + $endtime[1]; 787 788 $result = @ociparse($this->db_connect_id, $query); 789 $success = @ociexecute($result, OCI_DEFAULT); 790 $row = array(); 791 792 while (@ocifetchinto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS)) 793 { 794 // Take the time spent on parsing rows into account 795 } 796 @ocifreestatement($result); 797 798 $splittime = explode(' ', microtime()); 799 $splittime = $splittime[0] + $splittime[1]; 800 801 $this->sql_report('record_fromcache', $query, $endtime, $splittime); 802 803 break; 804 } 805 } 806} 807 808?>