Andrew's Web Libraries (AWL)
AwlQuery.php
1<?php
11require_once('AwlDatabase.php');
12
40function _awl_connect_configured_database() {
41 global $c, $_awl_dbconn;
42
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>
84EOERRMSG;
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
118{
126 protected $connection;
127
132 protected $querystring;
133
138 protected $bound_querystring;
139
144 protected $bound_parameters;
145
150 protected $sth;
151
156 protected $result;
157
162 protected $rownum = null;
163
168 protected $rows;
169
174 protected $error_info;
175
181 protected $execution_time;
182
193 public $location;
194 public $location_line;
195 public $location_file;
196
205 public $query_time_warning = 5;
215 function __construct() {
216 global $_awl_dbconn, $c;
217 $this->rows = null;
218 $this->execution_time = 0;
219 $this->error_info = null;
220 if ( isset($c->default_query_warning_threshold) ) {
221 $this->query_time_warning = $c->default_query_warning_threshold;
222 }
223
224 $this->rownum = -1;
225 if ( isset($_awl_dbconn) ) $this->connection = $_awl_dbconn;
226 else $this->connection = null;
227
228 $argc = func_num_args();
229 $args = func_get_args();
230
231 $this->querystring = array_shift($args);
232 if ( 1 < $argc ) {
233 if ( is_array($args[0]) )
234 $this->bound_parameters = $args[0];
235 else
236 $this->bound_parameters = $args;
237// print_r( $this->bound_parameters );
238 }
239
240 return $this;
241 }
242
243
248 function SetConnection( $new_connection, $options = null ) {
249 if ( is_string($new_connection) || is_array($new_connection) ) {
250 $dbuser = null;
251 $dbpass = null;
252 if ( is_array($new_connection) ) {
253 $dsn = $new_connection['dsn'];
254 if ( isset($new_connection['dbuser']) ) $dbuser = $new_connection['dbuser'];
255 if ( isset($new_connection['dbpass']) ) $dbpass = $new_connection['dbpass'];
256 }
257 elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $new_connection, $matches ) ) {
258 $dsn = $matches[2];
259 if ( isset($matches[1]) && $matches[1] != '' ) {
260 $dsn = $matches[1] . $dsn;
261 }
262 else {
263 $dsn = 'pgsql:' . $dsn;
264 }
265 if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
266 if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
267 }
268 if ( ! $new_connection = new AwlDatabase( $dsn, $dbuser, $dbpass, $options ) ) return;
269 }
270 $this->connection = $new_connection;
271 return $new_connection;
272 }
273
274
275
279 function GetConnection() {
280 return $this->connection;
281 }
282
283
296 function _log_query( $locn, $tag, $string, $line = 0, $file = "") {
297 // replace more than one space with one space
298 $string = preg_replace('/\s+/', ' ', $string);
299
300 if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
301 dbg_error_log( "LOG-$locn", " Query: %s: %s in '%s' on line %d", ($tag == 'QF' ? 'Error' : 'Possible slow query'), $tag, $file, $line );
302 }
303
304 while( strlen( $string ) > 0 ) {
305 dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
306 $string = substr( "$string", 240 );
307 }
308 }
309
310
318 public static function quote($str = null) {
319 global $_awl_dbconn;
320 if ( !isset($_awl_dbconn) ) {
321 _awl_connect_configured_database();
322 }
323 return $_awl_dbconn->Quote($str);
324 }
325
326
337 function Bind() {
338 $argc = func_num_args();
339 $args = func_get_args();
340
341 if ( $argc == 1 ) {
342 if ( gettype($args[0]) == 'array' ) {
343 $this->bound_parameters = $args[0];
344 }
345 else {
346 $this->bound_parameters[] = $args[0];
347 }
348 }
349 else {
350 $this->bound_parameters[$args[0]] = $args[1];
351 }
352 }
353
354
358 function Prepare() {
359 global $c;
360
361 if ( isset($this->sth) ) return; // Already prepared
362 if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) return; // No-op if we're expanding internally
363
364 if ( !isset($this->connection) ) {
365 _awl_connect_configured_database();
366 $this->connection = $GLOBALS['_awl_dbconn'];
367 }
368
369 $this->sth = $this->connection->prepare( $this->querystring );
370
371 if ( ! $this->sth ) {
372 $this->error_info = $this->connection->errorInfo();
373 }
374 else $this->error_info = null;
375 }
376
380 function Execute() {
381 global $c;
382
383 if ( !isset($this->connection) ) {
384 _awl_connect_configured_database();
385 $this->connection = $GLOBALS['_awl_dbconn'];
386 }
387 if ( !is_object($this->connection) ) throw new Exception('Database not connected.');
388
389 if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) {
390 $this->bound_querystring = $this->querystring;
391 if ( isset($this->bound_parameters) ) {
392 $this->bound_querystring = $this->connection->ReplaceParameters($this->querystring,$this->bound_parameters);
393// printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
394// printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring);
395// print_r( $this->bound_parameters );
396 }
397 $t1 = microtime(true); // get start time
398 $execute_result = $this->sth = $this->connection->query($this->bound_querystring);
399 }
400 else {
401 $t1 = microtime(true); // get start time
402 $execute_result = $this->sth = $this->connection->prepare($this->querystring);
403 if ( $this->sth ) $execute_result = $this->sth->execute($this->bound_parameters);
404// printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
405// print_r( $this->bound_parameters );
406 }
407 $this->bound_querystring = null;
408
409 if ( $execute_result === false ) {
410 $this->error_info = $this->connection->errorInfo();
411 return false;
412 }
413 $this->rows = $this->sth->rowCount();
414
415 $i_took = microtime(true) - $t1;
416 $c->total_query_time += $i_took;
417 $this->execution_time = sprintf( "%2.06lf", $i_took);
418
419 $this->error_info = null;
420 return true;
421 }
422
423
427 function QueryString() {
428 return $this->querystring;
429 }
430
431
435 function Parameters() {
436 return $this->bound_parameters;
437 }
438
439
443 function rows() {
444 return $this->rows;
445 }
446
447
451 function rownum() {
452 return $this->rownum;
453 }
454
455
461 function TransactionState() {
462 global $_awl_dbconn;
463 if ( !isset($this->connection) ) {
464 if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
465 $this->connection = $_awl_dbconn;
466 }
467 return $this->connection->TransactionState();
468 }
469
470
474 public function Begin() {
475 global $_awl_dbconn;
476 if ( !isset($this->connection) ) {
477 if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
478 $this->connection = $_awl_dbconn;
479 }
480 return $this->connection->Begin();
481 }
482
483
487 public function Commit() {
488 if ( !isset($this->connection) ) {
489 trigger_error("Cannot commit a transaction without an active statement.", E_USER_ERROR);
490 }
491 return $this->connection->Commit();
492 }
493
494
498 public function Rollback() {
499 if ( !isset($this->connection) ) {
500 trigger_error("Cannot rollback a transaction without an active statement.", E_USER_ERROR);
501 }
502 return $this->connection->Rollback();
503 }
504
505
510 public function SetSql( $sql ) {
511 $this->rows = null;
512 $this->execution_time = 0;
513 $this->error_info = null;
514 $this->rownum = -1;
515 $this->bound_parameters = null;
516 $this->bound_querystring = null;
517 $this->sth = null;
518
519 $this->querystring = $sql;
520 }
521
522
530 public function QDo() {
531 $argc = func_num_args();
532 $args = func_get_args();
533
534 $this->SetSql( array_shift($args) );
535 if ( 1 < $argc ) {
536 if ( is_array($args[0]) )
537 $this->bound_parameters = $args[0];
538 else
539 $this->bound_parameters = $args;
540 }
541
542 return $this->Exec();
543 }
544
545
563 function Exec( $location = null, $line = null, $file = null ) {
564 global $c;
565 if ( isset($location) ) $this->location = trim($location);
566 if ( !isset($this->location) || $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
567
568 if ( isset($line) ) $this->location_line = intval($line);
569 else if ( isset($this->location_line) ) $line = $this->location_line;
570
571 if ( isset($file) ) $this->location_file = trim($file);
572 else if ( isset($this->location_file) ) $file = $this->location_file;
573
574 if ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
575 $this->_log_query( $this->location, 'DBGQ', $this->querystring, $line, $file );
576 if ( isset($this->bound_parameters) && !isset($this->sth) ) {
577 foreach( $this->bound_parameters AS $k => $v ) {
578 $this->_log_query( $this->location, 'DBGQ', sprintf(' "%s" => "%s"', $k, $v), $line, $file );
579 }
580 }
581 }
582
583 if ( isset($this->bound_parameters) ) {
584 $this->Prepare();
585 }
586
587 $success = $this->Execute();
588
589 if ( ! $success ) {
590 // query failed
591 $this->errorstring = sprintf( 'SQL error "%s" - %s"', $this->error_info[0], (isset($this->error_info[2]) ? $this->error_info[2] : ''));
592 if ( isset($c->dbg['print_query_errors']) && $c->dbg['print_query_errors'] ) {
593 printf( "\n=====================\n" );
594 printf( "%s[%d] QF: %s\n", $file, $line, $this->errorstring);
595 printf( "%s\n", $this->querystring );
596 if ( isset($this->bound_parameters) ) {
597 foreach( $this->bound_parameters AS $k => $v ) {
598 printf( " %-18s \t=> '%s'\n", "'$k'", $v );
599 }
600 }
601 printf( ".....................\n" );
602 }
603 $this->_log_query( $this->location, 'QF', $this->errorstring, $line, $file );
604 $this->_log_query( $this->location, 'QF', $this->querystring, $line, $file );
605 if ( isset($this->bound_parameters) && ! ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) ) {
606 foreach( $this->bound_parameters AS $k => $v ) {
607 dbg_error_log( 'LOG-'.$this->location, ' Query: QF: "%s" => "%s"', $k, $v);
608 }
609 }
610 }
611 elseif ( $this->execution_time > $this->query_time_warning ) {
612 // if execution time is too long
613 $this->_log_query( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
614 }
615 elseif ( isset($c->dbg['querystring']) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
616 // query successful, but we're debugging and want to know how long it took anyway
617 $this->_log_query( $this->location, 'DBGQ', "Took: $this->execution_time to find $this->rows rows.", $line, $file );
618 }
619
620 return $success;
621 }
622
623
629 function Fetch($as_array = false) {
630
631 if ( ! $this->sth || $this->rows == 0 ) return false; // no results
632 if ( $this->rownum == null ) $this->rownum = -1;
633 if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
634
635 $this->rownum++;
636 $row = $this->sth->fetch( ($as_array ? PDO::FETCH_NUM : PDO::FETCH_OBJ) );
637
638 return $row;
639 }
640
641
645 function getErrorInfo() {
646 return $this->error_info;
647 }
648
649
656 function SetSlowQueryThreshold( $new_threshold ) {
657 $old = $this->query_time_warning;
658 $this->query_time_warning = $new_threshold;
659 return $oldval;
660 }
661
662}
663
SetConnection( $new_connection, $options=null)
Definition: AwlQuery.php:248
Exec( $location=null, $line=null, $file=null)
Definition: AwlQuery.php:563
Rollback()
Definition: AwlQuery.php:498
_log_query( $locn, $tag, $string, $line=0, $file="")
Definition: AwlQuery.php:296
GetConnection()
Definition: AwlQuery.php:279
Parameters()
Definition: AwlQuery.php:435
__construct()
Definition: AwlQuery.php:215
Fetch($as_array=false)
Definition: AwlQuery.php:629
static quote($str=null)
Definition: AwlQuery.php:318
SetSlowQueryThreshold( $new_threshold)
Definition: AwlQuery.php:656
TransactionState()
Definition: AwlQuery.php:461
SetSql( $sql)
Definition: AwlQuery.php:510
QueryString()
Definition: AwlQuery.php:427
getErrorInfo()
Definition: AwlQuery.php:645