Effectively Implementing Sql Phrasebooks

This article describes effective and portable implementations of

SQL Phrasebooks, using formatted constants as opposed to the currently accepted methods

which call for XML Configuration files and dynamic general SQL Query generation

routines. Formatting is a very old and standardized method of making

strings dynamic, and although there are currently numerous implementations

that vary depending on language, the formatted strings we will use are completely

portable and standardized among all popular languages today.

Why not use XML? Well, there is an argument (taken from

title="opens in new window">The Phrasebook Design Pattern)

that mixing languages is bad. My feeling is that mixing SQL and

XML, with library specific scalars intended to be parsed by the current

application, is hardly better than mixing SQL with Perl, PHP, or any other programming

language. So our response is to isolate the SQL. This keeps us from having

a statement for any one table more than once, ever. We do this

by only using completely constant values where we don't need variations of the

same query, and otherwise using strict prototyping. This gives three distinct

advantages when writing Phrasebooks as libraries.

The first is the obvious

speed advantage, resulting from not having to parse the XML file. The second is

that string formatting is extremely standard among today's modern languages, which

means our formatted queries are quite portable, and porting the Phrasebooks

themselves to different languages can be done with minimal effort.

The third advantage is that it helps reduce repetition.

Reducing repetition is the main focus behind Phrasebook design. Repeating

yourself in the code is quite possibly the worst thing you can do, especially

when your repetition is large chunks of an alternate language within your

program. Why worry about repetition? Well, repetition makes maintaining code

quite difficult, especially with SQL, since if you change your database, you

have to remember to search all your programs that access the specific tables

you have changed, for whatever unanticipated reason you changed them, and

change the queries contained in them. If you have all of your queries in a single

location, and even then only once, this makes this process much less painful.

When I first began discussing this in a public forum, the first question I

received was, "Well, then won't we need a bunch of extra sprintfs? I think that

is kind of annoying." Of course, my response was "Yes, but you don't have to see

them." The way we get around this is by writing our own extremely mild wrappers

to whichever DBI we are using. The wrappers are quite simple; for instance,

in PHP we simply make a class to wrap around the long, ugly mysql_* functions

that PHP's mysql DBI provides:

/* Simple DBI Wrapper */
class EasyMySQL {
var $Result;
var $Link;
function EasyMySQL ($User,$Pass,$Data,$Host = "localhost") {
    $this->Link = mysql_connect($Host,$User,$Pass);
    mysql_select_db($Data,$this->Link);
}
function rqueryf () {
    $args = func_get_args();
    return mysql_fetch_object(mysql_query
        ( call_user_func_array( 'sprintf', $args ), $this->Link));
}
function queryf () {
    $args = func_get_args();
    return mysql_query(call_user_func_array( 'sprintf', $args ));
}
function rquery ($Query) {
    return mysql_fetch_object(mysql_query($Query, $this->Link));
}
function query ($Query) {
    return $this->Result = mysql_query($Query, $this->Link);
}
function next ($Handle="") {
    return mysql_fetch_object($Handle ? $Handle : $this->Result);
}
/* Destruction Function [ php has no destructor, call explicitly ] */
function close () {
    mysql_close($this->Link);
    return NULL;
}
}

This specific class may leave you with a few questions. It provides more

than just a simple wrapper around sprintf and the query at hand. It also provides

slightly enhanced syntax for iterations, formatted and nonformatted

functions, and an internal Link and Result reference. I used these because in

the particularprogram I wrote this class for, my module API left the global

mysql link available to extensions, without interference. Why did we fetch our

rows as objects, instead of arrays or associative arrays? That's a matter of

personal preference. You can use whichever methods you want.

So, when does the Phrasebook come in? It comes in now. Personally, my

favorite way to design Phrasebooks in PHP is as a class that inherits my DBI

wrapper. Here is a simple example:

class Phrasebook extends EasyMySQL {
function Q ($Query) {
    switch ($Query):
        case ACCOUNT:
return <<<SQL
SELECT  id,
    name,
    address,
    city,
    state,
    zip
FROM    accounts
WHERE   id = %d
SQL;
        case HITS:
return <<<SQL
SELECT  sum(raw) as raw,
    sum(uni) as uni,
    date
FROM    stats
WHERE   id = %d
AND date >= %d
AND date <= %d
SQL;
    endswitch;
}
}

Here is an example of how we would use this:

/* Was like this... */
mysql_connect($User, $Pass);
mysql_select_db($Database);
$result = mysql_query("SELECT id, name, address, city, state, zip
                    FROM accounts WHERE id = $id");
while ($Data = mysql_fetch_object($result)) {
...
}
/* Now it's like this */
$Database = new Phrasebook ($User, $Pass, $Database);
$Database->queryf($Database->Q("ACCOUNTS"), $id);
while ($Data = $Database->next()) {
...
}

Obviously, the end result is a lot less code, especially for very large

queries, and looks much nicer, with all of the advantages discussed earlier.

So what about Perl? Well, Perl actually has specific advantages in

Phrasebooks over PHP, mostly, that we can contain our SQL Phrasebook in a

package with constant, null prototyped definitions (when possible) and this

increases the speed of the Phrasebook access exponentially (not that it isn't

already very fast in the example above). One rule to keep in mind with Perl

is that if you only use a table once, you should use a null prototype for that

query. Why? Well if we're not doing anything dynamic, we don't need to pass

parameters. If we use a null prototype, the Perl compiler flags that method

as a constant, and compiles it as if it were a single quoted string into our

code. So it has absolutely no runtime loss over single quoted, static string

formatting. That's pretty impressive, abstracting the code without slowing it

down whatsoever.

Here is an example Phrasebook package in Perl that executes the same

queries as the above PHP example:

package Phrase;
sub ACCOUNT () { 
<<SQL;
SELECT  id,
    name,
    address,
    city,
    state,
    zip
FROM    accounts
WHERE   id = %d
SQL
}
sub HITS    () {
<<SQL;
SELECT  sum(raw) as raw,
    sum(uni) as uni,
    date
FROM    stats
WHERE   id = %d
AND date >= %d
AND date <= %d
SQL
}

Here is an example of how we would use this:

# What was this
$sth = $dbh->prepare
("SELECT id, name, address, city, state, zip FROM accounts WHERE id = $id");
# Now becomes this
$sth = $dbh->prepare(sprintf(Phrase->ACCOUNT, $id));

This is obviously not only more pleasant to have in your code if you're not

currently worrying about the SQL, but also has all the advantages mentioned

earlier.

As you can see here, we didn't exercise the database abstraction as in the

PHP example mentioned, because the robustness of Perl's DBI doesn't

really require it. If you wanted, however, to rid your code of the

extra sprintfs, considering that Perl's DBI already offers the other conveniences our

PHP wrapper had, write a simple object to inherit a DBI object and call

prepare with a sprintf. Here is an extremely simple example:

package FormatDBI;
use DBI;
use Data::Dumper;
@FormatDBI::ISA = qw(DBI);
package FormatDBI::db;
@FormatDBI::db::ISA = qw(DBI::db);
sub preparef {
return $_[0]->prepare(sprintf($_[1], @_[2..$#_]));
}
package FormatDBI::st;
@FormatDBI::st::ISA = qw(DBI::st);
package main;
use strict;
use warnings FATAL => qw( all );
use Phrase;
my $dsn = "DBI:mysql:database=aff;host=localhost";
my $dbh = FormatDBI->connect($dsn, "username", "password");
my ($Data,$sth);
$sth = $dbh->preparef(Phrase->ACCOUNT, 40);
...etc...

Hope you find this useful.