New module: SQL::Functional

2016-08-01

If you have an application with simple database needs, writing the SQL strings directly in the program works fine. If it’s bigger, an Object-Relational Mapper like DBIx::Class starts to make sense. I find the additional boilerplate of an ORM isn’t worth the effort for simple needs, but it doesn’t take much more for the overhead to be worthwhile.

Then there’s another point beyond that where the ORM doesn’t know how to effectively and efficiently run a request. When you get there, it’s time to write raw SQL again, perhaps giving the result set back to your ORM so you can continue on as if nothing happened.

How do you write that SQL? Probably with strings again, or a helper library like SQL::Abstract.

The problem with raw strings is brought up in Epigrams in Programming:

• It’s difficult to extract sense from strings, but they’re the only communication coin we can count on.
• The string is a stark data structure and everywhere it is passed there is much duplication of process. It is a perfect vehicle for hiding information.

The nature of our database problem is that we’ve hit on a very complicated case, and now we’re stuck using the most ad-hoc way of structuring data. Not a good combination!

SQL::Abstract tries to address this by providing a structured way of building a string. We still have to output a string (“ . . . they’re the only communication coin we can count on”), but at least we don’t have to deal with it directly. The problem is that, again, we’re getting to the most complicated cases of SQL, and SQL::Abstract abuses syntax tricks for these cases. Consider this case I pulled out of its docs:

my %where = {
  start0 => { -between => [ 1, 2 ] },
  start1 => { -between => \["? AND ?", 1, 2] },
  start2 => { -between => \"lower(x) AND upper(y)" },
  start3 => { -between => [
    \"lower(x)",
    \["upper(?)", 'stuff' ],
  ] },
};

Why are we taking references to scalars? And references to arrayrefs, for that matter? Could you copy-and-paste this to a DBA and explain what’s going on?

When you drill down a bit, the reason for these issues is that SQL::Abstract uses an Object Oriented interface to build this string, and OO is just not a good fit for this problem.

On the other hand, the problem can be solved more cleanly by combining functions that look vaguely like SQL:

my ($sql, @sql_params) = SELECT [qw{ bar baz }],
    FROM( 'foo' ),
    WHERE AND(
        match( 'bar', '=', 1 ),
        OR(
            match( 'baz', '=', 2 ),
            match( 'qux', '=', 3 ),
        ),
    );

This is exactly what SQL::Functional does. Version 0.1 is now up on CPAN.