/halogy/database/drivers/oci8/oci8_driver.php
PHP | 780 lines | 299 code | 119 blank | 362 comment | 36 complexity | c0d1b430d4d30b2be066824acd04982f MD5 | raw file
1<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 2/** 3 * CodeIgniter 4 * 5 * An open source application development framework for PHP 4.3.2 or newer 6 * 7 * @package CodeIgniter 8 * @author ExpressionEngine Dev Team 9 * @copyright Copyright (c) 2008 - 2009, EllisLab, Inc. 10 * @license http://codeigniter.com/user_guide/license.html 11 * @link http://codeigniter.com 12 * @since Version 1.0 13 * @filesource 14 */ 15 16// ------------------------------------------------------------------------ 17 18/** 19 * oci8 Database Adapter Class 20 * 21 * Note: _DB is an extender class that the app controller 22 * creates dynamically based on whether the active record 23 * class is being used or not. 24 * 25 * @package CodeIgniter 26 * @subpackage Drivers 27 * @category Database 28 * @author ExpressionEngine Dev Team 29 * @link http://codeigniter.com/user_guide/database/ 30 */ 31 32/** 33 * oci8 Database Adapter Class 34 * 35 * This is a modification of the DB_driver class to 36 * permit access to oracle databases 37 * 38 * NOTE: this uses the PHP 4 oci methods 39 * 40 * @author Kelly McArdle 41 * 42 */ 43 44class CI_DB_oci8_driver extends CI_DB { 45 46 var $dbdriver = 'oci8'; 47 48 // The character used for excaping 49 var $_escape_char = '"'; 50 51 // clause and character used for LIKE escape sequences 52 var $_like_escape_str = " escape '%s' "; 53 var $_like_escape_chr = '!'; 54 55 /** 56 * The syntax to count rows is slightly different across different 57 * database engines, so this string appears in each driver and is 58 * used for the count_all() and count_all_results() functions. 59 */ 60 var $_count_string = "SELECT COUNT(1) AS "; 61 var $_random_keyword = ' ASC'; // not currently supported 62 63 // Set "auto commit" by default 64 var $_commit = OCI_COMMIT_ON_SUCCESS; 65 66 // need to track statement id and cursor id 67 var $stmt_id; 68 var $curs_id; 69 70 // if we use a limit, we will add a field that will 71 // throw off num_fields later 72 var $limit_used; 73 74 /** 75 * Non-persistent database connection 76 * 77 * @access private called by the base class 78 * @return resource 79 */ 80 function db_connect() 81 { 82 return @ocilogon($this->username, $this->password, $this->hostname); 83 } 84 85 // -------------------------------------------------------------------- 86 87 /** 88 * Persistent database connection 89 * 90 * @access private called by the base class 91 * @return resource 92 */ 93 function db_pconnect() 94 { 95 return @ociplogon($this->username, $this->password, $this->hostname); 96 } 97 98 // -------------------------------------------------------------------- 99 100 /** 101 * Reconnect 102 * 103 * Keep / reestablish the db connection if no queries have been 104 * sent for a length of time exceeding the server's idle timeout 105 * 106 * @access public 107 * @return void 108 */ 109 function reconnect() 110 { 111 // not implemented in oracle 112 } 113 114 // -------------------------------------------------------------------- 115 116 /** 117 * Select the database 118 * 119 * @access private called by the base class 120 * @return resource 121 */ 122 function db_select() 123 { 124 return TRUE; 125 } 126 127 // -------------------------------------------------------------------- 128 129 /** 130 * Set client character set 131 * 132 * @access public 133 * @param string 134 * @param string 135 * @return resource 136 */ 137 function db_set_charset($charset, $collation) 138 { 139 // @todo - add support if needed 140 return TRUE; 141 } 142 143 // -------------------------------------------------------------------- 144 145 /** 146 * Version number query string 147 * 148 * @access public 149 * @return string 150 */ 151 function _version() 152 { 153 return ociserverversion($this->conn_id); 154 } 155 156 // -------------------------------------------------------------------- 157 158 /** 159 * Execute the query 160 * 161 * @access private called by the base class 162 * @param string an SQL query 163 * @return resource 164 */ 165 function _execute($sql) 166 { 167 // oracle must parse the query before it is run. All of the actions with 168 // the query are based on the statement id returned by ociparse 169 $this->stmt_id = FALSE; 170 $this->_set_stmt_id($sql); 171 ocisetprefetch($this->stmt_id, 1000); 172 return @ociexecute($this->stmt_id, $this->_commit); 173 } 174 175 /** 176 * Generate a statement ID 177 * 178 * @access private 179 * @param string an SQL query 180 * @return none 181 */ 182 function _set_stmt_id($sql) 183 { 184 if ( ! is_resource($this->stmt_id)) 185 { 186 $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql)); 187 } 188 } 189 190 // -------------------------------------------------------------------- 191 192 /** 193 * Prep the query 194 * 195 * If needed, each database adapter can prep the query string 196 * 197 * @access private called by execute() 198 * @param string an SQL query 199 * @return string 200 */ 201 function _prep_query($sql) 202 { 203 return $sql; 204 } 205 206 // -------------------------------------------------------------------- 207 208 /** 209 * getCursor. Returns a cursor from the datbase 210 * 211 * @access public 212 * @return cursor id 213 */ 214 function get_cursor() 215 { 216 $this->curs_id = ocinewcursor($this->conn_id); 217 return $this->curs_id; 218 } 219 220 // -------------------------------------------------------------------- 221 222 /** 223 * Stored Procedure. Executes a stored procedure 224 * 225 * @access public 226 * @param package package stored procedure is in 227 * @param procedure stored procedure to execute 228 * @param params array of parameters 229 * @return array 230 * 231 * params array keys 232 * 233 * KEY OPTIONAL NOTES 234 * name no the name of the parameter should be in :<param_name> format 235 * value no the value of the parameter. If this is an OUT or IN OUT parameter, 236 * this should be a reference to a variable 237 * type yes the type of the parameter 238 * length yes the max size of the parameter 239 */ 240 function stored_procedure($package, $procedure, $params) 241 { 242 if ($package == '' OR $procedure == '' OR ! is_array($params)) 243 { 244 if ($this->db_debug) 245 { 246 log_message('error', 'Invalid query: '.$package.'.'.$procedure); 247 return $this->display_error('db_invalid_query'); 248 } 249 return FALSE; 250 } 251 252 // build the query string 253 $sql = "begin $package.$procedure("; 254 255 $have_cursor = FALSE; 256 foreach($params as $param) 257 { 258 $sql .= $param['name'] . ","; 259 260 if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR)) 261 { 262 $have_cursor = TRUE; 263 } 264 } 265 $sql = trim($sql, ",") . "); end;"; 266 267 $this->stmt_id = FALSE; 268 $this->_set_stmt_id($sql); 269 $this->_bind_params($params); 270 $this->query($sql, FALSE, $have_cursor); 271 } 272 273 // -------------------------------------------------------------------- 274 275 /** 276 * Bind parameters 277 * 278 * @access private 279 * @return none 280 */ 281 function _bind_params($params) 282 { 283 if ( ! is_array($params) OR ! is_resource($this->stmt_id)) 284 { 285 return; 286 } 287 288 foreach ($params as $param) 289 { 290 foreach (array('name', 'value', 'type', 'length') as $val) 291 { 292 if ( ! isset($param[$val])) 293 { 294 $param[$val] = ''; 295 } 296 } 297 298 ocibindbyname($this->stmt_id, $param['name'], $param['value'], $param['length'], $param['type']); 299 } 300 } 301 302 // -------------------------------------------------------------------- 303 304 /** 305 * Begin Transaction 306 * 307 * @access public 308 * @return bool 309 */ 310 function trans_begin($test_mode = FALSE) 311 { 312 if ( ! $this->trans_enabled) 313 { 314 return TRUE; 315 } 316 317 // When transactions are nested we only begin/commit/rollback the outermost ones 318 if ($this->_trans_depth > 0) 319 { 320 return TRUE; 321 } 322 323 // Reset the transaction failure flag. 324 // If the $test_mode flag is set to TRUE transactions will be rolled back 325 // even if the queries produce a successful result. 326 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE; 327 328 $this->_commit = OCI_DEFAULT; 329 return TRUE; 330 } 331 332 // -------------------------------------------------------------------- 333 334 /** 335 * Commit Transaction 336 * 337 * @access public 338 * @return bool 339 */ 340 function trans_commit() 341 { 342 if ( ! $this->trans_enabled) 343 { 344 return TRUE; 345 } 346 347 // When transactions are nested we only begin/commit/rollback the outermost ones 348 if ($this->_trans_depth > 0) 349 { 350 return TRUE; 351 } 352 353 $ret = OCIcommit($this->conn_id); 354 $this->_commit = OCI_COMMIT_ON_SUCCESS; 355 return $ret; 356 } 357 358 // -------------------------------------------------------------------- 359 360 /** 361 * Rollback Transaction 362 * 363 * @access public 364 * @return bool 365 */ 366 function trans_rollback() 367 { 368 if ( ! $this->trans_enabled) 369 { 370 return TRUE; 371 } 372 373 // When transactions are nested we only begin/commit/rollback the outermost ones 374 if ($this->_trans_depth > 0) 375 { 376 return TRUE; 377 } 378 379 $ret = OCIrollback($this->conn_id); 380 $this->_commit = OCI_COMMIT_ON_SUCCESS; 381 return $ret; 382 } 383 384 // -------------------------------------------------------------------- 385 386 /** 387 * Escape String 388 * 389 * @access public 390 * @param string 391 * @param bool whether or not the string will be used in a LIKE condition 392 * @return string 393 */ 394 function escape_str($str, $like = FALSE) 395 { 396 if (is_array($str)) 397 { 398 foreach($str as $key => $val) 399 { 400 $str[$key] = $this->escape_str($val, $like); 401 } 402 403 return $str; 404 } 405 406 // Access the CI object 407 $CI =& get_instance(); 408 409 $str = $CI->input->_remove_invisible_characters($str); 410 411 // escape LIKE condition wildcards 412 if ($like === TRUE) 413 { 414 $str = str_replace( array('%', '_', $this->_like_escape_chr), 415 array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr), 416 $str); 417 } 418 419 return $str; 420 } 421 422 // -------------------------------------------------------------------- 423 424 /** 425 * Affected Rows 426 * 427 * @access public 428 * @return integer 429 */ 430 function affected_rows() 431 { 432 return @ocirowcount($this->stmt_id); 433 } 434 435 // -------------------------------------------------------------------- 436 437 /** 438 * Insert ID 439 * 440 * @access public 441 * @return integer 442 */ 443 function insert_id() 444 { 445 // not supported in oracle 446 return $this->display_error('db_unsupported_function'); 447 } 448 449 // -------------------------------------------------------------------- 450 451 /** 452 * "Count All" query 453 * 454 * Generates a platform-specific query string that counts all records in 455 * the specified database 456 * 457 * @access public 458 * @param string 459 * @return string 460 */ 461 function count_all($table = '') 462 { 463 if ($table == '') 464 { 465 return 0; 466 } 467 468 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); 469 470 if ($query == FALSE) 471 { 472 return 0; 473 } 474 475 $row = $query->row(); 476 return (int) $row->numrows; 477 } 478 479 // -------------------------------------------------------------------- 480 481 /** 482 * Show table query 483 * 484 * Generates a platform-specific query string so that the table names can be fetched 485 * 486 * @access private 487 * @param boolean 488 * @return string 489 */ 490 function _list_tables($prefix_limit = FALSE) 491 { 492 $sql = "SELECT TABLE_NAME FROM ALL_TABLES"; 493 494 if ($prefix_limit !== FALSE AND $this->dbprefix != '') 495 { 496 $sql .= " WHERE TABLE_NAME LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char); 497 } 498 499 return $sql; 500 } 501 502 // -------------------------------------------------------------------- 503 504 /** 505 * Show column query 506 * 507 * Generates a platform-specific query string so that the column names can be fetched 508 * 509 * @access public 510 * @param string the table name 511 * @return string 512 */ 513 function _list_columns($table = '') 514 { 515 return "SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '$table'"; 516 } 517 518 // -------------------------------------------------------------------- 519 520 /** 521 * Field data query 522 * 523 * Generates a platform-specific query so that the column data can be retrieved 524 * 525 * @access public 526 * @param string the table name 527 * @return object 528 */ 529 function _field_data($table) 530 { 531 return "SELECT * FROM ".$table." where rownum = 1"; 532 } 533 534 // -------------------------------------------------------------------- 535 536 /** 537 * The error message string 538 * 539 * @access private 540 * @return string 541 */ 542 function _error_message() 543 { 544 $error = ocierror($this->conn_id); 545 return $error['message']; 546 } 547 548 // -------------------------------------------------------------------- 549 550 /** 551 * The error message number 552 * 553 * @access private 554 * @return integer 555 */ 556 function _error_number() 557 { 558 $error = ocierror($this->conn_id); 559 return $error['code']; 560 } 561 562 // -------------------------------------------------------------------- 563 564 /** 565 * Escape the SQL Identifiers 566 * 567 * This function escapes column and table names 568 * 569 * @access private 570 * @param string 571 * @return string 572 */ 573 function _escape_identifiers($item) 574 { 575 if ($this->_escape_char == '') 576 { 577 return $item; 578 } 579 580 foreach ($this->_reserved_identifiers as $id) 581 { 582 if (strpos($item, '.'.$id) !== FALSE) 583 { 584 $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item); 585 586 // remove duplicates if the user already included the escape 587 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); 588 } 589 } 590 591 if (strpos($item, '.') !== FALSE) 592 { 593 $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; 594 } 595 else 596 { 597 $str = $this->_escape_char.$item.$this->_escape_char; 598 } 599 600 // remove duplicates if the user already included the escape 601 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); 602 } 603 604 // -------------------------------------------------------------------- 605 606 /** 607 * From Tables 608 * 609 * This function implicitly groups FROM tables so there is no confusion 610 * about operator precedence in harmony with SQL standards 611 * 612 * @access public 613 * @param type 614 * @return type 615 */ 616 function _from_tables($tables) 617 { 618 if ( ! is_array($tables)) 619 { 620 $tables = array($tables); 621 } 622 623 return implode(', ', $tables); 624 } 625 626 // -------------------------------------------------------------------- 627 628 /** 629 * Insert statement 630 * 631 * Generates a platform-specific insert string from the supplied data 632 * 633 * @access public 634 * @param string the table name 635 * @param array the insert keys 636 * @param array the insert values 637 * @return string 638 */ 639 function _insert($table, $keys, $values) 640 { 641 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; 642 } 643 644 // -------------------------------------------------------------------- 645 646 /** 647 * Update statement 648 * 649 * Generates a platform-specific update string from the supplied data 650 * 651 * @access public 652 * @param string the table name 653 * @param array the update data 654 * @param array the where clause 655 * @param array the orderby clause 656 * @param array the limit clause 657 * @return string 658 */ 659 function _update($table, $values, $where, $orderby = array(), $limit = FALSE) 660 { 661 foreach($values as $key => $val) 662 { 663 $valstr[] = $key." = ".$val; 664 } 665 666 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; 667 668 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; 669 670 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); 671 672 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; 673 674 $sql .= $orderby.$limit; 675 676 return $sql; 677 } 678 679 // -------------------------------------------------------------------- 680 681 /** 682 * Truncate statement 683 * 684 * Generates a platform-specific truncate string from the supplied data 685 * If the database does not support the truncate() command 686 * This function maps to "DELETE FROM table" 687 * 688 * @access public 689 * @param string the table name 690 * @return string 691 */ 692 function _truncate($table) 693 { 694 return "TRUNCATE TABLE ".$table; 695 } 696 697 // -------------------------------------------------------------------- 698 699 /** 700 * Delete statement 701 * 702 * Generates a platform-specific delete string from the supplied data 703 * 704 * @access public 705 * @param string the table name 706 * @param array the where clause 707 * @param string the limit clause 708 * @return string 709 */ 710 function _delete($table, $where = array(), $like = array(), $limit = FALSE) 711 { 712 $conditions = ''; 713 714 if (count($where) > 0 OR count($like) > 0) 715 { 716 $conditions = "\nWHERE "; 717 $conditions .= implode("\n", $this->ar_where); 718 719 if (count($where) > 0 && count($like) > 0) 720 { 721 $conditions .= " AND "; 722 } 723 $conditions .= implode("\n", $like); 724 } 725 726 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; 727 728 return "DELETE FROM ".$table.$conditions.$limit; 729 } 730 731 // -------------------------------------------------------------------- 732 733 /** 734 * Limit string 735 * 736 * Generates a platform-specific LIMIT clause 737 * 738 * @access public 739 * @param string the sql query string 740 * @param integer the number of rows to limit the query to 741 * @param integer the offset value 742 * @return string 743 */ 744 function _limit($sql, $limit, $offset) 745 { 746 $limit = $offset + $limit; 747 $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)"; 748 749 if ($offset != 0) 750 { 751 $newsql .= " WHERE rnum >= $offset"; 752 } 753 754 // remember that we used limits 755 $this->limit_used = TRUE; 756 757 return $newsql; 758 } 759 760 // -------------------------------------------------------------------- 761 762 /** 763 * Close DB Connection 764 * 765 * @access public 766 * @param resource 767 * @return void 768 */ 769 function _close($conn_id) 770 { 771 @ocilogoff($conn_id); 772 } 773 774 775} 776 777 778 779/* End of file oci8_driver.php */ 780/* Location: ./system/database/drivers/oci8/oci8_driver.php */