Hi,
(i’m using SValentina for SQLite but the question could be extended to other similar situations).
Which is the best practice to update/notify a client when server database is updated (from another client) ?
For example:
I’ve a listbox with 10 records shown in it.
User1 delete 1 element.
User2 should see 9 records but till he ‘remains static’ (viewing the listview) nothing happens and continue to view 10 records.
I should refresh every … second the listview to make the listview updated or there’s some other best practice to follow in this case ?
you could use a trigger at INSERT, UPDATE or DELETE and update a last modified field in a extra table.
then poll if this date time has changed and update your data on screen and memory the last date time.
I like to use the Activate event to refresh the screen. This works well if the users are also working on other applications, or if your application has others windows that users work with.
For some applications where a refresh takes a long time to run, I add a label to the screen showing “Current as of 8:44am” along with a Refresh button.
Do nothing, you are simply generating extra network traffic and the user may not even be interested in the change. If the user does choose to work with the entry at that point inform the user of the change.
We use a LastModified field and check it before we attempt to do any updates. That doesn’t help with record deletes so we tend to mark records as deleted and our queries just exclude records that are ‘deleted’.
Record Locking is complicated and the more complicated it is the more you’ll fight it to get it ‘perfect’. So my recommendation is to use the simplest and easiest way, in your workflow, to not screw up data. The motto should be “Do no harm” when it comes to your data.
Our approach is to separate fields into 2 categories: Static and Cumulative. Static fields (the majority) contain a single value, such as a name or description. Cumulative fields represent some kind of running total, such as an invoice total or customer balance.
The rules are
Only update the fields that the user changed.
Replace static fields - the most recent change wins.
Update cumulative fields by the delta the user changed it. Eg., if it was originally 30 when we read it from the database and the user changed it to 40, the update sql is “field = field + 10”.
This approach relieves us from keeping the data “in sync” with other updates to the database. If I change a name while somebody else has changed a description, both changes appear in the database. If you change the name and I change it too, whoever updates last is the value that gets saved (this would happen if we kept the data in sync anyway, I would see your change and replace it with mine, so it doesn’t matter if I see it or not). And if you update a total and I update it too, the cumulative value remains correct.
Another possibility if all of the terminals connected to the database are on the same network is UDP. Before I worked at Xojo, I made a system where the AutoDiscovery class would send out a simple packet that had a customer ID to notify other terminals that if they were looking at that customer’s account, they needed a refresh.
You could certainly send more info if need be. I seem to remember the upper limit being 4K.
My Advice is never user direct database connection. Use a middle ware which connects to database and works as a TCP listener. Connect all your client apps to this middle ware and update the change notification from the middle ware side to the client apps.