Lesson 3: Creating a synchronization model

This lesson assumes you have completed all preceding lessons. See Lesson 1: Designing the schemas.

In this lesson, you use the Create Synchronization Model Wizard to create a new synchronization model.

Before you can create a model, you must use the Create Project Wizard to create a new MobiLink project. You can then access the Create Synchronization Model Wizard from the Create Project Wizard.

 Create a new MobiLink project and synchronization model

  1. Click Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.

  2. Click Tools » MobiLink 12 » New Project.

    The Create Project Wizard appears.

  3. In the What Do You Want To Name The New Project field, type mlsqla_project.

  4. In the Where Do You Want To Save The New Project field, type C:\mlsqla, and click Next.

  5. Select the Add A Consolidated Database To The Project option.

  6. In the Database Display Name field, type demo.

  7. Click Edit.

  8. Perform the following tasks in the Connect To A Generic ODBC Database page:

    1. In the ODBC Data Source Name field, click Browse and choose SQL Anywhere 12 Demo.

    2. Click OK, and click Save.

  9. Select the Remember The Password option, and click Next.

  10. Select Create A New Model, and click Next.

  11. Select the Add A Remote Schema Name To The Project option.

  12. In the What Do You Want To Name The New Remote Schema field, type mlsqla_remote_schema.

  13. Choose UltraLite from the Which Type Of Database Does This Remote Schema Name Apply To options and click Finish.

  14. Click Yes when you are prompted to install the MobiLink setup scripts.

  15. Click OK.

    The Create Synchronization Model Wizard appears.

  16. In the What Do You Want To Name The New Synchronization Model field, type sync_mlsqla, and click Next.

  17. On the Primary Key Requirements page, select all three checkboxes (you guarantee unique primary keys in a later lesson). Click Next.

  18. Select the demo consolidated database from the list, and click Next.

  19. Click No, Create A New Remote Database Schema, and click Next.

  20. On the New Remote Database Schema page, in the Which Consolidated Database Tables And Columns Do You Want To Have In Your Remote Database list, select the following tables:

    • Customers
    • CustomerProducts
    • Products
    • SalesOrders

  21. Click Next.

  22. Click Timestamp-based Download, and click Next.

    Timestamp-based downloads minimize the amount of data that is transferred because only data that has been updated since the last download is transmitted.

  23. On the Timestamp Download Options page, click Use Shadow Tables To Hold Timestamp Columns, and click Next.

    Using shadow tables is often preferred because it does not require any changes to existing tables.

  24. Perform the following tasks on the Download Deletes page:

    1. Click Yes for the Do You Want Data Deleted On The Consolidated Database To Be Deleted On The Remote Databases option.

    2. Click Use Shadow Tables To Record Deletions.

      MobiLink creates shadow tables on the consolidated database to implement deletions that need to be synchronized.

    3. Click Next.

  25. Click Yes, Download The Same Data To Each Remote.

  26. Click Next.

  27. Click No Conflict Detection, and click Next.

  28. Perform the following tasks on the Publication, Script Version And Description page:

    1. In the What Do You Want To Name The Publication field, type sync_mlsqla_publication.

    2. In the What Do You Want To Name The Script Version field, type sync_mlsqla_scriptversion.

      The publication is the object on the remote database that specifies what data is synchronized. MobiLink server scripts define how uploaded data from remotes should be applied to the consolidated database, and how script versions group scripts. You can use different script versions for different applications, allowing you to maintain a single MobiLink server while synchronizing different applications.

    3. Click Finish.

  29. In Sybase Central, click View » Folders.

  30. In the left pane under MobiLink 12, expand mlsqla_project, Synchronization Models, sync_mlsqla.

  31. Perform the following tasks in the right pane of Sybase Central:

    1. Click the Events tab.

    2. Update the download cursor for the Customers table to only download customer information from the Eastern Region.

      Replace the existing SQL script for the download_cursor event for the Customers table with the following query:



      		 
      SELECT "GROUPO"."Customers"."ID",
	"GROUPO"."Customers"."Surname",
	"GROUPO"."Customers"."GivenName",
	"GROUPO"."Customers"."Street",
	"GROUPO"."Customers"."City",
	"GROUPO"."Customers"."State",
	"GROUPO"."Customers"."Country",
	"GROUPO"."Customers"."PostalCode",
	"GROUPO"."Customers"."Phone",
	"GROUPO"."Customers"."CompanyName",
	"GROUPO"."Customers"."Region"
FROM "GROUPO"."Customers"
INNER JOIN "GROUPO"."Customers_mod" ON "GROUPO"."Customers"."ID" = 
    "GROUPO"."Customers_mod"."ID"
WHERE Region = 'Eastern';

    3. Update the CustomerProducts download cursor to only download customer products for customers in the Eastern region.

      Replace the existing SQL script for the download_cursor event for the CustomerProducts table with the following query:

      SELECT "DBA"."CustomerProducts"."ID", 
    "DBA"."CustomerProducts"."SalesOrderID", 
    "DBA"."CustomerProducts"."CustomerID", 
    "DBA"."CustomerProducts"."ProductID" 
FROM "DBA"."CustomerProducts" 
INNER JOIN "GROUPO"."Customers" ON "GROUPO"."Customers"."ID" = 
    "DBA"."CustomerProducts"."CustomerID"
WHERE "GROUPO"."Customers"."Region" = 'Eastern';

    4. Update the SalesOrders download cursor to only download sales order information for customers in the Eastern region.

      Replace the existing SQL script for the download_cursor event for the SalesOrders table with the following query:



      		 
      SELECT "GROUPO"."SalesOrders"."ID",
     "GROUPO"."SalesOrders"."CustomerID",
     "GROUPO"."SalesOrders"."OrderDate",
     "GROUPO"."SalesOrders"."FinancialCode",
     "GROUPO"."SalesOrders"."Region",
     "GROUPO"."SalesOrders"."SalesRepresentative"
FROM "GROUPO"."SalesOrders"
WHERE "GROUPO"."SalesOrders"."Region" = 'Eastern' 
AND "GROUPO"."SalesOrders"."ID" IN 
(SELECT "DBA"."CustomerProducts"."SalesOrderID" 
FROM "DBA"."CustomerProducts");

  32. Save the synchronization model.

    Click File » Save.

    The synchronization model is complete and ready for deployment.

  33. Proceed to Lesson 4: Deploying the synchronization model.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1