Android update SQLite DB schema?

Question

What is the best practice for updating the DB schema? I could send a text file with the SQL commands. The app could check the text file and execute the commands needed. I am guessing then I will have a flag to indicate that the update has been done. I havent found a way to delete a file in the asset folder from the app, which would be the best thing to do after the DB was updated.

Any thoughts on this?

cagreen · Accepted Answer · 2011-02-18 00:36:30Z

13

You will probably want to override

onUpdate(SQLiteDatabase db,int old Version,int newVerison)

The following tutorial can walk you through the process: http://www.codeproject.com/KB/android/AndroidSQLite.aspx

answered Feb 18, 2011 at 0:36

cagreen

1,6251 gold badge14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Worsnop Over a year ago

this looks good but what happens if a user doesnt update and skips a few versions. So supposing they have version 1. All is good. I use the onUpdate() on the version 2. All is good here and I can put in the SQL commands in the code. Then version 3 comes along, I wipe out the last updates and put in new ones, I assume. This is all good except what happens to the person that never got version 2?

CommonsWare Over a year ago

@Mark Worsnop: Your onUpdate() has to handle all scenarios. So, if you have schema revisions 1, 2, and 3, and you need to go from 1 to 3, one solution is to apply the 1->2 change, then the 2->3 change.

Kushal Over a year ago

As @CommonsWare sir mentioned, in onUpdate() method we should consider all conditions i.e. 1 -> 2 , 1 -> 3 , 2 -> 3 because we should not assume that all users have latest version

CommonsWare · Accepted Answer · 2011-02-18 00:34:49Z

4

What is the best practice for updating the DB schema?

Use SQLiteOpenHelper. The only time you will be updating your schema is when you update the application. Whether you have the SQL commands in a file that you read in or just in your Java code is up to you.

I am guessing then I will have a flag to indicate that the update has been done.

That is part of what SQLiteOpenHelper gives you.

I havent found a way to delete a file in the asset folder from the app, which would be the best thing to do after the DB was updated.

That is not possible, sorry.

answered Feb 18, 2011 at 0:34

CommonsWare

1.0m195 gold badges2.4k silver badges2.7k bronze badges

Comments

tichy · Accepted Answer · 2011-02-18 06:46:38Z

1

As mentioned from others the SQLiteOpenHelper is the thing you are starting with.

Depending on your environment and requirements, Liquibase may be worth a look. It allows writing your update statements in a more structural way and is easier to use than plain SQL operations. I haven't used it for android, though.

answered Feb 18, 2011 at 6:46

tichy

5014 silver badges10 bronze badges

Comments

vishnuc156 · Accepted Answer · 2019-03-12 09:38:26Z

0

If you want to update the db schema use the following code in your SQLiteOpenHelper extended class. This will update the table structure and never lose your saved data from db,

 public void onUpgrade(SQLiteDatabase db, int oldVersion, int 
    newVersion) {
      if (oldVersion < newVersion) 
      {
        db.execSQL("ALTER TABLE " + "TABLE_NAME" + " TO " + 
        "NEW_TABLE_NAME");

        db.execSQL("ALTER TABLE " + "TABLE_NAME" + " ADD COLUMN " + 
        "FIELD_NAME" + " INTEGER;");
      }   
    }

SQLite doesn't support removing or modifying columns.

edited Mar 12, 2019 at 9:38

answered Mar 12, 2019 at 9:27

vishnuc156

97013 silver badges10 bronze badges

Collectives™ on Stack Overflow

Android update SQLite DB schema?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related