Problem with ValueObservation when changes are made on the raw SQLite connection using the C API

[gerdemb]

I want to start by saying I'm really enjoying using GRDB and want to thank everyone for all the work that has been put into. Now on to my issue....

I am having trouble getting ValueObservation to work properly when changes are made to the database through the raw SQLite connection db.sqliteConnection using the C API. I'm not sure if this should be possible or if I'm doing something wrong, or if I've stumbled into a bug.

Here's my simplified setup:

try dbQueue.write { db in
    // ...
    try applyChangeset(db: db.sqliteConnection!, bIgnoreConflicts: true, nChangeset: data.length, pChangeset: mutableCopy.mutableBytes)
}

where applyChangeset calls the SQLite C API function sqlite3session_apply which "applies" the change set data to the database, executing INSERT, UPDATE or DELETE statements as necessary.

This part works fine. The problem is that I'm trying to observe those changes using ValueObservation like this:

ValueObservation
            .tracking { db in
                do {
                    let players = try Player.fetchAll(db)
                    debugPrint("Player.fetchAll() count", players.count)
                    return players
                } catch {
                    print("Unable to track players \(error)")
                    throw error
                }
            }
            .print()
            .publisher(in: dbQueue, scheduling: .immediate)

This ValueObservation works fine when working with records and the dbQueue object, Player(...).insert(db) etc. However, changes made by sqlite3session_apply are not observed.

I enabled SQL debugging statements by modifying my DatabaseQueue configuration like this:

var config = Configuration()
    config.prepareDatabase { db in
    db.trace { print("SQL: \($0)") }
}

and I can see that the INSERT statements for sqlite3session_apply are being caught. I also added a print() statement to my ValueObservation and I can see that it looks like the changes made by sqlite3session_apply are triggering ValueObservation, but that the trigger happens too soon before the INSERTs have started and so Player.fetchAll(db) returns no results.

Here's the log. Notice that database did change is triggered, but "Player.fetchAll() count" 0 because it is run before the INSERT INTO main."player"("id", "name", "score") VALUES(?, ?, ?) statements later.

