1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  15:  16:  17:  18:  19:  20:  21:  22:  23:  24:  25:  26:  27:  28:  29:  30:  31:  32:  33:  34:  35:  36:  37:  38:  39:  40:  41:  42:  43:  44:  45:  46:  47:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 168: 169: 170: 171: 172: 173: 174: 175: 176: 177: 178: 179: 180: 181: 182: 183: 184: 185: 186: 187: 188: 189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 200: 201: 202: 203: 204: 205: 206: 207: 208: 209: 210: 211: 212: 213: 214: 215: 216: 217: 218: 219: 220: 221: 222: 223: 224: 225: 226: 227: 228: 229: 230: 231: 232: 233: 234: 235: 236: 237: 238: 239: 240: 241: 242: 243: 244: 245: 246: 247: 248: 249: 250: 251: 252: 253: 254: 255: 256: 257: 258: 259: 260: 261: 262: 263: 264: 265: 266: 267: 268: 269: 270: 271: 272: 273: 274: 275: 276: 277: 278: 279: 280: 281: 282: 283: 284: 285: 286: 287: 288: 289: 290: 291: 292: 293: 294: 295: 296: 297: 298: 299: 300: 301: 302: 303: 304: 305: 306: 307: 308: 309: 310: 311: 312: 313: 314: 315: 316: 317: 318: 319: 320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333: 334: 335: 336: 337: 338: 339: 340: 341: 342: 343: 344: 345: 346: 347: 348: 349: 350: 351: 352: 353: 354: 355: 356: 357: 358: 359: 360: 361: 362: 363: 364: 365: 366: 367: 368: 369: 370: 371: 372: 373: 374: 375: 376: 377: 378: 379: 380: 381: 382: 383: 384: 385: 386: 387: 388: 389: 390: 391: 392: 393: 394: 395: 396: 397: 398: 399: 400: 401: 402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431: 432: 433: 434: 435: 436: 437: 438: 439: 440: 441: 442: 443: 444: 445: 446: 447: 448: 449: 450: 451: 452: 453: 454: 455: 456: 457: 458: 459: 460: 461: 462: 463: 464: 465: 466: 467: 468: 469: 470: 471: 472: 473: 474: 475: 476: 477: 478: 479: 480: 481: 482: 483: 484: 485: 486: 487: 488: 489: 490: 491: 492: 493: 494: 495: 496: 497: 498: 499: 500: 501: 502: 503: 504: 505: 506: 507: 508: 509: 510: 511: 512: 513: 514: 515: 516: 517: 518: 519: 520: 521: 522: 523: 524: 525: 526: 527: 528: 529: 530: 531: 532: 533: 534: 535: 536: 537: 538: 539: 540: 541: 542: 543: 544: 545: 546: 547: 548: 549: 550: 551: 552: 553: 554: 555: 556: 557: 558: 559: 560: 561: 562: 563: 564: 565: 566: 567: 568: 569: 570: 571: 572: 573: 574: 575: 576: 577: 578: 579: 580: 581: 582: 583: 584: 585: 586: 587: 588: 589: 590: 591: 592: 593: 594: 595: 596: 597: 598: 599: 600: 601: 602: 603: 604: 605: 606: 607: 608: 609: 610: 611: 612: 613: 614: 615: 616: 617: 618: 619: 620: 621: 622: 623: 624: 625: 626: 627: 628: 629: 630: 631: 632: 633: 634: 635: 636: 637: 638: 639: 640: 641: 642: 643: 

<?php
/**
* @package   awl
* @subpackage   AwlDatabase
* @author    Andrew McMillan <andrew@morphoss.com>
* @copyright Morphoss Ltd
* @license   http://gnu.org/copyleft/gpl.html GNU GPL v3 or later
* @compatibility Requires PHP 5.1 or later
*/

require_once('AwlDatabase.php');

/**
* Database query class and associated functions
*
* This subpackage provides some functions that are useful around database
* activity and an AwlQuery class to simplify handling of database queries.
*
* The class is intended to be a very lightweight wrapper with no pretentions
* towards database independence, but it does include some features that have
* proved useful in developing and debugging web-based applications:
*  - All queries are timed, and an expected time can be provided.
*  - Parameters replaced into the SQL will be escaped correctly in order to
*    minimise the chances of SQL injection errors.
*  - Queries which fail, or which exceed their expected execution time, will
*    be logged for potential further analysis.
*  - Debug logging of queries may be enabled globally, or restricted to
*    particular sets of queries.
*  - Simple syntax for iterating through a result set.
*
* This class is intended as a transitional mechanism for moving from the
* PostgreSQL-specific Pg Query class to something which uses PDO in a more
* replaceable manner.
*
*/

