Faking It - Simulated Database Queries

by Alan Richmond

How often have you put off coding part of an application because the database wasn't ready?

If you answered even once, you delayed a project — which means you delayed your client. Maybe it was only a couple of days, maybe it was a couple of weeks. In either case, it took you a little longer to finish and your client had to wait. And we all know how patiently clients wait.

But what if you don't wait for your DBA? What if you just... pretend the database is done... and start coding? You could get finished on schedule. Or maybe even ahead of schedule. Now, if the application would just play along, if it would just fake it while you code...

Playing Along

Query simulations (QuerySims) were conceived by Hal Helms to remove the design and population of a database from the critical path of application development. Using QuerySims a programmer may proceed with writing and testing data display code while the DBA is designing the database, saving a significant amount of time.

Alternatively, the database may be designed after the application is finished. I've worked on more than one project were columns and even whole tables were never even used. It sounds counter intuitive to the way most of us learned to develop applications but by waiting we know exactly what columns and tables are needed and how best to group them for performance on the database end.

When the database is complete, simply change the connect string to point to the real database and replace the QuerySim text with an SQL select statement that returns the appropriate columns. There is no need to modify your display code, because as far it could tell, it was already using a result set from the database.

QuerySims are especially useful when following the FLiP methodology and using the Fusebox framework for application development, but you don't have to be using FLiP or Fusebox to use QuerySims!

Database Abstraction

Keeping your code portable from one database to another will increase its reusability tremendously, but this is virtually impossible using PHP's native database functions. The intelligent thing to do is to use a database abstraction layer that can interface with any database using common methods. Even more intelligent is to use one that is already familiar to other developers, so when your code needs to be updated by your successor, he or she doesn't need to learn a new API in addition to the ins and outs of your application. (Remember this works both ways, things will be easier for you if the situation is reversed.)

The original QuerySim was a ColdFusion custom tag that took advantage of ColdFusion's built in database abstraction layer and functions for populating a real query-scoped variable with data. Unfortunately, PHP doesn't have a built in abstraction layer we can populate with data. Since each database has its own set of functions for connecting, querying and manipulating data, we need a common interface for a clean switch from QuerySim to a live database.

There are several very good database abstraction layers available, PEAR DB and ADODB are two of the more popular ones. This version of QuerySim is implemented as a database driver for PEAR DB.

PEAR DB

DB is a class package available from the PHP Extension and Add-on Repository (PEAR) project. Sean Cazzell's article will give you a quick synopsis of what PEAR is trying to achieve.

If you would like to follow along with the tutorial, you may download the current stable DB package here. But one of PEAR's strengths is its close association with the PHP Group; PEAR and the DB package are included with the default PHP install, so you probably already have it!