plugins.rawSQL.notifyDataChange in same client session

[rossent]

Hi All,

Our application uses database views for certain aggregations which must be done in the database and presented on the UI. Since there is no "native" mechanism in Servoy of handling data changes for DB view similar to the regular tables, we need to use the plugins.rawSQL.notifyDataChange in order to ensure that clients do not use "stale" data from the views whenever data in the underlying tables referenced by the views is changed.

The problem which we are facing related to the usage of plugins.rawSQL.notifyDataChange is this: when a client makes changes to some of the underlying tables, it calls:

Code: Select all

plugins.rawSQL.notifyDataChange(servername, viewName, pkDS, sqlActionType)

This supposedly takes care with notifying the other clients about the change so that they do not use stale data. However what happens with the cache of client session which made the changes in the first place and issued that notifyDataChange call? What we are seeing is a very non-deterministic behavior. "Eventually" the client's data does get refreshed, but it certainly is not right after the plugins.rawSQL.notifyDataChange call. In other words if the following is done in the same method:

Code: Select all

1 - get DB view data
2 - make change to underlying table referenced by the view
3- call plugins.rawSQL.notifyDataChange(server, viewName, pkDS, sqlActionType)
4- test the DB view data (expecting that it will be updated to reflect the changes in the underlying table)


At step 4 the DB view data will still be the old "stale" data. If we insert an application.updateUI() call between steps 3 and 4, we get the expected results in about 60-70% of the cases, but there are still enough cases where the data is not being refreshed "on time" - it appears that the processing of the plugins.rawSQL.notifyDataChange by the client session which made that call is done asynchronously and there is no guarantee when the new data will be available in the foundset based on the DB view. What can be done to resolve this issue, without having to do 100% of the time databaseManager.refreshRecordFromDatabase?

[ngervasi]

You need to insert a step "2.5" with databaseManager.refreshRecordsFromDatabase().

[rossent]

You need to insert a step "2.5" with databaseManager.refreshRecordsFromDatabase().

But this is exactly what we are trying to avoid!

Imagine that each one of those steps is executed in a separate method. So the "orchestration" method simply calls 4 other methods, and it does not really "know" what actually happens in them. Sometimes, in step 2, there may not be any changes needed. If we add that step 2.5 as suggested, we will be doing a completely unnecessary fetch from the database. Our goal is to do the database refresh only when it is necessary (when there are changes). In addition, we use the table "After" triggers to do the notification. Those "after" triggers are in the context of the underlying tables and there we do not have a foundset based on the view with a record which can be refreshed which again makes the databaseManager.refreshRecordsFromDatabase() impossible to use.

As far as we are concerned, the effect of the plugins.rawSQL.notifyDataChange should be immediate for the client from which that call was made. The question is: what is the designed behavior and should we consider the current behavior as a bug?

[ngervasi]

I get your point but this is the way it always worked. If you file a feature request for the change I'll be very glad to give my vote.

[sbutler]

I've always thought of the notifyDataChange as notifying other clients about the change in data. If you are in a client session, and the client is changing data, and you know if that data change is effecting a View (because you wrote the view), then you should be able to do a refresh records from DB as Nicola suggested.

If you don't want to do that, consider calling the notifyDataChange from a different session than the client that changed the data. You could do this with the headless client plugin, or build a RESTful web service in servoy that you could call. That would execute the code from a different session.

To me, expecting to notify data change in the same session is illogical. Imagine doing this on a table event (as you have) and if you have other things going on in your solution, like stored calculations, column converts, etc. It would seem that you could easily get yourself into an infinite loop situation where your continuously broadcasting data notifications.

Also, just for the record, the end result of a notifyDataChange is that the clients with those PKs has to do another call to the DB to refresh their data, so in effect, it's about the same thing as calling refreshRecordsFromDatabase followed by a notifyDataChange on the client that made the changes. In other words, the effected clients will be making another call to the DB regardless. So, if you want to update data on the current session, use refreshRecordsFromDatabase, and if you want to tell other clients to refresh their data, use notifyDataChange.

