RE: [OT] Inspired by closing comments from the UBB thread. (fwd)
Since you asked, my opinion is that what you describe would not be
useful. Primarily for the reason pointed out already by a number of people
-- lack of flexibility. Most, if not all, database servers accept highly
customizable performance params to a query, and most even moderately
evolved applications make use of SQL queries that are significantly
more complex than a single-where-clause select.
At ValueClick we built a wrapper module (DB.pm :) that delivered a $dbh
into the API, handling everything up to that point with minimal
fuss. From that point on, some standard things were collected in a utility
class, but most modules created their own $sth, usually with bind
variables, with SQL statements nicely formatted in the source using a here
doc ... it was highly manageable and functional, and most of all it was
flexible. Not all applications are fast-developing, but my experience is
that it pays to develop as if yours were ... rapid access to tweak the SQL
fetching data into the application is very desirable, IMHO.
The point is not that you can't abstract it all away as you show in your
code below, it's that by the time you have covered all eventualities
(sorts, groups, selects from multiple tables, et al.), your interface is
so complicated you are basically paraphrasing the SQL in some new language
of your invention. And that, if I am not mistaken, is the purpose of SQL
in the first place!
There is such a thing as over-abstraction, IMHO, and having played with
this a lot, I have found that this type of effort would be such.
Hope this helps,
~~~
Nick Tonkin
On Wed, 1 Aug 2001, Joe Breeden wrote:
Woooie!?!
I didn't expect the firestorm this post would generate. From what I hear
people are either embedding SQL or writing their own utility module to
essentially do something along the line of:
$s-StartDBI ( DSN = 'somedsn_pointer') ;
eval {
$s-SelectSQL ( NAME = 'sql_select',
TABLE = 'sometable',
FIELDS = ['field1', 'field2', 'field3'],
WHERE = 'field1=?',
VALUES = $some_value_for_field1);
while ( my $return = $s-SQLGetArray( NAME = 'sql_select')) {
#do something $return - maybe complete a template object?
}
};
$s-EndDBI ( DSN = 'somedsn_pointer', QUERIES = 'sql_select', RESULTS =
$@);
Where the different calls do the things hinted at in their name (i.e.
StartDBI opens the DSN and connects to the database in question, SelectSQL
would prepare the SQL select statement and execute it via DBI). This allows
the us to pass a native Perl structure which is reformatted to work with
DBI. We also get back scalars, arrays, or hashes that are easy to work with.
This is what we do here where I work. I still consider this embedded SQL
because a change to the table or even to the server could cause the program
to break in a lot of places. I think what I had in mind was some way to put
this type of processing into a layer where all the SQL related items are
essentially in a template file somewhere maybe a SQL::Template type thingy.
If this is something that people feel would be a worthwhile endeavor, let me
know and maybe when there's have a little free time in the Fall one could
write a CPAN module that has this functionality.
We had the conversation awhile back about adding redundant and unnecessary
crap to CPAN and I want to make sure something like this would be a good
thing or not.
Thanks,
--Joe Breeden
--
Re: [OT] Inspired by closing comments from the UBB thread. (fwd)
Nicely put Nick. There's already a Structured Query Language,
And there's an easy to use abstraction called DBI up on CPAN.
Feel free to use in application code thusly:
my $statement = qq~
SELECT field1, field2
FROM table
WHERE id = ?
~;
my $ref;
my $sth = $dbh-prepare($statement);
foreach my $question (@questions) {
$sth-execute($question);
$ref = $sth-fetchrow_hashref;
$sth-finish;
display_data($ref);
}
At the end of the day you're gonna have a $dbh somewhere and it's gotta
receive some SQL to be useful. Hide it where you want to, I'll put it
real close to where the data is going to be used (unless the data needs
to be used from many different access points in which case all that
nasty :-) SQL goes into a OO module that understands how to provide:
my $handle = new foobar $dbh;
my $arrayref = $handle-gimme_foobar_data;
).
--
Daniel Bohling
NewsFactor Network
The point is not that you can't abstract it all away as you show in your
code below, it's that by the time you have covered all eventualities
(sorts, groups, selects from multiple tables, et al.), your interface is
so complicated you are basically paraphrasing the SQL in some new language
of your invention. And that, if I am not mistaken, is the purpose of SQL
in the first place!
There is such a thing as over-abstraction, IMHO, and having played with
this a lot, I have found that this type of effort would be such.
Hope this helps,
~~~
Nick Tonkin
On Wed, 1 Aug 2001, Joe Breeden wrote:
Woooie!?!
I didn't expect the firestorm this post would generate. From what I hear
people are either embedding SQL or writing their own utility module to
essentially do something along the line of:
$s-StartDBI ( DSN = 'somedsn_pointer') ;
eval {
$s-SelectSQL ( NAME = 'sql_select',
TABLE = 'sometable',
FIELDS = ['field1', 'field2', 'field3'],
WHERE = 'field1=?',
VALUES = $some_value_for_field1);
while ( my $return = $s-SQLGetArray( NAME = 'sql_select')) {
#do something $return - maybe complete a template object?
}
};
$s-EndDBI ( DSN = 'somedsn_pointer', QUERIES = 'sql_select', RESULTS =
$@);
Re: [OT] Inspired by closing comments from the UBB thread. (fwd)
On Wed, Aug 01, 2001 at 05:29:10AM -0700, Daniel wrote:
Nicely put Nick. There's already a Structured Query Language,
And there's an easy to use abstraction called DBI up on CPAN.
Feel free to use in application code thusly:
my $statement = qq~
SELECT field1, field2
FROM table
WHERE id = ?
~;
my $ref;
my $sth = $dbh-prepare($statement);
foreach my $question (@questions) {
$sth-execute($question);
$ref = $sth-fetchrow_hashref;
$sth-finish;
display_data($ref);
}
Umm, these days I'd write loop that as:
foreach my $question (@questions) {
display_data( $dbh-selectrow_arrayref($sth, undef, $question) );
}
:-)
Since ValueClick's been mentioned I'll point out that I now have the
task of exploring how to migrate all the embedded SQL code that Nick
mentioned from MySQL over to Oracle :-) [Hi Nick!]
I'm not a big fan of heavy abstractions and I'm pretty comfortable
with how much of the code is structured, in general.
I'm hoping that a mixture of new DBD::Oracle and DBI features, possibly
a DBD::Oracle::mysql subclass, and a sprinkling of DBIx::AnyDBD will
prove sufficient.
Combining that with using Oracle's ODBC gateway to make MySQL tables
appear live within Oracle should enable a smooth migration without a
sharp 'big bang' transition.
Of course, all this is just theory at the moment.
Tim.
