Alex Kapustian Alex Kapustian - 3 months ago 15
Android Question

what is the correct way to upgrade sqllite db?

private static final String TABLE_MAIN_CREATE = "CREATE TABLE IF NOT EXISTS " + TABLE_MAIN_NAME + " ( a INTEGER, b LONG, c TEXT, d TEXT, e DATETIME, f TEXT)";
private static final String TABLE_MAIN_UPGRADE = "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column f TEXT";

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(TABLE_MAIN_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion < newVersion) {
db.execSQL(TABLE_MAIN_UPGRADE);
}
onCreate(db);
}


The previous db version was without the "f" field.
Is this the correct way to upgrade the DB?
Do I need onCreate(db) in onUpgrade?

I did this several times and each time I got another exception:
1) table already exists so I added "IF NOT EXISTS"
2) could not read row 0, col 5 error here I lost it....

IS this the correct way to upgrade the db?

I want to keep my data

Answer

Caution: This is a good and working solution but there is a lot inmature people which downvote this answer in revenge(or because they have something personal) or because they don't know java (case from 10 to 12 are without break;)

You have to do something depends on oldVersion ...

fx: you can have db versions from 10-14, the older then 10 you dont wana support and at every new version you add a new column the code should looks like(newVersion is 14) :

private static final String TABLE_MAIN_CREATE_14 = 
    "CREATE TABLE IF NOT EXISTS " + TABLE_MAIN_NAME + 
    " ( a INTEGER, b LONG, c TEXT, d TEXT, e DATETIME, f TEXT, g TEXT, h TEXT, i TEXT)";
private static final String TABLE_MAIN_UPGRADE_10_11 = 
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column f TEXT";
private static final String TABLE_MAIN_UPGRADE_11_12 = 
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column g TEXT";
private static final String TABLE_MAIN_UPGRADE_12_13 = 
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column h TEXT";
private static final String TABLE_MAIN_UPGRADE_13_14 = 
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column i TEXT";
    @Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_MAIN_CREATE_14);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion){
            case 10: // from 10 to 11 and go to next case
                db.execSQL(TABLE_MAIN_UPGRADE_10_11);
            case 11: // from 11 to 12 and go to next case
                db.execSQL(TABLE_MAIN_UPGRADE_11_12);
            case 12: // from 12 to 13 and go to next case
                db.execSQL(TABLE_MAIN_UPGRADE_12_13);
            case 13: // from 13 to newVersion
                db.execSQL(TABLE_MAIN_UPGRADE_13_14);
                break;
            default:
                //not upgratable too old - so we should drop and recreate;
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_MAIN_NAME  );
                onCreate(db);
                break;
    }
}