Bruno Wolff III wrote:
>On Mon, Dec 05, 2005 at 19:22:22 +0100,
> Erik Sigra <sigra@kth.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
naturaljoin 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,
(selectdistinct 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
