Back to index

awl  0.53
PgQuery.php
Go to the documentation of this file.
00001 <?php
00036 if ( ! function_exists('pg_Connect') ) {
00037   echo <<<EOERRMSG
00038 <html>
00039 <head>
00040 <title>PostgreSQL Support Not Present</title>
00041 </head>
00042 <body>
00043 <h1>PostgreSQL Support Not Present</h1>
00044 <h3>PHP is not configured to support the PostgreSQL database</h3>
00045 <p>You need to ensure that the PostgreSQL support module is installed, and then to configure
00046 it in your php.ini file by adding a line like this:</p>
00047 <pre>
00048 extension=pgsql.so
00049 </pre>
00050   </body>
00051   </html>
00052 EOERRMSG;
00053   exit;
00054 }
00055 
00056 require_once("AWLUtilities.php");
00057 
00061 function connect_configured_database() {
00062   global $c, $dbconn;
00063 
00064   if ( isset($dbconn) ) return;
00068   $dbconn = false;
00069   dbg_error_log('pgquery', 'Attempting to connect to database');
00070   if ( isset($c->pg_connect) && is_array($c->pg_connect) ) {
00071     foreach( $c->pg_connect AS $k => $v ) {
00072       if ( !$dbconn ) {
00073         if ( $dbconn = ((isset($c->use_persistent) && $c->use_persistent) ? pg_pConnect($v) : pg_Connect($v) ) ) break;
00074       }
00075     }
00076   }
00077   if ( ! $dbconn ) {
00078     echo <<<EOERRMSG
00079   <html><head><title>Database Connection Failure</title></head><body>
00080   <h1>Database Error</h1>
00081   <h3>Could not connect to PostgreSQL database</h3>
00082   </body>
00083   </html>
00084 EOERRMSG;
00085     if ( isset($c->pg_connect) && is_array($c->pg_connect) ) {
00086       dbg_error_log("ERROR", "Failed to connect to database" );
00087     }
00088     exit;
00089   }
00090 
00091   if ( isset($c->db_schema) && $c->db_schema != '' ) {
00092     $result = pg_exec( $dbconn, "SET Search_path TO ".$c->db_schema.",public;" );
00093     $row = pg_fetch_array($result, 0);
00094   }
00095 
00096   $result = pg_exec( $dbconn, "SELECT version()" );
00097   $row = pg_fetch_array($result, 0);
00098   $c->found_dbversion = preg_replace( '/^PostgreSQL (\d+\.\d+)\..*$/i', '$1', $row[0]);
00099 }
00100 
00101 
00107 $dbconn = null;
00108 
00109 if ( !function_exists('duration') ) {
00121   function duration( $t1, $t2 ) {
00122     list ( $ms1, $s1 ) = explode ( " ", $t1 );   // Format times - by spliting seconds and microseconds
00123     list ( $ms2, $s2 ) = explode ( " ", $t2 );
00124     $s1 = $s2 - $s1;
00125     $s1 = $s1 + ( $ms2 -$ms1 );
00126     return $s1;                                  // Return duration of time
00127   }
00128 }
00129 
00130 
00147 function qpg($str = null) {
00148   global $c;
00149 
00150   switch (strtolower(gettype($str))) {
00151     case 'null':
00152       $rv = 'NULL';
00153       break;
00154     case 'integer':
00155     case 'double' :
00156       return $str;
00157     case 'boolean':
00158       $rv = $str ? 'TRUE' : 'FALSE';
00159       break;
00160     case 'string':
00161     default:
00162       $str = str_replace("'", "''", $str);
00163       //PostgreSQL treats a backslash as an escape character.
00164       $str = str_replace('\\', '\\\\', $str);
00165       $rv = "'$str'";
00166       if ( !isset($c->found_dbversion) || $c->found_dbversion > 8.0 ) $rv = 'E'.$rv;
00167   }
00168   return $rv;
00169 }
00170 
00182 function clean_string( $unclean, $type = 'full' ) {
00183   if ( ! isset($unclean) ) return null;
00184   if ( is_array($unclean) ) {
00185     $result = array();
00186     foreach( $unclean AS $k => $v ) {
00187       $result[$k] = clean_string( $v, $type );
00188     }
00189     return $result;
00190   }
00191   if ( $type != 'basic' ) $cleaned = strtolower($unclean); else $cleaned = &$unclean;
00192   $cleaned = preg_replace( "/['\"!\\\\()\[\]|*\/{}&%@~;:?<>]/", '', $cleaned ); //"// Stupid Bluefish Syntax highlighting...
00193   dbg_error_log( "PgQuery", "clean_string: Cleaned string from <<%s>> to <<%s>>", $unclean, $cleaned );
00194   return $cleaned;
00195 }
00196 
00219 function awl_replace_sql_args() {
00220   $argc = func_num_args(); //number of arguments passed to the function
00221   $qry = func_get_arg(0); //first argument
00222   $args = func_get_args(); //all argument in an array
00223 
00224   if ( is_array($qry) ) {
00225     $qry = $args[0][0];
00226     $args = $args[0];
00227     $argc = count($args);
00228   }
00229 
00230 // building query string by replacing ? with
00231 // escaped parameters
00232   $parts = explode( '?', $qry );
00233   $querystring = $parts[0];
00234   $z = min( count($parts), $argc );
00235 
00236   for( $i = 1; $i < $z; $i++ ) {
00237     $arg = $args[$i];
00238     if ( !isset($arg) ) {
00239       $querystring .= 'NULL';
00240     }
00241     elseif ( is_array($arg) && $arg['plain'] != '' ) {
00242       // We abuse this, but people should access it through the PgQuery::Plain($v) function
00243       $querystring .= $arg['plain'];
00244     }
00245     else {
00246   $querystring .= qpg($arg);  //parameter
00247     }
00248     $querystring .= $parts[$i]; //extras eg. ","
00249   }
00250   if ( isset($parts[$z]) ) $querystring .= $parts[$z]; //puts last part on the end
00251 
00252   return $querystring;
00253 }
00254 
00255 
00277 class PgQuery
00278 {
00287   var $connection;
00288 
00294   var $querystring;
00295 
00301   var $result;
00302 
00308   var $rownum = -1;
00309 
00315   var $location;
00316 
00322   var $object;
00323 
00334   var $rows;
00335 
00341   var $errorstring;
00342 
00348   var $execution_time;
00349 
00357   var $query_time_warning = 0.3;
00367   function __construct() {
00368     global $dbconn;
00369     $this->result = 0;
00370     $this->rows = 0;
00371     $this->execution_time = 0;
00372     $this->rownum = -1;
00373     $this->connection = $dbconn;
00374 
00375     $argc = func_num_args();
00376 
00377     if ( 1 < $argc ) {
00378       $this->querystring = awl_replace_sql_args( func_get_args() );
00379     }
00380     else {
00381       // If we are only called with a single argument, we do
00382       // nothing special with any question marks.
00383       $this->querystring = func_get_arg(0);
00384     }
00385 
00386     return $this;
00387   }
00388 
00389 
00394   function SetConnection( $new_connection ) {
00395     $this->connection = $new_connection;
00396   }
00397 
00398 
00399 
00412   function _log_error( $locn, $tag, $string, $line = 0, $file = "") {
00413     // replace more than one space with one space
00414     $string = preg_replace('/\s+/', ' ', $string);
00415 
00416     if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
00417       dbg_error_log( "LOG-$locn", " Query: %s: Error in '%s' on line %d", $tag, $file, $line );
00418     }
00419 
00420     while( strlen( $string ) > 0 )  {
00421       dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
00422       $string = substr( "$string", 240 );
00423     }
00424   }
00425 
00429   function rows() {
00430     return $this->rows;
00431   }
00432 
00443   function quote($str = null) {
00444     return qpg($str);
00445   }
00446 
00455   function Plain( $field ) {
00456     // Abuse the array type to extend our ability to avoid \\ and ' replacement
00457     $rv = array( 'plain' => $field );
00458     return $rv;
00459   }
00460 
00478   function Exec( $location = '', $line = 0, $file = '' ) {
00479     global $debuggroups, $c, $dbconn;
00480     if ( !isset($this->connection) ) {
00481       if ( !isset($dbconn) ) {
00482         connect_configured_database();
00483       }
00484       $this->connection = $dbconn;
00485     }
00486     $this->location = trim($location);
00487     if ( $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
00488 
00489     if ( isset($debuggroups['querystring']) || isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
00490       $this->_log_error( $this->location, 'DBGQ', $this->querystring, $line, $file );
00491     }
00492 
00493     $t1 = microtime(); // get start time
00494     $this->result = @pg_exec( $this->connection, $this->querystring ); // execute the query
00495     $this->rows = ($this->result ? pg_numrows($this->result) : -1); // number of rows returned
00496     $t2 = microtime(); // get end time
00497     $i_took = duration( $t1, $t2 );   // calculate difference
00498     $c->total_query_time += $i_took;
00499     $this->execution_time = sprintf( "%2.06lf", $i_took);
00500 
00501     if ( !$this->result ) {
00502      // query simply failed
00503       $this->errorstring = @pg_errormessage(); // returns database error message
00504       $this->_log_error( $this->location, 'QF', $this->querystring, $line, $file );
00505       $this->_log_error( $this->location, 'QF', $this->errorstring, $line, $file );
00506     }
00507     elseif ( $this->execution_time > $this->query_time_warning ) {
00508      // if execution time is too long
00509       $this->_log_error( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
00510     }
00511     elseif ( isset($debuggroups[$this->location]) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
00512      // query successful, but we're debugging and want to know how long it took anyway
00513       $this->_log_error( $this->location, 'DBGQ', "Took: $this->execution_time for $this->querystring to find $this->rows rows.", $line, $file );
00514     }
00515 
00516     return $this->result;
00517   }
00518 
00519 
00525   function Fetch($as_array = false) {
00526     global $c, $debuggroups;
00527 
00528     if ( ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 )
00529        || (isset($c) && is_object($c) && ( isset($c->dbg[strtolower($this->location)]) && isset($c->dbg[strtolower($this->location)]) )
00530                                         || isset($c->dbg['ALL']) ) ) {
00531         $this->_log_error( $this->location, "Fetch", "$this->result Rows: $this->rows, Rownum: $this->rownum");
00532     }
00533     if ( ! $this->result ) return false; // no results
00534     if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
00535 
00536     $this->rownum++;
00537     if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) {
00538       $this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" );
00539     }
00540     if ( $as_array )
00541     {
00542       $this->object = pg_fetch_array($this->result, $this->rownum);
00543     }
00544     else
00545     {
00546       $this->object = pg_fetch_object($this->result, $this->rownum);
00547     }
00548 
00549     return $this->object;
00550   }
00551 
00570   function UnFetch() {
00571     global $debuggroups;
00572     $this->rownum--;
00573     if ( $this->rownum < -1 ) $this->rownum = -1;
00574   }
00575 
00581   function FetchBackwards($as_array = false) {
00582     global $debuggroups;
00583 
00584     if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 ) {
00585       $this->_log_error( $this->location, "FetchBackwards", "$this->result Rows: $this->rows, Rownum: $this->rownum");
00586     }
00587     if ( ! $this->result ) return false;
00588     if ( ($this->rownum - 1) == -1 ) return false;
00589     if ( $this->rownum == -1 ) $this->rownum = $this->rows;
00590 
00591     $this->rownum--;
00592 
00593     if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) {
00594       $this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" );
00595     }
00596     if ( $as_array )
00597     {
00598       $this->object = pg_fetch_array($this->result, $this->rownum);
00599     }
00600     else
00601     {
00602       $this->object = pg_fetch_object($this->result, $this->rownum);
00603     }
00604 
00605     return $this->object;
00606   }
00607 
00615   function BuildOptionList( $current = '', $location = 'options', $parameters = false ) {
00616     global $debuggroups;
00617     $result = '';
00618     $translate = false;
00619 
00620     if ( isset($maxwidth) ) unset($maxwidth);
00621     if ( is_array($parameters) ) {
00622       if ( isset($parameters['maxwidth']) ) $maxwidth = max(4,intval($parameters['maxwidth']));
00623       if ( isset($parameters['translate']) ) $translate = true;
00624     }
00625 
00626     // The query may already have been executed
00627     if ( $this->rows > 0 || $this->Exec($location) ) {
00628       $this->rownum = -1;
00629       while( $row = $this->Fetch(true) )
00630       {
00631         if (is_array($current)) {
00632           $selected = ( ( in_array($row[0],$current,true) || in_array($row[1],$current,true)) ? ' selected="selected"' : '' );
00633         }
00634         else {
00635           $selected = ( ( "$row[0]" == "$current" || "$row[1]" == "$current" ) ? ' selected="selected"' : '' );
00636         }
00637         $display_value = $row[1];
00638         if ( isset($translate) ) $display_value = translate( $display_value );
00639         if ( isset($maxwidth) ) $display_value = substr( $display_value, 0, $maxwidth);
00640         $nextrow = "<option value=\"".htmlspecialchars($row[0])."\"$selected>".htmlspecialchars($display_value)."</option>";
00641         $result .= $nextrow;
00642       }
00643     }
00644     return $result;
00645    }
00646 
00647 }
00648