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
203 public $query_time_warning = 5;
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
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
277 function GetConnection() {
278 return $this->connection;
279 }
280
281
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
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
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
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
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
425 function QueryString() {
426 return $this->querystring;
427 }
428
429
433 function Parameters() {
434 return $this->bound_parameters;
435 }
436
437
441 function rows() {
442 return $this->rows;
443 }
444
445
449 function rownum() {
450 return $this->rownum;
451 }
452
453
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
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
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
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
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
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
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
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
643 function getErrorInfo() {
644 return $this->error_info;
645 }
646
647
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
SetConnection( $new_connection, $options=null)
Definition: AwlQuery.php:246
Exec( $location=null, $line=null, $file=null)
Definition: AwlQuery.php:561
Rollback()
Definition: AwlQuery.php:496
_log_query( $locn, $tag, $string, $line=0, $file="")
Definition: AwlQuery.php:294
GetConnection()
Definition: AwlQuery.php:277
Parameters()
Definition: AwlQuery.php:433
__construct()
Definition: AwlQuery.php:213
Fetch($as_array=false)
Definition: AwlQuery.php:627
static quote($str=null)
Definition: AwlQuery.php:316
SetSlowQueryThreshold( $new_threshold)
Definition: AwlQuery.php:654
TransactionState()
Definition: AwlQuery.php:459
SetSql( $sql)
Definition: AwlQuery.php:508
QueryString()
Definition: AwlQuery.php:425
getErrorInfo()
Definition: AwlQuery.php:643