Android SQLiteDatabase update schema without loosing data

Question

I'm using SQLiteDatabase in my app. My SQLiteOpenHelper.onUpgrade(..) method looks like this:

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    //Save current data
    ArrayList<Course> tempCourses = getAllCourses();

    //Drop tables
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COURSES);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ASSIGNMENTS);

    //create new tables
    onCreate(db);

    //Add temp data back to the database
    for (Course course : tempCourses) {
        addCourse(course);
    }
}

I want to keep the old userdata when upgrading the database schema. However I get the following error when i increase the database version and start my app:

...
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.tak3r07.unihelper/com.tak3r07.CourseStatistics.MainActivity}: java.lang.IllegalStateException: getDatabase called recursively
...

This is because getAllCourses() will open the database again to get the data which calls the onUpgrade method (obvious loop). But how should i store my userdata then?

Regards, Tak3r07

what are you trying to do exactly? add a column? – tyczj Jun 02 '15 at 14:04 — tyczj, Jun 02 '15 at 14:04

score 2 · Answer 1 · edited May 23 '17 at 11:43

The ALTER TABLE command can be used to perform some schema changes on existing tables without losing data. If you do find that you need to create the table from scratch, you should follow the following pattern:

Begin transaction
Create the new table
Do a insert .. select statement
Drop the old tables
Commit transaction

If any of the steps fail, you still have the data, and you can pick up at any step to finish the upgrade if it fails for any reason. You do not want to load all the courses into your app's memory like you're doing and then drop the tables because if your app fails to insert the courses back into the db, you'll lose all your data. Do it all in SQL.

ALTER TABLE does exactly what I was looking for! I forgot to think about the "what if it fails" part. Good point! — Tak3r07, Jun 02 '15 at 19:45

Android SQLiteDatabase update schema without loosing data

1 Answers1