Doctrine2 with GROUP_CONCAT and non-related JOIN

Doctrine2 ORM is a fantastic and powerful object relational mapper (ORM) for PHP. We use it for IXP Manager to great effect and we only support MySQL so our hands are not tied to pure Doctrine2 DQL supported functions. We also use the excellent Laravel Doctrine project with the Berberlei extensions.

Sometimes time is against you as a developer and the documentation (and StackOverflow!) lacks the obvious solutions you need and you end up solving what could be a single elegant query very inefficiently in code with iterative database queries. Yuck. 

I spent a bit of time last night trying to unravel one very bad example of this where the solution would require DQL that could:

  1. group / concatenate multiple column results from a one-to-many relationship;
  2. join a table without a relationship;
  3. ensure the joining of the table without the relationship would not exclude results where the joint table had no matches;
  4. provide a default value for (3).

Each of these was solved as follows:

  1. via MySQL’s GROUP_CONCAT() aggregator. The specific example here is that when a MAC address associated with a virtual interface can be visible in multiple switch ports. We want to present the switch ports to the user and GROUP_CONCAT() allows us to aggregate these as a comma separated concatenated string (e.g. "Ethernet1,Ethernet8,Ethernet9").
  2. Normally with Doctrine2, all relationships would be well-defined with foreign keys. This is not always practical and sometimes we need to join tables on the result of some equality test. We can do this using a DQL construct such as: JOIN Entities\OUI o WITH SUBSTRING( m.mac, 1, 6 ) = o.oui.
  3. This is as simple as ensuring you LEFT JOIN.
  4. The COALESCE() function is used for this: COALESCE( o.organisation, 'Unknown' ) AS organisation.

We have not yet pushed the updated code into IXP Manager mainline but the above referenced function / code is not replaced with the DQL query:

 

Doctrine2 Provider for Laravel 5 with Authentication Support

About a year ago, I released a Doctrine2 provider for Laravel 4. We’ve just updated this as a new package for Laravel 5.  Full details can be seen in the project’s README.md.

This package creates four bindings into Laravel’s IoC:

  • Doctrine\ORM\EntityManagerInterface (which is an instance of Doctrine\ORM\EntityManager), also available via the D2EM facade;
  • Doctrine\Common\Cache\Cache (which is an instance of the appropriate cache provider), also available via the D2Cache facade;
  • Doctrine\ORM\Mapping\ClassMetadataFactory (used in this package by the console generator commands); and
  • the Doctrine2Bridge\Support\Repository helper object which used by the D2R facade to instantiate repositories such as D2R::r( 'User' ).

An example of using the D2Cache and D2EM

It includes support for Laravel’s authentication system by way of a Doctrine2 based user provider with some example classes. Simply put – this allows a Doctrine2 database table storing users’ usernames and passwords to be used as the backend for Laravel 5 authentication.

Full documentation of the authentication with examples can be found here.

Available on Packagist and forkable on GitHub.

IXP Manager – Planning for v4

A lot has changed in the 3 to 5 years that the decision was made to use certain libraries / technologies / methods on IXP Manager.

In previous major version changes we made some serious architecture changes in one sweep. For example v2 -> v3 saw the complete migration from Doctrine ORM v1 to v2 (which was a change from the Active Record pattern to the Data Mapper pattern).

Today, IXP Manager is a very large project and to do such a sweeping migration in one go would stifle development, break something that isn’t actually broken and take a lot of time.

But, sticking with older technologies and libraries has negative effects also. It creates developer apathy (for which I can personally vouch for). It also provides a major stumbling block for bringing on new developers and contributors (who wants to learn Zend Framework 1 now which has been EOL’d for sometime?).

So, our plan for v4 is to bring in new technologies without throwing away or rewriting everything we have.

IXP Manager is a MVC application that currently uses Doctrine2 as the Model, Smarty as the View and ZF1 as the Controller. Doctrine2 is still current and won’t be changing.

Smarty will remain as the view engine for current / unmigrated functionality. But Smarty is… oh my God… soooooo bad. v4 will default to Twig which is more modern and far better structured from a programming point of view. Coupled with the new framework, it will also allow for a nicer means of skinning. For the interested, Twig has some very nice features including layouts, macros and also some nice security features.

