The Developer Day | Staying Curious

TAG | database

Feb/10

20

Zend Framework Database Profiler Reporting

Zend Framework has a powerful Database Profiler component. It is remarkably simple to integrate the database profiler with the Zend Framework front controller with the use of Firefox addons FireBug and FirePHP:

// In your bootstrap file
$profiler = new Zend_Db_Profiler_Firebug('All DB Queries');
$profiler->setEnabled(true);
// Attach the profiler to your db adapter
$db->setProfiler($profiler);

Or just by enabling it in the database configuration:

$db = array(
    'type' => 'pdo_mysql',
    'host' => 'localhost',
    'dbname' => 'dbname',
    'username' => 'username',
    'password' => 'password',
    'profiler' => array(
        'enabled' => true,
        'class' => 'Zend_Db_Profiler_Firebug',
    )
);

Producing nicely formatted results:

Firebug profiler is great tool. But it requires to use Firefox and have FireBug and FirePHP addons installed. Not everyone in your organization may be Firefox users or have these addons installed. Another drawback is that sometimes queries may be quite big and complex. It’s not easy to analyse them in a Firebug window or to copy them to an editor that supports SQL highlighting.

To solve that it is possible to create a custom database profiler reporting mechanism. To output the profiler queries I’ll create a custom Zend Framework action helper:

class Company_Product_Helper_Profiler extends Zend_Controller_Action_Helper_Abstract
{
    protected $_profiler;
    protected $_view;
    protected $_config;
    protected $_db;
    public function __construct(Zend_Config $config, $db)
    {
        $this->_config = $config;
        $this->_db = $db;
    }
    public function init()
    {
        $this->_view = new Zend_View();
        parent::init();
    }
    public function postDispatch()
    {
        $this->_profiler = new Company_Product_Db_Profiler_Report($this->_db1);
        if (!$this->_isProfilerEnabled()) {
            return false;
        }
        $this->outputToScreen();
    }
    protected function _isProfilerEnabled()
    {
        if ($this->_config->environment == 'live') {
            return false;
        }
        if (!empty($_SERVER['HTTP_X_REQUESTED_WITH'])) {
            return false;
        }
        $profiler = $this->getRequest()->getParam('profiler');
        if (!empty($profiler)) {
            return true;
        }
        return false;
    }
    protected function outputToScreen()
    {
        $this->_view->headLink()->appendStylesheet('/css/highlight/default.css', 'screen');
        $this->_view->headScript()->appendFile('/js/highlight.pack.js');
        $this->_view->headScript()->appendScript('
            hljs.tabReplace = \'    \';
            hljs.initHighlightingOnLoad();
        ');
        $this->getResponse()->appendBody($this->_profiler);
    }
}

The action helper uses a post dispatch hook. The post dispatch hook is called after the Zend Framework dispatcher dispatches a request. A post dispatch hook is used to ensure that all queries have been run and to integrate the profiler with the Front Controller without changing any of the controller classes.

Action helper profiler checks to see if any profiling information should be outputted. It checks if the current configuration environment is not production to not allow unauthorized people to use the profiler. It also checks if a request is not an AJAX request, since AJAX requests may be returning JSON data structures and any additional output can break the client which sent the request. Profiler can be enabled by adding a URL query parameter ?profiler=1.

To highlight the SQL queries profiler uses a third party JavaScript highlighting library Highlight.js. On page load highlight.js finds all <code> nodes in the DOM structure, detects the programming or markup language type of the content inside them and highlights it.

The profiler action helper also uses another class to format the profiler report. The profiler report class implements an iterator interface and the __toString method allowing to use it in different ways. It could also be used to format profiler output for environments such as terminals which can’t interpret HTML.

class Company_Product_Db_Profiler_Report implements Iterator
{
    protected $_profiler;
    protected $_html = false;
    protected $_position ;
    public function __construct($db, $html = true)
    {
        $this->_profiler = $db->getProfiler();
        $this->_html = $html;
        $this->_position = 0;
        $this->_profiles = $this->_profiler->getQueryProfiles();
    }
    public function setOutputFormatToHtml($value)
    {
        $this->_html = !empty($value);
    }
    public function __toString()
    {
        $out = "";
        if ($this->_html) {
            foreach ($this->_profiles as $key => $query) {
                $out .= $this->_formatProfileAsHtml($key, $query);
            }
        } else {
            foreach ($this->_profiles as $key => $query) {
                $out .= $this->_formatProfileAsText($key, $query);
            }
        }
        return $out;
    }
    protected function _formatProfileAsHtml($key, $query)
    {
        $out = "<pre><code>{$key}. " . wordwrap($query->getQuery(), 150) . "\n\n ({$query->getElapsedSecs()} s.) </code> </pre>";
        return $out;
    }
    protected function _formatProfileAsText($key, $query)
    {
        $out = "$key. {$query->getQuery()} ({$query->getElapsedSecs()} s.)\n\n";
        return $out;
    }
    public function rewind()
    {
        $this->_position = 0;
    }
    public function current()
    {
        if ($this->_html) {
            return $this->_formatProfileAsHtml($this->_position, $this->_profiles[$this->_position]);
        } else {
            return $this->_formatProfileAsText($this->_position, $this->_profiles[$this->_position]);
        }
    }
    public function key()
    {
        return $this->_position;
    }
    public function next()
    {
        ++$this->_position;
    }
    public function valid()
    {
        return isset($this->_profiles[$this->_position]);
    }
}

To make Zend Framework aware of the profiler action helper add the following lines of code to the bootstrap file:

Zend_Controller_Action_HelperBroker::addHelper(
    new Company_Product_Helper_Profiler($config, $databaseConnection)
);

When done the output of the profiler should look something like this:

, , , , , Hide

Nov/08

18

What is the future of relational databases?

A great recorded video keynote from the OpenSQL camp conference featuring Brian Aker. He asks some interesting questions about the state of databases. He has some interesting points about the way we currently use hardware and problems that are at our door and how opensource projects should step up to solve them.

, , Hide

Find it!

Theme Design by devolux.org