Message-ID: <4394E2A2.1050301@kth.se>
Date: Tue, 06 Dec 2005 02:00:18 +0100
From: Erik Sigra <sigra@kth.se>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051014)
MIME-Version: 1.0
To: Bruno Wolff III <bruno@wolff.to>
CC: pgsql-sql@postgresql.org
Subject: Re: Database query: Notification about change?
References: <4394855E.6060201@kth.se> <20051205204053.GB19356@wolff.to>
In-Reply-To: <20051205204053.GB19356@wolff.to>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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
                       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