Re: OO and relation "impedance mismatch"

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Tue, 05 Oct 2004 17:25:58 -0400
Message-ID: <6h3vjc.js4.ln_at_mercury.downsfam.net>


Tony Andrews wrote:

> Kenneth Downs wrote:

>> You always want to err on the side of correct data, which leads to

> dirty
>> reads, which leads to the possibility that you will have spurious

> failures.
>
> I'm actually more concerned about spurious successes than spurious
> failures. I use Oracle, which doesn't even do dirty reads, so I am not
> sure exactly what would happen with dirty reads in this scenario:
>
> User A> insert into parent (pid) values (123);
>
> User B> insert into child (pid, cid) values (123, 456);
> -- dirty read sees user A's record and allows the insert
>
> User A> rollback;
>
> User B> commit;
> -- User B's child record is now an orphan.
>
> Could that not happen with your trigger-based approach?

Hmmm, good question. At a first glance, the unique constraint should do a dirty read and the RI should not. Uniques need to err on the side of catching all possible violations, hence the dirty read. RI needs to err on the side of not giving an OK on a value that may not yet be committed, so you do the opposite.

If the constraints are coded this way, user b's RI will fail, as it should.

Thanks for raising the point.

>
> Another question: how can you generate the code for your triggers from
> the data dictionary, when your data dictionary doesn't know about
> primary keys, unique keys, foreign keys?

Since I designed my dd, I put the keys into it. As stated in the other sub-thread on this, by dd I mean my own set of information, not anything provided by the vendor.

>
> Why not (just to please me!) create all the proper, declarative
> constraints AND generate your triggers from them.

Because if the declarative constraint exists you cannot control the DBMS's implementation of it, you cannot control firing order, and so you have given up the only motivation for the trigger in the first place, which is uniform error handling.

> That way you can
> present the errors in the way you like, without (I believe)
> compromising data integrity.

If it compromises data integrity it is out the window no matter what other advantages it may have. In the battle between my preference for show-me-all-errors and the absolute requirement for valid data, my preference will always lose. I have not yet determined if the trigger is worth the price.

To recap:

My use of triggers is currently experimental. I know for a fact that I will trade performance to get my preference for show-me-all-errors. I believe *intuitively* that I can get 100% data protection, but I have not proven it yet to my own satisfaction. Stay tuned, you will undoubtedly hear more from me as I work on the project.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me
Received on Tue Oct 05 2004 - 23:25:58 CEST

Original text of this message