Android SQLite Database, WHY drop table and recreate on upgrade

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:

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

I agree when you upgrade you should be adding columns or adding tables to your database. Most of the onupgrade samples actually suck because why am I deleting all this data then recreating the table? I found this blog entry I call it the Adams Incremental Update Method. It also handles situations where users may have not upgraded your app with each release.

Here is a good blog on sqlite onupgrade that doesn't do drop table.

Depends on the kind of approach you want to create and how important your data, what complexity of table it is.Example: If your app has been upgraded quite a lot of times and table structure have change enough times, then its better to drop table and re-create it, rather than writing code for changing structure for every version of db, in this approach you will have to make sure that you can backup any data that you have on server side, so that things remain ok.

From my exp: recreating is better if you can judge that there might further be changes in future, else it gets quite complicated