May 21, 2019
Hot Topics:

Case Study: NC DOT Database Synchronization

  • August 9, 2005
  • By Breck Carter
  • Send Email »
  • More Articles »

Synchronization Basics

MobiLink software consists of client and server components. The remote client starts a synchronization session by connecting to a local database, gathering all the rows changed since the previous synchronization and uploading those rows to the MobiLink server. The server then applies the uploaded changes to the central database, selects rows to be downloaded, and sends those rows to the client. Finally, the MobiLink client applies the downloaded changes to the local database and sends an acknowledgment back to the server.

Failure handling is an important part of synchronization in any environment, but especially in a mobile environment where connections are frequently lost or accidentally dropped. This issue was well understood before the project began, and it was one of the reasons MobiLink was selected. With MobiLink, failures are handled at the level of upload and download database transactions. If an upload fails, all the uploaded changes are rolled back and the whole process stops. Recovery consists of running the synchronization process again; the upload stream is reconstructed from scratch and the process repeated as if it had never been attempted.

Failure during the download stage is handled in a similar fashion: the download is rolled back and the recovery technique is the same: "try again." This time, however, only the download is repeated because the previous upload worked and was committed. All of this is automatic with MobiLink, requiring no administrative effort or intervention.

Importance of Implementing Business Rules

One of MobiLink's main strengths is the ability to tailor the synchronization process by exploiting its event-driven architecture. You can provide a custom script to specify the processing that should happen for any particular event. MobiLink provides an extremely fine level of control; in a synchronization involving 100 tables, for example, literally thousands of different events are available. Of course, most events don't need scripts, the default action is okay; in the case of NC DOT, custom scripts were written for the following events:

Event Name Purpose of Script
upload_insert for each table, specify how each uploaded insert is handled
upload_update for each table, specify how each uploaded update is handled
upload_delete for each table, specify how each uploaded delete is handled
upload_statistics for each table, record how many rows were uploaded
begin_download determine the set of tables and rows are to be downloaded
download_cursor for each table, select the rows to be downloaded
download_delete_cursor for each table, select the primary keys for rows to be deleted from the remote database
end_download record which set of tables and rows don't need to be downloaded again until they change
download_statistics for each table, record how many rows were downloaded
handle_error special handling for specific errors that may occur

Sophisticated applications often impose complex business rules on the synchronization process, and the Mobile Inspector is no exception. MobiLink scripts are used for business rules as straightforward as "only District Supervisors get rows in District Supervisor Inspection table" as well as selection rules that are more complex. For example, the following download_cursor script selects Inspection rows that (1) are assigned to particular inspection team, and (2a) have not already been downloaded or (2b) have been updated since the last time they were downloaded. The SELECT shown here makes use of the BRI_RECD_SYNC_IDS table to identify rows assigned to this team; an earlier, much longer, begin_download script dynamically populates this table so it is available for use in scripts like this one:

      ( SELECT *
             = TO_NUMBER ( USERENV ( SESSIONID ) )
             = BRI_TMP_SYNC_VRBLS.GLOBAL_DB_ID  -- for this remote
             = BRI_INSPCTN.STRCTR_ID    -- matches on structure id
             = BRI_INSPCTN.INSPCTN_ID   -- matches on inspection id
             = '1900-01-01'        -- not yet been downloaded
             >= ? ) )          -- updated since the last synch

MobiLink scripts are invoked by the MobiLink server, but they are actually executed by the central or "consolidated" database server, passing rows back and forth to the MobiLink server. At NC DOT, two sets of MobiLink servers and two sets of scripts are used to implement the three-level database architecture. At the top level, scripts, written in Oracle PL/SQL, are run by the central Oracle 9i server; the example shown above is one of those scripts.. Rows are passed to the MobiLink server running at the central site, and from there, to a Team Leader tablet PC in an inspection office.

Out in the field, another MobiLink server runs on each Team Leader's tablet PC. It invokes SQL scripts in the ASA database server running on the same PC; those rows are passed to the MobiLink server, and from there, to Team Member PCs via wireless links. In this setup, the Team Leader's database acts as a remote database when synchronizing with Oracle, and as a consolidated database when synchronizing with a Team Member.

Page 2 of 3

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date