Outils pour utilisateurs

Outils du site


langages:php:profiler-pdo-mysql

En gists pour archive : version de base et complète

<?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>
langages/php/profiler-pdo-mysql.txt · Dernière modification: 08/12/2014 16:28 (modification externe)