Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Journal of markjugg (792)

Friday July 06, 2007
11:32 PM

SQL::Interp 1.00 released as a SQL::Abstract alternative

[ #33734 ]

Perl hashes are natural representation of name/value pairs, and there should be an easy way to translate them in the name/value pairs SQL expects in INSERT and UPDATE statements, as well as WHERE clauses.

Also, for best security practices, bind variable should be used. This further complicates the problem space, because names and values need to be split up in the resulting SQL.

SQL::Abstract is a popular solution for addressing these problems, and I one I used myself for some time. To its credit, it has a fairly intuitive API, and focuses just on SQL generation, not getting involved the actual query execution.

However, as SQL::Abstract grew over time, it tried to handle more and more complex cases of SQL, and the design failed to scale up well. Here's an example bit of SQL, straight from the SQL::Abstract docs.

The goal is to generate SQL that looks like this:

    WHERE ( user = ? AND
                 ( ( workhrs > ? AND geo = ? )
                OR ( workhrs < ? AND geo = ? ) ) )
   

The SQL abstract solution looks like this:

  my @where = (
             -and => [
                user => &#8217;nwiger&#8217;,
                -nest => [
                    -and => [workhrs => {&#8217;>&#8217;, 20}, geo => &#8217;ASIA&#8217; ],
                    -and => [workhrs => {&#8217;<&#8217;, 50}, geo => &#8217;EURO&#8217; ]
                ],
            ],
  );
 

Now, to generate that structure, you would have to know exactly the SQL structure you wanted, plus you would have to know the special syntax that goes with "-and", "-nest" and the especially cumbersome syntax that translated " > 20 " into " => {'>', 20}, ". The resulting Perl is actually more cumbersome and ugly to write than the original SQL!

With SQL::Interp, there is nothing special to learn to handle this case. You just write out the SQL like you want, and drop in the Perl variables you need as references:

    "WHERE ( user = ",\$user,"  AND
    ( ( workhrs > ",\$asia_hrs," AND geo = ",\$asia," )
                OR ( workhrs < ",\$euro_hrs," AND geo = ",\$euro," ) ) )"
   

The result is easy to develop, easy to read, and neatly takes care of managing bind variables for you.

And, yes, there are shortcuts for managing the common insert and update cases, similar to those offered by SQL::Abstract:

Insert

    SQL::Abstract
        my ($sql, @bind) = $o->insert('table',\%data)
        $dbh->do($sql,{},@bind);

    SQL::Interpolate
        my ($sql, @bind) = sql_interp("INSERT INTO table",\%data);
        $dbh->do($sql,{},@bind);

    DBIx::Interpolate
        $dbh->do_i("INSERT INTO table",\%data);
       

Update

    SQL::Abstract
        my ($sql, @bind) = $o->update('table',\%data,\%where);
        $dbh->do($sql,{},@bind);

    SQL::Interpolate
        my ($sql, @bind) = sql_interp("UPDATE table SET", \%data, "WHERE", \%where );
        $dbh->do($sql,{},@bind);

    DBIx::Interpolate
        $dbh->do_i("UPDATE table SET", \%data, "WHERE", \%where )

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Hi,

    Could you describe the important differences between SQL::Interp and SQL::Interpolate, for someone who's trying to choose?

    Cheers
    • Juerd,

      Thanks for your interest. SQL::Interp and DBIx::Interp were created as a fork, after the author of SQL::Interpolate couldn't be found.

      The core functionality remains unchanged, but I took some liberties on some design decisions as part of the fork:

      - The optional source filtering feature has been removed.
      - The optional "macro" feature was removed
      - A legacy, deprecated function "sql_literal" was removed.
      - The docs were overhauled to try to be simpler and clearer.

      So if you want those removed features, you