Synchronizing Data Between Client and Server (Magic xpa 2.x)

Since local data is often a subset of server data, it is necessary to update local data with new data from the server, or to update the server with updated local data. There are various patterns to synchronize data between a server data source and a local (client-side) data source. The information detailed below is relevant for bidirectional updates between a server and multiple clients, and follows the master-master row level synchronization pattern, assuming optimistic concurrency control.

If you require only a one-directional update, then you need only a subset of the actions detailed below.

For a more detailed discussion of this pattern, you can also refer to http://msdn.microsoft.com/en-us/library/ff650702.aspx.

Important note: If clients and servers are running in different time zones, it is mandatory to store all timestamps using UTC times and not local times.

Data Source Structure to Support Synchronization

Data synchronization between a server data source and a local (client-side) data source depends on identifying the modified records (on both data sources) and the last synchronization time. Being able to properly keep track of changes requires making changes to both data structures and to the logic that displays and updates data.

To keep track of the last update time and the synchronization time:

1. Each data source (on the server or on the client) that needs to be synchronized should have the following additional fields:

• Last modified timestamp – A string in YYYYMMDDHHMMSS format
  
  This field will be used to find the newly updated records in the data source.
  
  It is recommended to define an index for this field.
  

• Delete indication – Logical
  
  This field will be used to identify deleted rows.

2. A new local data source is required to keep track of the last synchronization time. This data source should contain the following field:

• Last synchronization timestamp – A string in YYYYMMDDHHMMSS format
  
  This field will be used as the range criteria to find the newly updated records.

Note: Consider using two synchronization timestamps fields; one for the server scan and one for the client scan for the following scenarios:

• If there is a chance that the client and server clocks are not synchronized.

• If the synchronization timing of the server data is different than the timing of the client data. For example, when the synchronization of the server data to the client is done on startup and the synchronization of the local data to the server is done on task termination.

Synchronization Programs

The following programs need to be defined for each data source that you want to synchronize:

• Server to Local sync – Update the local data source with server data.

• Local to Server sync – Update the server data source with local data.

Notes:

• Both of these programs are necessary if the update is bidirectional; meaning, the data can be updated on both the server data source and the local data source. If the update is done only in one location, then only one program is required.

• If a bidirectional update is required, you should start with the Client to Server sync and then do the sync from the Server to the Client. This way the client update prevails. If you want the server update to prevail, the Server to Client program should be executed first.

• If a bidirectional update is required, you may want to copy the data to a temporary table and then copy the data into the real table using a set of rules, such as keeping the last updated record according to the modification time.

• Sometimes you will need to do a full (clean) data copy (usually from the server to the client). This can be done by first deleting the destination data source, using either the DBDel() or ClientDBDel() functions. Copying data to a new table is faster than copying data to an existing table.

Update the client data source with server data

The update of the local data source with server data is done by selecting the records updated since the last successful synchronization on the server and updating the local data source with them.

This will be done by using a non-interactive, non-Offline Rich Client program with a server data source as the main source.

This program has:

• A range on the records starting from the last synchronization timestamp.

• An End task condition of Yes and an Evaluate condition of Before.

• An Evaluate expression of the DataViewToDataSource() function with the required variables from the main source, the destination data source and the column names of the destination data source to which the variables' values will be inserted into.

Update the server data source with client data

Updating from the client to the server is done exactly the same, but the main source will be the local data source and the destination data source in the function will be the server data source.

Synchronization management program

It is recommended to use a synchronization management program to call the above programs.

The management program will be a non-interactive, non-Offline Rich Client program.

This program will:

1. Read the last synchronization timestamp from a local data source.

2. Save the current timestamp in a temporary variable.

3. Call the two synchronization programs so that they will update the data, starting from the last synchronization timestamp.

4. If the synchronization process was finished successfully, save the temporary timestamp (the timestamp from the beginning of the synchronization process) to the local data source, so it will be used again in the next synchronization process.

Depending on the amount of data to synchronize, a progress indication (either on the management program or on each of the synchronization programs) should be shown to the user.

The synchronization management program can be called either automatically on startup or manually by the user, depending on the amount and type of data that needs to be synchronized.

If the program is run manually, consider showing a message to the user if the synchronization process fails. This can be done by using the Unavailable Server event or by using the ServerLastAccessStatus() function after calling the synchronization program.

Handling deleted records

As mentioned above, each data source has a field to indicate whether a record is deleted.

Records are never physically deleted from the data source. Instead, any record that needs to be deleted is marked as Deleted. This way, synchronization of deleted records is done in the same way as any other field update.

Make sure to range out deleted records in your application.

Alternatively, you can use a separate data source to keep track of deleted records. This data source needs to include fields to identify the deleted records and fields for the deletion date and time. With this method, after each delete operation, a new record needs to be added to this data source.

Note: To prevent the user from physically deleting a record, you can do one of the following:

1. Set the Allowed mode of Delete in the task properties to No.

2. Add an Event logical unit for a Delete Line internal event. Set this logical unit’s Propagate property to No.

This way, the user will be able to delete records only using your application’s functionality.