Tracking Multiple Connections with PHP Debug Bar and Atlas ORM

My library AtlasOrm.DebugBar.Bridge now supports multiple connections.

On Friday I talked about a new library I created that helps integrate Atlas.ORM with PHP Debug Bar.

Hari K T replied asking about using multiple connections. For instance, if you use Atlas with a default master database for writes and multiple read-only slave databases. My library AtlasOrm.DebugBar.Bridge does not support this is any way out of the box.

I spent the weekend thinking about how to solve this.

First, I asked “How would I do this right now with what’s available?” and the answer was:

I wasn’t happy with this solution. Too complicated and too easy to mess up.

It was difficult, because of Atlas.ORM inheriting the Aura.SQL requirement that adding read and write connections require a callable that returns the connection. This is done because if we want to register multiple possible connections, we don’t want to connect to all of them right away. We only want to connect when we’re using the connection.

Finally, yesterday morning I came up with a possible solution.

If I created a new class ConnectionFactory that could be configured identically as a connection and be passed into AtlasContainer as a callable (I implement the __invoke method). It could then also be aware of the AtlasOrmCollector which would have a new method addConnectionFactory that registered itself with the ConnectionFactory object.

This way, I could easily configure new connections (via ConnectionFactory), add them to the AtlasContainer and AtlasOrmCollector and they would wire themselves up. The TraceablePDO connection would only get added to the collector when it was invoked which prevented me from invoking all of the connections up front whether they were used or not.

The new method of adding multiple connections is then:

I think it looks a lot cleaner and less prone to errors.

This code is currently sitting as a PR to allow any interested parties to comment before it gets merged. Introducing ConnectionFactory. Feel free to review and comment.

Collecting Data from Atlas ORM with PHP Debug Bar

My new library AtlasOrm.DebugBar.Bridge lets you collect data from Atlas.ORM using PHP Debug Bar.

In my last article, I talked about how I found an N+1 bug in Atlas ORM.

I had mentioned how it took a little work to get PHP Debug Bar configured with Atlas but didn’t really explain why it was difficult, or how I got them working together.

At first, it seemed like it would be easy. Debug Bar comes with a PDOCollector and Atlas is based on PDO. In Atlas\Orm\AtlasContainer it creates a new Aura\Sql\ConnectionLocator then creates and adds an Aura\Sql\ExtendedPdo connection to it.

PHP Debug Bar requires you wrap your PDO connection in DebugBar\DataCollector\PDO\TraceablePDO which extends PDO.

Aura\Sql\ExtendedPdo wraps a PDO connection and extends PDO however DebugBar\DataCollector\PDO\TraceablePDO is not a Aura\Sql\ExtendedPdo object.

That means that I need to wrap PDO in DebugBar\DataCollector\PDO\TraceablePDO which is then wrapped in Aura\Sql\ExtendedPdo.

There is no easy way of doing that without replacing some code.

The first step is to create a new ExtendedPdo class that extends Aura\Sql\ExtendedPdo that overrides connect() which instantiates the PDO connection. We want to wrap the newly created connection in DebugBar\DataCollector\PDO\TraceablePDO.

Next, we create a new AtlasContainer class that extends Atlas\Orm\AtlasContainer that overrides setConnectionLocator to instantiates my new ExtendedPdo instead of Aura\Sql\ExtendedPdo.

Finally, we can use the DebugBar\DataCollector\PDO\PDOCollector which requires a DebugBar\DataCollector\PDO\TraceablePDO connection. We can get that out of our AtlasContainer like this:

To make this step easier I created AtlasOrmCollector which extends DebugBar\DataCollector\PDO\PDOCollector but expects my AtlasContainer instead of a PDO connection in the constructor. This way I can pull out the DebugBar\DataCollector\PDO\TraceablePDO connection and pass it to the parent constructor.

I’ve packaged all of this up into the library AtlasOrm.DebugBar.Bridge which is also available on packagist as cadre/atlasorm-debugbar-bridge.

You can now integrate PHP Debug Bar into your applications that use Atlas ORM as simply as this:

Complex Database Relationships with AtlasORM

How I used Atlas.ORM to perform complex queries in my Pen & Paper side project.

In my side project I’m working with a very well normalized database with many relationships.

One of the most complex pages is a Creator where it shows info about the creator and all of the works that they have contributed to.

creatordiagram

This is the database structure for this page. On the left, we have a Creator which has a one-to-many relationship to seven different join tables. Each of these tables represents this creators credit on that particular type of work. So if the creator worked on an RPG book there would be a RpgBookCreator that shows what credit that creator had in a particular RPG book. A credit could be something like Author or Illustrator.

When I first started working on this project I was doing plain SQL with PDO. Quickly I realized that for all the pages I was building I’d be writing a whole lot of queries and that didn’t sound particularly fun.

Then I tried porting part of this page over to Doctrine 2 ORM and that was a huge mess. All of the mapping files and entity objects was taking a long time to get right. Then, once I had it configured for Creator, RpgBookCreator, RpgBook, Credit, GameLine and Publisher (which is only 1/7 of the structure) I ran the query and the page just sat there spinning. I was not familiar enough with Doctrine to make it perform the way I wanted it to.

It was suffering from the “N+1 Problem”:

This problem occurs when the code needs to load the children of a parent-child relationship (the “many” in the “one-to-many”). Most ORMs have lazy-loading enabled by default, so queries are issued for the parent record, and then one query for EACH child record. As you can expect, doing N+1 queries instead of a single query will flood your database with queries, which is something we can and should avoid.

