49

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

Mike
  • 1,481
  • 6
  • 17
  • 22

4 Answers4

108

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();
}
Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Hi Pentium, excellent answer, I was wondering could you clarify how I "get the intersection with the new columns, this time columns taken from the upgraded table" though? – Donal Rafferty Sep 15 '10 at 09:13
  • 1
    it's there in the code it's the retainAll method on the list, so `oldColumns.retainAll(newColumns)` at that time `DBUtils.GetColumns(db, TableName)` will return new columns, as the new tables has just been created, and the `columns` variable holds the reference to the columns before renaming the table, so old columns. – Pentium10 Sep 15 '10 at 10:38
  • Thanks Pentuim, I got confused by the extra brackets you had around the code, I thought you were using an additonal variable or something outside the brackets, Excellent answer and thanks again. – Donal Rafferty Sep 15 '10 at 13:23
  • 1
    `StringUtils.join` is unnecessary. Android has a TextUtils class which has a join method, see [TextUtils.join](http://developer.android.com/reference/android/text/TextUtils.html). Otherwise a great answer. – JosephL Feb 13 '12 at 03:12
  • 1
    I read this answer many times. it gives feel so good at all time because of detail explanation. – S.J. Lim Nov 29 '13 at 09:05
  • @Pentium10 But where does db version come into play in all of this? – IgorGanapolsky Jan 20 '14 at 18:55
  • @IgorGanapolsky DB version fires the event to start this process for you. – Pentium10 Apr 30 '14 at 21:07
  • 3
    Hello, and thanks for your answer. Quick question: is it really necessary to call `beginTransaction()` and `setTransactionSuccessful()` inside of `onUpgrade()`? As the call to `onUpgrade()` in `SQLiteOpenHelper` is already surrounded by those two. – Gabriel Morin Apr 14 '16 at 11:57
38

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

shanraisshan
  • 3,521
  • 2
  • 21
  • 44
pjv
  • 10,658
  • 6
  • 43
  • 60
6

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);
}
lazyList
  • 541
  • 5
  • 5
1

I've been using the solution proposed by @Pentium10 for a long time but today i had a problem, after doing alter table, getColumns from the original table still returns the same columns (in the new version of the db the table suffer mayor structure changes, some columns added some others), really i don't know why select statement does not reflect the structure changes, more over before creating my table again, select statement still returns the columns! When the table is not re-created yet!

So i manage solving this issue updating getColumns method using pragma table_info, like this:

    /**
 * Get a list of column base_dictionary for the selected table
 *
 * @param db
 *  Database that contains the table
 * @param tableName
 *  Table name to be used
 * @return A List of column name
 */
public static List<String> getColumns(SQLiteDatabase db, String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("pragma table_info(" + tableName + ")", null);

        ar = new ArrayList<String>();
        if (c != null && c.moveToFirst()) {
            do {
                ar.add(c.getString(c.getColumnIndexOrThrow("name")));
            } while (c.moveToNext());
            c.close();
        }

    } catch (Exception e) {
        Log.v(tableName, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        if (c != null) c.close();
    }
    return ar;
}
JavierSP1209
  • 899
  • 8
  • 17