Android : Change SQLite DB on upgrade

Question

I want to add a column to my existing database

However I'm imagining I'm in the scenario where people have already got a version of the code and database and I'm going to make the changes through a update on google play, therefore previous data cannot be lost

To create my database I used the following code;

protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_calculation);
  db = openOrCreateDatabase("EJuiceData", Context.MODE_PRIVATE, null);
  db.execSQL("create table if not exists Inventory(_id integer primary key autoincrement, NAME TEXT NOT NULL, AMOUNT TEXT, PRICE TEXT, AMOUNTLEFT TEXT, WEIGHT TEXT);");
....

I've looked around online and people are mentioning using the following command to update;

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (newVersion > oldVersion) {
    db.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
  }
}

However onUpgrade is commented out stating the it isn't being called, also no where in my code have I stated what version of the database it is or given a new version

Anyone know how I get around this issue?

Thanks

EDIT;

From looking at the answers I've decided to try do this the most efficient way and what's seen as the best practice

So I've created a new Java Class called MyDBHelper which has the following

package com.sjhdevelopment.shaunharrison.myejuiceapp;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHelper extends SQLiteOpenHelper {

  public static final String DATABASE_NAME = "EJuiceData.db";
  public static final int DATABASE_VERSION = 1;



  public MovieDatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);

  }

  public void onCreate(SQLiteDatabase database) {
    database.execSQL("create table if not exists Recipe");
    .....

  }

  @Override
  public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
    if (newVersion > oldVersion)
        database.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
    onCreate(database);
  }

}

So far I have an error on MovieDatabaseHelper stating 'invalid method declared; return type required

Answer 1

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion)
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_NAME + "'");
        onCreate(db);
    }
}

Answer 2

Are you using SQLiteOpenHelper ? If yes you must have a version you send to the super class from the constructor (see documentation)

Here is a code sample. The onUpgrade function will be called when the DATABASE_VERSION changes.

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "EJuiceData.db";

    public DatabaseHelper (Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL("create table if not exists Inventory(_id integer primary key autoincrement, NAME TEXT NOT NULL, AMOUNT TEXT, PRICE TEXT, AMOUNTLEFT TEXT, WEIGHT TEXT);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
            onCreate(database);
        }
    }
}

EDIT

now you can get access your database using

DatabaseHelper helper = new DatabaseHelper(getContext());
helper.getReadableDatabase().query(...);
//or
helper.getWritableDatabase().insert(...);

Answer 3

You can extend SQLiteOpenHelper like below code, and new your helper class with version param which db version you want to open.

If you open db with version 1, and next time you open db with version 2, the onUpgrade method will be called with param ordVersion = 1 and newVersion = 2, then you can do something to upgrade your db schema here.

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "mydata.db";
    public static final int VERSION = 1;    
    private static SQLiteDatabase database;

    public MyDBHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
    }

    public static SQLiteDatabase getDatabase(Context context) {
        if (database == null || !database.isOpen()) {
            database = new MyDBHelper(context, DATABASE_NAME, 
                    null, VERSION).getWritableDatabase();
        }

        return database;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

}