Insert Silently

[Rossdex]

Is your feature request related to a problem? Please describe.

Our app downloads data from a private api where users can download their data and the data the system requires them to have, it has a main 'loading' screen to load this data in using the main connection to the sqlite database. It also has a 'sync' connection to the database that downloads extra items in the background while the user is using the app, there are some fairly complex queries with joins when they are looking at certain 'show' pages that bring in all the related data. This is using a stream so that when the user has completed certain things on these pages the page updates automatically without having to refresh the whole screen.

The main issue we're having is when looking at one of these 'show' pages (which is a watchSingle with joins). There are also futures and other streams on these pages to load in extra data.

This is causing the page to jump/flicker while data is being loaded in the background.

Our example of this is you have a jobs table with a venue id.

The user looks at job 1 which joins in venue 1.

Venue 1 is already in the database as it was downloaded alongside job 1, however when looking at job 1, venue 45 has been downloaded and has caused the page to refresh as the 'venues' table has updated.

Describe the solution you'd like

Is there a way to only listen to certain rows or insert silently when we know the data won't being used on a frontend?

[simolus3]

Venue 1 is already in the database as it was downloaded alongside job 1, however when looking at job 1, venue 45 has been downloaded and has caused the page to refresh as the 'venues' table has updated.

Is it really the queries which are slow here (they should run on a background isolate with NativeDatabase.createInBackground)? If the only problem is refreshing the UI when the stream updates, you can use .distinct() with a ListEquality() on the stream or the part of the UI responsible for showing a single venue. If refreshing the UI in response to new data causes the UI glitches, that should hopefully solve that.

Is there a way to only listen to certain rows or insert silently when we know the data won't being used on a frontend?

This feels like solving a problem that shouldn't exist :D In my mind, this should be solved by the subscriber ignoring duplicate rows where possible. If it turns out that this is still hugely inefficient in your case, I can take a look at ways to inhibit some stream updates though.

[Rossdex]

Thanks for the reply.

Our main issue is that the page refreshes at all, the query is really fast.

As an example our page is setup like this.

StreamBuilder(
           stream: database.venueRepository.get(426),
           builder: (context, snapshot) {
           return  Page Widgets
          }
);

Which is calling

(select(db.venues)..where((tbl) => tbl.id.equals(id))..limit(1)).watchSingle();

Within this stream builder we have other futures/streams active and if anything is added to the venues table while the user is looking at the single venue page it causes a rebuild on the stream and the page to flicker.

[simolus3]

I see, can you see if adding a distinct to the stream helpers? E.g. query.watchSingle().distinct() or venueRepository.get(426).distinct() higher up. That will make the resulting stream not emit an item if it's equal to the previous one. You may have to do that on multiple methods with streams causing rebuilds, but I think that should fix the problem.

[dickermoshe]

@simolus3 Why don't we add distinct automatically? People can use db.tableUpdates if they just want to listen for any changes.