ValueObservation with specific columns?

[mackuba]

Is it possible to tell ValueObservation to only observe changes in specific columns of the records (or when whole records are added/removed), but not in other fields?

What did you do?

I do something like this:

let galleries = Gallery.select([Gallery.CodingKeys.id])
let photos = Photo.select([Photo.CodingKeys.id])
let observation = ValueObservation.tracking([galleries, photos], fetch: { ... })

What did you expect to happen?

I would like to only be notified when the listed fields are modified.

What happened instead?

The fetch block gets called and the observation sends a notification through rx when some other fields like status are changed in the monitored records. I use this to refresh a table view, but fields like status aren't displayed there so there's no point reloading if they are changed.

[groue]

Hello @mackuba,

Well done, you have found a bug! I can reproduce your issue.

I think it is a bug in SQLite itself, but I will have to check on their mailing list. It can partially be addressed in GRDB itself, but we'll always find requests that exhibit the same unexpected behavior.

As far as I can tell, after a few experiments, the problem only happens when one observes a single column which is the integer primary key. The problem vanishes when you add any other column to the request, or when you observe other columns, or when the primary key is not an integer.

Maybe this can help you find a workaround?

Also, can you please tell with version of GRDB and SQLite you are using?

let version = try dbQueue.read { db in
    try String.fetchOne(db, "SELECT sqlite_version()")!
}
print(version)

[mackuba]

Well done, you have found a bug! I can reproduce your issue.

I'm lucky, I guess ;)

As far as I can tell, after a few experiments, the problem only happens when one observes a single column which is the integer primary key. The problem vanishes when you add any other column to the request, or when you observe other columns, or when the primary key is not an integer.

Hmm, I've actually simplified the example because in reality I tried to observe 2 columns in one table and 4 columns in another... I will do some more experiments.

Oh, and the requests are also filtered through associations - what I'm actually fetching is:

• all "photo sequences" belonging to a specific "gallery", tracking their id (Int) and position (Int)
• all photos joining sequences where the sequence belongs to that specific gallery, tracking their id (Int), position (Int), file name (String) and sequence id

Basically I want to be notified when a sequence is added or removed or when the set of photos in each changes, but not when the status of a photo or a sequence changes. So there might be a few more things there that are triggering the issue or making more specific tracking impossible...

Should I be seeing this list of fields somewhere in the logs when I use the trace property in the config to print all statements? Because I don't, I only see select * from ... or select "photo".* from ....

I'm mostly testing in a simulator, the Sqlite version is 3.24.0.

[groue]

OK. We have found one bug, but there may be more... Let's try to figure this out, @mackuba.

Should I be seeing this list of fields somewhere in the logs when I use the trace property in the config to print all statements? Because I don't, I only see select * from ... or select "photo".* from ....

You will see in trace the executed SQL statements. Observed requests are not executed, so you won't see them in the trace.

But tracing is a very good idea. I do, too, want to know what GRDB actually observes.

To know that, would you run add a few lines of code to your app, and report the result here?

Take the real requests you are tracking, the ones you feed ValueObservation with:

let request1 = ...
let request2 = ...

Add those lines:

try dbQueue.read { db in
    // sql
    try print(request1.prepare(db).0.sql)
    try print(request2.prepare(db).0.sql)
    // regions
    try print(request1.databaseRegion(db).debugDescription)
    try print(request2.databaseRegion(db).debugDescription)
}

You should see raw SQL statements, followed by something like photo(*), gallery(id, status), etc.

Can you report those results here, please?

[mackuba]

Did you mean description? There doesn't seem to be a debugDescription.

Here's what I got:

SELECT "id", "position" FROM "draftPhotoSequence" WHERE ("draftGalleryId" = ?)
SELECT "draftPhoto"."id", "draftPhoto"."position", "draftPhoto"."localFileName", "draftPhoto"."draftPhotoSequenceId" FROM "draftPhoto" JOIN "draftPhotoSequence" ON (("draftPhotoSequence"."id" = "draftPhoto"."draftPhotoSequenceId") AND ("draftPhotoSequence"."draftGalleryId" = ?))
draftPhotoSequence(draftGalleryId,id,position)
draftPhoto(draftPhotoSequenceId,id,localFileName,position),draftPhotoSequence(draftGalleryId,id)

(looks like what I'd expect)

[mackuba]

I'm on version 3.6.2, I see 3.7 was released after I've set it up, but not sure if that changes anything...

[mackuba]

I've updated to 3.7, but it works as before.

[mackuba]

Oh maan, I found it... 🤦🏻‍♂️

It's because I'm using photo.update(db) to make changes, this has bitten me before when one property was mysteriously resetting back after I've changed it, because in another place the record was kept for some longer time and then I called update on it to change something else, but update updates all fields, including this one which was stored there in an earlier unchanged version.

This was one of those pieces of code triggering the observation unexpectedly:

var draftPhoto = objects.draftPhoto
draftPhoto.status = .uploaded
draftPhoto.isBeingProcessed = false
try draftPhoto.update(db)

This doesn't seem to:

var draftPhoto = objects.draftPhoto
try draftPhoto.updateChanges(db, with: {
  $0.status = .uploaded
  $0.isBeingProcessed = false
})

[groue]

That's great, @mackuba! Isn't our job funny sometimes? Report an issue, find another bug 😄

It's because I'm using photo.update(db) to make changes, this has bitten me before when one property was mysteriously resetting back after I've changed it, because in another place the record was kept for some longer time and then I called update on it to change something else, but update updates all fields, including this one which was stored there in an earlier unchanged version.

More seriously, what you wrote is very important. I'm quite happy you have found the difference between update(_:) and updateChanges(_:with:). The update(_:colums:) method is also a valuable item in the tool belt. And all of them may throw PersistenceError.recordNotFound, in case the database does not contain any row to update. The reference can be found at Persistence Methods and The updateChanges Methods.

It takes some practice to pick the update method that fits each particular situation in your applications. You are becoming a fluent GRDB user 🎉

I'm closing this issue now. It is added to the TODO list, so that it is not forgotten.