Zend Enable SQL Query logging

Question

I am using this to retrieve the database connection atm.

    $db = Zend_Db_Table::getDefaultAdapter();

I do set this up in my config like this:

resources.db.adapter = pdo_mysql
resources.db.isDefaultTableAdapter = true
resources.db.params.host = localhost
resources.db.params.username = root
resources.db.params.password = password
resources.db.params.dbname = db
resources.db.params.profiler.enabled = true
resources.db.params.profiler.class = Zend_Db_Profiler

I would like to output everything to a sql.log for example. Is this possible to apply on the default adapter? for example through the settings, so I can ignore it in production environment?

Much appriciated.

I did look at: How to enable SQL output to log file with Zend_Db? but it didn't seem to cover my issue.

/Marcus

Answer 1

There is an example of extending Zend_Db_Profiler so you can write the queries to /logs/db-queries.log file.

So you have to do the following:

1. Create My_Db_Profiler_Log class in the library folder
2. Add the following lines to the application.ini

resources.db.params.profiler.enabled = true

resources.db.params.profiler.class = My_Db_Profiler_Log

Note: be aware, that the log file will become very big, very soon! So it is a good idea to log only the queries you are interested in. And this example should be considered only as a starting point in implementation of such a logging system.

Here is the code for the custom profiler class:

<?php

class My_Db_Profiler_Log extends Zend_Db_Profiler {

/**
 * Zend_Log instance
 * @var Zend_Log
 */
protected $_log;

/**
 * counter of the total elapsed time
 * @var double 
 */
protected $_totalElapsedTime;


public function __construct($enabled = false) {
    parent::__construct($enabled);

    $this->_log = new Zend_Log();
    $writer = new Zend_Log_Writer_Stream(APPLICATION_PATH . '/logs/db-queries.log');
    $this->_log->addWriter($writer);
}

/**
 * Intercept the query end and log the profiling data.
 *
 * @param  integer $queryId
 * @throws Zend_Db_Profiler_Exception
 * @return void
 */
public function queryEnd($queryId) {
    $state = parent::queryEnd($queryId);

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

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



        // update totalElapsedTime counter
        $this->_totalElapsedTime += $profile->getElapsedSecs();

        // create the message to be logged
        $message = "\r\nElapsed Secs: " . round($profile->getElapsedSecs(), 5) . "\r\n";
        $message .= "Query: " . $profile->getQuery() . "\r\n";

        // log the message as INFO message
        $this->_log->info($message);

}

}

?>