CODEWITHSUNDEEP
androidsqlite
Kaloyan Roussev
Kaloyan Roussev

Reputation: 14711

SQLite onUpgrade with 4 database versions

How is the proper way to execute an SQLiteOpenHelper's onUpgrade method, when we have 4 database versions and we have added a new field to the user table in each version?

Variant A: // No "break" after each case, does it keep running for case 2 and 3?

public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
    switch (version_old) {
    case 1:
        database.execSQL(addPostcodeFieldToUserTable);
    case 2:
        database.execSQL(addGenderFieldToUserTable);
    case 3:
        database.execSQL(addEmailSubscriptionFieldToUserTable);
        break;
    }
}

Variant B:

public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
    switch (version_old) {
    case 1:
        database.execSQL(addPostcodeFieldToUserTable);
        break;
    case 2:
        database.execSQL(addPostcodeFieldToUserTable);
        database.execSQL(addGenderFieldToUserTable);
        break;
    case 3:
        database.execSQL(addPostcodeFieldToUserTable);
        database.execSQL(addGenderFieldToUserTable);
        database.execSQL(addEmailSubscriptionFieldToUserTable);
        break;
    }

But what do we do in the case when a user had version 1 of the DB, then missed version 2 and upgraded the app with version 3?

Variant 3:

public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
    if(version_old==1 && current_version==2) {
        database.execSQL(addPostcodeFieldToUserTable);

    } else if(version_old==2 && current_version==3) {
        database.execSQL(addGenderFieldToUserTable);

    } else if(version_old==3 && current_version==4) {
        database.execSQL(addEmailSubscriptionFieldToUserTable);

    } else if(version_old==1 && current_version==3) {
        database.execSQL(addPostcodeFieldToUserTable);
        database.execSQL(addGenderFieldToUserTable);

    } else if(version_old==1 && current_version==4) {
        database.execSQL(addPostcodeFieldToUserTable);
        database.execSQL(addGenderFieldToUserTable);
        database.execSQL(addEmailSubscriptionFieldToUserTable);

    } else if(version_old==2 && current_version==4) {
        database.execSQL(addGenderFieldToUserTable);
        database.execSQL(addEmailSubscriptionFieldToUserTable);

    }
}

Upvotes: 1

Views: 95

Answers (3)

CL.
CL.

Reputation: 180070

Using a switch without breaks is counterintuitive.

A common pattern is to use a series of ifs instead:

if (version_old < 2) {
    database.execSQL(addPostcodeFieldToUserTable);
}
if (version_old < 3) {
    database.execSQL(addGenderFieldToUserTable);
}
if (version_old < 4) {
    database.execSQL(addEmailSubscriptionFieldToUserTable);
}

Upvotes: 1

Ajay
Ajay

Reputation: 1836

What you could also do is make the onUpgrade a recursive call, 

public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
switch (version_old) {
case 1:
    database.execSQL(addPostcodeFieldToUserTable);
    onUpgrade(database,version_old++,current_version);
    break;
case 2:
    database.execSQL(addPostcodeFieldToUserTable);
    database.execSQL(addGenderFieldToUserTable);
    onUpgrade(database,version_old++,current_version)
    break;
default:
    break;
}

u get the idea, so it will upgrade to the latest version and quit calling itself.

Upvotes: 3

laalto
laalto

Reputation: 152817

No "break" after each case, does it keep running for case 2 and 3?

Yes. A common practice is to add a comment like

// fallthrough

to indicate the missing break is intentional.

But what do we do in the case when a user had version 1 of the DB, then missed version 2 and upgraded the app with version 3?

onUprade() would be called with oldVersion 1 and newVersion 3. The code should update the database to version 3 of your schema.

Which variant to use depends on which is the most comfortable for you to maintain. I'd personally go with something like variant A since it has the least code.

Upvotes: 2

Related Questions