ZF1 has served us well but it’s been EOL’d and is now quite outdated. The new hotness in PHP is Laravel, which I’ve been using to great effect for a while now. Laravel show cases some of the new and best functionality of PHP and using very modern techniques (such as IoC).

But more importantly, Laravel will let us do things in a much different and much more flexible manner for the IXPs using IXP Manager. Some of these include:

  •  Job queues: built-in and simple (to use) support for job queues via Beanstalkd and others. Queuing jobs will provide functionality that we at INEX have been looking for (and it’s also an FAQ from other IXPs) -> reconfiguring services on demand (or, at least quicker than a twice daily cronjob).

Put this together with:

  • Events: Laravel allows us to trigger events and subscribe to them.

A key example of queue and event functionality would be that a change to a VLAN interface (such as checking the route server client box) would trigger a vlan interface changed event. One subscriber to this event would be the route server configuration manager. Based on the VLAN change, this event handler can then queue events. The route servers themselves would monitor these queues and rebuild / reconfigure the route servers appropriately on demand.

Similar handlers for route collectors, DNS ARPA changes, etc. can offer much more real time control of all the services at an IXP.

IoC decouples logic from the controller. What this means is that IXPs who want to do things differently than INEX (let’s say use Cacti instead of MRTG as an example), can swap out MRTG with Cacti with one line of code (that’s assuming we write contracts – interfaces – for such handlers and a Cacti version is coded of course!). But that’s the kind of power and flexibility we’re looking to bring in.

Other features Laravel provides includes:

  • Much improved unit testing on controller actions. Right now, we spin up Apache and MySQL to test controller actions. This is no longer required with Laravel making tests easier to write, more robust and more focused with built in support for mock objects.
  • A much nicer and more structured way of creating command line interfaces rather than the quite clunky way we have of doing it currently.
  • A much more natural way to develop REST API endpoints with json:api compatible responses.

And that leads us to the front end. Right now, the front end and the back end are tightly coupled. During the development lifetime of v4, we want to move more towards an API is Everything back end with a decoupled front end.

This separation will again aid unit testing providing a more reliable and robust IXP Manager. It will allow other IXPs to create their own front end on member facing portals or, even, move to IXP Manager as their back end system but retaining investment of current member portals by adding new features from IXP Manager through API endpoints. It will also allow existing systems in IXPs to integrate with IXP Manager to provision services and ports for example.

One of the bigger tests of this plan will be the (long awaited and badly needed) revamp of the member facing area. We’re currently planning the UI / UX of this to deliver key information to members in the best way possible. This will include Bootstrap v3 which is fluid from the ground up so mobile browsers to wide screen browsers should be supported naturally.

During the early stages of v4, we’ll create the API endpoints necessary to support the member portal functions and then create a front end on that using Ember.js.

Other changes in v4 will include:

  • A switch from package management via Git sub-modules to composer and Packagist as is current standard practice.
  • Introduction of Bower for front end asset management.
  • And we’ll need a task runner for pulling everything together – for that we’ll use Grunt (although that’ll mostly be a development / release prep tool rather than an end user requirement).

So, that’s what we’re looking at! It won’t happen overnight but we’ll continue our policy of release early, release often and we’ll update the documentation and provide complete upgrade instructions at the appropriate times. Some of the above is also subject to change depending on practical experience / issues as we move towards it.

Comments, ideas, etc. are all welcome.

Virtual Mail with Ubuntu, Postfix, Dovecot and ViMbAdmin

As part of pushing our new release of ViMbAdmin, I wrote up a mini how-to for setting up a virtual email system on Ubuntu where the components are:

  • Postfix as the SMTP engine;
  • Dovecot for IMAP. POP3, Sieve and LMTP;
  • ViMbAdmin as the domain / mailbox / alias management system via web interface.

It supports a number of features including mailbox archival and deletion, quota support and display of mailbox sizes (as well as per domain totals).

Find the how-to at:

Doctrine2 Provider for Laravel 4 Authentication

I’ve just added to the Doctrine2 service provider for Laravel by adding a UserProvider allowing Doctrine2Bridge to provide a driver for Laravel’s authentication system.

Simply put – this allows a Doctrine2 database table stroing users’ usernames and passwords to be used as the backend for Laravel 4 authentication.

Full documentation and examples can be found here. Available on Packagist and forkable on GitHub.