Ensure that Smoothie can read and migrate the database of Open Food Facts v1 on Android

[teolemon]

What

• Ensure that Smoothie can read and migrate the database of Open Food Facts v1 on Android
  • Understand how the original database is structured:
    • Original database https://github.com/openfoodfacts/openfoodfacts-androidapp/blob/develop/app/src/main/java/openfoodfacts/github/scrachx/openfood/utils/OFFDatabaseHelper.kt
    • https://developer.android.com/studio/inspect/database
    • The class DaoMaster is generated dinamically by the Gradle GreenDao plugin during build.
    • Every table is made of rows represented by GreenDao @Entityes.
    • You can find the Entities classes here (except the MapOfStringsToStringConverter.java)
    • They are the only classes in java because GreenDAO does not support Kotlin so no way to convert them.
    • Why the migration should be done entirely by the new app: What if a user hasn't opened the app for a month, and gets updated silently by the Play Store / App Store ? The only way to do reliably the migration is to have the Flutter app do it.
  • Choose a DART library to read it
  • Write a migration script for scan history
  • Write a migration script for lists (Android) #1682
  • Write a migration script for allergens settings
  • Write a migration script for OFF user account
  • Write a migration script for apps settings
  • Add a boolean in the app that gets toggled once the script has been run once (successfully or unsucessfully)

Related

• iOS side: Ensure that Smoothie can read and migrate the database of Open Food Facts v1 on iOS #1233

Part of

• Launch improved app as an update on AppStore and Playstore (Tracker) #502

[monsieurtanuki]

@teolemon We need help from the Android team here, to provide us with each needed table creation script:

• which tables do we need?
• I could not find openfoodfacts.github.scrachx.openfood.models.DaoMaster and more specifically its DaoMaster.createAllTables(db, true) method

[teolemon]

@VaiTon would like to have your input on that one.

[VaiTon]

The class DaoMaster is generated dinamically by the Gradle GreenDao plugin during build.

I'm not sure though how we'll be able to read from the native app DB since we're using another ID and android doesn't allows to read from a different app unless stated otherwise by the native app.

[M123-dev]

But changing the app id shouldn't be a big problem right

[monsieurtanuki]

@VaiTon Among the hypotheses was the perilous option to let "smoothie" pretend being a next version of the current android app (same package), in which case we would have been able to use the existing database. But that would be very dangerous, wouldn't it?

Anyway, the first question is: "is there a way to get the schema of the current android database, possibly with the meaning of each column?". Like, the generated DaoMaster code?

But changing the app id shouldn't be a big problem right

@M123-dev I don't share your optimism...

[teolemon]

The idea is to swap the app, so the Flutter app would become source wise org.openfoodfacts.scanner, and the Classic Android app would become the current org.openfoodfacts.app

[teolemon]

It does have to be done carefully

[VaiTon]

Anyway, the first question is: "is there a way to get the schema of the current android database, possibly with the meaning of each column?". Like, the generated DaoMaster code?

Every table is made of rows represented by GreenDao @Entityes.

You can find the Entities classes here (except the MapOfStringsToStringConverter.java)

They are the only classes in java because GreenDAO does not support Kotlin so no way to convert them.

[monsieurtanuki]

It does have to be done carefully

@teolemon I like your understatement, so British :)

Thank you @VaiTon for those details.
From what I read in the code the problem is that I do know relational databases (like SQLite), but not at all GreenDao annotations.

And the main trouble with all that "swap" idea is that I cannot picture how to test the migration from one android version of the app to the flutter version of the same app.

I definitely think we should rather implement some kind of data sync between android and the server, and between flutter and the server (and of course ios / server). That way we implement a meaningful, testable and reusable code.

[VaiTon]

IMHO the only thing worth moving (if any) are user lists. Offline products are not made to stay indefinetly in local storage and allergens can be set at the app first start.

[teolemon]

history being a userlist. agreed @VaiTon
as for storing user data on the server, not a big fan at this point.

[VaiTon]

If that's the case we can think of implementing a custom content provider into the native app and then query it from flutter (idk if that's possible) as stated in this post to get a csv/json/interoperable format that we can parse in flutter.

[monsieurtanuki]

Indeed it would make sense to be able to:

