Android update - merge database contents?

Question

What if I update the database tables my app is using, eg if I introduce another class or change an existing. Now when a user updates the app, how can I merge any existing database content of my app to the new db definitions?

There is no automatic way to do that... ergo you will have to upgrade it manually by creating temporary tables, moving data there, drop tables, recreate them, etc. all that using SQL sentences. — Cristian, Jun 05 '12 at 14:13

score 3 · Accepted Answer · answered Jun 05 '12 at 14:30

There is an abstract method for this called onUpgradeDatabase(). This function is called when Android thinks your database needs to be updated. This mostly occurs when you change your database version number.

In all my recent projects I'm using the following approach to upgrade the database on the user's phone version by version (even from e.g: version1 to version5 if user didnt update the application for a while):

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    updateDatabase(db, oldVersion + 1, newVersion);
}

private void updateDatabase(SQLiteDatabase db, int fromVersion, int toVersion) {
    Log.i("Updating database from version " + fromVersion + " to " + toVersion);

    for (int ver = fromVersion; ver <= toVersion; ver++) {
        switch (ver) {
            case 1:
                handleDatabaseVersion1(db);
                break;
            case 2:
                handleDatabaseVersion2(db);
                break;                  
        }
    }
}

private void handleDatabaseVersion1(SQLiteDatabase db) {
   // create initial tables and so on
}

private void handleDatabaseVersion2(SQLiteDatabase db) {
   // here we extend the Users table with another two columns
}

If you modify any schema including new tables, new columns, modified column types and whatsoever you must execute the proper SQL statements to update everything without deleting all the user's data! Let's say you are writing a notepad application and all the notes are stored in a database. If you modify the Notes table you must not delete the user's notes, so handle those data with care.

score 1 · Answer 2 · edited Jun 05 '12 at 14:18

1

You will have to use onUpgradeDatabase

In this method you can alter tables.

edited Jun 05 '12 at 14:18

Cristian

198,401
62
356
264

answered Jun 05 '12 at 14:17

SantiM

192
8

Just to add to this correct answer, try searching onUpgradeDatabase, there are already a few threads on this with more examples. http://stackoverflow.com/questions/7173896/onupgrade-database-oldversion-newversion – ian.shaun.thomas Jun 05 '12 at 14:22

Android update - merge database contents?

2 Answers2

Linked