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

Question

I m following this tutorial.http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/

can any body please make me clear this chunk of code.

 // Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

    // Create tables again
    onCreate(db);
}

Questions

What is the purpose of onUpgrade(); method?

When it is Called? as docs says this is Called when the database needs to be upgraded what does it means by upgrading the database?

Important

why we drop the table in this method and recreate?

Thanks in advance.

Helfant

Answer 1

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.

Mayfly

ok i have cleared some how. but when it is called. if I add a new column in my existing table, will this automatically called? – Helfant

lets say u have an app in google play with dbversion=1 and now you are planning to relase a new version of your app with additional feature which needs database changes. so u change your dbversion=2 and the onUpgrade method automatically gets called for the users who are already using ur old version of the app. Hope i am cler here. – Mayfly

:) Yes still cleared some how. also there is a method of onDowngrade(). when it is called? – Helfant

Its just opposite of what onUpgrade does but yes strictly speaking u wont normally overide it. – Mayfly

Answer 2

Upgrade means changes have been made to the database schema(version numbers are different) and it needs to be upgraded. Dropping the table and recreating is one way to do that. You could also issue "alter table" statements.

Tadio

+1 for altering issue in SQLite. I want ask one thing more is onCreate() called every time whenever we make a call to DBHelper class.? if so, will this not overwrite my existing database? – Helfant

No. That is only called when the database is created the first time. – Tadio

Answer 3

When you open your database it checks the version number and whether or not it exists. You can just "upgrade" your database rather than creating it new.

A good tutorial: http://www.vogella.com/articles/AndroidSQLite/article.html

Peeples

what happens here if I do not drop my table in onUpgrade method? – Helfant

It's all up to you. Just remember that you cannot control how/when users update. Some will go from 1 to 2 to 3, etc. Other will start at 2 or 3, etc. Some will start at 1 and never update ;) (happens, trust me.) And still others will skip a version (or 5). That's why you need create and upgrade methods. Make sure you have a path. – Peeples

Answer 4

This method is called when you update your database version. It drops the existing tables and creates it again when onCreate method is called again.

Zwinglian

Answer 5

Replying to your 4 questions:

1) The purpose of onUpgrade is to manage a new database structure. You could start you app with simple features, then you need for instance to add a new column, so you need to increase the version of your database from 1 to 2 and in onUpgrade give the instruction to add a new column, so that if the user update the app, the new column become added.

2) onUpgrade is called when you have a new version of your database and you incremented the int number in the super method( here is 1, so you eventually change it to 2)

 public static class DatabaseHelper extends SQLiteOpenHelper{
    DatabaseHelper(Context context){
        super (context,DATABASE_NAME,null,1);
    }

3) Please see above regarding what does it means to update the db

4) We Drop the table and recreate, because to modify the table (example for adding a new column that fits a new feature) a logic way to proceed could be, before to "destroy"/DROP the table and then create a new one with all the data. But this can be not the way to go although recreating the data could mean that the id numbers will be consecutive( usually are not consecutive: you could have 1, 2, and..4 because 3 has been deleted), hence dropping and then creating the table again, and eventually loading the previous data you could have this id consistency. Sometimes you may want to use ALTER instead of DROP. Why? Usually because using DROP the user loses the content already has in the database, then if you want to learn more about Best practices and more complex real life scenarios please have a look at this amazing reply

Kieffer