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

markjugg on 2007-07-07T04:32:57

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 => ’nwiger’,
                -nest => [
                    -and => [workhrs => {’>’, 20}, geo => ’ASIA’ ],
                    -and => [workhrs => {’<’, 50}, geo => ’EURO’ ]
                ],
            ],
  );
  

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 )

---

SQL::Interpolate

Juerd on 2007-07-07T12:57:21

Hi,

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

Cheers

SQL::Interpolate and SQL::Interp

markjugg on 2007-07-07T13:42:11

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 should stick with SQL::Interpolate. I used it for years and never missed them.

Also, there were a few improvements to SQL::Interpolate which were never officially released in that name space because the author disappeared. I tried to release "0.40" in that name space with those updates, that release was marked "UNAUTHORIZED" by CPAN, and is difficult to discover and download.

So, SQL::Interp, also has the improvements from 0.33 to 0.40 in SQL::Interpolate. These were mostly made by the author or other contributors.

http://search.cpan.org/src/MARKSTOS/SQL-Interpolate-0.40/Changes

      Mark