Using updates stream for watch()

[rkistner]

I'm not that familiar with the Drift codebase, but as I understand, Drift currently uses pre-parsing of queries and associated triggers and views to determine which tables could be affected by an insert/update/delete statement.

Is there a reason for not using the native SQLite update notifications for that, as exposed in the updates stream?

My main use case is to be able to use customStatement or similar for arbitrary custom queries, and still have watch() on other queries function correctly.

At this stage I'm happy to manually pass in tables for the watch() query part - I can use EXPLAIN with the same statement to get the tables it depends on (see this for the implementation).

Other advantages of using the updates stream:

• It supports arbitrary statements and triggers - does not depend on the query parsing to correctly identify all affected tables.
• It will only notify when the table was actually written to, instead of whenever the statement might write to the table. The current implementation appears to already handle common cases (no rows modified), but not more complex cases (e.g. conditional trigger only sometimes updating another table).

At my company we're happy to contribute an implementation of this. But before we start - is this functionality you'll accept as a PR? Are there any major things to take into consideration?

[simolus3]

Is there a reason for not using the native SQLite update notifications for that, as exposed in the updates stream?

Originally, because drift was using sqflite only which doesn't give us access to these notifications. There are some remaining issues like this not working for WITHOUT ROWID tables and truncates, but using this channel in addition to the existing update mechanism we have in drift could definitely be useful, and I'm contributions for this would be welcome!

At this stage I'm happy to manually pass in tables for the watch() query part - I can use EXPLAIN with the same statement to get the tables it depends on (see this for the implementation).

Nice idea!

It supports arbitrary statements and triggers - does not depend on the query parsing to correctly identify all affected tables.

In drift we resolve the issue by looking at triggers and foreign key constraints at build time to build a graph of rules describing which additional tables might be affected by updates.

It will only notify when the table was actually written to, instead of whenever the statement might write to the table.

That's a big advantage for sure. Introducing update notifications as a reliable source and then only relying on the existing mechanism for WITHOUT ROWID tables would be a big performance win due to fewer queries being invalidated.

Are there any major things to take into consideration?

Thanks a lot for your interest in making this contribution! This will likely require some architectural changes to the package (that I haven't fully thought through yet). The QueryExecutor interface used to talk to databases must be kept stable, but we have the DatabaseConnection class which allows supplying a custom StreamQueryStore that could be used to report these kinds of updates. NativeDatabase and WasmDatabase could have static factories returning an instance of DatabaseConnection (which implements QueryExecutor) to use the custom query store. Then this also needs to be handled in the remote protocol so that it works for drift isolates.

Another thing to consider is transactions: In drift, a transaction updating a table will directly invalidate query streams made inside of the transaction. Queries created outside of the transaction won't be invalidated if the transaction is rolled back however. And if a transaction makes multiple writes, these are all batched up and delivered in one notification to avoid invalidating streams more than necessary.