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

Migrate the data from the existing database into the new database with the additional column. When migration is done, the old database can be removed. So for a while you have 2 databases — Tim, Jan 10 '15 at 17:18
Does your new app come with a pre-created database or do you just want to update the old one? — Simas, Jan 10 '15 at 17:20
@TimCastelijns well, thats one solution. Thank you very much. But this will be too much work for just adding a column to a table in the database. Lets see if anyone knows any shorter solution. If not, migration stuff will be the only saviour — Hirak Chhatbar, Jan 10 '15 at 17:23
@user3249477 - to be more specific, my database has a table which contain user added notes. Now, I want to add a column to that database table and this can be done by changing database version. This will create a new database and hence, user will lose the notes that he prevously created. — Hirak Chhatbar, Jan 10 '15 at 17:25
if you are adding a column to existing table, you can use ALTER in onUpgrade() instead of CREATE to avoid date overwrite — display name, Jan 10 '15 at 17:37

Anatol · Accepted Answer · 2015-01-10T17:54:38.240

4

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.

edited Jan 10 '15 at 17:54

answered Jan 10 '15 at 17:28

Anatol

941
1
7
13

I never knew this existed... let me google a bit and see if it works... +1 for your effort :) – Hirak Chhatbar Jan 10 '15 at 17:39
@user3249477, sorry, but i can't understand your answer. We are architectures of our database, so we defenitely can know if this column name is using inside our app. And we can operate with tables with a normal sql language. So what do you mean by your question? – Anatol Jan 10 '15 at 17:43
If you later change the database version to, say 3, will it try to re-add this column again? Because `onUpgrade` will definitely be called. – Simas Jan 10 '15 at 17:47
as fas as I can understand, we also need to add our new columm to the create table query also, so that if the database is being created for the first time (like for the new users), the new column will alse be created and if the database exist, it will add the column (for current users)... awesome stuff (y) – Hirak Chhatbar Jan 10 '15 at 17:49
@user3249477 Just try to use other logic) if (newVersion == 2) { // doSomething1} else if (newVerion == 3) { // doSomething2} – Anatol Jan 10 '15 at 17:50
Right that would work, just update this `if (newVersion > oldVersion) {` in your answer. – Simas Jan 10 '15 at 17:52

score 1 · Answer 2 · edited May 23 '17 at 11:52

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

edited May 23 '17 at 11:52

Community

1
1

answered Jan 10 '15 at 17:38

display name

4,165
2
27
52

this worked.. thank you, accepted the other answer because he was first to mention this method... thank you :) – Hirak Chhatbar Jan 10 '15 at 17:51
He updated his answer to adopt my solution for ALTER instead of CREATE table. Not sure why you think he did first. but anyway, glad to help – display name Jan 10 '15 at 17:54
no bro.. he edited before u answered... see his edit history:) – Hirak Chhatbar Jan 10 '15 at 17:55
his was edited a minute ago in compare to 17 min ago?? lol. :) Let's stop this non sense. Again, glad that I have the correct solution – display name Jan 10 '15 at 17:56
@IsabelHM I am sorry. i didn't see your answer before posting alter example. later updates was to give more accuracy to code and explanations. – Anatol Jan 10 '15 at 17:59
@Anatol it's not your problem. no need to apologize. It's just a fact that I provided an accurate solution from the beginning and honestly as i said, glad that it's the solution. best wishes to you, Anatol. :) – display name Jan 10 '15 at 18:02

upgrade sqlite database in my app

2 Answers2