julp/pdo_mysql_profiler.php

## pdo_mysql_profiler.php
<?php
namespace Julp;

if (isset($_SERVER['PHP_ENV']) && $_SERVER['PHP_ENV'] == 'development' && function_exists('ob_tidyhandler')) {
/*
tidy.default_config is:
indent: true
indent-spaces: 4
output-xhtml: yes
wrap: 0
tidy-mark: no
input-encoding: utf8
output-bom: no
output-encoding: utf8
*/
    ob_start('ob_tidyhandler');
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>XXX</title>
        <script type="text/javascript" src="http://code.jquery.com/jquery-1.5.min.js"></script>
        <script type="text/javascript">
<!--
function id2num(id) {
    return id.substring(1);
}

$(
    function () {
        $('.query_execution').hide();

        $('.query').click(
            function () {
                var qe_id = '#QE' + id2num($(this).attr('id'));
                $('.query_execution').not(qe_id).hide();
                $(qe_id).toggle();
            }
        );
    }
);
-->
        </script>
        <style type="text/css">
<!--
#profiling table {
    border-collapse: separate;
    border: #3399CC 1px solid;
    margin: auto;
    width: 100%;
}

#profiling table table {
    width: 80%;
    margin: 16px auto;
}

#profiling th {
    background-color: #FFCC66;
    border: #FFCC66 1px solid;
    font-variant: small-caps;
    font-size: 0.8em;
    letter-spacing: 1px;
}

#profiling td {
    border: #DDEEFF 1px solid;
}
-->
        </style>
    </head>
    <body>

<?php
//use \PDO;
use \Exception;

require_once('pdo_constantes.php');

function h($text) {
    return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}

class Timer
{
    protected $_precision;
    protected $_start_time;
    protected $_stop_time;

    public function __construct($precision = 5) {
        $this->_precision = $precision;
    }

    public function start() {
        $this->_start_time = microtime(TRUE);
        return $this;
    }

    public function stop() {
        $this->_stop_time = microtime(TRUE);
        return $this;
    }

    public function getTime() {
        return round($this->_stop_time - $this->_start_time, $this->_precision);
    }

    public function getPrecision() {
        return $this->_precision;
    }
}

class Query_Execution
{
    protected $_query;
    protected $_id;

    public function __construct(Query $query, $id) {
        $this->_query = $query;
        $this->_id = $id;
    }

    public function getQuery() {
        return $this->_query;
    }

    public function getId() {
        return $this->_id;
    }
}

class Query implements \Countable, \IteratorAggregate
{
    protected $_id;
    protected $_prepared;
    protected $_query_string;
    protected $_executions = array();

    public function __construct($query_string, $prepared = FALSE) {
        $this->_id = md5($query_string);
        $this->_query_string = $query_string;
        $this->_prepared = $prepared;
    }

    public function addQueryExecution($id) {
        $this->_executions[] = new Query_Execution($this, $id);
    }

    public function count() {
        return count($this->_executions);
    }

    public function getIterator() {
        return new \ArrayIterator($this->_executions);
    }

    public function getQueryString() {
        return $this->_query_string;
    }

    public function getId() {
        return $this->_id;
    }

    public function isPrepared() {
        return $this->_prepared;
    }
}

class PDO/*_MySQL_Profiler*/ extends \PDO implements \Countable, \IteratorAggregate
{
    protected $_counter = 0;
    protected $_queries = array();

    public function __construct($dsn, $username = NULL, $password = NULL, $driver_options = array()) {
        parent::__construct($dsn, $username, $password, $driver_options);
        parent::setAttribute(self::ATTR_STATEMENT_CLASS, array(__NAMESPACE__ . '\PDOStatement', array($this)));
        parent::exec('SET SESSION query_cache_type = OFF, PROFILING = 1');
    }

