Skip to content

How It Works (DatabaseTimeStamp)

Jump to bottom
Aldy J edited this page Aug 2, 2019 · 5 revisions

How It Works

The DatabaseTimeStamp approach supports two kinds of mechanism, PushThenPull, and PullThenPush.

For the PushThenPull mechanism, the client will act as the local, and the server will act as the remote. Basically, the local will push its changes to the remote, and then the local will pull the changes from the remote.

For the PullThenPush mechanism, the reversed role will be applied, now the server will act as the local, and the client will act as the remote. Which means, the remote will pull the changes from the local, and then the remote will push the changes to the local.

Whatever the mechanism is, the synchronization process will always have four phases that will happen sequentially as listed below:

1. Getting the Local Knowledge

The Knowledge information from the local will be acquired during this phase. Local Knowledge is actually a set of records that indicates what the local knows about the state of its records. Each record from a Knowledge will contain the following information:

  • DatabaseInstanceId: This is a unique Database Instance Id (expressed as Guid but stored as string) of some database participant that have been synchronized successfully with the local database (if its IsLocal property is false), or it's actually the unique Database Instance Id of the local database itself (if its IsLocal property is true).
  • IsLocal: This is the indicator (stored as bool) whether the particular record belongs to some other database participant (if false), or it belongs to the local database itself (if true).
  • MaxTimeStamp: This is the latest (maximum) time stamp for the particular record.

For example, if a local database table, let's say Employee, have the following records:

  • Row 1 -> Name: AAA, DatabaseInstanceId: null, LastUpdate: 1000
  • Row 2 -> Name: BBB, DatabaseInstanceId: null, LastUpdate: 2000
  • Row 3 -> Name: CCC, DatabaseInstanceId: DBA, LastUpdate: 3,
  • Row 4 -> Name: DDD, DatabaseInstanceId: DBA, LastUpdate: 6,
  • Row 5 -> Name: EEE, DatabaseInstanceId: DBB, LastUpdate: 12

And then, some other local database table, let's say Department, have the following records:

  • Row 1 -> Name: DEPT01, DatabaseInstanceId: null, LastUpdate: 1500
  • Row 2 -> Name: DEPT02, DatabaseInstanceId: DBB, LastUpdate: 14

Then, the Local Knowledge records will be:

  • Row 1 -> DatabaseInstanceId: SERVER, IsLocal: true, MaxTimeStamp: 2000
  • Row 2 -> DatabaseInstanceId: DBA, IsLocal: false, MaxTimeStamp: 6
  • Row 3 -> DatabaseInstanceId: DBB, IsLocal: false, MaxTimeStamp: 14

So, the characteristics of a Knowledge are:

  • A Knowledge records will always have a single record that have its IsLocal equals to true.
  • Other records in a Knowledge records that have IsLocal equals to false means that there have been successful synchronization process made by those DatabaseInstanceIds into the local database.
  • Each of the Knowledge record's MaxTimeStamp indicates the latest time stamp value for that particular DatabaseInstanceId in ALL the tables in the local database.
  • If the local database make some changes like inserts, updates, and deletes in its local database, the Knowledge record with IsLocal equals to true (its own DatabaseInstanceId) will also automatically update its MaxTimeStamp value accordingly. Also, even though the DatabaseInstanceId for it is recorded as SERVER (as shown in the Local Knowledge records example), the system will automatically write null as the DatabaseInstanceId in all the tables to save space (later on, the system still can recognize null as the local DatabaseInstanceId).

Now, for the time stamp (as recorded in Knowledge's MaxTimeStamp or table's LastUpdated), this depends on your implementation of getting an always-move-forward-long-value that is NOT DEPENDENT on world clock. This is calculated in the GetNextTimeStamp() method subclass, which have to return the said value. In the DatabaseTimeStamp example, the server is (confidently) using a world clock actually (SELECT CAST((EXTRACT(EPOCH FROM NOW() AT TIME ZONE 'UTC') * 1000)) which is executed as a PostgreSQL query. I say this confidently because server (very) rarely change its Date Time. But, in the client side, due to using a Realm Database, the GetNextTimeStamp() is implemented using a helper table (TimeStamp) which always increment its row's Counter column whenever the GetNextTimeStamp() method is executed. As for other database technologies, like SQL Server for example, you can use its @@DBTS. Or for MySQL, you can use the query: SELECT MAX(UPDATE_TIME) FROM TABLES WHERE UPDATE_TIME < NOW() in its INFORMATION_SCHEMA table.

2. Getting the Remote Knowledge

The Knowledge information from the remote will be acquired during this phase. Remote Knowledge is basically have the same explanation as the Local Knowledge above, but this is acquired from the remote.

3. Local Getting Its Changes Based on Remote Knowledge

After acquiring the Remote Knowledge, now local basically knows what the remote knows up until this point. For efficiency, local only gathered its records that have the same DatabaseInstanceId as the _Remote Knowledge's DatabaseInstanceId, and LastUpdated value greater than the Remote Knowledge's MaxTimeStamp. OR, if local database knows records with DatabaseInstanceId that is not known by the Remote Knowledge, whatever its LastUpdated value is, local will also gathered them. The gathered records will be sent to the remote to be applied there.

4. Remote Applying the Changes sent by the Local

The sent changes by the local is applied in the remote database. While doing so, remote will inspect every applied records, and update its Knowledge records as necessary (update its MaxTimeStamp for each DatabaseInstanceId, OR, creating new record of Knowledge for unknown DatabaseInstanceId).

So, these four phases, for any mechanism (PushThenPull or PullThenPush) will be executed twice, the first (phase 1, 2, 3 and 4) is for the Push in PushThenPull (or the Pull in PullThenPush), and the second (phase 1, 2, 3 and 4 again) is for the Pull in PushThenPull (or the Push in PullThenPush), which brings us to the total of 8 phases executed sequentially.

Things to Note

  • By using this kind of mechanism (Knowledge), it opens up a possibility to do a peer-to-peer synchronization, where the Knowledge itself is already sufficient to hold information from any other databases. But this is not implemented yet (as of now), maybe later when it is required to do so.
Clone this wiki locally