Bruno Wolff III wrote:
On Mon, Dec 05, 2005 at 19:22:22 +0100,
Erik Sigra <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