SQL: BEGIN DEFERRED TRANSACTION
SQL: DROP TABLE "changeset"
SQL: DELETE FROM "player"
SQL: COMMIT TRANSACTION
database did change
SQL: PRAGMA query_only = 1
SQL: BEGIN DEFERRED TRANSACTION
fetch
SQL: PRAGMA database_list
SQL: SELECT type, name, tbl_name, sql FROM sqlite_master
SQL: SELECT * FROM "player"
"Player.fetchAll() count" 0
SQL: COMMIT TRANSACTION
SQL: PRAGMA query_only = 0
value: []
SQL: BEGIN DEFERRED TRANSACTION
SQL: CREATE TABLE IF NOT EXISTS "changeset" ("id" TEXT PRIMARY KEY NOT NULL, "data" BLOB NOT NULL)
SQL: COMMIT TRANSACTION
"recordZoneChanges lastChangeToken" "NO TOKEN"
"changes count" 4
SQL: BEGIN DEFERRED TRANSACTION
SQL: PRAGMA database_list
SQL: SELECT type, name, tbl_name, sql FROM sqlite_master
SQL: PRAGMA main.table_xinfo("changeSet")
SQL: PRAGMA main.table_list("changeSet")
SQL: SELECT EXISTS (SELECT * FROM "changeSet" WHERE "id" = ?)
SQL: SAVEPOINT changeset_apply
SQL: PRAGMA defer_foreign_keys = 1
SQL: PRAGMA 'main'.table_info('player')
SQL: PRAGMA 'main'.table_info('player')
SQL: INSERT INTO main."player"("id", "name", "score") VALUES(?, ?, ?)
SQL: PRAGMA defer_foreign_keys = 0
SQL: RELEASE changeset_apply
SQL: INSERT INTO "changeSet" ("id", "data") VALUES (?,?)
SQL: COMMIT TRANSACTION
SQL: BEGIN DEFERRED TRANSACTION
SQL: SELECT EXISTS (SELECT * FROM "changeSet" WHERE "id" = ?)
SQL: SAVEPOINT changeset_apply
SQL: PRAGMA defer_foreign_keys = 1
SQL: PRAGMA 'main'.table_info('player')
SQL: PRAGMA 'main'.table_info('player')
SQL: INSERT INTO main."player"("id", "name", "score") VALUES(?, ?, ?)
SQL: PRAGMA defer_foreign_keys = 0
SQL: RELEASE changeset_apply
SQL: INSERT INTO "changeSet" ("id", "data") VALUES (?,?)
SQL: COMMIT TRANSACTION
SQL: BEGIN DEFERRED TRANSACTION
SQL: SELECT EXISTS (SELECT * FROM "changeSet" WHERE "id" = ?)
SQL: SAVEPOINT changeset_apply
SQL: PRAGMA defer_foreign_keys = 1
SQL: PRAGMA 'main'.table_info('player')
SQL: PRAGMA 'main'.table_info('player')
SQL: INSERT INTO main."player"("id", "name", "score") VALUES(?, ?, ?)
SQL: PRAGMA defer_foreign_keys = 0
SQL: RELEASE changeset_apply
SQL: INSERT INTO "changeSet" ("id", "data") VALUES (?,?)
SQL: COMMIT TRANSACTION
SQL: BEGIN DEFERRED TRANSACTION
SQL: SELECT EXISTS (SELECT * FROM "changeSet" WHERE "id" = ?)
SQL: SAVEPOINT changeset_apply
SQL: PRAGMA defer_foreign_keys = 1
SQL: PRAGMA 'main'.table_info('player')
SQL: PRAGMA 'main'.table_info('player')
SQL: INSERT INTO main."player"("id", "name", "score") VALUES(?, ?, ?)
SQL: PRAGMA defer_foreign_keys = 0
SQL: RELEASE changeset_apply
SQL: INSERT INTO "changeSet" ("id", "data") VALUES (?,?)
SQL: COMMIT TRANSACTION

Finally, for comparison a successful case when the INSERT statements are triggered "normally" on the dbQueue object:

SQL: BEGIN DEFERRED TRANSACTION
SQL: PRAGMA main.table_xinfo("player")
SQL: INSERT INTO "player" ("id", "name", "score") VALUES (?,?,?)
SQL: -- PRAGMA 'main'.table_info('player')
SQL: -- PRAGMA 'main'.table_info('player')
SQL: SAVEPOINT changeset
SQL: PRAGMA 'main'.table_info('player')
SQL: PRAGMA 'main'.table_info('player')
SQL: SELECT * FROM "main"."player" WHERE "id" IS ?1
SQL: RELEASE changeset
SQL: COMMIT TRANSACTION
database did change
SQL: PRAGMA query_only = 1
SQL: BEGIN DEFERRED TRANSACTION
fetch
SQL: SELECT * FROM "player"
"Player.fetchAll() count" 5
SQL: COMMIT TRANSACTION
SQL: PRAGMA query_only = 0
SQL: BEGIN DEFERRED TRANSACTION
SQL: INSERT INTO "changeSet" ("id", "data") VALUES (?,?)
SQL: COMMIT TRANSACTION
value: [SqliteSessionProject.Player(id: Optional(13), name: "Arthur", score: 653), SqliteSessionProject.Player(id: Optional(14), name: "Arthur", score: 845), SqliteSessionProject.Player(id: Optional(15), name: "Arthur", score: 240), SqliteSessionProject.Player(id: Optional(16), name: "Arthur", score: 675), SqliteSessionProject.Player(id: Optional(17), name: "Arthur", score: 795)]

Am I doing something wrong? Is this kind of functionality supported? It feels like a bug, but I'm also wondering if maybe accessing the db.sqliteConnection directly means our SQL statements are not queued and so the ValueObservation trigger fires too soon. Is there another approach I could take here or something else I could try?

Thanks for any and all help!

[groue]

Hello @gerdemb,

Thanks for the compliment!

