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

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 Samuel Singer takes an order for 20 baseball hats (product ID 400), he changes the quantity from 90 to 70. If Pamela Savarino takes an order for 10 baseball hats before receiving this change, she changes the column in her database from 90 to 80.

When Samuel Singer synchronizes his changes, the quantity column in the consolidated database is changed from 90 to 70. When Pamela 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'

Discuss this page in DocCommentXchange.