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) {
    }
}

Instead of using `onCreate()` and `onUpdate()` you copy the db from the assets folder. Simply delete the old database before copying the new one. Then store a value in your preferences to tell you that the new database has already been copied, so not to copy it again all the times. — Phantômaxx, Feb 12 '16 at 09:44

score 3 · Accepted Answer · answered Feb 12 '16 at 08:26

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.)

Zahan Safallwa · Answer 2 · 2016-02-11T23:47:48.823

1

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.

edited Feb 11 '16 at 23:47

answered Feb 11 '16 at 23:43

Zahan Safallwa

3,880
2
25
32

When I update the app shouldn't that uninstall the previous version? @ZahanSafallwa – M0rty Feb 11 '16 at 23:47
The database is stored in the assets folder – M0rty Feb 11 '16 at 23:48
Somehow it doesn't remove the database in android @M0rty – Zahan Safallwa Feb 11 '16 at 23:48
Is there a way to use the onUpgrade method to delete the old database and then create the new one? – M0rty Feb 11 '16 at 23:49
No you cant handle it in onUpgrade(). But using sharedpreferences it can be done in your databasehelper class constructor. Then you can delete the database there in the constructor (before the database is open ) @M0rty – Zahan Safallwa Feb 11 '16 at 23:57
if you are interested in using sharedpreference http://stackoverflow.com/questions/11601573/db-file-in-assets-folder-will-it-be-updated – Zahan Safallwa Feb 12 '16 at 00:01

Sqlite database not updating with app update

2 Answers2