How to Upgrade SQLite in Android without losing data!

   While writing a seemingly easy app that uses a database to keep track of contact info, I ran into a problem when adding columns to my SQLite table. The app would not keep the old data and just use a "null" for the new columns. I knew I would have to write an onUpgrade() Method that consisted of more than:


 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }
    But where to start?


 For my app this was a must. Frequent updates would need this method to work while keeping the users data, but all the examples I found of Android and SQLite just had the drop table method or something basically similar. Now I have read of a few different ways to tackle this problem. like exporting the data to XML and creating a new table then filling it in or building a temporary table to hold the data while I made the new one. I opted for the second option.

 This I thought would be a no-brainier. I would just Google it and "Pow!" tons of people have working examples of this.

    I was Wrong. I did find a couple of examples but they were either way too complex or just not workable in the Adapter class I had built for my database code. I couldn't find the answer on the Android developer guide site either. I found my answer instead at the SQLite documentation site.

This site gave me the crash course I needed on the types of calls and queries I could use on the SQL end, I just had to adapt it to fit my app.

I'm going to write a short mock-up of basically how I did it, Including the Adapter class I wrapped around the SQLiteOpenHelper:

Although my table had a considerable amount of columns to start with lets following the KISS rule and say The old table had only three columns named "_id, column_1, and column_2". My upgrade method will add a fourth column, "column_3" and save any user data for the new table:

//package here
//imports here

//Adapter class that wraps around the Helper class

public class FooDbAdapter
 {
private static final String DATABASE_NAME = "fooHelperDb";
private static final String DATABASE_TABLE = "contacts";
private static final int SCHEMA_VERSION = 2; //was "1",  make sure you change this by incrementing it by one or it will keep the old table. This makes the call to onUpgrade().         

         public static final String KEY_ID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_TYPE = "fooType";

   private FooDbHelper fDbHelper;
   private SQLiteDatabase fDb;
   private final Context mCtx;

 //Helper class uses SQLiteOpenHelper that does the actual database work
public static class FooDbHelper extends SQLiteOpenHelper
 {


FooDbHelper(Context context) {
    super(context, DATABASE_NAME, null, SCHEMA_VERSION);
}

//create table as you want it to be after onUpgrade() runs. ie: 4 columns.
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT, column_1 TEXT, column_2 TEXT, column_3 TEXT);" );
}

//this is the method I designed to solve the problem. I'm not sure if it's redundant in parts but it works great.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 //create temp. table to hold data
             db.execSQL("CREATE TEMPORARY TABLE contacts_backup (_id INTEGER PRIMARY KEY AUTOINCREMENT, column_1 TEXT, column_2 TEXT);");
//insert data from old table into temp table

db.execSQL("INSERT INTO contacts_backup SELECT _id, column_1, column_2 FROM contacts ");
//drop the old table now that your data is safe in the temporary one
db.execSQL("DROP TABLE contacts");
//recreate the table this time with all 4 columns
db.execSQL("CREATE TABLE contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT, column_1 TEXT, column_2 TEXT, column_3 TEXT);");
//fill it up using null for the column_3
db.execSQL("INSERT INTO contacts SELECT _id, column_1, column_2, null FROM contacts_backup");
//then drop the temporary table
db.execSQL("DROP TABLE contacts_backup");


   }



// other methods here such as:
 public Cursor getAll() {
    return(getWritableDatabase().rawQuery("SELECT _id, column_1, column_2, column_3 FROM contacts ORDER BY name", null));
}

 public void insert(String column_1, String column_2, String column_3);  {
    ContentValues cv = new ContentValues();
      cv.put("column_1", column_1);
cv.put("column_2", column_2);
cv.put("column_3", column_3);
  getWritableDatabase().insert("contacts", "column_1", cv);
}

//getter methods

public String get_Id(Cursor c)  {
return(c.getString(0));
}

public String getColumn1(Cursor c) {
return(c.getString(1));
}
public String getColumn2(Cursor c) {
return(c.getString(2));
}
public String getColumn3(Cursor c) {
return(c.getString(3));
}

}
//that's it for the wrapper then I just wrote a context and open method

 public FooDbAdapter(Context ctx) {
       this.mCtx = ctx;
   }

   public FooDbAdapter open() throws SQLException {
        fDbHelper = new FooDbHelper(mCtx);
        fDb = fDbHelper.getWritableDatabase();
       return this;
   }

   public void close() {
        fDbHelper.close();
   }

   So there it is. pretty simple eh? Just gave me a hard time. don't forget to update any references  in the rest of your classes and stuff of course.



    A few things I hope this post will achieve:
      First of course is to help someone solve this problem.
      Second is that I hope some more seasoned folks may share some other ways to attack this problem. That is how good design patterns are made and people like myself and others new to Android learn.
     Third, is that instead of getting trolled  people understand the purpose of this and keep this site a positive learning environment.

Go Android!

-Quest Graves