Profiling MySQL/PDO queries

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');
}

//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 $_query_string;
    protected $_executions = array();

    public function __construct($query_string) {
        $this->_query_string = $query_string;
    }

    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;
    }
}

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) {
        if (!isset($this->_queries[$statement])) {
            $this->_queries[$statement] = new Query($statement);
        }
        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);
        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 '<ul>';
            $queries = $prof->fetchAll(PDO::FETCH_UNIQUE);
            foreach ($this->_queries as $q) {
                echo '<li>';
                $duration = 0;
                foreach ($q as $qe) {
                    $duration += $queries[$qe->getId()]->Duration;
                }
                echo '<p>', h($q->getQueryString()), ' Executed : ', count($q), ' times', ' Total duration: ', $duration, ' Average duration: ', $duration / count($q), '</p>';
                echo '<ul>';
                foreach ($q as $qe) {
                    $qp = $queries[$qe->getId()];
                    echo '<li>', $qe->getId(), ' : ', h($qp->Query), ' ', $qp->Duration, '</li>';
                }
                echo '</ul>';
                echo '</li>';
            }
            echo '</ul>';
        }
        /*
        $prof = parent::query('SHOW PROFILES', self::FETCH_OBJ);
        if ($prof->rowCount()) {
            $queryprof = parent::prepare('SHOW PROFILE SOURCE FOR QUERY :id');
            echo '<ul>';
            foreach ($prof as $q) {
                //printf('<li>%s : %d exécution(s)</li>', $q->getQueryString(), count($q));
                printf('<li>%d : %s (%f)</li>', $q->Query_ID, $q->Query, $q->Duration);
                //echo '<ul>';
                //foreach ($q as $qe) {
                    //printf('<li>ID = %d, time = %f</li>', $qe->getId(), $qe->getTime());
                    //$queryprof->bindValue('id', $qe->getId(), self::PARAM_INT);
                    $queryprof->bindValue('id', intval($q->Query_ID), self::PARAM_INT);
                    $queryprof->execute();
                    //$queryprof->setFetchMode(PDO::FETCH_OBJ);
                    printf('<table class="profile" id="P%d">', $q->Query_ID);
                    echo '<thead>';
                    echo '<tr>';
                    echo '<th>Status</th>';
                    echo '<th>Duration</th>';
                    echo '<th>Source function</th>';
                    echo '<th>Source file</th>';
                    echo '<th>Source line</th>';
                    echo '</tr>';
                    echo '</thead>';
                    echo '<tbody>';
                    foreach ($queryprof as $p) {
                        echo '<tr>';
                        echo '<td>', $p->Status, '</td>';
                        echo '<td>', $p->Duration, '</td>';
                        echo '<td>', $p->Source_function, '</td>';
                        echo '<td>', $p->Source_file, '</td>';
                        echo '<td>', $p->Source_line, '</td>';
                        echo '</tr>';
                    }
                    echo '</tbody>';
                    echo '</table>';
                //}
                //echo '</ul>';
            }
            echo '</ul>';
        }*/
    }
}

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();

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

/*
Output:

    * SELECT COUNT(*) FROM utilisateurs WHERE id = :id Executed : 3 times Total duration: 0.0004075 Average duration: 0.00013583333333333
          o 1 : SELECT COUNT(*) FROM utilisateurs WHERE id = 3 0.00019925
          o 3 : SELECT COUNT(*) FROM utilisateurs WHERE id = 6 0.00010900
          o 5 : SELECT COUNT(*) FROM utilisateurs WHERE id = 1 0.00009925
    * UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto" Executed : 2 times Total duration: 0.0002645 Average duration: 0.00013225
          o 2 : UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto" 0.00015250
          o 4 : UPDATE utilisateurs SET verrouille = 1 WHERE login = "toto" 0.00011200
*/