Modifying the download subset

Customize the download subset for each table. Each MobiLink remote database can synchronize a subset of the data in the consolidated database.

Prérequis

You must have a synchronization model and the table mapping must not have a download type set to Custom.

Task

Double-click the MobiLink project name.
Double-click Synchronization Models and select your synchronization model name.
Click the Mappings tab.
In the Table Mappings pane, select a remote table.
In the Details pane, open the Download Subset tab.
Choose one of the following download subsets from the Download Subset dropdown list: None, User, Remote, or Custom.
If you chose User or Remote, identify where the column containing the user name and remote ID is located.

If the column is in the consolidated table being synchronized, select Use a column in the consolidated table and then select the column containing the user name or remote ID from the Column name dropdown list.

If the column is in a different table, select Use a column in a shared relationship table. Select the table containing the column from the Table to join dropdown list. Select the column containing the user name or remote ID in the Column to match dropdown list. Use the join condition to define a join condition for joining the synchronizing table to the shadow table.
If you chose Custom, there are two text boxes where you add information to construct a download_cursor script. You do not have to write a complete download_cursor. You only need to add extra information to identify the join and other restrictions for the download subset.
- In the first text box (Tables To Add To The Download Cursor's FROM Clause), enter the table name(s) if your download_cursor requires a join to other tables. If the join requires multiple tables, separate them with commas.
- In the second box (SQL Expression To Use In The Download Cursor's WHERE Clause), enter a SQL expression to be added to the generated WHERE clause that specifies the download subset condition and join condition. You can use MobiLink named parameters, including authentication parameters, in the expression. By default, the same expression and joined tables are used for the download delete subset. If you are using a shadow table to track deletes and want to use the same expression, avoid using the base table name in the expression. If that is not possible, use a custom download delete subset.

Résultat

The download subset is modified.

Exemple

User example

For example, the ULOrder table in CustDB can be shared between users. By default, orders are assigned to the employee who created them, but there are times when another employee needs to see orders created by someone else. For example, a manager may need to see all the orders created by employees in their department. The CustDB database has a provision for this case via the ULEmpCust table. It allows you to assign customers to employees. They download all orders for that employee customer relationship.

View the download_cursor script for ULOrder without download subsetting. Select the ULEmpCust table in the Mapping tab. Choose Timestamp for the Download Type and None for the Download Subset. Right-click the table and click Go To Events. The download_cursor for the table looks like this:

SELECT "DBA"."ULOrder"."order_id",
    "DBA"."ULOrder"."cust_id",
    "DBA"."ULOrder"."prod_id",
    "DBA"."ULOrder"."emp_id",
    "DBA"."ULOrder"."disc",
    "DBA"."ULOrder"."quant",
    "DBA"."ULOrder"."notes",
    "DBA"."ULOrder"."status"
FROM "DBA"."ULOrder"
WHERE "DBA"."ULOrder"."last_modified" >= {ml s.last_table_download}

Now go back to the Mappings tab. In the Download subset tab of the Details pane, change the Download Subset dropdown list for ULOrder to User. Select Use A Column In A Joined Relationship Table. For the table to join, select ULEmpCust. For the column to match, select emp_id. For the join condition, select DBA.ULOrder.cust_id=DBA.ULEmpCust.cust_id.

Right-click the table in the top pane and click Go To Events. The download_cursor for the table now looks like this (the new lines are shown in bold):

SELECT "DBA"."ULOrder"."order_id",
    "DBA"."ULOrder"."cust_id",
    "DBA"."ULOrder"."prod_id",
    "DBA"."ULOrder"."emp_id",
    "DBA"."ULOrder"."disc",
    "DBA"."ULOrder"."quant",
    "DBA"."ULOrder"."notes",
    "DBA"."ULOrder"."status"
FROM "DBA"."ULOrder", "DBA"."ULEmpCust"
WHERE "DBA"."ULOrder"."last_modified" >= {ml s.last_table_download} 
AND "DBA"."ULOrder"."cust_id" = "DBA"."ULEmpCust"."cust_id" 
AND "DBA"."ULEmpCust"."cust_id" = {ml s.username}

Custom example

Assume you want to subset the download of a table called Customer by MobiLink user and you also want to only download rows where active=1. The MobiLink user names do not exist in the table you are subsetting, so you need to create a join to a table called SalesRep, which contains the MobiLink user names.

In the Mappings tab, select the mapping for the Customer table. Open the Download Type tab in the Details pane. Set the download type to Timestamp. Open the Download Subset tab in the Details pane. Select Custom for the Download Subset dropdown list. In the first box (Tables To Add To The Download Cursor's FROM Clause), type:

SalesRep

In the second box (SQL Expression To Use In The Download Cursor's WHERE Clause), type:

SalesRep.ml_username = {ml s.username} 
   AND Customer.active = 1
   AND Customer.cust_id = SalesRep.cust_id

Both tables have a cust_id column, so references to those columns have to be prefixed with the table name in the expression. If you use a shadow table for tracking deletes to be downloaded, you need to use None or Custom in the Download Delete Subset column for the Customer table mapping, since the shadow table is called Customer_del instead of Customer.

Right-click the table in the top pane and click Go To Events. The download_cursor for the table now looks like this:

SELECT "DBA"."Customer"."cust_id",
 "DBA"."Customer"."cust_name"
FROM "DBA"."Customer", SalesRep
WHERE "DBA"."Customer"."last_modified" >= {ml s.last_table_download}
 AND SalesRep.ml_username = {ml s.username}
 AND Customer.active = 1
 AND Customer.cust_id = SalesRep.cust_id

The final line of the WHERE clause creates a key join of Customer to SalesRep.

Discuter à propos de cette page dans DocCommentXchange.