Integrating support for the SQLite session extension into GRDB

[mallman]

I'm exploring the use of SQLite's session extension in my app. I see GRDB does not have an API for it. I may write my own. Is this something you've explored implementing? Does it interest you as a potential part of GRDB?

[groue]

Hello @mallman,

I have no direct experience with the session extension, but it was involved in a recent discussion: #1457.

We learned that sqlite3changeset_apply() performs changes that are not visible to the built-in GRDB database observation features. That's because observation requires statements that are compiled by GRDB, when the session extension does not.

This led to #1460, which allows the application developer to explicitly declare that some changes were applied. The original reporter, @gerdemb, did not need fine-grained change reporting, so a generic db.notifyChanges(in: .fullDatabase /* some region */), unrelated to the session extension, was enough to address his observation needs. We did not spend time investigating whether we a session changeset contains enough information that would make it possible to perform fine-grained change notifications - and only trigger observations that were actually impacted by the changeset.

Maybe you can both share your experiences with the session extension? My only contribution so far would be: take care that database observation, a very important feature of the library, might make get in the way of a smooth integration in the main library.

[mallman]

My use case involves notification only. I want to get a changeset for changes made in a session (or, more specifically, a transaction). That's something that GRDB does not appear to provide. There's value in providing a "read-only" API for the session extension, with the change application part left out. That would avoid compromising GRDB's observation subsystem.

ATM, I am using the C-api using db.sqliteConnection. That works fine, but it's not very Swifty, and I will probably abstract it away somewhere.

On other wrinkle to consider is that the system version of SQLite distributed with macOS does not come with the session extension. Everything session-related in GRDB would have to be sequestered behind some build flag. The documentation should make it clear that only custom builds of SQLite with the session extension enabled will work with session support.

I'm also curious to know if you plan or want to provide a GRDB database observation method that reports diffs instead of full query results. What do you think?

[groue]

Thanks for the details, @mallman :-)

I want to get a changeset for changes made in a session (or, more specifically, a transaction). That's something that GRDB does not appear to provide.

Not directly indeed. But apps can do it with a bit of elbow grease.

The mother of database observation features, the TransactionObserver protocol, is implemented on top of the Data Change Notification Callback, which only reports the rowid of inserted/updated/deleted rows. We just know that player with id 1 was modified, not that their score was changed from 100 to 200.

With SQLITE_ENABLE_PREUPDATE_HOOK, TransactionObserver uses the pre-update hook, and reports inserted/updated/deleted values - now we can track that score was changed from 100 to 200 (see Support for SQLite Pre-Update Hooks). The Swift API is rather low-level, as everything in TransactionObserver, and there's no concept of a changeset. But applications can build one from the reported events, which leads us to:

I'm also curious to know if you plan or want to provide a GRDB database observation method that reports diffs instead of full query results. What do you think?

That would be based on a new type that implements TransactionObserver, and SQLITE_ENABLE_PREUPDATE_HOOK. You can write such a type in your app today. And if it is general enough, it could be merged in the main library!

On other wrinkle to consider is that the system version of SQLite distributed with macOS does not come with the session extension. Everything session-related in GRDB would have to be sequestered behind some build flag. The documentation should make it clear that only custom builds of SQLite with the session extension enabled will work with session support.

This does not prevent code from shipping in GRDB. We'd just have to gate it behind some #if, like we already do for SQLITE_ENABLE_PREUPDATE_HOOK and SQLITE_ENABLE_FTS5.

It would be possible to eventually provide a version of sqlite3changeset_apply that plays well with the observation subsystem. Precisely speaking, we'd not use SQLITE_ENABLE_SESSION directly, but reimplement it!

---

Unrelated: Very interestingly, the documentation of the session extension mentions:

Prior to SQLite version 3.17.0, the session extension only worked with rowid tables, not WITHOUT ROWID tables. As of 3.17.0, both rowid and WITHOUT ROWID tables are supported.

GRDB observation currently only works with rowid tables, so I really need to have a look at the implementation of the session extension :-)

[gerdemb]

@mallman I wrote an experimental Swift package SQLiteChangesetSync that allows for the offline-first synchronization of SQLite databases that works by leveraging the SQLite Session Extension.

If I understand your use case correctly, it sounds like you want to get a changeset for the changes made to a database, and then be able to inspect the contents of that changeset, but without applying it? Conceptually, changesets work like the UNIX command patch. You call sqlite3session_attach on your connection and then you you can call sqlite3session_changeset to get a changeset representing the changes to the database (update/delete/insert) that were made since you called sqlite3session_attach. In other words, it monitors the changes that you have already made to the database. (I suppose if you're monitoring inside a transaction you might be able to rollback those changes before they are actually applied--I'm not sure about that). Anyway, the interesting part is that the changeset data, like a UNIX patch, can be applied to other databases to replicate the modifications.

Anyway, It's been a while since I looked at my project, but I think you can use https://github.com/gerdemb/SQLiteChangesetSync/blob/main/Sources/SQLiteChangesetSync/SQLiteSession.swift to capture change set data and into a ChangsetData object then access the operations property of that object to inspect it's contents. Hope this helps! And I would be curious to hear if you get it working.

On other wrinkle to consider is that the system version of SQLite distributed with macOS does not come with the session extension.

