Main Page Content
Effectively Implementing SQL Phrasebooks
Rated 2.54 (Ratings: 7) (Add your rating)
Log in to add a comment
(9 comments so far)
Scott McCoy
User since: May 10, 2003
Last login: February 12, 2011
Articles written: 1
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 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.
