SQL templates

nkuitse on 2003-08-07T15:46:59

I'm not crazy about positional parameters in DBI, but I don't want to use a heavy-duty module like Tangram or Alzabo* to get away from them.

What I decided was to use simple SQL templates with placeholders like $foo instead of ?, then write a simple module to expand the template given a hash of placeholder names and values.

my $str = 'SELECT id FROM People'
    . 'WHERE age >= $min AND age <= $max';
my $template = SQL::Template->new($str);
my ($sql, @args) = $template->process(
    'min' => 20,
    'max' => 29
);
# $sql = 'SELECT id FROM People'
# . 'WHERE age >= ? AND age <= ?'
# @args = (20, 29)
$dbh->prepare_cached($sql, @args);

That was simple enough. But then I also wanted multiple substring searching like this:

my $str = 'SELECT id FROM People'
    . 'WHERE { name ILIKE $name }:AND';
my $template = SQL::Template->new($str);
my ($sql, @args) = $template->process(
    'name' => 'tolk% %chris%'
);
# $sql = 'SELECT id FROM People'
# . 'WHERE name ILIKE ? AND name ILIKE ?'
# @args = ('tolk%', '%chris%')
$dbh->prepare_cached($sql, @args);

That was easier than I had thought it would be.

The hardest part is what to call my module. SQL::Template is the most obvious choice, but to me that sounds like a big, fancy module whereas mine is very simple (and likely to remain that way).

Meanwhile, the module isn't quite ready for release, since I think there are some Postgresisms to root out.

* Not that there's anything with Tangram or Alzabo!

Update 18 Aug: Fixed the quotes in the sample code (should have been single quotes, not double. D'oh!)

---

Positional Parameters are a good idea

Dom2 on 2003-08-07T21:31:37

Not using them means that you have to recreate the query execution plan each time. You get better performance by using them.

You might want to have a look at Tim Bunce's Advanced DBI talk

-Dom

Re:Positional Parameters are a good idea

nkuitse on 2003-08-08T12:45:06

Not using them means that you have to recreate the query execution plan each time.

Actually, my code generates SQL with positional parameters. It takes a pseudo-SQL template like this:

SELECT foo
  FROM bar
  WHERE baz = $baz
   AND qux = $qux

And turns it into this:

SELECT foo
  FROM bar
  WHERE baz = ?
   AND qux = ?

Simultaneously taking the name-based parameters that you provide (in a hash, i.e., unordered) and lining their values up in the correct order in a params array:

$tmpl = SQL::Template->new($template_string);
($sql, @params) = $tmpl->process(
    'qux' => 'quaint',
    'baz' => 'baboon'
);

The result is:

$sql = ...as above...
@params = ('baboon', 'quaint')

Which you can then use normally:

$sth = $dbh->prepare[_cached]($sql);
$sth->execute(@params);

Oh, and thanks for the tip on Tim Bunce's slides!

need testers?

WebDragon on 2003-08-14T22:18:49

I'd be happy to help test this with MySQL if you'd like. It sounds a lot like it would be useful for me with an upcoming project I'm working on.

Re:need testers?

nkuitse on 2003-08-20T14:47:28

I'd be happy to help test this with MySQL if you'd like.

Sure, thanks. (Though it doesn't really matter which RDBMS is used, since SQL::Template simply spits out SQL and a correctly ordered list of values.)

You can download version 0.01 here (ca. 3KB) for now. Do the usual to install:

perl Makefile.PL
make
make test
[sudo] make install

It sounds a lot like it would be useful for me with an upcoming project I'm working on.

Glad to hear it!