    public function setAttribute($attribute, $value) {
        /*if (defined('__NAMESPACE__') && $attribute == self::ATTR_STATEMENT_CLASS) {
            if ($value[0][0] != '\\') {
                $value[0] = __NAMESPACE__ . '\\' . $value[0];
            }
        }*/
        if ($attribute == self::ATTR_STATEMENT_CLASS) {
            throw new LogicException('PDO::ATTR_STATEMENT_CLASS overwrite forbidden: a specific class is already in place.');
        }
        return parent::setAttribute($attribute, $value);
    }

    public function _query($statement, $prepared = FALSE) {
        if (!isset($this->_queries[$statement])) {
            $this->_queries[$statement] = new Query($statement, $prepared);
        } else {
            if ($prepared) {
                trigger_error('Prepared statement created more than once: ' . h($statement), E_USER_WARNING);
            }
        }
        return $this->_queries[$statement];
    }

    public function _register_query_execution($statement) {
        $this->_query($statement)->addQueryExecution(++$this->_counter);
    }

    public function exec($statement) {
        $affected = parent::exec($statement);
        $this->_register_query_execution($statement);
        return $affected;
    }

    public function query($statement) {
        $args = func_get_args();
        if (func_num_args() >= 3) {
            if ($args[1] == self::FETCH_CLASS) {
                if ($args[2][0] != '\\') {
                    $args[2] = __NAMESPACE__ . '\\' . $args[2];
                }
            }
        }
        $stmt = call_user_func_array(array(get_parent_class(), __FUNCTION__), $args);
        $this->_register_query_execution($statement);
        return $stmt;
    }

    public function prepare($statement, $driver_options = array()) {
        $this->_query($statement, TRUE);
        return parent::prepare($statement, $driver_options);
    }

    public function count() {
        return $this->_counter;
    }

    public function getIterator() {
        return new \ArrayIterator($this->_queries);
    }

    public function __destruct() {
        parent::exec('SET PROFILING = 0');

        $prof = parent::query('SHOW PROFILES', self::FETCH_OBJ);
        if ($prof->rowCount()) {
            echo '<div id="profiling">';
            echo '<table>';
            echo '<thead>';
            echo '<th>Query</th>';
            echo '<th>Prepared ?</th>';
            echo '<th>Executed</th>';
            echo '<th>Total Duration</th>';
            echo '<th>Average Duration</th>';
            echo '</thead>';
            echo '<tbody>';
            $queries = $prof->fetchAll(PDO::FETCH_UNIQUE);
            foreach ($this->_queries as $q) {
                echo '<tr class="query" id="Q', $q->getId(), '">';
                $duration = 0;
                foreach ($q as $qe) {
                    $duration += $queries[$qe->getId()]->Duration;
                }
                echo '<td>', h($q->getQueryString()), '</td>';
                echo '<td>', $q->isPrepared() ? 'Yes' : 'No', '</td>';
                echo '<td>', count($q), ' time(s)</td>';
                echo '<td>', number_format($duration, 8), '</td>';
                echo '<td>', number_format($duration / count($q), 8), '</td>';
                echo '</tr>';
                echo '<tr class="query_execution" id="QE', $q->getId(), '">';
                echo '<td colspan="99">';
                echo '<table>';
                echo '<thead>';
                echo '<th>ID</th>';
                echo '<th>Real final query</th>';
                echo '<th>Duration</th>';
                echo '</thead>';
                echo '<tbody>';
                foreach ($q as $qe) {
                    $qp = $queries[$qe->getId()];
                    echo '<tr>';
                    echo '<td>', $qe->getId(), '</td>';
                    echo '<td>', h($qp->Query), '</td>';
                    echo '<td>', number_format($qp->Duration, 8), '</td>';
                    echo '</tr>';
                }
                echo '</tbody>';
                echo '</table>';
                echo '</td>';
                echo '</tr>';
            }
            echo '</tbody>';
            echo '</table>';
            echo '</div>';
        }
    }
}

class PDOStatement/*_MySQL_Profiler*/ extends \PDOStatement
{
    protected $_pdo;

    protected function __construct(PDO $pdo) {
        $this->_pdo = $pdo;
    }

