SQLiteOpenHelper onUpgrade() Confusion Android

Ok, before you run into bigger problems you should know that SQLite is limited on the ALTER TABLE command, it allows add and rename only no remove/drop which is done with recreation of the table.

You should always have the new table creation query at hand, and use that for upgrade and transfer any existing data. Note: that the onUpgrade methods runs one for your sqlite helper object and you need to handle all the tables in it.

So what is recommended onUpgrade:

• beginTransaction
• run a table creation with if not exists (we are doing an upgrade, so the table might not exists yet, it will fail alter and drop)
• put in a list the existing columns List<String> columns = DBUtils.GetColumns(db, TableName);
• backup table (ALTER table " + TableName + " RENAME TO 'temp_" + TableName)
• create new table (the newest table creation schema)
• get the intersection with the new columns, this time columns taken from the upgraded table (columns.retainAll(DBUtils.GetColumns(db, TableName));)
• restore data (String cols = StringUtils.join(columns, ","); db.execSQL(String.format( "INSERT INTO %s (%s) SELECT %s from temp_%s", TableName, cols, cols, TableName)); )
• remove backup table (DROP table 'temp_" + TableName)
• setTransactionSuccessful

(This doesn't handle table downgrade, if you rename a column, you don't get the existing data transfered as the column names do not match).

.

public static List<String> GetColumns(SQLiteDatabase db, String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("select * from " + tableName + " limit 1", null);
        if (c != null) {
            ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
        }
    } catch (Exception e) {
        Log.v(tableName, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        if (c != null)
            c.close();
    }
    return ar;
}

public static String join(List<String> list, String delim) {
    StringBuilder buf = new StringBuilder();
    int num = list.size();
    for (int i = 0; i < num; i++) {
        if (i != 0)
            buf.append(delim);
        buf.append((String) list.get(i));
    }
    return buf.toString();
}

Next to Pentium10's excellent answer, here are some good examples from living code:

• Android AOSP: com.android.providers.calendar.CalendarDatabaseHelper.java

• Android AOSP: com.android.browser.BrowserProvider.java

• OpenIntents Notepad: org.openintents.notepad.NotePadProvider.java

Thank you for clarifying that onUpgrade() will not support Remove/Drop statements @Pentium 10

For those of you who would like to know the exact moment when onUpgrade() gets called, it is during a call to either getReadableDatabase() or getWriteableDatabase().

To those who are not clear how it ensure it gets triggered...the answer is: It is triggered when the database version provided to the constructor of SqLiteOpenHelper is updated. Here is a example

public class dbSchemaHelper extends SQLiteOpenHelper {

private String sql;
private final String D_TAG = "FundExpense";
//update this to get onUpgrade() method of sqliteopenhelper class called
static final int DB_VERSION = 2; 
static final String DB_NAME = "fundExpenseManager";

public dbSchemaHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    // TODO Auto-generated constructor stub
}

now to...onUpgrade()

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    sql = "ALTER TABLE " + fundExpenseSchema.Expense.TABLE_NAME + " ADD COLUMN " + fundExpenseSchema.Expense.FUNDID + " INTEGER";
    arg0.execSQL(sql);
}