pg_connect. * * We will die if the database is not currently connected and we fail to find * a working connection. * * @package awl * @subpackage PgQuery * @author Andrew McMillan * @copyright Catalyst IT Ltd, Morphoss Ltd * @license http://gnu.org/copyleft/gpl.html GNU GPL v2 or later */ if ( ! function_exists('pg_Connect') ) { echo << PostgreSQL Support Not Present

PostgreSQL Support Not Present

PHP is not configured to support the PostgreSQL database

You need to ensure that the PostgreSQL support module is installed, and then to configure it in your php.ini file by adding a line like this:

extension=pgsql.so

EOERRMSG; exit; } require_once("AWLUtilities.php"); /** * Connect to the database defined in the $c->dbconn[] array */ function connect_configured_database() { global $c, $dbconn; if ( isset($dbconn) ) return; /** * Attempt to connect to the configured connect strings */ $dbconn = false; dbg_error_log('pgquery', 'Attempting to connect to database'); if ( isset($c->pg_connect) && is_array($c->pg_connect) ) { foreach( $c->pg_connect AS $k => $v ) { if ( !$dbconn ) { if ( $dbconn = ((isset($c->use_persistent) && $c->use_persistent) ? pg_pConnect($v) : pg_Connect($v) ) ) break; } } } if ( ! $dbconn ) { echo <<Database Connection Failure

Database Error

Could not connect to PostgreSQL database

EOERRMSG; if ( isset($c->pg_connect) && is_array($c->pg_connect) ) { dbg_error_log("ERROR", "Failed to connect to database" ); } exit; } if ( isset($c->db_schema) && $c->db_schema != '' ) { $result = pg_exec( $dbconn, "SET Search_path TO ".$c->db_schema.",public;" ); $row = pg_fetch_array($result, 0); } $result = pg_exec( $dbconn, "SELECT version()" ); $row = pg_fetch_array($result, 0); $c->found_dbversion = preg_replace( '/^PostgreSQL (\d+\.\d+)\..*$/i', '$1', $row[0]); } /** * @global resource $dbconn * @name $dbconn * The database connection. */ $dbconn = null; if ( !function_exists('duration') ) { /** * A duration (in decimal seconds) between two times which are the result of calls to microtime() * * This simple function is used by the PgQuery class because the * microtime function doesn't return a decimal time, so a simple * subtraction is not sufficient. * * @param microtime $t1 start time * @param microtime $t2 end time * @return double difference */ function duration( $t1, $t2 ) { list ( $ms1, $s1 ) = explode ( " ", $t1 ); // Format times - by spliting seconds and microseconds list ( $ms2, $s2 ) = explode ( " ", $t2 ); $s1 = $s2 - $s1; $s1 = $s1 + ( $ms2 -$ms1 ); return $s1; // Return duration of time } } /** * Quote the given string (depending on its type) so that it can be used * safely in a PostgreSQL query without fear of SQL injection errors. * * Although this function effectively achieves a similar goal to the pg_escape_string() * function, it is needed for older versions of PHP (< 4.2.0) and older versions * of PostgreSQL (< 7.2.0), however. PgQuery does not attempt to use the newer * pg_escape_string() function at this stage. * * This function is outside the PgQuery class because it is sometimes * desirable to quote values for SQL command strings in circumstances * where there is no benefit to using the class. * * @param mixed $str Data to be converted to a string suitable for including as a value in SQL. * @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped */ function qpg($str = null) { global $c; switch (strtolower(gettype($str))) { case 'null': $rv = 'NULL'; break; case 'integer': case 'double' : return $str; case 'boolean': $rv = $str ? 'TRUE' : 'FALSE'; break; case 'string': default: $str = str_replace("'", "''", $str); //PostgreSQL treats a backslash as an escape character. $str = str_replace('\\', '\\\\', $str); $rv = "'$str'"; if ( !isset($c->found_dbversion) || $c->found_dbversion > 8.0 ) $rv = 'E'.$rv; } return $rv; } /** * Clean a string of many suspicious characters * * While this is a fairly aggressive approach, it applies in many circumstances * where various strings should not contain things that might screw up (e.g.) * filesystem semantics. Although not strictly a PgQuery function it's here * for the time being until I invent a new "generally useful functions" include. * * @param string $unclean The dirty filthy string needing washing. * @return string The pristine uncontaminated string we can safely use for Just About Anything(tm). */ function clean_string( $unclean, $type = 'full' ) { if ( ! isset($unclean) ) return null; if ( is_array($unclean) ) { $result = array(); foreach( $unclean AS $k => $v ) { $result[$k] = clean_string( $v, $type ); } return $result; } if ( $type != 'basic' ) $cleaned = strtolower($unclean); else $cleaned = &$unclean; $cleaned = preg_replace( "/['\"!\\\\()\[\]|*\/{}&%@~;:?<>]/", '', $cleaned ); //"// Stupid Bluefish Syntax highlighting... dbg_error_log( "PgQuery", "clean_string: Cleaned string from <<%s>> to <<%s>>", $unclean, $cleaned ); return $cleaned; } /** * Replaces PostgreSQL query with escaped parameters in preparation * for execution. * * The function takes a variable number of arguments, the first is the * SQL string, with replaceable '?' characters (a la DBI). The subsequent * parameters being the values to replace into the SQL string. * * The values passed to the routine are analyzed for type, and quoted if * they appear to need quoting. This can go wrong for (e.g.) NULL or * other special SQL values which are not straightforwardly identifiable * as needing quoting (or not). In such cases the parameter can be forced * to be inserted unquoted by passing it as "array( 'plain' => $param )". * * This function is outside the PgQuery class because it is sometimes * desirable to build SQL command strings in circumstances where there * is no benefit to using the class. * * @param string The query string with replacable '?' characters. * @param mixed The values to replace into the SQL string. * @return The built query string */ function awl_replace_sql_args() { $argc = func_num_args(); //number of arguments passed to the function $qry = func_get_arg(0); //first argument $args = func_get_args(); //all argument in an array if ( is_array($qry) ) { $qry = $args[0][0]; $args = $args[0]; $argc = count($args); } // building query string by replacing ? with // escaped parameters $parts = explode( '?', $qry ); $querystring = $parts[0]; $z = min( count($parts), $argc ); for( $i = 1; $i < $z; $i++ ) { $arg = $args[$i]; if ( !isset($arg) ) { $querystring .= 'NULL'; } elseif ( is_array($arg) && $arg['plain'] != '' ) { // We abuse this, but people should access it through the PgQuery::Plain($v) function $querystring .= $arg['plain']; } else { $querystring .= qpg($arg); //parameter } $querystring .= $parts[$i]; //extras eg. "," } if ( isset($parts[$z]) ) $querystring .= $parts[$z]; //puts last part on the end return $querystring; } /** * The PgQuery Class. * * This class builds and executes PostgreSQL Queries and traverses the * set of results returned from the query. * * Example usage *


* $sql = "SELECT * FROM mytable WHERE mytype = ?";
* $qry = new PgQuery( $sql, $myunsanitisedtype );
* if ( $qry->Exec("typeselect", __line__, __file__ )
*      && $qry->rows() > 0 )
* {
*   while( $row = $qry->Fetch() ) {
*     do_something_with($row);
*   }
* }
*

* * @package awl */ class PgQuery { /**#@+ * @access private */ /** * holds the connection to the database * should be internal * @var resource */ var $connection; /** * stores a query string * should be read-only * @var string */ var $querystring; /** * stores a resource result * should be internal * @var resource */ var $result; /** * number of current row * should be internal, or at least read-only * @var int */ var $rownum = -1; /** * Where we called this query from so we can find it in our code! * Debugging may also be selectively enabled for a $location. * @var string */ var $location; /** * The row most recently fetched by a call to Fetch() or FetchBackwards * which will either be an array or an object (depending on the Fetch call). * @var mixed */ var $object; /**#@-*/ /**#@+ * @access public */ /** * number of rows from pg_numrows - for fetching result * should be read-only * @var int */ var $rows; /** * The PostgreSQL error message, if the query fails. * Should be read-only, although any successful Exec should clear it * @var string */ var $errorstring; /** * Stores the query execution time - used to deal with long queries. * should be read-only * @var string */ var $execution_time; /** * How long the query should take before a warning is issued. * * This is writable, but a method to set it might be a better interface. * The default is 0.3 seconds. * @var double */ var $query_time_warning = 0.3; /**#@-*/ /** * Constructor * @param string The query string with replacable '?' characters. * @param mixed The values to replace into the SQL string. * @return The PgQuery object */ function __construct() { global $dbconn; $this->result = 0; $this->rows = 0; $this->execution_time = 0; $this->rownum = -1; $this->connection = $dbconn; $argc = func_num_args(); if ( 1 < $argc ) { $this->querystring = awl_replace_sql_args( func_get_args() ); } else { // If we are only called with a single argument, we do // nothing special with any question marks. $this->querystring = func_get_arg(0); } return $this; } /** * Use a different database connection for this query * @param resource $new_connection The database connection to use. */ function SetConnection( $new_connection ) { $this->connection = $new_connection; } /** * Log error, optionally with file and line location of the caller. * * This function should not really be used outside of PgQuery. For a more * useful generic logging interface consider calling dbg_error_log(...); * * @param string $locn A string identifying the calling location. * @param string $tag A tag string, e.g. identifying the type of event. * @param string $string The information to be logged. * @param int $line The line number where the logged event occurred. * @param string $file The file name where the logged event occurred. */ function _log_error( $locn, $tag, $string, $line = 0, $file = "") { // replace more than one space with one space $string = preg_replace('/\s+/', ' ', $string); if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) { dbg_error_log( "LOG-$locn", " Query: %s: Error in '%s' on line %d", $tag, $file, $line ); } while( strlen( $string ) > 0 ) { dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) ); $string = substr( "$string", 240 ); } } /** * Provide a rows() method for forward compatibility with AwlQuery. */ function rows() { return $this->rows; } /** * Quote the given string so it can be safely used within string delimiters * in a query. * * @see qpg() * which is where this is really done. * * @param mixed $str Data to be converted to a string suitable for including as a value in SQL. * @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped */ function quote($str = null) { return qpg($str); } /** * Convert a string which has already been quoted and escaped for PostgreSQL * into a magic array so that it will be inserted unmodified into the SQL * string. Use with care! * * @param string $field The value which has alread been quoted and escaped. * @return array An array with the value associated with a key of 'plain' */ function Plain( $field ) { // Abuse the array type to extend our ability to avoid \\ and ' replacement $rv = array( 'plain' => $field ); return $rv; } /** * Execute the query, logging any debugging. * * Example * So that you can nicely enable/disable the queries for a particular class, you * could use some of PHPs magic constants in your call. *


  * $qry->Exec(__CLASS__, __LINE__, __FILE__);
  *

* * * @param string $location The name of the location for enabling debugging or just * to help our children find the source of a problem. * @param int $line The line number where Exec was called * @param string $file The file where Exec was called * @return resource The actual result of the query (FWIW) */ function Exec( $location = '', $line = 0, $file = '' ) { global $debuggroups, $c, $dbconn; if ( !isset($this->connection) ) { if ( !isset($dbconn) ) { connect_configured_database(); } $this->connection = $dbconn; } $this->location = trim($location); if ( $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1); if ( isset($debuggroups['querystring']) || isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) { $this->_log_error( $this->location, 'DBGQ', $this->querystring, $line, $file ); } $t1 = microtime(); // get start time $this->result = @pg_exec( $this->connection, $this->querystring ); // execute the query $this->rows = ($this->result ? pg_numrows($this->result) : -1); // number of rows returned $t2 = microtime(); // get end time $i_took = duration( $t1, $t2 ); // calculate difference $c->total_query_time += $i_took; $this->execution_time = sprintf( "%2.06lf", $i_took); if ( !$this->result ) { // query simply failed $this->errorstring = @pg_errormessage(); // returns database error message $this->_log_error( $this->location, 'QF', $this->querystring, $line, $file ); $this->_log_error( $this->location, 'QF', $this->errorstring, $line, $file ); } elseif ( $this->execution_time > $this->query_time_warning ) { // if execution time is too long $this->_log_error( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-) } elseif ( isset($debuggroups[$this->location]) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) { // query successful, but we're debugging and want to know how long it took anyway $this->_log_error( $this->location, 'DBGQ', "Took: $this->execution_time for $this->querystring to find $this->rows rows.", $line, $file ); } return $this->result; } /** * Fetch the next row from the query results * @param boolean $as_array True if thing to be returned is array * @return mixed query row */ function Fetch($as_array = false) { global $c, $debuggroups; if ( ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 ) || (isset($c) && is_object($c) && ( isset($c->dbg[strtolower($this->location)]) && isset($c->dbg[strtolower($this->location)]) ) || isset($c->dbg['ALL']) ) ) { $this->_log_error( $this->location, "Fetch", "$this->result Rows: $this->rows, Rownum: $this->rownum"); } if ( ! $this->result ) return false; // no results if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results $this->rownum++; if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) { $this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" ); } if ( $as_array ) { $this->object = pg_fetch_array($this->result, $this->rownum); } else { $this->object = pg_fetch_object($this->result, $this->rownum); } return $this->object; } /** * Set row counter back one * * In the case that you may like to fetch the same row twice, for example * if your SQL returns some columns that are the same for each row, and you * want to display them cleanly before displaying the other data repeatedly * for each row. * * Example *


  * $master_row = $qry->Fetch();
  * $qry->UnFetch();
  * do_something_first($master_row);
  * while( $row = $qry->Fetch() ) {
  *   do_something_repeatedly($row);
  * }
  *

*/ function UnFetch() { global $debuggroups; $this->rownum--; if ( $this->rownum < -1 ) $this->rownum = -1; } /** * Fetch backwards from the result resource * @param boolean $as_array True if thing to be returned is array (default: False * @return mixed query row */ function FetchBackwards($as_array = false) { global $debuggroups; if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 2 ) { $this->_log_error( $this->location, "FetchBackwards", "$this->result Rows: $this->rows, Rownum: $this->rownum"); } if ( ! $this->result ) return false; if ( ($this->rownum - 1) == -1 ) return false; if ( $this->rownum == -1 ) $this->rownum = $this->rows; $this->rownum--; if ( isset($debuggroups["$this->location"]) && $debuggroups["$this->location"] > 1 ) { $this->_log_error( $this->location, "Fetch", "Fetching row $this->rownum" ); } if ( $as_array ) { $this->object = pg_fetch_array($this->result, $this->rownum); } else { $this->object = pg_fetch_object($this->result, $this->rownum); } return $this->object; } /** * Build an option list from the query. * @param string $current Default selection of drop down box (optional) * @param string $location for debugging purposes * @param array $parameters an array further parameters, including 'maxwidth' => 20 to set a maximum width * @return string Select box HTML */ function BuildOptionList( $current = '', $location = 'options', $parameters = false ) { global $debuggroups; $result = ''; $translate = false; if ( isset($maxwidth) ) unset($maxwidth); if ( is_array($parameters) ) { if ( isset($parameters['maxwidth']) ) $maxwidth = max(4,intval($parameters['maxwidth'])); if ( isset($parameters['translate']) ) $translate = true; } // The query may already have been executed if ( $this->rows > 0 || $this->Exec($location) ) { $this->rownum = -1; while( $row = $this->Fetch(true) ) { if (is_array($current)) { $selected = ( ( in_array($row[0],$current,true) || in_array($row[1],$current,true)) ? ' selected="selected"' : '' ); } else { $selected = ( ( "$row[0]" == "$current" || "$row[1]" == "$current" ) ? ' selected="selected"' : '' ); } $display_value = $row[1]; if ( isset($translate) ) $display_value = translate( $display_value ); if ( isset($maxwidth) ) $display_value = substr( $display_value, 0, $maxwidth); $nextrow = ""; $result .= $nextrow; } } return $result; } }