$script_override

[bcooney]

Hi EasySyncers!

I'm having a bit of a brain freeze. Can someone please explain the purpose of $script_override to me?

tia,

Barbara

[dansmith65]

Going by memory here, but I thought that was used to tell auto-enter calculations that a sync script was running, so adjust your logic accordingly.

[bcooney]

Yes, Dan. Here is the calc:

Let ( trigger = GetField ( "" ); If ( IsEmpty ( $script_override ) ; Get ( CurrentTimeUTCMilliseconds ); Self ) )

In English, this says to me, "if the sync is running, then don't change the ES_UTC time in this record. Let it remain whatever it was."

However, what I cannot solve is this scenario:

User creates a new record and doesn't sync. Puts laptop away for six months. Others continue to sync. Then, user syncs (six months later). If the ES_UTC in that record is transferred as it is created on the server, how then will other users pull that record? Its ES_UTC is six months old...much older than any other user's last pull time?

[bcooney]

Any further thoughts, Dan?

[Joshua Willing Halpern]

On 8/31/2017 at 10:03 AM, bcooney said:

User creates a new record and doesn't sync. Puts laptop away for six months. Others continue to sync. Then, user syncs (six months later). If the ES_UTC in that record is transferred as it is created on the server, how then will other users pull that record? Its ES_UTC is six months old...much older than any other user's last pull time?

Here's my understanding. The $script_override is used during a pull to keep ES_UTC and ES_Device_ID from auto-entering on the local file, so that's fine. And ES_Device_ID should also not auto-enter on the server and should be set the same as that in the pushed record. Tim originally scripted it to do that by sneaking this in in the middle of 'Process Payload from Client':

The calc shows that instead of letting ES_Device_ID update by itself with the server's id, it uses $client_persistent_id, which comes from the script parameter. In summation: $script override prevents auto-enter while the field is manually written. All good so far.

But i don't think he actually addressed the issue you raised, where a lazy syncer might not sync their device for months and other users will therefore not pull his new/updated record.

We need to allow the server to auto-enter in the ES_UTC field. I did this by splitting the variable into $script_override for the ES_Device_ID field and $time_override for the ES_UTC field. That way you can independently control when each field's auto-enter calc is turned off, which is what you need during Process Payload from Client. Round tripping will still be prevented because ES_Device_ID still matches but other users will be able to pull the data because the ES_UTC field is now later than their last sync. Once broken apart, make sure both are set to 1 during a pull and only $script_override is set during "Process Payload from Client".

It's been a while so I hope my understanding is correct and that that helps.

J

[bcooney]

Thanks, Josh. When you think of it in the extreme, a "lazy-syncer" (lol) that took months to push their record won't be pulled. But, it doesn't really need to be that extreme. Any pushed record with an ES_UTC older than a user's last pull will not be pulled. Doesn't make sense and seems that I must be missing something. Lots of records wouldn't be pulled. 

[Joshua Willing Halpern]

Barbara, you're totally right.

Just to be clear, updating the ES_UTC on the server should always prevent that problem of records not being pulled, barring any simultaneous sync snafus. However This brings up another issue: e.g. If two users edit the same record, User A does first, and User B does second. If User A syncs first, then the record on the server will update with a new later timestamp and when User B goes to sync, she will end up pulling the data as edited by User A even though she ( User B ) was the last to edit the record :/.

Just spitballing, but I believe this could be solved with a second timestamp field. Use ES_UTC to determine if the record should be examined at all during a pull, and a new ES_UTC_ACTUAL that only ever reflects actual user edit timestamps and is used for conflict resolution.

Cheers, J

[bcooney]

Joshua,

Yes, we are thinking this thru as well, and the scenario you described is of concern to us. Interesting idea...

-B

[Joshua Willing Halpern]

Let us know what you come up with! j

[bcooney]

Hi Joshua,

Here's what we did. We went with your brilliant idea to maintain the "integrity" of the ES_UTC_Time field. It will always maintain the UTC of the record's last modification and we made no changes to that field. We added to every table on the Host a new field, ES_UTC_LastPush. We named it "LastPush" because our setup doesn't have a UI to the hosted file. So, records are not edited directly on the Host by a user.

ES_UTC_LastPush = Let ( trigger = GetField ( "" );   Get ( CurrentTimeUTCMilliseconds ) ) with Do Not Replace deselected.

So, every time that record is processed by Process Payload from Client, this field will update but the ES_UTC_TIme will be set to the client's ES_UTC_Time (no change there).

In the Prepare Payload for Client, we updated the Set Variable $dyn_sql to be

Substitute ( 

"SELECT " & 
$dyn_sql & ", '" & $$record_delimiter & "'" &
" FROM \\\"" & $sync_table & "\\\"" & 
" WHERE " &

// Exclude records flagged for sync exclusion.
"( COALESCE ( ES_Exclude, 0 ) = 0 ) " &

// If the client is merging new/updated data with data already on the device...
// Only include records that have been added/updated since the last pull...
// And only include data that they did not just push (i.e. no "round tripping!")...


If ( ( $$sync_method = "Merge" ) and ( $last_pull_utc > 0 );

  "AND ( ES_UTC_LastPush > " & $last_pull_utc & " ) " 

&  "AND ( COALESCE ( ES_Device_ID, 'X' ) NOT IN ( DeviceList))"         

; "" )

; "DeviceList" ; $device_list)

Notice, we replaced the ES_UTC_Time in the WHERE clause with the new field ES_UTC_LastPush. This allows records to be included in a payload even if their ES_UTC_Time is less than the user's $last_pull_utc. This solves the scenario where user A mods a rec, user B syncs, then user A syncs. Previously, User B would not receive that record on his next sync, since his $last_pull_utc was greater than the record's ES_UTC_Time. Now, he'll receive that record because the record's ES_UTC_LastPush IS greater than his $last_pull_utc. No more missing records!

We use "recent" as our conflict handling method. Therefore, Process Payload from Client stays the same and since we don't ever modify a record's ES_UTC_TIme during a sync, the $update_record section in the Process Payload from Client works as original. It still lets the most recently modified record win. So, if user B modifies a record after user A, even if user A syncs first and the record's ES_UTC_LastPush is greater than user B's ES_UTC_Time, it doesn't matter. User B's ES_UTC_Time > $ES_UTC_Time_Host and so the record will be updated. And that is why two UTC fields are much better, imho, than using the existing ES_UTC_Time to be updated by the server on Process Payload from Client.

[Joshua Willing Halpern]

Barbara! Looks great, and excellent refinements. I want to implement something like this in a future version as well. Thanks for sharing your code.