SQLiteOpenHelper onUpgrade() Confusion Android

by

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();
}

Leave a Comment