Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » MobiLink - Getting Started » Introducing MobiLink Technology » Exploring the CustDB sample for MobiLink

 

Tables in the CustDB databases

The table definitions for the CustDB database are in platform-specific files in samples-dir\MobiLink\CustDB. (For information about samples-dir, see Samples directory.)

For an entity-relationship diagram of the CustDB tables, see About the CustDB sample database.

Both the consolidated and the remote databases contain the following five tables, although their definitions are slightly different in each location.

ULCustomer

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 that identifies 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.

ULProduct

The ULProduct table contains a list of products.

In both the remote and consolidated databases, ULProduct has the following columns:

  • prod_id   A primary key column that contains a unique integer that identifies the product.

  • price   An integer identifying the unit price.

  • prod_name   A 30-character string that contains the name of the product.

ULOrder

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.

ULOrderIDPool

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.

ULCustomerIDPool

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_emp_id   An integer column containing the employee ID that is used for a new employee 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:

ULIdentifyEmployee_nosync

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.

ULEmployee

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.

ULEmpCust

The ULEmpCust table controls which customers' orders are downloaded. If the employee needs a new customer's orders, inserting the employee ID and customer ID forces 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.

ULOldOrder and ULNewOrder

These tables exists only in the consolidated database. They are for conflict resolution and contain the same columns as ULOrder. In SQL Anywhere and Microsoft SQL Server, these are temporary tables. In Adaptive Server Enterprise, these are normal tables and @@spid. DB2 LUW 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.