Timestamp-based downloads

The timestamp method is the most useful general technique for efficient synchronization. This technique involves tracking the last time that each user synchronized and only downloading rows that have changed since then.

MobiLink maintains a timestamp value indicating when each MobiLink user last downloaded data. This value is called the last download time.

See Using last download times in scripts.

To implement timestamp-based synchronization for a table

At the consolidated database, add a column that holds the most recent time the row was modified. The column is typically declared as follows:

DBMS	last modified column
Adaptive Server Enterprise	`datetime`
IBM DB2 LUW	`timestamp`
IBM DB2 mainframe	`timestamp`
Microsoft SQL Server	`datetime`
MySQL	`timestamp`
Oracle	`date`
SQL Anywhere	`timestamp DEFAULT timestamp`

In scripts for the download_cursor and download_delete_cursor events, compare the first parameter to the value in the timestamp column.

Example

The following table declaration and scripts implement timestamp-based synchronization on the Customer table in the Contact sample:

Table definition:

CREATE TABLE "DBA"."Customer"(
   "cust_id"  integer NOT NULL DEFAULT GLOBAL AUTOINCREMENT,
   "name"  char(40) NOT NULL,
   "rep_id"  integer NOT NULL,
   "last_modified" timestamp NULL DEFAULT timestamp,
   "active"  bit NOT NULL,
   PRIMARY KEY ("cust_id") )

download_delete_cursor script:

SELECT cust_id
FROM Customer JOIN SalesRep
ON Customer.rep_id = SalesRep.rep_id
WHERE Customer.last_modified >= {ml s.last_table_download}
    AND ( SalesRep.ml_username != {ml s.username}
          OR Customer.active = 0 )

download_cursor script:

SELECT cust_id, Customer.name, Customer.rep_id
FROM Customer KEY JOIN SalesRep
WHERE Customer.last_modified >= {ml s.last_table_download}
    AND SalesRep.ml_username = {ml s.username}
    AND Customer.active = 1

See Synchronization logic source code and Synchronizing contacts in the Contact sample.

Using last download times in scripts
Dealing with daylight savings time

Discuss this page in DocCommentXchange.
Send feedback about this page using email.