Re: view management

On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote:
> you have to rig a build system.  if you have a lot of views
> (which is good), and keeping them up to date is a pain, you
> have to automate their creation. simplest way to do that is to
> rig a build system around sql scripts.  when you create a view
> the first time, save it's creation script in a .sql file and
> replay that when you need it.   if you like to get fancy, you
> can always work solutions around make, etc on top of this.
>  there are other tricks...for example you could grep object
> dropped by the database and replay them.

That looks about as ugly as can be. Ugh. What it appears to
boil down to is that views become unusable unless you are
willing to invest the effort in a complex build system. The DB
should handle this issue automatically. Does Oracle?

There is a slightly related todo item:

-------
# Allow VIEW/RULE recompilation when the underlying tables change

Another issue is whether underlying table changes should be
reflected in the view, e.g. should SELECT * show additional
columns if they are added after the view is created.
-------

Looks like a tricky problem. What I think is needed is some sort
of column-level linkage tracking between views, rather than just
view-level dependencies. For example,

create table foo (id integer, msg varchar);
create table bar (id integer, msg varchar);
create view fooview as select id from foo;
create view barview as
select b.*, f.id as fooid
from bar b join fooview f on b.id = f.id;

When barview is created, f.id would need to be noted as depending
on fooview.id rather than just noting barview depends on
fooview. Then, if someone decides to recreate fooview to
include foo.msg, it can be discerned that the change will not
impact barview at all. That approach looks far too simple to
have not been done already.