Is that true? I found that I could use the session extension in the version of SQLite I had in macOS as well as inside the iOS simulators used with Xcode.

[mallman]

Is that true? I found that I could use the session extension in the version of SQLite I had in macOS as well as inside the iOS simulators used with Xcode.

I took a closer look into the macOS version. The session extension functions are not declared in sqlite3.h, however the relevant symbols are in the system libsqlite3.dylib. I noticed that you include a header for the session extension functions in https://github.com/gerdemb/SQLiteChangesetSync/blob/main/Sources/SQLiteSessionExtension/sqlite3session.h, and it looks like that's how you access the system changeset functionality.

The fact that it's not part of the system sqlite header is a bit weird, almost like Apple doesn't want to "officially" provide that API. However, it's in the binaries and it's part of PRAGMA compile_options.

[mallman]

At this point it looks like I won't be using the SQLite session extension after all, however a GRDB value observer that (efficiently) provides a diff (say, transaction scoped) instead of a new query result could be a valuable addition to the API.

I'm working with an NSCollectionView with upwards of 50,000 items (though the sky's the limit, it could be 500,000+). For my purposes, the performance difference between refreshing with a diff of a few inserts and removes versus an entirely new result set is enormous.

@groue I would suggest you consider providing this kind of functionality directly with GRDB. It would be a major feature addition. I'm using Swift's powerful CollectionDifference API and a simple extension to NSCollectionView.

[groue]

Hi @mallman. Yes, sure, it would be interesting to provide efficient for collection view diffing through CollectionDifference. And it looks like you were able to come up with a satisfying implementation. I can help you maturing it if you feel like it is necessary and that I can be useful!

[mallman]

My implementation is tailored very specifically to my project, but if I can/do abstract it out I will seriously consider submitting my work.

[groue]

This sounds good to me. Take your time: I'm personally busy preparing for Swift 6.

It is good when applications can build and refine their dedicated tooling on top of the existing public apis.

In its early days, GRDB used to ship a diffing tool that looked a lot like NSFetchedResultsController. It wasn't perfect:

1. It didn't deal with sections (useful for Table/Collection views).
2. The chosen diffing algorithm was inefficient, compared to the runtime complexity of the state of the art.
3. It could only deal with one request, when a proper use of GRDB focuses on transactions.

The first point was not too bad: support for sections could have ship later.

The second point was serious, but it could have been addressed eventually. I wasn't happy with the performance of long lists, though. I don't like it when GRDB users hit a wall.

The third point was a real problem. Normal apps evolve over time, and eventually need to observe the list AND something else. Sometimes the observed values are related by some database invariant that the application cares about. And database invariants can only be honored when all related values are fetched from a single transaction. In other words, most apps should observe compositions of requests (by fetching all observed values from a single ValueObservation). Apps that compose multiple observations should be the exception, and be well aware that those compositions drop database invariants.

All in all, an ideal collection diffing api would:

• Deal with sections.
• Have good runtime efficiency.
• Acknowledge that database invariants are important, and either clearly document that it does not support them, either support them fully (so that the user can, say, observe two lists and an arbitrary third value with the guarantee that all those values are always fetched from a single transaction).

The ideal is just an ideal - good and useful code can ship without reaching this level. But I think it's good to have a clear idea of the ideal.

---

Last word: GRBD ships with two "snapshot" database connections: DatabaseSnapshot and DatabaseSnapshotPool. Their goal is to allow delayed reads from a known and immutable database state. For example:

// An observation of the 'player' table
// that notifies fresh database snapshots:
let observation = ValueObservation.tracking { db in
    // Don't fetch players now, and return a snapshot instead.
    // Register an access to the player table so that the
    // observation tracks changes to this table.
    try db.registerAccess(to: Player.all())
    return try DatabaseSnapshotPool(db)
}


// Start observing the 'player' table
let cancellable = try observation.start(in: dbPool) { error in
    // Handle error
} onChange: { (snapshot: DatabaseSnapshotPool) in
    // Store the snapshot for alter use, or fetch values
    // right away - the fetched results will always
    // be the same anyway: that's what a snapshot is about.
    let players = try snapshot.read { db in
        try Player.fetchAll(db)
    }
}

Those snapshots can help dealing with very large lists.

I have some experimental code that uses snapshots in order to feed very large UITableView on demand (elements are fetched in batches). This gives good results. I didn't care about animating changes in a large list, so I just called reloadData when database observation would notify a fresh snapshot.

A pain point with snapshots is that they are not DatabaseQueue/DatabasePool agnostic. It is possible to create a snapshot from a DatabaseQueue, but it then becomes impossible to write as long as the snapshot is alive. Only DatabasePool and its WAL mode can both take snapshots, and write. This is not very nice for tests and previews, which are fond of in-memory DatabaseQueue.

This means that an ideal "large collection" would be able to use snapshots and batches when the app is using a DatabasePool, but perform an immediate and full fetch when the app is using a DatabaseQueue.

Due to this experience, I understand that the "ideal" I mentioned above is not easy to reach. Again: good and useful code can ship even when it's not perfect. And the golden rule wants us to warn the user about known limitations.

[mallman]

I think this convo has run its course. I'm going to close it now.