    public function execute($input_parameters = NULL) {
        $bool = parent::execute($input_parameters);
        $this->_pdo->_register_query_execution($this->queryString);
        return $bool;
    }

    public function fetchObject($class_name = '\stdClass', $ctor_args = array()) {
        if ($class_name[0] != '\\') {
            $class_name = __NAMESPACE__ . '\\' . $class_name;
        }
        return parent::fetchObject($class_name, $ctor_args);
    }

    public function setFetchMode($mode, $class_name = NULL) {
        if ($mode == \PDO::FETCH_CLASS) {
            $ctor_args = func_num_args() >= 3 ? func_get_arg(2) : array();
            if ($class_name[0] != '\\') {
                $class_name = __NAMESPACE__ . '\\' . $class_name;
            }
            return parent::setFetchMode($mode, $class_name, $ctor_args);
        } else {
            return call_user_func_array(array(get_parent_class(), __FUNCTION__), func_get_args());
        }
    }
}

/* TESTING */

//function var_dump() {}

try {
    $dbh = new PDO(DSN, LOGIN, MOT_DE_PASSE);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

    /*$dbh->exec('SET SESSION query_cache_type = OFF');
    $dbh->exec('SET PROFILING = 1');*/

    $utils = $dbh->prepare('SELECT COUNT(*) FROM utilisateurs WHERE id = :id');
    $utils->bindParam('id', $id, PDO::PARAM_INT);

    $id = 3;
    $utils->execute();
    $utils->fetch();

    $dbh->exec('UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto"');

    $id = 6;
    $utils->execute();
    $utils->fetch();

    $dbh->exec('UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto"');

    $id = 1;
    $utils->execute();
    $utils->fetch();

    $utils = $dbh->prepare('SELECT COUNT(*) FROM utilisateurs WHERE id = :id');
    $utils->execute(array('id' => '42'));

} catch (Exception $e) {
    die(sprintf("%s dans %s à la ligne %d : %s", get_class($e), $e->getFile(), $e->getLine(), $e->getMessage()));
}
?>

    </body>
