Suppose you have 2 apps, one for monitoring the DB and another that is using actually the database, what is the preferred, best way to watch for this change???

I mean, I can be reading (pooling) to see if the data is changed from one snapshot to the next, but I think about "events" and those events fire the actualization of the front-end GUI (perhaps a list of events/human checks pending).



Another way to see it is, if I have a "snapshot" (result of a select) of the DB that Im making manipulations (or more like showing part of it) but not directly over the DB, what is the "best" way to update the snapshot without being checking each X time if there is a change in the DB.

[added in edit] Another way to see it is, suppose you are reading this thread and I delete it, you will not be able to see the change until you refresh, but before that, I already have confirmed the deletion of this thread and you wherent notified with a message "this thread has been deleted, you whant to save it?" but see that there shouldnt be pooling of the data, but an event send to your browser launching that little funciton..., what is the best way if I not use pooling of change??? or there is no other way??


The question come because I have choosed to take a snapshoot of certain views and not retrieve them each time there is an "action" over the view (because not always will be changed, or I can take a subset to show or to "keep in the eye" without ask again to the DBMS what subset should I "keep in the eye") and work programatically on them, that is instead of --select name from viewx where name="xname"-- or --select name, id from viewx where name="xname" do something like --take snapshot of viewx and provide functions (interface) for ask names and other funny things, and not update the snapshot until the view in the DB is changed--.


Yes, I can take the "snapshot" each time there is an interaction of an event in the GUI, but what happend if there is no event in the GUI (the user is watching the GUI) and there are 2 or 3 changes of the view in the DB.




Being with a timer in a thread is the only way... or aka pooling for changes?, Im missing some?.

I know there are triggers that can be launched when certain conditions happend... but they are internal to the DB (they dosent send events/signals to apps)... how those events/triggers will launch an update of my snapshot??.

You could (in postgres, at least, if you used a non-trusted
language for the trigger-function) use a trigger to activate
on OS-level, but it's not very portable. Unless your result-
sets that you "cache" on the client-side is very large (re-
running the query takes over a few seconds) and the time was
prohibitive the polling of the database on client-side events
is one sensible way to go about it. Another option would be
to lock the rows you're looking at (select for update [which
may upset colleagues wanting to change or retrieve it]).

It all depends on the actual needs.


Cheers,
Tink

Sounds like the observer pattern to me. How you implement it would depend upon the application.

I see that you say about the observer pattern, I guess it can be a solution, but the subject to observe then should be the "interface" that is used to update, insert, delete data in the DBMS and not really the data in the DBMS, then I should take care of this in the design of the other components that "update, insert and delete" because they are outside of the app (observer component) and instead of provide "direct" access to the DB provide an interface that they can use and that is perhaps like "static/unique in instance".




Or perhaps have a table where I can put the latest changes in the things of interest that are writed automatically be the trigger and do pooling in that table...

One way to achieve this would be to build an intermediate layer between the GUI and the database then the GUI will tell this layer which pieces of data it wants and if it wishes to be notified of any alterations to that data. The GUI will send any data alterations to this intermediate layer, which will be responsible for updating the database and seeeing if any other GUI instance wants to be notified of changes to this piece of data.

Yea, but actually the code that people have here, dosent follow a pattern...

In fact people here have 1 app that is the "GUI" for watch and set some manual options in the data in DBMS (with some pooling), and there is other app that do some automatic updates time to time... the question is... I think I can add the layer that watches for sqlcommands like "update", "insert", "delete" (even a simple tokenizer for see if there is a reference to x table not a complete SQL parser...), the problem come in the way that this 2 different apps dosent have a common layer for communicate with the DB and that being 2 different instances, how they will share the same instance for communicate with the DB.