SQLiteDatabase: Handling upgrades

When you're looking for tutorials online on how to create an SQLiteOpenHelper. You will probably find one where the onUpgrade() method drops your whole table and recreates it. In a lot of cases, you don't want the user to lose all their data whenever a database change has been made in a new version of the app.
It would be really bad practice when this would happen.

Now, how can we handle database upgrades then?
For this, let's have a look at the arguments that are passed in the onUpgrade() method. We have our SQLiteDatabase, the version number of the old database and the version number of the new database.

You could use a switch, checking the new version number and run some SQLite ALTER methods in the case block. That way, every time a new version is installed it will only run the ALTER methods of that new version. But when a user switches from version number 1 to version number 3, the changes for version number 2 will not have happened.

Next option is using a fall-through switch, where it jumps in on the right version number and runs all the changes that have happened since then. So suppose the version number is 3, then it will run all the changes for version 3 and version 2. The problem is, if the changes have already been done, it will try to run them again. This might cause a SQLiteDatabaseException for example when you're adding a new column, it might have a duplicate column name.

There's one way to solve this:
Use a fall-through switch and check if the old version number is the same as the case. When this happens, break out of the switch before running the changes.

Here's an example of this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  switch (newVersion) {
  case 3:
   if(oldVersion == 3) {
    break;
   }
   db.execSQL("ALTER TABLE " + CONTACTS_TABLE + " ADD COLUMN " + CONTACTS_EMAIL + " TEXT");
  case 2:
   if(oldVersion == 2) {
    break;
   }
   db.execSQL("ALTER TABLE " + CONTACTS_TABLE + " ADD COLUMN " + CONTACTS_AGE + " INTEGER");
   
  }
 }

This method might be helpful to you and your users.

At all costs, try to avoid this if it's possible:

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + CONTACTS_TABLE);
        onCreate(db);
    }