/**
* Connect to the database defined in the $c->db_connect[] (or $c->pg_connect) arrays
*/
function _awl_connect_configured_database() {
  global $c, $_awl_dbconn;

  /**
  * Attempt to connect to the configured connect strings
  */
  $_awl_dbconn = false;

  if ( isset($c->db_connect) ) {
    $connection_strings = $c->db_connect;
  }
  elseif ( isset($c->pg_connect) ) {
    $connection_strings = $c->pg_connect;
  }

  foreach( $connection_strings AS $k => $v ) {
    $dbuser = null;
    $dbpass = null;
    if ( is_array($v) ) {
      $dsn = $v['dsn'];
      if ( isset($v['dbuser']) ) $dbuser = $v['dbuser'];
      if ( isset($v['dbpass']) ) $dbpass = $v['dbpass'];
    }
    elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $v, $matches ) ) {
      $dsn = $matches[2];
      if ( isset($matches[1]) && $matches[1] != '' ) {
        $dsn = $matches[1] . $dsn;
      }
      else {
        $dsn = 'pgsql:' . $dsn;
      }
      if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
      if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
    }
    if ( $_awl_dbconn = new AwlDatabase( $dsn, $dbuser, $dbpass, (isset($c->use_persistent) && $c->use_persistent ? array(PDO::ATTR_PERSISTENT => true) : null) ) ) break;
  }

  if ( ! $_awl_dbconn ) {
    echo <<<EOERRMSG
  <html><head><title>Database Connection Failure</title></head><body>
  <h1>Database Error</h1>
  <h3>Could not connect to database</h3>
  </body>
  </html>
EOERRMSG;
    exit;
  }

  if ( isset($c->db_schema) && $c->db_schema != '' ) {
    $_awl_dbconn->SetSearchPath( $c->db_schema . ',public' );
  }

  $c->_awl_dbversion = $_awl_dbconn->GetVersion();
}


/**
* The AwlQuery Class.
*
* This class builds and executes SQL Queries and traverses the
* set of results returned from the query.
*
* <b>Example usage</b>
* <code>
* $sql = "SELECT * FROM mytable WHERE mytype = ?";
* $qry = new AwlQuery( $sql, $myunsanitisedtype );
* if ( $qry->Exec("typeselect", __line__, __file__ )
*      && $qry->rows > 0 )
* {
*   while( $row = $qry->Fetch() ) {
*     do_something_with($row);
*   }
* }
* </code>
*
* @package   awl
*/
class AwlQuery
{
  /**#@+
  * @access private
  */
  /**
  * Our database connection, normally copied from a global one
  * @var resource
  */
  protected $connection;

  /**
  * The original query string
  * @var string
  */
  protected $querystring;

  /**
  * The actual query string, after we've replaced parameters in it
  * @var string
  */
  protected $bound_querystring;

  /**
  * The current array of bound parameters
  * @var array
  */
  protected $bound_parameters;

  /**
  * The PDO statement handle, or null if we don't have one yet.
  * @var string
  */
  protected $sth;

  /**
  * Result of the last execution
  * @var resource
  */
  protected $result;

  /**
  * number of current row - use accessor to get/set
  * @var int
  */
  protected $rownum = null;

  /**
  * number of rows from pg_numrows - use accessor to get value
  * @var int
  */
  protected $rows;

  /**
  * The Database error information, if the query fails.
  * @var string
  */
  protected $error_info;

  /**
  * Stores the query execution time - used to deal with long queries.
  * should be read-only
  * @var string
  */
  protected $execution_time;

  /**#@-*/

  /**#@+
  * @access public
  */
  /**
  * Where we called this query from so we can find it in our code!
  * Debugging may also be selectively enabled for a $location.
  * @var string
  */
  public $location;