[rossent]

I've always thought of the notifyDataChange as notifying other clients about the change in data. If you are in a client session, and the client is changing data, and you know if that data change is effecting a View (because you wrote the view), then you should be able to do a refresh records from DB as Nicola suggested.

If you don't want to do that, consider calling the notifyDataChange from a different session than the client that changed the data. You could do this with the headless client plugin, or build a RESTful web service in servoy that you could call. That would execute the code from a different session.

To me, expecting to notify data change in the same session is illogical. Imagine doing this on a table event (as you have) and if you have other things going on in your solution, like stored calculations, column converts, etc. It would seem that you could easily get yourself into an infinite loop situation where your continuously broadcasting data notifications.

Also, just for the record, the end result of a notifyDataChange is that the clients with those PKs has to do another call to the DB to refresh their data, so in effect, it's about the same thing as calling refreshRecordsFromDatabase followed by a notifyDataChange on the client that made the changes. In other words, the effected clients will be making another call to the DB regardless. So, if you want to update data on the current session, use refreshRecordsFromDatabase, and if you want to tell other clients to refresh their data, use notifyDataChange.

Scott,

Thank you for the comment but I could not disagree with you more!

The notifyDataChange is really for the Application Server which keeps the data cache. With that respect, it must affect not only the "other" clients but also the client which makes that call (similar to what the flushAllClientCache does) - that actually is part of the API documentation and is quite logical.

The table events are the perfect place for this notification to take place. This way the code which notifies about the changes is completely agnostic of how the changes are actually made and ensures that the notification will always be sent. This also makes the code which changes the data completely agnostic of any other code which could be using the views based on the changed tables. I think you would agree that the single responsibility principle is a proven concept for good programming. And finally, using the notifyDataChange is better in terms of performance because it actually does not fetch anything from the database - it only tells the server that if any client asks for the records with those particular PKs, it should fetch the data from the database and not from its cache - and the point which you and Nicola perhaps are missing is that specific if. In other words, the code which makes the data change does not know (and should not care) if any other part of the code (in the same or in any other client) is using data from the affected views; the responsibility of the code which makes the data change is only to validate and persist the change in a consistent manner - it should not try to refresh some other data which it does not use or reference at the moment. Without that data notification, the only safe way to use data from the database views will be to always do double-fetch (first load the data in a foundset, then refresh the data in the foundset). The same applies to any other scenario where the data is changed by processes outside of Servoy. With that explanation, I hope you would agree that using the notifyDataChange is quite different from using the refreshRecordsFromDatabase because the data will be refreshed from the database only if necessary and not always - again, keep in mind that the code which makes the data change does not know (and should not know) if anything else is using data from the affected views.

And the part where you suggest using web services or other headless client to call the notifyDataChange outside the current session is just a total waste of Servoy licenses and an unnecessary complication for the scenario which I described. Besides, it will not resolve the issue which started that whole discussion.

[sbutler]

The notifyDataChange is really for the Application Server which keeps the data cache. With that respect, it must affect not only the "other" clients but also the client which makes that call (similar to what the flushAllClientCache does) - that actually is part of the API documentation and is quite logical.

No, this is incorrect. The Application Server doesn't keep the data cache. The client keeps the cache. plugins.rawSQL.notifyDataChange is telling the Application Server, to notify the Clients to have the Clients update their cache. So, in your scenario, you asking the client, to notify the server, to then have the server, notify the (same) client to update its cache. That's illogical.

The table events are the perfect place for this notification to take place. This way the code which notifies about the changes is completely agnostic of how the changes are actually made and ensures that the notification will always be sent. This also makes the code which changes the data completely agnostic of any other code which could be using the views based on the changed tables. I think you would agree that the single responsibility principle is a proven concept for good programming.

