Confusion: How Does SQLiteopenhelper Onupgrade() Behave? and Together with Import of an Old Database Backup

Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

What's the correct way of handling database upgrades of a live app, so the user doesn't lose his data? Do you have to check all possible (old) versions in the onUpgrade() method and execute different alter table statements based on that version?

By and large, yes.

A common approach to this is to do pair-wise upgrades:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (oldVersion<2) {
    // do upgrade from 1 to 2
  }

  if (oldVersion<3) {
    // do upgrade from 2 to 3, which will also cover 1->3,
    // since you just upgraded 1->2
  }

  // and so on
}

This roughly equates to Rails migrations, for example.

What happens if the user has app version 1, exports the database, upgrades the app (new database structure) and imports the old version 1 backup? --> How will SQLiteOpenHelper behave?

If by "copy the whole database away", you literally mean a full file copy of the SQLite database file, then when SQLiteOpenHelper goes to open the restored backup, it will that the database has the old schema version and will go through onUpgrade() as normal.

What is the correct way to handle db upgrades together with import/export functionality?

I suspect the answer is: either make your backup by copying the entire file, or also arrange to backup and restore the schema version, which you can get by calling getVersion() on a SQLiteDatabase object. That being said, I haven't dealt with this scenario much, and there may be more issues that I am not thinking of.

Android SQLiteOpenHelper - how does onUpgrade() work?

The documentation doesn't say this explicitly, but it wouldn't make sense to give both the old and new version numbers as parameters if it would be called for each increment.

Note: it is preferrable to use a series of if statements instead of a switch: as long as the old version is smaller than the version for the n-th step, upgrade that step.
Not only does this avoid duplications, but also prevents you from forgetting a break ...

Changing sqlite3 database data on upgrade

This would work in the exact same way as upgrading schema. You are only running SQL statements. Simply use Update statements instead of Alter Table etc

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (oldVersion<2) {
    // do upgrade from 1 to 2
  }

  if (oldVersion<3) {
    // do upgrade from 2 to 3, which will also cover 1->3,
    // since you just upgraded 1->2
  }

  // and so on
}

You can use Switch or If statements to check the old version. I personally preferIf as I think it is more readable and the fall through is much more obvious.

Reference:
Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

How to test onUpgrade() method before rolling out the app?

Yes that is valid if you follow the simple rules.

Change the version number of your database in the SqliteOpenHelper otherwise OnUpgrade() will never be called.

Ensure both the old app and the new app are signed with the same signature otherwise, you cannot upgrade from the earlier version to the later one. You may need to temporarily sign your debug build with your release key.

Then just install old app, make sure you do whatever you need to do to ensure the database is created. Update to the new app and test.

For reference: Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

Do you have to alter the database schema in the onUpgrade method of the SQLiteOpenHelper?

I was wondering whether the ALTER TABLE query must be executed in onUpgrade or whether I can do it in another method within the SQLiteOpenHelper subclass.

You are welcome to execute ALTER TABLE statements whenever you want, though (as with all database I/O) on a background thread, please.

In your case, I do not know why you are using SQLiteOpenHelper, though. The point behind SQLiteOpenHelper is to help developers building apps with fixed (per version) schemas. That is not the route that you are taking, in which case SQLiteOpenHelper may not really be helping much.

self call onUpgrade method

After a restore, close all open handles to the database, then use your SQLiteOpenHelper to get a writable database. That should trigger onUpgrade() to be called, AFAIK.

Will onUpgrade be called for each increment?

i guess you can use:

if (oldVersion == 1) {
  do stuff;
  oldVersion = oldVersion + 1;
}

if (oldVersion == 2) {
  do stuff;
  oldVersion = oldVersion + 1;
}

or use a switch.

Will my app update sucessfully in market if i add new columns to sqlite?

Using a SQLiteOpenHelper this problem is resolved. Using this class you will have a db_version. Everytime you increase your db_version, you have a method called onUpgrade that will be called.

Check this out.

One thing you have to take into consideration is the db_version of your previous app version. You will need to apply all the changes between your old db_version to your last db_version.

Hope it helps

---

Related Topics