regarding "still requires your Perl code to know the names of your SQL statements" ... if you take a look at your ABSTRACT example. The process of getting the SQL and creating $dbx is missing.
The SQL can live in any format/object, as long as it somehow includes sufficient provides/requires "metadata" for DBIx::Declare to figure out which statements need to be executed to generate the desired data. To keep things short in the SYNOPSIS I left the SQL with the Perl code, but it could be loaded from a master "dictionary" file, or some form of Phrasebook object with added metadata.
Presuming it is created in the same way as the SYNOPSIS, then the SQL is now wed to a particular template. Since you probably often find the need for reusing the same query in a wide variety of the small bio-informatic apps you write, you need a way of resourcing the same SQL in a number of different apps completely independant of which template is using it so that you can reuse it. The only way I can see this as doable is via a Class::Phrasebook or SQL::Catalog.
Again, the idea of DBIx::Declare is that you *do* have one dictionary/phrasebook/catalog of SQL lying around somewhere that everything can make use of; but the way a script/template/etc makes use of the SQL is by telling DBIx::Declare "Look, I need these three data pieces, and here's what I already have - using this global store of SQL statements, figure out what needs to be done", as opposed to "Go use the SQL named 'get_user_info' to provide me x, y and z given a and b".
regarding how to provide the right data. I have to admit you are right. I think the key thing would be to ask if it makes the code sufficiently cleaner. Your examples seem fine for single row SELECTs but how about returning multiple rows of data?
Great question; my current thought process is that the returned data is tightly bound to field names. For example, an SQL query might return one or multiple rows:
my ($output) = $dbx->process(@stuff);
$output is a hashref with "wanted" field names as keys; the values of the hash would be scalars if only one row was returned, or arrayrefs for multiple rows.
Or, maybe the values of the hashref should always be arrayrefs, so that your code wouldn't have to worry about whether one row or many were returned.
Or, maybe the $output hashref itself should be tied such that a FETCH in scalar context provided the first element of the arrayref (so that code could access it in either fashion). You could also imagine that the tied version had a built-in "iterator" so that looping would be feasible (thus allowing a more memory-sensitive fetchrow() approach):
my ($output) = $dbx->process(@stuff);
# looping construct version 1: every FETCH increases the iterator
while(my @val = values %$output) {
my %data;
@data{keys %$output} = @val;
}# looping construct version 2: iterator functions
while ($output->next_row) {
# use %$output directly as a hash of row values.
}Thoughts?
Also, I can't help but mention that the requires and provides parts of your SQL description are strongly reminiscent of stored procedures.
Sure; SQL is a declarative language, after all. But stored procedures still must be called by name, not by context.
Finally, where is this module obtained?
Currently, only in the dark nether regions of my cranium. Once I'm satisfied that I've at least tried to satisfy comments such as yours, I'll actually do the work of typing it out.
Thanks for your time and thoughts,
-Aaron
