[coldsync-hackers] Design logic of Sync conduits

[Marco van Beek <marco at 84andahalf dot com>]

Hi All,

Since it would appear that I am going to have to write sync conduits 
earlier than expected (as it appears that the generic sync conduit does 
not update {appinfo}{categories} ), I have written a sort of design 
HOW-TO, which, if anyone has a spare moment, I would appreciate any 
holes that you can poke in it.

Once all those holes have been repaired (!!) I would be happy for this 
document to end up on the ColdSync web site as part of the 
documentation. For this reason it has been written in HTML.

Regards,

Marco.

Title: Multi User Conduits in Coldsync

Multi User Conduits in Coldsync

One of Coldsync's many capabilities includes being able to share Palm data with many users. However, when the relationship between the desktop and the palms becomes a one-to-many relationship, the underlying design logic of the conduit has to take this into account.

The only piece of data that the user cannot change on the Palm is the {id} field. This means that in order to syncronise across many Palm's, we have to use the {id} as the only reliable piece of information we can obtain from the Palm to match records. However, there is a sting in the tail. When a record is edited on the Palm (which I will now refer to as Handheld ), what really happens is that the old record is marked as deleted, and the new record assumes a new, hopefully unique, {id} number. This gives us one good, one bad issue. First of all, we already have a way of avoiding the desktop ( which I will now refer to as Server ) overwriting a record that has also been changed on the Handheld (or vice versa) as they would have different {id} numbers. Secondly, there is a remote chance that two Handhelds would create a new record with the same {id}, so the Server MUST control the issuing of {id} numbers.

There are 8 possible scenarios when we syncronise a record.

Scenario	Handheld Record	Server Record	Task
1	Unchanged	Unchanged	Nothing to do
2	New		Add record to Server
3	Updated	Unchanged	Update record on Server
4	Deleted	Unchanged	Delete record on Server
5		New	Add record to Handheld
6	Unchanged	Updated	Update record on Handheld
7	Unchanged	Deleted	Delete record on Handheld
8	Updated	Updated	Conflict

Based on the information above, we can make the following observations.

• Scenario 2 will need the Master {id} from the server written back to the Handheld.
• Scenario 3 is actually a combination of 2 + 4, since we have no easy way of deciding which "new" Handheld record is really an edited record.
• Scenario 8 is really a combination of 2 + 5. All we have to do extra is send some sort of message to the user that a duplicate record has been created. This way we can avoid nasty conflict resolutions, and leave it to the user.

So, logically, we need to look at the Master database first, and force any changes into the Handheld. We can do this because we know that any changed record on the Handheld will no longer have the same {id} number. As long as the new Handheld {id} range is different to the Server {id} range, we should never have a problem. We may be able to force this by using the {appinfo}{lastUniqueID} field in each of the Handhelds' databases.

1. Match Server record to Handheld record by {id}:
   1. If found compare data:
      1. If data matches - Scenario = 1: Do nothing
      2. If data mismatch: Scenario = 6: Update Handheld
   2. If no match, check for {deleted} flag and if not set: Scenario 5: Add to Handheld and force {id} field.
2. Match Handheld record to Server record by {id}:
   1. If found ignore (duplicate of above)
   2. If not found check {dirty} flag
      1. If {dirty} is set, Scenario = 2: Add to Server, and update {id} on Handheld
      2. if {dirty} is not set, Scenario = 7: Delete from Handheld
3. Look for {deleted} flag on Handheld and if set: Scenario 4 = Delete from Server

The most difficult part of this is comparing the data between the Server and the Handheld. Comparing each field can get labourious, so one way of speeding up the code might be to have a timestamp field in the master database, and compare it to the {dbinfo}{baktime}. If this is set to the time we last syncronised, we can quickly ignore Server records that have not been modified since that time. We would need to set this time at the very end of the sync process, so that we ensure that the timestamps modified as part of the current sync are before our "last sync" date/time.

Also very important for all this to work is being able to exclusively lock the Server database while the sync process is going on, otherwise external changes can be made that will cause chaos!.

Marco van Beek - February 2004.

Written while coding SSiS: The Single Source information Server.