Laravel + Doctrine

[matiux]

In a old post I read of some people that have asked about Doctrine support. I use Laravel with Doctrine, so I was wondering if you are already thinking to add this Data Source.

[itsgoingd]

Not likely as I have pretty much no experience with Doctrine, but I can help you create one. If you know how to get the executed queries from Doctrine hooking it up with Clockwork is the easy part.

[matiux]

Ok, I'm studying how to Doctrine generates events, so I will be able to say you how to catch the queries. Thanks

[matiux]

Ok, I'm manage to get the Doctrine queries. For now, I put the results in a log file with Monolog but there are the variables with all datas.

/vendor/itsgoingd/clockwork/Clockwork/Support/Laravel/ClockworkServiceProvider.php

use Doctrine\Common\EventManager;

....
public function boot() {
....
        $this->app['clockwork.laravel']->listenToEvents();
        $this->app['clockwork.eloquent']->listenToEvents();
        $this->app->make('clockwork.swift');
        $this->app->make('clockwork.doctrine');
...
}

public function register() {

....

        $this->app->singleton('clockwork.doctrine', function($app)
        {
            $doctrineEm             = $app['Doctrine\ORM\EntityManager'];

            return new DoctrineDataSource($doctrineEm, $app['log']);
        });
....
}

/vendor/itsgoingd/clockwork/Clockwork/DataSource/DoctrineDataSource.php

<?php namespace Clockwork\DataSource;

use Clockwork\Request\Request;

use Doctrine\ORM\EntityManager;
use Illuminate\Log\Writer;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Logging\SQLLogger;


class DoctrineDataSource extends DataSource implements SQLLogger
{
    /**
     * Internal array where queries are stored
     * @var array
     */
    protected $queries = array();

    /**
     * Doctrine event manager
     *
     * @var EventManager
     */
    protected $evm;

    /**
     * Doctrine entity manager
     *
     * @var EntityManager
     */
    protected $enm;

    /**
     * @var Logger
     */
    protected $logger;

    /**
     * For timing queries:
     * @var float|null
     */
    public $start = null;

    /**
     * @var null
     */
    public $query = null;

    /**
     * Doctrine connection
     *
     * @var Connection
     */
    protected $connection;

    public function __construct(EntityManager $enm, Writer $logger)
    {
        $this->evm          = $enm->getEventManager();     //Unused, for now
        $this->enm          = $enm;
        $this->logger       = $logger;
        $this->connection   = $enm->getConnection();

        $enm->getConnection()->getConfiguration()->setSQLLogger($this);
    }

    /**
     * From SQLLogger Doctrine Interface
     *
     * @param string $sql
     * @param array $params
     * @param array $types
     */
    public function startQuery($sql, array $params = null, array $types = null)
    {
        $this->start    = microtime(true);
        $sql            = $this->replaceParams($sql, $params);
        $sql            = $this->formatQuery($sql);

        $this->query    = array( 'sql' => $sql, 'params' => $params, 'types' => $types );
    }

    protected function formatQuery($sql)
    {
        $keywords   = array('select', 'insert', 'update', 'delete', 'where', 'from', 'limit', 'is', 'null', 'having', 'group by', 'order by', 'asc', 'desc');
        $regexp     = '/\b' . implode('\b|\b', $keywords) . '\b/i';

        $sql = preg_replace_callback($regexp, function($match){
            return strtoupper($match[0]);
        }, $sql);

        return $sql;
    }

    protected function replaceParams($sql, $params)
    {
        if (is_array($params)) {

            $pattern        = '/\?/';

            foreach ($params as $param) {

                $param  = $this->convertParam($param);
                $sql    = preg_replace($pattern, $param, $sql, 1);
            }
        }

        return $sql;
    }

    protected function convertParam($param)
    {
        if (is_object($param)) {

            if (!method_exists($param, '__toString')) {

                // Carbon Object
                if (is_a($param, 'DateTime'))
                    $param = $param->format('Y-m-d');
                else
                    throw new \Exception('Unstringable Object');
            }
        }

        return $param;
    }

    /**
     * From SQLLogger Doctrine Interface
     */
    public function stopQuery()
    {
        $endTime = microtime(true) - $this->start;

        /**
         * TODO Improve Connection name
         * For now, $this->connection->getDatabase() return the name of the Database
         */

        $this->registerQuery($this->query['sql'], $this->query['params'], $endTime, $this->connection->getDatabase());

        /**
         * This call is helpful to register queries in log file when the application is in debug/local mode
         */
        $this->logger->debug($this->query['sql']. " [Executed in " . $endTime . " secs.] ", array( 'params' => $this->query['params'], 'types' => $this->query['types'] ));
    }

    /**
     * Log the query into the internal store
     * @return array
     */
    public function registerQuery($query, $bindings, $time, $connection)
    {
        $this->queries[] = array(
            'query'      => $query,
            'bindings'   => $bindings,
            'time'       => $time,
            'connection' => $connection
        );
    }

    /**
     * Adds ran database queries to the request
     */
    public function resolve(Request $request)
    {
        $request->databaseQueries = array_merge($request->databaseQueries, $this->getDatabaseQueries());
        return $request;
    }

    /**
     * Returns an array of runnable queries and their durations from the internal array
     */
    protected function getDatabaseQueries()
    {
        $queries = array();
        foreach ($this->queries as $query)
            $queries[] = array(
                'query'      => $query['query'],
                'duration'   => $query['time'],
                'connection' => $query['connection']
            );

        return $queries;
    }
}

