upgrade sqlite database in my app

Question

So, I already have my app on playstore....

Now, I want to add a column to the database in my app. For this, I must upgrade my databse which can be done by changing the database version.

The users will already have some stuff in the database and when I will upload the updated version of my app (with changed version of the databse), it will create a new databse and user will loose all the stuff he/she has in his/her database.

What is the solution for this issue? And how to backup / restore contents of the old databse to new database? (I know how to backup the database by simply copy pasting the database to external storage programatically).

Answer 1

If you are only adding a new column, you can alter existing table instead of create new table. An example:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if(oldVersion<2){
        db.execSQL("ALTER TABLE "+this.getTableName()+" ADD COLUMN "+COLUMNS.NAME+ " integer default 0;", null);
        db.execSQL("UPDATE "+this.getTableName()+ " SET "+COLUMNS.NAME+ "="+COLUMNS.NAMEVALUE+";", null);
    }
};

Here is Android documentation on ALTER TABLE use case in onUpgrade(). So in this case, if you are not rename or remove existing table, you don't need to backup old table.

If you add new columns you can use ALTER TABLE to insert them into a live table.

Also see: https://stackoverflow.com/a/8291718/2777098