Ed L. wrote:
> On Friday 16 November 2007 2:48 pm, Scott Marlowe wrote:
>
>> On Nov 16, 2007 3:43 PM, Ed L. <pgsql@bluepolka.net> wrote:
>>
>>> 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?
>>>
>> Really? I find a build system to be a pretty necessary part
>> of enterprise development. I can't imagine trying to keep
>> track of what I've done to my db without using some kind of
>> simple .sql scripts with all my ddl in them.
>>
>> And I use views and user defined functions a lot.
>>
>
> The overall schema upgrade management system is not the difficult
> part. I find the difficulty comes with, for example, 5 levels
> of view dependencies. The view you want to update requires you
> to rebuild 15 others, which in turn requires you to trace back
> another 15 views, and so on until you reach the leafs of the
> tree. You don't know those dependencies when you create the
> first few views. Maybe you just manually discover all these
> dependency paths each time you decide to change a view with
> dependencies. That's the part I'm griping about and for which I
> was hoping for a better way.
>
We have a system that has quite a few views to access some of the data
(although we purposely tried to avoid views that pulled from other view
due to some performance issues), but when we had all of the view
interdependencies, we had a simple shell script that ran through a list
of SQL files and imported them one after the other. If we every had to
drop a view that cascaded to other dependent views, we would just do the
DROP VIEW ... CASCADE, then run the shell script to recreate all of the
views. In your situation, the time consuming part would be the initial
creation of the script to get the SQL files in the correct order. After
that is done, it's just a matter of proper maintenance to keep it working.
Justin
