Sqlite database not updating with app update

Question

I'm creating an app that uses a Sqlite database. The database contains one table which I'm updating using sqlitebrowser. However whenever I up the database the changes are not seen in the app, possibly due to the app reading the old database. I'm changing the DATABASE_VERSION each time I update the databse but it doesn't seem to help.

I believe the issue is to do with my onUpgrade() method but I'm unsure what to place inside it.

DBHelper class:

public class DBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "BB2SoulDatabase.db";
    public String DB_PATH ="";
    private static final int DATABASE_VERSION = 2;
    public static final String TABLE_NAME = "SOULS";
    public static final String SOUL_COLUMN_NAME = "Name";
    private final Context myContext;
    private SQLiteDatabase myDataBase;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME , null, DATABASE_VERSION);
        this.myContext = context;
        DB_PATH = myContext.getDatabasePath(DATABASE_NAME).getPath();
    }

    public void createDataBase() throws IOException {
        boolean dbExist = checkDataBase();
        if(dbExist){ }
        else {
            this.getReadableDatabase();
            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }
    private boolean checkDataBase(){
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DB_PATH;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
        } catch(SQLiteException e) { }
        if(checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }

    private void copyDataBase() throws IOException{
        InputStream myInput = myContext.getAssets().open(DATABASE_NAME);
        String outFileName = DB_PATH;
        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();
    }

    public void open() throws SQLException {
        try {
            createDataBase();
        } catch (IOException e) {
            throw new Error("\n" +
                    "It was impossible to create the database");
        }
        String myPath = DB_PATH;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }

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


    public Cursor getAllItems(String sortBy, String sortByAffinity, String sortByRace) {
        SQLiteDatabase db = this.getReadableDatabase();
        String orderBy = " DESC";
        String affinity = "";
        String race = "";
        String raceAnd = " WHERE";
        if(sortByAffinity.equals("Quickstrike")) {
            affinity = " WHERE Quickstrike = 'y'";
            raceAnd = " AND";
        }
        else if(!sortByAffinity.equals("Affinity/All")){
            affinity = " WHERE Affinity = '"+sortByAffinity+ "'";
            raceAnd = " AND";
        }

        if(!sortByRace.equals("Race/All")){
            race = raceAnd+" Race = '"+sortByRace+ "'";
        }

        if(sortBy.equals("Name")) orderBy = " ASC";

        Cursor res =  db.rawQuery( "SELECT * FROM " + TABLE_NAME + affinity + race + " ORDER BY "+ sortBy + orderBy , null );
        return res;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

Answer 1

Your code bypasses the normal creation/upgrade framework of the SQLiteOpenHelper class, so you have to handle the upgrade yourself. (Either delete the old database and copy over the new version, or manually upgrade the database, depending on whether you need to keep old data.)

It would be a better idea to use a library like SQLiteAssetHelper that makes this easier for you.

If you have a read-only database (i.e., you know you do not need to keep the old file), just change the file name. This ensures that you can simply copy the new file, if it does not yet exist. (Don't forget to delete the old file, if it exists.)

Answer 2

Scenario:

The database you keep in external directory is not the database your app directly uses. It creates its own database in specified path and just copies it's information from the database of asset folder.

The Problem:

In your code you create (& copy) a database (the one app directly uses) only when it does not exist. when you are creating new database with sqlite browser you are keeping it in external directory. But the database used by the app is already there. So, it does not copy new data from your external directory's new database.

Solution:

Uninstall the previously installed application and then install new one when you bring change to database.