isnt that a long word to type every time? I envision subroutines with an interpolating property:
sub get_authors : dbi_interp {
my ($author_list) = @_;
qq[SELECT * FROM AUTHORS WHERE author_id IN ], $author_list ;
}
my $sql = get_authors (10 .. 12);
but this is just brainstorming
Yes, I shall shorten the name. I'm not certain if you mean something similar to what I'm thinking of when using the "get_authors : dbi_interp" syntax, but I am considering adding support for Filter::Simple to simplify the syntax even further as such:
use SQL::Interpolate FILTER => 1;
...
my $rows = $dbh->selectall_arrayref(dbi_interp qq[
SELECT * FROM table
WHERE color IN [EMAIL PROTECTED]
AND y = {$x}
LIMIT {[1, 10] }
]);in which case the source filter will internally translate the string into the parameters list as before rather than letting Perl perform its standard string interpolation.
except that SQL::Interpolate still exposes and utilizes the full native SQL syntax of your database.
this means you must write custom SQL for each database the code is to work on... for example LIMIT is handled very differently by Postgres and MySQL... it might be nice if LIMIT
were a subroutine which were translated to the proper syntax... in fact I have just started on a module which generates SQL snippets based on the DBD of the $dbh.
True, but if SQL::Interpolate were told during module initialialization (or object construction if made OO) which database it is to generate SQL for, it would be able to generate the appropriate SQL and translated binding parameters assuming that the reference [1, 10] is always interpreted in a database-independent manner.
An alternate approach would be to use something like this:
sql_interp(q[ SELECT * FROM mytable], &limit($a, $b) );
in which case the "limit" function (implemented in SQL::Interpolate or in third-party another module) would return an arrayref as such:
bless ["LIMIT ", \$b, "OFFSET", \$a], 'SQL::Interpolate::FlattenArray'
dependent on the database type. Before processing, sql_interp will flatten the contained array elements into its argument list so that the return value of sql_interp will be
("SELECT * FROM mytable LIMIT ? OFFSET ?", $b, $a)
The question then is to what extent SQL::Interpolate should concern itself with database independence. Simple things like handling how variables are interpolated between surrounding SQL are the responsibility of SQL::Interpolate and are easily done. But if an application must support multiple databases using drastically different SQL structures (e.g. different built-in functions or choice of joins or sub-selects), then SQL might not be appropriate, in which case SQL::Interpolate is not appropriate either. Modules that do aim for such database independence might internally use SQL::Interpolate for native database access, however. Admittedly, the code I write tends to be targeted to a single database or to two databases restricted to fairly compatible SQL, so database independence is not something I've looked at much.
I find it interesting that you require scalars to be passed by reference (e.g., \$x) , but this creates consistency.
The interpolation function could have instead understood even-numbered arguments to be variables to interpolate and odd-numbers arguments as the SQL:
sq_interpolate("SELECT * FROM mytable WHERE x=", $x)
but I think its more extensible to have the function unambigously distinguish arguments by whether each is a reference. This way, the 'limit' function shown above will work as used, and one could even pass in "manipulators" similar to that of C++'s iostream if the need arrises:
sql_interp(qq[ SELECT * FROM mytable WHERE height = ], interp_number(), \$y ])
best regards, -davidm
