7

I have an app that has an sqlite with 3 tables. My concern is that, if I introduce an update that adds another table (so that's 4 tables) then the updated version will wipe out the database.

How can I backup/restore db given that the backup happens before the update and the restore after the update? If I do it using the IO (copy to SD card and copy back) then it will fail.

I am thinking probably of exporting data to xml and loading manually. Is there another way? any example on how to do it?

Lokesh Mehra
  • 545
  • 6
  • 16
Snake
  • 14,228
  • 27
  • 117
  • 250

1 Answers1

4

If you simply want to add a new table, comment out the DROP TABLE commands in your onUpgrade() method. You have full control of the code in onUpgrade() so it can just be:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("CREATE TABLE ...");
}

Technically you don't even need to increment the database version, anytime you have access to a copy of db you can execute your CREATE statement.

Sam
  • 86,580
  • 20
  • 181
  • 179
  • oh damn! how did I not think of that! Thank you. But my question still holds, how do I back up. lets say the user backup the db, uninstall the app, download the new updated app and then decides he wants to load the backed up file. How can I achieve that? I dont think simple IO copy would work – Snake Nov 24 '12 at 02:55
  • 1
    A SQLite database is just a regular file, so you can just copy it to the SD card or even in the same folder as the existing database. [Here is an example](http://stackoverflow.com/q/2170031/1267661). – Sam Nov 24 '12 at 02:58
  • I think you missed my point. Lets say I have Version 1 of db which has 3 tables. I copied it to SD card. The user upgraded to version 2 of db which has 4 tables. Now if you copy the db file back from SD, then you forced version 1 although the code will be treating it as version 2. See the problem, I want a way to be able to export and import data to the same tables no matter if there is extra tables or not – Snake Nov 24 '12 at 06:14
  • 2
    Ok. The classic `onUpgrade()` method that drops tables is as primitive as it gets. (Wiping all of your data is not useful.) A smarter, SQL savvy approach uses [ALTER TABLE](http://www.sqlite.org/lang_altertable.html) to add columns or otherwise finagle the data into your new schema. [This question](http://stackoverflow.com/q/9109837/1267661) demonstrates one author's multi-version upgrades. – Sam Nov 24 '12 at 07:19