Andrew's Web Libraries (AWL)
AwlQuery.php
1 <?php
11 require_once('AwlDatabase.php');
12 
40 function _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>
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 
117 class AwlQuery
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 
AwlQuery\QDo
QDo()
Definition: AwlQuery.php:528
AwlQuery\Rollback
Rollback()
Definition: AwlQuery.php:496
AwlQuery\getErrorInfo
getErrorInfo()
Definition: AwlQuery.php:643
AwlQuery\SetSql
SetSql( $sql)
Definition: AwlQuery.php:508
AwlQuery\Fetch
Fetch($as_array=false)
Definition: AwlQuery.php:627
AwlQuery\rownum
rownum()
Definition: AwlQuery.php:449
AwlQuery\Begin
Begin()
Definition: AwlQuery.php:472
AwlQuery\quote
static quote($str=null)
Definition: AwlQuery.php:316
AwlQuery\__construct
__construct()
Definition: AwlQuery.php:213
AwlQuery\QueryString
QueryString()
Definition: AwlQuery.php:425
AwlQuery\Prepare
Prepare()
Definition: AwlQuery.php:356
AwlQuery\Commit
Commit()
Definition: AwlQuery.php:485
AwlQuery
Definition: AwlQuery.php:117
AwlQuery\_log_query
_log_query( $locn, $tag, $string, $line=0, $file="")
Definition: AwlQuery.php:294
AwlQuery\rows
rows()
Definition: AwlQuery.php:441
AwlQuery\Parameters
Parameters()
Definition: AwlQuery.php:433
AwlQuery\SetSlowQueryThreshold
SetSlowQueryThreshold( $new_threshold)
Definition: AwlQuery.php:654
AwlQuery\GetConnection
GetConnection()
Definition: AwlQuery.php:277
AwlQuery\TransactionState
TransactionState()
Definition: AwlQuery.php:459
AwlQuery\Exec
Exec( $location=null, $line=null, $file=null)
Definition: AwlQuery.php:561
AwlQuery\Execute
Execute()
Definition: AwlQuery.php:378
AwlQuery\Bind
Bind()
Definition: AwlQuery.php:335
AwlQuery\SetConnection
SetConnection( $new_connection, $options=null)
Definition: AwlQuery.php:246
AwlDatabase
Definition: AwlDatabase.php:58