Implementing timestamp-based downloads

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

Context

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

Procedure

To implement timestamp-based synchronization for a table, at the consolidated database, add a last_modified 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 NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP`
Microsoft SQL Server	`datetime`
MySQL	`timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`
Oracle	`timestamp`
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.

Results

The timestamp-based synchronization is implemented.

Example

The following example, taken from the MobiLink Contact sample, is an illustration of how you can implement a timestamp-based download.

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_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