</html>
	<?php
	namespace Julp;

	if (isset($_SERVER['PHP_ENV']) && $_SERVER['PHP_ENV'] == 'development' && function_exists('ob_tidyhandler')) {
	/*
	tidy.default_config is:
	indent: true
	indent-spaces: 4
	output-xhtml: yes
	wrap: 0
	tidy-mark: no
	input-encoding: utf8
	output-bom: no
	output-encoding: utf8
	*/
	ob_start('ob_tidyhandler');
	}
	?>

	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>XXX</title>
	<script type="text/javascript" src="http://code.jquery.com/jquery-1.5.min.js"></script>
	<script type="text/javascript">
	<!--
	function id2num(id) {
	return id.substring(1);
	}

	$(
	function () {
	$('.query_execution').hide();

	$('.query').click(
	function () {
	var qe_id = '#QE' + id2num($(this).attr('id'));
	$('.query_execution').not(qe_id).hide();
	$(qe_id).toggle();
	}
	);
	}
	);
	-->
	</script>
	<style type="text/css">
	<!--
	#profiling table {
	border-collapse: separate;
	border: #3399CC 1px solid;
	margin: auto;
	width: 100%;
	}

	#profiling table table {
	width: 80%;
	margin: 16px auto;
	}

	#profiling th {
	background-color: #FFCC66;
	border: #FFCC66 1px solid;
	font-variant: small-caps;
	font-size: 0.8em;
	letter-spacing: 1px;
	}

	#profiling td {
	border: #DDEEFF 1px solid;
	}
	-->
	</style>
	</head>
	<body>

	<?php
	//use \PDO;
	use \Exception;

	require_once('pdo_constantes.php');

	function h($text) {
	return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
	}

	class Timer
	{
	protected $_precision;
	protected $_start_time;
	protected $_stop_time;

	public function __construct($precision = 5) {
	$this->_precision = $precision;
	}

	public function start() {
	$this->_start_time = microtime(TRUE);
	return $this;
	}

	public function stop() {
	$this->_stop_time = microtime(TRUE);
	return $this;
	}

	public function getTime() {
	return round($this->_stop_time - $this->_start_time, $this->_precision);
	}

	public function getPrecision() {
	return $this->_precision;
	}
	}

	class Query_Execution
	{
	protected $_query;
	protected $_id;

	public function __construct(Query $query, $id) {
	$this->_query = $query;
	$this->_id = $id;
	}

	public function getQuery() {
	return $this->_query;
	}

	public function getId() {
	return $this->_id;
	}
	}

	class Query implements \Countable, \IteratorAggregate
	{
	protected $_id;
	protected $_prepared;
	protected $_query_string;
	protected $_executions = array();

	public function __construct($query_string, $prepared = FALSE) {
	$this->_id = md5($query_string);
	$this->_query_string = $query_string;
	$this->_prepared = $prepared;
	}

	public function addQueryExecution($id) {
	$this->_executions[] = new Query_Execution($this, $id);
	}

	public function count() {
	return count($this->_executions);
	}

	public function getIterator() {
	return new \ArrayIterator($this->_executions);
	}

	public function getQueryString() {
	return $this->_query_string;
	}

	public function getId() {
	return $this->_id;
	}

	public function isPrepared() {
	return $this->_prepared;
	}
	}

	class PDO/_MySQL_Profiler/ extends \PDO implements \Countable, \IteratorAggregate
	{
	protected $_counter = 0;
	protected $_queries = array();

	public function __construct($dsn, $username = NULL, $password = NULL, $driver_options = array()) {
	parent::__construct($dsn, $username, $password, $driver_options);
	parent::setAttribute(self::ATTR_STATEMENT_CLASS, array(__NAMESPACE__ . '\PDOStatement', array($this)));
	parent::exec('SET SESSION query_cache_type = OFF, PROFILING = 1');
	}

	public function setAttribute($attribute, $value) {
	/*if (defined('__NAMESPACE__') && $attribute == self::ATTR_STATEMENT_CLASS) {
	if ($value[0][0] != '\\') {
	$value[0] = __NAMESPACE__ . '\\' . $value[0];
	}
	}*/
	if ($attribute == self::ATTR_STATEMENT_CLASS) {
	throw new LogicException('PDO::ATTR_STATEMENT_CLASS overwrite forbidden: a specific class is already in place.');
	}
	return parent::setAttribute($attribute, $value);
	}

	public function _query($statement, $prepared = FALSE) {
	if (!isset($this->_queries[$statement])) {
	$this->_queries[$statement] = new Query($statement, $prepared);
	} else {
	if ($prepared) {
	trigger_error('Prepared statement created more than once: ' . h($statement), E_USER_WARNING);
	}
	}
	return $this->_queries[$statement];
	}

	public function _register_query_execution($statement) {
	$this->_query($statement)->addQueryExecution(++$this->_counter);
	}

	public function exec($statement) {
	$affected = parent::exec($statement);
	$this->_register_query_execution($statement);
	return $affected;
	}

	public function query($statement) {
	$args = func_get_args();
	if (func_num_args() >= 3) {
	if ($args[1] == self::FETCH_CLASS) {
	if ($args[2][0] != '\\') {
	$args[2] = __NAMESPACE__ . '\\' . $args[2];
	}
	}
	}
	$stmt = call_user_func_array(array(get_parent_class(), __FUNCTION__), $args);
	$this->_register_query_execution($statement);
	return $stmt;
	}

	public function prepare($statement, $driver_options = array()) {
	$this->_query($statement, TRUE);
	return parent::prepare($statement, $driver_options);
	}

	public function count() {
	return $this->_counter;
	}

	public function getIterator() {
	return new \ArrayIterator($this->_queries);
	}

	public function __destruct() {
	parent::exec('SET PROFILING = 0');

	$prof = parent::query('SHOW PROFILES', self::FETCH_OBJ);
	if ($prof->rowCount()) {
	echo '<div id="profiling">';
	echo '<table>';
	echo '<thead>';
	echo '<th>Query</th>';
	echo '<th>Prepared ?</th>';
	echo '<th>Executed</th>';
	echo '<th>Total Duration</th>';
	echo '<th>Average Duration</th>';
	echo '</thead>';
	echo '<tbody>';
	$queries = $prof->fetchAll(PDO::FETCH_UNIQUE);
	foreach ($this->_queries as $q) {
	echo '<tr class="query" id="Q', $q->getId(), '">';
	$duration = 0;
	foreach ($q as $qe) {
	$duration += $queries[$qe->getId()]->Duration;
	}
	echo '<td>', h($q->getQueryString()), '</td>';
	echo '<td>', $q->isPrepared() ? 'Yes' : 'No', '</td>';
	echo '<td>', count($q), ' time(s)</td>';
	echo '<td>', number_format($duration, 8), '</td>';
	echo '<td>', number_format($duration / count($q), 8), '</td>';
	echo '</tr>';
	echo '<tr class="query_execution" id="QE', $q->getId(), '">';
	echo '<td colspan="99">';
	echo '<table>';
	echo '<thead>';
	echo '<th>ID</th>';
	echo '<th>Real final query</th>';
	echo '<th>Duration</th>';
	echo '</thead>';
	echo '<tbody>';
	foreach ($q as $qe) {
	$qp = $queries[$qe->getId()];
	echo '<tr>';
	echo '<td>', $qe->getId(), '</td>';
	echo '<td>', h($qp->Query), '</td>';
	echo '<td>', number_format($qp->Duration, 8), '</td>';
	echo '</tr>';
	}
	echo '</tbody>';
	echo '</table>';
	echo '</td>';
	echo '</tr>';
	}
	echo '</tbody>';
	echo '</table>';
	echo '</div>';
	}
	}
	}

	class PDOStatement/_MySQL_Profiler/ extends \PDOStatement
	{
	protected $_pdo;

	protected function __construct(PDO $pdo) {
	$this->_pdo = $pdo;
	}

	public function execute($input_parameters = NULL) {
	$bool = parent::execute($input_parameters);
	$this->_pdo->_register_query_execution($this->queryString);
	return $bool;
	}

	public function fetchObject($class_name = '\stdClass', $ctor_args = array()) {
	if ($class_name[0] != '\\') {
	$class_name = __NAMESPACE__ . '\\' . $class_name;
	}
	return parent::fetchObject($class_name, $ctor_args);
	}

	public function setFetchMode($mode, $class_name = NULL) {
	if ($mode == \PDO::FETCH_CLASS) {
	$ctor_args = func_num_args() >= 3 ? func_get_arg(2) : array();
	if ($class_name[0] != '\\') {
	$class_name = __NAMESPACE__ . '\\' . $class_name;
	}
	return parent::setFetchMode($mode, $class_name, $ctor_args);
	} else {
	return call_user_func_array(array(get_parent_class(), __FUNCTION__), func_get_args());
	}
	}
	}

	/* TESTING */

	//function var_dump() {}

	try {
	$dbh = new PDO(DSN, LOGIN, MOT_DE_PASSE);

	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

	/*$dbh->exec('SET SESSION query_cache_type = OFF');
	$dbh->exec('SET PROFILING = 1');*/

	$utils = $dbh->prepare('SELECT COUNT(*) FROM utilisateurs WHERE id = :id');
	$utils->bindParam('id', $id, PDO::PARAM_INT);

	$id = 3;
	$utils->execute();
	$utils->fetch();

	$dbh->exec('UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto"');

	$id = 6;
	$utils->execute();
	$utils->fetch();

	$dbh->exec('UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto"');

	$id = 1;
	$utils->execute();
	$utils->fetch();

	$utils = $dbh->prepare('SELECT COUNT(*) FROM utilisateurs WHERE id = :id');
	$utils->execute(array('id' => '42'));

	} catch (Exception $e) {
	die(sprintf("%s dans %s à la ligne %d : %s", get_class($e), $e->getFile(), $e->getLine(), $e->getMessage()));
	}
	?>

	</body>
	</html>