DBWhere clause builder

One of the things that's always bugged me about designing good cross-platform SQL queries is the difficulty in constructing complicated 'where' clauses.  Things like nested logic (AND (a = 2 OR b = 3)), column comparisons (whenadded != lastupdated), and function calls (whenadded > dateAdd(d . 10, getDate())) are always practically impossible without resorting to raw SQL.  That raw SQL is often not cross platform.

With that in mind, I've assembled a small library called DBWhere that generates where clauses in a fairly sane but easily extensible fashion.  I've included 2 differrent implementations, one that escapes for MySQL, and another that escapes for TSql (MSSQL).  I've also included a test script that shows both implementations in action.

Examples

For these examples, I'll assume we're using MySQL and have the following function:

function GetWhere($keyOrWhere, $value=null, $logic='=', $type=DBWhere::VALUE_IS_VALUE) {
    return new MySqlWhere($keyOrWhere, $value, $logic, $type);
}

The library starts with the base class DBWhere.  You call the constructor with the parameters for the first clause of your where like so:

$where = GetWhere('status', 2)->End();
WHERE `status` = '2

Parameters 1 and 2 respectively represent the left and right sides of a where clause, with the assumed logic being '='.

You can then add to the command by chaining the functions Also and Either (for And and Or, respectively.  Darn you PHP reserved words!):

$where = GetWhere('status', 2)->Also('timestamp', '1/1/2000', '<')->End();
WHERE `status` = '2' AND `timestamp` < '1/1/2000'

Here we use the 3rd parameter to assign some custom logic (note that we could have done this in the constructor as well).

We can also do WHERE IN by passing array values:

$where = GetWhere('status', (1,2))->End();
WHERE `status` IN ('1', '2')

...or WHERE NOT IN by passing negative logic:

$where = GetWhere('status', (1,2), '!=')->End();
WHERE `status` NOT IN ('1', '2')

We can also nest logic by nesting DBWhere objects:

$where = GetWhere('status', 2)
    ->Also(
        GetWhere('lastname', 'smith')
            ->Either('firstname', 'George')
    )->End();
WHERE `status` = '2' AND (`lastname` = 'smith' OR `firstname` = 'George')

Finally, we can compare columns by changing the constant we use for valueType:

