SQL::Interpolate::Filter - Source filtering for SQL::Interpolate
use SQL::Interpolate FILTER => 1, qw(:all); ($sql, @bind) = sql_interp sql[ SELECT * FROM mytable WHERE color IN @colors AND y = $x OR {z => 3, w => 2} ]; ($sql, @bind) = sql_interp sql[ INSERT INTO table { color => $new_color, shape => $new_shape width => $width, height => $height, length => $length } ];
This module adds source filtering capability to SQL::Interpolate. The source filtering option provides Perl an additional quote-like operator (see perlop) denoted sql//. The quote can contain SQL and Perl variables.
sql/SELECT * FROM mytable WHERE x = $x/;
Source filtering will transform this construct into a constructor for a macro object that simply contains the filtered interpolation list:
SQL::Interpolate::Filter::_make_sql("SELECT * FROM mytable WHERE x = ", \$x);
The macro expands into the contained interpolation list, which sql_interp (or dbi_interp) can then interpolate as usual:
sql_interp
dbi_interp
"SELECT * FROM mytable WHERE x = ?", ($x)
To enable the quote-like sql// operator, add a "FILTER => 1" to your use statement:
use SQL::Interpolate FILTER => 1, qw(:all);
Just as it is possible to do with q// or qq// operators, you can use various delimiters on the sql// operator, such as
sql[SELECT * from mytable WHERE x = $x] sql(SELECT * from mytable WHERE x = $x) sql<SELECT * from mytable WHERE x = $x> sql/SELECT * from mytable WHERE x = $x/
SQL:::Interpolate::SQL objects (and sql// operators representing them) come with a string concatenation operator (.), so you can do things like
sql[ SELECT partnum, desc, price, stock FROM inventory ] . $show_all ? sql[] : sql[WHERE price > $price AND stock IN $stocks]
An sql// object concatenated with a string will append the string verbatim into your result SQL. Future versions of SQL::Interpolate may throw an error if one attempts to concatenate an sql// object with a string. It's a better idea to do this if you must:
$dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = ] . sql[$name]);
or simply
$dbx->do(sql[UPDATE mytable SET y = 0 WHERE x = $name]);
because then the $name value will be properly interpolated.
INPUT: sql[WHERE one=$x AND $y] OUTPUT: "WHERE one=", \$x, " AND ", $y INPUT: sql[INSERT INTO mytable @x] OUTPUT: "INSERT INTO mytable ", \@x INPUT: sql[INSERT INTO mytable [1, 2]] OUTPUT: "INSERT INTO mytable ", [1, 2] INPUT sql[INSERT INTO mytable %x] OUTPUT: "INSERT INTO mytable ", \%x INPUT: sql[INSERT INTO mytable {one => 1, two => 2}] OUTPUT: "INSERT INTO mytable ", {one => 1, two => 2}
To enable tracing on the source code filter, do
use SQL::Interpolate TRACE_FILTER => 1, FILTER => 1;
The source code of the module after source filtering will be sent to STDERR.
... SQL::Interpolate::Filter::_make_sql(qq[SELECT * FROM mytable WHERE x = ], \$x) ...
This module depends on SQL::Interpolate, Filter::Simple (any), and Text::Balanced >= 1.87.
Source filtering is somewhat experimental and has the potential to give unexpected results because lexing Perl is hard. The module relies on Text::Balanced for the lexing. Even though Text::Balanced makes a valiant effort, the task is difficult and the results not always precise, especially for very obscure Perl constructs. It should work fine though on many things. If in doubt, check the output yourself by enabling the TRACE_FILTER option.
Support Text::Balanced 2.0 and improved Perl lexing.
Should a distinction be made between q// v.s. qq// for sql//? Which semantics should sql// have?
How should Perl variables containing SQL literals (rather than than binding variables) be interpolated? Maybe with a stringified macro? e.g. sql/...LITERAL($x).../. Should stringified macros be allowed in the interpolated SQL literal? (probably no for security).
Variables inside a stringified macro probably prevents the macro from being un-stringified. e.g. sql/...MYMACRO($x).../ --> "...MYMACRO(", \$x, ")..."
Copyright (c) 2004-2005, David Manura. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself. See http://www.perl.com/perl/misc/Artistic.html.
Other modules in this distribution: SQL::Interpolate, SQL::Interpolate::Macro, DBIx::Interpolate.
Dependent: Text::Balanced, Filter::Simple.
To install SQL::Interpolate, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Interpolate
CPAN shell
perl -MCPAN -e shell install SQL::Interpolate
For more information on module installation, please visit the detailed CPAN module installation guide.