Upgrade SQLite database from one version to another?

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

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

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;";@Overridepublic 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.