SQLiteOpenHelper onUpgrade() Confusion Android

Question

I am doing my first app with a database and I am having a little trouble understanding the onUpgrade function. My database has a table with an items and a favorite column so that the user can favorite an item. Most implementations I see simply drop the table and reconstruct it but I don't want to do this. I want to be able to add more items to the table.

When the app is upgraded through the android marketplace does the database know its version number? So could I increment the version number in the code and then export it to the marketplace and when the user boots up the upgraded version for the first time then onUpgrade will be called?

If this is the case my onUpgrade would simply pull from a file and add the database items in. Is this a standard way of doing things or is there a better way of handling this in Android. I am trying to stay as standard as possible.

Thanks

Answer 1

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

Answer 2

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

Answer 3

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