inverse view
Date: Tue, 06 Feb 2001 19:06:52 GMT
Message-ID: <95pi03$jv6$1_at_nnrp1.deja.com>
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. Database Views provide an exellent way to achieve logical data independence. In theory, database views are superior to any procedural solution (such as stored produre interfaces), because views have fundamental relational closure property. This statement, however, is seriously undermined by existing limitations on view updates.
Problem of database view updates has a long history. "An Introduction to Database System" by C.J. Date has an inspiring chapter on view updates with extensive 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.
This article introduces a new concept of inverse view and demonstrates that a problem of view updates is essntially a problem of deducing inverse view. An open question remains if it’s possible to deduce inverse view from direct view automatically.
Schema evolution example ^^^^^^^^^^^^^^^^^^^^^^^^
In a typical database schema evolution 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. Some RDBMS allow user to define view as updatable through update trigger coding. Update triggers, therefore, are essential for full backward compatibility of this schema transformation. Here is the code that works in our example (you might ignore trigger declaration syntax, which is unimportant; just note the two insert statements in the trigger body):
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 the definition of what updateable view is.
Inverse View ^^^^^^^^^^^^
Informal criteria for deciding if a view is modifiable is ambiguity. 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? In Oracle RDBMS manual this informal idea has been narrowed down to a rigorous definition of a view based upon keypreserving 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.
Inverse view deduction ^^^^^^^^^^^^^^^^^^^^^^
It would be ideal if RDBMS would be able to deduce inverse view automatically. My next example demonstrates that in order to do that RDBMS must be at least as poweful as computer algebra system:
TABLE CartesianPoint (
x NUMBER,
y NUMBER,
);
TABLE PolarPoint (
r NUMBER,
theta NUMBER,
);
create view Cartesian2Polar as
select SQRT(x*x+y*y) r, arcsin(y/SQRT(x*x+y*y)) theta from
CartesianPoint
create view Polar2Cartesian as
select r*cos(theta) x, r*sin(theta) y from PolarPoint
Here, Cartesian2Polar and Polar2Cartesian are two views that are inverse to each other, but deducing one from another requres abilites at least to solve systems of algebraic equations.
Join Example ^^^^^^^^^^^^
Finally what is a conclusion about classic natural join examples like Employee/Department? It is intuitively clear that inverse view dosn't exist, but better observation would be that inverse view is not defined in such cases simply because there is more than one source table. We need to generalise our definition where a set of direct views maps a set of source tables into a set of target tables. Inverse views, then map a set of target tables back to sources. In order to be able to define inverse view in Employee/Department case we need to add one more direct view, say, identity map
create view EmployeeIdentityMap
select * from Employee
Then, every transaction that updates EmployeeDepartment joint view must also perform some action on EmployeeIdentityMap and this would resolve the ambiguty that prevented us defining inverse view!
Sent via Deja.com
http://www.deja.com/
Received on Tue Feb 06 2001 - 20:06:52 CET