Thoughts, ideas, random notes, ramblings...

Today we've reached another milestone on the way to FuelPHP v2.0: The Database package is finished, fully tested, and ready to be used.

This is a big step towards the 2.0 version of FuelPHP. Almost every application built with FuelPHP uses this part of the framework and our original query builder had an API, a lot of users love to use. Still, there were a good number of places where it had room for improvement.

For the new 2.0 design, the goal was to create a new Database Abstraction Layer which would have the same ease of use as the old one, improve and expand upon functionality, be composer based and be very well tested. We also needed better support for other RDBMS platforms than MySQL, which meant dealing with stuff like "getting an insert ID out of a Postgres database table"...

When we started with FuelPHP, we decided to use Kohana's database module. It was readily available, provided the flexibility we needed for the FuelPHP framework, was easy to use, and very powerful. Not having to develop it gave the framework a flying start. But good as it was, it wasn't without its drawbacks. Database packages are often complex pieces of software, and a developer requires in-depth knowledge of it's internal workings in order to maintain it properly and expand it's functionality. As it turned out, the code was very MySQL-centric, having it support other RDBMS platforms proved to be a very daunting task.

Improvements

For the new version 2.0 package we have kept all the key aspects which we liked in the current 1.x code, but also added a bunch of new features:

• Full PDO support
• Expression types
• Savepoints
• New fetch modes for select queries
• AND and OR support for JOIN's
• Better RDBMS platform support
• Better schema support by integrating the Doctrine DBAL
• Better query parameter handling
• A lot of new query condition possiblities

The new codebase is (at the time of writing nearly) 100% tested, which I think is a massive improvement as well!

Doctrine Integration

One of the things that might raise a few eyebrows is the dependency on Doctrine's DBAL package. This has been discussed in the team at length, and we decided that it fits perfectly. After all, FuelPHP is "based on the best idea's of other frameworks'!

What we do very well in FuelPHP is Query Building. From the very first FuelPHP release, we had a very intuitive Query Builder with a great API that perfoms really well. On the other hand, the DBUtil utility class only had a static API and was designed specifically for MySQL, so you could forget any schema support for other RDBMS platforms.

The Doctrine project on the other hand, specializes in dealing with databases and has amassed a wealth of knowledge about dealing with a large number of RDBMS platforms.

Query Building is something you do in your applications on a daily basis. We keep that under our own control, so we can make sure it is performs at its best. Schema management on the other hand doesn't need to be blazingly fast. By using the Doctrine DBAL package, we've been able to create the new database schema layer in days instead of weeks. To make it as easy for you to use as any other part of the FuelPHP framework, we've created a wrapper around the DBAL package which provides a similar API to schema building as our Query Builder does when it comes to, well, query building. As easy to use, and an improvement over the old DBUtil class by a mile...

Expression Types

Expressions have been a great way to inject your own custom SQL statements into the Query builder. However, in more complex situations they have proven to be difficult to use. For the new database layer we have decided to add additional functionality, which makes it easier to work with expressions:

• Command, for SQL functions
• Identifier, which automatically wraps values as identifiers
• Increment, which generates increment statements for update queries
• Parameter, for wrapping for query parameters
• Value, which allows you to properly quote literal values

These new Expression types will certainly be of help when you want to generate more complex queries.

New fetch modes

Before work started on the Database package, I created Cabinet. This was a personal learning project, with the goal to create a composer based database package. While working on it, it already picked up quite some interest from the community. Several people contributed to it's development, such as:

• Late value setting for fetched class objects
• Constructor arguments for fetched classes
• Fechting a row into an existing object

All these concepts have been added to the new FuelPHP 2.0 database package.

New query condition functionalities

A large part of building SQL queries is the selection process. SQL WHERE and HAVING clauses can become quite complex. All functionality that was present in version 1 is still available:

• operator guessing for "=", "IN", "IS" and "NOT IN"
• BETWEEN
• IN
• automatic NULL value conversions
• WHERE groups and group nesting

In the new database package, we added support for "NOT". This makes it possible to be even more expressive and produce highly customized queries. It allows you create SQL queries like:

• SELECT * FROM `users` WHERE `age` > 18 AND NOT `age` > 85

There is also better support for JOINS, adding complex AND and OR handling to JOIN clauses.

In all, I think I've made some massive improvements to the way FuelPHP will interact with databases. I'm very happy how it turned out. You can check out the repo tests to see how it works untill the docs are up. Please let me know what you think.