Synchronization techniques
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:
|
1 |
SELECT order_id, cust_id, order_date<br>   FROM Sales_Order<br>WHERE last_modified >= ?<br>   AND sales_rep = ? |
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 in a script in your PowerBuilder
SYNCHRONIZATION DELETE
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 SYNCHRONIZATIONstatement is executed.
DELETE
For example, you might provide a menu item called Delete Local
where the code that handles the delete is wrapped, as in this example:
|
1 |
STOP SYNCHRONIZATION DELETE;<br>// call code to perform delete operation<br>START SYNCHRONIZATION DELETE;<br>COMMIT; |
There are other approaches to handling deletes. For more information,
see the chapter on synchronization techniques in the online MobiLink
– Server Administration book.