Re: Database query: Notification about change?

Bruno Wolff III wrote:

>On Mon, Dec 05, 2005 at 19:22:22 +0100,
> Erik Sigra <sigra(at)kth(dot)se> wrote:
>
>
>>Hi,
>>I plan to develop an application that is somewhat like a spreadsheet
>>with cells containing formulas. When a cell value is changed, things
>>must be updated. But the formulas can contain database queries, which
>>means that the cell has to be notified when the database changes in such
>>a way that the result of the query changes. How is this done? I would
>>really like to avoid recalculating the whole thing for each change to
>>the database.
>>
>>I looked in my database book and read about materialized views and
>>triggers. It seems like I should do the following:
>>1. Make a materialized view from the query.
>>2. Add a trigger for changes to this view.
>>3. Make the trigger notify the application program when it is trigged.
>>
>>Would this be possible? (I was planning to use Qt for application
>>programming and database access.)
>>
>>
>
>Postgres provides the NOTIFY command
>(http://developer.postgresql.org/docs/postgres/sql-notify.html)
>and you could use that in appropiate triggers to let your application know
>that it needs to refresh that values in at least some cells.
>You shouldn't need to use materialized views though. Just put the triggers
>on the base tables.
>
Thanks for the hint! I thought I need to put the triggers on
materialized views. If I would have to put them on the base tables, the
application program would have to understand the query to figure out
which tables it uses. I really do not want to make a parser for SQL.
What if the user of the spreadsheet-like application enters a formula
containing a query like this:
select distinct t1.person_id--, t1.sport_id, t2.sport_id
from
(select distinct sport_id, person_id
from
(-- hitta sporten för varje lopp_match
select *
from
(select lopp_match_id, sport_id
from
ensamlopp_match
natural join
ensamgrentävling
natural join
grentävling
natural join
gren)
as t
union
(select lopp_match_id, sport_id
from
laglopp_match
natural join
laggrentävling
natural join
grentävling natural
join gren))
as t
natural join
resursbokning
natural join
personresurs
natural join
resurstyp
where resurstyp_namn = 'domare')
as t1,
(select distinct sport_id, person_id
from
(-- hitta sporten för varje lopp_match
select *
from
(select lopp_match_id, sport_id
from
ensamlopp_match
natural join
ensamgrentävling
natural join
grentävling
natural join
gren)
as t
union
(select lopp_match_id, sport_id
from
laglopp_match
natural join
laggrentävling
natural join
grentävling natural
join gren))
as t
natural join
resursbokning
natural join
personresurs
natural join
resurstyp
where resurstyp_namn = 'domare')
as t2
where t1.sport_id != t2.sport_id and t1.person_id = t2.person_id;

? (Example query taken from a university course project.) I do not want
the application program to do anything more complex with the query than
adding "create view <some-name> as" in front of it and then add some
trigger or whatever.

And I think it will be much more efficient to watch a materialized view
than to rerun the query whenever one of the base tables change. That is
what materialized view optimization is about, right? A typical query
might look like this:
select sum(price * fraction_deductible) from yearxxxx_expenditures;

Thanks. I hope it is possible to understand what I wrote eventhough I do
not know that much about practical database programming.
Erik