MobiLink Synchronization User's Guide
  The CustDB Sample Application
    Synchronization

Synchronizing orders in the CustDB sample

Business rules 

The business rules for the ULOrder table are as follows:

• Only approved orders are downloaded.

• Orders can modified at both the consolidated and remote databases.

• Each remote database contains only the orders assigned to an employee.

Downloads 

Orders can be inserted, deleted or updated at the consolidated database. The scripts corresponding to these operations are as follows:

• download_cursor    The first parameter in the download_cursor script is the last download timestamp. It is used to ensure that only rows that have been modified on either the remote or the consolidated database since the last synchronization are downloaded. The second parameter is the employee ID. It is used to determine which rows to download.

  The download_cursor script for CustDB is as follows:

  CALL ULOrderDownload( ?, ? )

  The ULOrderDownload procedure for CustDB is as follows:

  ALTER PROCEDURE ULOrderDownload ( IN LastDownload timestamp, IN EmployeeID integer )
  BEGIN
    SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status
      FROM ULOrder o, ULEmpCust ec
      WHERE o.cust_id = ec.cust_id
      AND ec.emp_id = EmployeeID
      AND ( o.last_modified > LastDownload
      OR ec.last_modified > LastDownload)
      AND ( o.status IS NULL  OR  o.status != 'Approved' )
      AND ( ec.action IS NULL )
  END

• download_delete_cursor    The download_delete_cursor script for CustDB is as follows:

SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status
 FROM ULOrder o, ULEmpCust ec
 WHERE o.cust_id = ec.cust_id
 AND ( ( o.status = 'Approved' AND o.last_modified > ? )
 OR ( ec.action = 'D'  )  )
 AND ec.emp_id = ?

Uploads 

Orders can be inserted, deleted or updated at the remote database. The scripts corresponding to these operations are as follows:

• upload_insert    The upload_insert script for CustDB is as follows:

INSERT INTO "ULOrder" ( "order_id", "cust_id", "prod_id", "disc", "quant", "notes", "status" )
  VALUES ( ?, ?, ?, ?, ?, ?, ? )

• upload_update    The upload_update script for CustDB is as follows:

UPDATE ULOrder SET cust_id=?, prod_id=?, emp_id=?, disc=?, quant=?, notes=?, status=?
   WHERE order_id = ?

• upload_delete    The upload_delete script for CustDB is as follows:

DELETE FROM "ULOrder" WHERE "order_id" = ?

• upload_fetch    The upload_fetch script for CustDB is as follows:

SELECT order_id, cust_id, prod_id, emp_id, disc, quant, notes, status
   FROM ULOrder WHERE order_id = ?

• upload_old_row_insert    The upload_old_row_insert script for CustDB is as follows:

INSERT INTO ULOldOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status )
   VALUES( ?, ?, ?, ?, ?, ?, ?, ? )

• upload_new_row_insert    The upload_new_row_insert script for CustDB is as follows:

INSERT INTO ULNewOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status )
   VALUES( ?, ?, ?, ?, ?, ?, ?, ? )

Conflict resolution 

• resolve_conflict    The resolve_conflict script for CustDB is as follows:

  CALL ULResolveOrderConflict

  The ULResolveOrderConflict procedure for CustDB is as follows:

  ALTER PROCEDURE ULResolveOrderConflict()
  BEGIN
    -- approval overrides denial
    IF 'Approved' = (SELECT status FROM ULNewOrder) THEN
      UPDATE ULOrder o
      SET o.status = n.status, o.notes = n.notes
      FROM ULNewOrder n
      WHERE o.order_id = n.order_id;
    END IF;
    DELETE FROM ULOldOrder;
    DELETE FROM ULNewOrder;
  END