Framework: Zend Framework 1
Objective: Log all database queries preferably to a file

Logging all database queries is very useful for debugging applications. In Zend Framework I at least 4 methods exist that can do this.

Method 1. View helper

Display all queries in debug-mode at the bottom of the screen, on many platforms (CodeIginition for example) this feature is built-in.

In the bottom of application\layouts\scripts\layout.phtml set:


echo profilerView();

where profilerView() is a view helper set in \library\My\View\Helpers\ProfilerView.php


class My_View_Helpers_ProfilerView
{
    public function profilerView()
    {
        $htmlArr = array();

        $db = Zend_Registry::get('db');
        /* @var $db Zend_Db_Adapter_Pdo_Mysql */

        $htmlArr[] = $db->getProfiler()->getTotalNumQueries();

        $queries = $db->getProfiler()->getQueryProfiles();
        foreach ($queries as $query) {

            /* @var $query Zend_Db_Profiler_Query */
            $params = $query->getQueryParams();

            $line = $query->getQuery();

            if (count($params) > 0) {
                $line .= ' ' . implode(", ", $params);
            }

            $htmlArr[] = $line;
        }
        return implode('
', $htmlArr); } }

The helper path is set for example in the bootstrap file:


class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
 protected function _initView()
    {
        $view = new Zend_View();
        $viewRenderer 
= new Zend_Controller_Action_Helper_ViewRenderer();
        $view
->addHelperPath('My/View/Helpers/', 'My_View_Helpers');       
        $viewRenderer
->setView($view);
        Zend_Controller_Action_HelperBroker::addHelper($viewRenderer);
    }
}

Method 2. Via FireBug

In application.ini set


dbassess.adapter = PDO_MYSQL
dbassess.params.host = localhost
dbassess.params.username = user
dbassess.params.password = secret
dbassess.params.dbname = db
dbassess.params.profiler.enabled  = true
dbassess.params.profiler.class  = Zend_Db_Profiler_Firebug

In Firefox get de FirePHP extension. With FireBug and Net >> Console get the query log output.

Method 3. Via extending Zend_Db_Profiler

For logging to file other methods work. Firstly, in the bootstrap file register a logging device:


class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
 protected function _initDbLogging()
    {
        $logger = new Zend_Log();
        $path 
= APPLICATION_PATH . '/../data/logs/db-' . date('Y-m-d') . '.log';
        $writer = new Zend_Log_Writer_Stream($path);
        $formatter 
= new Zend_Log_Formatter_Simple('%timestamp% %message%' . PHP_EOL);
        $writer->setFormatter($formatter);
        $logger->addWriter($writer);
        Zend_Registry::set('dbLogger', $logger);
    }
}

Then in application.ini set a new profiler class:


dbassess.adapter = PDO_MYSQL
dbassess.params.host = localhost
dbassess.params.username = user
dbassess.params.password = secret
dbassess.params.dbname = db
dbassess.params.profiler.enabled  = true
dbassess.params.profiler.class  = My_Db_Profiler

This class (in library\My\Db\Profiler.php) extends from Zend_Db_Profiler:


class My_Db_Profiler extends Zend_Db_Profiler
{
    public function queryEnd($queryId)
    {
        $state = parent::queryEnd($queryId);

        if (!$this->getEnabled() || $state == self::IGNORED) {
            return false;
        }

        // get the query
        $query = $this->getQueryProfile($queryId);

        // start a zend log
        $log = Zend_Registry::get('dbLogger');

        /* @var $log Zend_Log */

        /* @var $query Zend_Db_Profiler_Query */
        $params = $query->getQueryParams();

        $line = $query->getQuery();

        if (count($params) > 0) {
            $line .= ' ' . implode(", ", $params);
        }

        $log->log($line, Zend_Log::INFO);
    }

}

Method 4 Via a plugin

Register a new plugin in application.ini and also as before enable the profiler:


resources.frontController.plugins[] = "My_Plugins_MyProfiler"

dbassess.params.profiler.enabled  = true

Then create the plugin in library\My\Plugins\MyProfiler.php
The dispatchLoopShutdown() method is called automatically when the front controller exits the dispatch loop.


class My_Plugins_MyProfiler extends Zend_Controller_Plugin_Abstract
{

    public function dispatchLoopShutdown()
    {

        $log = Zend_Registry::get('dbLogger');

        /* @var $log Zend_Log */

        $db = Zend_Registry::get('db3');

        /* @var $db Zend_Db_Adapter_Pdo_Mysql */

        $queries = $db->getProfiler()->getQueryProfiles();

        if (is_array($queries)) {
            
            foreach ($queries as $query) {

                /* @var $query Zend_Db_Profiler_Query */
                $params = $query->getQueryParams();

                $line = $query->getQuery();

                if (count($params) > 0) {
                    $line .= ' ' . implode(", ", $params);
                }

                $log->log($line . ' ', Zend_Log::INFO);
            }
        }
    }
}

Note that dispatchLoopShutdown is not called when redirecting for example with $this->_redirect() in a controller. Queries that happened before this redirect will not get logged. As a solution add to bootstrap.php


 protected function _initRedirector()
 {
   $redirector 
       = Zend_Controller_Action_HelperBroker::getStaticHelper('redirector');
   $redirector->setExit(false);
 }

Links:
* http://stackoverflow.com/questions/8114988/zend-enable-sql-query-logging?lq=1
* https://coderwall.com/p/-3ppca
* http://zend-framework-community.634137.n4.nabble.com/dispatchLoopShutdown-not-called-when-redirecting-workaround-td3008797.html

Advertisements