CODEWITHSUNDEEP
androidsqlite
Hirak Chhatbar
Hirak Chhatbar

Reputation: 3181

upgrade sqlite database in my app

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).

Upvotes: 4

Views: 4875

Answers (2)

Anatol
Anatol

Reputation: 961

You can use onUpgrade() method for handling this.

Something like this:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
     if (oldVersion == 1 && newVersion == 2) {
      db.execSQL("create temporary table people_tmp ("
          + "id integer, name text, position text, posid integer);");

      db.execSQL("insert into people_tmp select id, name, position, posid from people;");
      db.execSQL("drop table people;");

      db.execSQL("create table people ("
          + "id integer primary key autoincrement,"
          + "name text, posid integer);");

      db.execSQL("insert into people select id, name, posid from people_tmp;");
      db.execSQL("drop table people_tmp;");
    }

}

So. You are creating temporary table and saving all needed info inside that table. Next you dropping your table, creating new one and inserting values to it from your temporary table. You can add additional fields and feel free to put there all what you want.

UPDATE: After a little googling i found an easier solution:

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

 // If you need to add a column
 if (newVersion == 2) {
     db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
 }
}

Alter table method will change your database structure without loosing data.

Upvotes: 4

display name
display name

Reputation: 4185

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

Upvotes: 1

Related Questions