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
  1. 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.

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

 See also