B4J Question Does anyone have practical experience with "delta synchronization" for database synchronization?

[fredo]

A new project is still in the concept phase, in which the profiling of the database is currently being worked on.

I would like to know if anyone has any practical experience with the concept below.

1. Are there any pitfalls in it that are essential to consider?
2. Has anything been forgotten that might be important for long-term day-to-day operation?
   
   

The use case
An online database (jRDC2 server on a VPS server with SQLite) has many tables, each with many records.
It should be possible to synchronize the data regularly from several mobile clients on demand.
As a program technical approach the "delta synchronization" is intended, since thereby possibly not unnecessarily many data are moved.

The concept
It is to be implemented in such a way that only the changed or new data records are transferred.
This is to be done using timestamps to determine what data has been changed since the last synchronization.

RESTful API:​

Access is to be controlled via a RESTful API, if applicable.​

Mobile clients can send HTTP requests to this API to retrieve, update, or add specific records.​

​

Websockets:​

Websockets for real-time synchronization would also be ideal.​

This would allow clients to receive changes in real time.​

​

Batch processing:​

Synchronization logic should be able to process data in batches, rather than transferring records one at a time.​

This should reduce overhead costs.​

​

Client-side caching:​

Client-side data should be stored locally to allow previously retrieved data to be used without retransmission.​

​

Conflict resolution:​

A conflict resolution mechanism shall be implemented in case multiple clients access the same data simultaneously and make changes. This is important to ensure data consistency.​

​

Authentication and authorization:​

It should be ensured that only authorized users can access the data to ensure data privacy and security.​

Fallback mechanisms:​

Fallback mechanisms may need to be implemented to allow clients to synchronize data even if the network connection is temporarily interrupted.​

 

[aeric]

I have an app name Keep My Notes where I sync the notes using PHP API to MySQL database.
I am also developing POS system with synchronization of daily sales file through JSON format file using the Web API server I developed in B4J.
I don't use jRDC2 in production.

In Keep My Notes app, I need to consider if a user deleted a note, I need to keep the deleted ID and make sure the old data is not downloaded again to the client which has deleted the data.

For POS system, I need to consider a return sales which may occur after a few days from the invoice date. I think this is not difficult because most likely the data is only one way, which is uploaded from the cashier to the main backend. I can use Credit Note to balance the account.

 

[Erel]

Have you seen [B4X] CloudKVS - synchronized key / value store ?
I recommend you to study it. It fulfils several of the points you mentioned.