Lesson 11: Creating scripts for conflict detection and resolution

This lesson assumes you have completed all preceding lessons. See Lesson 1: Setting up a MobiLink consolidated database.

In this lesson, you add the following scripts to detect and resolve conflicts:

upload_fetch You use this script to fetch rows from a table in the consolidated database for conflict detection.
upload_update You use this script to determine how data inserted into the remote database should be applied to the consolidated database. You can also use an extended prototype of upload_update to detect update conflicts. See upload_update table event.
upload_old_row_insert You use this script to handle old row values obtained by the remote database during its previous synchronization. See upload_old_row_insert table event.
upload_new_row_insert You use this script to handle new row values (the updated values on the remote database). See upload_new_row_insert table event.
upload_delete You use this script to handle rows that are deleted from the remote database. You set the MobiLink server to ignore this event for the purpose of this tutorial.
resolve_conflict The resolve conflict script applies business logic to resolve the conflict. See resolve_conflict table event.

Install synchronization scripts for conflict detection and resolution

Install the conflict detection and resolution scripts.

Execute the following SQL statements:

/* upload_fetch */
CALL ml_add_table_script( 'sync_mlintro_scriptversion', 'Product', 
    'upload_fetch',
    'SELECT name, quantity FROM Product WHERE name = {ml r.name}' );

/* upload_update */
CALL ml_add_table_script( 'sync_mlintro_scriptversion', 'Product',
    'upload_update',
    'UPDATE Product
        SET quantity = {ml r.quantity}, last_modified = now()
        WHERE name = {ml r.name}' );

/* upload_old_row_insert */
CALL ml_add_table_script( 'sync_mlintro_scriptversion', 'Product',
    'upload_old_row_insert',
    'INSERT INTO Product_old (name,quantity,last_modified)
        VALUES ({ml r.name}, {ml r.quantity}, now())');

/* upload_new_row_insert */
CALL ml_add_table_script( 'sync_mlintro_scriptversion', 'Product',
    'upload_new_row_insert',
    'INSERT INTO Product_new (name,quantity,last_modified)
        VALUES ({ml r.name}, {ml r.quantity}, now())');

/* upload_delete */
CALL ml_add_table_script( 'sync_mlintro_scriptversion', 'Product', 
    'upload_delete', '--{ml_ignore}');

/* resolve_conflict */
CALL ml_add_table_script( 'sync_mlintro_scriptversion', 'Product',
    'resolve_conflict',
    'DECLARE @product_name VARCHAR(128);
     DECLARE @old_rem_val INTEGER;
     DECLARE @new_rem_val INTEGER;
     DECLARE @curr_cons_val INTEGER; 
     DECLARE @resolved_value INTEGER;

     // obtain the product name
     SELECT name INTO @product_name FROM Product_old;

     // obtain the old remote value
     SELECT quantity INTO @old_rem_val FROM Product_old;

     //obtain the new remote value
     SELECT quantity INTO @new_rem_val FROM Product_new;

     // obtain the current value in cons
     SELECT quantity INTO @curr_cons_val FROM Product WHERE name = @product_name;

     // determine the resolved value
     SET @resolved_value = @curr_cons_val- (@old_rem_val - @new_rem_val);

     // update cons with the resolved value
     UPDATE Product
         SET quantity = @resolved_value
             WHERE name = @product_name;

    // clear the old and new row tables
    DELETE FROM Product_new;
    DELETE FROM Product_old');

COMMIT;

Note

In this tutorial, the MobiLink server runs with the -zf option specified, which allows the server to detect any new scripts added to the consolidated database during a synchronization. Unless this option is specified, you must stop the MobiLink server before adding new scripts to the consolidated database; restart the server after adding the new scripts.

Proceed to Lesson 12: Verifying the conflict scripts using the MobiLink Monitor.