Using Views to hide different sync semantics

[pfandrade]

I've been working on adding CloudKit sync to an app and I just though I'd like to share something I think is pretty cool that's even possible.

This app has a Wishlist type that looks like this:

@Table
struct Wishlist: Identifiable, Hashable, Sendable, Codable {
    var id: UUID
    var name: String
    var rgbColor: Int
    var icon: Icon
    var sortOrder: Int
    var listOrder: ListOrder
}

The problem is the last two fields: sortOrder and listOrder.

• sortOrder allows the user to manually adjust the order of their wishlists, and it should be synced between his devices but not shared other users.
• listOrder is just how the items in that list are ordered. This changes often (and could probably be backed by UserDefaults) so it doesn't need to be synced at all.

We've seen Brandon and Stephen solve a similar problem by separating the part that doesn't need sync to a different table. My issue was that the rest of the app already expects a Wishlist to have that layout. And it kind of makes sense that the rest of the app doesn't need to know what's being sync, shared, or just local.

So my solution was to create a database view to represent that Wishlist, that's composed of a Core, Private and Local tables.

extension Wishlist {
    @Table("wishlist")
    struct Core: Identifiable, Hashable, Sendable, Codable {
        var id: UUID
        var name: String
        var rgbColor: Int
        var icon: Wishlist.Icon
    }
    @Table("wishlistPrivate")
    struct Private: Hashable, Sendable, Codable {
        @Column(primaryKey: true)
        var wishlistID: Wishlist.ID
        var sortOrder: Int
    }
    
    @Table("wishlistLocal")
    struct Local: Hashable, Sendable, Codable {
        @Column(primaryKey: true)
        var wishlistID: Wishlist.ID
        var listOrder: Wishlist.ListOrder
    }
}
// And created the view like so
try Wishlist.createTemporaryView(
                as: Wishlist.Core
                    .leftJoin(Wishlist.Private.all) { $0.id.eq($1.wishlistID) }
                    .leftJoin(Wishlist.Local.all) { $0.id.eq($2.wishlistID) }
                    .select { Wishlist.Columns(
                        id: $0.id,
                        name: $0.name,
                        rgbColor: $0.rgbColor,
                        icon: $0.icon,
                        sortOrder: $1.sortOrder.ifnull(#sql("\(raw: 0)")),
                        listOrder: $2.listOrder.ifnull(#sql("\(quote: Wishlist.ListOrder.default.rawValue, delimiter: .text)")))
                    }
            )
            .execute(db)

When setting up the SyncEngine we just need to tell it which tables are private, which are synced and don't tell it about the local tables.

This works fine for reading, but we still need to insert, edit and delete the Wishlist type. Ideally the rest of the app shouldn't need to know this is a database view. Unfortunately, support for these operations on views doesn't exist out of the box in SQLite, but it does support triggers in views.

Using triggers we can replace calls to insert update or delete the Wishlist type:

try Wishlist.createTemporaryTrigger("insert_wishlist", insteadOf: .insert(forEachRow: { new in
    Wishlist.Core
        .insert { .init(id: new.id, name: new.name, rgbColor: new.rgbColor, icon: new.icon) }
    Wishlist.Private
        .insert { .init(wishlistID: new.id, sortOrder: new.sortOrder) }
    Wishlist.Local
        .insert { .init(wishlistID: new.id, listOrder: new.listOrder) }

}))
.execute(db)

try Wishlist.createTemporaryTrigger("update_wishlist", insteadOf: .update(forEachRow: { old, new in
    Wishlist.Core
        .find(old.id)
        .update {
            $0.id = new.id
            $0.name = new.name
            $0.rgbColor = new.rgbColor
            $0.icon = new.icon
        }
    Wishlist.Private
        .insert { columns in
            (columns.wishlistID, columns.sortOrder)
        } values: {
            (new.id, new.sortOrder)
        } onConflictDoUpdate: { update, _ in
            update.sortOrder = new.sortOrder
        } where: {
            $0.wishlistID.eq(old.id)
        }
    Wishlist.Local
        .insert { columns in
            (columns.wishlistID, columns.listOrder)
        } values: {
            (new.id, new.listOrder)
        } onConflictDoUpdate: { update, _ in
            update.listOrder = new.listOrder
        } where: {
            $0.wishlistID.eq(old.id)
        }
}))
.execute(db)
try Wishlist.createTemporaryTrigger("delete_wishlist", insteadOf: .delete(forEachRow: { old in
    Wishlist.Core
        .find(old.id)
        .delete()
    // Private and Local are deleted by cascading
}))
.execute(db)
}

There are a couple caveats, but I think it's pretty cool that this is even possible! Caveats:

1. I haven't been able to figure out how to create a trigger on the view that replaces an upsert. I was expecting SQLite to just call my insert or update triggers depending on whether there's a conflict or not, but that doens't seem to be the case. So currently I only use inserts to insert wishlists.
2. When inserting a wishlist, the UUID needs to be generated in Swift instead of the DB so that the trigger I've written has that value when inserting the other two tables. It may be possible to have the trigger operations insert the "core" table first and the get its id to use when inserting the other "private" and "local" tables but I didn't get that far.

That's it. I just felt this deserved to be shared 😄

[mbrandonw]

Hi @pfandrade, thanks for sharing your experiments. I'm not 100% sure I am following though. Which tables do you hand to the SyncEngine? Are you saying you are able to pass WishList.Core and Wishlist.Local to SyncEngine and it works? I didn't think it was possible to install triggers on database views, which is what the SyncEngine does under the hood.

[pfandrade]

I pass WishList.Core and WishList.Private to the SyncEngine. Wishlist.Local is not meant to be synced and should stay on that device only.
Here's my call to setup SyncEngine:

prepareDependencies {
    $0.defaultSyncEngine = try! SyncEngine(
        for: db,
        tables: Wishlist.Core.self, WishedItem.self, TrackedChange.Core.self,
        privateTables: Wishlist.Private.self, TrackedChange.Private.self,
        containerIdentifier: kCloudKitContainerIdentifier,
        defaultZone: CKRecordZone(zoneName: kCloudKitShopieZoneName))
}

These are all proper tables on SQLite, not views. So the SyncEngine has no issues installing triggers on them. The Wishlist view is only used by the app itself.

And it is true that views don't support all triggers, but they do support INSTEAD OF triggers which is what I'm using to replace inserts, updates and deletes to do correctly update the respective tables.

[mbrandonw]

Oh I see, I had it flipped. You still use separate tables for the core/local/private but then you combine them all into a database view. Yeah that’s a great trick, thanks for sharing! I would imagine there’s a generic helper that could be written to create those “instead of” triggers given the view type + the table types.

[pfandrade]

Yes, that's it. I haven't tried creating a generic helper for the triggers as this is the only table I have done this little dance. I can see how it might be interesting to include a generic trigger builder in SQLiteData but I'm not quite sure it's possible.