[ Index ]

PHP Cross Reference of Unnamed Project

title

Body

[close]

/includes/db/ -> oracle.php (source)

   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  */
  14  if (!defined('IN_PHPBB'))
  15  {
  16      exit;
  17  }
  18  
  19  include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
  20  
  21  /**
  22  * Oracle Database Abstraction Layer
  23  * @package dbal
  24  */
  25  class 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  ?>


Generated: Wed Oct 2 15:03:47 2013 Cross-referenced by PHPXref 0.7.1