This section highlights some issues that you need to consider when designing an application that uses MobiLink synchronization.
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:
SELECT order_id, cust_id, order_date FROM Sales_Order WHERE last_modified >= ? 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 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:
STOP SYNCHRONIZATION DELETE; // call code to perform delete operation START SYNCHRONIZATION DELETE; COMMIT;
There are other approaches to handling deletes. For more information, see the chapter on synchronization techniques in the online MobiLink - Server Administration book.
