Re: TECH: Database independence

From: Samantha Atkins (
Date: Tue Oct 09 2001 - 02:39:02 MDT

Emlyn O'regan wrote:
> Hi all,
> I've got a technical question, so I hope there are some technically minded
> people out there (erm...). Warning to non-IT types... you will likely find
> this entire post entirely pointless, so switch off now!
> 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.

I hear you!

This is actually close to my speciality. I've been doing
persistence middleware and object persistence especially for a
lot of the last 15 years. My speciality is object persistence
and especially through middleware I created. I just finished
the grand opus of this work (unfortunately proprietary to my
employer for now) in Java. The performance is quite good. The
system includes in memory caching, multiple threading, its own
lock manager and an adapter framework for plugging in different
database products. It doesn't matter if they are even of the
same type (all relational for instance). At the top the
applications only see the middleware API. Also included is
builtin support for binary relations, persistent collections and
persistent queues. There is room in the architecture to scale
and distribute the lock manager and cache and to support
language adapters for different software language object and
structure persistence. The system supports dynamically
difining new object/structs whose instances can then be
persisted, queried, related to other instances and so on.

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

What is needed to make a middleware approach work is to
concentrate on the type of persistence services that are needed
for your domain of applications instead of diving down
immediately into SQL blues. Build a good layer for giving that
which sits on top of adapters which abstract out the vagaries
that are driving you nuts.
> In the past I've worked on systems using various approaches to solving this
> problem, from parallel code bases for each database (yes, really!
> urrgghh....), through database tables full of alternate forms of queries for
> each desired database (still nasty to maintain), even apps which try to
> stick to a common simple subset of SQL which works across all dbs (a noble
> but doomed approach). The nicest approach I've used so far was a system I
> built for translating the SQL from one database's dialect to another at run
> time, so that I could stick to one brand of SQL with impunity (within some
> restrictions).

Embedding SQL of any flavor within the application code already
produces cognitive dissonance, known in the industry as
"impedance mismatch".
> I'm really truly sick of this stupid problem popping up all the time, and I
> think it's time to nail it. I've got some ideas for the Grand Solution to
> proprietary database evilness, and I'm ready to go and build these solutions
> myself. However, before I go off half cocked, I was hoping others could
> share their experiences with this common problem, solutions they've
> used/know of. I don't want to reinvent the wheel, but the best that google
> has been able to tell me is that yes, this really is a problem without a
> decent solution, and for which one may buy all manner of half baked partial
> fixes. If people can help me get a decent feel for what is available to
> address this problem, I can move forward in some sensible manner.

Perhaps the above could give you some ideas. If your world is
guaranteed to be always relational then it can make sense to
base middleware first on an ODBC adapter. This gives you the
most coverage the most cheaply.

- samantha

This archive was generated by hypermail 2b30 : Sat May 11 2002 - 17:44:12 MDT