Re: is pivoted phones view updateable?

Aloha Kakuikanu wrote:
> Tchuzhie Giri wrote:
> > It is not "OK" so long as one does not have your rules of updatability.
> > You put a pile of symbols on the pile of symbols: it does not have
> > sense without the rules. Please provide the rules, simple examples
> > and then one can go to more complex examples.
>
> Given two relation variables
>
> Y(n, #) -- aka WorkPhones(name, number)
> and
> Z(n, #) -- aka CellPhones(name, number)
>
> and two relation constants
>
> A = {(t='work')}
>
> and
>
> B = {(t='cell')}
>
> is the view
>
> X = (Y /\ A) \/ (Z /\ B) -- aka ConsolidatedPhones
>
> updateable? There is no minus operator involved. The only two RA
> operators are join /\ and union \/.

When writing an article about view updates, found some old (Nov 2000) unpublished article about view updates. Here it goes:

Database View Updates

Introduction.

Relational databases live longer than applications that use them. In a long database lifetime its schema inevitably changes. Those changes often break existing applications unless application developers provide some kind of logical data independence. Using PL/SQL packages as an interface to the database data became ubiquitous approach in today's practice.

Database Views is an alternative way to achieve logical data independence. In theory, database views are superior to any procedural solution (such as PL/SQL package interfaces), because views have fundamental relational closure property. This statement, however, is seriously undermined by existing limitations on view updates.

Let reader not to receive a false impression that this is pretty abstract subject interesting to experts only. The article goes through an example of schema change that author found repeating over and over again during his work at several application development projects. And the purpose of this whole exercise is to show how to use views such that the reader would be saved of writing tedious mechanical procedural code.

Schema evolution example

During database lifetime its schema encounters several growth and restructuring changes:

� Expansion of a table to include new column, or modifications of existing column datatypes.

�	The inclusion of a new table.
�	Restructuring that, although preserves overall information, changes

Table restructuring is a main theme of this article. In a typical example the table
TABLE contact (

id             NUMBER,
voice          VARCHAR2(10),
fax            VARCHAR2(10)

might require additional cellular column. Instead of growing the table, however, it is better to reorganize it like this:

CREATE TABLE newcontact (

id              NUMBER,
phonetype       VARCHAR2(5),
number          VARCHAR2(10)

Each record in the old contact table

ID	VOICE	FAX
1	4150000000	4081111111
2	80012345672	6501234567

is equivalent to 2 records in the newcontact table

ID	PHONETYPE	NUMBER
1	VOICE	4150000000
1	FAX	4081111111
2	VOICE	8001234567
2	FAX	6501234567

Clearly, if we simply drop the old table and naively assign contact name to the new table it would break existing applications. Let's take a look how we can handle this problem.

Mapping View

The view
CREATE VIEW oldcontactview AS
select a.id, a.number voice, b.number fax from newcontact a, newcontact b
where a.id = b.id
and a.phonetype = 'VOICE'
and b.phonetype = 'FAX';

transforms the data in the newcontact table to look just like the old contact table. If we drop the old contact table and use a synonym to this view instead of the old table, then, nothing have to be changed in the applications that still use old contact name.

There is one technical problem, though. View oldcontactview is not modifiable, and any attempt to insert, update or delete a row would fail. Update triggers, therefore, are essential for full backward compatibility of this schema transformation. Here is the code that works in our example

CREATE TRIGGER oldcontactview_insert
INSTEAD OF INSERT ON oldcontactview
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
  insert into newcontact values
(:n.id, 'VOICE', :n.voice );
  insert into newcontact values
(:n.id, 'FAX', :n.fax );
END; (I omitted update and delete parts in order to save the space).

Writing update triggers might be the end point in the traditional database schema redesign project, but I'm still left somewhat unsatisfied with this solution. Writing even that small amount of trigger code (actually, 3 times more than that;-) makes the whole view approach less appealing. I'll demontrate in the next section, that oldcontactview is fully updatable, though, only if we extend Oracle definition of what updateable view is.

Inverse View

Oracle documentation introduces ambiguity as informal criteria for deciding if a view is modifiable. Basically, if update operation on a view could be interpreted in different ways how could database server know what operations on the base table(s) to execute? Of course, since programming reference manual need to be precise, this informal idea has been narrowed down to a rigorous definition of a view based upon key-preserving table. Omitting the details, I'll just say that oldcontactview doesn't fall into this category and, therefore, is not modifiable. (this is why I had to write update trigger in the previous section). Are update operations on oldcontactview ambiguous, though? If they are not, then how could we extend the definition of what modifiable view is?

Trigger code that we wrote in previous section have no resemblance to oldcontactview definition. Where those 2 insert operations came from, then?
To answer these questions let's declare another view:

create view NEWCONTACTVIEW as
select id, "VOICE" phonetype, voice number from contact

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

This view is exactly the opposite of oldcontactview. While oldcontactview defines what data must be in the old table if we know the content of the new table, the newcontactview defines new table data if we know the old one. Yes, this view is definitely useful, especially if we want to migrate the data from old table to new one, but I have more advanced application in mind. I reserve new name for it - let's call it an inverse view, while the original oldcontactview would simply be a direct view. Formally, an inverse view is such a view that applying it after direct view will restore the content of the base table that the direct view uses in its declaration.

Modifiable view definition

Thesis: A view is modifiable if an inverse view exists. To justify this thesis I'll demonstrate that writing "INSTEAD OF" trigger is simply a matter of embedding inverse view into trigger's body. Some auxiliary table

TABLE oldcontact (

id             NUMBER,
voice          VARCHAR2(10),
fax            VARCHAR2(10)

is required, since the new record data must be put into some kind of table before they could be used by a view's select statement

CREATE TRIGGER oldcontactview_insert
INSTEAD OF INSERT
ON oldcontactview
REFERENCING NEW AS N OLD AS OLD
BEGIN
insert into OLDCONTACT values

    (:n.id, :n.voice, :n.fax);

insert into newcontact (
  select id, 'VOICE' phonetype, voice number   from OLDCONTACT
 union
  select id, 'FAX' phonetype, fax number   from OLDCONTACT
);

delete from OLDCONTACT;
END; This is more code than the last version of the trigger had, and it will definitely be slower executed, but the point here is that trigger code is nothing more than mechanical application of inverse view. If you still don't quite believe in this, try to improve the code as follows:
1. Introduce a new procedure trigger_insert that accepts two arguments: new record, the name of the view, and, the name of temporary table.
2. One possible (not necessary the most efficient) way to implement trigger_insert procedure body, is to make both insert statements to be dynamic SQL.

You need to navigate Oracle Data Dictionary in order to be able to figure out the column names of OLDCONTACT table. You also need to substitute the name of the table in newcontactview when building second insert statement.

Again any further refactoring of the trigger code is purely academic exercise as you'll hardly do any better than the original version. But this is a proof that database server can treat this view, and any other view that has inverse, as a modifiable view. It is server's job then to implement a view update the most efficient way.

Some historical background

"An Introduction to Database System" by C.J. Date has an inspiring chapter on view updates with bibliography. Not surprisingly, author proposes his solution to update view problem as well. He revised it in his latest "Relational Database Writings" series of papers, but his approach is inherently case-by-case analysis. It is a matter of believe, of course, whether case-by-case approach is fitting well into Relational Theory

Author discovered the inverse and direct views while working on one of application integration projects. Unfortunately, today's reality is that application integration engineers community tend to think in API, Messages, XML and many other categories rather than views. Ironically, it seems to require certain level of sophistication to appreciate elegance and simplicity of views.

Conclusion

This article goes only as far as claiming that if inverse view exists then direct view is modifiable. Writing reverse view declaration is still database programmer's responsibility. Which approach is better: writing "INSTEAD OF" trigger, or writing inverse view is a matter of preference declarative SQL style over procedural programming.

An open question remains if it's possible to automatically deduce inverse view from direct view. The fact, though, that general view update problem is hard, indicates that there likely to be no easy answer. Received on Fri Nov 10 2006 - 19:26:36 CET