single responsibility principle is a great. I would disagree that the table events meet this criteria. You've decided to use SQL Views. What your missing is some place in your application where you keep track of the relationship between your base tables and the Views. For example, somewhere you must store in a configuration area that if Table W, Column X, or Table Y, Column Z changes, then you need to update the SQL View. So you need to do a refreshRecordsFromDatabase to update the current client, plus a plugins.rawSQL.notifyDataChange to notify other clients.

And finally, using the notifyDataChange is better in terms of performance because it actually does not fetch anything from the database - it only tells the server that if any client asks for the records with those particular PKs, it should fetch the data from the database and not from its cache - and the point which you and Nicola perhaps are missing is that specific if.

Thats also incorrect. A notifyDataChange will tell all the clients with those records in their cache to update their cache, which results in those clients doing another call to the DB to update their cache.

And the part where you suggest using web services or other headless client to call the notifyDataChange outside the current session is just a total waste of Servoy licenses and an unnecessary complication for the scenario which I described. Besides, it will not resolve the issue which started that whole discussion.

Yes, that is the lazy approach, but it would solve your problem. If I were you, I'd considering building a new DB scope with something that holds some configurations about which changes to the real backend tables should result in updating the view. Then when you save data, do something like scopes.db.saveData() instead of Servoy's default saveData call. Then you would be able to take a look at which records/tables are being modified in the save, and then you would know when you need to do a refreshRecordsFromDatabase and plugins.rawSQL.notifyDataChange to update the cache of the View. This would also give you some possible performance increase, as usually a View is built across multiple tables, so if changes are made to several tables that make up the view, you'll be able to do 1 refreshRecordsFromDatabase and 1 plugins.rawSQL.notifyDataChange call. With table events, you could be triggering that multiple times.


You might want to take another look at the Docs which outline how the caching and broadcasting works: https://wiki.servoy.com/display/DOCS/Wo ... lientCache

Client Cache
A Servoy client instance keeps track of which database records are in use. This is called the Client Cache and it optimizes performance by reducing the number of queries made to the database. Records are tracked by primary key. The first time the contents of a record are accessed, the Application Server must issue a query to the database on behalf of the client. The values for all of the columns of the record object are held in memory and therefore, subsequent access of the record will not produce anymore queries to the database. The user experience is greatly enhanced as one can browse quickly between forms and cached records.
A record may fall out of the cache gracefully to conserve memory and is automatically reloaded the next time it is accessed. This happens at the discretion of the client's caching engine, which is highly optimized. Relieved of the burden of managing memory, the developer can focus on more important things.

[rossent]

The notifyDataChange is really for the Application Server which keeps the data cache. With that respect, it must affect not only the "other" clients but also the client which makes that call (similar to what the flushAllClientCache does) - that actually is part of the API documentation and is quite logical.

No, this is incorrect. The Application Server doesn't keep the data cache. The client keeps the cache. plugins.rawSQL.notifyDataChange is telling the Application Server, to notify the Clients to have the Clients update their cache. So, in your scenario, you asking the client, to notify the server, to then have the server, notify the (same) client to update its cache. That's illogical.

You are almost correct there but not quite. When it comes to smart clients, what you describe is true, but when it comes to web clients, the things are a bit different and the cache (or at least part of it) is shared among all client sessions. My request is exactly to avoid the "illogical" situation where the client which makes the plugins.rawSQL.notifyDataChange has to wait for the server - it should be possible for Servoy to change that implementation to instruct the client making the plugins.rawSQL.notifyDataChange call to update its cache immediately. You would agree that this is logical, right?

The table events are the perfect place for this notification to take place. This way the code which notifies about the changes is completely agnostic of how the changes are actually made and ensures that the notification will always be sent. This also makes the code which changes the data completely agnostic of any other code which could be using the views based on the changed tables. I think you would agree that the single responsibility principle is a proven concept for good programming.