$sql, $params and $types contain the datas that I put in log file. This is an example:

[2014-12-10 08:48:36] local.DEBUG: INSERT INTO user_data (created_at, updated_at, name, surname, email, phone, active, deleted_at) VALUES (2014-12-10, 2014-12-10, Mattia, Bhoo, mymail@gmail.com, +39 3895896357, 1, ) [Executed in 0.00026988983154297 secs.] {"params":{"1":"2014-12-10 08:48:36","2":"2014-12-10 08:48:36","3":"Matteo","4":"Rossi","5":"mymail@gmail.com","6":"+39 3895896357","7":1,"8":null},"types":{"1":"datetime","2":"datetime","3":"string","4":"string","5":"string","6":"string","7":"boolean","8":"datetime"}} []

Do you think that this is sufficient to integrate Doctrine query in Clockwork?

[matiux]

I further improved DoctrineDataSource.php integrating the logger and removing DoctrineSqlLogger.php. So, now the only file that I added to your package is DoctrineDataSource.php, as well as some changes to the file ClockworkServiceProvider.php. I have updated the previous posts with the current code

[itsgoingd]

Hey, this is shaping up pretty nice! Few tweaks that need to be done before merging:

• I don't think we should change the service provider as this is not a feature of Laravel itself, we should document how to do it and leave it to the end user. (Note to myself, maybe we should make adding custom/non-default data sources in Laravel easier?)
• Some minor style issues, opening brace for the methods should be on a new line, protected instead of private, tabs for indentation.
• I would prefer for the replaceParams and convertParams methods to not use pass-by-reference, just return the final sql string instead.
• Uppercase sql keywords in the final query like the EloquentDataSource does for consistency. (https://github.com/itsgoingd/clockwork/blob/master/Clockwork/DataSource/EloquentDataSource.php#L80)

Integrating this with Clockwork is pretty simple, you can take EloquentDataSource as an example (https://github.com/itsgoingd/clockwork/blob/master/Clockwork/DataSource/EloquentDataSource.php). All you need to do is collect the query data in a form of arrays with the keys: 'query' => final runnable sql query, 'duration' => query duration, 'connection' => database connection name (could be 'default' if not known for now). Provide a "resolve" method on the data source that sets the database queries on the request, should look exactly like https://github.com/itsgoingd/clockwork/blob/master/Clockwork/DataSource/EloquentDataSource.php#L59.

Feel free to make a pull request out of this at any point, I can finish the tweaks myself, just don't want to take any credit from you. :) Thanks!

[matiux]

Ok, you can see the new version of DoctrineDataSource.php in the above post. It seems to be ok according to your specifics but let me know if something is not correct.
Last thing to do, fix the hard coded embedding of doctrine in Clockwork Service Provider. I had to insert this lines:

public function boot()
{
...
   $this->app->make('clockwork.doctrine');
...
}

public function register()
{
   ...
   $this->app->singleton('clockwork.doctrine', function($app)
   {
      return new DoctrineDataSource($app['Doctrine\ORM\EntityManager'], $app['log']);
   });
   ...
   $this->app->singleton('clockwork', function($app)
   {
      $clockwork = new Clockwork();
      ...
      $clockwork->addDataSource($app['clockwork.doctrine']);
      ...
   }
}

As you said, we should find a way to not put doctrine data source into the service provider...
For now I see the queries and timing in chrome with clockwork plugin :-)

P.s. sorry for my english, I hope it is understandable

[matiux]

I thought to create another Service Provider (ClockworkDoctrineServiceProvider.php) to don't modify the original ClockworkServiceProvider.php
So I had create this:

<?php namespace Clockwork\Support\Laravel;

use Clockwork\DataSource\DoctrineDataSource;
use Illuminate\Support\ServiceProvider;

class ClockworkDoctrineServiceProvider extends ServiceProvider
{
    public function boot()
    {
        $this->package('itsgoingd/clockwork', 'clockwork', __DIR__);
    }

    public function register()
    {

        $this->app->singleton('clockwork.doctrine', function($app)
        {
            return new DoctrineDataSource($app['Doctrine\ORM\EntityManager'], $app['log']);
        });

        $clockwork = $this->app->make('clockwork');

        $clockwork->addDataSource($this->app['clockwork.doctrine']);
    }

}

I added the provider in app.php, after the original clockwork SP
but unfortunately I get this error

ReflectionException (-1) 
Class mailer does not exist

[itsgoingd]

You should move the addDataSource call to the boot method, register should only be used to add new bindings to the container.

[matiux]

Ok, now doctrine service provider is ok. Just add 'Clockwork\Support\Laravel\ClockworkDoctrineServiceProvider', after 'Clockwork\Support\Laravel\ClockworkServiceProvider',

I will do a pull request as soon as possible

<?php namespace Clockwork\Support\Laravel;

use Clockwork\DataSource\DoctrineDataSource;
use Illuminate\Support\ServiceProvider;

class ClockworkDoctrineServiceProvider extends ServiceProvider
{
    public function boot()
    {
        $this->app->singleton('clockwork.doctrine', function($app)
        {
            return new DoctrineDataSource($app['Doctrine\ORM\EntityManager'], $app['log']);
        });

        $clockwork = $this->app['clockwork'];

        $clockwork->addDataSource($this->app['clockwork.doctrine']);

    }

    public function register()
    {

    }
}

[itsgoingd]

Included in Clockwork 1.7, I've removed the service provider in favour of adding the data source via configuration file.