Siim Viikman's blog

Joomla has a debug plugin that is able to display different kind of information about page execution – load times in page lifecycle, strings localizations and executed SQL statements. But when using Doctrine, you can’t use this Joomla plugin for SQL statements because Doctrine uses it’s own database connection management, different from Joomla’s.

Fortunately Doctrine already comes with built-in profiler support, we only need to integrate it with Joomla plugins framework. Joomla documentation contains a tutorial on how to create a plugin, it contains also a list of events that are supported by the plugin framework. And there are a wiki page that describes the Joomla API execution order.

I needed onAfterDispatch and onAfterRender events so I created plugin of type system. In onAfterDispatch event I attach Doctrine profiler to the database connection and in the onAfterRender event I collect the SQL statements. Profiler logs different kind of events, but I’m only interested in events that show execution.

Here is the source of the plugin ( I have skipped the not so important parts to keep it shorter).

class plgSystemDoctrine_profiler extends JPlugin
{
	private static $NewLineKeywords = '/(FROM|LEFT|INNER|OUTER|WHERE|SET|VALUES|ORDER|GROUP|HAVING|LIMIT|ON|AND)/';

	private $_outputEnabled = false;
	private $_log4phpEnabled = false;
	private $_logEnabled = false;
	private $_logName = 'doctrine.profiler';

	private $_profiler = null;
	private $_formattedEvents = array();

	public function __construct(&$subject, $config)
	{
		parent::__construct($subject, $config);

		// Initialize from params
	}

	private function getProfiler()
	{
		return $this->_profiler;
	}

	public function onAfterDispatch()
	{
		$this->_log4phpEnabled = $this->_log4phpEnabled && class_exists('Logger');

		if (!class_exists('Doctrine_Connection_Profiler')) return;

		$this->_profiler = new Doctrine_Connection_Profiler();

		$connection = Doctrine_Manager::connection();
		$connection->setListener($this->_profiler);
	}

	public function onAfterRender()
	{
		if ($this->getProfiler() == null) return;

		foreach($this->getProfiler() as $event)
		{
			if ($event->getName() != 'execute') continue;

			$this->_formattedEvents[] = $this->formatEvent($event);
		}

		$this->writeOutput();
		$this->writeLog4php();
		$this->writeLog();
	}

	private function writeOutput()
	{
		if (!$this->_outputEnabled) return;

		// write output to page
	}

	private function writeLog4php()
	{
		if (!$this->_log4phpEnabled) return;

		// Write output to log4php
	}

	private function writeLog()
	{
		if (!$this->_logEnabled) return;

		// Write output to Joomla logs
	}

	private function formatEvent(Doctrine_Event $event)
	{
		$sql = $event->getQuery();
		$sql = preg_replace(self::$NewLineKeywords, "\n\t\\0", $sql);

		$formatted = str_repeat('--', 10)."\n";
		$formatted .= $sql."\n";
		$formatted .= "Params: ".join(', ', $event->getParams())."\n";
		$formatted .= 'Elapsed time: '.$event->getElapsedSecs().' seconds'."\n";
		return $formatted;
	}
}

The usage of the onAfterDispatch event depends on your architecture, in some cases it may be too late for you to attach the profiler in that point. Another options is to use similar approach like Joomla is using with its profiler – usage of global variable $_PROFILER. You can define a similar variable for Doctrine’s profiler and attach it to the connection in the same place where you created it. In that case you need to override the getProfiler() method in the plugin class to return that global variable.

One thing that is really missing in Joomla debug plugin, is the support of writing output to a log file. For example in some cases I don’t want to display debug information on the page (although I can hide it with CSS I guess) or I want to monitor it in the background and review later. So I added logging support to my profiler plugin. It has support for log4php and also for Joomla’s own logging system, in addition to standard output to the page. You can configure it under plugin settings. You can download it from here.