Why is onUpgrade() not being invoked on Android sqlite database?

Question

I want to upgrade my database when it is installed onto my android emulator. I have set the db version in my DbHelper which inherits from SQLiteOpenHelper to +1.

However, when my 1st activity loads, I instantiate my DbHelper, which I would expect SQLiteOpenHelper to call onUpgrade as the db version is now newer. However it is never called. I'm wondering if there is something I am missing. Where is the version that the DbHelper is using stored to compare against the new version? Why is this not working?

I am actually copying the database from the assets folder into the data folder rather than re-creating the schema.

public class DbHelper extends SQLiteOpenHelper {
    private static final String TAG = "DbHelper";

    static final String DB_NAME = "caddata.sqlite";
    static final int DB_VERSION = 4;

    private static String DB_PATH = "";
    private Context myContext;
    private SQLiteDatabase myDataBase;

    public DbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.myContext = context;

        DB_PATH = "/data/data/"
                + context.getApplicationContext().getPackageName()
                + "/databases/";            
    }

    public DbHelper open() throws SQLException {        
        myDataBase =  getWritableDatabase();

        Log.d(TAG, "DbHelper Opening Version: " +  this.myDataBase.getVersion());
        return this;
    }

    @Override
    public synchronized void close() {

        if (myDataBase != null)
            myDataBase.close();

        super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d(TAG, "onCreate called");

        try {           
            createDataBase();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if ( newVersion > oldVersion)
        {
            Log.d(TAG, "New database version exists for upgrade.");         
            try {
                Log.d(TAG, "Copying database...");
                copyDataBase();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }       
        }
    }

    public void createDataBase() throws IOException {

        boolean dbExist = checkDataBase();

        if (!dbExist) {         

            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }

        openDataBaseForRead();
    }


    private boolean checkDataBase() {

        SQLiteDatabase checkDB = null;

        try {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READONLY
                            | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
            Log.d(TAG, "db exists");
        } catch (SQLiteException e) {
            // database does't exist yet.
            Log.d(TAG, "db doesn't exist");

        }

        if (checkDB != null) {
            checkDB.close();            
        }

        return checkDB != null ? true : false;
    }


    private void copyDataBase() throws IOException {

        // Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;

        // Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);

        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[2048];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();

        myDataBase.setVersion(DB_VERSION);
    }

    public void openDataBaseForRead() throws SQLException {

        // Open the database
        String myPath = DB_PATH + DB_NAME;      
        myDataBase = SQLiteDatabase.openDatabase(myPath, null,  SQLiteDatabase.OPEN_READONLY);
    }

    public void openDataBaseForWrite() throws SQLException {

        // Open the database
        String myPath = DB_PATH + DB_NAME;      
        myDataBase = SQLiteDatabase.openDatabase(myPath, null,  SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.NO_LOCALIZED_COLLATORS );
    }


}

That would be great if you shown code for incrementing database version. — pawelzieba, Oct 04 '11 at 12:17
Incrementing database version? I thought android handled this for you. — jaffa, Oct 05 '11 at 12:27
@jaffa in sqlite there is a PRAGMA user_version for every database. android compares this to the value you supply to the constructor of SQLiteOpenHelper. if it finds your version > user_version it calls onUpgrade() and sets the PRAGMA user_version to the new value, else it calls onCreate(). so all you have to do is increment the version you supply to SQLiteOpenHelper constructor and implement the onUpgrade() method to modify the database to the required new state. — kdehairy, Aug 18 '12 at 02:02

kdehairy · Answer 1 · 2015-09-22T09:07:16.663

31

This is a code snippet from the source of SQLiteOpenHelper.getWritableDatabase():

