Case Study: NC DOT Database Synchronization
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.
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 BRI_INSPCTN.INSPCTN_ID, BRI_INSPCTN.STRCTR_ID, BRI_INSPCTN.INSPCTN_DT, ... BRI_INSPCTN.APRVL_DT FROM WIGINS_UNIT.BRI_INSPCTN WHERE EXISTS ( SELECT * FROM WIGINS_UNIT.BRI_RECD_SYNC_IDS, WIGINS_UNIT.BRI_TMP_SYNC_VRBLS WHERE BRI_TMP_SYNC_VRBLS.SESSION_ID = TO_NUMBER ( USERENV ( SESSIONID ) ) AND BRI_RECD_SYNC_IDS.GLOBAL_DB_ID = BRI_TMP_SYNC_VRBLS.GLOBAL_DB_ID -- for this remote AND BRI_RECD_SYNC_IDS.STRCTR_ID = BRI_INSPCTN.STRCTR_ID -- matches on structure id AND BRI_RECD_SYNC_IDS.INSPCTN_ID = BRI_INSPCTN.INSPCTN_ID -- matches on inspection id AND ( BRI_RECD_SYNC_IDS.FRST_DOWNLOAD_TS = '1900-01-01' -- not yet been downloaded OR BRI_INSPCTN.LST_MDFD_TS >= ? ) ) -- 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