$where = GetWhere('whenadded', 'lastupdated, '!=', DBWhere::VALUE_IS_COLUMN)->End();
WHERE `whenadded` != `lastupdated`

Best Practices

As illustrated in the examples, it makes the most sense to use this class when you have a function somewhere that returns the type of DBWhere object that you need.  For example, in the Cebrum library, I have a Connection object that knows what type of database it's connected to.  The Connection obejct has the function GetWhere that will return the proper kind of DBWhere object, so I can keep all my database configuration code in one place.

Also, notice that there's a $valueType called DBWhere::VALUE_IS_SPECIAL.  When you use this type, the value will not be escaped!  Sometimes this is necessary, as when you want to use built-in SQL functions.  It shold be used carefully and sparingly at all times.

Future Direction

This was a quick one-evening project, and so even though I have the basics in place, I already know many things that need to be done in order to make this a fully functional tool.  Here's a few:

1. Use better escape functions (right now I'm using regex replacement, which isn't terribly secure).
2. Handle dates intelligently
3. Have some basic intelligence for cross-platform function use (probably outside the scope of this project)
4. Handle subselects (thanks, David)

If you have any other thoughts, questions, comments, or concerns, let me know!

Google application server - it's about time

When Amazon began reselling their backbone as web services, another barrier to the internet collapsed. Much like the introduction of the LAMP stack heralded the arrival of commoditized web applications, Amazon's service heralded the commoditization of large-scale application deployment. A budding web company no longer needs to choose between initial infrastructure investments (in the form of large-iron servers) or robust prayer (that their nascent website can survive a digg, crunch, or slashdotting).

The problem...or at least my problem...with Amazon's services was trust. As the first large-scale cloud computing service, Amazon was destined to experience growing pains of some sort. Companies that bought into their technologies had to trust that these pains would not prove deliberating, and had little choice but to sit back and hope during recent outages.

Today, Google has launched their own version of Amazon's cloud computing technologies. While currently more limited in scope, Google's histoy suggests that they will eventually be brought up to speed with Amazons offerings, though likely in a slightly different form. Once some form of parity in these services is achieved, purveyers of cloud computing techology will finally have the one missing peice of the web services puzzle.

Redundant services.

Once platform developers are comfortable in the niceties of both environments, I anticipate that most smart startups will begin to hedge their infrastructure bets and learn ways to load-balance their offerings across both of these platforms. As each service is pay-as-you-go, the ongoing costs will not be drastically different than the consumption of either service separately, and the ability to leverage the infrastructure of two web giants should outweigh any additional development overhead.

This can only be a good thing for everone involved. Google has found yet another revenue stream that leverages its massive hardware backbone, developers gain the power of choice, and even Amazon should benefit from the growth caused by the expanding market. All in all, it is a good day for the internet.

Layman's analogy for version control

One of my clients recently overhauled their talent pool. Their staff went from two freelancers with very general development skills to one generalist (me), two php developers, and a CSS/HTML developer. System administration was understandably not in the three new developers' bags of tricks, and so they needed to be given development environments. As the only remaining person with system chops (and what mediocre chops they are), it fell to me to set these up. The project manager and I decided to set up completely isolated instances of the site and database, all hooked together by our subversion system.

Setup took longer than expected. Between minor issues at the host, incompatability between our code and library versions on the new server, and inevitable communications delays, we lost the better part of the week to our setup. This left several of the clients' staff wondering at why such elaborate preparations were necessary. Why couldn't all the developers just work out of a single environment and database?

This confusion left me at a bit of a loss. I remain confident that we took the right steps in getting our new talent up and running on the project, but I had no idea how to communicate the need to a non-technical individual. Most analogies I worked through fell apart in short order. The building-a-house analogy? Doesn't work - you generally don't have one contractor building a roof while the other works on the foundation. The writing-a-contract analogy? No parallellism to reference.

Finally, I started picking apart some variations on the writing-a-story analogy and came up with one that seems to fit. Let's assume several authors decided to team up and write a character-driven, multi-chapter story. Each author would write one chapter that would feature one main character, and that author would be the authority on that character. However, all the characters will by necessity interact at various points during the story.

When writing begins, each author can work effectively in isolation within his or her own chapter. The character develops, and encounters with the other characters are documented. As these interactions unfold, however, they may begin to start interfering with other parts of the story. What if in Chapter 1, Jonny said "Where will we go next?", while in Chapter 3's telling of the same encounter, he says "Let's go to the cave!"? Johnny's author would need to go into someone else's chapter and fix the quotation.

Now you're faced with the prospect of more than one person editing the same document at the same time. Most people can understand that significant issues can occur here - for instance, if two authors are editing the same chapter at the same time, the chapter will ultimatley contain only the changes of whoever saves last. What you need is a mechanism by which each author can edit a chapter and have their changes merged together seamlessly.

That's where version control comes in. Version control takes the different parts of the story and gives the authors the freedom to make changes wherever necessary without impacting one another's work. When you extrapolate the analogy into real development terms, its usefulness becomes amplified by the fact that it enables editing by anyone, anywhere with an internet connection.

There's certainly some gaps in this description, but for the most part I think it holds up. If nothing else, it serves as an interesting intellectual exercise.

IE8: Coddling the few to spite the many

Microsoft has announced that IE8 will include yet another non-standard hack in order to get their browsers to be backwards compatible.  While their intentions are good, they've still got the wrong idea.

Microsoft's approach to browser creation is a good one, considering their dominant position in the browser market.  "Don't break the web".  Make sure new versions of IE don't cause terrible breakages in existing content, since each breaking change they introduce can cost the industry millions, if not billions of dollars.  Their difficulty lies in improving the browser without introducing such breakages.  Their solution has always been to add some kind of version-identification functionality that developers can use to customize their sites for each iteration of IE.  For example:

• IE5: Conditional comments
• IE6: Strict doctype

IE8 will continue this trend, adding a new meta tag that will act as a browser render mode identifier.

The issue is that Microsoft wants to prevent companies with IE6/7 specific pages from having to redesign for IE8. However, even though "Don't break the web" is their credo, they've never been able to follow through on it completely. IE6 broke IE 5.5 sites, IE7 broke IE6 sites, and no matter what they do, IE8 will break IE7 sites. People are going to expect this to occur and will be testing and tweaking their sites despite Microsoft's best intentions.

With that in mind, why not make their tweaks be opt-ins to the IE6/7 model as opposed to opt-outs? Again, people are going to have to fix their sites. If history is any indicator, some changes will be needed regardless of Microsoft's compatibility intentions. If adding the tag during this process will get developers most of the way back to a working site, they'll add it, make their changes, and move on.

For for both Microsoft and the web in general this would be a big win, as it would minimize the need for the hack-per-version development model IE currently follows. If they're not screwing around this time and IE8's new super compatibility mode really does make it work like other browsers, then they'll be able to focus on making IE9 an even better browser and not just a standards-fixing exercise.

ShareGames in beta!

Our game sharing application, ShareGames, goes into beta today!  We've put ShareGames together as a way to generate a conversation between friends about casual games.    We're hoping to expose facebook users to some of the great casual and independant games on the market by giving them a fun way to show off what they're playing to their friends.  Users can search for games, download and play free trials, then brag about their exploits afterwards.   Better yet, their friends can see what they've been bragging about and brag right back.

So come check out the beta!  We'd love to have you and to hear your feedback.  Help us make ShareGames the best game application on Facebook!

Website update

A new version of the website went live today.  We\'ve incorporated our Cebrum framework this time around, so not only are we eating our own dogfood, we\'re in a better position to add more features to the site over time!  What\'s more, we should be able to get more content available on the site for our clients and partners.

Silverlight - Is it a winner?

It certainly is a geek's dream - handwritable layout code, stylesheets, full access to the .NET libraries, integrated 3D graphics. The closed nature of the platform is still disconcerting, but this is a case where you can't blame Microsoft. Their only real competitor in the space is Flash, which is still proprietary itself. I'll be watching this technology closely!

SMS site tools updated

More progress has been made on the administration tools. Two new features were added today:

1. The item picker is updated in real time when a new item is created
2. Resized images are cleared out when they're replaced in image columns

Neither update is terribly impressive, but both make the default admin tools even more usable out of the box, which is critical.