I start feeling traditional after i see each one of these SQL producing database abstraction layers and all sorts of individuals ORMs available, although I'm not even close to being old. I realize the requirement for them, however their use propagates to places they normally don't fit in with.

I firmly think that using database abstraction layers for SQL generation isn't the right way with words database programs which should operate on multiple database engines, particularly when you toss in really costly databases like Oracle. Which seems global, it does not affect merely a couple of languages.

Only a simple example, using query pagination and insertion: when utilizing Oracle you could make use of the FIRST_ROWS and APPEND hints(where appropriate). Likely to advanced good examples I possibly could mention investing in the database plenty of Saved Methods/Packages where it seems sensible. And individuals will vary for each RDBMS.

By utilizing merely a limited group of features, generally open to many RDBMS one does not exploit the options that individuals costly and advanced database engines need to offers.

So returning to one's heart from the question: how can you develop PHP, Python, Ruby etc. programs which should operate on multiple database engines?

I'm especially interested hearing the way you separate/make use of the queries which are especially written for running on one RDBMS. Say there is a statement which should operate on 3 RDBMS: Oracle, DB2 and Sql Server as well as for all these you are writing another SQL statement to be able to take advantage of features this RDBMS needs to offer. How can you get it done?

Letting this aside, what's you opinion walking this path? Could it be worthwhile inside your experience? Why? Why don't you?

If you wish to leverage the features of numerous RDBMSes, you are able to certainly get it done. Just apply standard OO Concepts. Evaluate which type of API your persistence layer will have to provide.

You'll finish up writing some isomorphic persistence adapter classes. In the outlook during your model code (which is calling adapter techniques to load and store data), these courses are identical. Writing good test coverage ought to be easy, and good tests can make existence a great deal simpler. Determining just how much abstraction is supplied through the persistence plugs may be the trickiest part, and it is largely application-specific.

For whether this really is worthwhile: it is dependent. It is a good exercise if you have never tried it before. It might be premature if you do not really know without a doubt what your target databases are.

A great strategy may be to implement two persistence plugs to begin. Let us say you anticipate the most typical back finish is going to be MySQL. Implement one adapter updated for MySQL. Implement another that utilizes your database abstraction library of preference, and uses only standard and broadly available SQL features. Now you have support for a lot of back finishes (everything based on your abstraction library of preference), plus updated support for mySQL. Should you decide after this you wish to offer an enhanced adapter from Oracle, you are able to implement it whenever you want, and you will realize that the application supports swappable database back-finishes.

You can't consume a cake and also have it, choose on from the following options.

  • Make use of your database abstraction layer whenever feasible as well as in the rare times when you need to have a hands-made query (eg. performance reasons) stay with the cheapest common denominator and do not use saved methods or any proprietary extensions that you simply database needs to offer. Within this situation implementing the applying on the different RDBMS ought to be trivial.
  • Make use of the full energy of the costly RDBMS, but consider that the application will not easily be portable. Once the need arises you'll have to spend considerable effort on porting and maintenance. Obviously a good layered design encapsulating all of the variations in one module or class can help within this endeavor.

Quite simply you should look at how probable one thing the application is going to be used to multiple RDBMSes making an educated choice.

It is always good if code written for just one platform works on almost every other with no modification whatsoever, but normally, this is not the situation and most likely won't be. Exactly what the current frameworks do is all about all anybody can.

It's much more "traditional" than modern ORMs, but does not ODBC address this problem?