Upgrade SQLite database from one version to another?

Question

I am getting an error from Logcat saying that a certain column (in my SQLiteOpenHelper subclass) does not exist. I thought I could upgrade the database by changing the DATABASE_CREATE string. But apparently not, so how can I (step-by-step) upgrade my SQLite Database from version 1 to version 2?

I apologize if the question seems "noobish", but I am still learning about Android.

@Pentium10 This is what I do in onUpgrade:

private static final int DATABASE_VERSION = 1;

....

switch (upgradeVersion) {
case 1:
    db.execSQL("ALTER TABLE task ADD body TEXT");
    upgradeVersion = 2;
    break;
}

...

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

.

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

Wouldn't something like the following be easier for the vast majority of cases? Just add the new column for each version upgrade:

private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";

private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL(DATABASE_ALTER_TEAM_1);
    }
    if (oldVersion < 3) {
         db.execSQL(DATABASE_ALTER_TEAM_2);
    }
}

For a bit more on this, check out this blog.

Answer 3

Here is how I upgrade my database.

In a previous version of my app, the gameType column doesn't exist. In the new version, it does.

  void upgradeDatabase() throws IOException {
    try {
      String column = DatabaseConstants.GAME_TYPE_COLUMN_NAME; // gameType
      String table = DatabaseConstants.RECORDS_TABLE;
      String query = String.format("SELECT %s FROM %s LIMIT 1", column, table);
      database.rawQuery(query, null);
      return;
    }
    catch (Exception e) {
      // Column doesn't exist. User had old version of app installed, so upgrade database.
    }

    // Save all old data
    String query = "SELECT * FROM " + DatabaseConstants.USERS_TABLE;
    Cursor c = database.rawQuery(query, null);
    List<List<Object>> values1 = new ArrayList<List<Object>>();
    if (c.moveToFirst()) {
      while (!c.isAfterLast()) {
        List<Object> record = new ArrayList<Object>();
        record.add(c.getInt(0));
        record.add(c.getString(1));
        values1.add(record);
        c.moveToNext();
      }
    }
    c.close();

    query = "SELECT * FROM " + DatabaseConstants.RECORDS_TABLE;
    c = database.rawQuery(query, null);
    List<List<Object>> values2 = new ArrayList<List<Object>>();
    if (c.moveToFirst()) {
      while (!c.isAfterLast()) {
        List<Object> record = new ArrayList<Object>();
        record.add(c.getInt(0));
        record.add(c.getInt(1));
        record.add(c.getInt(2));
        record.add(c.getInt(3));
        values2.add(record);
        c.moveToNext();
      }
    }
    c.close();

    // Copy empty database with new schema
    copyDatabase();

    // Restore all old data
    for (List<Object> record : values1) {
      ContentValues cv = new ContentValues();
      cv.put(DatabaseConstants.ID_COLUMN_NAME, (Integer) record.get(0));
      cv.put(DatabaseConstants.USERNAME_COLUMN_NAME, record.get(1).toString());
      database.insert(DatabaseConstants.USERS_TABLE, null, cv);
    }
    for (List<Object> record : values2) {
      ContentValues cv = new ContentValues();
      cv.put(DatabaseConstants.USER_ID_COLUMN_NAME, (Integer) record.get(0));
      cv.put(DatabaseConstants.GAME_TYPE_COLUMN_NAME, GameType.CLASSIC.name());
      cv.put(DatabaseConstants.WINS_COLUMN_NAME, (Integer) record.get(1));
      cv.put(DatabaseConstants.LOSSES_COLUMN_NAME, (Integer) record.get(2));
      cv.put(DatabaseConstants.TIES_COLUMN_NAME, (Integer) record.get(3));
      database.insert(DatabaseConstants.RECORDS_TABLE, null, cv);
    }
  }

Here's the code to copy the database file. The database is initially empty, and I created it outside my app. (I used a program called Navicat for SQLite.)

  public DatabaseHelper(Context context) {
    super(context, DatabaseConstants.DATABASE_NAME, null, 1);
    this.context = context;
    databasePath = context.getDatabasePath(DatabaseConstants.DATABASE_NAME).getPath();
  }

  void copyDatabase() throws IOException {
    InputStream is = context.getAssets().open(DatabaseConstants.DATABASE_NAME); // data.db
    OutputStream os = new FileOutputStream(databasePath);

    byte[] buffer = new byte[1024];
    int length;
    while ((length = is.read(buffer)) > 0) {
      os.write(buffer, 0, length);
    }

    // Close the streams.
    os.flush();
    os.close();
    is.close();
  }