Re: practical view updates

"Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message news:<3d982821$0$52173$edfadb0f_at_dspool01.news.tele.dk>...
> "Mikito Harakiri" <mikharakiri_at_yahoo.com> skrev i en meddelelse
> news:bdf69bdf.0209291808.6348efe3_at_posting.google.com...
> > jingleheimerschmitt_at_hotmail.com (John Jacob) wrote in message
> news:<72f08f6c.0209281332.3efe8b6d_at_posting.google.com>...
> [lots of snippets]
>
> >
> > In other words, you exclude domain operators from the view update
> > analysis?
> > Then, you miss many practical problems like updating the view
> >
> > select FirstName || ' ' || LastName from customer
>
> How on earth could a view like that be updateable? An if it (due to
> restrictions on FirstName and/or LastName) was updateable, is this kind of
> deduction something we in your opinion should be able to expect from a
> standard DBMS?

How about

select FirstName||LastName, length(FirstName) from customer

? I'm not saying that full equation solving system is something that DBMS desperately need right now. Besides the whole matter already settled queitly with adoption of INSTEAD OF triggers. My proposition is for the programmer to specify inverse view(s), rather than writing INSTEAD OF trigger procedural code. I admit, this is rather small economy, although, the added benefit would be the ability of the system to check correctness, namely that View*InverseView=1. For example,  

select v/2+w/2 as x, v/2-w/2 as y from (

   select x+y as v, x-y as w from xypoint )

can be merged into

select x, y from xypoint

which indicates that xypoint is indeed inverse to vwpoint.

> > > create table Contact
> > > {
> > > ID : Integer,
> > > Voice : String { default "" },
> > > Fax : String { default "" },
> > > key { ID }
> > > };
> > >
> > > create view ContactPhone
> > > Contact over { ID, Voice } rename { Voice Phone } add { "VOICE" Type
> > > }
> > > union
> > > Contact over { ID, Fax } rename { Fax Phone } add { "FAX" Type };
> > >
> > > Not only is this view updatable, but I would argue that it has
> > > precisely the desired affect your example implies is impossible to
> > > determine. The insertion of the row row { 1 ID, "VOICE" Type,
> > > "123-4567" Phone } satisfies the predicate for the left side of the
> > > union, and so is inserted there. The update through the add is
> > > successful because the value of the Type column is indeed "VOICE". The
> > > update through the rename, obviously, succeeds, and the update through
> > > the project succeeds as well (because of the default definitions). The
> > > effect is then the insertion of the row row { 1 ID, "123-4567" Voice,
> > > "" Fax } into the Contact table.
> > ---^^^^^
> >
> > There are 2 problems.
> >
> > 1. NULL value in the Fax column. Mr. Date doesn't admit NULLs, so how
> > application of his rules suddenly contain one?
>
> Actually, there are no NULLs here, just default values.

By "default" value do you mean wrong value? This is the topic that never settled.  

> >
> > 2. It is not a single tuple insertion that must be translated, but the
> > whole transaction.
> Is it so? This puts a much larger burden on the system.
> >
> > insert into phoneview (id, type, number) values (1,'VOICE',
> > '123-4567');
> > insert into phoneview (id, type, number) values (1,'FAX', '555-5555');
> > commit;
> >
> > is equivalent to
> >
> > insert into contact (id, voice, fax) values (1, '123-4567',
> > '555-5555');
> > commit;
> What is wrong with the following translation?
>
> insert into contact (id, voice, fax) values (1, '123-4567','');
> modify contact set fax = '555-5555' where ID = 1;
> commit;
>
> I am not sure if one form is purer than the other. The second form looks
> more consistent from my intuitive point of view.

C.Date suggested eliminate updates from theoretical consideration and use equivalent delete-insert operations instead. Your transaction becomes:

insert into contact (id, voice, fax) values (1, '123-4567',''); delete from contact where ID = 1;
insert into contact (id, voice, fax) values (1, '123-4567','555-5555');
commit;

which is equivalent to mine. Received on Mon Sep 30 2002 - 19:09:55 CEST