The synchronization mechanism
The database synchronization mechanism implements concurrent data access and updates between central and remote databases used by DB Sync client apps.
Basics
Each end user and app pair is identified in the DB Sync system, to distinguish changes done in the remote and central databases.
Application SQL tables shared between the central database and remote databases are updated by the DB Sync server during the synchronization process.
The DB Sync server writes all changes done by each user/app pair in a dedicated DB Sync system
SQL table named dbsync_log, also known as the "changes log".
DB Sync client apps use the DB Sync API to implement the data synchronization.
Data filters can be defined for each app/table, with parameterized SQL conditions that can be customized for each end user. When collecting data from the central database, the DB Sync server will only select the data rows matching the data filters.
Communication between the central DB Sync server and DB Sync client apps is implemented with the GWS RESTful API.
Types of data synchronization procedures
- Complete DB copy with table creation and data (SQLite client db only): The full DB copy is prepared server-side, by creating a complete SQLite database file, populated with filtered data rows, and when ready, the SQLite file is send back to the client app as payload of the GET REST request.
- Full data sync: The local database and tables must exist. All data is fetched from the server, ignoring last modification timestamp for the current user/app.
- Regular data sync: The last modification timestamp for the current user/app is compared with the modification timestamps of corresponding rows in the central changes log.
In case of corrupted local database, a complete db-copy of full-sync can be done anytime, to get a fresh clean and up to date database on the client side.
Regular data sync procedure
The following diagrams show a regular data sync procedure initiated by a DB Sync client app.
Central change log
When a synchronization procedure is initiated by the DB Sync client user/app, the DB Sync server applies changes send by the DB Sync client app, registers each modification in the central change log, and returns the modifications done by other users/apps since the last synchronization procedure done by the current client app.
INSERT, UPDATE or DELETE changes coming
from DB Sync client apps are written into the dbsync_log system table in the central
database. Each log row contains:- An ordinal number, to order the change events as they come.
- A timestamp in UTC+00:00, indicating when the modification occured.
- The app and user id that made the changes.
- The name of the SQL table where the change was done.
- The type of operation (insert, update, delete, change, sync, dbcopy)
- The SQL column indexes when the operation undergoes a partial update (field-wise change)
- The primary key of the affected row.
To support world-wide DB Sync client apps, central timestamps are stored in UTC+00:00. However, time zones using daylight saving time are not supported: When the daylight time changes, the client app must do a complete re-sync.
The central changes log table dbsync_log can be inspected with the dbsync_log tool.
Local change log and stash tables
A dbsync_log table is also created in the local database of each DB Sync client
app, to register local changes to be sent to the DB Sync server. The stash tables are only used to
register local changes between two sync procedures: On successful sync, the stash tables are
cleared.
The DB Sync API creates a stash table for each SQL table used by the app. This stash table
contains a copy of the data rows for each change filed in the local dbsync_log
changes log table. Stash data is used to produce the change events to be sent to the server. The
stash table can also be used to try to re-apply local changes if a sync procedure failed.
Database schema
The DB Sync framework requires a database schema definition (JSON) to describe application SQL tables, with constraints (primary keys, foreign keys, etc) as well as serial or sequence columns and a synchronization type.
All SQL tables used in the sync process must be defined in the database schema. Each SQL table
must be defined with a synchronization type (sync_type property),to indicate how
data rows are to be transmitted between the central database and the DB Sync client apps. For more
details, see SQL table definition.
User/App identification
Each DB Sync client app must be identified by an app id and each end user must be identified by a user id. The user and app id identify who does the data changes in a given app instance.
Modification timestamps
In the central database, a DB Sync system table contains the timestamp of the last successful sync, for each user, app and table.
Concurrent changes are controlled by the server side, by checking the DB Sync timestamps and the DB Sync log with the changes history.
When a client user/app asks for a database synchronization, the server is aware of the last valid synchronization point in time for this user/app, and collects central changes since the last synchronization.
Client-side changes are also applied. If there is a conflict, the whole sync process is canceled. This is achieved in a single SQL transaction, to perform an atomic modification in the central database.
Concurrent data access
The first client (user/app pair) asking for a data sync wins. If a second client sends changes on the same data rows, conflicts are detected by the DB Sync server, and the whole sync process will fail for the second client. The second client must then ask for a new sync/update, rework or drop the changes it wanted to send, and proceed with a new sync/update.
Return receipt
After applying central changes, the DB Sync client app sends a return receipt to inform the DB Sync server that the changes could be applied in the local database. The DB Sync server then commits the modification timestamp, considering that the client app database is up to date with the central database.