Well, it just so happens Paul M. Jones the creator of Radar wrote the book “Solving The N+1 Problem in PHP”. Not only that, he is the creator of AtlasORM described as:

Atlas is a data mapper implementation for your persistence model (not your domain model).

If anyone would have an ORM that could handle my data structure, it’s Paul.

It didn’t take very long to get my site working with Atlas. There is a very useful CLI that will analyze your database and generate the scaffolding needed for Atlas to work.

The two types of classes required for atlas are Tables and Mappers. The Table classes (ex: CreatorTable) described things like the column names, the primary key, and the default values.

The Mapper describes the relationships between tables. By default, the method setRelated is empty because the CLI doesn’t create them. However, it was very easy for me to write a script that did automatically create these relationships because I was using a consistent naming scheme.

Here is the final CreatorMapper which describes all of its one-to-many relationships.

Finally, in my CreatorsAtlasRepository class I query the database, specifying all of the relationships I’d like to include.

It was pretty fast. I picked a creator with many credits across different types of work (Monte Cook) and it loaded in about 1.8 seconds. However, I wanted to make sure Atlas was querying efficiently.

In an ideal situation, it would query each table once or join some tables and have less than 20 queries. I wasn’t expecting this.

In a more expected situation, it would query the tables close to the root once and some of the edge tables (publisher, game_line) might get queries a few times for different batches of data. Probably around 30-40 queries depending on the creator.

I found a project called PHP Debug Bar that could hook into different systems (including PDO) and show stats for each page.

It took a little work to get that configured with Atlas, but I got it working and went to see how many queries were made to render the Monte Cook page.

I was very surprised to see a total of 484 queries. That was an order of magnitude larger than I was expecting. Clearly, there was still an N+1 problem with Atlas. I dug through the query log and determined that the issue was with the edge relationships. There were many many queries for single publishers and game lines.

I submitted a PR explaining my issue. Today Paul commented that he had a patch in a development branch and wanted me to test it.

I’m happy to report that the same Monte Cook page now loads with only 32 queries which is right where I was expecting it to be. The page is a little faster at 1.5 seconds which is good enough for development. Before the site goes live I’ll implement a caching layer.

This patch should get merged into the main branch soon. This is really a fabulous library that I’m planning on using in all of my new projects.

LazyArray Officially Part of Aura.Di

My LazyArray feature has been officially added to Aura.DI.

In my prior posts about integrating Symfony Forms with Radar, I created a helper class called LazyArray.

It was designed so I could pass an array of lazily instantiated objects into a setter like Twigs setExtensions method.

Paul liked my implementation and asked me to submit a PR.

Today it was merged and included in Aura.Di 3.2.0 which makes me very excited.

Symfony Forms and Radar – Part 2

I refactor my code from Part One and integrate Aura.Filter.

In Part 1 I talked about the difficulties in getting Symfony Forms to play nicely with Aura.Di and Radars ADR model.

Today I’m picking up where I left off. My goal is to figure out what I need to do differently to achieve the clean separation I desire.

All of my issues with Aura.Di have been resolved and my LazyArray class is currently being reviewed for inclusion in Aura.Di.

Removing Validation

The first thing I did was remove Symfony Validator from the form (and my app). I did this for several reasons.

First, my initial attempts to validate independently from the Form showed that Validator is fairly complex. There also seems to be very little documentation for using outside of Symfony. The few examples I found didn’t seem like the sort of validation I’d like to use in my projects.

Second, the point of this series is to integrate Symfony Forms, not Validator. No sense going down another rabbit hole. If you’re interested in me figuring out Symfony Validator, please contact me and let me know.

Isolating Form to the Responder

The second thing I did was move all use of the Form object to the Responder. Since I’d like to handle the Request manually and validate separately in the domain, the form can be moved to the Responder.

You can follow along with my commits here.

How I’m handling things in Input and the Domain right now are just placeholders until I get the form figured out.

I’m still calling handleRequest at this point because I haven’t figured out how to pass in the payload from the domain. I’ll do that next.

Handling Request from Payload

This was far easier than I expected. Since I’m not doing anything crazy with file uploads or anything I was simply able to submit the form directly.

Validating and Showing Validation Errors

Now that the basics are working, I can implement validation and display the validation errors with the form.

I decided to use Aura.Filter for my validation. It integrates easily with Aura.Di and fits my idea of a clean validation library.

The first step is to create a filter. This is easily done by extending Aura\Filter\SubjectFilter and overriding the init method.

Next, since I’ll need this filter in my post domain, I convert my closure into an invokable class.

I then configure the container to instantiate this filter and pass it into RegistrationPost.

Next, I apply this filter on the data I get from RegistrationInput.

Finally, I consume the payload in RegistrationResponder going through the failure messages and adding FormError objects to the correct elements.

You can see the result of this in the 2.x branch of the project repo.

Conclusion

This exercise left me with a greater understanding of Symfony Forms and Aura.Filter. I think the solution is pretty good. If I was using this in production, I’d probably refactor some of this to be more reusable, especially if I plan on defining a lot of forms. I probably wouldn’t use arrays as the data transfer mechanism. I’d also probably create a factory that takes a generic definition and generates the form, validation and entity objects that I’d be using.

I’m very interested in hearing from you. I will be creating new content over the coming weeks and I want to customize the content for YOU and help you overcome any obstacles you might be experiencing. Send me a message!