Re: is pivoted phones view updateable?
Date: 10 Nov 2006 16:47:17 -0800
Message-ID: <1163206037.913823.102320_at_f16g2000cwb.googlegroups.com>
Vadim Tropashko wrote:
> 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
> the placement of the data.
>
> 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';
>
One can talk about the reste after one solves or rejects the problem of PIVOT/UNPIVOT.
-- TegiReceived on Sat Nov 11 2006 - 01:47:17 CET
> 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.