Re: Any reason not to have logic in the db?

> What I want to know is: Is there any reason whatsoever
> to have logic outside the database that could potentially
> be kept there - notably CHECKs, CONSTRAINTs,
> TRIGGERs, containing possibly complex PL/SQL - i.e.,
> it's sort of a question about App-tiers vs. DB.
No, never, not under any circumstances at all. Other opinions are available, however, but they are wrong! ;-)

> My own opinion (to put my cards on the table) is that
> "logic" should be stored as far down the stack as possible
> (DB, first, then app-server...&c).

Agreed. The best place to protect and keep the data secure, is within the database.

> Anyone know of any counter-examples?

Yes, vendors of "database agnostic" applications which simply don't work. (Except for those developed in Uniface - because Uniface does it right!)

Check constraints in the application, in fact, any constraint in the application is fine, provided that the constraints are (already) in the database.

I don't mind the application checking and validating data before it gets to the database so that the user gets a better error message than the database may chuck back, but the data has to be validated by the database before storage and commit.

Any application which does all the constraint and trigger work and simply uses the database as a bit bucket for the data is a system asking for trouble. And when trouble hits, it's us DBAs who get called out at stupid o'clock to fix things - even though we said "don't put that pile of cr4p on our servers" in the first place!

Do I sound bitter? I'm not - my call out rates are obscene! :-)

Data are the most important thing to the business, the data must be protected. That means arranging everything possible to protect the data as close to the data as possible.

After all, these application usually work on the principle that it was the application which put the data into the database so when it is read back, it *must* be correct. Except that because there are vendor supplied patch scripts and possibly the odd occasional DBA run script that directly puts data in or out of the database - completely bypassing all constraints etc - we get application failures when the data are not exactly as expected when reading back from the database.

And because the application was expecting perfection, there are little or no exception handlers to inform the user of what happened, and the pile of mess gets bigger and bigger until, eventually, it barfs, big time. Call out the DBAs again, it has to be a problem in the database. Yeah right!

<end rant>

Now, in addition and also, what about code? I like to see all the code in the database too. Personal preference because when I get called out, I don't have the time or skills to decompile a Java application (which I won't go off on another rant about!) running under WebLogic to see what the hell it does to get into the mess it's now in. I don't need that sort of stuff at stupid o'clock!

Ok, some vendors are so ashamed of their code that they have to hide it within the Java, it's not a major problem, I'll just trace the execution with 10046 or similar, and see exactly what it's doing anyway - the vendors are usually unable to use something like "wrap" to obfuscate their code anyway!

Let's face it, running compiled PL/SQL for example, within the database, running on a hugely powerful database server can't be any faster than running byte-code interpreted Java on a lesser WebLogic server can it? (Oh yes it can vendors!) Or, as is sometimes the case, client side!

So, finally and in conclusion, apologies for ranting, and no, there are no good reasons for having constraints etc outside the database.

Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l