Enhance.How Pi Analyzes Database SQL Execution State

Brief

As we know, the entry instance of Zend is Zend\Db\Adapter\Adapter and Pi inherits this class to make it possible to implement some features for Pi. In order to hide the initialization from user, Pi use a Pi\Db\Gateway class to do the task. Then we can fetch the adapter instance easily by:

$adapter = Pi::db();

Then we can operate database without any problems by using provided APIs. For more information and API, please refer to:

• Pi Gateway
• Pi Model

Pi profiler

When we execute a sql statement, we can find its execution details in the db tab of debug panel at the bottom of the page, only if you set the environment to empty or development. Zend also provides a profiler named Zend\Db\Adapter\Profiler\Profiler to do the task, but Pi does not use it. So how Pi embeds its dbprofiler?

We can find the answer by viewing the code in Gateway class:

public function createAdapter(array $config, $platform = null)
{
    // Canonize config
    $options = array();
    if (isset($config['options'])) {
        $options = $config['options'];
        unset($config['options']);
    }

    // Set user-supplied statement class derived from PDOStatement.
    // Cannot be used with persistent PDO instances.
    // @see http://www.php.net/manual/en/pdo.setattribute.php
    if (!isset($config['driver_options'][PDO::ATTR_STATEMENT_CLASS])) {
        $config['driver_options'][PDO::ATTR_STATEMENT_CLASS] = array(
            static::STATEMENT_CLASS,
            array($this->profiler() ?: null)
        );
    }

    $adapter = new Adapter($config, $platform);

    return $adapter;
}

In the method, you can see a PDO attribute PDO::ATTR_STATEMENT_CLASS is added to configuration and this configuration is therefore use to construct PDO instance. By viewing the code, we can learn that, the statement class can also be customized by setting the service.database.php. For example:

    return array(
    // Connection configs, to be passed to driver
    'connection'    => array(
        'driver'    => 'pdo',
        ...

        'driver_options'   => array(
            PDO::MYSQL_ATTR_INIT_COMMAND    =>
                'SET NAMES utf8 COLLATE utf8_general_ci',
            PDO::ATTR_PERSISTENT            => false,

            // Custom PDOstatement class.
            PDO::ATTR_STATEMENT_CLASS       => array('CustomClass', []),
        ),

        ...
    ),
    ...
);

Pi uses Pi\Db\Adapter\Driver\Statement as statement class, and passes Pi\Log\Formatter\DbProfiler as parameter to it. And DbProfiler is used to log SQL execution details.

Brief on how Pi log sql execution details

• Get database adapter, Pi::db()->adapter(), in the process, db connection driver will be created
• Start a SQL by $adapter->query();
• query method initializes a statement instance
• Then a PDO prepare method is call, $statement->prepare()
• Since we set the statement class at the beginning, a Pi\Db\Adapter\Driver\Statement instance is return
• At last, execute() method of statement class is called
• A execution instruction is send to DB server, and DbProfiler::log()