onUpgrade is not getting called in SQLiteOpenHelper

Question

The method onUpgrade is not getting called even if it gets the DB version greater than the current.

I verified the DB version using the following command and it is 6 currently.

sqlite> PRAGMA user_version;
6

In the constructor, I'm passing the following (version 7),

super(context, DB_NAME, null, 7);

Note: My class DBHelper extends SQLiteOpenHelper and the above line is present in the constructor of DBHelper.

SQLite version: 3.32.3

As I'm passing the new (greater) DB version, I expect the onUpgrade to be called. But the onUpgrade is never called. Am I missing any other thing?

UPDATE 1:

DBHelper.class

class DBHelper extends SQLiteOpenHelper
{
    @Inject
    public DBHelper(final Context context)
    {
        this(context, DBContracts.DATABASE_FILE_NAME);
    }

    DBHelper(final Context context, final String databaseFileName)
    {
        super(context, databaseFileName, null, DBContracts.DATABASE_VERSION);
        Logger.i("APPTAG", "DBHelper constructor");
        Logger.i("APPTAG", "DATABASE_VERSION : " + DBContracts.DATABASE_VERSION);
    }

    @Override
    public void onCreate(final SQLiteDatabase db)
    {
        Logger.i("APPTAG", "onCreate");
        // create scripts
    }

    @Override
    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion)
    {
        Logger.i("APPTAG", "onUpgrade");
        Logger.i("APPTAG", "oldVersion: " + oldVersion);
        Logger.i("APPTAG", "newVersion: " + newVersion);
        // upgrade scripts
    }

    @Override
    public void onConfigure(final SQLiteDatabase db)
    {
        Logger.i("APPTAG", "onConfigure");
        Logger.i("APPTAG", "DB version: " + db.getVersion());

        try {
            Logger.i("APPTAG", "DB path: " + db.getPath());
            int version = ((Long) DatabaseUtils.longForQuery(db, "PRAGMA user_version;", null)).intValue();
            Logger.i("APPTAG", "onConfigure version: " + version);
        } catch (Exception e) {
            Logger.e("APPTAG", "Error" + e, e);
        }

        super.onConfigure(db);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Logger.i("APPTAG", "onDowngrade");
        Logger.i("APPTAG", "oldVersion: " + oldVersion);
        Logger.i("APPTAG", "newVersion: " + newVersion);
        super.onDowngrade(db, oldVersion, newVersion);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        Logger.i("APPTAG", "onOpen");
        Logger.i("APPTAG", "DB version: " + db.getVersion());
        super.onOpen(db);
    }
}

And this is the log I see,

05-04 07:47:33.021  4295  4295 I APPTAG: DBHelper constructor
05-04 07:47:33.021  4295  4295 I APPTAG: DATABASE_VERSION : 7
05-04 07:47:33.023  4295  4295 I APPTAG: UpdatesDB constructor
05-04 07:47:33.023  4295  4295 I APPTAG: SQLiteDatabaseWrapperFactory create
05-04 07:47:33.029  4295  4295 I APPTAG: onConfigure
05-04 07:47:33.030  4295  4295 I APPTAG: DB version: 7
05-04 07:47:33.030  4295  4295 I APPTAG: DB path: /data/user/0/<application-name>/databases/updates.db
05-04 07:47:33.030  4295  4295 I APPTAG: onConfigure version: 7
05-04 07:47:33.031  4295  4295 I APPTAG: onOpen
05-04 07:47:33.031  4295  4295 I APPTAG: DB version: 7

Interestingly, if I execute this command PRAGMA user_version; directly on the database, I get the version as 6. But when I tried through the code (as in onConfigure), it gives me 7. Due to this, it skips the onUpgrade method as per this code.

Could anyone help me understand why the db version is getting reported as 7 when I fetch it through code?

Clarifications:

• I'm calling getWritableDatabase() to get the db instance.
• DBContracts.DATABASE_VERSION is set to 7

Answer 1

I believe that your issue lies outside of the code in your question, or perhaps it is a misuse the fist constructor (perhaps comment that out and see how you fare).

Consider this modification (primarily see the end of the code and the i.e. the added getDatabaseVersion method), to include retrieval of the version number prior to to opening the database and other minor changes for my convenience :-

class DBHelper extends SQLiteOpenHelper {
    //@Inject
    /* MAKE SURE THIS CONSTRUCTOR CAN'T BE USED, it could cause issues
    public DBHelper(final Context context) {
        this(context, DBContracts.DATABASE_FILE_NAME);
    }
     */
    
    /* Changed logger to Log.i throughout */
    
    DBHelper(final Context context, final String databaseFileName) {
        super(context, databaseFileName, null, DBContracts.DATABASE_VERSION);
        Log.i("APPTAG","PREOPEN VERSION = " + getDatabaseVersion(context)); //<<<<<<<<<< ADDED 
        Log.i("APPTAG", "DBHelper constructor");
        Log.i("APPTAG", "DATABASE_VERSION : " + DBContracts.DATABASE_VERSION);
    }

    @Override
    public void onCreate(final SQLiteDatabase db) {
        Log.i("APPTAG", "onCreate");
        // create scripts
    }

