File Source for _db.class.php

<?php
/**
* This file implements the DB class.
*
* Based on ezSQL - Class to make it very easy to deal with MySQL database connections.
* b2evo Additions:
* - nested transactions
* - symbolic table names
* - query log
* - get_list
* - dynamic extension loading
* - Debug features (EXPLAIN...)
* and more...
*
* This file is part of the Quam Plures project - {@link http://quamplures.net/}.
* See also {@link https://launchpad.net/quam-plures}.
*
* @copyright (c) 2009 - 2011 by the Quam Plures developers - {@link http://quamplures.net/}
* @copyright (c)2003-2009 by Francois PLANQUE - {@link http://fplanque.net/}.
* Parts of this file are copyright (c)2004 by Justin Vincent - {@link http://php.justinvincent.com}
* Parts of this file are copyright (c)2004-2005 by Daniel HAHLER - {@link http://thequod.de/contact}.
*
* {@internal License choice
* - If you have received this file as part of a package, please find the license.txt file in
* the same folder or the closest folder above for complete license terms.
* - If you have received this file individually (e-g: from http://evocms.cvs.sourceforge.net/)
* then you must choose one of the following licenses before using the file:
* - GNU General Public License 2 (GPL) - http://www.opensource.org/licenses/gpl-license.php
* - Mozilla Public License 1.1 (MPL) - http://www.opensource.org/licenses/mozilla1.1.php
* }}}
*
* {@internal Origin:
* This file is based on the following package (excerpt from ezSQL's readme.txt):
* =======================================================================
* Author: Justin Vincent (justin@visunet.ie)
* Web: http://php.justinvincent.com
* Name: ezSQL
* Desc: Class to make it very easy to deal with database connections.
* License: FREE / Donation (LGPL - You may do what you like with ezSQL - no exceptions.)
* =======================================================================
* A $10 donation has been made to Justin VINCENT on behalf of the b2evolution team.
* The package has been relicensed as GPL based on
* "You may do what you like with ezSQL - no exceptions."
* 2004-10-14 (email): Justin VINCENT grants Francois PLANQUE the right to relicense
* this modified class under other licenses. "Just include a link to where you got it from."
* }}}
*
* {@internal Open Source relicensing agreement:
* Daniel HAHLER grants Francois PLANQUE the right to license
* Daniel HAHLER's contributions to this file and the b2evolution project
* under any OSI approved OSS license (http://www.opensource.org/licenses/).
* }}}
*
* {@internal Below is a list of authors who have contributed to design/coding of this file: }}
* @author blueyed: Daniel HAHLER
* @author fplanque: Francois PLANQUE
* @author Justin VINCENT
*
* @todo transaction support
*
* @package pond
*/
if( !defined('QP_MAIN_INIT') ) die( 'Please, do not access this page directly.' );
/**
* ezSQL Constants
*/
define( 'EZSQL_VERSION', '1.25' );
define( 'OBJECT', 'OBJECT', true );
define( 'ARRAY_A', 'ARRAY_A', true);
define( 'ARRAY_N', 'ARRAY_N', true);
/**
* The Main Class
*
* @package pond
*/
class DB
{
/**
* Show/Print errors?
* @var boolean
*/
var $show_errors = true;
/**
* Halt on errors?
* @var boolean
*/
var $halt_on_error = true;
/**
* Has an error occured?
* @var boolean
*/
var $error = false;
/**
* Number of done queries.
* @var integer
*/
var $num_queries = 0;
/**
* last query SQL string
* @var string
*/
var $last_query = '';
/**
* last DB error string
* @var string
*/
var $last_error = '';
/**
* Last insert ID
* @var integer
*/
var $insert_id = 0;
/**
* Last query's resource
* @access protected
* @var resource
*/
var $result;
/**
* Last result's rows
* @var array
*/
var $last_result;
/**
* Number of rows in result set (after a select)
*/
var $num_rows = 0;
/**
* Number of rows affected by insert, delete, update or replace
*/
var $rows_affected = 0;
/**
* Aliases that will be replaced in queries:
*/
var $dbaliases = array();
/**
* Strings that will replace the aliases in queries:
*/
var $dbreplaces = array();
/**
* CREATE TABLE options.
*
* This gets appended to every "CREATE TABLE" query.
*
* Edit those if you have control over you MySQL server and want a more professional
* database than what is commonly offered by popular hosting providers.
*
* @todo dh> If the query itself uses already e.g. "CHARACTER SET latin1" it should not get overridden..
* @var string
*/
var $table_options = '';
/**
* Use transactions in DB?
*
* You need to use InnoDB in order to enable this. See the {@link $app_db_config "table_options" key}.
*/
var $use_transactions = false;
/**
* How many transactions are currently nested?
*/
var $transaction_nesting_level = 0;
/**
* Rememeber if we have to rollback at the end of a nested transaction construct
*/
var $rollback_nested_transaction = false;
/**
* MySQL Database handle
* @var object
*/
var $dbhandle;
/**
* Database username
* @var string
*/
var $dbuser;
/**
* Database username's password
* @var string
*/
var $dbpassword;
/**
* Database name
* @var string
* @see select()
*/
var $dbname;
/**
* Database hostname
* @var string
*/
var $dbhost = 'localhost';
/**
* Current connection charset
* @var string
* @see DB::set_connection_charset()
*/
var $connection_charset;
// DEBUG:
/**
* Do we want to log queries?
* If null, it gets set according to {@link $debug}.
* A subclass may set it by default (e.g. DbUnitTestCase_DB).
* @var boolean
*/
var $log_queries;
/**
* Log of queries:
* @var array
*/
var $queries = array();
/**
* Do we want to explain joins?
* This requires {@link DB::$log_queries} to be true.
*
* @todo fp> we'd probably want to group all the advanced debug vars under a single setting now. We might even auto enable it when $debug=2. (And we might actually want to include a $debug="cookie" mode for easy switching with bookmarks or a bookmarklet)
*
* @var boolean
*/
var $debug_explain_joins = false;
/**
* Do we want to output a function backtrace for every query?
* Number of stack entries to show (from last to first) (Default: 0); true means 'all'.
*
* This requires {@link DB::$log_queries} to be true.
*
* @var integer
*/
var $debug_dump_function_trace_for_queries = 0;
/**
* Number of rows we want to dump in debug output (0 disables it)
* This requires {@link DB::$log_queries} to be true.
* @var integer
*/
var $debug_dump_rows = 0;
/**
* Time in seconds that is considered a fast query (green).
* @var float
* @see dump_queries()
*/
var $query_duration_fast = 0.05;
/**
* Time in seconds that is considered a slow query (red).
* @var float
* @see dump_queries()
*/
var $query_duration_slow = 0.3;
/**
* DB Constructor
*
* Connects to the server and selects a database.
*
* @param array An array of parameters.
* Manadatory:
* - 'user': username to connect with
* - 'password': password to connect with
* OR
* - 'handle': a MySQL Database handle (from a previous {@link mysql_connect()})
* Optional:
* - 'name': the name of the default database, see {@link DB::select()}
* - 'host': host of the database; Default: 'localhost'
* - 'show_errors': Display SQL errors? (true/false); Default: don't change member default ({@link $show_errors})
* - 'halt_on_error': Halt on error? (true/false); Default: don't change member default ({@link $halt_on_error})
* - 'table_options': sets {@link $table_options}
* - 'use_transactions': sets {@link $use_transactions}
* - 'aliases': Aliases for tables (array( alias => table name )); Default: no aliases.
* - 'new_link': create a new link to the DB, even if there was a mysql_connect() with
* the same params before. (requires PHP 4.2)
* - 'client_flags': optional settings like compression or SSL encryption. See {@link http://www.php.net/manual/en/ref.mysql.php#mysql.client-flags}.
* (requires PHP 4.3)
*/
function DB( $params )
{
global $debug;
// Mandatory parameters:
if( isset( $params['handle'] ) )
{ // DB-Link provided:
$this->dbhandle = $params['handle'];
}
else
{
$this->dbuser = $params['user'];
$this->dbpassword = $params['password'];
}
// Optional parameters (Allow overriding through $params):
if( isset($params['name']) ) $this->dbname = $params['name'];
if( isset($params['host']) ) $this->dbhost = $params['host'];
if( isset($params['show_errors']) ) $this->show_errors = $params['show_errors'];
if( isset($params['halt_on_error']) ) $this->halt_on_error = $params['halt_on_error'];
if( isset($params['table_options']) ) $this->table_options = $params['table_options'];
if( isset($params['use_transactions']) ) $this->use_transactions = $params['use_transactions'];
if( isset($params['debug_dump_rows']) ) $this->debug_dump_rows = $params['debug_dump_rows']; // Nb of rows to dump
if( isset($params['debug_explain_joins']) ) $this->debug_explain_joins = $params['debug_explain_joins'];
if( isset($params['debug_dump_function_trace_for_queries']) ) $this->debug_dump_function_trace_for_queries = $params['debug_dump_function_trace_for_queries'];
if( isset($debug) && ! isset($this->log_queries) )
{ // $log_queries follows $debug and respects subclasses, which may define it:
$this->log_queries = $debug;
}
if( ! extension_loaded('mysql') )
{ // The mysql extension is not loaded, try to dynamically load it:
$mysql_ext_file = is_windows() ? 'php_mysql.dll' : 'mysql.so';
$php_errormsg = null;
$old_track_errors = ini_set('track_errors', 1);
$old_html_errors = ini_set('html_errors', 0);
@dl( $mysql_ext_file );
$error_msg = $php_errormsg;
if( $old_track_errors !== false ) ini_set('track_errors', $old_track_errors);
if( $old_html_errors !== false ) ini_set('html_errors', $old_html_errors);
if( ! extension_loaded('mysql') )
{ // Still not loaded:
$this->print_error( 'The PHP MySQL module could not be loaded.', '
<p><strong>Error:</strong> '.$error_msg.'</p>
<p>You probably have to edit your php configuration (php.ini) and enable this module ('.$mysql_ext_file.').</p>
<p>Do not forget to restart your webserver (if necessary) after editing the PHP conf.</p>', false );
return;
}
}
$new_link = isset( $params['new_link'] ) ? $params['new_link'] : false;
$client_flags = isset( $params['client_flags'] ) ? $params['client_flags'] : 0;
if( ! $this->dbhandle )
{ // Connect to the Database:
// mysql_error() is tied to an established connection
// if the connection fails we need a different method to get the error message
$php_errormsg = null;
$old_track_errors = ini_set('track_errors', 1);
$old_html_errors = ini_set('html_errors', 0);
$this->dbhandle = @mysql_connect( $this->dbhost, $this->dbuser, $this->dbpassword, $new_link, $client_flags );
$mysql_error = $php_errormsg;
if( $old_track_errors !== false ) ini_set('track_errors', $old_track_errors);
if( $old_html_errors !== false ) ini_set('html_errors', $old_html_errors);
}
if( ! $this->dbhandle )
{
$this->print_error( 'Error establishing a database connection!', '
<p>('.$mysql_error.')</p>
<ol>
<li>Are you sure you have typed the correct user/password?</li>
<li>Are you sure that you have typed the correct hostname?</li>
<li>Are you sure that the database server is running?</li>
</ol>', false );
}
elseif( isset($this->dbname) )
{
$this->select($this->dbname);
}
if( !empty($params['connection_charset']) )
{ // Specify which charset we are using on the client:
$this->set_connection_charset( $params['connection_charset'] );
}
/*
echo '<br />Server: '.$this->get_var( 'SELECT @@character_set_server' );
echo '<br />Database: '.$this->get_var( 'SELECT @@character_set_database' );
echo '<br />Connection: '.$this->get_var( 'SELECT @@character_set_connection' );
echo '<br />Client: '.$this->get_var( 'SELECT @@character_set_client' );
echo '<br />Results: '.$this->get_var( 'SELECT @@character_set_results' );
*/
if( isset($params['aliases']) )
{ // Prepare aliases for replacements:
foreach( $params['aliases'] as $dbalias => $dbreplace )
{
$this->dbaliases[] = '#\b'.$dbalias.'\b#'; // \b = word boundary
$this->dbreplaces[] = $dbreplace;
}
}
if( $debug )
{ // Force MySQL strict mode
// TRADITIONAL mode is only available to mysql > 5.0.2
$mysql_version = $this->get_version( 'we do this in DEBUG mode only' );
if( version_compare( $mysql_version, '5.0.2' ) > 0 )
{
$this->query( 'SET sql_mode = "TRADITIONAL"', 'we do this in DEBUG mode only' );
}
}
}
/**
* Select a DB (if another one needs to be selected)
*/
function select($db)
{
if( !@mysql_select_db($db, $this->dbhandle) )
{
$this->print_error( 'Error selecting database ['.$db.']!', '
<ol>
<li>Are you sure the database exists?</li>
<li>Are you sure the DB user is allowed to use that database?</li>
<li>Are you sure there is a valid database connection?</li>
</ol>', false );
}
}
/**
* Format a string correctly for safe insert under all PHP conditions
*/
function escape($str)
{
return mysql_real_escape_string($str, $this->dbhandle);
}
/**
* Quote a value, either in single quotes (and escaped) or if it's NULL as 'NULL'.
*
* @param string|array|null
* @return string Quoted (and escaped) value or 'NULL'.
*/
function quote($str)
{
if( is_null( $str ) )
{
return 'NULL';
}
elseif( is_array( $str ) )
{
$r = '';
foreach( $str as $elt )
{
$r .= $this->quote($elt).',';
}
return substr( $r, 0, -1 );
}
else
{
return "'".mysql_real_escape_string($str, $this->dbhandle)."'";
}
}
/**
* @return string Return the given value or 'NULL', if it's === NULL.
*/
function null($val)
{
if( $val === NULL )
return 'NULL';
else
return $val;
}
/**
* Returns the correct WEEK() function to get the week number for the given date.
*
* @link http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
*
* @todo disable when MySQL < 4
* @param string will be used as is
* @param integer 0 for sunday, 1 for monday
*/
function week( $date, $startofweek )
{
if( $startofweek == 1 )
{ // Week starts on Monday, week 1 must have a monday in this year:
return ' WEEK( '.$date.', 5 ) ';
}
// Week starts on Sunday, week 1 must have a sunday in this year:
return ' WEEK( '.$date.', 0 ) ';
}
/**
* Print SQL/DB error.
*
* TODO: fp> bloated: it probably doesn't make sense to display errors if we don't stop. Any use case?
* dh> Sure. Local testing (and test cases).
*
* @param string Short error (no HTML)
* @param string Extended description/help for the error (for HTML)
* @param string|falseQuery title; false if {@link DB::$last_query} should not get displayed
*/
function print_error( $title = '', $html_str = '', $query_title = '' )
{
// All errors go to the global error array $EZSQL_ERROR..
global $EZSQL_ERROR, $is_cli;
$this->error = true;
// If no special error string then use mysql default..
if( ! strlen($title) )
{
if( is_resource($this->dbhandle) )
{ // use mysql_error:
$this->last_error = mysql_error($this->dbhandle).'(Errno='.mysql_errno($this->dbhandle).')';
}
else
{
$this->last_error = 'Unknown (and no $dbhandle available)';
}
}
else
{
$this->last_error = $title;
}
// Log this error to the global array..
$EZSQL_ERROR[] = array(
'query' => $this->last_query,
'error_str' => $this->last_error
);
if( ! ( $this->halt_on_error || $this->show_errors ) )
{ // no reason to generate a nice message:
return;
}
if( $this->halt_on_error && ! $this->show_errors )
{ // do not show errors, just die:
die();
}
if( $is_cli )
{ // Clean error message for command line interface:
$err_msg = "MySQL error!\n{$this->last_error}\n";
if( ! empty($this->last_query) && $query_title !== false )
{
$err_msg .= "Your query: $query_title\n";
$err_msg .= $this->format_query( $this->last_query, false );
}
}
else
{
$err_msg = '<p class="error">MySQL error!</p>'."\n";
$err_msg .= "<div><p><strong>{$this->last_error}</strong></p>\n";
$err_msg .= $html_str;
if( !empty($this->last_query) && $query_title !== false )
{
$err_msg .= '<p class="error">Your query: '.$query_title.'</p>';
$err_msg .= '<pre>';
$err_msg .= $this->format_query( $this->last_query, ! $is_cli );
$err_msg .= '</pre>';
}
$err_msg .= "</div>\n";
}
if( $this->halt_on_error )
{
if( function_exists('debug_die') )
{
debug_die( $err_msg );
}
else
{
die( $err_msg );
}
}
elseif( $this->show_errors )
{ // If there is an error then take note of it
echo '<div class="error">';
echo $err_msg;
echo '</div>';
}
}
/**
* Kill cached query results
*/
function flush()
{
// Get rid of these
$this->last_result = NULL;
$this->last_query = NULL;
}
/**
* Get MYSQL version
*/
function get_version( $query_title = NULL )
{
if( isset( $this->version ) )
{
return $this->version;
}
$this->save_error_state();
// Blatantly ignore any error generated by potentially unknown function...
$this->show_errors = false;
$this->halt_on_error = false;
if( ($this->version_long = $this->get_var( 'SELECT VERSION()', 0, 0, $query_title ) ) === NULL )
{ // Very old version ( < 4.0 )
$this->version = '';
$this->version_long = '';
}
else
{
$this->version = preg_replace( '¤-.*¤', '', $this->version_long );
}
$this->restore_error_state();
return $this->version;
}
/**
* Save the vars responsible for error handling.
* This can be chained.
* @see DB::restore_error_state()
*/
function save_error_state()
{
$this->saved_error_states[] = array(
'show_errors' => $this->show_errors,
'halt_on_error' => $this->halt_on_error,
'last_error' => $this->last_error,
'error' => $this->error,
);
}
/**
* Call this after {@link save_halt_on_error()} to
* restore the previous error state.
* This can be chained.
* @see DB::save_error_state()
*/
function restore_error_state()
{
if( empty($this->saved_error_states)
|| ! is_array($this->saved_error_states) )
{
return false;
}
$state = array_pop($this->saved_error_states);
$this->show_errors = $state['show_errors'];
$this->halt_on_error = $state['halt_on_error'];
$this->last_error = $state['last_error'];
$this->error = $state['error'];
}
/**
* Basic Query
*
* @param string SQL query
* @param string title for debugging
* @return mixed # of rows affected or false if error
*/
function query( $query, $title = '' )
{
global $Timer;
// initialise return
$return_val = 0;
// Flush cached values..
$this->flush();
// Replace aliases:
if( ! empty($this->dbaliases) )
{
// TODO: this should only replace the table name part(s), not the whole query!
// blueyed> I've changed it to replace in table name parts for UPDATE, INSERT and REPLACE, because
// it corrupted serialized data..
// IMHO, a cleaner solution would be to use {T_xxx} in the queries and replace it here. In object properties (e.g. DataObject::$dbtablename), only "T_xxx" would get used and surrounded by "{..}" in the queries it creates.
if( preg_match( '~^\s*(UPDATE\s+)(.*?)(\sSET\s.*)$~is', $query, $match ) )
{ // replace only between UPDATE and SET:
$query = $match[1].preg_replace( $this->dbaliases, $this->dbreplaces, $match[2] ).$match[3];
}
elseif( preg_match( '~^\s*(INSERT|REPLACE\s+)(.*?)(\s(VALUES|SET)\s.*)$~is', $query, $match ) )
{ // replace only between INSERT|REPLACE and VALUES|SET:
$query = $match[1].preg_replace( $this->dbaliases, $this->dbreplaces, $match[2] ).$match[3];
}
else
{ // replace in whole query:
$query = preg_replace( $this->dbaliases, $this->dbreplaces, $query );
if( ! empty($this->table_options) && preg_match( '#^ \s* create \s* table \s #ix', $query) )
{ // Query is a table creation, we add table options:
$query = preg_replace( '~;\s*$~', '', $query ); // remove any ";" at the end
$query .= ' '.$this->table_options;
}
}
}
elseif( ! empty($this->table_options) )
{ // No aliases, but table_options:
if( preg_match( '#^ \s* create \s* table \s #ix', $query) )
{ // Query is a table creation, we add table options:
$query = preg_replace( '~;\s*$~', '', $query ); // remove any ";" at the end
$query .= $this->table_options;
}
}
// Keep track of the last query for debug..
$this->last_query = $query;
// Perform the query via std mysql_query function..
$this->num_queries++;
if( $this->log_queries )
{ // We want to log queries:
$this->queries[ $this->num_queries - 1 ] = array(
'title' => $title,
'sql' => $query,
'rows' => -1,
'time' => 'unknown',
'results' => 'unknown' );
}
if( is_object($Timer) )
{
// Resume global query timer
$Timer->resume( 'sql_queries' );
// Start a timer for this particular query:
$Timer->start( 'sql_query', false );
// Run query:
$this->result = @mysql_query( $query, $this->dbhandle );
if( $this->log_queries )
{ // We want to log queries:
// Get duration for last query:
$this->queries[ $this->num_queries - 1 ]['time'] = $Timer->get_duration( 'sql_query', 10 );
}
// Pause global query timer:
$Timer->pause( 'sql_queries' );
}
else
{
// Run query:
$this->result = @mysql_query( $query, $this->dbhandle );
}
// If there is an error then take note of it..
if( is_resource($this->dbhandle) && mysql_error($this->dbhandle) )
{
@mysql_free_result($this->result);
$this->print_error( '', '', $title );
return false;
}
if( preg_match( '#^\s*(INSERT|DELETE|UPDATE|REPLACE)\s#i', $query, $match ) )
{ // Query was an insert, delete, update, replace:
$this->rows_affected = mysql_affected_rows($this->dbhandle);
if( $this->log_queries )
{ // We want to log queries:
$this->queries[ $this->num_queries - 1 ]['rows'] = $this->rows_affected;
}
// Take note of the insert_id, for INSERT and REPLACE:
$match[1] = strtoupper($match[1]);
if( $match[1] == 'INSERT' || $match[1] == 'REPLACE' )
{
$this->insert_id = mysql_insert_id($this->dbhandle);
}
// Return number of rows affected
$return_val = $this->rows_affected;
}
else
{ // Query was a select, alter, etc...:
$this->num_rows = 0;
if( is_resource($this->result) )
{ // It's not a resource for CREATE or DROP for example and can even trigger a fatal error (see http://forums.b2evolution.net//viewtopic.php?t=9529)
// Store Query Results
while( $row = mysql_fetch_object($this->result) )
{
// Store relults as an objects within main array
$this->last_result[$this->num_rows] = $row;
$this->num_rows++;
}
}
if( $this->log_queries )
{ // We want to log queries:
$this->queries[ $this->num_queries - 1 ]['rows'] = $this->num_rows;
}
// Return number of rows selected
$return_val = $this->num_rows;
}
if( $this->log_queries )
{ // We want to log queries:
if( $this->debug_dump_function_trace_for_queries )
{
$this->queries[ $this->num_queries - 1 ]['function_trace'] = debug_get_backtrace( $this->debug_dump_function_trace_for_queries, array( array( 'class' => 'DB' ) ), 1 ); // including first stack entry from class DB
}
if( $this->debug_dump_rows )
{
$this->queries[ $this->num_queries - 1 ]['results'] = $this->debug_get_rows_table( $this->debug_dump_rows );
}
}
// Free original query's result:
@mysql_free_result($this->result);
// EXPLAIN JOINS ??
if( $this->log_queries && $this->debug_explain_joins && preg_match( '#^ [\s(]* SELECT \s #ix', $query) )
{ // Query was a select, let's try to explain joins...
// save values:
$saved_last_result = $this->last_result;
$saved_num_rows = $this->num_rows;
$this->last_result = NULL;
$this->num_rows = 0;
$this->result = @mysql_query( 'EXPLAIN '.$query, $this->dbhandle );
// Store Query Results
$this->num_rows = 0;
while( $row = @mysql_fetch_object($this->result) )
{
// Store results as an objects within main array
$this->last_result[$this->num_rows] = $row;
$this->num_rows++;
}
$this->queries[ $this->num_queries - 1 ]['explain'] = $this->debug_get_rows_table( 100, true );
// Free "EXPLAIN" result resource:
@mysql_free_result($this->result);
// Restore:
$this->last_result = $saved_last_result;
$this->num_rows = $saved_num_rows;
}
return $return_val;
}
/**
* Get one variable from the DB - see docs for more detail
*
* Note: To be sure that you received NULL from the DB and not "no rows" check
* for {@link $num_rows}.
*
* @return mixed NULL if not found, the value otherwise (which may also be NULL).
*/
function get_var( $query = NULL, $x = 0, $y = 0, $title = '' )
{
// If there is a query then perform it if not then use cached results..
if( $query )
{
$this->query($query, $title);
}
// Extract var out of cached results based x,y vals
if( $this->last_result[$y] )
{
$values = array_values(get_object_vars($this->last_result[$y]));
}
if( isset($values[$x]) )
{
return $values[$x];
}
return NULL;
}
/**
* Get one row from the DB - see docs for more detail
*
* @return mixed
*/
function get_row( $query = NULL, $output = OBJECT, $y = 0, $title = '' )
{
// If there is a query then perform it if not then use cached results..
if( $query )
{
$this->query($query, $title);
}
// If the output is an object then return object using the row offset..
if( $output == OBJECT )
{
return $this->last_result[$y]
? $this->last_result[$y]
: NULL;
}
// If the output is an associative array then return row as such..
elseif( $output == ARRAY_A )
{
return $this->last_result[$y]
? get_object_vars( $this->last_result[$y] )
: array();
}
// If the output is an numerical array then return row as such..
elseif( $output == ARRAY_N )
{
return $this->last_result[$y]
? array_values( get_object_vars($this->last_result[$y]) )
: array();
}
// If invalid output type was specified..
else
{
$this->print_error('DB::get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N', '', false);
}
}
/**
* Function to get 1 column from the cached result set based on X index
* see docs for usage and info
*
* @return array
*/
function get_col( $query = NULL, $x = 0, $title = '' )
{
// If there is a query then perform it if not then use cached results..
if( $query )
{
$this->query( $query, $title );
}
// Extract the column values
$new_array = array();
for( $i = 0, $count = count($this->last_result); $i < $count; $i++ )
{
$new_array[$i] = $this->get_var( NULL, $x, $i );
}
return $new_array;
}
/**
* Function to get the second column from the cached result indexed by the first column
*
* @return array [col_0] => col_1
*/
function get_assoc( $query = NULL, $title = '' )
{
// If there is a query then perform it if not then use cached results..
if( $query )
{
$this->query( $query, $title );
}
// Extract the column values
$new_array = array();
for( $i = 0, $count = count($this->last_result); $i < $count; $i++ )
{
$key = $this->get_var( NULL, 0, $i );
$new_array[$key] = $this->get_var( NULL, 1, $i );
}
return $new_array;
}
/**
* Return the the query as a result set - see docs for more details
*
* @return mixed
*/
function get_results( $query = NULL, $output = OBJECT, $title = '' )
{
// If there is a query then perform it if not then use cached results..
if( $query )
{
$this->query($query, $title);
}
// Send back array of objects. Each row is an object
if( $output == OBJECT )
{
return $this->last_result ? $this->last_result : array();
}
elseif( $output == ARRAY_A || $output == ARRAY_N )
{
$new_array = array();
if( $this->last_result )
{
$i = 0;
foreach( $this->last_result as $row )
{
$new_array[$i] = get_object_vars($row);
if( $output == ARRAY_N )
{
$new_array[$i] = array_values($new_array[$i]);
}
$i++;
}
return $new_array;
}
else
{
return array();
}
}
}
/**
* Get a table (or "<p>No Results.</p>") for the SELECT query results.
*
* @return string HTML table or "No Results" if the
*/
function debug_get_rows_table( $max_lines, $break_at_comma = false )
{
$r = '';
if( ! is_resource($this->result) )
{
return '<p>No Results.</p>';
}
// Get column info:
$col_info = array();
$n = mysql_num_fields($this->result);
$i = 0;
while( $i < $n )
{
$col_info[$i] = mysql_fetch_field($this->result, $i);
$i++;
}
// =====================================================
// Results top rows
$r .= '<table cellspacing="0" summary="Results for query"><tr>';
for( $i = 0, $count = count($col_info); $i < $count; $i++ )
{
$r .= '<th><span class="type">'.$col_info[$i]->type.' '.$col_info[$i]->max_length.'</span><br />'
.$col_info[$i]->name.'</th>';
}
$r .= '</tr>';
// ======================================================
// print main results
if( $this->last_result )
{
for( $i = 0, $n = min(count($this->last_result), $max_lines); $i < $n; $i++ )
{
$one_row = $this->get_row(NULL, ARRAY_N, $i);
$r .= '<tr>';
foreach( $one_row as $item )
{
if( $i % 2 )
{
$r .= '<td class="odd">';
}
else
{
$r .= '<td>';
}
if( $break_at_comma )
{
$item = str_replace( ',', '<br />', $item );
$item = str_replace( ';', '<br />', $item );
$r .= $item;
}
else
{
if( strlen( $item ) > 50 )
{
$item = substr( $item, 0, 50 ).'...';
}
$r .= htmlspecialchars($item);
}
$r .= '</td>';
}
$r .= '</tr>';
}
} // if last result
else
{
$r .= '<tr><td colspan="'.(count($col_info)+1).'">No Results</td></tr>';
}
if( $i >= $max_lines )
{
$r .= '<tr><td colspan="'.(count($col_info)+1).'">Max number of dumped rows has been reached.</td></tr>';
}
$r .= '</table>';
return $r;
}
/**
* Format a SQL query
* @static
* @todo dh> Steal the code from phpMyAdmin :)
* @param string SQL
* @param boolean Format with/for HTML?
*/
function format_query( $sql, $html = true )
{
$sql = trim( str_replace("\t", ' ', $sql ) );
if( $html )
{
$sql = htmlspecialchars( $sql );
$replace_prefix = "<br />\n";
}
else
{
$replace_prefix = "\n";
}
// Split by FROM, WHERE, .. and AND, OR (if there's no comment sign before)
// TODO: dh> should not wrap in comments/string literals
$search = array(
'~(FROM|WHERE|GROUP BY|ORDER BY|LIMIT|VALUES)~',
'~(AND |OR )~',
);
$replace = array(
$replace_prefix.'$1',
$replace_prefix.( $html ? ' ' : ' ' ).' $1',
);
$sql = preg_replace( $search, $replace, $sql );
return $sql;
}
/**
* Displays all queries that have been executed
*
* @param boolean Use HTML.
*/
function dump_queries( $html = true )
{
global $Timer;
if( is_object( $Timer ) )
{
$time_queries = $Timer->get_duration( 'sql_queries' );
}
else
{
$time_queries = 0;
}
$count_queries = 0;
$count_rows = 0;
if ( $html )
{
echo '<strong>DB queries:</strong> '.$this->num_queries."<br />\n";
}
else
{
echo 'DB queries: '.$this->num_queries."\n\n";
}
if( ! $this->log_queries )
{ // nothing more to do here..
return;
}
// Javascript function to toggle DIVs (EXPLAIN, results, backtraces).
if( $html )
{
echo '<script type="text/javascript">
function debug_onclick_toggle_div( div_id, text_show, text_hide ) {
var div = document.getElementById(div_id);
var a = document.createElement("a");
a.href= "#";
a.style.display = "block";
var a_onclick = function() {
if( div.style.display == \'\' ) {
div.style.display = \'none\';
a.innerHTML = text_show;
} else {
div.style.display = \'\';
a.innerHTML = text_hide;
}
return false;
};
a.onclick = a_onclick;
div.parentNode.insertBefore(a, div);
a_onclick();
};
</script>';
}
foreach( $this->queries as $i => $query )
{
$count_queries++;
if ( $html )
{
echo '<h4>Query #'.$count_queries.': '.$query['title']."</h4>\n";
echo '<code>';
echo $this->format_query( $query['sql'] );
echo "</code>\n";
}
else
{
echo '= Query #'.$count_queries.': '.$query['title']." =\n";
echo $this->format_query( $query['sql'], false )."\n\n";
}
// Color-Format duration: long => red, fast => green, normal => black
if( $query['time'] > $this->query_duration_slow )
{
$style_time_text = 'color:red;font-weight:bold;';
$style_time_graph = 'background-color:red;';
$plain_time_text = ' [slow]';
}
elseif( $query['time'] < $this->query_duration_fast )
{
$style_time_text = 'color:green;';
$style_time_graph = 'background-color:green;';
$plain_time_text = ' [fast]';
}
else
{
$style_time_text = '';
$style_time_graph = 'background-color:black;';
$plain_time_text = '';
}
// Number of rows with time (percentage and graph, if total time available)
if ( $html )
{
echo '<div class="query_info">';
echo 'Rows: '.$query['rows'];
echo ' – Time: ';
}
else
{
echo 'Rows: '.$query['rows'].' - Time: ';
}
if( $html && $style_time_text )
{
echo '<span style="'.$style_time_text.'">';
}
echo number_format( $query['time'], 4 ).'s';
if( $time_queries > 0 )
{ // We have a total time we can use to calculate percentage:
echo ' ('.number_format( 100/$time_queries * $query['time'], 2 ).'%)';
}
if( $style_time_text || $plain_time_text )
{
echo $html ? '</span>' : $plain_time_text;
}
if( $time_queries > 0 )
{ // We have a total time we can use to display a graph/bar:
$perc = round( 100/$time_queries * $query['time'] );
if ( $html )
{
echo '<div style="margin:0; padding:0; height:12px; width:'.$perc.'%;'.$style_time_graph.'"></div>';
}
else
{ // display an ASCII bar
printf( "\n".'[%-50s]', str_repeat( '=', $perc / 2 ) );
}
}
echo $html ? '</div>' : "\n\n";
// Explain:
if( isset($query['explain']) )
{
if( $html )
{
$div_id = 'db_query_explain_'.$i.'_'.md5(serialize($query));
echo '<div id="'.$div_id.'">';
echo $query['explain'];
echo '</div>';
echo '<script type="text/javascript">debug_onclick_toggle_div("'.$div_id.'", "Show EXPLAIN", "Hide EXPLAIN");</script>';
}
else
{ // TODO: dh> contains html.
echo $query['explain'];
}
}
// Results:
if( $query['results'] != 'unknown' )
{
if( $html )
{
$div_id = 'db_query_results_'.$i.'_'.md5(serialize($query));
echo '<div id="'.$div_id.'">';
echo $query['results'];
echo '</div>';
echo '<script type="text/javascript">debug_onclick_toggle_div("'.$div_id.'", "Show results", "Hide results");</script>';
}
else
{ // TODO: dh> contains html.
echo $query['results'];
}
}
// Function trace:
if( isset($query['function_trace']) )
{
if( $html )
{
$div_id = 'db_query_backtrace_'.$i.'_'.md5(serialize($query));
echo '<div id="'.$div_id.'">';
echo $query['function_trace'];
echo '</div>';
echo '<script type="text/javascript">debug_onclick_toggle_div("'.$div_id.'", "Show function trace", "Hide function trace");</script>';
}
else
{ // TODO: dh> contains html.
echo $query['function_trace'];
}
}
if( $html )
{
echo '<hr />';
}
else
{
echo "=============================================\n";
}
$count_rows += $query['rows'];
}
if ( $html )
{
echo "\n<strong>Total rows:</strong> $count_rows<br />\n";
}
else
{
echo 'Total rows: '.$count_rows."\n";
}
}
/**
* BEGIN A TRANSCATION
*
* Note: By default, MySQL runs with autocommit mode enabled.
* This means that as soon as you execute a statement that updates (modifies)
* a table, MySQL stores the update on disk.
* Once you execute a BEGIN, the updates are "pending" until you execute a
* {@link DB::commit() COMMIT} or a {@link DB:rollback() ROLLBACK}
*
* Note 2: standard syntax would be START TRANSACTION but it's not supported by older
* MySQL versions whereas BEGIN is...
*
* Note 3: The default isolation level is REPEATABLE READ.
*/
function begin()
{
if( $this->use_transactions )
{
$this->query( 'BEGIN', 'BEGIN transaction' );
$this->transaction_nesting_level++;
}
}
/**
* Commit current transaction
*/
function commit()
{
if( $this->use_transactions )
{
if( $this->transaction_nesting_level == 1 )
{ // Only COMMIT if there are no remaining nested transactions:
if( $this->rollback_nested_transaction )
{
$this->query( 'ROLLBACK', 'ROLLBACK transaction because there was a failure somewhere in the nesting of transactions' );
}
else
{
$this->query( 'COMMIT', 'COMMIT transaction' );
}
$this->rollback_nested_transaction = false;
}
if( $this->transaction_nesting_level )
{
$this->transaction_nesting_level--;
}
}
}
/**
* Rollback current transaction
*/
function rollback()
{
if( $this->use_transactions )
{
if( $this->transaction_nesting_level == 1 )
{ // Only ROLLBACK if there are no remaining nested transactions:
$this->query( 'ROLLBACK', 'ROLLBACK transaction' );
$this->rollback_nested_transaction = false;
}
else
{ // Remember we'll have to roll back at the end!
$this->rollback_nested_transaction = true;
}
if( $this->transaction_nesting_level )
{
$this->transaction_nesting_level--;
}
}
}
/**
* Convert a PHP charset to its MySQL equivalent.
*
* @param string PHP charset
* @return string MYSQL charset or unchanged
*/
function php_to_mysql_charmap( $php_charset )
{
$php_charset = strtolower($php_charset);
/**
* This is taken from phpMyAdmin (libraries/select_lang.lib.php).
*/
static $mysql_charset_map = array(
'big5' => 'big5',
'cp-866' => 'cp866',
'euc-jp' => 'ujis',
'euc-kr' => 'euckr',
'gb2312' => 'gb2312',
'gbk' => 'gbk',
'iso-8859-1' => 'latin1',
'iso-8859-2' => 'latin2',
'iso-8859-7' => 'greek',
'iso-8859-8' => 'hebrew',
'iso-8859-8-i' => 'hebrew',
'iso-8859-9' => 'latin5',
'iso-8859-13' => 'latin7',
'iso-8859-15' => 'latin1',
'koi8-r' => 'koi8r',
'shift_jis' => 'sjis',
'tis-620' => 'tis620',
'utf-8' => 'utf8',
'windows-1250' => 'cp1250',
'windows-1251' => 'cp1251',
'windows-1252' => 'latin1',
'windows-1256' => 'cp1256',
'windows-1257' => 'cp1257',
);
if( isset($mysql_charset_map[$php_charset]) )
{
return $mysql_charset_map[$php_charset];
}
// for lack of a better answer:
return $php_charset;
}
/**
* Set the charset of the connection.
*
* WARNING: this will fail on MySQL 3.23
*
* @staticvar array "regular charset => mysql charset map"
* @param string Charset
* @param boolean Use the "regular charset => mysql charset map"?
* @return boolean true on success, false on failure
*/
function set_connection_charset( $charset, $use_map = true )
{
global $Debuglog;
$charset = strtolower($charset);
if( $use_map )
{ // We want to use the map
$charset = $this->php_to_mysql_charmap( $charset );
}
$r = true;
if( $charset != $this->connection_charset )
{
// SET NAMES is not supported by MySQL 3.23 and for a non-supported charset even not in MySQL 5 probably..
$save_show_errors = $this->show_errors;
$save_halt_on_error = $this->halt_on_error;
$this->show_errors = false;
$this->halt_on_error = false;
$last_error = $this->last_error;
$error = $this->error;
if( $this->query( 'SET NAMES '.$charset ) === false )
{
$Debuglog->add( 'Could not "SET NAMES '.$charset.'"! (MySQL error: '.strip_tags($this->last_error).')', 'locale' );
$r = false;
}
else
{
$Debuglog->add( 'Set DB connection charset: '.$charset, 'locale' );
$this->connection_charset = $charset;
}
$this->show_errors = $save_show_errors;
$this->halt_on_error = $save_halt_on_error;
// Blatantly ignore any error generated by SET NAMES...
$this->last_error = $last_error;
$this->error = $error;
}
return $r;
}
}

Source for file _db.class.php