On 10/8/01 8:17 PM, "Emlyn O'regan" <oregan.emlyn@healthsolve.com.au> wrote:
>
> I'm doing YADA work at the moment (Yet Another Database App), and I've run
> into an old problem for which there never seems to be a really good
> solution. This particular app is targetted at RDBMS Brand X (actually it's
> Sybase), and now there is a need to retarget it to another RDBMS. I am
> positive that more databases will creep into the story over time, so this is
> the old database-independent-app problem.
This is a standard problem. I've actually constructed fairly thorough
database abstraction layers (ANSI SQL only gets you so much), and there are
actually two primary problems in my experience: SQL migration and schema
migration.
The schema migration is the worse part, since you may find that there is no
equivalent form for your schema on a different database. This is
particularly true if you start from relatively unrestricted databases (e.g.
DB2, Oracle) and move to a database that has dozens of seemingly innocuous
restrictions and limits (e.g. SQL Server 7) that are violated all over the
place with the original schema.
> The big drama with retargetting the app is the mighty mountain of SQL which
> is database specific. RDBMSs are supposed to follow standards with their SQL
> - yeah, right. While they do look superficially similar, SQL dialects differ
> in fundamental join syntax, in built in functions, in the kinds of primitive
> data types they provide operators for, etc. This can be a really big hassle,
> and from an ongoing maintenance point of view it is big enough to be a
> project killer, especially if it forces a codebase to be forked into one
> copy for each database to be targetted.
There isn't really a nice way to do this. The last time I had to do this
(build an abstraction layer with support for every major RDBMS under the
sun), I built a Java abstraction framework for all database functionality
that was likely to be used by the application. This was supported by a
plug-in cartridges that handled differences between our reference SQL (we
chose ANSI SQL99) and the individual database dialects. The SQL conversion
was then done inside the API from SQL99 to the local dialect of the plug-in
cartridge. This way, all the application code could be written to a single
ANSI SQL target. Adding new plug-in cartridges turned out to be pretty
trivial once the first couple ones had been done, and this system worked
pretty nicely. The APIs had support for many things beyond simple database
abstraction as well, such as caching and persistence, so it turned out to be
a good way to get all the company applications off a particular database
and is used for just about everything these days.
As I said, the biggest problem was migrating existing schema. We started
with an Oracle 8i schema, which turned out to be a major problem when
migrating to feature restricted databases such as SQL Server 7. In this
case there is no choice but to re-engineer the schema to the lowest common
denominator and/or move some database capabilities (e.g. sequence
generation) out of the DBMS into middleware. We did the latter in some
cases and the former in others, depending on what the feature was that
wasn't portable.
Good luck,
-James Rogers
jamesr@best.com
This archive was generated by hypermail 2b30 : Sat May 11 2002 - 17:44:12 MDT