Synchronization techniques – PB Docs 2021

This section highlights some issues that you need to consider when
designing an application that uses MobiLink synchronization.

Limiting data downloads

One of the major goals of synchronization is to increase the speed
and efficiency of data movement by restricting the amount of data moved.
To limit the data transferred by the download_cursor script, you can
partition data based on its timestamp, the MobiLink user name, or
both.

Timestamp partitioning

One way to limit downloads to data changed since the last download
is to add a last_modified column to each table in the consolidated
database (or, if the table itself cannot be changed, to a shadow table
that holds the primary key and that is joined to the original table in the
download_cursor script). The last_modified column need only be added to
the consolidated database.

In SQL Anywhere, you can use built-in DEFAULT TIMESTAMP datatypes
for this column. In other DBMSs, you need to provide an update trigger to
set the timestamp of the last_modified column.

The timestamp is generated on the consolidated database and
downloaded unmodified to the remote database during synchronization; the
time zone of the remote database does not affect it.

User-based partitioning

The download_cursor script has two parameters: last_download, of
datatype datetime, and ml_username, of type varchar(128). You can use
these parameters to restrict the download not only to rows that have
changed since the last synchronization, but also to rows that belong to
the current user.

In this sample download_cursor script, only those rows are
downloaded that have been modified since the last synchronization, and
that apply to the sales representative whose ID matches the MobiLink user
ID:

For this to work correctly, the MobiLink user ID must match the
sales_rep ID. If this is not the case, you might need to join a table that
associates these two IDs.

Primary key uniqueness

In a conventional client/server environment where clients are always
connected, referential integrity is directly imposed. In a mobile
environment, you must ensure that primary keys are unique and that they
are never updated. There are several techniques for achieving this, such
as using primary key pools.

Handling conflicts

You need to handle conflicts that arise when, for example, two
remote users update the same rows but synchronize at different intervals,
so that the latest synchronization might not be the latest update.
MobiLink provides mechanisms to detect and resolve conflicts.

Deleting rows from the remote database
only

By default, when a user starts a synchronization, the net result of
all the changes made to the database since the last synchronization is
uploaded to the consolidated database. However, sometimes a remote user
deletes certain rows from the remote database to recapture space, perhaps
because the data is old or a customer has transferred to another sales
agent. Usually, those deleted rows should not be deleted from the
consolidated database.

One way to handle this is to use the command STOP SYNCHRONIZATION
DELETE in a script in your PowerBuilder application to hide the SQL DELETE
statements that follow it from the transaction log. None of the subsequent
DELETE operations on the connection will be synchronized until the START
SYNCHRONIZATION DELETE statement is executed.

For example, you might provide a menu item called Delete Local where
the code that handles the delete is wrapped, as in this example:

There are other approaches to handling deletes. For more
information, see the chapter on synchronization techniques in the online
MobiLink – Server Administration book.