Aug 5, 2008

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!