Would like a bit of clarification with SQLite database's onUpgrade method

Question

So I have an app with a database with one table. I am wanting to put out a big update that includes a new table in the database.

I successfully used the onUpgrade method to create the new table in the old database by doing "db.execSQL(SQL_CREATE_STRING" in the onUpgrade method.

My question might not be a great one... but it's a question I can't find the answer for.

What happens next time I upgrade the database? Before I added the table the database's version was 2, after the new table the database version is 3. What happens if later down the road I want to add yet another table? Do I leave the current "db.execSQL(SQL_CREATE_STRING" in the onUpgrade or would I have to first remove it?

The SQL_CREATE_STRING starts with "CREATE TABLE IF NOT EXISTS", so I'm assuming I could just leave it in the onUpgrade method, correct? Since if it sees the table already it already exists, it wont try to make another one.

I'm just trying to confirm this thinking.

Thanks!

You may find [a different approach](https://stackoverflow.com/questions/46339534/android-sqlite-detect-if-new-version-not-found/46342520#46342520) of interest. A bit long-winded but it makes adding tables/columns/indexes a breeze without using `onUpgrade`. Works for me. — MikeT, Oct 20 '17 at 00:32

score 3 · Accepted Answer · answered Oct 19 '17 at 23:40

The SQL_CREATE_STRING starts with "CREATE TABLE IF NOT EXISTS", so I'm assuming I could just leave it in the onUpgrade method, correct? Since if it sees the table already it already exists, it wont try to make another one.

That sounds right. However, when you start having a few more versions it might be hard to keep track of which updates are okay to run regardless and which aren't.

What I recommend doing is having each of the upgrade steps in their own methods, and executing all the ones that need to for the given upgrade call in sequence. So for example:

public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
    for(int i = oldVersion + 1; i < newVersion; i++) {
        switch(i) {
        case 2: upgradeFromv1Tov2(); break;
        case 3: upgradeFromv2Tov3(); break;
        ...
    }
}

That way if someone is updating from e.g. v1 to v3, the upgrade will run the v1->v2 then v2->v3 logic. Doing it this way means when you create each upgrade step you know exactly what state the database schema will be in (ie the version before), so you don't have to worry about interactions between all the upgrade steps at the same time.

Would like a bit of clarification with SQLite database's onUpgrade method

1 Answers1