int version = db.getVersion();
if (version != mNewVersion) {
    db.beginTransaction();
    try {
        if (version == 0) {
            onCreate(db);
        } else {
            if (version > mNewVersion) {
                onDowngrade(db, version, mNewVersion);
            } else {
                onUpgrade(db, version, mNewVersion);
            }
        }
        db.setVersion(mNewVersion);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

onOpen(db);

As you can see, the onCreate() or onUpgrade() are called within the call to getWritableDatabase(). You have to use these calls whenever you need an instance of SQLiteDatabase. You shouldn't use your own methods, except if they are wrappers arround the getWritableDatabase or getReadableDatabase method.

edited Sep 22 '15 at 09:07

answered Jul 25 '12 at 08:53

kdehairy

2,630
22
27

Does that means `onUpgrade()` is called every time when user invokes `getWritableDatabase()`? – Malwinder Singh Jun 08 '15 at 10:37
yes IF it finds that the version it has is greater than the current/previous version. – kdehairy Jun 08 '15 at 13:30
Timesaver! When I had to trigger the database update, simply getting a WRITEABLEDATABASE with the above method works. – Yoraco Gonzales Jul 25 '15 at 23:17
The version I'm looking at right now calls this code irrespective of the "writable" parameter. So, the onCreate, onUpgrade, and onDowngrade methods get executed whenever you retrieve a database instance, depending on the current database version. To trigger this, you do not have to call getWritableDatabase() but can also call getReadableDatabase(). – Alen Siljak Sep 18 '15 at 16:30
@AlenSiljak is right on that. I just wanted to give a simple non-confusing answer. I'll update the answer. – kdehairy Sep 22 '15 at 09:06

score 14 · Answer 2 · answered Oct 12 '11 at 13:21

14

Documentation says:

The database is not actually created or opened until one of getWritableDatabase() or getReadableDatabase() is called.

I see that you've implemented your own methods for getting database: openDataBaseForRead() and openDataBaseForWrite(). That's bad ;-)

answered Oct 12 '11 at 13:21

pawelzieba

16,082
3
46
72

Why is it bad? I think I used them for testing purposes. – jaffa Oct 12 '11 at 13:27
This was great. My app used to onUpgrade when I started it, but apparently my launcher no longer has any db calls so I was tearing my hair out trying to figure out why it wasn't upgrading - had to navigate to an activity that _did_ call the db! – rythos42 Jun 17 '13 at 23:11

score 5 · Answer 3 · answered Nov 19 '15 at 10:55

I have figured out the problem. This problem comes only when you copy the db from assets. The db in the assets folder has a default version 0. So when you call the database open helper with the version number(say 1) and then overwrite it with the one in assets the version number is reset to 0.

And when you call the db.getReadableDatabase() or db.getWriteableDatabase which is supposed to call the onUpgrade method, it fails because the version number 0 which is supposed to be a fresh database. you can look the source code of SqliteOpenHelper. It triggers the the onUpgrade method only when the version is greater than zero and the current version is greater than the old one.

`

db.beginTransaction();
try {
  //Skips updating in your case
  if (version == 0) {
  onCreate(db);
  } else {
  if (version > mNewVersion) {
    onDowngrade(db, version, mNewVersion);
  } else {
   onUpgrade(db, version, mNewVersion);
  }
}
db.setVersion(mNewVersion);
db.setTransactionSuccessful();
} finally {
   db.endTransaction();
}

`

So this is what I did to overcome this issue. Its no where a perfect solution, but it works. Basically you need to update the db version before you call the super method of SQLiteOpenHelper class (your Database manager's constructor).

`

try
 {
   String myPath = MyApplication.context.getDatabasePath(DATABASE_NAME).toString();
   //open a database directly without Sqliteopenhelper 
   SQLiteDatabase myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
   //if the version is default 0 the update the version
   if (myDataBase.getVersion() == 0)
    {
     //update the database version to the previous one
     myDataBase.execSQL("PRAGMA user_version = " + 1);
    }
    //Close DataBase 
    myDataBase.close();
  }
   catch (Exception e)
   {
     //Do Nothing a fresh install happened
   }

`

P.S: I know that the answer is late but, it might help for someone like me who was searching for this particular solution

Clever idea. I am in same situation as this and this help me big time. Cheers — Greg Ellis, Apr 27 '16 at 00:51
you could archive this easier by calling onUpgrad from OnCreate and handle every thing in onUpgrade also you could call the pragma in OnConfigure as per documentation This method should only call methods that configure the parameters of ... or executing PRAGMA statements — Mohammad Yahia, Apr 04 '17 at 18:36

score 0 · Answer 4 · answered Nov 16 '18 at 12:07

0

In my case the Database version in asset folder and current Database version in code, Both are same, Later I increased Current Database version in code, Then the onUpgrade method is called.

answered Nov 16 '18 at 12:07

creativecoder

1,470
1
14
23

Why is onUpgrade() not being invoked on Android sqlite database?

4 Answers4

Linked