How profound is the DB implementation

[M123-dev]

@monsieurtanuki there is the idea of implementing some parts or even the whole app, inside the website.
The question is how deeply is the DB integrated in the code, can we for example allow to show the product page on web and exclude the caching and adding to history lists.
And is it worth the effort?

CC: @zond @stephanegigandet

[zond]

I suspect another use case (perhaps more realistic) would be to run the app as a mobile web app for users that don't want to install it as a native app.

On the web site it's probably not practical to run the whole app, but instead pick the components that make sense, e.g. to provide a common product info view.

[monsieurtanuki]

The database is accessed only through Dao% classes, like "give me the product 012345666" or "give me the latest 10 scanned barcodes". I guess we could multiplex Dao in order to support web apps too (where sqlite does not exist).

In my own app I really enjoyed using the code (99%) for Android, iOS and web app.

If the question is: "Should we build a different smaller flutter web app from smoothie?", I don't really see the point, we might as well start a new web project with web components.

I'll have a look at sqlite for flutter web.

@M123-dev @zond I'm not sure I fully understood what you're worried about: is that 1) that smoothie uses a sqlite database and therefore doesn't work in web for the moment or 2) that smoothie is too complex for a web app?

[M123-dev]

Thanks @monsieurtanuki , for me it was more point 1 since a large part of the features are currently dependent on the database, I was unsure whether it would be worth making it web-compatible or if it would be too much effort.

[monsieurtanuki]

@M123-dev Let's say that for the moment the web app is not part of the plan AFAIK, and that we're still trying to figure out what the (Android+iOS) app will be.
Once we have a better view on what we actually need and once we implemented that, we'll be able to say which features are important and how we could possibly implement them without a sqlite database.

For instance, we could easily store all products in SharedPreferences instead of sqlite.
For lists, depending on what we really do with them, we could decide to store them in SharedPreferences as well.
But perhaps some use cases definitely need a relational database. We'll see then if we should remove the related features without damage to the spirit of the app (same code and behavior everywhere), if we should remove the related features from the web app only, or if we should try our best to include those features in the web app too.
For the moment it's too early to decide.
cf. #55.

PS: but with the use of Dao%s it would be easy to switch from a tech to another, the only impact being in the Dao% code.

[monsieurtanuki]

@M123-dev Correction: from what I understood, SharedPreferences won't be a good option for storing product data because:

