Andrew's Web Libraries (AWL)
PgQuery.php
1 <?php
36 if ( ! function_exists('pg_Connect') ) {
37  echo <<<EOERRMSG
38 <html>
39 <head>
40 <title>PostgreSQL Support Not Present</title>
41 </head>
42 <body>
43 <h1>PostgreSQL Support Not Present</h1>
44 <h3>PHP is not configured to support the PostgreSQL database</h3>
45 <p>You need to ensure that the PostgreSQL support module is installed, and then to configure
46 it in your php.ini file by adding a line like this:</p>
47 <pre>
48 extension=pgsql.so
49 </pre>
50  </body>
51  </html>
52 EOERRMSG;
53  exit;
54 }
55 
56 require_once("AWLUtilities.php");
57 
61 function connect_configured_database() {
62  global $c, $dbconn;
63 
64  if ( isset($dbconn) ) return;
68  $dbconn = false;
69  dbg_error_log('pgquery', 'Attempting to connect to database');
70  if ( isset($c->pg_connect) && is_array($c->pg_connect) ) {
71  foreach( $c->pg_connect AS $k => $v ) {
72  if ( !$dbconn ) {
73  if ( $dbconn = ((isset($c->use_persistent) && $c->use_persistent) ? pg_pConnect($v) : pg_Connect($v) ) ) break;
74  }
75  }
76  }
77  if ( ! $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 PostgreSQL database</h3>
82  </body>
83  </html>
84 EOERRMSG;
85  if ( isset($c->pg_connect) && is_array($c->pg_connect) ) {
86  dbg_error_log("ERROR", "Failed to connect to database" );
87  }
88  exit;
89  }
90 
91  if ( isset($c->db_schema) && $c->db_schema != '' ) {
92  $result = pg_exec( $dbconn, "SET Search_path TO ".$c->db_schema.",public;" );
93  $row = pg_fetch_array($result, 0);
94  }
95 
96  $result = pg_exec( $dbconn, "SELECT version()" );
97  $row = pg_fetch_array($result, 0);
98  $c->found_dbversion = preg_replace( '/^PostgreSQL (\d+\.\d+)\..*$/i', '$1', $row[0]);
99 }
100 
101 
107 $dbconn = null;
108 
109 if ( !function_exists('duration') ) {
121  function duration( $t1, $t2 ) {
122  list ( $ms1, $s1 ) = explode ( " ", $t1 ); // Format times - by spliting seconds and microseconds
123  list ( $ms2, $s2 ) = explode ( " ", $t2 );
124  $s1 = $s2 - $s1;
125  $s1 = $s1 + ( $ms2 -$ms1 );
126  return $s1; // Return duration of time
127  }
128 }
129 
130 
147 function qpg($str = null) {
148  global $c;
149 
150  switch (strtolower(gettype($str))) {
151  case 'null':
152  $rv = 'NULL';
153  break;
154  case 'integer':
155  case 'double' :
156  return $str;
157  case 'boolean':
158  $rv = $str ? 'TRUE' : 'FALSE';
159  break;
160  case 'string':
161  default:
162  $str = str_replace("'", "''", $str);
163  //PostgreSQL treats a backslash as an escape character.
164  $str = str_replace('\\', '\\\\', $str);
165  $rv = "'$str'";
166  if ( !isset($c->found_dbversion) || $c->found_dbversion > 8.0 ) $rv = 'E'.$rv;
167  }
168  return $rv;
169 }
170 
182 function clean_string( $unclean, $type = 'full' ) {
183  if ( ! isset($unclean) ) return null;
184  if ( is_array($unclean) ) {
185  $result = array();
186  foreach( $unclean AS $k => $v ) {
187  $result[$k] = clean_string( $v, $type );
188  }
189  return $result;
190  }
191  if ( $type != 'basic' ) $cleaned = strtolower($unclean); else $cleaned = &$unclean;
192  $cleaned = preg_replace( "/['\"!\\\\()\[\]|*\/{}&%@~;:?<>]/", '', $cleaned ); //"// Stupid Bluefish Syntax highlighting...
193  dbg_error_log( "PgQuery", "clean_string: Cleaned string from <<%s>> to <<%s>>", $unclean, $cleaned );
194  return $cleaned;
195 }
196 
219 function awl_replace_sql_args() {
220  $argc = func_num_args(); //number of arguments passed to the function
221  $qry = func_get_arg(0); //first argument
222  $args = func_get_args(); //all argument in an array
223 
224  if ( is_array($qry) ) {
225  $qry = $args[0][0];
226  $args = $args[0];
227  $argc = count($args);
228  }
229 
230 // building query string by replacing ? with
231 // escaped parameters
232  $parts = explode( '?', $qry );
233  $querystring = $parts[0];
234  $z = min( count($parts), $argc );
235 
236  for( $i = 1; $i < $z; $i++ ) {
237  $arg = $args[$i];
238  if ( !isset($arg) ) {
239  $querystring .= 'NULL';
240  }
241  elseif ( is_array($arg) && $arg['plain'] != '' ) {
242  // We abuse this, but people should access it through the PgQuery::Plain($v) function
243  $querystring .= $arg['plain'];
244  }
245  else {
246  $querystring .= qpg($arg); //parameter
247  }
248  $querystring .= $parts[$i]; //extras eg. ","
249  }
250  if ( isset($parts[$z]) ) $querystring .= $parts[$z]; //puts last part on the end
251 
252  return $querystring;
253 }
254 
255 
277 class PgQuery
278 {
287  var $connection;
288 
294  var $querystring;
295 
301  var $result;
302 
308  var $rownum = -1;
309 
315  var $location;
316 
322  var $object;
323 
334  var $rows;
335 
341  var $errorstring;
342 
348  var $execution_time;
349 
357  var $query_time_warning = 0.3;
367  function __construct() {
368  global $dbconn;
369  $this->result = 0;
370  $this->rows = 0;
371  $this->execution_time = 0;
372  $this->rownum = -1;
373  $this->connection = $dbconn;
374 
375  $argc = func_num_args();
376 
377  if ( 1 < $argc ) {
378  $this->querystring = awl_replace_sql_args( func_get_args() );
379  }
380  else {
381  // If we are only called with a single argument, we do
382  // nothing special with any question marks.
383  $this->querystring = func_get_arg(0);
384  }
385 
386  return $this;
387  }
388 
389 
394  function SetConnection( $new_connection ) {
395  $this->connection = $new_connection;
396  }
397 
398 
399 
412  function _log_error( $locn, $tag, $string, $line = 0, $file = "") {
413  // replace more than one space with one space
414  $string = preg_replace('/\s+/', ' ', $string);
415 
416  if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
417  dbg_error_log( "LOG-$locn", " Query: %s: Error in '%s' on line %d", $tag, $file, $line );
418  }
419 
420  while( strlen( $string ) > 0 ) {
421  dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
422  $string = substr( "$string", 240 );
423  }
424  }
425 
429  function rows() {
430  return $this->rows;
431  }
432 
443  function quote($str = null) {
444  return qpg($str);
445  }
446 
455  function Plain( $field ) {
456  // Abuse the array type to extend our ability to avoid \\ and ' replacement
457  $rv = array( 'plain' => $field );
458  return $rv;
459  }
460 
478  function Exec( $location = '', $line = 0, $file = '' ) {
479  global $debuggroups, $c, $dbconn;
480  if ( !isset($this->connection) ) {
481  if ( !isset($dbconn) ) {
482  connect_configured_database();
483  }
484  $this->connection = $dbconn;
485  }
486  $this->location = trim($location);
487  if ( $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
488 
489  if ( isset($debuggroups['querystring']) || isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
490  $this->_log_error( $this->location, 'DBGQ', $this->querystring, $line, $file );
491  }
492 
493  $t1 = microtime(); // get start time
494  $this->result = @pg_exec( $this->connection, $this->querystring ); // execute the query
495  $this->rows = ($this->result ? pg_numrows($this->result) : -1); // number of rows returned
496  $t2 = microtime(); // get end time
497  $i_took = duration( $t1, $t2 ); // calculate difference
498  $c->total_query_time += $i_took;
499  $this->execution_time = sprintf( "%2.06lf", $i_took);
500 
501  if ( !$this->result ) {
502  // query simply failed
503  $this->errorstring = @pg_errormessage(); // returns database error message
504  $this->_log_error( $this->location, 'QF', $this->querystring, $line, $file );
505  $this->_log_error( $this->location, 'QF', $this->errorstring, $line, $file );
506  }
507  elseif ( $this->execution_time > $this->query_time_warning ) {
508  // if execution time is too long
509  $this->_log_error( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
510  }
511  elseif ( isset($debuggroups[$this->location]) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
512  // query successful, but we're debugging and want to know how long it took anyway
513  $this->_log_error( $this->location, 'DBGQ', "Took: $this->execution_time for $this->querystring to find $this->rows rows.", $line, $file );
514  }
515 
516  return $this->result;
517  }
518 
519 
525  function Fetch($as_array = false) {
526  global $c, $debuggroups;
527 
528  if ( ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 )
529  || (isset($c) && is_object($c) && ( isset($c->dbg[strtolower($this->location)]) && isset($c->dbg[strtolower($this->location)]) )
530  || isset($c->dbg['ALL']) ) ) {
531  $this->_log_error( $this->location, "Fetch", "$this->result Rows: $this->rows, Rownum: $this->rownum");
532  }
533  if ( ! $this->result ) return false; // no results
534  if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
535 
536  $this->rownum++;
537  if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) {
538  $this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" );
539  }
540  if ( $as_array )
541  {
542  $this->object = pg_fetch_array($this->result, $this->rownum);
543  }
544  else
545  {
546  $this->object = pg_fetch_object($this->result, $this->rownum);
547  }
548 
549  return $this->object;
550  }
551 
570  function UnFetch() {
571  global $debuggroups;
572  $this->rownum--;
573  if ( $this->rownum < -1 ) $this->rownum = -1;
574  }
575 
581  function FetchBackwards($as_array = false) {
582  global $debuggroups;
583 
584  if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 ) {
585  $this->_log_error( $this->location, "FetchBackwards", "$this->result Rows: $this->rows, Rownum: $this->rownum");
586  }
587  if ( ! $this->result ) return false;
588  if ( ($this->rownum - 1) == -1 ) return false;
589  if ( $this->rownum == -1 ) $this->rownum = $this->rows;
590 
591  $this->rownum--;
592 
593  if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) {
594  $this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" );
595  }
596  if ( $as_array )
597  {
598  $this->object = pg_fetch_array($this->result, $this->rownum);
599  }
600  else
601  {
602  $this->object = pg_fetch_object($this->result, $this->rownum);
603  }
604 
605  return $this->object;
606  }
607 
615  function BuildOptionList( $current = '', $location = 'options', $parameters = false ) {
616  global $debuggroups;
617  $result = '';
618  $translate = false;
619 
620  if ( isset($maxwidth) ) unset($maxwidth);
621  if ( is_array($parameters) ) {
622  if ( isset($parameters['maxwidth']) ) $maxwidth = max(4,intval($parameters['maxwidth']));
623  if ( isset($parameters['translate']) ) $translate = true;
624  }
625 
626  // The query may already have been executed
627  if ( $this->rows > 0 || $this->Exec($location) ) {
628  $this->rownum = -1;
629  while( $row = $this->Fetch(true) )
630  {
631  if (is_array($current)) {
632  $selected = ( ( in_array($row[0],$current,true) || in_array($row[1],$current,true)) ? ' selected="selected"' : '' );
633  }
634  else {
635  $selected = ( ( "$row[0]" == "$current" || "$row[1]" == "$current" ) ? ' selected="selected"' : '' );
636  }
637  $display_value = $row[1];
638  if ( isset($translate) ) $display_value = translate( $display_value );
639  if ( isset($maxwidth) ) $display_value = substr( $display_value, 0, $maxwidth);
640  $nextrow = "<option value=\"".htmlspecialchars($row[0])."\"$selected>".htmlspecialchars($display_value)."</option>";
641  $result .= $nextrow;
642  }
643  }
644  return $result;
645  }
646 
647 }
648 
Plain( $field)
Definition: PgQuery.php:455
__construct()
Definition: PgQuery.php:367
BuildOptionList( $current='', $location='options', $parameters=false)
Definition: PgQuery.php:615
Exec( $location='', $line=0, $file='')
Definition: PgQuery.php:478
UnFetch()
Definition: PgQuery.php:570
_log_error( $locn, $tag, $string, $line=0, $file="")
Definition: PgQuery.php:412
rows()
Definition: PgQuery.php:429
FetchBackwards($as_array=false)
Definition: PgQuery.php:581
Fetch($as_array=false)
Definition: PgQuery.php:525
quote($str=null)
Definition: PgQuery.php:443
SetConnection( $new_connection)
Definition: PgQuery.php:394