-1

My Android App use SQLite database as an asset. I deliver a .db file in /assets directory to provide both read-only tables and read/write tables. When the user uses the App, it's datas are stored in read/write tables.

Sometimes I need to modify the schema of the database by adding a column in a table for example. So I need to deliver a new .db file in the release.

If i do this the problem is that the user loses his data.

Is there a way to export/import the user data when he updates the App on his devise ?

I didn't find anything about this issue on the Web.

Julien
  • 1
  • 1

1 Answers1

0

The standard way of handling this is, assuming that you are using (extending) the SQLiteOpenHelper class, is to utilise the version number in conjunction with overriding the onUpgrade method to ALTER the schema of the existing database, thus retaining the existing data.

version int: number of the database (starting at 1); if the database is older, onUpgrade(SQLiteDatabase, int, int) will be used to upgrade the database; if the database is newer, onDowngrade(SQLiteDatabase, int, int) will be used to downgrade the database

The SQLiteOpenHelper (or if using SQliteAssetHelper which extends SQLiteOPenHelper), when opening the database (if it exists) compares the coded version number against the version number stored in the header of the database. If the coded version number is greater than the stored version number then the onUpgrade method is called which is passed three parameters,

  1. the SQLiteDatabase
  2. the old (stored) version number as an int
  3. the new (coded) version number as an int

You would typically check old and new and have code that makes the changes (ALTER TABLE or an alternative if the limitations of the ALTER TABLE force to do something like rename the original table, CREATE the changed table with the correct name, copy the existing data from the original table to the new table and finally DROP the renamed original table )

If the App is installed for a new user then that is when the asset will be copied. Thus covering both scenarios.

If not using an extension of SQLiteOpenHelper, then you can mimic using the stored version number by reading 4 bytes at offset 60 and comparing this to a coded version or even by copying the asset and getting the asset's version number, which would be managed with whatever tool you use to maintain the asset (e.g. via the user_version PRAGMA)

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Thanks Mike. It is agreed that I have to implement onUpgrade method. But there's something I don't understand. If I release a new version of the database so I have to copy the user data from TABLE1 of the old database to the new TABLE1 (same name) to the new database. How to identify the new and old database. I suppose the SQL should be something like this : INSERT INTO new_db.table1 SELECT * FROM old_db.table1 But what is "new_db" and "old_db" in Flutter/SQFLite context ? – Julien Jan 24 '23 at 22:59
  • @Julien something like `ALTER table1 RENAME TO table1_original;` then `CREATE TABLE table1 ....` then `INSERT INTO table1 SELECT * FROM table1_original;` then `DROP table1_original;` – MikeT Jan 25 '23 at 04:03
  • Ok I understand. So normaly, the "ALTER table1..." will modify the table from the old .db file (from the old database actually installed from the previous release). In my case I don't need the "CREATE ..." because the new table will be provided by the new .db file in the new release. What disturbs me is that the manipulation of the tables is implicit and not explicit with respect to the database to be used. I'll try. – Julien Jan 25 '23 at 22:45
  • Apparently, DROP isn't allow in onUpgrade() : https://stackoverflow.com/a/3505944/12360489 – Julien Jan 26 '23 at 16:57
  • @Julien that is saying that the ALTER command doesn't support DROP, which means DROPing a column. It is not saying that DROP TABLE can't be used. ALTER does now support DROP column see https://www.sqlite.org/lang_altertable.html. DROP TABLE has been used in onUpgrade probably since inception. It most certainly can be used in onUpgrade but typically `DROP TABLE IF EXISTS thetable;` which would not fail if the table doesn't exist, whilst `DROP TABLE thetable` would fail if the table does not exist. – MikeT Jan 26 '23 at 18:58
  • Finally a I just put this in the onUpgrade() function : `batch.execute('ALTER TABLE TABLE_NAME ADD COLUMN COLUMN_NAME COLUMN_TYPE');` – Julien Feb 05 '23 at 18:41