MobiLink Synchronization User's Guide
  Synchronization Techniques
    Partitioning rows among remote databases

Partitioning with overlaps

Some tables in your consolidated database may have rows that belong to many remote databases. Each remote database has a subset of the rows in the consolidated database and the subset overlaps with other remote databases. This is frequently the case with a customer table. In this case, there is a many-to-many relationship between the table and the remote databases and there will usually be a table to represent the relationship. The scripts for the download_cursor and download_delete_cursor events need to join the table being downloaded to the relationship table.

Example 

The CustDB sample application uses this technique for the ULOrder table. The ULEmpCust table holds the many-to-many relationship information between ULCustomer and ULEmployee.

Each remote database receives only those rows from the ULOrder table for which the value of the emp_id column matches the MobiLink user name.

The Adaptive Server Anywhere version of the download_cursor script for ULOrder in the CustDB application 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 ec.emp_id = ?
   AND ( o.last_modified > ?
      OR ec.last_modified > ?)
   AND    ( o.status IS NULL
      OR o.status != 'Approved' )
   AND ( ec.action IS NULL )

This script is fairly complex. It illustrates that the query defining a table in the remote database can include more than one table in the consolidated database. The script downloads all rows in ULOrder for which:

• the cust_id column in ULOrder matches the cust_id column in ULEmpCust,

• the emp_id column in ULEmpCust matches the synchronization user name,

• the last modification of either the order or the employee-customer relationship was later than the most recent synchronization time for this user, and

• the status is anything other than Approved.

The action column on ULEmpCust is used to mark columns for delete. Its purpose is not relevant to the current topic.

The download_delete_cursor script is as follows.

SELECT o.order_id
FROM ULOrder o, ULEmpCust ec
WHERE o.cust_id = ec.cust_id
  AND ec.emp_id = ?
  AND ( o.last_modified > ? OR
        c.last_modified > ? )
  AND ( o.status IS NULL OR
        o.status != 'Approved' )
  AND ( ec.action IS NULL )

This script deletes all approved rows from the remote database.