Android SQLite Database, WHY drop table and recreate on upgrade

Question

In the tutorials I am following and a lot of more places I see this, onUpgrade -> drop table if exists, then recreate table.

What is the purpose of this?

private static class DbHelper extends SQLiteOpenHelper{

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL);"
        );  
    }

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

Answer 1

Well, the most common method in android applications is to "relog" the user when a database upgrade is in order. And considering any local database should only be mirroring what is on the serverside application, it is much easier to just drop the database, recreate it and repopulate from the server than it is to carefully plan migrations from one version to the other.

It certainly isn't the best approach, but it is easier.

To make an example of how it would be implementing a migration (a change from an older version of a database to a newer one)

Lets say in your DbHelper class you define that your database is version 1, in a later version of your application (version 2), you need a few more columns in one of your tables.

So you would need to upgrade your table and add the columns via ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Check this link for that -> Insert new column into table in sqlite ?

so your onUpgrade() method would have to reflect that change by adding:

 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion){
        case 1:
            db.execSQL("ALTER TABLE " + DATABASE_TABLE + " ADD COLUMN " + NEW_COLUMN_NAME + TYPE);
    }
}