/
Client DB and Sync

Client DB and Sync

Owned by Andrew Geery
Last updated: about 7 hours ago
4 min read

Overview

The nSpect client app uses a SQLite DB for storing data. The tables in the DB fall into three basic categories:

  • Server maintained - data that is maintained on the server and used in a read-only way on the client

  • Dual maintained - data that is read and written on both the client and the server

  • Client only - data that lives on, and is only used by, the client

Server Maintained Tables

The server maintained, read-only tables can be classified into four categories:

  • Lookups

    • Group

    • InspectionType

    • Principal

    • Project

    • Role

    • ViolationLevel

  • Form-related

    • Form

    • FormVersion

    • FormSection

    • FormControl

  • Join tables

    • AutoAssignForm (Form and InspectionType)

    • GroupRole

    • OrgGroup

    • OrgInspectionType

  • Configuration

    • Config (client-wide configuration)

    • InspectionCmeValue (inspection prefill info, used when adding a form to an inspection on the client)

Dual Maintained Tables

The dual maintenance tables consists of the following inspection-related tables

  • Inspection

  • InspectionForm

  • InspectionFormSection

  • InspectionFormRepeatingSection

  • InspectionFormAnswer

  • InspectionFormMedia.

In addition to inspection tables, the site-related tables can also be modified from either the server or client:

  • Site

  • SiteAddress

Client Only Tables

The client-only tables are used by the client to determine DB state (Version) and help with syncing the data between the client and server (SyncConifg and SyncStaging).

Unused Tables

Finally, there are a couple of tables that don’t seem to be used:

  • KeyValue

  • Log

Sync Process

The sync process has two major parts:

  • Sending data changed on the client to the server using a sync session token

  • Downloading and processing data from the server that has changed since the last sync date

Sending Data

In each of the tables containing data that be modified on the client, there is a column called IsDirty. When a row of data in one of these tables is modified (inserted or updated) on the client, the IsDirty flag is set to true. In addition, for inspection-related tables, rows are not initially physically deleted. Instead, the IsDeleted column is set to true. After the IsDeleted data is sent to the server, the server will instruct the client to physically delete the DB row.

The IsDirty and IsDeleted data is uploaded to the server in a JSON format that matches the name of the table columns in the client DB. It is important to note that the parent data, all the way up to the inspection or site itself, related to a piece of changed child data, must also be uploaded. For example, for every modified inspection answer (InspectionFormAnswer row), the section (InspectionFormSection), form (InspectionForm) and inspection (Inspection) the answer is associated to must also be uploaded to the server.

For each inspection- and site-related table on the client, there is a corresponding server endpoint for uploading the modified data. After all the modified data has been uploaded, the client calls an endpoint instructing the server to process the data. During the server processing, the changed data is moved from server staging tables into the real server tables. In addition, rows with a true value for the IsDelete flag are physically deleted on the server. Finally, data conflicts are flagged on the server by saving different versions of the form data (InspectionForm).

Receiving Data

The second step of the sync process is getting data from the server that has changed since the last time the server has successfully synced. Like the update process, each client table has a corresponding server endpoint from which to download data.

In addition to the last time the client successfully synced, the user’s id (Principal ID) is also sent to the server. This info is used by the endpoints related to forms. The endpoints return all the active form-related data, plus any non-active form-related data in inspections the user is related to.

For each table, the client sends only the primary key GUID values in the table to the server, plus the last time the client successfully synced with the server. The server sends back JSON data in the following categories

  • data that exists on the server but not on the client and thus must be added to the client

  • data that exists on both the server and the client, but that has changed on the server since the last time the client successfully synced

  • PK GUIDs for data that no longer exists on the server and thus must be deleted from the client

Finding Data To Send

There are two types of data the client sends to the server:

  • A JSON array of PK GUID values for all the rows in a table; and

  • An array of JSON objects for all dirty and deleted rows

To make this process as efficient as possible, the JSON on the client is generated in the DB. Instead of using the client app code to generate the JSON, SQLite has a number of JSON functions that can be used to efficiently extract the data into the proper format.

Processing the Downloaded Data

Like finding the data to send to the server, processing the downloaded data is also done in the database. The SyncStaging table is used for this. The client app downloads the data changes from the server as a string and simply inserts it into the SyncStaging table associated with the correct table name. The SQLite JSON functions again are used to perform the proper operation on the data.

SyncConfig

Instead of storing the sync configuration in code, it is instead stored in the SyncConfig DB table. This table has columns for both the required fetch data path (e.g., GetInspections) and the optional upload path (e.g., UploadInspections), as well as the parameter name used for the data to upload. In addition, the table defines queries to use for finding both dirty data and the PK GUIDs in a table. For both queries, the data is returned from the DB in JSON format. The table also contains DML statements for extracting data from the SyncStaging table and inserting/updating/deleting it into the client tables. Finally, the SyncConfig table contains data about the order in which the fetch and upload operations should be executed, and also the order in which the upsert and delete statements should be executed.