Sqlite database onUpgrade() does not get called

Question

I am working on an android application where i used existing sqlite database in the asset folder. So i actually copy the database from that folder. User can also save their own data(mark any content as favorite).

I uploaded a version in the market. Now i want to add some new data in the database and uploaded a new version. If user update the app from the market i want to keep the user data(from previous version) and add the new data as well. I have done some google and found that on upgrade method should do the trick. But I am changing the DATABASE_VERSION from code but the on upgrade method does not get call. I am wondering i have missed something. Here is my code:

public class DataBaseHelper extends SQLiteOpenHelper {

private static String DB_PATH = "/data/data/riskycoder.login/databases/";
public static String DB_NAME = "datenbank_EN.sqlite";
private SQLiteDatabase myDataBase;
private final Context myContext;
private static final int DATABASE_VERSION = 1;





public DataBaseHelper(Context context) {
    super(context, DB_NAME, null, DATABASE_VERSION);
    this.myContext = context;
}

public void createDataBase() throws IOException {
    boolean dbExist = checkDataBase();
    if (dbExist) {
    } else {
        this.getWritableDatabase();
        try {
            this.close();
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copying database");
        }
    }

}

private boolean checkDataBase() {
    SQLiteDatabase checkDB = null;
    try {
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READWRITE);
    } catch (SQLiteException e) {
    }
    if (checkDB != null)
        checkDB.close();
    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException {

    InputStream myInput = myContext.getAssets().open(DB_NAME);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream myOutput = new FileOutputStream(outFileName);
    byte[] buffer = new byte[2048];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);
    }
    myOutput.flush();
    myOutput.close();
    myInput.close();

    //myDataBase.setVersion(DATABASE_VERSION);
}

public void openDataBase() throws SQLException {
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READWRITE);
    Log.d("Test", "Database version: " +myDataBase.getVersion());
}

@Override
public synchronized void close() {
    if (myDataBase != null)
        myDataBase.close();
    super.close();
}



@Override
public void onCreate(SQLiteDatabase db) {
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.d("Test", "in onUpgrade. Old is: " + oldVersion + " New is: " + newVersion);


}

}

Answer 1

onUpgrade() is only called if a change in the database version number is detected. Increment the database version as follows:

private static final int DATABASE_VERSION = 2;

Answer 2

Change your onUpgrade to the following

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

            if(newVersion > oldVersion){                    
                    myContext.deleteDatabase(DB_NAME);
            }
        }

And also change your createDataBase() like below,

public void createDataBase() throws IOException{

            boolean dbExist = checkDataBase();

            if(dbExist){
                // By calling this method here onUpgrade will be called on a
                // writable database, but only if the version number has been increased

                this.getWritableDatabase();
            }

            dbExist = checkDataBase();

            if(!dbExist){

                //By calling this method an empty database will be created into the                     default system path
                   //of the application so we will be able to overwrite that database with our database.
                this.getReadableDatabase();

                try {

                    copyDataBase();

                } catch (IOException e) {

                    throw new Error("Error copying database");

                }
            }

        }

At the end increase your database version and run it again. onUpgrade doesn't get called till you call getReadableDatabase() or similar functions.

Hope this helps

Answer 3

Try this (increase the DATABASE_VERSION) & the onUpdate() will be called:

private static final int DATABASE_VERSION = 2; 

Answer 4

Assuming you did change the DATABASE_VERSION, you still have to put code in the onUpgrade to make it happen. It's not magical, you have to tell it what to do.

In your case you need to:

1) copy the old database (give it a new name)
2) copy in your new database
3) read the data from the old database
4) copy any differences into the new database
5) delete the old database

EDIT

Assuming you ship the DB in the assets folder you would copy it for use like so:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // First copy old db
    InputStream bakInput = getActivity().getAssets().open(dbname);
    String bakFileName = getActivity().getAssets() + "YourDB.old";
    OutputStream bakOutput = new FileOutputStream(bakFileName);
    byte[] buffer = new byte[1024];
    int length;
    while ((length = bakInput.read(buffer)) > 0) {
        bakOutput.write(buffer, 0, length);
    }
    myOutput.flush();
    myOutput.close();
    myInput.close();

    // delete the old db
    File delDB = new File(getActivity().getAssets() + "YourDB"); 
    boolean deleted = file.delete(); 

    // Copy in the new db
    InputStream myInput = getActivity().getAssets().open("YourDB");
    String outFileName = MAIN_DB_PATH + dbname;
    OutputStream myOutput = new FileOutputStream(outFileName);
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);
    }
    myOutput.flush();
    myOutput.close();
    myInput.close();

    // add code here to get changes user has made to db and move them to updated db

    // delete the old db copy
    File delDB = new File(getActivity().getAssets() + "YourDB.old"); 
    boolean deleted = file.delete(); 
}