[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: PreparedStatementCommon.php,v 1.16 2005/11/13 01:30:00 gamr Exp $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://creole.phpdb.org>.
  20   */
  21  
  22  /**
  23   * Class that represents a shared code for handling emulated pre-compiled statements.
  24   * 
  25   * Many drivers do not take advantage of pre-compiling SQL statements; for these
  26   * cases the precompilation is emulated.  This emulation comes with slight penalty involved
  27   * in parsing the queries, but provides other benefits such as a cleaner object model and ability
  28   * to work with BLOB and CLOB values w/o needing special LOB-specific routines.
  29   *
  30   * @author    Hans Lellelid <hans@xmpl.org>
  31   * @version   $Revision: 1.16 $
  32   * @package   creole.common
  33   */
  34  abstract class PreparedStatementCommon {
  35  
  36      /**
  37       * The database connection.
  38       * @var Connection
  39       */ 
  40      protected $conn;
  41      
  42      /**
  43       * Max rows to retrieve from DB.
  44       * @var int
  45       */
  46      protected $limit = 0;
  47      
  48      /**
  49       * Offset at which to start processing DB rows.
  50       * "Skip X rows"
  51       * @var int
  52       */
  53      protected $offset = 0;
  54      
  55      /**
  56       * The SQL this class operates on.
  57       * @var string
  58       */
  59      protected $sql;
  60  
  61      /**
  62       * Possibly contains a cached prepared SQL Statement.
  63       * Gives an early out to replaceParams if the same
  64       * query is run multiple times without changing the
  65       * params.
  66       * @var string
  67       */
  68      protected $sql_cache;
  69  
  70      /**
  71       * Flag to set if the cache is upto date or not
  72       * @var boolean
  73       */
  74      protected $sql_cache_valid = false;
  75  
  76      /**
  77       * The string positions of the parameters in the SQL.
  78       * @var array
  79       */
  80      protected $positions;
  81  
  82  
  83      /**
  84       * Number of positions (simply to save processing).
  85       * @var int
  86       */
  87      protected $positionsCount;
  88  
  89      /**
  90       * Map of index => value for bound params.
  91       * @var array string[]
  92       */
  93      protected $boundInVars = array();    
  94      
  95      /**
  96       * Temporarily hold a ResultSet object after an execute() query.
  97       * @var ResultSet
  98       */
  99      protected $resultSet;
 100  
 101      /**
 102       * Temporary hold the affected row cound after an execute() query.
 103       * @var int
 104       */
 105      protected $updateCount;
 106      
 107      /**
 108       * Create new prepared statement instance.
 109       * 
 110       * @param object $conn Connection object
 111       * @param string $sql The SQL to work with.
 112       * @param array $positions The positions in SQL of ?'s.
 113       * @param restult $stmt If the driver supports prepared queries, then $stmt will contain the statement to use.
 114       */ 
 115      public function __construct(Connection $conn, $sql)
 116      {
 117          $this->conn = $conn;
 118          $this->sql = $sql;
 119      
 120      $this->positions = $this->parseQuery ( $sql );
 121          // save processing later in cases where we may repeatedly exec statement
 122      $this->positionsCount = count ( $this->positions );
 123      }
 124  
 125      /**
 126       * Parse the SQL query for ? positions
 127       *
 128       * @param string $sql The query to process
 129       * @return array Positions from the start of the string that ?'s appear at
 130      */
 131      protected function parseQuery ( $sql )
 132      {
 133  
 134          $positions = array();
 135      // match anything ? ' " or \ in $sql with an early out if we find nothing
 136          if ( preg_match_all ( '([\?]|[\']|[\"]|[\\\])', $sql, $matches, PREG_OFFSET_CAPTURE ) !== 0 ) {
 137                  $matches = $matches['0'];
 138                  $open = NULL;
 139          // go thru all our matches and see what we can find
 140                  for ( $i = 0, $j = count ( $matches ); $i < $j; $i++ ) {
 141                          switch ( $matches[$i]['0'] ) {
 142                  // if we already have an open " or ' then check if this is the end
 143                  // to close it or not
 144                                  case $open:
 145                                          $open = NULL;
 146                                          break;
 147                  // we have a quote, set ourselves open
 148                                  case '"':
 149                                  case "'":
 150                                          $open = $matches[$i]['0'];
 151                                          break;
 152                  // check if it is an escaped quote and skip if it is
 153                                  case '\\':
 154                                          $next_match = $matches[$i+1]['0'];
 155                                          if ( $next_match === '"' || $next_match === "'" ) {
 156                                                  $i++;
 157                                          }
 158                                          unset ( $next_match );
 159                                          break;
 160                  // we found a ?, check we arent in an open "/' first and
 161                  // add it to the position list if we arent
 162                                  default:
 163                                          if ( $open === NULL ) {
 164                                                  $positions[] = $matches[$i]['1'];
 165                                          }
 166                          }
 167                          unset ( $matches[$i] );
 168                  }
 169                  unset ( $open, $matches, $i, $j );
 170          }
 171  
 172      return $positions;
 173  
 174      }
 175  
 176      /**
 177       * @see PreparedStatement::setLimit()
 178       */
 179      public function setLimit($v)
 180      {
 181          $this->limit = (int) $v;
 182      }
 183      
 184      /**
 185       * @see PreparedStatement::getLimit()
 186       */
 187      public function getLimit()
 188      {
 189          return $this->limit;
 190      }
 191      
 192      /**
 193       * @see PreparedStatement::setOffset()
 194       */ 
 195      public function setOffset($v)
 196      {
 197          $this->offset = (int) $v;
 198      }
 199      
 200      /**
 201       * @see PreparedStatement::getOffset()
 202       */
 203      public function getOffset()
 204      {
 205          return $this->offset;
 206      }
 207      
 208      /**
 209       * @see PreparedStatement::getResultSet()
 210       */
 211      public function getResultSet()
 212      {
 213          return $this->resultSet;
 214      }
 215  
 216      /**
 217       * @see PreparedStatement::getUpdateCount()
 218       */
 219      public function getUpdateCount()
 220      {
 221          return $this->updateCount;
 222      }
 223      
 224      /**
 225       * @see PreparedStatement::getMoreResults()
 226       */
 227      public function getMoreResults()
 228      {
 229          if ($this->resultSet) $this->resultSet->close();
 230          $this->resultSet = null;
 231          return false;
 232      }
 233       
 234      /**
 235       * @see PreparedStatement::getConnection()
 236       */
 237      public function getConnection()
 238      {
 239          return $this->conn;
 240      }
 241      
 242      /**
 243       * Statement resources do not exist for emulated prepared statements,
 244       * so this just returns <code>null</code>.
 245       * @return null
 246       */
 247      public function getResource()
 248      {
 249          return null;
 250      }
 251      
 252      /**
 253       * Nothing to close for emulated prepared statements.
 254       */
 255      public function close()
 256      {       
 257      }
 258      
 259      /**
 260       * Replaces placeholders with the specified parameter values in the SQL.
 261       * 
 262       * This is for emulated prepared statements.
 263       * 
 264       * @return string New SQL statement with parameters replaced.
 265       * @throws SQLException - if param not bound.
 266       */
 267      protected function replaceParams()
 268      {
 269          // early out if we still have the same query ready
 270          if ( $this->sql_cache_valid === true ) {
 271          return $this->sql_cache;
 272      }
 273  
 274          // Default behavior for this function is to behave in 'emulated' mode.    
 275          $sql = '';    
 276          $last_position = 0;
 277  
 278          for ($position = 0; $position < $this->positionsCount; $position++) {
 279              if (!isset($this->boundInVars[$position + 1])) {
 280                  throw new SQLException('Replace params: undefined query param: ' . ($position + 1));
 281              }
 282              $current_position = $this->positions[$position];            
 283              $sql .= substr($this->sql, $last_position, $current_position - $last_position);
 284              $sql .= $this->boundInVars[$position + 1];                    
 285              $last_position = $current_position + 1;            
 286          }
 287          // append the rest of the query
 288          $sql .= substr($this->sql, $last_position);
 289  
 290      // just so we dont touch anything with a blob/clob
 291      if ( strlen ( $sql ) > 2048 ) { 
 292          $this->sql_cache = $sql;
 293              $this->sql_cache_valid = true;
 294          return $this->sql_cache;
 295      } else {
 296          return $sql;
 297      }
 298      }
 299  
 300      /**
 301       * Executes the SQL query in this PreparedStatement object and returns the resultset generated by the query.
 302       * We support two signatures for this method:
 303       * - $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
 304       * - $stmt->executeQuery(array($param1, $param2), ResultSet::FETCHMODE_NUM);
 305       * @param mixed $p1 Either (array) Parameters that will be set using PreparedStatement::set() before query is executed or (int) fetchmode.
 306       * @param int $fetchmode The mode to use when fetching the results (e.g. ResultSet::FETCHMODE_NUM, ResultSet::FETCHMODE_ASSOC).
 307       * @return ResultSet
 308       * @throws SQLException if a database access error occurs.
 309       */
 310  	public function executeQuery($p1 = null, $fetchmode = null)
 311      {    
 312          $params = null;
 313          if ($fetchmode !== null) {
 314              $params = $p1;
 315          } elseif ($p1 !== null) {
 316              if (is_array($p1)) $params = $p1;
 317              else $fetchmode = $p1;
 318          }
 319          
 320              foreach ( (array) $params as $i=>$param ) {
 321              $this->set ( $i + 1, $param );
 322              unset ( $i, $param );
 323          }
 324          unset ( $params );
 325          
 326          $this->updateCount = null; // reset
 327          $sql = $this->replaceParams();        
 328          
 329          if ($this->limit > 0 || $this->offset > 0) {
 330              $this->conn->applyLimit($sql, $this->offset, $this->limit);
 331          }
 332          
 333          $this->resultSet = $this->conn->executeQuery($sql, $fetchmode);
 334          return $this->resultSet;
 335      }
 336  
 337      /**
 338       * Executes the SQL INSERT, UPDATE, or DELETE statement in this PreparedStatement object.
 339       * 
 340       * @param array $params Parameters that will be set using PreparedStatement::set() before query is executed.
 341       * @return int Number of affected rows (or 0 for drivers that return nothing).
 342       * @throws SQLException if a database access error occurs.
 343       */
 344      public function executeUpdate($params = null) 
 345      {
 346          foreach ( (array) $params as $i=>$param ) {
 347              $this->set ( $i + 1, $param );
 348              unset ( $i, $param );
 349          }
 350          unset ( $params );
 351  
 352          if($this->resultSet) $this->resultSet->close();
 353          $this->resultSet = null; // reset                
 354          $sql = $this->replaceParams();        
 355          $this->updateCount = $this->conn->executeUpdate($sql);
 356          return $this->updateCount;
 357      }    
 358  
 359      /**
 360       * Escapes special characters (usu. quotes) using native driver function.
 361       * @param string $str The input string.
 362       * @return string The escaped string.
 363       */
 364      abstract protected function escape($str);
 365      
 366      /**
 367       * A generic set method.
 368       * 
 369       * You can use this if you don't want to concern yourself with the details.  It involves
 370       * slightly more overhead than the specific settesr, since it grabs the PHP type to determine
 371       * which method makes most sense.
 372       * 
 373       * @param int $paramIndex
 374       * @param mixed $value
 375       * @return void
 376       * @throws SQLException
 377       */
 378      function set($paramIndex, $value)
 379      {
 380          $type = gettype($value);
 381          if ($type == "object") {
 382              if (is_a($value, 'Blob')) {
 383                  $this->setBlob($paramIndex, $value);
 384              } elseif (is_a($value, 'Clob')) {
 385                  $this->setClob($paramIndex, $value);
 386              } elseif (is_a($value, 'Date')) {
 387                   // can't be sure if the column type is a DATE, TIME, or TIMESTAMP column
 388                   // we'll just use TIMESTAMP by default; hopefully DB won't complain (if
 389                   // it does, then this method just shouldn't be used).
 390                   $this->setTimestamp($paramIndex, $value);
 391              } else {
 392                  throw new SQLException("Unsupported object type passed to set(): " . get_class($value));
 393              }
 394          } else {
 395          switch ( $type ) {
 396              case 'integer':
 397              $type = 'int';
 398              break;
 399          case 'double':
 400              $type = 'float';
 401              break;
 402          }
 403          $setter = 'set' . ucfirst($type); // PHP types are case-insensitive, but we'll do this in case that change
 404          if ( method_exists ( $this, $setter ) ) {
 405              $this->$setter($paramIndex, $value);
 406          } else {
 407              throw new SQLException ( "Unsupported datatype passed to set(): " . $type );
 408          }
 409          }
 410      }
 411      
 412      /**
 413       * Sets an array.
 414       * Unless a driver-specific method is used, this means simply serializing
 415       * the passed parameter and storing it as a string.
 416       * @param int $paramIndex
 417       * @param array $value
 418       * @return void
 419       */
 420      function setArray($paramIndex, $value) 
 421      {        
 422          $this->sql_cache_valid = false;
 423          if ($value === null) {
 424              $this->setNull($paramIndex);
 425          } else {
 426              $this->boundInVars[$paramIndex] = "'" . $this->escape(serialize($value)) . "'";
 427          }
 428      }
 429  
 430      /**
 431       * Sets a boolean value.
 432       * Default behavior is true = 1, false = 0.
 433       * @param int $paramIndex
 434       * @param boolean $value
 435       * @return void
 436       */
 437      function setBoolean($paramIndex, $value) 
 438      {                
 439          $this->sql_cache_valid = false;
 440          if ($value === null) {
 441              $this->setNull($paramIndex);
 442          } else {
 443              $this->boundInVars[$paramIndex] = (int) $value;
 444          }
 445      }
 446      
 447  
 448      /**
 449       * @see PreparedStatement::setBlob()
 450       */
 451      function setBlob($paramIndex, $blob) 
 452      {        
 453          $this->sql_cache_valid = false;
 454          if ($blob === null) {
 455              $this->setNull($paramIndex);
 456          } else {
 457              // they took magic __toString() out of PHP5.0.0; this sucks
 458              if (is_object($blob)) {
 459                  $this->boundInVars[$paramIndex] = "'" . $this->escape($blob->__toString()) . "'";
 460              } else {
 461                  $this->boundInVars[$paramIndex] = "'" . $this->escape($blob) . "'";
 462          }
 463          }
 464      } 
 465  
 466      /**
 467       * @see PreparedStatement::setClob()
 468       */
 469      function setClob($paramIndex, $clob) 
 470      {
 471          $this->sql_cache_valid = false;
 472          if ($clob === null) {
 473              $this->setNull($paramIndex);
 474          } else {      
 475              // they took magic __toString() out of PHP5.0.0; this sucks
 476              if (is_object($clob)) {
 477                  $this->boundInVars[$paramIndex] = "'" . $this->escape($clob->__toString()) . "'";
 478              } else {
 479                  $this->boundInVars[$paramIndex] = "'" . $this->escape($clob) . "'";
 480          }
 481          }
 482      }     
 483  
 484      /**
 485       * @param int $paramIndex
 486       * @param string $value
 487       * @return void
 488       */
 489      function setDate($paramIndex, $value) 
 490      {
 491          $this->sql_cache_valid = false;
 492          if ($value === null) {
 493              $this->setNull($paramIndex);
 494          } else {
 495              if (is_numeric($value)) $value = date("Y-m-d", $value);
 496              elseif (is_object($value)) $value = date("Y-m-d", $value->getTime());        
 497              $this->boundInVars[$paramIndex] = "'" . $this->escape($value) . "'";
 498          }
 499      } 
 500      
 501      /**
 502       * @param int $paramIndex
 503       * @param double $value
 504       * @return void
 505       */
 506      function setDecimal($paramIndex, $value) 
 507      {
 508          $this->sql_cache_valid = false;
 509          if ($value === null) {
 510              $this->setNull($paramIndex);
 511          } else {
 512              $this->boundInVars[$paramIndex] = (float) $value;
 513          }
 514      }             
 515  
 516      /**
 517       * @param int $paramIndex
 518       * @param double $value
 519       * @return void
 520       */
 521      function setDouble($paramIndex, $value) 
 522      {
 523          $this->sql_cache_valid = false;
 524          if ($value === null) {
 525              $this->setNull($paramIndex);
 526          } else {
 527              $this->boundInVars[$paramIndex] = (double) $value;
 528          }
 529      } 
 530          
 531      /**
 532       * @param int $paramIndex
 533       * @param float $value
 534       * @return void
 535       */
 536      function setFloat($paramIndex, $value) 
 537      {
 538          $this->sql_cache_valid = false;
 539          if ($value === null) {
 540              $this->setNull($paramIndex);
 541          } else {
 542              $this->boundInVars[$paramIndex] = (float) $value;
 543          }
 544      } 
 545  
 546      /**
 547       * @param int $paramIndex
 548       * @param int $value
 549       * @return void
 550       */
 551      function setInt($paramIndex, $value) 
 552      {
 553          $this->sql_cache_valid = false;
 554          if ($value === null) {
 555              $this->setNull($paramIndex);
 556          } else {
 557              $this->boundInVars[$paramIndex] = (int) $value;
 558          }
 559      } 
 560      
 561      /**
 562       * Alias for setInt()
 563       * @param int $paramIndex
 564       * @param int $value
 565       */
 566      function setInteger($paramIndex, $value)
 567      {
 568          $this->sql_cache_valid = false;
 569          $this->setInt($paramIndex, $value);
 570      }
 571  
 572      /**
 573       * @param int $paramIndex
 574       * @return void
 575       */
 576      function setNull($paramIndex) 
 577      {
 578          $this->sql_cache_valid = false;
 579          $this->boundInVars[$paramIndex] = 'NULL';
 580      }
 581  
 582      /**
 583       * @param int $paramIndex
 584       * @param string $value
 585       * @return void
 586       */
 587      function setString($paramIndex, $value) 
 588      {
 589          $this->sql_cache_valid = false;
 590          if ($value === null) {
 591              $this->setNull($paramIndex);
 592          } else {
 593              // it's ok to have a fatal error here, IMO, if object doesn't have
 594              // __toString() and is being passed to this method.
 595          if ( is_object ( $value ) ) {
 596                  $this->boundInVars[$paramIndex] = "'" . $this->escape($value->__toString()) . "'";
 597          } else {
 598                  $this->boundInVars[$paramIndex] = "'" . $this->escape((string)$value) . "'";
 599          }
 600          }
 601      } 
 602      
 603      /**
 604       * @param int $paramIndex
 605       * @param string $value
 606       * @return void
 607       */
 608      function setTime($paramIndex, $value) 
 609      {        
 610          $this->sql_cache_valid = false;
 611          if ($value === null) {
 612              $this->setNull($paramIndex);
 613          } else {
 614              if ( is_numeric ( $value ) ) {
 615                  $value = date ('H:i:s', $value );
 616              } elseif ( is_object ( $value ) ) {
 617                  $value = date ('H:i:s', $value->getTime ( ) );
 618              }
 619              $this->boundInVars [ $paramIndex ] = "'" . $this->escape ( $value ) . "'";
 620          }
 621      }
 622      
 623      /**
 624       * @param int $paramIndex
 625       * @param string $value
 626       * @return void
 627       */
 628      function setTimestamp($paramIndex, $value) 
 629      {        
 630          $this->sql_cache_valid = false;
 631          if ($value === null) {
 632              $this->setNull($paramIndex);
 633          } else {
 634                 if (is_numeric($value)) $value = date('Y-m-d H:i:s', $value);
 635                 elseif (is_object($value)) $value = date('Y-m-d H:i:s', $value->getTime());
 636              $this->boundInVars[$paramIndex] = "'".$this->escape($value)."'";
 637          }
 638      }
 639              
 640  }