video/system/database/drivers/oci8/oci8_driver.php

   1 <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
   2 /**
   3  * CodeIgniter
   4  *
   5  * An open source application development framework for PHP 5.1.6 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008 - 2011, 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  * @author        Kelly McArdle
  39  *
  40  */
  41
  42 class CI_DB_oci8_driver extends CI_DB {
  43
  44         var $dbdriver = 'oci8';
  45
  46         // The character used for excaping
  47         var $_escape_char = '"';
  48
  49         // clause and character used for LIKE escape sequences
  50         var $_like_escape_str = " escape '%s' ";
  51         var $_like_escape_chr = '!';
  52
  53         /**
  54          * The syntax to count rows is slightly different across different
  55          * database engines, so this string appears in each driver and is
  56          * used for the count_all() and count_all_results() functions.
  57          */
  58         var $_count_string = "SELECT COUNT(1) AS ";
  59         var $_random_keyword = ' ASC'; // not currently supported
  60
  61         // Set "auto commit" by default
  62         var $_commit = OCI_COMMIT_ON_SUCCESS;
  63
  64         // need to track statement id and cursor id
  65         var $stmt_id;
  66         var $curs_id;
  67
  68         // if we use a limit, we will add a field that will
  69         // throw off num_fields later
  70         var $limit_used;
  71
  72         /**
  73          * Non-persistent database connection
  74          *
  75          * @access  private called by the base class
  76          * @return  resource
  77          */
  78         public function db_connect()
  79         {
  80                 return @oci_connect($this->username, $this->password, $this->hostname, $this->char_set);
  81         }
  82
  83         // --------------------------------------------------------------------
  84
  85         /**
  86          * Persistent database connection
  87          *
  88          * @access  private called by the base class
  89          * @return  resource
  90          */
  91         public function db_pconnect()
  92         {
  93                 return @oci_pconnect($this->username, $this->password, $this->hostname, $this->char_set);
  94         }
  95
  96         // --------------------------------------------------------------------
  97
  98         /**
  99          * Reconnect
 100          *
 101          * Keep / reestablish the db connection if no queries have been
 102          * sent for a length of time exceeding the server's idle timeout
 103          *
 104          * @access      public
 105          * @return      void
 106          */
 107         public function reconnect()
 108         {
 109                 // not implemented in oracle
 110                 return;
 111         }
 112
 113         // --------------------------------------------------------------------
 114
 115         /**
 116          * Select the database
 117          *
 118          * @access  private called by the base class
 119          * @return  resource
 120          */
 121         public function db_select()
 122         {
 123                 // Not in Oracle - schemas are actually usernames
 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         public 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  protected
 149          * @return  string
 150          */
 151         protected function _version()
 152         {
 153                 return oci_server_version($this->conn_id);
 154         }
 155
 156         // --------------------------------------------------------------------
 157
 158         /**
 159          * Execute the query
 160          *
 161          * @access  protected  called by the base class
 162          * @param   string  an SQL query
 163          * @return  resource
 164          */
 165         protected 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                 oci_set_prefetch($this->stmt_id, 1000);
 172                 return @oci_execute($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         private function _set_stmt_id($sql)
 183         {
 184                 if ( ! is_resource($this->stmt_id))
 185                 {
 186                         $this->stmt_id = oci_parse($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         private 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         public function get_cursor()
 215         {
 216                 $this->curs_id = oci_new_cursor($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         public 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         private 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                         oci_bind_by_name($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         public 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         public 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 = oci_commit($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         public 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 = oci_rollback($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         public 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                 $str = remove_invisible_characters($str);
 407
 408                 // escape LIKE condition wildcards
 409                 if ($like === TRUE)
 410                 {
 411                         $str = str_replace(     array('%', '_', $this->_like_escape_chr),
 412                                                                 array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
 413                                                                 $str);
 414                 }
 415
 416                 return $str;
 417         }
 418
 419         // --------------------------------------------------------------------
 420
 421         /**
 422          * Affected Rows
 423          *
 424          * @access  public
 425          * @return  integer
 426          */
 427         public function affected_rows()
 428         {
 429                 return @oci_num_rows($this->stmt_id);
 430         }
 431
 432         // --------------------------------------------------------------------
 433
 434         /**
 435          * Insert ID
 436          *
 437          * @access  public
 438          * @return  integer
 439          */
 440         public function insert_id()
 441         {
 442                 // not supported in oracle
 443                 return $this->display_error('db_unsupported_function');
 444         }
 445
 446         // --------------------------------------------------------------------
 447
 448         /**
 449          * "Count All" query
 450          *
 451          * Generates a platform-specific query string that counts all records in
 452          * the specified database
 453          *
 454          * @access  public
 455          * @param   string
 456          * @return  string
 457          */
 458         public function count_all($table = '')
 459         {
 460                 if ($table == '')
 461                 {
 462                         return 0;
 463                 }
 464
 465                 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
 466
 467                 if ($query == FALSE)
 468                 {
 469                         return 0;
 470                 }
 471
 472                 $row = $query->row();
 473                 $this->_reset_select();
 474                 return (int) $row->numrows;
 475         }
 476
 477         // --------------------------------------------------------------------
 478
 479         /**
 480          * Show table query
 481          *
 482          * Generates a platform-specific query string so that the table names can be fetched
 483          *
 484          * @access      protected
 485          * @param       boolean
 486          * @return      string
 487          */
 488         protected function _list_tables($prefix_limit = FALSE)
 489         {
 490                 $sql = "SELECT TABLE_NAME FROM ALL_TABLES";
 491
 492                 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
 493                 {
 494                         $sql .= " WHERE TABLE_NAME LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr);
 495                 }
 496
 497                 return $sql;
 498         }
 499
 500         // --------------------------------------------------------------------
 501
 502         /**
 503          * Show column query
 504          *
 505          * Generates a platform-specific query string so that the column names can be fetched
 506          *
 507          * @access  protected
 508          * @param   string  the table name
 509          * @return  string
 510          */
 511         protected function _list_columns($table = '')
 512         {
 513                 return "SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '$table'";
 514         }
 515
 516         // --------------------------------------------------------------------
 517
 518         /**
 519          * Field data query
 520          *
 521          * Generates a platform-specific query so that the column data can be retrieved
 522          *
 523          * @access  public
 524          * @param   string  the table name
 525          * @return  object
 526          */
 527         protected function _field_data($table)
 528         {
 529                 return "SELECT * FROM ".$table." where rownum = 1";
 530         }
 531
 532         // --------------------------------------------------------------------
 533
 534         /**
 535          * The error message string
 536          *
 537          * @access  protected
 538          * @return  string
 539          */
 540         protected function _error_message()
 541         {
 542                 // If the error was during connection, no conn_id should be passed
 543                 $error = is_resource($this->conn_id) ? oci_error($this->conn_id) : oci_error();
 544                 return $error['message'];
 545         }
 546
 547         // --------------------------------------------------------------------
 548
 549         /**
 550          * The error message number
 551          *
 552          * @access  protected
 553          * @return  integer
 554          */
 555         protected function _error_number()
 556         {
 557                 // Same as _error_message()
 558                 $error = is_resource($this->conn_id) ? oci_error($this->conn_id) : oci_error();
 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      protected
 570          * @param       string
 571          * @return      string
 572          */
 573         protected 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      protected
 613          * @param       type
 614          * @return      type
 615          */
 616         protected 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         protected function _insert($table, $keys, $values)
 640         {
 641                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 642         }
 643
 644         // --------------------------------------------------------------------
 645
 646         /**
 647          * Insert_batch statement
 648          *
 649          * Generates a platform-specific insert string from the supplied data
 650          *
 651          * @access      protected
 652          * @param       string  the table name
 653          * @param       array   the insert keys
 654          * @param       array   the insert values
 655          * @return      string
 656          */
 657         protected function _insert_batch($table, $keys, $values)
 658         {
 659                 $keys = implode(', ', $keys);
 660                 $sql = "INSERT ALL\n";
 661
 662                 for ($i = 0, $c = count($values); $i < $c; $i++)
 663                 {
 664                         $sql .= '       INTO ' . $table . ' (' . $keys . ') VALUES ' . $values[$i] . "\n";
 665                 }
 666
 667                 $sql .= 'SELECT * FROM dual';
 668
 669                 return $sql;
 670         }
 671
 672         // --------------------------------------------------------------------
 673
 674         /**
 675          * Update statement
 676          *
 677          * Generates a platform-specific update string from the supplied data
 678          *
 679          * @access      protected
 680          * @param       string  the table name
 681          * @param       array   the update data
 682          * @param       array   the where clause
 683          * @param       array   the orderby clause
 684          * @param       array   the limit clause
 685          * @return      string
 686          */
 687         protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
 688         {
 689                 foreach ($values as $key => $val)
 690                 {
 691                         $valstr[] = $key." = ".$val;
 692                 }
 693
 694                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 695
 696                 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
 697
 698                 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
 699
 700                 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
 701
 702                 $sql .= $orderby.$limit;
 703
 704                 return $sql;
 705         }
 706
 707         // --------------------------------------------------------------------
 708
 709         /**
 710          * Truncate statement
 711          *
 712          * Generates a platform-specific truncate string from the supplied data
 713          * If the database does not support the truncate() command
 714          * This function maps to "DELETE FROM table"
 715          *
 716          * @access      protected
 717          * @param       string  the table name
 718          * @return      string
 719          */
 720         protected function _truncate($table)
 721         {
 722                 return "TRUNCATE TABLE ".$table;
 723         }
 724
 725         // --------------------------------------------------------------------
 726
 727         /**
 728          * Delete statement
 729          *
 730          * Generates a platform-specific delete string from the supplied data
 731          *
 732          * @access      protected
 733          * @param       string  the table name
 734          * @param       array   the where clause
 735          * @param       string  the limit clause
 736          * @return      string
 737          */
 738         protected function _delete($table, $where = array(), $like = array(), $limit = FALSE)
 739         {
 740                 $conditions = '';
 741
 742                 if (count($where) > 0 OR count($like) > 0)
 743                 {
 744                         $conditions = "\nWHERE ";
 745                         $conditions .= implode("\n", $this->ar_where);
 746
 747                         if (count($where) > 0 && count($like) > 0)
 748                         {
 749                                 $conditions .= " AND ";
 750                         }
 751                         $conditions .= implode("\n", $like);
 752                 }
 753
 754                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 755
 756                 return "DELETE FROM ".$table.$conditions.$limit;
 757         }
 758
 759         // --------------------------------------------------------------------
 760
 761         /**
 762          * Limit string
 763          *
 764          * Generates a platform-specific LIMIT clause
 765          *
 766          * @access  protected
 767          * @param   string  the sql query string
 768          * @param   integer the number of rows to limit the query to
 769          * @param   integer the offset value
 770          * @return  string
 771          */
 772         protected function _limit($sql, $limit, $offset)
 773         {
 774                 $limit = $offset + $limit;
 775                 $newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)";
 776
 777                 if ($offset != 0)
 778                 {
 779                         $newsql .= " WHERE rnum >= $offset";
 780                 }
 781
 782                 // remember that we used limits
 783                 $this->limit_used = TRUE;
 784
 785                 return $newsql;
 786         }
 787
 788         // --------------------------------------------------------------------
 789
 790         /**
 791          * Close DB Connection
 792          *
 793          * @access  protected
 794          * @param   resource
 795          * @return  void
 796          */
 797         protected function _close($conn_id)
 798         {
 799                 @oci_close($conn_id);
 800         }
 801
 802
 803 }
 804
 805
 806
 807 /* End of file oci8_driver.php */
 808 /* Location: ./system/database/drivers/oci8/oci8_driver.php */