• export a product list json from android/ios
• import a product list json into flutter
• the json being a file or a clipboard, whatever is convenient for both android/ios/flutter
• we may even add the allergens settings

That mechanism

• is easy to code
• is not far-fetched UX-wise for the users
• is easy to test on all sides
• could be reused for a future "hey I send you my list of favorite soy milks" feature

[monsieurtanuki]

Would you guys want me to work on a module that imports a product lists json?

[Edit]: little detail - for the moment we only have "history" and "scan history" lists in smoothie, no user-defined lists.

[teolemon]

That would be awesome.
The history would be a very good first point, without any user visible distinction as to whether they were scanned or opened by other means (like I believe is already the case)
And very clearly being able to preserve user-defined lists is a must for app swap.

[monsieurtanuki]

@teolemon Ok then.
I'll work on a structure similar to this one:

{
   "list": ["12345", "23456", "34567"],
   "data":{
      "12345": {"qty":5}
   }
}

Probably for "history" we won't have a "data" section, unless you keep timestamps (we currently don't in smoothie).

[VaiTon]

@monsieurtanuki what's the use of data? We only keep a list of barcodes and a timestamp for products in history

[monsieurtanuki]

@VaiTon The use of data is optional.

In previous smoothie versions there were data attached to products (for a shopping list a quantity, for a pantry a list of qty * "best before" dates, for history a "last seen" date).
And I think shopping lists are still in the pipes for flutter, therefore we need at least to think about product data. As opposed to a mere json-encoded string list which wouldn't allow us to grow, structure-wise.

That said, regarding the migration from android

• perhaps we don't need data attached to products (e.g. because they don't exist in the current android version)
• perhaps we need data attached to the list itself (but for the moment we don't have anything like that in the flutter version)

[teolemon]

any update on this ?

[monsieurtanuki]

@teolemon I'm starting an export/import feature on Smoothie, that can be used "internally" from list to list, perhaps through the clipboard.

[teolemon]

I've updated and clarified the issue, based on the discussion here and on #1735
cc @VaiTon

[g123k]

Here is the plan that we've discussed the other day during the community event:

• Since the deadline is approaching, we won't develop any native code
  We will instead really on Flutter packages: sqflite for Android and realm for iOS.
  Since importing those two packages will bring Realm for Android and SQLite for iOS, we will fork both packages to remove their unnecessary Android and iOS implementations.

• On Android, lists only contain the bare minimum: barcode and product name
  The process will consist of listing all lists/products, and they fetch them one by one

• Since this code will be removed in the feature, I tend to think it has to be developed in a separate package.

[M123-dev]

I'd say a dedicated folder would be enough but feel free to create a new package, I mean we already have the folder structure for that

[monsieurtanuki]

@g123k Understood.
I hope you guys already have your Android and ios versions on your devices, with populated lists, and that the flutter version switch will not delete somehow the databases.
Regardless of the way barcode lists are going to be extracted from the legacy databases, we need an async method that takes the barcode list, downloads all the related products and stores both the list and the products locally. I can do that.
I guess a migration folder in the existing smoothie package would do the trick. We may even reuse that code later for barcode list sharing between friends.

[g123k]

Hi,

A quick world after this morning's presentation, where the migration on Android is mostly OK.
We can export credentials, history, and user lists from V1.

• Lists (including history) are stored in an SQLite Database -> A fork of sqflite is used
• Credentials are based on preferences, but no Flutter Plugin allows the usage of a custom file -> Native code is used here

Regarding the architecture, we have two packages:

• data_migration_shared with some shared interfaces/models between Smoothie & data_migration
• data_migration where the code for both platforms is implemented (still in progress for iOS)

On Smoothie, the import feature has been a little tweaked and used (thanks @monsieurtanuki) to now support history and credentials.

The migration is started on the first launch. If for some reason, it failed, it will retry later (a maximum number of retries is implemented). We also consider the credentials import as non-blocking because the user may have changed his password.

A few things are still in progress (e.g.: limiting the number of requests with a large history…), but for Android, we can assume that it's OK. Before releasing any PR, I prefer to finish implementing the iOS part, to be sure I don't have to change the API between Smoothie <-> package.

If you have any questions, feel free to do so!