Sunday, April 6, 2014

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);
    }

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Somebody essentially lend a hand to make severely articles I
    might state. That is the very first time I frequented your website page and thus far?
    I amazed with the analysis you made to make this actual put
    up amazing. Fantastic process!

    https://www.vwvortex.com/members/blankcorp.3804991/#about

    ReplyDelete
  3. Poker369
    Poker369 Situs Poker Online Indonesia Terpercaya, Poker369, Daftar Poker369, Login Poker369, Link Alternatif Poker369, Poker369 Android, Deposit Poker369, Poker369 Apk, Poker369 Live Chat.

    ReplyDelete
  4. Pokerbo
    Pokerbo Merupakan Situs Poker Terpercaya, Informasi Mengenai Poker bo, Daftar Pokerbo, Link Alternatif Pokerbo, Login Pokerbo, Deposit Pokerbo, Pokerbo Mobile, Pokerbo Android, Livechat Pokerbo, Pokerbo.com ada disini

    ReplyDelete