Overview

Packages

  • awl
    • AuthPlugin
    • AwlDatabase
    • Browser
    • classEditor
    • DataEntry
    • DataUpdate
    • EMail
    • iCalendar
    • MenuSet
    • PgQuery
    • Session
    • Translation
    • User
    • Utilities
    • Validation
    • vCalendar
    • vComponent
    • XMLDocument
    • XMLElement
  • None
  • PHP

Classes

  • AwlDatabase
  • AwlDBDialect
  • AwlQuery
  • AwlUpgrader

Functions

  • _awl_connect_configured_database
  • Overview
  • Package
  • Function
  • Tree
  • Deprecated
  • Todo
  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 5 seconds, which can be overridden by setting a value
200:   * for $c->default_query_warning_threshold
201:   * @var double
202:   */
203:   public $query_time_warning = 5;
204:   /**#@-*/
205: 
206: 
207:  /**
208:   * Constructor
209:   * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
210:   * @param mixed The values to replace into the SQL string.
211:   * @return The AwlQuery object
212:   */
213:   function __construct() {
214:     global $_awl_dbconn, $c;
215:     $this->rows = null;
216:     $this->execution_time = 0;
217:     $this->error_info = null;
218:     if ( isset($c->default_query_warning_threshold) ) {
219:       $this->query_time_warning = $c->default_query_warning_threshold;
220:     }
221: 
222:     $this->rownum = -1;
223:     if ( isset($_awl_dbconn) ) $this->connection = $_awl_dbconn;
224:     else                       $this->connection = null;
225: 
226:     $argc = func_num_args();
227:     $args = func_get_args();
228: 
229:     $this->querystring = array_shift($args);
230:     if ( 1 < $argc ) {
231:       if ( is_array($args[0]) )
232:         $this->bound_parameters = $args[0];
233:       else
234:         $this->bound_parameters = $args;
235: //      print_r( $this->bound_parameters );
236:     }
237: 
238:     return $this;
239:   }
240: 
241: 
242:  /**
243:   * Use a different database connection for this query
244:   * @param  resource $new_connection The database connection to use.
245:   */
246:   function SetConnection( $new_connection, $options = null ) {
247:     if ( is_string($new_connection) || is_array($new_connection) ) {
248:       $dbuser = null;
249:       $dbpass = null;
250:       if ( is_array($new_connection) ) {
251:         $dsn = $new_connection['dsn'];
252:         if ( isset($new_connection['dbuser']) ) $dbuser = $new_connection['dbuser'];
253:         if ( isset($new_connection['dbpass']) ) $dbpass = $new_connection['dbpass'];
254:       }
255:       elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $new_connection, $matches ) ) {
256:         $dsn = $matches[2];
257:         if ( isset($matches[1]) && $matches[1] != '' ) {
258:           $dsn = $matches[1] . $dsn;
259:         }
260:         else {
261:           $dsn = 'pgsql:' . $dsn;
262:         }
263:         if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
264:         if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
265:       }
266:       if ( ! $new_connection = new AwlDatabase( $dsn, $dbuser, $dbpass, $options ) ) return;
267:     }
268:     $this->connection = $new_connection;
269:     return $new_connection;
270:   }
271: 
272: 
273: 
274:  /**
275:   * Get the current database connection for this query
276:   */
277:   function GetConnection() {
278:     return $this->connection;
279:   }
280: 
281: 
282:   /**
283:   * Log query, optionally with file and line location of the caller.
284:   *
285:   * This function should not really be used outside of AwlQuery.  For a more
286:   * useful generic logging interface consider calling dbg_error_log(...);
287:   *
288:   * @param string $locn    A string identifying the calling location.
289:   * @param string $tag     A tag string, e.g. identifying the type of event.
290:   * @param string $string  The information to be logged.
291:   * @param int    $line    The line number where the logged event occurred.
292:   * @param string $file    The file name where the logged event occurred.
293:   */
294:   function _log_query( $locn, $tag, $string, $line = 0, $file = "") {
295:     // replace more than one space with one space
296:     $string = preg_replace('/\s+/', ' ', $string);
297: 
298:     if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
299:       dbg_error_log( "LOG-$locn", " Query: %s: %s in '%s' on line %d", ($tag == 'QF' ? 'Error' : 'Possible slow query'), $tag, $file, $line );
300:     }
301: 
302:     while( strlen( $string ) > 0 )  {
303:       dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
304:       $string = substr( "$string", 240 );
305:     }
306:   }
307: 
308: 
309:   /**
310:   * Quote the given string so it can be safely used within string delimiters
311:   * in a query.  To be avoided, in general.
312:   *
313:   * @param mixed $str Data to be converted to a string suitable for including as a value in SQL.
314:   * @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped
315:   */
316:   public static function quote($str = null) {
317:     global $_awl_dbconn;
318:     if ( !isset($_awl_dbconn) ) {
319:       _awl_connect_configured_database();
320:     }
321:     return $_awl_dbconn->Quote($str);
322:   }
323: 
324: 
325:   /**
326:   * Bind some parameters.  This can be called in three ways:
327:   * 1) As Bind(':key','value), when using named parameters
328:   * 2) As Bind('value'), when using ? placeholders
329:   * 3) As Bind(array()), to overwrite the existing bound parameters.  The array may
330:   *    be ':name' => 'value' pairs or ordinal values, depending on whether the SQL
331:   *    is using ':name' or '?' style placeholders.
332:   *
333:   * @param mixed $args See details above.
334:   */
335:   function Bind() {
336:     $argc = func_num_args();
337:     $args = func_get_args();
338: 
339:     if ( $argc == 1 ) {
340:       if ( gettype($args[0]) == 'array' ) {
341:         $this->bound_parameters = $args[0];
342:       }
343:       else {
344:         $this->bound_parameters[] = $args[0];
345:       }
346:     }
347:     else {
348:       $this->bound_parameters[$args[0]] = $args[1];
349:     }
350:   }
351: 
352: 
353:   /**
354:   * Tell the database to prepare the query that we will execute
355:   */
356:   function Prepare() {
357:     global $c;
358: 
359:     if ( isset($this->sth) ) return; // Already prepared
360:     if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) return; //  No-op if we're expanding internally
361: 
362:     if ( !isset($this->connection) ) {
363:       _awl_connect_configured_database();
364:       $this->connection = $GLOBALS['_awl_dbconn'];
365:     }
366: 
367:     $this->sth = $this->connection->prepare( $this->querystring );
368: 
369:     if ( ! $this->sth ) {
370:       $this->error_info = $this->connection->errorInfo();
371:     }
372:     else $this->error_info = null;
373:   }
374: 
375:   /**
376:   * Tell the database to execute the query
377:   */
378:   function Execute() {
379:     global $c;
380: 
381:     if ( !isset($this->connection) ) {
382:       _awl_connect_configured_database();
383:       $this->connection = $GLOBALS['_awl_dbconn'];
384:     }
385:     if ( !is_object($this->connection) ) throw new Exception('Database not connected.');
386: 
387:     if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) {
388:       $this->bound_querystring = $this->querystring;
389:       if ( isset($this->bound_parameters) ) {
390:         $this->bound_querystring = $this->connection->ReplaceParameters($this->querystring,$this->bound_parameters);
391: //        printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
392: //        printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring);
393: //        print_r( $this->bound_parameters );
394:       }
395:       $t1 = microtime(true); // get start time
396:       $execute_result = $this->sth = $this->connection->query($this->bound_querystring);
397:     }
398:     else {
399:       $t1 = microtime(true); // get start time
400:       $execute_result = $this->sth = $this->connection->prepare($this->querystring);
401:       if ( $this->sth ) $execute_result = $this->sth->execute($this->bound_parameters);
402: //      printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
403: //      print_r( $this->bound_parameters );
404:     }
405:     $this->bound_querystring = null;
406: 
407:     if ( $execute_result === false ) {
408:       $this->error_info = $this->connection->errorInfo();
409:       return false;
410:     }
411:     $this->rows = $this->sth->rowCount();
412: 
413:     $i_took = microtime(true) - $t1;
414:     $c->total_query_time += $i_took;
415:     $this->execution_time = sprintf( "%2.06lf", $i_took);
416: 
417:     $this->error_info = null;
418:     return true;
419:   }
420: 
421: 
422:   /**
423:   * Return the query string we are planning to execute
424:   */
425:   function QueryString() {
426:     return $this->querystring;
427:   }
428: 
429: 
430:   /**
431:   * Return the parameters we are planning to substitute into the query string
432:   */
433:   function Parameters() {
434:     return $this->bound_parameters;
435:   }
436: 
437: 
438:   /**
439:   * Return the count of rows retrieved/affected
440:   */
441:   function rows() {
442:     return $this->rows;
443:   }
444: 
445: 
446:   /**
447:   * Return the current rownum in the retrieved set
448:   */
449:   function rownum() {
450:     return $this->rownum;
451:   }
452: 
453: 
454:   /**
455:   * Returns the current state of a transaction, indicating if we have begun a transaction, whether the transaction
456:   * has failed, or if we are not in a transaction.
457:   * @return int 0 = not started, 1 = in progress, -1 = error pending rollback/commit
458:   */
459:   function TransactionState() {
460:     global $_awl_dbconn;
461:     if ( !isset($this->connection) ) {
462:       if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
463:       $this->connection = $_awl_dbconn;
464:     }
465:     return $this->connection->TransactionState();
466:   }
467: 
468: 
469:   /**
470:   * Wrap the parent DB class Begin() so we can $qry->Begin() sometime before we $qry->Exec()
471:   */
472:   public function Begin() {
473:     global $_awl_dbconn;
474:     if ( !isset($this->connection) ) {
475:       if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
476:       $this->connection = $_awl_dbconn;
477:     }
478:     return $this->connection->Begin();
479:   }
480: 
481: 
482:   /**
483:   * Wrap the parent DB class Commit() so we can $qry->Commit() sometime after we $qry->Exec()
484:   */
485:   public function Commit() {
486:     if ( !isset($this->connection) ) {
487:       trigger_error("Cannot commit a transaction without an active statement.", E_USER_ERROR);
488:     }
489:     return $this->connection->Commit();
490:   }
491: 
492: 
493:   /**
494:   * Wrap the parent DB class Rollback() so we can $qry->Rollback() sometime after we $qry->Exec()
495:   */
496:   public function Rollback() {
497:     if ( !isset($this->connection) ) {
498:       trigger_error("Cannot rollback a transaction without an active statement.", E_USER_ERROR);
499:     }
500:     return $this->connection->Rollback();
501:   }
502: 
503: 
504:   /**
505:   * Simple SetSql() class which will reset the object with the querystring from the first argument.
506:   * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
507:   */
508:   public function SetSql( $sql ) {
509:     $this->rows = null;
510:     $this->execution_time = 0;
511:     $this->error_info = null;
512:     $this->rownum = -1;
513:     $this->bound_parameters = null;
514:     $this->bound_querystring = null;
515:     $this->sth = null;
516: 
517:     $this->querystring = $sql;
518:   }
519: 
520: 
521:   /**
522:   * Simple QDo() class which will re-use this query for whatever was passed in, and execute it
523:   * returning the result of the Exec() call.  We can't call it Do() since that's a reserved word...
524:   * @param  string The query string in PDO syntax with replacable '?' characters or bindable parameters.
525:   * @param mixed The values to replace into the SQL string.
526:   * @return boolean Success (true) or Failure (false)
527:   */
528:   public function QDo() {
529:     $argc = func_num_args();
530:     $args = func_get_args();
531: 
532:     $this->SetSql( array_shift($args) );
533:     if ( 1 < $argc ) {
534:       if ( is_array($args[0]) )
535:         $this->bound_parameters = $args[0];
536:       else
537:         $this->bound_parameters = $args;
538:     }
539: 
540:     return $this->Exec();
541:   }
542: 
543: 
544:   /**
545:   * Execute the query, logging any debugging.
546:   *
547:   * <b>Example</b>
548:   * So that you can nicely enable/disable the queries for a particular class, you
549:   * could use some of PHPs magic constants in your call.
550:   * <code>
551:   * $qry->Exec(__CLASS__, __LINE__, __FILE__);
552:   * </code>
553:   *
554:   *
555:   * @param string $location The name of the location for enabling debugging or just
556:   *                         to help our children find the source of a problem.
557:   * @param int $line The line number where Exec was called
558:   * @param string $file The file where Exec was called
559:   * @return boolean Success (true) or Failure (false)
560:   */
561:   function Exec( $location = null, $line = null, $file = null ) {
562:     global $c;
563:     if ( isset($location) ) $this->location = trim($location);
564:     if ( !isset($this->location) || $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
565: 
566:     if ( isset($line) )     $this->location_line = intval($line);
567:     else if ( isset($this->location_line) ) $line = $this->location_line;
568: 
569:     if ( isset($file) )     $this->location_file = trim($file);
570:     else if ( isset($this->location_file) ) $file = $this->location_file;
571: 
572:     if ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
573:       $this->_log_query( $this->location, 'DBGQ', $this->querystring, $line, $file );
574:       if ( isset($this->bound_parameters) && !isset($this->sth) ) {
575:         foreach( $this->bound_parameters AS $k => $v ) {
576:           $this->_log_query( $this->location, 'DBGQ', sprintf('    "%s" => "%s"', $k, $v), $line, $file );
577:         }
578:       }
579:     }
580: 
581:     if ( isset($this->bound_parameters) ) {
582:       $this->Prepare();
583:     }
584: 
585:     $success = $this->Execute();
586: 
587:     if ( ! $success ) {
588:       // query failed
589:       $this->errorstring = sprintf( 'SQL error "%s" - %s"', $this->error_info[0], (isset($this->error_info[2]) ? $this->error_info[2] : ''));
590:       if ( isset($c->dbg['print_query_errors']) && $c->dbg['print_query_errors'] ) {
591:         printf( "\n=====================\n" );
592:         printf( "%s[%d] QF: %s\n", $file, $line, $this->errorstring);
593:         printf( "%s\n", $this->querystring );
594:         if ( isset($this->bound_parameters) ) {
595:           foreach( $this->bound_parameters AS $k => $v ) {
596:             printf( "    %-18s \t=> '%s'\n", "'$k'", $v );
597:           }
598:         }
599:         printf( ".....................\n" );
600:       }
601:       $this->_log_query( $this->location, 'QF', $this->errorstring, $line, $file );
602:       $this->_log_query( $this->location, 'QF', $this->querystring, $line, $file );
603:       if ( isset($this->bound_parameters) && ! ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) ) {
604:         foreach( $this->bound_parameters AS $k => $v ) {
605:           dbg_error_log( 'LOG-'.$this->location, ' Query: QF:     "%s" => "%s"', $k, $v);
606:         }
607:       }
608:     }
609:     elseif ( $this->execution_time > $this->query_time_warning ) {
610:      // if execution time is too long
611:       $this->_log_query( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
612:     }
613:     elseif ( isset($c->dbg['querystring']) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
614:      // query successful, but we're debugging and want to know how long it took anyway
615:       $this->_log_query( $this->location, 'DBGQ', "Took: $this->execution_time to find $this->rows rows.", $line, $file );
616:     }
617: 
618:     return $success;
619:   }
620: 
621: 
622:   /**
623:   * Fetch the next row from the query results
624:   * @param boolean $as_array True if thing to be returned is array
625:   * @return mixed query row
626:   */
627:   function Fetch($as_array = false) {
628: 
629:     if ( ! $this->sth || $this->rows == 0 ) return false; // no results
630:     if ( $this->rownum == null ) $this->rownum = -1;
631:     if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
632: 
633:     $this->rownum++;
634:     $row = $this->sth->fetch( ($as_array ? PDO::FETCH_NUM : PDO::FETCH_OBJ) );
635: 
636:     return $row;
637:   }
638: 
639: 
640:   /**
641:    * Get any error information from the last query
642:    */
643:   function getErrorInfo() {
644:     return $this->error_info;
645:   }
646: 
647: 
648:   /**
649:   * Set the slow query threshold for this query (and subsequent queries using this object). The
650:   * overall default is $c->slow_query_threshold, or 5 seconds if that is not set.
651:   * @param double $new_threshold The new threshold for slow queries to be logged
652:   * @return double The old threshold.
653:   */
654:   function SetSlowQueryThreshold( $new_threshold ) {
655:     $old = $this->query_time_warning;
656:     $this->query_time_warning = $new_threshold;
657:     return $oldval;
658:   }
659: 
660: }
661: 
662: 
AWL API documentation generated by ApiGen 2.8.0