With many OO wrappers you lose the ability to readily take the generated SQL and test it outside of Perl. It also becomes hard to take the SQL to a DBA when trying to figure out performance issues. Or the other way around when the DBA says, "These are our most expensive running SQL statements..." - does no good when you can't find the query.

So by all means find ways of modularizing your code so that the SQL is not strewn throughout. That is just common sense. But the use of an OO abstraction layer might or might not make sense for you.

Just because someone else thinks that the approach is worthwhile, and has gone through the work to implement it, does not mean that it will be the best solution for you. Understand the costs and benefits, then decide appropriately for your situation.

I like your analogy to templating. Because the fundamental issues are pretty much the same. As I pointed out in Re (tilly) 6: Code Critique, there are many possible solutions, and depending on your circumstances, any could turn out to make the sense for you. Until people start thinking in terms of the trade-offs, they don't have a hope of guessing what the right one is going to be for them.

With many OO wrappers you lose the ability to readily take the generated SQL and test it outside of Perl. It also becomes hard to take the SQL to a DBA when trying to figure out performance issues. Or the other way around when the DBA says, "These are our most expensive running SQL statements..." - does no good when you can't find the query.

Yes indeed.

My current job is trying to help a company that uses Apples Web Objects to create java apps. The SQL generated by WO5 is OK in most cases, but sometimes you need to tweak it to get decent behavior, and trying to figure out which part of the OO framework generates which queries, and then figuring out how to fix them is ugly and difficult.

Using an OO framework is great for the programer, and can lead to faster development times. But when it comes to getting good performance from the database itself in a non-trivial app they cause a lot of problems and headaches for the DBA (which is the role I play these days...)

Michael

I see these OO wrapper modules as more like using CGI.pm's HTML generation functions instead of writing webpages in HTML. They aren't about separating Perl and SQL. They're about replacing SQL with a new Perl OO notation. In my opinion that only makes sense if you're uncomfortable working with SQL. I haven't seen one yet that looked like it was worth the trouble. All the stuff they claim to make so much easier is already easy if you're fluent with SQL!

All depends on your definition of "easy" I guess. If I have a database where Class::DBI would be a good match, and I need to access/update all the columns on a row by row basis I'd rather do:

use strict;
use base qw(Class::DBI::Pg);
__PACKAGE__->set_db( Main => 'dbi:Pg:dbname=dbname', 'user', 'pass' );
__PACKAGE__->set_up_table( 'table' );
[download]

Than spend time writing all the code to fetch, update and store data by hand.

Not that I'm against writing SQL by hand. Do it all the time. It's just that I like perl to automate the dull repetitive bits where feasible.

I see these OO wrapper modules as more like using CGI.pm's HTML generation functions instead of writing webpages in HTML. They aren't about separating Perl and SQL. They're about replacing SQL with a new Perl OO notation. In my opinion that only makes sense if you're uncomfortable working with SQL. I haven't seen one yet that looked like it was worth the trouble. All the stuff they claim to make so much easier is already easy if you're fluent with SQL!

The big win to using a wrapper, at least in the apps I write, comes when I have to generate complex queries dynamically, based on user input.

I'm not talking about when just a parameter changes (bind 5 versus 10), but rather when the query may span many tables, with many where clauses, subclauses, etc. Generating SQL on the fly for this sort of thing is extremely painful.

I also don't think SQL is all that great a language, so I really don't mind using Perl instead. Once Perl 6 comes out I'd like to write a truly relational DB using it as the native language (or some subset thereof). That would be my ideal database.

Ah, dreams ...