inc/AwlQuery.php

   1 <?php
   2 /**
   3 * @package   awl
   4 * @subpackage   AwlDatabase
   5 * @author    Andrew McMillan <andrew@morphoss.com>
   6 * @copyright Morphoss Ltd
   7 * @license   http://gnu.org/copyleft/gpl.html GNU GPL v3 or later
   8 * @compatibility Requires PHP 5.1 or later
   9 */
  10
  11 require_once('AwlDatabase.php');
  12
  13 /**
  14 * Database query class and associated functions
  15 *
  16 * This subpackage provides some functions that are useful around database
  17 * activity and an AwlQuery class to simplify handling of database queries.
  18 *
  19 * The class is intended to be a very lightweight wrapper with no pretentions
  20 * towards database independence, but it does include some features that have
  21 * proved useful in developing and debugging web-based applications:
  22 *  - All queries are timed, and an expected time can be provided.
  23 *  - Parameters replaced into the SQL will be escaped correctly in order to
  24 *    minimise the chances of SQL injection errors.
  25 *  - Queries which fail, or which exceed their expected execution time, will
  26 *    be logged for potential further analysis.
  27 *  - Debug logging of queries may be enabled globally, or restricted to
  28 *    particular sets of queries.
  29 *  - Simple syntax for iterating through a result set.
  30 *
  31 * This class is intended as a transitional mechanism for moving from the
  32 * PostgreSQL-specific Pg Query class to something which uses PDO in a more
  33 * replaceable manner.
  34 *
  35 */
  36
  37 /**
  38 * Connect to the database defined in the $c->db_connect[] (or $c->pg_connect) arrays
  39 */
  40 function _awl_connect_configured_database() {
  41   global $c, $_awl_dbconn;
  42
  43   /**
  44   * Attempt to connect to the configured connect strings
  45   */
  46   $_awl_dbconn = false;
  47
  48   if ( isset($c->db_connect) ) {
  49     $connection_strings = $c->db_connect;
  50   }
  51   elseif ( isset($c->pg_connect) ) {
  52     $connection_strings = $c->pg_connect;
  53   }
  54
  55   foreach( $connection_strings AS $k => $v ) {
  56     $dbuser = null;
  57     $dbpass = null;
  58     if ( is_array($v) ) {
  59       $dsn = $v['dsn'];
  60       if ( isset($v['dbuser']) ) $dbuser = $v['dbuser'];
  61       if ( isset($v['dbpass']) ) $dbpass = $v['dbpass'];
  62     }
  63     elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $v, $matches ) ) {
  64       $dsn = $matches[2];
  65       if ( isset($matches[1]) && $matches[1] != '' ) {
  66         $dsn = $matches[1] . $dsn;
  67       }
  68       else {
  69         $dsn = 'pgsql:' . $dsn;
  70       }
  71       if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
  72       if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
  73     }
  74     if ( $_awl_dbconn = new AwlDatabase( $dsn, $dbuser, $dbpass, (isset($c->use_persistent) && $c->use_persistent ? array(PDO::ATTR_PERSISTENT => true) : null) ) ) break;
  75   }
  76
  77   if ( ! $_awl_dbconn ) {
  78     echo <<<EOERRMSG
  79   <html><head><title>Database Connection Failure</title></head><body>
  80   <h1>Database Error</h1>
  81   <h3>Could not connect to database</h3>
  82   </body>
  83   </html>
  84 EOERRMSG;
  85     exit;
  86   }
  87
  88   if ( isset($c->db_schema) && $c->db_schema != '' ) {
  89     $_awl_dbconn->SetSearchPath( $c->db_schema . ',public' );
  90   }
  91
  92   $c->_awl_dbversion = $_awl_dbconn->GetVersion();
  93 }
  94
  95
  96 /**
  97 * The AwlQuery Class.
  98 *
  99 * This class builds and executes SQL Queries and traverses the
 100 * set of results returned from the query.
 101 *
 102 * <b>Example usage</b>
 103 * <code>
 104 * $sql = "SELECT * FROM mytable WHERE mytype = ?";
 105 * $qry = new AwlQuery( $sql, $myunsanitisedtype );
 106 * if ( $qry->Exec("typeselect", __line__, __file__ )
 107 *      && $qry->rows > 0 )
 108 * {
 109 *   while( $row = $qry->Fetch() ) {
 110 *     do_something_with($row);
 111 *   }
 112 * }
 113 * </code>
 114 *
 115 * @package   awl
 116 */
 117 class AwlQuery
 118 {
 119   /**#@+
 120   * @access private
 121   */
 122   /**
 123   * Our database connection, normally copied from a global one
 124   * @var resource
 125   */
 126   protected $connection;
 127
 128   /**
 129   * The original query string
 130   * @var string
 131   */
 132   protected $querystring;
 133
 134   /**
 135   * The actual query string, after we've replaced parameters in it
 136   * @var string
 137   */
 138   protected $bound_querystring;
 139
 140   /**
 141   * The current array of bound parameters
 142   * @var array
 143   */
 144   protected $bound_parameters;
 145
 146   /**
 147   * The PDO statement handle, or null if we don't have one yet.
 148   * @var string
 149   */
 150   protected $sth;
 151
 152   /**
 153   * Result of the last execution
 154   * @var resource
 155   */
 156   protected $result;
 157
 158   /**
 159   * number of current row - use accessor to get/set
 160   * @var int
 161   */
 162   protected $rownum = null;
 163
 164   /**
 165   * number of rows from pg_numrows - use accessor to get value
 166   * @var int
 167   */
 168   protected $rows;
 169
 170   /**
 171   * The Database error information, if the query fails.
 172   * @var string
 173   */
 174   protected $error_info;
 175
 176   /**
 177   * Stores the query execution time - used to deal with long queries.
 178   * should be read-only
 179   * @var string
 180   */
 181   protected $execution_time;
 182
 183   /**#@-*/
 184
 185   /**#@+
 186   * @access public
 187   */
 188   /**
 189   * Where we called this query from so we can find it in our code!
 190   * Debugging may also be selectively enabled for a $location.
 191   * @var string
 192   */
 193   public $location;
 194
 195   /**
 196   * How long the query should take before a warning is issued.
 197   *
 198   * This is writable, but a method to set it might be a better interface.
 199   * The default is 0.3 seconds.
 200   * @var double
 201   */
 202   public $query_time_warning = 0.3;
 203   /**#@-*/
 204
 205
 206  /**
 207   * Constructor
 208   * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
 209   * @param mixed The values to replace into the SQL string.
 210   * @return The AwlQuery object
 211   */
 212   function __construct() {
 213     global $_awl_dbconn;
 214     $this->rows = null;
 215     $this->execution_time = 0;
 216     $this->error_info = null;
 217     $this->rownum = -1;
 218     if ( isset($_awl_dbconn) ) $this->connection = $_awl_dbconn;
 219     else                       $this->connection = null;
 220
 221     $argc = func_num_args();
 222     $args = func_get_args();
 223
 224     $this->querystring = array_shift($args);
 225     if ( 1 < $argc ) {
 226       if ( is_array($args[0]) )
 227         $this->bound_parameters = $args[0];
 228       else
 229         $this->bound_parameters = $args;
 230 //      print_r( $this->bound_parameters );
 231     }
 232
 233     return $this;
 234   }
 235
 236
 237  /**
 238   * Use a different database connection for this query
 239   * @param  resource $new_connection The database connection to use.
 240   */
 241   function SetConnection( $new_connection, $options = null ) {
 242     if ( is_string($new_connection) || is_array($new_connection) ) {
 243       $dbuser = null;
 244       $dbpass = null;
 245       if ( is_array($new_connection) ) {
 246         $dsn = $new_connection['dsn'];
 247         if ( isset($new_connection['dbuser']) ) $dbuser = $new_connection['dbuser'];
 248         if ( isset($new_connection['dbpass']) ) $dbpass = $new_connection['dbpass'];
 249       }
 250       elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $new_connection, $matches ) ) {
 251         $dsn = $matches[2];
 252         if ( isset($matches[1]) && $matches[1] != '' ) {
 253           $dsn = $matches[1] . $dsn;
 254         }
 255         else {
 256           $dsn = 'pgsql:' . $dsn;
 257         }
 258         if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
 259         if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
 260       }
 261       if ( $new_connection = new AwlDatabase( $dsn, $dbuser, $dbpass, $options ) ) break;
 262     }
 263     $this->connection = $new_connection;
 264     return $new_connection;
 265   }
 266
 267
 268
 269  /**
 270   * Get the current database connection for this query
 271   */
 272   function GetConnection() {
 273     return $this->connection;
 274   }
 275
 276
 277   /**
 278   * Log query, optionally with file and line location of the caller.
 279   *
 280   * This function should not really be used outside of AwlQuery.  For a more
 281   * useful generic logging interface consider calling dbg_error_log(...);
 282   *
 283   * @param string $locn    A string identifying the calling location.
 284   * @param string $tag     A tag string, e.g. identifying the type of event.
 285   * @param string $string  The information to be logged.
 286   * @param int    $line    The line number where the logged event occurred.
 287   * @param string $file    The file name where the logged event occurred.
 288   */
 289   function _log_query( $locn, $tag, $string, $line = 0, $file = "") {
 290     // replace more than one space with one space
 291     $string = preg_replace('/\s+/', ' ', $string);
 292
 293     if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
 294       dbg_error_log( "LOG-$locn", " Query: %s: %s in '%s' on line %d", ($tag == 'QF' ? 'Error' : 'Possible slow query'), $tag, $file, $line );
 295     }
 296
 297     while( strlen( $string ) > 0 )  {
 298       dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
 299       $string = substr( "$string", 240 );
 300     }
 301   }
 302
 303
 304   /**
 305   * Quote the given string so it can be safely used within string delimiters
 306   * in a query.  To be avoided, in general.
 307   *
 308   * @param mixed $str Data to be converted to a string suitable for including as a value in SQL.
 309   * @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped
 310   */
 311   public static function quote($str = null) {
 312     global $_awl_dbconn;
 313     if ( !isset($_awl_dbconn) ) {
 314       _awl_connect_configured_database();
 315     }
 316     return $_awl_dbconn->Quote($str);
 317   }
 318
 319
 320   /**
 321   * Bind some parameters.  This can be called in three ways:
 322   * 1) As Bind(':key','value), when using named parameters
 323   * 2) As Bind('value'), when using ? placeholders
 324   * 3) As Bind(array()), to overwrite the existing bound parameters.  The array may
 325   *    be ':name' => 'value' pairs or ordinal values, depending on whether the SQL
 326   *    is using ':name' or '?' style placeholders.
 327   *
 328   * @param mixed $args See details above.
 329   */
 330   function Bind() {
 331     $argc = func_num_args();
 332     $args = func_get_args();
 333
 334     if ( $argc == 1 ) {
 335       if ( gettype($args[0]) == 'array' ) {
 336         $this->bound_parameters = $args[0];
 337       }
 338       else {
 339         $this->bound_parameters[] = $args[0];
 340       }
 341     }
 342     else {
 343       $this->bound_parameters[$args[0]] = $args[1];
 344     }
 345   }
 346
 347
 348   /**
 349   * Tell the database to prepare the query that we will execute
 350   */
 351   function Prepare() {
 352     global $c;
 353
 354     if ( isset($this->sth) ) return; // Already prepared
 355     if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) return; //  No-op if we're expanding internally
 356
 357     if ( !isset($this->connection) ) {
 358       _awl_connect_configured_database();
 359       $this->connection = $GLOBALS['_awl_dbconn'];
 360     }
 361
 362     $this->sth = $this->connection->prepare( $this->querystring );
 363
 364     if ( ! $this->sth ) {
 365       $this->error_info = $this->connection->errorInfo();
 366     }
 367     else $this->error_info = null;
 368   }
 369
 370   /**
 371   * Tell the database to execute the query
 372   */
 373   function Execute() {
 374     global $c;
 375
 376     if ( !isset($this->connection) ) {
 377       _awl_connect_configured_database();
 378       $this->connection = $GLOBALS['_awl_dbconn'];
 379     }
 380     if ( !is_object($this->connection) ) throw new Exception('Database not connected.');
 381
 382     if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) {
 383       $this->bound_querystring = $this->querystring;
 384       if ( isset($this->bound_parameters) ) {
 385         $this->bound_querystring = $this->connection->ReplaceParameters($this->querystring,$this->bound_parameters);
 386 //        printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
 387 //        printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring);
 388 //        print_r( $this->bound_parameters );
 389       }
 390       $t1 = microtime(true); // get start time
 391       $execute_result = $this->sth = $this->connection->query($this->bound_querystring);
 392     }
 393     else {
 394       $t1 = microtime(true); // get start time
 395       $execute_result = $this->sth = $this->connection->prepare($this->querystring);
 396       if ( $this->sth ) $execute_result = $this->sth->execute($this->bound_parameters);
 397 //      printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
 398 //      print_r( $this->bound_parameters );
 399     }
 400     $this->bound_querystring = null;
 401
 402     if ( $execute_result === false ) {
 403       $this->error_info = $this->connection->errorInfo();
 404       return false;
 405     }
 406     $this->rows = $this->sth->rowCount();
 407
 408     $i_took = microtime(true) - $t1;
 409     $c->total_query_time += $i_took;
 410     $this->execution_time = sprintf( "%2.06lf", $i_took);
 411
 412     $this->error_info = null;
 413     return true;
 414   }
 415
 416
 417   /**
 418   * Return the query string we are planning to execute
 419   */
 420   function QueryString() {
 421     return $this->querystring;
 422   }
 423
 424
 425   /**
 426   * Return the parameters we are planning to substitute into the query string
 427   */
 428   function Parameters() {
 429     return $this->bound_parameters;
 430   }
 431
 432
 433   /**
 434   * Return the count of rows retrieved/affected
 435   */
 436   function rows() {
 437     return $this->rows;
 438   }
 439
 440
 441   /**
 442   * Return the current rownum in the retrieved set
 443   */
 444   function rownum() {
 445     return $this->rownum;
 446   }
 447
 448
 449   /**
 450   * Returns the current state of a transaction, indicating if we have begun a transaction, whether the transaction
 451   * has failed, or if we are not in a transaction.
 452   * @return int 0 = not started, 1 = in progress, -1 = error pending rollback/commit
 453   */
 454   function TransactionState() {
 455     global $_awl_dbconn;
 456     if ( !isset($this->connection) ) {
 457       if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
 458       $this->connection = $_awl_dbconn;
 459     }
 460     return $this->connection->TransactionState();
 461   }
 462
 463
 464   /**
 465   * Wrap the parent DB class Begin() so we can $qry->Begin() sometime before we $qry->Exec()
 466   */
 467   public function Begin() {
 468     global $_awl_dbconn;
 469     if ( !isset($this->connection) ) {
 470       if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
 471       $this->connection = $_awl_dbconn;
 472     }
 473     return $this->connection->Begin();
 474   }
 475
 476
 477   /**
 478   * Wrap the parent DB class Commit() so we can $qry->Commit() sometime after we $qry->Exec()
 479   */
 480   public function Commit() {
 481     if ( !isset($this->connection) ) {
 482       trigger_error("Cannot commit a transaction without an active statement.", E_USER_ERROR);
 483     }
 484     return $this->connection->Commit();
 485   }
 486
 487
 488   /**
 489   * Wrap the parent DB class Rollback() so we can $qry->Rollback() sometime after we $qry->Exec()
 490   */
 491   public function Rollback() {
 492     if ( !isset($this->connection) ) {
 493       trigger_error("Cannot rollback a transaction without an active statement.", E_USER_ERROR);
 494     }
 495     return $this->connection->Rollback();
 496   }
 497
 498
 499   /**
 500   * Simple SetSql() class which will reset the object with the querystring from the first argument.
 501   * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
 502   */
 503   public function SetSql( $sql ) {
 504     $this->rows = null;
 505     $this->execution_time = 0;
 506     $this->error_info = null;
 507     $this->rownum = -1;
 508     $this->bound_parameters = null;
 509     $this->bound_querystring = null;
 510     $this->sth = null;
 511
 512     $this->querystring = $sql;
 513   }
 514
 515
 516   /**
 517   * Simple QDo() class which will re-use this query for whatever was passed in, and execute it
 518   * returning the result of the Exec() call.  We can't call it Do() since that's a reserved word...
 519   * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
 520   * @param mixed The values to replace into the SQL string.
 521   * @return boolean Success (true) or Failure (false)
 522   */
 523   public function QDo() {
 524     $argc = func_num_args();
 525     $args = func_get_args();
 526
 527     $this->SetSql( array_shift($args) );
 528     if ( 1 < $argc ) {
 529       if ( is_array($args[0]) )
 530         $this->bound_parameters = $args[0];
 531       else
 532         $this->bound_parameters = $args;
 533     }
 534
 535     return $this->Exec();
 536   }
 537
 538
 539   /**
 540   * Execute the query, logging any debugging.
 541   *
 542   * <b>Example</b>
 543   * So that you can nicely enable/disable the queries for a particular class, you
 544   * could use some of PHPs magic constants in your call.
 545   * <code>
 546   * $qry->Exec(__CLASS__, __LINE__, __FILE__);
 547   * </code>
 548   *
 549   *
 550   * @param string $location The name of the location for enabling debugging or just
 551   *                         to help our children find the source of a problem.
 552   * @param int $line The line number where Exec was called
 553   * @param string $file The file where Exec was called
 554   * @return boolean Success (true) or Failure (false)
 555   */
 556   function Exec( $location = null, $line = null, $file = null ) {
 557     global $c;
 558     if ( isset($location) ) $this->location = trim($location);
 559     if ( !isset($this->location) || $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
 560
 561     if ( isset($line) )     $this->location_line = intval($line);
 562     else if ( isset($this->location_line) ) $line = $this->location_line;
 563
 564     if ( isset($file) )     $this->location_file = trim($file);
 565     else if ( isset($this->location_file) ) $file = $this->location_file;
 566
 567     if ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
 568       $this->_log_query( $this->location, 'DBGQ', $this->querystring, $line, $file );
 569       if ( isset($this->bound_parameters) && !isset($this->sth) ) {
 570         foreach( $this->bound_parameters AS $k => $v ) {
 571           $this->_log_query( $this->location, 'DBGQ', sprintf('    "%s" => "%s"', $k, $v), $line, $file );
 572         }
 573       }
 574     }
 575
 576     if ( isset($this->bound_parameters) ) {
 577       $this->Prepare();
 578     }
 579
 580     $success = $this->Execute();
 581
 582     if ( ! $success ) {
 583       // query failed
 584       $this->errorstring = sprintf( 'SQL error "%s" - %s"', $this->error_info[0], (isset($this->error_info[2]) ? $this->error_info[2] : ''));
 585       if ( isset($c->dbg['print_query_errors']) && $c->dbg['print_query_errors'] ) {
 586         printf( "\n=====================\n" );
 587         printf( "%s[%d] QF: %s\n", $file, $line, $this->errorstring);
 588         printf( "%s\n", $this->querystring );
 589         if ( isset($this->bound_parameters) ) {
 590           foreach( $this->bound_parameters AS $k => $v ) {
 591             printf( "    %-18s \t=> '%s'\n", "'$k'", $v );
 592           }
 593         }
 594         printf( ".....................\n" );
 595       }
 596       $this->_log_query( $this->location, 'QF', $this->errorstring, $line, $file );
 597       $this->_log_query( $this->location, 'QF', $this->querystring, $line, $file );
 598       if ( isset($this->bound_parameters) && ! ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) ) {
 599         foreach( $this->bound_parameters AS $k => $v ) {
 600           dbg_error_log( 'LOG-'.$this->location, ' Query: QF:     "%s" => "%s"', $k, $v);
 601         }
 602       }
 603     }
 604     elseif ( $this->execution_time > $this->query_time_warning ) {
 605      // if execution time is too long
 606       $this->_log_query( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
 607     }
 608     elseif ( isset($c->dbg['querystring']) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
 609      // query successful, but we're debugging and want to know how long it took anyway
 610       $this->_log_query( $this->location, 'DBGQ', "Took: $this->execution_time to find $this->rows rows.", $line, $file );
 611     }
 612
 613     return $success;
 614   }
 615
 616
 617   /**
 618   * Fetch the next row from the query results
 619   * @param boolean $as_array True if thing to be returned is array
 620   * @return mixed query row
 621   */
 622   function Fetch($as_array = false) {
 623
 624     if ( ! $this->sth || $this->rows == 0 ) return false; // no results
 625     if ( $this->rownum == null ) $this->rownum = -1;
 626     if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
 627
 628     $this->rownum++;
 629     $row = $this->sth->fetch( ($as_array ? PDO::FETCH_NUM : PDO::FETCH_OBJ) );
 630
 631     return $row;
 632   }
 633
 634
 635   /**
 636    * Get any error information from the last query
 637    */
 638   function getErrorInfo() {
 639         return $this->error_info;
 640   }
 641 }
 642