You're not doing anything wrong, but ValueObservation does not detect changes performed by sqlite3session_apply. There is no way with the current api to make it work.

Your best option is to cancel and restart the observation after the transaction that calls this function has completed. This is somewhat brutal: your app will have to process fresh values even if sqlite3session_apply did not actually modify the observed region. If you know the content of the change set, you may avoid useless observation restarts.

---

A rationale for the suggested solution

The library relies on the Compile-Time Authorization Callbacks in order to know which tables and columns are read or written to. That's how executing INSERT INTO player can trigger an observation of players, but not observations of other tables. When statements are not compiled by GRDB, the observation mechanism is completely blinded because we don't know which observation(s) could be impacted by database changes.

We could imagine an api that allows you to make the observation mechanism less blind:

// A POSSIBLE API
try dbQueue.write { db in
    // Have related value observations listen to changes inside the block
    try db.withChanges(to: Table("player"), Table("team")) do {
        sqlite3session_apply(...)
    }
}

But it would still be pretty much short-sighted 😅 For example, an observation of Player.fetchCount(db) would be queried for all individual changes performed by DELETE FROM team. In particular, if 1000 teams are deleted, the player observation would have to check 1000 times "Is it the 'player' table?". This loop is bad for performance. Only Compile-Time Authorization Callbacks make it possible to avoid this loop: when we know that DELETE FROM team only deletes teams and has no impact on the player table, we know that the observation of players is not impacted.

Another problem with this technique is that statements that are not compiled by GRDB can trigger the truncate optimization. When this optimization is applied, individual deletions are not reported, and observations would not be triggered. That's another case of complete blindness.

A better option might be to force some observations to fetch a fresh values (even if no actual change was performed):

// ANOTHER POSSIBLE API
try dbQueue.write { db in
    sqlite3session_apply(...)
    
    // Force observations of those regions to fetch fresh values
    // after transaction has been committed.
    if /* we think the player table was modified */ {
        try db.registerChanges(to: Table("player"))
    }
    if /* we think the team table was modified */ {
        try db.registerChanges(to: Table("team"))
    }
}

But this is more or less equivalent to the initial advice: restart the observation when we think it was impacted.

Maybe you have more ideas? I'm curious!

[gerdemb]

@groue Thank your quick response!

There is no way with the current api to make it work.

Thank you for confirming this and saving me time exploring down a path that I was beginning to suspect was never going to work anyway.

As you suggested, I added a trigger to restart the observers after a sqlite3session_apply to refetch any changed values. This works and for now performance is acceptable. In my app, this is already a relatively "heavy" and uncommon operation so I'm not too concerned about performance.

Regarding your proposed solutions, it would be useful if GRDB offered a way to "invalidate" observations after we have modified the database in a way that GRDB cannot detect (using C API calls, etc.). In my particular case, a single call that invalidated all observations on the database would be sufficient although table-level granularity might be useful. Not sure if you would consider this a core requirement to GRDB as it can worked-around by canceling and restarting observers manually, but this is awkward.

Thanks again for your response and advice.

[groue]

And thank you for sponsoring the project :-)

[gerdemb]

Thank you, @groue, for the swift update! I've integrated the latest GRDB version into my project, and it's functioning flawlessly. The addition of this new feature has enabled me to eliminate the cumbersome event-handling code previously needed for restarting observers. It's always satisfying to refactor by reducing code – this is exactly what I appreciate!

[groue]

We're on the same track. Glad it works as expected! Happy GRDB, @gerdemb. If you have other good ideas, please chime in :-)

[gerdemb]

Hi @groue, I wanted to extend my thanks again for your help with my issue with ValueObservation. I thought you might be interested to know about a project I've been working on that uses GRDB called SQLiteChangesetSync. It's a Swift package for offline-first synchronization of SQLite databases across multiple devices. It's using the latest version of GRDB with your ValueObservation fix. If you have a moment, I'd be curious to hear your thoughts on it!

[groue]

Thank you very much for sharing, @gerdemb 🤩 I'll certainly have a look, because sync + offline is a topic I'm very very interested in!