android - Difficulty in upgrading SQlite table

Question

I have an application running with a working table called ANIMAL. Upon first creating this table it consisted simply of _id and animal_name columns.

Now I am trying to expand on it, including a animal_biography column, however I am having a little difficulty.At first I thought I was just a case of upgrading my CREATE_TABLE statement to include the animal bio:

private static final String DATABASE_CREATE =       
                        "create table " + ANIMALS_TABLE +
                        " (_id integer primary key autoincrement, " + 
                        "animal_name text not null, " +
                        "biography text not null);"; 

however, looking at the logcat it tells me the column biography does not exist when trying to insert into it.

Now, I have tried to upgrade the database by using the onUpgrade() and including the code

db.execSQL("ALTER TABLE" + DATABASE_NAME);
db.execSQL(DATABASE_CREATE);

but this is not solving the problem either. Does anyone have any pointers on how to go about fixing this problem?

Answer 1

If you are using SQLiteOpenHelper it's easy to upgrade a table. You need to implement methods onCreate and onUpgrade and provide current version of your database in class constructor. When updating your table just increment database version number, specify new table creation query in onCreate method and put ALTER TABLE to onUpgrade method to update previous version of table. When Android detects database version mismatch, it will call onUpgrade method automatically. See the example:

public class OpenHelper extends SQLiteOpenHelper {

    private final static int    DB_VERSION = 2;

    public TracksDB(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        final String CREATE_TBL =
            "create table " + ANIMALS_TABLE +
            " (_id integer primary key autoincrement, " + 
            "animal_name text not null, " +
            "biography text not null);";
             db.execSQL(CREATE_TBL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            final String ALTER_TBL = 
                "ALTER TABLE " + ANIMALS_TABLE +
                " ADD COLUMN biography text not null;";
            db.execSQL(ALTER_TBL);
        }
    }
}

This method of upgrading is the correct way to modify tables without losing user data, especially if the app had been released to public already.