Tag: doctrine

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:

SELECT m.mac AS mac, vi.id AS viid, m.id AS id, 
    m.firstseen AS firstseen, m.lastseen AS lastseen,  
    c.id AS customerid, c.abbreviatedName AS customer,
    s.name AS switchname, 
    GROUP_CONCAT( sp.name ) AS switchport, 
    GROUP_CONCAT( DISTINCT ipv4.address ) AS ip4, 
    GROUP_CONCAT( DISTINCT ipv6.address ) AS ip6,
    COALESCE( o.organisation, 'Unknown' ) AS organisation

FROM Entities\\MACAddress m
    JOIN m.VirtualInterface vi
    JOIN vi.VlanInterfaces vli
    LEFT JOIN vli.IPv4Address ipv4
    LEFT JOIN vli.IPv6Address ipv6
    JOIN vi.Customer c
    LEFT JOIN vi.PhysicalInterfaces pi
    LEFT JOIN pi.SwitchPort sp
    LEFT JOIN sp.Switcher s
    LEFT JOIN Entities\\OUI o WITH SUBSTRING( m.mac, 1, 6 ) = o.oui

GROUP BY m.mac, vi.id, m.id, m.firstseen, m.lastseen, 
    c.id, c.abbreviatedName, s.name, o.organisation

ORDER BY c.abbreviatedName ASC

 

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

D2Cache::save( $key, $value );
D2Cache::fetch( $key );

$object = new Entities\Object;
D2EM::persist( $object );
D2EM::flush();

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.

Doctrine2 Service Provider for Laravel 4

I’ve just released a Laravel 4 package which contains a service provider to the Doctine2 entity manager and the Doctrine2 cache. These are made available via facades named D2EM and D2Cache respectively.

Currently it uses the XML schema method for defining entities but this can easily be augmented with the other methods. Also, ArrayCache and MemcacheCache are fully supported caching interfaces. Any other cache that requires no configuration is also supported but some trivial coding will be required for caching backends required configuration.

Combining this with TwigBridge, we have an excellent framework with Laravel 4!

MySQL 5.6 – Memcached / NoSQL Support and More

MySQL 5.6 has been released with some interesting new features and performance increases:

  • What’s New in MySQL 5.6
  • DBA and Developer Guide to MySQL 5.6
  • InnoDB Integration with memcached:MySQL 5.6 includes a NoSQL interface, using an integrated memcached daemon that can automatically store data and retrieve it from InnoDB tables, turning the MySQL server into a fast “key-value store” for single-row insert, update, or delete operations. You can still also access the same tables through SQL for convenience, complex queries, bulk operations, application compatibility, and other strengths of traditional database software.

    With this NoSQL interface, you use the familiar memcached API to speed up database operations, letting InnoDB handle memory caching using its buffer pool mechanism. Data modified through memcached operations such as ADD, SET, INCR are stored to disk, using the familiar InnoDB mechanisms such as change buffering, the doublewrite buffer, and crash recovery. The combination of memcached simplicity and InnoDB durability provides users with the best of both worlds.

  • Multi-threaded Slaves
  • Improved IPv6 Support – both in the bind to address option and the INET_ATON() function.
  • Replication improvements.

All in all, some nice new features. Especially the memcached integration.

That said, MariaDB seems to be making inroads on MySQL with some distributions considering a switch. Some interesting reading from that project includes:

ViMbAdmin 2.1 Released – POP3/IMAP Access Restrictions

We’ve just pushed a new release of ViMbAdmin – version 2.1. The main highlights are:

  • it’s now possible to restrict access to a mailbox via either IMAP, POP3 or both. See this page on the wiki for more information.
  • it’s our first release requiring a database migration. But it’s really really easy – see this page for those instructions.

As always, a live demo is available at: http://www.opensolutions.ie/vimbadmin/.

Look What We Just Made: TallyStick

[TallyStick Logo]
Are you like I once was with scribbles in your diary to track what you were doing for whom and for how long on any given day? When you manage to remember to scribble that is.

I was once a member of your club. And I had enough. So we built TallyStick. Necessity, as the saying goes, is the mother of invention.

At Open Solutions, we manage networks, build Internet infrastructure, consult and develop web applications for many customers. Tracking the time our engineers spent on different projects – especially those on the front line hopping from customer to customer – was becoming an issue.

We believe in web applications that are simple, beautiful and functional. And after an exhaustive search, we couldn’t find one to fit our needs… so we built it. And who better to build a time tracking application for IT and other consulting professionals than those same professionals!

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. We believe it’s simple, efficient and quick. And we hope you agree.

As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

Also, all users signing up during the beta period will receive a lifetime discount of 10% on that account!

We’ve Just Launched TallyStick

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

[TallyStick Logo]

Overt at Open Solutions, we manage networks, build Internet infrastructure, consult and develop web applications for many customers. Tracking the time our engineers spent on different projects – especially those on the front line hopping from customer to customer – was becoming an issue.

And we had enough. So we built TallyStick. Necessity, as the saying goes, is the mother of invention.

We believe in web applications that are simple, beautiful and functional. And after an exhaustive search, we couldn’t find one to fit our needs… so we built it. And who better to build a time tracking application for IT and other consulting professionals than those same professionals!

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. We believe it’s simple, efficient and quick. And we hope you agree.

As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

Also, all users signing up during the beta period will receive a lifetime discount of 10% on that account!