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

It sounds like the abstraction is going to leak heavily.

First of all the idea that you create a model that you can implement portably and program to it is a good one. It is the right way to make a complex application cross-platform. The wrong way is to scatter around the equivalents of #ifdef everywhere.

If you can't create a model like that, there is a problem right there. As you try to document differences, they will quickly explode in complexity.

For instance take transaction semantics. In every relational database that I know that supports transactions except Oracle, if you try to read a row that is being updated in a transaction, you block. A key part of Oracle's design is that queries are non-blocking abd give you a consistent view of the database at a specific time. So if the row has been updated (partially or completely) since your transaction started, then it goes to the redo buffer to offer you what the value was when you opened your transaction. No blocking.

As you can imagine, this is a major difference. Oracle's behaviour is consistent with the standards. Oracle's approach is great for concurrency. However it introduces tons of possibilities for race conditions that people who are experienced with other databases would never think of. For instance suppose that you open a transaction, read the value, update it, and commit. The transaction guarantees an atomic update so that is perfectly safe, right? Not in Oracle! 2 transactions can start at the same time, read, one updates, the other's write blocks until the transaction finishes, and then it gets to write and commit. The second one never saw the first one's update.

I can easily see someone with experience on multiple databases use your module and not see that they have to do something different for Oracle than everyone else. Worse yet, it seems to work (that is always the fun with race conditions). And then when it goes wrong, if they can track it down they'll blame Oracle for working exactly as Oracle has always been documented to work.

So there is the problem for you. You can add an option to tell people when someone implements Oracle's semantics. You can try to add an explanation of the issue. Of course someone who reads that can't know if they have really forced serialization to happen where they need it to without having Oracle to play with. (And test heavily, race conditions are notoriously hard to detect.)

But that is just one issue with one database. The Sybase issue that I mentioned is nasty. My solution when I worked with Sybase was always to prepare and close one handle at a time, and never, ever use prepare_cached. It wasn't hard to avoid the problem. But fixing a system that already makes the mistake would be a lot more fun.

And no, I didn't get around it by opening up 2 database handles. Because in many versions of Sybase (including the one that I was on), they do page-level locking. (I think that they implemented row-level locking in version 12.) Page level locking means that all sorts of things that shouldn't deadlock, can in Sybase. Deliberately setting up races where you can readily deadlock yourself didn't strike me as a good idea.

So you document this as more stuff that people need to know to really program portably.

Before long you wind up with a document that winds up explaining tons of details of how lots of different databases work. And it all matters.

In the end DBMS portability is not a checkbox that you can just put in a comparison list. Because the abstraction leaks badly. You can offer assistance to people who want to write portable code. You can define a portability problem that you address, and address it. But you can't solve DBMS portability itself because it is intrinsically unsolvable except by forcing people to a very low lowest common denominator.

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

Thanks, I'd clearly misremembered. (Then again I haven't used DBD::Sybase in over a year. Those brain cells are getting rusty...)

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