Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: choices regarding where to place code - in the database or middletier

Re: choices regarding where to place code - in the database or middletier

From: Noons <>
Date: Fri, 30 Jan 2004 22:41:07 +1100
Message-ID: <401a42f4$0$5224$>

"Daniel Morgan" <> wrote in message news:1075442171.779310_at_yasure...

Sorry folks, I'm reducing the number of x-posts otherwise my ISP will kill my replies: thank God ONE provider is taking an active stance against spam on Usenet, I say!

> By selecting as your example: "A web-page that displays" you eliminated
> anything having to do with sequences. What I was trying to get you to
> deal with was issues related to insert, update, and delete. Issues that
> involve sequences but also the transaction and locking models that are
> totally different between commercial database products.
> So the response while impressive is unsatisfactory in that it didn't
> address the issue. And the stuff about Project Marvel ... as marvelous
> as it may be ... has nothing to do with the back-end database ...
> HTML_DB is a front-end tool. Could I impose upon you to try again this
> time addressing this issues around transaction processing.

I can see your point. But I think you are approaching it from the wrong angle.
The whole argument about wrappers is precisely to hide an implementation from a design requirement.

The concern is not if one database has a sequence and another has an auto sequence column.

The (design) concern is: I want a surrogate key on EVERY relational row storing my object data and an ID for my persistent object instance data. Period.

Now, how do I implement this? Well, I create a wrapper that says: give me the *object* instance data and I'll store it on a number of tables (or one single table, none of your business!).

The wrapper now calls Servant_Store_My_Object() *IF* I'm running this middleware
against a Microslop databases. It will instead call Larry_Store_My_Data_Cheap() *IF*
I'm running this against Larry-base 10g. And Itty_Bitty_Wake_Up_And_Store_This()
*IF* I manage to run this against ASS400-2. A config toggle, stored in XML.

Now, how do I code EACH one of those interface/class modules above? For the sake of
example, the Microslop one will just do a plain insert, then interrogate the row
just created to return the generated autogen key back to the caller (as part of a confirmation that the row(s) has/have indeed been stored). The Larry-base one simply calls a package that handles all access to the table(s)
and INSIDE the package it'll do whatever has to be done to use a sequence and return
a surrogate key with the data. The ASS400-2 will probably use sacrificial virgins.

Now, this whole thing is (partially) what is called Object-Relational mapping and is
what has been *missing* from this entire discussion. You CANNOT hope to answer or solve
any design problem or issues between OO programming and Relational DB storage *without*
that mapping up-front. It's like trying to shove snow with a toothpick.

Once you start from the OO-relational mapping, then the problem is of simple solution
as I'm quite sure you have already seen.

Wrappers deal with and handle impedance mismatches, which is what this problem is all

What also must be defined is WHICH PORTIONS of the OO-relational mapping should be
executed in the middleware and which ones need to be executed in the database.

If the database does or does not support one specific data-storage feature or runs
more efficiently using ANSI-SQL stored code, PL/SQL, Transact-SQL or square wheels
is totally immaterial (or should be...) to the middleware people.

My contention: Use whatever feature of each database better handles the required
functionality. Do *NOT* code the handling of thsi feature in the middleware. Code
the handling in whatever makes more sense to the database engines you have to

THEN, write a wrapper that "understands" how to call each interface to each specific database and get it to do the dirt work. That wrapper should run in
the middleware and should NOT assume that each object is gonna be stored in a
single row!

It should be concerned only with persisting or retrieving an object hierarchy by
calling the adequate class methods for EACH supported database. Or performing
a data transformation on that hierarchy and persisting that transformation to
the dbs. And any other performance specific functionality that is needed on a
OO-relational map. Like, NOT considering a join as a method of an object but an
interface to another object that represents the result. That way one can do proper efficient joins in the database. Instead of the moronic and usual "I'll
do the joins in middleware thank you". Responsible for so many disasters out there...

This can of course be extended for even more modularity, but the gist of the principle
is this. Keep the object handling in the middleware and the data storage specifics
in the database. The bit in the middle can stay in the middleware. Or for full
scalability, it may be split between handling the object mapping on the app server
and handling the storing of that object data in the stored database code. In the middle,
you pass (in old language) records. Record types. Supported by just about every
database with stored code execution.

And so on... The subject is quite extensive.

Nuno Souto

> I still content that you can't have your cake and eat it too.
but let's try, no? ;) -- Cheers Nuno Souto
Received on Fri Jan 30 2004 - 05:41:07 CST

Original text of this message