There's a lot of good activity in the Class::DBI community as of late as well. Definitely something to check in on.

Just a direct link to Class::DBI wiki.

Is cross-platform typically not an issue for your projects, or do you just target the lowest common denominator supported by the DBMS types you're targeting?

For example, the syntax for selecting a subset of rows with limit and offset varies from one database server to another -- do you just not use it in your applications, or do you have some other way of managing this issue?

If you're building a fixed-site application for a single customer, this isn't really an issue, but if you're building something for widespread reuse, it's nice to be able to say fetch_select( ..., limit => 10, offset => 40 ) and have it automatically use "limit 10 offset 40" when on MySQL, or "ROWNUM" when on Oracle.

What do you mean by cross-platform?

For instance the transactional semantics that Oracle uses differ from virtually any other database, do these modules correct for that?

For instance DBD::Sybase doesn't allow you to use placeholders with 2 open statement handles on the same database handle at the same time (because of race conditions). Do you run into bugs from that?

While I agree that it is nice to abstract away the details of the database, I'm also worried that the abstraction will leak. And in some ways an incompletely implemented feature is worse than a complete one because you rely on it - and get burned.

While I agree that it is nice to abstract away the details of the database, I'm also worried that the abstraction will leak. And in some ways an incompletely implemented feature is worse than a complete one because you rely on it - and get burned.

This is something that I've struggled with -- it's nice to translate idioms or emulate standards where possible, but no matter what you do, you can't hide the differences between DBD::CSV and DBD::Oracle.

In DBIx::SQLEngine, I've tried to take an intermediate path: provide a common interface where it is possible to do so, and provide a clear warning of where it isn't.

The simpler things, like limit and offset, are expected to work everywhere, with driver-specific subclasses providing emulation if necessary; for Oracle, this means that DBIx::SQLEngine::Driver::Oracle needs to convert limit and offset arguments to a subselect and ROWNUM criteria.

For more advanced features, like outer joins and transactions, the driver subclasses provide capability methods, so that an application can check whether they are available and exit at startup, rather than failing later in the middle of something complicated.

I'm still working on cataloging these differences and figuring out when they can be worked around and when they can't. It is my sincere hope that many of them can, in fact, be addressed in a consistent way, but of course there's a long way to go.

Differing transaction semantics, or the complete lack thereof, is obviously something we can't hide or emulate; all we can do is to make it possible to check which kind of semantics are in effect.

DBIx::SQLEngine doesn't yet provide a mechanism for special handling of multiple statement handles, but I know that this is an area of variation, and it's the kind of feature I'd like to add support for in the future. (Presumably Sybase users have workarounds for the issue you describe, like opening a second connection, or client-side placeholder substitutions, or something...) Again, at a minimum, it would be useful for an application to be able to ask "can I safely open multiple handles" and either trigger some work-around behavior, or exit early with an explanation of the requirement rather than crashing later on.

In practical terms, I don't have a local installation of Sybase nor a current client who's using it, so support of those advanced driver is likely to be limited unless someone chooses to work on adding it. (The existing Oracle support was mostly contributed by folks at the University of Innsbruck, for example.)

DBD::Sybase doesn't allow you to use placeholders with 2 open statement handles on the same database handle at the same time

Not quite - the problem is multiple open statement handles with AutoCommit off (because each statement handle requires its own connection if they are to be active at the same time, and DBD::Sybase isn't in the business of providing distributed transaction processing... :-)).

Michael

Is cross-platform typically not an issue for your projects, or do you just target the lowest common denominator supported by the DBMS types you're targeting?

Cross-platform? Sure! Cross-DB? Not usually. Take, for example, Krang. It's proving to be very portable, due partly to the fact that MySQL is very portable. Installing MySQL is so easy that it's hardly a barrier to adoption.

Supporting multiple databases in an application is very rarely worth the trouble, in my opinion. I say choose a good free database with wide portability and damn the rest.

-sam

Hey, if I had a DBA to write all my SQL for me, I'm sure I'd be using some kind of SQL equivalent of HTML::Template. You know, separate my code from my SQL so that the SQL geek can work on it more easily. Would I be using an OO wrapper? No way! If I did that the DBA wouldn't be able to help me! But I don't work with a DBA and I doubt I will anytime soon. That means I have to write my own DB access code and I might as well do it in the simplest way possible, with DBI.

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!

-sam

Even if you only get to work closely with a DBA, what you say has a lot of truth.

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.

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 ...