Figure out if select query is dirtied by a mutator query

[cw-dev]

The commit that removes this TODO hinted that it would eventually be implemented, so I figured I'd open this to see if you've had any thoughts since. Wouldn't this be impossible for some queries, or at least insanely hard?

[ursusursus]

This would be greate, as now I have to have distinctUntilChanged everywhere when using Rx, and emits are mostly false positive, + it doubles the work as the is DiffUtil near UI level almost always

[erikc5000]

SQLite only supports table level triggers. I don't think you can know if a given query's results changed without comparing everything -- unless you start making assumptions about what will be changed in response to mutating queries. Seems like it wouldn't be easy to do that reliably. And any query executed outside of SQLDelight would break it.

Edit: Disregard what I said above. I was mistaken about how the invalidation tracking works.

I believe Room requires you to do distinctUntilChanged() also. Could write some extension functions like mapDistinctToList() to cut down on boilerplate, though obviously that won't do anything to improve performance.

[AlecKazakova]

yea, i don't think we'll ever get it perfect but we can eliminate a lot of false positives by leveraging the compiler

for example these two queries are linked:

SELECT *
FROM table
WHERE id = 10;

UPDATE table
SET stuff = 'foo'
WHERE id = 10;

however this one wouldn't effect the queried row:

INSERT INTO table (id, stuff)
VALUES (11, 'foo');

we can determine that statically instead of trying to do it at runtime, and then for bind args its a combination of figuring out which queries could update each other and checking the right parameters at runtime.

[ursusursus]

what about this?

https://www.sqlite.org/c3ref/update_hook.html

it gives you rowid affected

[JakeWharton]

We don't have access to the native APIs on Android, but potentially on native platforms.

[AlecKazakova]

might revisit this in the future, but I'm going to close this out since it'd be a big effort (so I suspect I am the only one who would do it) and I'm not planning on it atm. Would be cool but with distributed databases a proper implementation would be wild

I think the long term solution is probably hooking into specific database implementations that provide result set invalidation

[cw-dev]

yeah this would definitely be a larger undertaking than initially anticipated

incremental view maintenance and dataflow stuff is pretty interesting if you ever decide to circle back in the future! https://github.com/mit-pdos/noria comes to mind