[school-discuss] test programming before you begin database design :-)

[mike eschman <meschman@engima.com>]

Faking It - Simulated Database Queries @ 
http://www.phpbuilder.com/columns/richmond20020827.php

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!