single responsibility principle is a great. I would disagree that the table events meet this criteria. You've decided to use SQL Views. What your missing is some place in your application where you keep track of the relationship between your base tables and the Views. For example, somewhere you must store in a configuration area that if Table W, Column X, or Table Y, Column Z changes, then you need to update the SQL View. So you need to do a refreshRecordsFromDatabase to update the current client, plus a plugins.rawSQL.notifyDataChange to notify other clients.

We have built exactly that already - a "Registry" of a sort which keeps that information. The problem is that the code which makes the change in table A does not and should not have foundset from view A' where it can do the refreshRecordsFromDatabase. In fact, the case could be that nothing has loaded that data yet (even in the client which makes the changes). What is the reason then to "refresh" something which may not have been loaded yet? Is that logical?

And finally, using the notifyDataChange is better in terms of performance because it actually does not fetch anything from the database - it only tells the server that if any client asks for the records with those particular PKs, it should fetch the data from the database and not from its cache - and the point which you and Nicola perhaps are missing is that specific if.

Thats also incorrect. A notifyDataChange will tell all the clients with those records in their cache to update their cache, which results in those clients doing another call to the DB to update their cache.

You are missing the point again - what happens if no client (even the one which makes the notifyDataChange call) has already loaded data from the view? With the approach suggested by you, this data will be fetched from the database even though it may not be necessary. If the current implementation/behavior of the notifyDataChange is changed as requested, the data will be refreshed from the database only if it is actually been loaded by any client.

And the part where you suggest using web services or other headless client to call the notifyDataChange outside the current session is just a total waste of Servoy licenses and an unnecessary complication for the scenario which I described. Besides, it will not resolve the issue which started that whole discussion.

Yes, this is the lazy approach, but it would solve your problem. If I were you, I'd considering building a new DB scope with something that holds some configurations about which changes to the real backend tables should result in updating the view. Then when you save data, do something like scopes.db.saveData() instead of Servoy's default saveData call. Then you would be able to take a look at which records/tables are being modified in the save, and then you would know when you need to do a refreshRecordsFromDatabase and plugins.rawSQL.notifyDataChange to update the cache of the View. This would also give you some possible performance increase, as usually a View is built across multiple tables, so if changes are made to several tables that make up the view, you'll be able to do 1 refreshRecordsFromDatabase and 1 plugins.rawSQL.notifyDataChange call. With table events, you could be triggering that multiple times.

Adding a wrapper for the saveData() could lead again to potential multiple triggers of the notification when it is called multiple times to save the data from the different tables (we never call saveData() without arguments). However, the biggest drawback to that approach is that it cannot be enforced - it is a matter of time before a programmer calls directly databaseManager.saveData() bypassing all that logic. With the table events we at least are sure that the code will be executed no matter how the changes are saved.

You might want to take another look at the Docs which outline how the caching and broadcasting works: https://wiki.servoy.com/display/DOCS/Wo ... lientCache

I probably know that by heart by now, but thanks for the suggestion anyway. I really would like to engage the Servoy guys in this discussion before deciding how to proceed. I clearly see room for improvement/enhancement in the current behavior.

[rossent]

After some discussion with Jan Blok, I created a case related to this: https://support.servoy.com/browse/SVY-5430
Feel free to vote for it.

[rgansevles]

Hi,

Let me explain a bit on how the rawsqlplugin works.

The records and foundsets are only cached in the within the client.
Also for web clients that live on the same server these are not shared (we may improve that in the future)

The server keeps track of which clients use which tables and a plugins.rawSQL.notifyDataChange() call will go to all clients using that table, including the sending client.

When the notify call is done within a transaction the actual notify will be delayed until the transaction is committed so that other clients will be able to read committed data.
Only in the current client (which reads from within the transaction) data will be refreshed immediately.

Hope this helps,

Rob

[rossent]

The created case requests that data in the current client is refreshed immediately even if no transaction is running currently.

[rgansevles]

That also happens.

The only issue is that you have uuids and we expect the underlying db type (string in this case).
I am working on that
Rob