  /**
  * How long the query should take before a warning is issued.
  *
  * This is writable, but a method to set it might be a better interface.
  * The default is 0.3 seconds.
  * @var double
  */
  public $query_time_warning = 0.3;
  /**#@-*/


 /**
  * Constructor
  * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
  * @param mixed The values to replace into the SQL string.
  * @return The AwlQuery object
  */
  function __construct() {
    global $_awl_dbconn;
    $this->rows = null;
    $this->execution_time = 0;
    $this->error_info = null;
    $this->rownum = -1;
    if ( isset($_awl_dbconn) ) $this->connection = $_awl_dbconn;
    else                       $this->connection = null;

    $argc = func_num_args();
    $args = func_get_args();

    $this->querystring = array_shift($args);
    if ( 1 < $argc ) {
      if ( is_array($args[0]) )
        $this->bound_parameters = $args[0];
      else
        $this->bound_parameters = $args;
//      print_r( $this->bound_parameters );
    }

    return $this;
  }


 /**
  * Use a different database connection for this query
  * @param  resource $new_connection The database connection to use.
  */
  function SetConnection( $new_connection, $options = null ) {
    if ( is_string($new_connection) || is_array($new_connection) ) {
      $dbuser = null;
      $dbpass = null;
      if ( is_array($new_connection) ) {
        $dsn = $new_connection['dsn'];
        if ( isset($new_connection['dbuser']) ) $dbuser = $new_connection['dbuser'];
        if ( isset($new_connection['dbpass']) ) $dbpass = $new_connection['dbpass'];
      }
      elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $new_connection, $matches ) ) {
        $dsn = $matches[2];
        if ( isset($matches[1]) && $matches[1] != '' ) {
          $dsn = $matches[1] . $dsn;
        }
        else {
          $dsn = 'pgsql:' . $dsn;
        }
        if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
        if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
      }
      if ( ! $new_connection = new AwlDatabase( $dsn, $dbuser, $dbpass, $options ) ) return;
    }
    $this->connection = $new_connection;
    return $new_connection;
  }



 /**
  * Get the current database connection for this query
  */
  function GetConnection() {
    return $this->connection;
  }


  /**
  * Log query, optionally with file and line location of the caller.
  *
  * This function should not really be used outside of AwlQuery.  For a more
  * useful generic logging interface consider calling dbg_error_log(...);
  *
  * @param string $locn    A string identifying the calling location.
  * @param string $tag     A tag string, e.g. identifying the type of event.
  * @param string $string  The information to be logged.
  * @param int    $line    The line number where the logged event occurred.
  * @param string $file    The file name where the logged event occurred.
  */
  function _log_query( $locn, $tag, $string, $line = 0, $file = "") {
    // replace more than one space with one space
    $string = preg_replace('/\s+/', ' ', $string);

    if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
      dbg_error_log( "LOG-$locn", " Query: %s: %s in '%s' on line %d", ($tag == 'QF' ? 'Error' : 'Possible slow query'), $tag, $file, $line );
    }

    while( strlen( $string ) > 0 )  {
      dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
      $string = substr( "$string", 240 );
    }
  }


  /**
  * Quote the given string so it can be safely used within string delimiters
  * in a query.  To be avoided, in general.
  *
  * @param mixed $str Data to be converted to a string suitable for including as a value in SQL.
  * @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped
  */
  public static function quote($str = null) {
    global $_awl_dbconn;
    if ( !isset($_awl_dbconn) ) {
      _awl_connect_configured_database();
    }
    return $_awl_dbconn->Quote($str);
  }


  /**
  * Bind some parameters.  This can be called in three ways:
  * 1) As Bind(':key','value), when using named parameters
  * 2) As Bind('value'), when using ? placeholders
  * 3) As Bind(array()), to overwrite the existing bound parameters.  The array may
  *    be ':name' => 'value' pairs or ordinal values, depending on whether the SQL
  *    is using ':name' or '?' style placeholders.
  *
  * @param mixed $args See details above.
  */
  function Bind() {
    $argc = func_num_args();
    $args = func_get_args();

    if ( $argc == 1 ) {
      if ( gettype($args[0]) == 'array' ) {
        $this->bound_parameters = $args[0];
      }
      else {
        $this->bound_parameters[] = $args[0];
      }
    }
    else {
      $this->bound_parameters[$args[0]] = $args[1];
    }
  }


  /**
  * Tell the database to prepare the query that we will execute
  */
  function Prepare() {
    global $c;

    if ( isset($this->sth) ) return; // Already prepared
    if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) return; //  No-op if we're expanding internally

    if ( !isset($this->connection) ) {
      _awl_connect_configured_database();
      $this->connection = $GLOBALS['_awl_dbconn'];
    }

    $this->sth = $this->connection->prepare( $this->querystring );

    if ( ! $this->sth ) {
      $this->error_info = $this->connection->errorInfo();
    }
    else $this->error_info = null;
  }

  /**
  * Tell the database to execute the query
  */
  function Execute() {
    global $c;

    if ( !isset($this->connection) ) {
      _awl_connect_configured_database();
      $this->connection = $GLOBALS['_awl_dbconn'];
    }
    if ( !is_object($this->connection) ) throw new Exception('Database not connected.');

    if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) {
      $this->bound_querystring = $this->querystring;
      if ( isset($this->bound_parameters) ) {
        $this->bound_querystring = $this->connection->ReplaceParameters($this->querystring,$this->bound_parameters);
//        printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
//        printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring);
//        print_r( $this->bound_parameters );
      }
      $t1 = microtime(true); // get start time
      $execute_result = $this->sth = $this->connection->query($this->bound_querystring);
    }
    else {
      $t1 = microtime(true); // get start time
      $execute_result = $this->sth = $this->connection->prepare($this->querystring);
      if ( $this->sth ) $execute_result = $this->sth->execute($this->bound_parameters);
//      printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
//      print_r( $this->bound_parameters );
    }
    $this->bound_querystring = null;

    if ( $execute_result === false ) {
      $this->error_info = $this->connection->errorInfo();
      return false;
    }
    $this->rows = $this->sth->rowCount();

    $i_took = microtime(true) - $t1;
    $c->total_query_time += $i_took;
    $this->execution_time = sprintf( "%2.06lf", $i_took);

    $this->error_info = null;
    return true;
  }


  /**
  * Return the query string we are planning to execute
  */
  function QueryString() {
    return $this->querystring;
  }


  /**
  * Return the parameters we are planning to substitute into the query string
  */
  function Parameters() {
    return $this->bound_parameters;
  }


  /**
  * Return the count of rows retrieved/affected
  */
  function rows() {
    return $this->rows;
  }


  /**
  * Return the current rownum in the retrieved set
  */
  function rownum() {
    return $this->rownum;
  }


  /**
  * Returns the current state of a transaction, indicating if we have begun a transaction, whether the transaction
  * has failed, or if we are not in a transaction.
  * @return int 0 = not started, 1 = in progress, -1 = error pending rollback/commit
  */
  function TransactionState() {
    global $_awl_dbconn;
    if ( !isset($this->connection) ) {
      if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
      $this->connection = $_awl_dbconn;
    }
    return $this->connection->TransactionState();
  }


  /**
  * Wrap the parent DB class Begin() so we can $qry->Begin() sometime before we $qry->Exec()
  */
  public function Begin() {
    global $_awl_dbconn;
    if ( !isset($this->connection) ) {
      if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
      $this->connection = $_awl_dbconn;
    }
    return $this->connection->Begin();
  }


  /**
  * Wrap the parent DB class Commit() so we can $qry->Commit() sometime after we $qry->Exec()
  */
  public function Commit() {
    if ( !isset($this->connection) ) {
      trigger_error("Cannot commit a transaction without an active statement.", E_USER_ERROR);
    }
    return $this->connection->Commit();
  }


  /**
  * Wrap the parent DB class Rollback() so we can $qry->Rollback() sometime after we $qry->Exec()
  */
  public function Rollback() {
    if ( !isset($this->connection) ) {
      trigger_error("Cannot rollback a transaction without an active statement.", E_USER_ERROR);
    }
    return $this->connection->Rollback();
  }


  /**
  * Simple SetSql() class which will reset the object with the querystring from the first argument.
  * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
  */
  public function SetSql( $sql ) {
    $this->rows = null;
    $this->execution_time = 0;
    $this->error_info = null;
    $this->rownum = -1;
    $this->bound_parameters = null;
    $this->bound_querystring = null;
    $this->sth = null;

    $this->querystring = $sql;
  }


  /**
  * Simple QDo() class which will re-use this query for whatever was passed in, and execute it
  * returning the result of the Exec() call.  We can't call it Do() since that's a reserved word...
  * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
  * @param mixed The values to replace into the SQL string.
  * @return boolean Success (true) or Failure (false)
  */
  public function QDo() {
    $argc = func_num_args();
    $args = func_get_args();

    $this->SetSql( array_shift($args) );
    if ( 1 < $argc ) {
      if ( is_array($args[0]) )
        $this->bound_parameters = $args[0];
      else
        $this->bound_parameters = $args;
    }

    return $this->Exec();
  }


  /**
  * Execute the query, logging any debugging.
  *
  * <b>Example</b>
  * So that you can nicely enable/disable the queries for a particular class, you
  * could use some of PHPs magic constants in your call.
  * <code>
  * $qry->Exec(__CLASS__, __LINE__, __FILE__);
  * </code>
  *
  *
  * @param string $location The name of the location for enabling debugging or just
  *                         to help our children find the source of a problem.
  * @param int $line The line number where Exec was called
  * @param string $file The file where Exec was called
  * @return boolean Success (true) or Failure (false)
  */
  function Exec( $location = null, $line = null, $file = null ) {
    global $c;
    if ( isset($location) ) $this->location = trim($location);
    if ( !isset($this->location) || $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);

    if ( isset($line) )     $this->location_line = intval($line);
    else if ( isset($this->location_line) ) $line = $this->location_line;

    if ( isset($file) )     $this->location_file = trim($file);
    else if ( isset($this->location_file) ) $file = $this->location_file;

    if ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
      $this->_log_query( $this->location, 'DBGQ', $this->querystring, $line, $file );
      if ( isset($this->bound_parameters) && !isset($this->sth) ) {
        foreach( $this->bound_parameters AS $k => $v ) {
          $this->_log_query( $this->location, 'DBGQ', sprintf('    "%s" => "%s"', $k, $v), $line, $file );
        }
      }
    }

    if ( isset($this->bound_parameters) ) {
      $this->Prepare();
    }

    $success = $this->Execute();

    if ( ! $success ) {
      // query failed
      $this->errorstring = sprintf( 'SQL error "%s" - %s"', $this->error_info[0], (isset($this->error_info[2]) ? $this->error_info[2] : ''));
      if ( isset($c->dbg['print_query_errors']) && $c->dbg['print_query_errors'] ) {
        printf( "\n=====================\n" );
        printf( "%s[%d] QF: %s\n", $file, $line, $this->errorstring);
        printf( "%s\n", $this->querystring );
        if ( isset($this->bound_parameters) ) {
          foreach( $this->bound_parameters AS $k => $v ) {
            printf( "    %-18s \t=> '%s'\n", "'$k'", $v );
          }
        }
        printf( ".....................\n" );
      }
      $this->_log_query( $this->location, 'QF', $this->errorstring, $line, $file );
      $this->_log_query( $this->location, 'QF', $this->querystring, $line, $file );
      if ( isset($this->bound_parameters) && ! ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) ) {
        foreach( $this->bound_parameters AS $k => $v ) {
          dbg_error_log( 'LOG-'.$this->location, ' Query: QF:     "%s" => "%s"', $k, $v);
        }
      }
    }
    elseif ( $this->execution_time > $this->query_time_warning ) {
     // if execution time is too long
      $this->_log_query( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
    }
    elseif ( isset($c->dbg['querystring']) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
     // query successful, but we're debugging and want to know how long it took anyway
      $this->_log_query( $this->location, 'DBGQ', "Took: $this->execution_time to find $this->rows rows.", $line, $file );
    }

    return $success;
  }


  /**
  * Fetch the next row from the query results
  * @param boolean $as_array True if thing to be returned is array
  * @return mixed query row
  */
  function Fetch($as_array = false) {

    if ( ! $this->sth || $this->rows == 0 ) return false; // no results
    if ( $this->rownum == null ) $this->rownum = -1;
    if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results

    $this->rownum++;
    $row = $this->sth->fetch( ($as_array ? PDO::FETCH_NUM : PDO::FETCH_OBJ) );

    return $row;
  }


  /**
   * Get any error information from the last query
   */
  function getErrorInfo() {
    return $this->error_info;
  }
}