Thanks for this, Samantha. I've worked on object persistence middleware
before, and am a big supporter of it. Unfortunately, most project which are
smaller than "BIG" don't seem to see the need for such infrastructure. I
commonly work on contract, and come in after things are well underway; just
about that time in every project's life when the iron clad guarantee that
"we will only ever use Brand X database" has rusted out.
What would be most useful from this retro fit perspective (besides five
minutes alone with those responsible and a baseball bat) would be something
which could translate SQL from one flavour to another. Of course as James
has pointed out, you still have schema mismatch to contend with, but
normally you can use a lowest common denominator approach to schema
implementation which will work across DBs and give you equivalent looking
schema to work with which will be amenable to translated SQL. This is in
comparison to trying to find a subset of SQL which works across platforms,
which will work if all your statements are of the form "select * from x",
and nothing more complex; ie, it's doomed to fail.
I've responded to you inline below:
> -----Original Message-----
> From: Samantha Atkins [mailto:samantha@objectent.com]
> Sent: Tuesday, 9 October 2001 18:09
> To: extropians@extropy.org
> Subject: Re: TECH: Database independence
>
>
> 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.
>
This is my usual approach. For instance, in the project I'm working on right
now, there is a middle layer which hides all SQL grubbiness from the clients
behind an API. Having said that, there is still a layer somewhere which
needs to actually provide a (really very large) bunch of SQL statements to
do the communication between the lowest non-db layer and the db; for multi
db support, you need to provide equivalent translations for each statement;
an excellent source of bugs and always a maintenance problem.
> > 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".
>
It's not too modular, is it? :-)
> > 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
>
Happily, I can usually count on the base db being relational.
Usually I work in Delphi, and use some excellent drivers called SQL Links
drivers; they crap all over ODBC performance wise (which isn't too difficult
of course). Otherwise, ADO is usually the way I end up going.
These do everything for you except actually giving you a single SQL dialect
to work with. Which is the biggest problem.
Emlyn
***************************************************************************
Confidentiality: The contents of this email are confidential and are
intended only for the named recipient. If the reader of this e-mail is not
the intended recipient you are hereby notified that any use, reproduction,
disclosure or distribution of the information contained in the e-mail is
prohibited. If you have received this e-mail in error, please reply to us
immediately and delete the document.
Viruses: Any loss/damage incurred by using this material is not the sender's
responsibility. Our entire liability will be limited to resupplying the
material. No warranty is made that this material is free from computer virus
or other defect.
This archive was generated by hypermail 2b30 : Sat May 11 2002 - 17:44:12 MDT