CloudKit synchronization options

[Jason-Abbott]

Just some musings of possible use to others …

There are some good options for synchronizing GRDB through CloudKit

• Harmony
  • “Just works” if your models are FetchableRecord & PersistableRecord
  • Must write records through its Harmonic layer rather than GRDB directly
• SQLiteChangesetSync
  • Persists raw SQLite changesets to iCloud rather than records
  • Local device just replays changesets to achieve sync
• Matt’s minimalist GRDB CloudKit sync layer
  • Pre-CKSyncEngine
  • Unlike others, uses TransactionObserver to create CKRecords so you can write changes however you want

For me, these all have shortcomings relative to my GRDB usage, which involves a lot of raw SQL, including updates across many rows in one statement and updates through SQLite triggers. That rules out Harmony.

I like the concept of the changeset sync. It seems it would be efficient and uncomplicated for actively used devices. Where it might fall down is adding a new device. Or having cloud sync disabled in your app and then deciding to enable it. It’s not obvious how changesets could efficiently address those common scenarios.

That brings me to Matt’s code. No magic to it, which I appreciate. It seems the right balance for me between high level Harmony and low level changesets.

Continued ...

[Jason-Abbott]

A shortcoming in Matt’s code relative to my usage is foreign key handling. I have many tables and relations.

What happens when CloudKit sends changed records in an order that violates foreign keys? It doesn’t seem to come up in Matt’s example, which has just a few tables. Harmony handles it by catching FK violations and re-queuing those records. Harmony also notes other options, like temporarily disabling FK checking.

Those are reasonable choices for a small database but don’t give me confidence when there’s more complexity. I have multiple “levels” of FKs between tables so requeueing could fail again. Do you just keep requeueing until there are no failures? Aside from sounding like a terrible pattern, Harmony notes that the missing FK dependencies may not even exist in the current batch of records from CloudKit.

So requeueing isn’t a robust solution.

What I’m experimenting with now is the use of CKZones to isolate FK dependencies. CKSyncEngine allows you to define CKSyncEngine.FetchChangesOptions with prioritizedZoneIDs which is just an array in the preferred order.

What I don’t know without experimenting is how that preferred order plays out. Is it a guarantee?

To begin experimenting, I used this rough SQL

WITH RECURSIVE 
  dependents AS (
    SELECT 0 AS level, s.name, NULL AS fk
      FROM sqlite_schema s
      LEFT JOIN pragma_foreign_key_list(s.name) fk 
        ON fk."table" != s.name -- ignore self-joins
     WHERE s.type = 'table'
     GROUP BY s.name
    HAVING COUNT(fk.id) = 0 -- no foreign keys
     UNION
    SELECT d.level + 1 AS level, s.name, fk."table"
      FROM sqlite_schema s,
           pragma_foreign_key_list(s.name) fk
     INNER JOIN dependents d 
        ON d.name = fk."table" 
       AND d.name != s.name
     WHERE s.type = 'table'
  )
SELECT
    MAX(level) AS level,
    name,
    group_concat(fk) AS foreign_keys
   FROM dependents
  GROUP BY name
  ORDER BY level, name

to group tables according to their FKs. The “level” 0 tables have no FKs. The level 1 tables only have FKs in level 0. The level 2 tables only have FKs in level < 2. And so on. I end up with five levels in my database.

I wrote code that generates CKZones for each dependency level and assigns them to the FetchChangesOptions noted above. Then the code that batches up local observed changes assigns the appropriate zone ID based on the table name.

It is pretty clean but there are many failure modes. I’ll comment here with my learnings.

(Edit: Corrected attributions I’d confused between Harmony and Matt)

[groue]

What happens when CloudKit sends changed records in an order that violates foreign keys

My current understanding of the relationship between CloudKit and foreign keys is that apps are all left by themselves. An Apple eng at WWDC recommends to maintain 2 stores:

Usually we recommend having another store that is not CloudKit enabled. Copy the data from the CloudKit store to the local store where you can perform validation. Serve clients from the local store where you know the graph is valid.

If I were to need CloudKit sync (which never happened to me), I would probably spend time to look after a schema without foreign keys in order to avoid as much as possible of this hassle. Bite the bullet, in other words.

[Jason-Abbott]

I reflected on your foreign key remarks last week and quickly realized you’re right.

FKs in a db shared by multiple apps, and perhaps user-queryable, are valuable but less so in an embedded db. They provide me with important debugging clues while coding so, personally, I’ll still define them but disable them for production builds.

Once I clear up a few things in my code (replacing FK cascades with triggers) that aren’t really about CloudKit, I’ll wrap up the part that may be useful “show and tell”, where I connect SyncEngine more directly to GRDB TransactionObserver, without the encode/decode of instantiating models, and post some sample code here.

[Jason-Abbott]

Another thing I’m experimenting with is avoiding all model initializations by sending the raw rows and DatabaseValue.Storage members through CloudKit. In other words, the Cloud Database records will just mirror SQLite tables. No point in multiple encode/decodes just to store Int64/Double/String/Data, I figured.

I’ll share code when I have something more put together.