MobiLink Synchronization User's Guide
The CustDB Sample Application
The table definitions for the CustDB database are in platform-specific files in the Samples\MobiLink\CustDB subdirectory of your SQL Anywhere 9 installation.
Both the consolidated and the remote databases contain the following five tables, although their definitions are slightly different in each location.
The ULCustomer table contains a list of customers.
In the remote database, ULCustomer has the following columns:
cust_id A primary key column that holds a unique integer identifying the customer.
cust_name A 30-character string containing the name of the customer.
In the consolidated database, ULCustomer has the following additional column:
last_modified A timestamp containing the last time the row was modified. This column is used for timestamp-based synchronization.
The ULProduct table contains a list of products.
In the both the remote and consolidated databases, ULProduct has the following columns:
prod_id A primary key column that holds a unique integer identifying the product.
price An integer identifying the unit price.
prod_name A 30-character string containing the name of the product.
The ULOrder table contains a list of orders, including details of the customer who placed the order, the employee who took the order, and the product being ordered.
In the remote database, ULOrder has the following columns:
order_id A primary key column that holds a unique integer identifying the order.
cust_id A foreign key column referencing ULCustomer.
prod_id A foreign key column referencing ULProduct.
emp_id A foreign key column referencing ULEmployee.
disc An integer containing the discount applied to the order.
quant An integer containing the number of products ordered.
notes A 50-character string containing notes about the order.
status A 20-character string describing the status of the order.
In the consolidated database, ULOrder has the following additional column:
last_modified A timestamp containing the last time the row was modified. This column is used for timestamp-based synchronization.
The ULOrderIDPool table is a primary key pool for ULOrder.
In the remote database, ULOrderIDPool has the following column:
pool_order_id A primary key column that holds a unique integer identifying the order ID.
In the consolidated database, ULOrderIDPool has the following additional columns:
pool_emp_id An integer column containing the employee ID of the owner of the remote database to which the order ID has been assigned.
last_modified A timestamp containing the last time the row was modified.
The ULCustomerIDPool table is a primary key pool for ULCustomer.
In the remote database, ULCustomerIDPool has the following column:
pool_cust_id A primary key column that holds a unique integer identifying the customer ID.
In the consolidated database, ULCustomerIDPool has the following additional columns:
pool_cust_id An integer column containing the customer ID that will be used for a new customer generated at a remote database.
last_modified A timestamp containing the last time the row was modified.
The following tables are contained in the consolidated database only:
The ULIdentifyEmployee_nosync table exists only in the consolidated database. It has a single column as follows:
emp_id This primary key column contains an integer representing an employee ID.
The ULEmployee table exists only in the consolidated database. It contains a list of sales employees.
ULEmployee has the following columns:
emp_id A primary key column that holds a unique integer identifying the employee.
emp_name A 30-character string containing the name of the employee.
The ULEmpCust table controls which customers' orders will be downloaded. If the employee needs a new customer's orders, inserting the employee ID and customer ID will force the orders for that customer to be downloaded.
emp_id A foreign key to ULEmployee.emp_id.
cust_id A foreign key to ULCustomer.cust_id. The primary key consists of emp_id and cust_id.
action A character used to determine if an employee record should be deleted from the remote database. If the employee no longer requires a customer's orders, set to D (delete). If the orders are still required, the action should be set to NULL.
A logical delete must be used in this case so that the consolidated database can identify which rows to remove from the ULOrder table. Once the deletes have been downloaded, all records for that employee with an action of D can also be removed from the consolidated database.
last_modified A timestamp containing the last time the row was modified. This column is used for timestamp-based synchronization.
These tables exists only in the consolidated database. They are for conflict resolution and contain the same columns as ULOrder. In Adaptive Server Anywhere and Microsoft SQL Server these are temporary tables. In Adaptive Server Enterprise, these are normal tables and @@spid. DB2 and Oracle do not have temporary tables, so MobiLink needs to be able to identify which rows belong to the synchronizing user. Since these are base tables, if five users are synchronizing, they might each have a row in these tables at the same time.
For more information about @@spid, see Variables.