data
A reader, November 12, 2012 - 6:33 pm UTC
Toon Koppelaars, November 13, 2012 - 2:50 pm UTC
"We are studying the opportunity to go to J2EE in order to lighten our DB server"
You will put more burden on your db-server if you are going to use it in a row-by-row approach, which is what JEE is all about.
The bad news is, you'll never be able to convince the new kids on the block of this fact.
business logic making things look pretty
George Joseph, November 13, 2012 - 8:21 pm UTC
Tom
You mention that business logic(making things look pretty) should be done by the application layer.
Does this mean if i have a requirement to show a comma/pipe/; separated values(say account numbers of customer id) in a single column, you would prefer it need to be done at the application layer rather than write a hierarchical query.
Could you elaborate on what is business logic
November 14, 2012 - 8:21 pm UTC
no, you have asked for the data to be in a specific format, you were not asking for it to be in red.
(I'm not convinced this thing called 'business logic' exists as a standalone entity - a lot of it seems to come down to "data logic").
There is the user interface, there is stuff that manipulates the data, there is the data.
User interface shows something in red.
Manipulating data involves connect by sometimes.
Data is the database.
What is business logic code?
Toon Koppelaars, November 14, 2012 - 12:11 am UTC
November 14, 2012 - 8:25 pm UTC
well said
Good article, but I don't agree with this as a standard
Martin, November 14, 2012 - 4:47 pm UTC
•DL-code can also be called by the DBMS via database triggers that fire as a result of the execution of DML-statements originating from wBL-code
As Tom states, put your code in a package where people can see it.
November 14, 2012 - 8:32 pm UTC
uh oh ;) the other TK will probably be back... and that is good.
An example of business logic in PL/SQL
Kim Berg Hansen, November 15, 2012 - 1:40 am UTC
7-8 years ago my company started selling wheels. We built an application to find for a given car which rims and tires in our assortment will fit that car.
It is built in a PL/SQL procedure (packaged) that accepts a car id and returns a cursor variable with all necessary data about the rims, tires, stock level, delivery time and so on. (Actually the stored procedure basically is just an "open out_cursor for <huge select statement>" :-)
That procedure is called from two widely different environments:
- Our old legacy unixbased system calling the database via OCI. Salespeople use this in VT220 characterbased terminal emulation to sell wheels to customers in the physical shop.
- Our webshop on classic VBscript ASP in IIS using OO4O to call the database. Here the asp code calls the procedure and creates HTML and script code for a graphical WheelGuide(R) letting customers view the possible wheels on drawings of their car model.
The webshop WheelGuide(R) was originally written in JavaScript, but that was rather slow image manipulation. So after a few years it was re-created in Flash (much quicker.) Changes needed in the PL/SQL: None!
Now the WheelGuide(R) is re-written yet again to use HTML only with a completely different layout making it much better on iPads. Changes needed in the PL/SQL: None!
The PL/SQL has had just a few tiny refinements over those years - either to improve the logic or add extra functionality.
But the logic is easily reused in more than one GUI environment and can survive the changes to the web environment.
I'll go on writing SQL and PL/SQL for my logic - it'll survive whatever is the newest buzz in GUI application design ;-)
November 19, 2012 - 9:09 am UTC
I've said many times - if you want to really achieve portability and code reusability, you'll use the heck out of the stored procedure language in your database of choice - you'll put as much as you can in there - simply because the UI (user interface) languages like Java, PHP, etc change so often as does the architecture (from three tier in the 80s and before, to client server, back to three tier (but with too many types of middle tiers to count), to mobile, to ....)
Harmful triggers...
Toon Koppelaars, November 15, 2012 - 7:35 am UTC
> •DL-code can also be called by the DBMS via database triggers that fire as a result of the
execution of DML-statements originating from wBL-code
>
> As Tom states, put your code in a package where people can see it.
>
> Followup November 14, 2012 - 8pm Central time zone:
> uh oh ;) the other TK will probably be back... and that is good.
Short answer: triggers have several use-cases. Some of them are harmful, one of them isn't. Unfortunately the latter use-case (using them to implement DL-code) is rather difficult to be executed correctly: need to deal with transactions executing at the same time, etc. But it *can* be done efficiently and correctly.
Btw. you need to deal with TX's executing at the same time in your package too, do not forget that.
I agree with Tom on the former use-cases.
Tom agrees (albeit somewhat reluctantly I sometimes think :-) ) on the latter use-case.
Long answer: harmfultriggers.blogspot.com (I know, I know it's not done yet).
Final answer: Oracle to support the ISO/ANSI assertion concept.
mfz, November 15, 2012 - 8:33 am UTC
<quote>
( The bad news is, you'll never be able to convince the new kids on the block of this fact. )
</quote>
I agree to this statement 100 % .
Even last week , I was in a project kick off meeting . I was trying to convince the dev. team to use stored procedures ( as XAPI ) for business/ data logic . Use Database functionality as much as possible .
But in the end , they are reluctant to use DBMS features; instead they are going fot MVC 4 architecture ; being that is the latest trend in the application development.
I really wish , these developers understand the concept / longevity of the data ; not just few buzz words .
my head hurts
Michael Kutz, November 15, 2012 - 8:51 am UTC
Toon,
As neat as 'assertion' seems, I will never use it.
It seems to be worst than triggers.
How would it even work?
Due to 'transactions', you really can't 'assert' something (that crosses tables and rows) on a single DML statement.
I mean, what happens if I a large data set and I need to 'insert' the 'manager' before the 'clerk'?
Do i get the 'assertion fail' at the row level?
Or do I get it at the 'commit' time?
If at the row level, FROM EXPERIENCE, this will be slow=true.
(god forbid if the assert's select statement does a full-table scan on a 30GB table)
If at the 'commit' level.. what happens if it fails?
does it do a 'rollback'? and do you really think the 'new kids on the block' are actually going to catch that error and handle it correctly?
Heck, what would happen if someone said "hey, all clerks need to have a manager"?? Instant DoS via Catch 22.
You can't insert a 'clert' without a 'manager', you can't insert a 'manager' without a 'clerk'.
The only SAFE way i see is:
begin
-- bulk load data.. THINK IN SETS!!!
insert ... select * from new_dept_temp;
insert ... select * from new_employees_temp;
assert_api.managers_need_clerks();
assert_api.clerks_need_managers();
-- depending on application
commit;
end;
November 19, 2012 - 9:47 am UTC
.... If at the row level, FROM EXPERIENCE, this will be slow=true.
(god forbid if the assert's select statement does a full-table scan on a 30GB
table) ...
do you use constraints?
because you just described them...
think "unindexed foreign key" and a delete on the parent table (with a 300gb child table..)
assertions would be statement level - and probably permit for deferring just like a constraint - if they were implemented.
they are a rather cool idea in my opinion.
why would clerks and managers be a catch 22? Unless you had a rule that said every manager has to have at least one clerk and every clerk has to have a manager - in which case either
a) a single sql statement to create both the manager and clerk
b) a deferrable assertion
business logic
George Joseph, November 16, 2012 - 12:43 am UTC
Thanks Toon and Tom.
The blog was of excellent reading material of what is business logic.(anything not UI or Data logic is business logic)
I am curious why you would call your blog Helsinki declaration :-)
Why it's called like that?
Toon Koppelaars, November 16, 2012 - 8:50 am UTC
@Michael Kutz
S, November 16, 2012 - 9:39 am UTC
Michael, you state that you'll never use assertions and then go on to show how you would enforce a condition. Questions:
1) Why don't you trust that the DBMS that you paid a substantial amount of money for cannot do it the right way?
2) Why do you think you can enforce a constraint better than your DBMS in a general case?
S
Amine, November 17, 2012 - 6:47 am UTC
...If you have an EJB calling a stored procedure - you are three tier, completely...
Could you explain me in detail how does this work ? Because our teacher said that when using SP you're still in C/S architecture...
THanks in advance Tom,
November 19, 2012 - 10:21 am UTC
client server is when a program running on a client machine connects directly to a database - there would be a one to one relationship between instances of the program running on the client and database connections. People would connect to the database as themselves - using their own username and password.
How in the world could a web browser - connecting to an application server - that connects via a connection pool to a database - be confused with client server like that???
running a stored procedure is no different than running a sql statement physically - web browser -> app server -> database, I don't care of the sql the app server submits is 'select * from emp' or "call get_emp_data()" - you are still three tier.
Three tier is an architecture that lets you take a large client base and multiplex them down into a small set of connections to a shared resource such as a database.
It is something we did in the 80's and before - ISPF green screens -> CICS -> IMS/IDMS/whatever.
client-server means "client connects to server, one to one"
three tier means.... client connects to transaction monitor connects to database.
but the really cool thing is - you could be three tier or client server with a single code base - if only you actually use stored procedures!!! because you can call the same bit of data logic from your app server, that you can from your phone, that you can from your client server app and so on..
that is - if you want portable reuseable code, you would use the heck out of your database of choice's stored procedure language!!!
Three Tier
djb, November 18, 2012 - 2:04 pm UTC
Amine, You are still three-tier because:
Client <-> EJB <-> DB Stored Procedure
The stored procedure is, among other things, a layer of protection to the actual data.
Amine, November 19, 2012 - 10:34 am UTC
clearly and well said Tom, Thanks again !
Thought-provoking
Michael, November 21, 2012 - 5:23 am UTC
Listen to what Amine said:
"Last week, I've got an Oracle University course about building ADF Faces clients for EJB and JPA."
...
"Could you explain me in detail how does this work ? Because our teacher said that when using SP you're still in C/S architecture... "
So, that is what Amine was told at Oracle University!
Might be an isolated case - but anyway, apprehensive and disturbing that is...
(And wrong)...
Assertions
Toon Koppelaars, November 21, 2012 - 6:19 am UTC
Michael,
I explain in detail in "Applied Mathematics for Database Professionals" (chapter 11 I think), how a DBMS vendor would have to support assertions for you (and me) to use them.
Your preassumption is that the assertion *text* as is, is used to perform the check. That of course should not be the case. The DBMS should be smart enough to a) given an assertion, and b) given a transaction, determine 1) if at all the assertion needs to be checked, and 2) if so, how to do that in an efficient manner. And in a manner that supports simultaneous transactions.
Checks, primary keys and foreign keys, are just special cases of assertions. They are types of constraints that are so ubiquitous in every database design, that the SQL standard has introduced special case shorthand notation for them. This has enabled DBMS vendors to produce special case constraint-validation code inside their engines for them.
A check such as CHECK(job='MANAGER' or SALARY < 9000), can be asserted as:
create assertion CHECK_EXAMPLE as
(not exists(select 'a violation'
from EMP e
where not(e.job='MANAGER' or e.SALARY < 9000))
)
/
A primary key such as constraint EMP_PK primary key (empno), can be asserted as:
create assertion EMP_PK as
(not exists (select 'duplicate rows'
from EMP e1
,EMP e2
where e1.EMPNO = e2.EMPNO
and e1.rowid != e2.rowid)
)
/
A foreign key such as constrint EMP_DEPT_FK foreign key(deptno) references dept(deptno), can be asserted as:
create assertion EMP_DEPT_FK as
(not exists(select 'an emp'
from EMP e
where not exists(select 'a matching dept'
from DEPT d
where d.DEPTNO = e.DEPTNO))
)
/
When declared as assertions, I would expect the DBMS to produce the same efficient validation code as currently it does with the 'special case' syntax. This requires sophisticated parsing and analysis of the assertion text. The real challenge (for DBMS vendors) is to produce the special case constraint-validation code in-flight when an assertion is declared.
If Oracle ever gets around implementing this: a bright future would lie ahead. There would be light at the end of the current midtier centric Java tunnel. And SQL DBMS's would have evolved a significant step towards being a relational DBMS's.
Toon
CREATE ASSERTION is a solved problem
E. Smout, July 22, 2013 - 10:13 am UTC
CREATE ASSERTION is a solved problem.
But the world doesn't really seem to want to believe that.
Anyhow, with CREATE ASSERTION solved, the need for coded SPROCS for the purpose of database constraint validation goes away, as does the need for materialized views for the same purpose, as does the problem of existing limitations on materialized views.
July 31, 2013 - 4:11 pm UTC
ok, i'll bite.
how is it a solved problem?
How CREATE ASSERTION is a solved problem.
Erwin Smout, October 02, 2013 - 7:28 pm UTC
Sorry about the delay. Not been checking here for a while for personal reasons.
Solved as in "I know how to enforce any arbitrary database constraint efficiently in a relational DBMS.". Getting to the solution has taken me several years, so don't expect me to just go give it away here and now.
I recently submitted a somewhat elaborated article on the subject to the NOCOUG journal, and the result of my work is publicly available as a fully operational system for anyone to try out.
Is there anything more specific (other than "give me the algorithm") covered by that "how" question you would like to know ?