Using onUpgrade doesn't work, am I missing something? Thank you

Question

This is my database name from assets folder, I saw someone say that assets is not writable.

public class DatabaseAssets_Milestones extends SQLiteOpenHelper {
    private static String dbName = "milestones.db";
    Context context;
    File dbFile;

This is my Constructor.

    public DatabaseAssets_Milestones(@Nullable Context context) {
        super(context, dbName, null, 2);
        this.context = context;
        File DB_PATH = context.getDatabasePath(dbName);
        String db = DB_PATH.getAbsolutePath();
        dbFile = new File(db);

        if(!dbFile.exists()){
            if (!dbFile.getParentFile().exists()) {
                dbFile.mkdirs();
            }
            copyDataBase(dbFile.getPath());
        }
    }

This is my copy database method.

    private void copyDataBase(String dbPath){

        try{

            InputStream assetDB = context.getAssets().open("databases/"+"milestones.db");
            OutputStream appDB = new FileOutputStream(dbPath,false);

            byte[] buffer = new byte[1024];
            int length;
            while ((length = assetDB.read(buffer)) > 0) {
            appDB.write(buffer, 0, length);
            }
            appDB.flush();
            appDB.close();
            assetDB.close();

        }catch(IOException e){
            e.printStackTrace();
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

Am I missing something here? Or was it something in my other code?

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + dbName);
        onCreate(db);
    }
}

MikeT · Answer 1 · 2021-12-09T05:58:34.423

What you have, assuming that you have a database at version 1 and change to version2, will:-

Skip the copy of the asset as the database exists.
Call onUpgrade which calls onCreate that DROPS the table from the database and then leave you with a database less the dropped table.

The onCreate method dropping the table will not result in the copy of the asset file. To call the copyDatabase method from within onCreate would likely result in issues.

If what you want is the changed asset file to be copied then you might find this or this useful.

If you really wanted to copy the assets file as the new databases (as such) in the onUpgrade method then you could so something like:-

use a modified copyDataBase where you can pass a parameter that modifies the name of the copiedDataBase, thus resulting in
1. the SQliteDatabase as passed to the onUpgrade method, and
2. an SQliteDatabase copied from the asset with a different file name.
ATTACH the asset copy database(2.) to the passed database(1.)
Make preparatory changes, such as dropping tables and perhaps indexes and triggers, to the passed database.
Apply the initial changes to the passed database, such as recreating the dropped tables.
Populate the changed tables as/if required (probably via suitable insert/update queries that extract the data from the asset copy database and insert/update the new/altered tables in the passed database).
Apply the final changes, such as creating indexes and triggers to the passed database 7. leaving indexes to last can be more efficient. 7. you probably don't want triggers to trigger, hence why they probably should be left till last.
DETACH the asset copy database
Delete the asset copy database using the File's delete method.

The above is only a rough guide, depending upon the requirements, deviations may be required.
Again, depending upon the requirements, e.g. if you wanted to basically drop all the project defined tables and then add all the tables from the asset copy, then you could automate this by interrogating the appropriate sqlite_master (the passed to drop existing tables, and the asset copy's sqlite_master to create and populate the replacement tables).
- although for Room something along the lines of what is done in the PrepackagedDatabaseCallback method in this answer (Except that the CREATE .... statements have been hard coded, they would have to be as they could be extracted from the asset copy's sqlite_master)

Using onUpgrade doesn't work, am I missing something? Thank you

1 Answers1