I am using prepopulated sqlite database in my android application. Now the problem is when i update data in my database and i increment the databse version onUpgrade() method is called but my database is not updated. Any help would be appreciated.

Here is my DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {

    private Context mContext;
    private SQLiteDatabase mDatabase;

    public DatabaseHelper(Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
        this.mContext = context;


        // Copy the Database if no database exists
        if (!DatabaseHandler.checkDataBase(context)) {
            DatabaseHandler.copyDataBase(context, Constants.DB_NAME, Constants.DB_VERSION);
        }
        mDatabase = this.getWritableDatabase();
    }


    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }


    public void openDatabase() throws SQLException {
        mDatabase = this.getWritableDatabase();
    }

    public Cursor getAllQuotes() {

        String query = "SELECT * FROM "   Constants.TABLE_QUOTE;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        try {
            openDatabase();
            cursor = db.rawQuery(query, null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cursor;
    }


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

And Here is my DatabaseHandler.java

CodePudding user response：

Now the problem is when i update data in my database and i increment the databse version onUpgrade() method is called but my database is not updated.

Assuming that you mean that you have updated the pre-populated database and thus want that new pre-populated database (which has been placed into the assets folder) to be applied. Then you need to call the appropriate DatabaseHandler's copyDatabase method.

It is not recommended to do this in the onUpragde method as the database has already been open/allocated at this stage.

It is recommended to check if there is an upgrade due before the SQLiteOpenHelper sub class's super call does the check.

The DatabaseHandler has a method getVersionFromAssetFile that retrieves the version of the asset database but not the version of the database.

If the version in the asset file, were used to check against the coded database version (DB_VERSION) then as the asset file is protected and cannot be changed within the app, testing the coded database version with asset version would result in a mismatch (upgrade) every time the app is run.

As such you means of checking the coded version against the database version to detect an upgrade.

As such you could change the DatabaseHandler class to include :-

/**
 * Get the SQLite user_version from the DB
 * @param context   used to get the database path
 * @param databaseName  the name of the database (assumes database is stored in default location)
 * @return the version number as stored in the Database
 */
public static int getVersionFromDatabaseFile(Context context, String databaseName) {
    InputStream is;
    try {
        is = new FileInputStream(context.getDatabasePath(databaseName));
    } catch (IOException e) {
        return OUCH;
    }
    return getDBVersionFromInputStream(is);
}

You could then amend the testing that determines if the asset should be copied to copy the asset if the database doesn't exist or if the database exists to copy the asset (new) if the database version is lower than the coded version.

e.g. :-

public DatabaseHelper(Context context) {
    super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
    this.mContext = context;

    // Copy the Database if no database exists
    // **NEW** or copy the Database if the database version is less than the
    // coded version (DB_VERSION)
    if (!DatabaseHandler.checkDataBase(context)) {
        DatabaseHandler.copyDataBase(context, Constants.DB_NAME, Constants.DB_VERSION);
    } else {
        if (DatabaseHandler.getVersionFromDatabaseFile(context,Constants.DB_NAME) < Constants.DB_VERSION) {
            DatabaseHandler.copyDataBase(context,Constants.DB_NAME,Constants.DB_VERSION);
        }
    }
    mDatabase = this.getWritableDatabase();
}

• Note that the above would copy the asset whenever the coded version (DB_VERSION were increased). More complex handling on upgrades could take into consideration the version of the asset database by utilising the getVersionFromDBInAssetFolder e.g.

        if (DatabaseHandler.getVersionFromDatabaseFile(context,Constants.DB_NAME) < Constants.DB_VERSION
                && DatabaseHandler.getVersionFromDBInAssetFolder(context,Constants.DB_NAME) == Constants.DB_VERSION
        ) {
            DatabaseHandler.copyDataBase(context,Constants.DB_NAME,Constants.DB_VERSION
            );
        }
  

  • thus the asset copy would only happen when the version in the asset database were the same as the updated DB_VERSION

• Note that the code above is in-principle code, it has not been run, so it may contain some errors.