practical view updates

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 29 Sep 2002 19:08:22 -0700
Message-ID: <bdf69bdf.0209291808.6348efe3_at_posting.google.com>

jingleheimerschmitt_at_hotmail.com (John Jacob) wrote in message news:<72f08f6c.0209281332.3efe8b6d_at_posting.google.com>...
> > I agree: those are just a set of ad-hock rules hardly illuminating
> > anything about the problem.
> This sounds like a criticism of the view updatability research of
> Date, McGoveran, Darwen, and many others. If you have a better
> mechanism for updating views, one that is less "ad hock" by all means
> enlighten us. Otherwise, you are not contributing to the solution of
> the problem.

Here are at least 2 missing points:

1. Definition. A view T is inverse to a view S iff T*S is identity map. For example,

create view vwpoint
select x+y as v, x-y as w from xypoint

is inverse to

create view xypoint
select v/2+w/2 as x, v/2-w/2 as y from vwpoint

Indeed,

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 reduced to a view

select x, y from xypoint

which is identity map. Likewise,

CREATE VIEW voicefax AS
select a.id, a.number voice, b.number fax from phone a, phone b
where a.id = b.id
and a.phonetype = 'VOICE'
and b.phonetype = 'FAX'

is inverse to

create view phone as
select id, "VOICE" phonetype, voice number from voicefax

   union
select id, "FAX" phonetype, fax number
from voicefax

If we know inverse view, then resolving view updates is essentially a mechanical task. For example, a transaction

delete from vwpoint where w*v=0;
insert into vwpoint (v,w) values (0,1);
insert into vwpoint (v,w) values (1,2);
commit;

becomes

delete from xypoint where (x,y) in

   (select v/2+w/2,v/2-w2 from vwpoint where w*v=0) insert into temporary (v,w) values (0,1); insert into temporary (v,w) values (1,2); insert into xypoint

    select v/2+w/2 as x, v/2-w/2 as y from temporary delete from temporary;
commit;

2. We need to broaden narrow perspective of updating a single view to that of a set of views. For example, a view

create view Expensive
select ename, sal from Emp
where sal in (select max(sal) from emp)

is clearly not updateable. A set of views

create view Expensive
select ename, sal from emp
where sal in (select max(sal) from emp)

create view Others
select ename, sal from emp
where sal not in (select max(sal) from emp)

however, allows to define inverse view as

select ename, sal from Expensive
union
select ename, sal from Others

Then, a transaction

delete from Others where ename = ‘Smith'; insert into Expensive values (‘Smith', 10000); insert into Others values (‘John', 5000); commit

could be resolved into

delete from Emp where (ename, sal) in

   ((select ename, sal from Expensive

     union
     select ename, sal from Others) where ename = ‘Smith');

insert into Etmp (ename, sal) values (‘Smith', 10000); insert into Otmp (ename, sal) values (‘John', 5000); insert into Emp

   select ename, sal from Etmp
   union
   select ename, sal from Otmp;
delete from Etmp;
delete from Otmp;
commit;

> > For example, is a view
> >
> > select x+y, x-y from point
> >
> > updateable?
> First, let me point out that you are not defining views here, nor
> indeed are these relation-valued expressions. Let me frame the
> argument in a relational language before I respond to it.
> create table Point { X : Integer, Y : Integer, key { X, Y } };
>
> create view PointView
> Point
> add { X + Y XPlusY, X - Y XMinusY }
> over { XPlusY, XMinusY };
>
> The projection is not updatable because not enough information can be
> obtained from the result to construct an update against the base
> relation variable. This is true for projections in general, unless of
> course, the base relation variable defines default values to fill in
> the missing information. If we remove the projection:
>
> create view PointView
> Point
> add { X + Y XPlusY, X - Y XMinusY };
>
> The view most certainly is updatable and the update will only fail if
> the constraints for the view are not satisfied, namely, the value for
> the column XPlusY must be equal to the evaluation of X + Y, and the
> value of the column XMinusY must be equal to the evaluation of X - Y.

The conclusion "XPlusY must be equal to the evaluation of X + Y" seems trivial to me, unless the sole purpose of your exercise was demonstrating "superiority" of Mr.Date relational syntax.

> If you are implying by these examples that the system should somehow
> be able to infer the values of X and Y, you are no longer talking
> about view updatability, rather you are talking about theorem proving.
> We made no such claims. Please restrict your criticisms of our product
> to claims we have made.

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

> > Even if we don't allow domain operators, what can we infer about
> >
> > select id, 'VOICE' type, voice phone
> > from contact
> > union
> > select id, 'FAX' type, fax phone
> > from contact
> >
> > ?
> It is with particular pleasure that I dissect this example as it is a
> perfect illustration of the power of our view updatability mechanism,
> precisely because of the "ad hock" theories on which it based. Again
> let me frame the argument in a relational context:
>
> 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?
2. It is not a single tuple insertion that must be translated, but the whole transaction.

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;

It is obvious from inverse view perspective, but I admit that deriving inverse view itself is hard.  

> > Therefore, claim like "The Dataphor DAE fully supports view
> > updateability" is just naive marketing.
>
> Therefore, we stand by our claim that the Dataphor DAE fully supports
> view updatability.

Again, fully supported except domain operators? Fully supported in purely relational domain, except for the problems like in the example above?

> Furthermore, it is apparent from your remarks that
> you have not even attempted to test your hypotheses against the
> Dataphor product. So your claim that we do not support updatability is
> just naive criticism. The Dataphor product is available for public
> evaluation from our site www.alphora.com. If you doubt the validity of
> our claims, by all means, try it out. Please refrain from making
> public criticisms of a product which you clearly know nothing about.

Well, my criticism is not up to high standards, but neither does product promotion in a theory newsgroup.

I must correct my statement: Mr.Date's view updates coverage in "The Introduction to the Databases" is inspiring, but, nevertheless, his solution barely scratches the surface of the problem. Received on Mon Sep 30 2002 - 04:08:22 CEST