MobiLink Synchronization User's Guide
  The Contact Sample Application
    Synchronization

Synchronizing products in the Contact sample

The scripts for the Product table illustrate conflict detection and resolution.

The Product table is kept in a separate publication from the other tables so that it can be downloaded separately. For example, if the price changes and the sales representative is synchronizing over a slow link, they can download the product changes without uploading their own customer and contact changes.

Business rules 

The only change that can be made at the remote database is to change the quantity column, when an order is taken.

Downloads 

• download_cursor    The following download_cursor script downloads all rows changed since the last time the remote database synchronized:

  SELECT id, name, size, quantity, unit_price
  FROM product
  WHERE last_modified > ?
  AND active = 1

• download_delete_cursor    The following download_delete_cursor script removes all products no longer sold by the company. These products are marked as inactive in the consolidated database.

  SELECT id, name, size, quantity, unit_price
  FROM product
  WHERE last_modified > ?
  AND active = 0

Uploads 

Only UPDATE operations are uploaded from the remote database. The major feature of these upload scripts is a conflict detection and resolution procedure.

If two sales representatives take orders and then synchronize, each order is subtracted from the quantity column of the Product table. For example, if Sam Singer takes an order for 20 baseball hats (product ID 400), he will change the quantity from 90 to 70. If Pam Savarino takes an order for 10 baseball hats before receiving this change, she will change the column in her database from 90 to 80.

When Sam Singer synchronizes his changes, the quantity column in the consolidated database is changed from 90 to 70. When Pam Savarino synchronizes her changes, the correct action is to set the value to 60. This setting is accomplished by detecting the conflict.

The conflict detection scheme includes the following scripts:

• upload_update    The following upload_update script is a straightforward UPDATE at the consolidated database:

  UPDATE product
  SET name = ?, size = ?, quantity = ?, unit_price = ?
  WHERE product.id = ?

• upload_fetch    The following upload_fetch script fetches a single row from the Product table for comparison with the old values of the uploaded row. If the two rows differ, a conflict is detected.

  SELECT id, name, size, quantity, unit_price
  FROM Product
  WHERE id = ?

• upload_old_row_insert    If a conflict is detected, the old values are placed into the product_conflict table for use by the resolve_conflict script. The row is added with a value of O (for Old) in the row_type column.

  INSERT INTO DBA.product_conflict(
     id, name, size, quantity, unit_price, row_type )
  VALUES( ?, ?, ?, ?, ?, 'O' )' )

• upload_new_row_insert    The following script adds the new values of the uploaded row into the product_conflict table for use by the resolve_conflict script:

  INSERT INTO DBA.product_conflict(
     id, name, size, quantity, unit_price, row_type )
  VALUES( ?, ?, ?, ?, ?, 'N' )

Conflict resolution 

• resolve_conflict    The following script resolves the conflict by adding the difference between new and old rows to the quantity value in the consolidated database:

  UPDATE Product
  SET p.quantity = p.quantity
                - old_row.quantity
                + new_row.quantity
  FROM Product p,
       DBA.product_conflict old_row,
       DBA.product_conflict new_row
  WHERE p.id = old_row.id
      AND p.id = new_row.id
      AND old_row.row_type = 'O'
      AND new_row.row_type = 'N'