• product data are heavy (like 10K for each product if I remember well)
• in its init SharedPreferences.getInstance() EVERYTHING is loaded in memory (and then we're stuck with the same perf issues as in NoSQL sembast)
• right now opening the SharedPreferences (that only includes about 30 int items) takes more than 1.5 seconds on my old smartphone, while opening the database takes about 300ms (for 16Mb of data, read/written on the fly)

[M123-dev]

@monsieurtanuki , what about hive? There you split your data into different named boxes. From how the dart code is structured I would think that the data is loaded into memory when the box is opened.

It's very likely not comparable to sqflite, but i think we should focus more on lists and not necessarily on caching in the web.

[monsieurtanuki]

@M123-dev I don't think hive is a good solution for us either (cf. https://pub.dev/packages/hive):

• "It is a perfect fit if you need a lightweight datastore for your app." - is that a lightweight solution or a solution for lightweight datastores?
• "Boxes are cached and therefore fast enough to be used directly in the build() method of Flutter widgets." - then all the boxes will be loaded and cached in the init phase (await Hive.initFlutter();)

To be benchmarked if needed.

[monsieurtanuki]

@M123-dev Reading further about hive and the concept of "lazy" boxes...

That probably means that:

• not all the boxes are opened at init time as I assumed at first - the init being just a "we're talking about this file" step
• we may put each product in a different box, to be lazily opened only if needed - we load each needed product into memory, and forget about it as soon as we don't need it.

I think a benchmark would then make sense - I'll reuse the one I coded around sembast vs. SQLite.
I'll keep you posted.

[monsieurtanuki]

I've just run tests with hive (compared to sqflite):

• the tests are a loop of 10 iterations
• open the "database"
• in the case of hive, open the "product" box either as a "standard" box or a "lazy" box (separate tests)
• insert 1000 fake products with unique keys ("bulk insert" for sqflite)
• close the database

As I suspected the main problem with hive is the time it takes to open the database. Of course in the real world we just open it once, but unfortunately we open it as we open the app: it's critical

nb of records	box opening (ms)	lazy box opening (ms)
0	17	25
1000	1351	1156
2000	2726	2029
3000	4004	2806
4000	5315	3535
5000	6733	4230
6000	8048	4955
7000	9649	5833
8000	10954	6667
9000	12250	7592

Which gives us 1.361 ms per product for a box, 0.843 ms per product for a lazy box.
For sqflite the database opening is always immediate, regardless of the number of records.

Regarding the file size, the hive version is slightly smaller (106Mb instead of 123Mb for sqflite) (10K products)

Regarding data bulk insert performance, the hive version is twice faster (2500 ms instead of 6000 ms) when inserting 1000 products.

An optimization for hive could be to use separate "partitioned" boxes for products.

Conclusion - as I said before, the main question is "what are we going to use the database for?":

• if it's realistic to need to cache 10K products, then hive would be a problem
• if we need complex relationships between products, then sqflite is more relevant
• but if we actually need web support, not more than 1K cached products and simple relationships between products, then hive is a decent solution

[M123-dev]

@monsieurtanuki thanks for the information. I am a fan of caching as such but I think with this performance we might not need caching on the web at all. The benchmarks you gave seemed rather daunting and I don't think it bringt that much of an advantage to the potential web users.

If I'm right, it shouldn't be that much extra work to check the platform in the Dao classes and some smaller things like the search history could be quickly rebuild with hive or maybe even sharedPreferences.

[monsieurtanuki]

Hi @M123-dev! I've just tested SharedPreferences too, and it's incredibly slower than hive (around 200ms per product insert - compared to 1ms!!!)

I do think it could be interesting to switch both SharedPreferences and sqflite to hive, and as already mentioned that could be painless because restricted to Dao* classes. And in that case the app and code would be the same for smartphones and web. Maybe at least in 2 steps: first SharedPreferences, then - if relevant - sqflite.

It's also possible to implement different Dao* classes specifically for web, but it's more problematic because there are actually 2 kinds of cache:

1. the cache for what we've just downloaded and will probably use in the next screen - we need that even for web
2. the history cache for cache number 1 - whatever was not explicitly removed; it's a nice-to-have currently implemented with sqflite

A solution could be to specify how many products we want to cache (in the history cache).
Even if the end-user does browse hundreds of products on the fly, there's a good chance that only a max of hundred of products are needed in the history cache or in the pantry. That would mean that after the threshold is reached, each time we want to insert a new product we need to delete the "least interesting" product.

Interesting issue, I'm willing to work on it, but first I need more feedbacks about the planned uses of the database and the relevancy of a flutter web app. @stephanegigandet @teolemon ?

[monsieurtanuki]

From @jasmeet0817's comment ("the concepts of user defined lists, shopping lists and pantries are discarded") I see that most of the database complexity can be removed.

That's would be a good opportunity to switch to hive:

1. the code would be written from scratch without its now useless historical layers
2. the code would be easier to read for developers that understand much better JSON than SQL
3. and we'll have the possibility to have the app work on web + desktop

@jasmeet0817 @M123-dev Your opinion on that?

[jasmeet0817]

Hi,

Hive looks secure and easy, I just have a couple devil's advocate questions.

1. What would happen if in one of the later versions/stages of the app we have more app complexity, will that cause us to move out of Hive and into another DB solution ?
2. How much engineering effort do you see here? Since we are getting closer to the launch date, I wonder if it's a reasonably big task than we could just continue with what we have.

[monsieurtanuki]

Good questions. My answers:

1. Maintenance-wise we're not safer with SQLite, that basically I'm the only one to understand here - or at least to code. Even before today (where your comments implied that the db could be highly simplified) I thought about switching to hive, I mean, with the current complexity of pantries. The choice for SQLite months ago was based on expected usages (e.g. 1000s of products locally stored) that don't match the app as it's supposed to be now. In the worst case scenario we can always put JSON strings in hive in order to implement the db complexity: we won't be prevented from coding anything.
2. I can code the hive part in a day or two. It will also be a very good opportunity to remove tons of code about pantries - much clearer code. By the end of the week.

[M123-dev]

Yes I think that is a good idea, since the list feature is (unfortunately) no longer provided it can probably really save a lot of code and even if we later need parts of the current SQFlite code for offline scanning or some kind of lists for something we can thanks to git always reach back to this state and grab some code.

[jasmeet0817]

Yeah, I also agree it's a good idea to go with Hive

[monsieurtanuki]

Working on it...
I'll probably won't write the data migration - I guess we can assume in first approach that the data stored so far is not that relevant. Tell me if you don't agree.

[M123-dev]

Yes, we don't need a migration, but we should delete the old data so that we don't have it stumbling around on the phone forever.

[monsieurtanuki]

@M123-dev Just uninstall the app then: I won't keep sqflite just to be able to remove the database.
Or I could delete the smoothie.db file, forever.

[M123-dev]

Ok, then let's remove sqflite completely, I won't miss the few kb extra