Sqliteopenhelper Onupgrade() Confusion Android

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: Determining reason for onUpgrade exceptions

You can wrap your onUpgrade() in a try-catch that logs and rethrows the exception. Along the lines of:

try {
    db.execSQL(/* upgrades */);
} catch (Throwable t) {
    Log.w(TAG, "onUpgrade failed", t);
    throw t;
}

Why does SQLiteOpenHelper drop the table in onUpgrade method?

If code executes "drop table", DB table data of old version DB will be
removed, isn't it?

Yup

Why need "onUpgrade" method?

If you are switching databases (for example because you added a new column), your app (usually) now depends on that change. Increasing the database version in your Helper class calls onUpgrade(), which allows you to take care of any migration to prepare the app to use your new schema.

Did you know why learned code executes "drop table"?

Convenience. It's not necessarily the right approach, but a database change can make it hard to take old data and merge it in the new table. Thus, it is easier logic-wise to simply start anew.

If you want to merge an existing and new table, have a look at this question.

why we need to onUpgrade(); method in SQLiteOpenHelper class

onUpgrade is basically for handling new db changes(could be new columns addition,table addition) for any new version of your app.

Droping the table is not always necessary in onUpgrade it all depends on what your use case is. If the requirment is to not to persists the data from your older version of app then drop should help,but if its like changing schema then it should only have alter scripts.

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.

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 ...

When does SQLiteOpenHelper onCreate() / onUpgrade() run?

SQLiteOpenHelper onCreate() and onUpgrade() callbacks are invoked when the database is actually opened, for example by a call to getWritableDatabase(). The database is not opened when the database helper object itself is created.

SQLiteOpenHelper versions the database files. The version number is the int argument passed to the constructor. In the database file, the version number is stored in PRAGMA user_version.

onCreate() is only run when the database file did not exist and was just created. If onCreate() returns successfully (doesn't throw an exception), the database is assumed to be created with the requested version number. As an implication, you should not catch SQLExceptions in onCreate() yourself.

onUpgrade() is only called when the database file exists but the stored version number is lower than requested in the constructor. The onUpgrade() should update the table schema to the requested version.

When changing the table schema in code (onCreate()), you should make sure the database is updated. Two main approaches:

1. Delete the old database file so that onCreate() is run again. This is often preferred at development time where you have control over the installed versions and data loss is not an issue. Some ways to delete the database file:

   • Uninstall the application. Use the application manager or adb uninstall your.package.name from the shell.

   • Clear application data. Use the application manager.

2. Increment the database version so that onUpgrade() is invoked. This is slightly more complicated as more code is needed.

   • For development time schema upgrades where data loss is not an issue, you can just use execSQL("DROP TABLE IF EXISTS <tablename>") in to remove your existing tables and call onCreate() to recreate the database.

   • For released versions, you should implement data migration in onUpgrade() so your users don't lose their data.

---

Related Topics