Back to index

awl  0.53
AwlQuery.php
Go to the documentation of this file.
00001 <?php
00011 require_once('AwlDatabase.php');
00012 
00040 function _awl_connect_configured_database() {
00041   global $c, $_awl_dbconn;
00042 
00046   $_awl_dbconn = false;
00047 
00048   if ( isset($c->db_connect) ) {
00049     $connection_strings = $c->db_connect;
00050   }
00051   elseif ( isset($c->pg_connect) ) {
00052     $connection_strings = $c->pg_connect;
00053   }
00054 
00055   foreach( $connection_strings AS $k => $v ) {
00056     $dbuser = null;
00057     $dbpass = null;
00058     if ( is_array($v) ) {
00059       $dsn = $v['dsn'];
00060       if ( isset($v['dbuser']) ) $dbuser = $v['dbuser'];
00061       if ( isset($v['dbpass']) ) $dbpass = $v['dbpass'];
00062     }
00063     elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $v, $matches ) ) {
00064       $dsn = $matches[2];
00065       if ( isset($matches[1]) && $matches[1] != '' ) {
00066         $dsn = $matches[1] . $dsn;
00067       }
00068       else {
00069         $dsn = 'pgsql:' . $dsn;
00070       }
00071       if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
00072       if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
00073     }
00074     if ( $_awl_dbconn = new AwlDatabase( $dsn, $dbuser, $dbpass, (isset($c->use_persistent) && $c->use_persistent ? array(PDO::ATTR_PERSISTENT => true) : null) ) ) break;
00075   }
00076 
00077   if ( ! $_awl_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 database</h3>
00082   </body>
00083   </html>
00084 EOERRMSG;
00085     exit;
00086   }
00087 
00088   if ( isset($c->db_schema) && $c->db_schema != '' ) {
00089     $_awl_dbconn->SetSearchPath( $c->db_schema . ',public' );
00090   }
00091 
00092   $c->_awl_dbversion = $_awl_dbconn->GetVersion();
00093 }
00094 
00095 
00117 class AwlQuery
00118 {
00126   protected $connection;
00127 
00132   protected $querystring;
00133 
00138   protected $bound_querystring;
00139 
00144   protected $bound_parameters;
00145 
00150   protected $sth;
00151 
00156   protected $result;
00157 
00162   protected $rownum = null;
00163 
00168   protected $rows;
00169 
00174   protected $error_info;
00175 
00181   protected $execution_time;
00182 
00193   public $location;
00194 
00202   public $query_time_warning = 0.3;
00212   function __construct() {
00213     global $_awl_dbconn;
00214     $this->rows = null;
00215     $this->execution_time = 0;
00216     $this->error_info = null;
00217     $this->rownum = -1;
00218     if ( isset($_awl_dbconn) ) $this->connection = $_awl_dbconn;
00219     else                       $this->connection = null;
00220 
00221     $argc = func_num_args();
00222     $args = func_get_args();
00223 
00224     $this->querystring = array_shift($args);
00225     if ( 1 < $argc ) {
00226       if ( is_array($args[0]) )
00227         $this->bound_parameters = $args[0];
00228       else
00229         $this->bound_parameters = $args;
00230 //      print_r( $this->bound_parameters );
00231     }
00232 
00233     return $this;
00234   }
00235 
00236 
00241   function SetConnection( $new_connection, $options = null ) {
00242     if ( is_string($new_connection) || is_array($new_connection) ) {
00243       $dbuser = null;
00244       $dbpass = null;
00245       if ( is_array($new_connection) ) {
00246         $dsn = $new_connection['dsn'];
00247         if ( isset($new_connection['dbuser']) ) $dbuser = $new_connection['dbuser'];
00248         if ( isset($new_connection['dbpass']) ) $dbpass = $new_connection['dbpass'];
00249       }
00250       elseif ( preg_match( '/^(\S+:)?(.*)( user=(\S+))?( password=(\S+))?$/', $new_connection, $matches ) ) {
00251         $dsn = $matches[2];
00252         if ( isset($matches[1]) && $matches[1] != '' ) {
00253           $dsn = $matches[1] . $dsn;
00254         }
00255         else {
00256           $dsn = 'pgsql:' . $dsn;
00257         }
00258         if ( isset($matches[4]) && $matches[4] != '' ) $dbuser = $matches[4];
00259         if ( isset($matches[6]) && $matches[6] != '' ) $dbpass = $matches[6];
00260       }
00261       if ( $new_connection = new AwlDatabase( $dsn, $dbuser, $dbpass, $options ) ) break;
00262     }
00263     $this->connection = $new_connection;
00264     return $new_connection;
00265   }
00266 
00267 
00268 
00272   function GetConnection() {
00273     return $this->connection;
00274   }
00275 
00276   
00289   function _log_query( $locn, $tag, $string, $line = 0, $file = "") {
00290     // replace more than one space with one space
00291     $string = preg_replace('/\s+/', ' ', $string);
00292 
00293     if ( ($tag == 'QF' || $tag == 'SQ') && ( $line != 0 && $file != "" ) ) {
00294       dbg_error_log( "LOG-$locn", " Query: %s: %s in '%s' on line %d", ($tag == 'QF' ? 'Error' : 'Possible slow query'), $tag, $file, $line );
00295     }
00296 
00297     while( strlen( $string ) > 0 )  {
00298       dbg_error_log( "LOG-$locn", " Query: %s: %s", $tag, substr( $string, 0, 240) );
00299       $string = substr( "$string", 240 );
00300     }
00301   }
00302 
00303 
00311   public static function quote($str = null) {
00312     global $_awl_dbconn;
00313     if ( !isset($_awl_dbconn) ) {
00314       _awl_connect_configured_database();
00315     }
00316     return $_awl_dbconn->Quote($str);
00317   }
00318 
00319 
00330   function Bind() {
00331     $argc = func_num_args();
00332     $args = func_get_args();
00333 
00334     if ( $argc == 1 ) {
00335       if ( gettype($args[0]) == 'array' ) {
00336         $this->bound_parameters = $args[0];
00337       }
00338       else {
00339         $this->bound_parameters[] = $args[0];
00340       }
00341     }
00342     else {
00343       $this->bound_parameters[$args[0]] = $args[1];
00344     }
00345   }
00346 
00347 
00351   function Prepare() {
00352     global $c;
00353 
00354     if ( isset($this->sth) ) return; // Already prepared
00355     if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) return; //  No-op if we're expanding internally
00356 
00357     if ( !isset($this->connection) ) {
00358       _awl_connect_configured_database();
00359       $this->connection = $GLOBALS['_awl_dbconn'];
00360     }
00361 
00362     $this->sth = $this->connection->prepare( $this->querystring );
00363 
00364     if ( ! $this->sth ) {
00365       $this->error_info = $this->connection->errorInfo();
00366     }
00367     else $this->error_info = null;
00368   }
00369 
00370 
00374   function Execute() {
00375     global $c;
00376 
00377     if ( !isset($this->connection) ) {
00378       _awl_connect_configured_database();
00379       $this->connection = $GLOBALS['_awl_dbconn'];
00380     }
00381     if ( !is_object($this->connection) ) throw new Exception('Database not connected.');
00382     
00383     if ( isset($c->expand_pdo_parameters) && $c->expand_pdo_parameters ) {
00384       $this->bound_querystring = $this->querystring;
00385       if ( isset($this->bound_parameters) ) {
00386         $this->bound_querystring = $this->connection->ReplaceParameters($this->querystring,$this->bound_parameters);
00387 //        printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
00388 //        printf( "\n=============================================================== QQ\n%s\n", $this->bound_querystring);
00389 //        print_r( $this->bound_parameters );
00390       }
00391       $t1 = microtime(true); // get start time
00392       $this->sth = $this->connection->query($this->bound_querystring);
00393     }
00394     else {
00395       $t1 = microtime(true); // get start time
00396       $this->sth = $this->connection->prepare($this->querystring);
00397       if ( $this->sth ) $this->sth->execute($this->bound_parameters);
00398 //      printf( "\n=============================================================== OQ\n%s\n", $this->querystring);
00399 //      print_r( $this->bound_parameters );
00400     }
00401     $this->bound_querystring = null;
00402 
00403     if ( ! $this->sth ) {
00404       $this->error_info = $this->connection->errorInfo();
00405       return false;
00406     }
00407     $this->rows = $this->sth->rowCount();
00408 
00409     $i_took = microtime(true) - $t1;
00410     $c->total_query_time += $i_took;
00411     $this->execution_time = sprintf( "%2.06lf", $i_took);
00412 
00413     $this->error_info = null;
00414     return true;
00415   }
00416 
00417 
00421   function QueryString() {
00422     return $this->querystring;
00423   }
00424 
00425 
00429   function Parameters() {
00430     return $this->bound_parameters;
00431   }
00432 
00433 
00437   function rows() {
00438     return $this->rows;
00439   }
00440 
00441 
00445   function rownum() {
00446     return $this->rownum;
00447   }
00448 
00449 
00455   function TransactionState() {
00456     global $_awl_dbconn;
00457     if ( !isset($this->connection) ) {
00458       if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
00459       $this->connection = $_awl_dbconn;
00460     }
00461     return $this->connection->TransactionState();
00462   }
00463 
00464 
00468   public function Begin() {
00469     global $_awl_dbconn;
00470     if ( !isset($this->connection) ) {
00471       if ( !isset($_awl_dbconn) ) _awl_connect_configured_database();
00472       $this->connection = $_awl_dbconn;
00473     }
00474     return $this->connection->Begin();
00475   }
00476 
00477 
00481   public function Commit() {
00482     if ( !isset($this->connection) ) {
00483       trigger_error("Cannot commit a transaction without an active statement.", E_USER_ERROR);
00484     }
00485     return $this->connection->Commit();
00486   }
00487 
00488 
00492   public function Rollback() {
00493     if ( !isset($this->connection) ) {
00494       trigger_error("Cannot rollback a transaction without an active statement.", E_USER_ERROR);
00495     }
00496     return $this->connection->Rollback();
00497   }
00498 
00499 
00504   public function SetSql( $sql ) {
00505     $this->rows = null;
00506     $this->execution_time = 0;
00507     $this->error_info = null;
00508     $this->rownum = -1;
00509     $this->bound_parameters = null;
00510     $this->bound_querystring = null;
00511     $this->sth = null;
00512 
00513     $this->querystring = $sql;
00514   }
00515 
00516 
00524   public function QDo() {
00525     $argc = func_num_args();
00526     $args = func_get_args();
00527 
00528     $this->SetSql( array_shift($args) );
00529     if ( 1 < $argc ) {
00530       if ( is_array($args[0]) )
00531         $this->bound_parameters = $args[0];
00532       else
00533         $this->bound_parameters = $args;
00534     }
00535 
00536     return $this->Exec();
00537   }
00538 
00539 
00557   function Exec( $location = null, $line = null, $file = null ) {
00558     global $c;
00559     if ( isset($location) ) $this->location = trim($location);
00560     if ( !isset($this->location) || $this->location == "" ) $this->location = substr($_SERVER['PHP_SELF'],1);
00561 
00562     if ( isset($line) )     $this->location_line = intval($line);
00563     else if ( isset($this->location_line) ) $line = $this->location_line;
00564 
00565     if ( isset($file) )     $this->location_file = trim($file);
00566     else if ( isset($this->location_file) ) $file = $this->location_file;
00567 
00568     if ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) {
00569       $this->_log_query( $this->location, 'DBGQ', $this->querystring, $line, $file );
00570       if ( isset($this->bound_parameters) && !isset($this->sth) ) {
00571         foreach( $this->bound_parameters AS $k => $v ) {
00572           $this->_log_query( $this->location, 'DBGQ', sprintf('    "%s" => "%s"', $k, $v), $line, $file );
00573         }
00574       }
00575     }
00576 
00577     if ( isset($this->bound_parameters) ) {
00578       $this->Prepare();
00579     }
00580 
00581     $success = $this->Execute();
00582 
00583     if ( ! $success ) {
00584       // query failed
00585       $this->errorstring = sprintf( 'SQL error "%s" - %s"', $this->error_info[0], (isset($this->error_info[2]) ? $this->error_info[2] : ''));
00586       if ( isset($c->dbg['print_query_errors']) && $c->dbg['print_query_errors'] ) {
00587         printf( "\n=====================\n" );
00588         printf( "%s[%d] QF: %s\n", $file, $line, $this->errorstring);
00589         printf( "%s\n", $this->querystring );
00590         if ( isset($this->bound_parameters) ) {
00591           foreach( $this->bound_parameters AS $k => $v ) {
00592             printf( "    %-18s \t=> '%s'\n", "'$k'", $v );
00593           }          
00594         }
00595         printf( ".....................\n" );
00596       }
00597       $this->_log_query( $this->location, 'QF', $this->errorstring, $line, $file );
00598       $this->_log_query( $this->location, 'QF', $this->querystring, $line, $file );
00599       if ( isset($this->bound_parameters) && ! ( isset($c->dbg['querystring']) || isset($c->dbg['ALL']) ) ) {
00600         foreach( $this->bound_parameters AS $k => $v ) {
00601           dbg_error_log( 'LOG-'.$this->location, ' Query: QF:     "%s" => "%s"', $k, $v);
00602         }
00603       }
00604     }
00605     elseif ( $this->execution_time > $this->query_time_warning ) {
00606      // if execution time is too long
00607       $this->_log_query( $this->location, 'SQ', "Took: $this->execution_time for $this->querystring", $line, $file ); // SQ == Slow Query :-)
00608     }
00609     elseif ( isset($c->dbg['querystring']) || isset($c->dbg[strtolower($this->location)]) || isset($c->dbg['ALL']) ) {
00610      // query successful, but we're debugging and want to know how long it took anyway
00611       $this->_log_query( $this->location, 'DBGQ', "Took: $this->execution_time to find $this->rows rows.", $line, $file );
00612     }
00613 
00614     return $success;
00615   }
00616 
00617 
00623   function Fetch($as_array = false) {
00624 
00625     if ( ! $this->sth || $this->rows == 0 ) return false; // no results
00626     if ( $this->rownum == null ) $this->rownum = -1;
00627     if ( ($this->rownum + 1) >= $this->rows ) return false; // reached the end of results
00628 
00629     $this->rownum++;
00630     $row = $this->sth->fetch( ($as_array ? PDO::FETCH_NUM : PDO::FETCH_OBJ) );
00631 
00632     return $row;
00633   }
00634 
00635 
00636 }
00637