Designing a MobiLink application

Online applications
Users update data by connecting to the central database directly. When a connection is unavailable, the user cannot work.

Occasionally connected smart client applications
Each user has a local database. Their database application is always available to them, regardless of connectivity, and
is kept synchronized with other databases in the system.

MobiLink is designed for creating occasionally connected smart client applications. Smart client applications can greatly
increase the usability, efficiency, and scalability of an application, but they pose new issues for application developers.
This section describes some of the major issues facing developers of smart client applications, and describes how you can
implement solutions in a MobiLink synchronization environment.

In most applications it would be a disaster to download the entire consolidated database every time you want to update any
piece of data on your remote device. The time and bandwidth would be prohibitive, making the whole system unworkable. There
are various techniques for ensuring you upload and download only what each user needs.

First, each remote database should only contain a subset of the tables and columns in the consolidated database. For example,
a salesperson in Region A may need different tables and columns than a salesperson in Region B or a supervisor.

Of the tables and columns that you put on a remote device, you only want to mark ones for synchronization that need to be
synchronized. In a MobiLink application you can map tables and columns, regardless of their names, as long as the data types
match. By default, data is both uploaded and downloaded, but MobiLink also allows you to specify that certain columns are
upload-only or download-only.

Your synchronization should only download rows to a remote database that are relevant to the user. You might want to partition
your download by remote database, by user, or by other criteria. For example, a sales rep in Region A may only need data updates
about Region A.

You only want to update data that has changed. In a MobiLink application the upload is based on the transaction log and so
by default, data is only uploaded if it has changed on the remote database. To do the same for the download, you specify timestamp-based
synchronization so that your system records the time that data is successfully downloaded, and data is downloaded only if
it has changed since then.

You may also want to implement a system of high priority synchronization: time-sensitive data is scheduled to be updated frequently,
but less time-critical data is scheduled to be updated at night or when the device is in a cradle. You can implement high-priority
synchronization by creating different publications that are scheduled to run at different times.

In addition, your users may benefit from a push-synchronization system, where data is effectively pushed down to remote devices
when needed. For example, if a trucking company dispatcher learns of a traffic disruption, they can download an update to
the truck drivers who are heading towards that area. In MobiLink, this is called server-initiated synchronization.

Say you have a warehouse. Each employee has a handheld device that they use to update inventory as they add or remove boxes.
They start a shift with 100 boxes, so each employee's remote database registers 100, as does the consolidated database. David
removes 20 boxes. He updates his database and synchronizes. Now both his database and the consolidated database register 80.
Now Susan removes ten boxes. But when Susan updates her database and synchronizes, her application expects the consolidated
database to have 100 boxes, not 80. This generates an upload conflict.

In this warehouse application, the solution is to create conflict resolution logic that says that the correct value is whatever
David updated it to, minus the original value less Susan's value:

80 - (100 - 90) = 70

While this conflict resolution logic works for inventory-based applications such as a warehouse, it isn't appropriate in all
business applications. With MobiLink, you can define conflict resolution logic to cover:

Inventory model
Update the row for the correct number of units.

Date
The latest update wins (based on when the value was changed in the database, not when the value was synchronized).

Person
For example, the manager always wins or the owner of the record always wins.

Custom
Just about any other business logic you need to implement.

Sometimes you can design your system so that upload conflicts cannot occur. If data is partitioned on the remotes so that
there is no overlap, conflicts may be avoided. However, if conflicts can happen, you should create a programmatic solution
for detecting and resolving them.

To upload data, detect upload conflicts, and synchronize deleted rows on the consolidated database, you must have unique primary
keys on every synchronized table in your database system. Each row must have a primary key that is unique not only within
the database, but within the entire database system. Primary keys must not be updated.

MobiLink provides several ways to guarantee unique primary keys. One is to set the data type of the primary key to a GUID.
GUID, which stands for Globally Unique Identifier, is a 16-byte hexadecimal number. MobiLink provides a NEWID function that
causes a GUID to be created automatically for a new row.

Another solution is a composite key. In MobiLink, each remote database has a unique value called a remote ID. Your primary
keys could be formed from the remote ID plus a regular primary key, such as an ordinal value.

SQL Anywhere also offers a global autoincrement solution. You declare a column as global autoincrement and then when a row
is added, the primary key is automatically created by incrementing the last value. This solution works best when your consolidated
database is SQL Anywhere.

Finally, you can create a pool of primary key values that are distributed to remote databases.

How you choose which primary key system to use, like many decisions in developing a synchronization solution, has to do with
the level of control you have over the consolidated and remote databases. Often, the remote databases must be able to operate
without any administration. You may also find that it is difficult to change the schema on the consolidated database. In addition,
your choice of RDBMS for the consolidated database may limit your options, as not all RDBMSs support all features.

Another issue in a synchronization system is how to handle rows that are deleted from the consolidated database. Say I delete
a row from the consolidated database. The next time David synchronizes his remote database, the delete is downloaded—deleting
the row from David's database. But what do I do with it on the consolidated database? I can't delete it because I need to
download the delete to Susan as well.

Here are two ways you can handle download deletes. First, you can add a status column to each table that indicates whether
the row is deleted or not. In this case, the row is never deleted—it is just marked for deletion. You can occasionally clean
up the rows marked for deletion, once you are sure that all the remote databases are up to date. Alternatively, you can create
a shadow table for each table. The shadow table stores the primary key values of deleted rows. When a row is deleted, a trigger
populates the shadow table, and the values in the shadow table determine what to delete on the remote database.

In a synchronized database system, only database transactions that are committed should be synchronized. In addition, all
committed transactions involving data that is to be synchronized should be synchronized, or an error should be generated.
This is the default behavior in MobiLink.

You also need to consider the isolation level of the connection to the consolidated database. You need to use an isolation
level that provides the best performance possible while ensuring data consistency. Isolation level 0 (READ UNCOMMITTED) is
generally unsuitable for synchronization as it can lead to inconsistent data.

By default, MobiLink uses the isolation level SQL_TXN_READ_COMMITTED for uploads, and if possible it uses snapshot isolation
for downloads (otherwise it uses SQL_TXN_READ_COMMITTED). Snapshot isolation eliminates the problem of downloads being blocked
until transactions are closed on the consolidated database, but not all RDBMSs support it.

The annual change to daylight savings time can pose a problem for synchronized databases during the hour that the time changes.
In the autumn the time moves back an hour; 2:00 AM becomes 1:00 AM. If you attempt to synchronize between 1:00 AM and 2:00
AM, the timestamp of the synchronization is ambiguous: is it the first 1:15 AM or the second 1:15 AM?

To resolve this problem you can shut down for an hour when the time changes in the autumn, or you can put your consolidated
database server on coordinated universal time (UTC) time.