    @Override
    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
        Log.i("APPTAG", "onUpgrade");
        Log.i("APPTAG", "oldVersion: " + oldVersion);
        Log.i("APPTAG", "newVersion: " + newVersion);
        // upgrade scripts
    }

    @Override
    public void onConfigure(final SQLiteDatabase db) {
        Log.i("APPTAG", "onConfigure");
        Log.i("APPTAG", "DB version: " + db.getVersion());

        try {
            Log.i("APPTAG", "DB path: " + db.getPath());
            int version = ((Long) DatabaseUtils.longForQuery(db, "PRAGMA user_version;", null)).intValue();
            Log.i("APPTAG", "onConfigure version: " + version);
        } catch (Exception e) {
            Log.e("APPTAG", "Error" + e, e);
        }

        super.onConfigure(db);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("APPTAG", "onDowngrade");
        Log.i("APPTAG", "oldVersion: " + oldVersion);
        Log.i("APPTAG", "newVersion: " + newVersion);
        super.onDowngrade(db, oldVersion, newVersion);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        Log.i("APPTAG", "onOpen");
        Log.i("APPTAG", "DB version: " + db.getVersion());
        super.onOpen(db);
    }

    /*<<<<<<<<<< ADDED >>>>>>>>>>*/
    private int getDatabaseVersion(Context context) {
        int rv = -666;
        SQLiteDatabase db = null;
        File databaseFilePath = new File(context.getDatabasePath(DBContracts.DATABASE_FILE_NAME).getPath());
        File databaseDirectory = databaseFilePath.getParentFile();
        /* If database file does not exist AND version is 6 then create the databases directory and then create the database */
        if (! databaseFilePath.exists() && (DBContracts.DATABASE_VERSION == 6)) {
            databaseDirectory.mkdirs();
            db = SQLiteDatabase.openOrCreateDatabase(databaseFilePath.getPath(),null);
        }
        else {
            db = SQLiteDatabase.openDatabase(context.getDatabasePath(DBContracts.DATABASE_FILE_NAME).getPath(), null, SQLiteDatabase.OPEN_READWRITE);
        }
        if (db != null) {
            rv = db.getVersion();
            db.close();
        }
        return rv;
    }
}

Then the following code in an activity:-

public class MainActivity extends AppCompatActivity {

    DBHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = new DBHelper(this,DBContracts.DATABASE_FILE_NAME);
        SQLiteDatabase db = dbHelper.getWritableDatabase(); //<<<<<<<<<< Force an open of the db
    }
}

The App was run twice:-

1. First with DBContracts.DATABASE_VERSION as 6
   1. This would then create version 0, which would then be changed by the SQLiteOpenHelper to be version 6
2. Second without uninstalling the App with DBContracts.DATABASE_VERSION as 7

The results being :-

2022-05-05 21:30:06.639 I/APPTAG: PREOPEN VERSION = 0
2022-05-05 21:30:06.640 I/APPTAG: DBHelper constructor
2022-05-05 21:30:06.640 I/APPTAG: DATABASE_VERSION : 6
2022-05-05 21:30:06.643 I/APPTAG: onConfigure
2022-05-05 21:30:06.644 I/APPTAG: DB version: 0
2022-05-05 21:30:06.644 I/APPTAG: DB path: /data/user/0/a.a.so721000750unupdatenotbeingcalled/databases/testdb
2022-05-05 21:30:06.645 I/APPTAG: onConfigure version: 0
2022-05-05 21:30:06.645 I/APPTAG: onCreate
2022-05-05 21:30:06.655 I/APPTAG: onOpen
2022-05-05 21:30:06.655 I/APPTAG: DB version: 6 

• as expected (onUpgrade not called as there is no upgrade rather onCreate is called which sets the version as 6).

Then :-

2022-05-05 21:33:37.138 I/APPTAG: PREOPEN VERSION = 6
2022-05-05 21:33:37.138 I/APPTAG: DBHelper constructor
2022-05-05 21:33:37.138 I/APPTAG: DATABASE_VERSION : 7
2022-05-05 21:33:37.140 I/APPTAG: onConfigure
2022-05-05 21:33:37.141 I/APPTAG: DB version: 6
2022-05-05 21:33:37.141 I/APPTAG: DB path: /data/user/0/a.a.so721000750unupdatenotbeingcalled/databases/testdb
2022-05-05 21:33:37.141 I/APPTAG: onConfigure version: 6
2022-05-05 21:33:37.141 I/APPTAG: onUpgrade
2022-05-05 21:33:37.142 I/APPTAG: oldVersion: 6
2022-05-05 21:33:37.142 I/APPTAG: newVersion: 7
2022-05-05 21:33:37.152 I/APPTAG: onOpen
2022-05-05 21:33:37.153 I/APPTAG: DB version: 7

• this exactly as expected; that is
  • the database before an attempt to open is version 6 and
  • the DATABASE_VEARSION is 7.
  • At onConfigure the version is 6.
  • onUpgrade is called to change from 6 to 7 and
  • finally the version is 7 when the database is opened.

A third run, without changing anything (so db already at version 7) then :-

2022-05-05 21:43:02.167 I/APPTAG: PREOPEN VERSION = 7
2022-05-05 21:43:02.167 I/APPTAG: DBHelper constructor
2022-05-05 21:43:02.167 I/APPTAG: DATABASE_VERSION : 7
2022-05-05 21:43:02.170 I/APPTAG: onConfigure
2022-05-05 21:43:02.170 I/APPTAG: DB version: 7
2022-05-05 21:43:02.170 I/APPTAG: DB path: /data/user/0/a.a.so721000750unupdatenotbeingcalled/databases/testdb
2022-05-05 21:43:02.171 I/APPTAG: onConfigure version: 7
2022-05-05 21:43:02.171 I/APPTAG: onOpen
2022-05-05 21:43:02.171 I/APPTAG: DB version: 7

• Which bar the questioned (as per the comments) 2 logged lines and the additional PREOPEN logged line basically matches what you have shown. Which could be your issue if for some reason you are incorrect when you say "Interestingly, if I execute this command PRAGMA user_version; directly on the database, I get the version as 6."