Andrew's Web Libraries (AWL)
Loading...
Searching...
No Matches
PgQuery.php
1<?php
36if ( ! 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
46it in your php.ini file by adding a line like this:</p>
47<pre>
48extension=pgsql.so
49</pre>
50 </body>
51 </html>
52EOERRMSG;
53 exit;
54}
55
56require_once("AWLUtilities.php");
57
61function 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>
84EOERRMSG;
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
109if ( !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
147function 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
182function 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
219function 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
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
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