Merge user's Android Room database when migrating database

Question

I have a Room database which is stored in assets/database with its preloaded data. I am creating an updated version with more contents in the database for the next update.

Currently, if I add new contents to the database with no schema changes and reinstall the app, these new contents do not show up. The only way I can see the changes is if I uninstall and reinstall the app. However, I need to merge the user's data with the database with the new contents since I need to get the "favorites" of the user which is an integer column of a table with the item contents.

Is this possible?

This is how I create my database.

public static AppDatabase getInMemoryDatabase(Context context) {
    if (INSTANCE == null) {
        synchronized (AppDatabase.class) {
            if (INSTANCE == null) {
                INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "app_database.db")
                        .createFromAsset("database/QuotesDB.db")
                        .addMigrations(MIGRATION_1_2)
                        .build();
            }
        }
    }
    return INSTANCE;
}

I tried to migrate with the following code but it still doesn't update the contents.

/**
 * Migrate from:
 * version 1 - initial contents.
 * to
 * version 2 - updated database contents (no schema changes)
 */
@VisibleForTesting
static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        // I need to tell Room that it should use the data
        // from version 1 ( with the user's favorites ) to version 2.
    }
};

Answer 1

Is this possible? Yes. However it is a little complex.

In short, you can actually do it the other way round. Rather than use the new database from the asset and try to retrive the previous data (is complicated if using Room Migration as you have to effectiviely swap to the newly created/copied database which is further complicated as you're inside a transaction when migrating).

If however you do the schema changes to the database in use rather than the asset database, you can then get the asset database and copy the new non-user data (would be greatly complicated if the user's data were intermingled with the non-user data).

Even this isn't that simple. However, here's a simple exmaple/scanario based upon a slight expansion of your code to :-

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase db) {
        final String TAG = "MIGRATE_1_2";
        Log.d(TAG,"Database Version when called is " + db.getVersion());

        // I need to tell Room that it should use the data
        // from version 1 ( with the user's favorites ) to version 2.

        // "CREATE TABLE IF NOT EXISTS `userdata` (`userId` INTEGER DEFAULT uid, `name` TEXT, PRIMARY KEY(`userId`))"
        //db.execSQL("CREATE TABLE IF NOT EXISTS `userdata_saveuserdata` (`userId` INTEGER, `name` TEXT, PRIMARY KEY(`userId`))");
        //db.execSQL("INSERT INTO `userdata_saveuserdata` SELECT * FROM `userdata`");
        db.execSQL("ALTER TABLE `otherdata` ADD COLUMN `column2` TEXT");
        Log.d(TAG,"Checking Context");
        if (sContext != null) {
            applyAssetDB(db);
        } else {
            Log.d(TAG,"Context is null!!!!");
        }
    }
};

As you can see this changes the otherdata table (not the users table) by adding a new column.

It then checks to see if sContext isn't null.

• A valid Context is used rather then hard coding paths.

Then the applyAssetDB is invoked, which is :-

private static void applyAssetDB(SupportSQLiteDatabase sdb) {
    String TAG = "APPLYASSETDB";
    String mainDatabaseName = (new File(sdb.getPath()).getName());
    String assetDatabaseName = mainDatabaseName + "_asset";
    String asset_schema = "asset_schema";
    Log.d(TAG,"Attempting application of asset data to database."
                    + "\n\tActual Database = " + mainDatabaseName
                    + "\n\tAsset Database will be " + assetDatabaseName
                    + "\n\tSchema for attached database will be  " + asset_schema
            );
    copyDatabaseFromAssets(AppDatabase.sContext,MainActivity.ASSETNAME,assetDatabaseName);
    /*
    if (sdb.isWriteAheadLoggingEnabled()) {
        setAssetDBToWALMode(sContext.getDatabasePath(assetDatabaseName).getPath());
    }
    Log.d(TAG,"Attempting to ATTACH  asset database " + sContext.getDatabasePath(assetDatabaseName).getPath() + "." + asset_schema);
    sdb.execSQL("ATTACH DATABASE '" + sContext.getDatabasePath(assetDatabaseName).getPath() + "' AS " + asset_schema);
    Log.d(TAG,"Attempting INSERTING NEW DATA using\n\t" + "INSERT OR IGNORE INTO `otherdata` SELECT * FROM `otherdata`." + asset_schema);
    sdb.execSQL("INSERT OR IGNORE INTO `otherdata` SELECT * FROM `otherdata`." + asset_schema);
    Log.d(TAG,"Attempting to DETACH " + sContext.getDatabasePath(assetDatabaseName).getPath() + "." + asset_schema);
    sdb.execSQL("DETACH DATABASE '" + sContext.getDatabasePath(assetDatabaseName).getPath() + "." + asset_schema);
    */

    int insertRows = 0;
    SQLiteDatabase assetdb = SQLiteDatabase.openDatabase(sContext.getDatabasePath(assetDatabaseName).getPath(),null,SQLiteDatabase.OPEN_READONLY);
    Cursor assetCursor = assetdb.query("`otherdata`",null,null,null,null,null,null);
    ContentValues cv = new ContentValues();
    while (assetCursor.moveToNext()) {
        cv.clear();
        for (String c: assetCursor.getColumnNames()) {
            if (assetCursor.getType(assetCursor.getColumnIndex(c)) == Cursor.FIELD_TYPE_BLOB) {
                cv.put(c,assetCursor.getBlob(assetCursor.getColumnIndex(c)));
            } else {
                cv.put(c,assetCursor.getString(assetCursor.getColumnIndex(c)));
            }
        }
        if (sdb.insert("`otherdata`", OnConflictStrategy.IGNORE,cv) > 0 ) insertRows++;
    }
    Log.d(TAG,"Inserted " + insertRows + " from the Asset Database");
    assetCursor.close();

    Log.d(TAG,"Deleting " + sContext.getDatabasePath(assetDatabaseName).getPath());
    if ((new File(sContext.getDatabasePath(assetDatabaseName).getPath())).delete()) {
        Log.d(TAG,"Copied AssetDatabase successfully deleted.");
    } else {
        Log.d(TAG,"Copied Asset Database file not deleted????");
    }
    Log.d(TAG,"Finished");
}

• commented out code left in intentionally as issue were encountered when trying to ATTACH the database copied from the assets, so reverted to using seperate connetions.

This copies the database from the asset to the defauly database location via the copyDatabaseFromAssets method (as below). It the extracts all of the non user's data from the asset database and inserts it into the original (but altered according to the changed schema) database relying upon the OnConflictStrategy.IGNORE to only insert new rows. The userdata table is untouched, so the user's data is retianed.

• Obviously this would not cater for changed rows.

Here's copyDatabaseFromAssets

private static void copyDatabaseFromAssets(Context context, String assetName, String databaseName) {
    String TAG = "COPYDBFROMASSET";
    int bufferSize = 1024 * 4, length = 0, read = 0, written = 0, chunks = 0;
    byte[] buffer = new byte[bufferSize];
    try {
        Log.d(TAG,"Attempting opening asset " + assetName + " as an InputFileStream.");
        InputStream is = context.getAssets().open(assetName);
        Log.d(TAG,"Attempting opening FileOutputStream " + context.getDatabasePath(databaseName).getPath());
        OutputStream os = new FileOutputStream(context.getDatabasePath(databaseName));
        Log.d(TAG,"Initiating copy.");
        while((length = is.read(buffer)) > 0) {
            read += length;
            os.write(buffer,0,length);
            written += length;
            chunks++;
        }
        Log.d(TAG,"Read " + read + "bytes; Wrote " + written + " bytes; in " + chunks);
        Log.d(TAG,"Finalising (Flush and Close output and close input)");
        os.flush();
        os.close();
        is.close();
        Log.d(TAG,"Finalised");

    } catch (IOException e) {
        throw new RuntimeException("Error copying Database from Asset " + e.getMessage());
    }
}

Here's an example Activity MainActivity that puts this all together (noting that for convenience I've used allowMainThreadQueries ) :-

public class MainActivity extends AppCompatActivity {

    //public static final int DBVERSION = 1; //!!!!! ORIGINAL
    public static final int DBVERSION = 2;
    public static final String DBNAME = "app_database.db";
    public static final String ASSETNAME = "database/QuotesDB.db";

    AppDatabase appDB;
    AllDao adao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        appDB.setContext(this);
        appDB = Room.databaseBuilder(this,AppDatabase.class,DBNAME)
                .allowMainThreadQueries()
                .createFromAsset(ASSETNAME)
                .addCallback(AppDatabase.CALLBACK)
                .addMigrations(AppDatabase.MIGRATION_1_2)
                .build();
        adao = appDB.allDao();
        appDB.logDBInfo();
        if (adao.getUserDataRowCount() == 3) {
            adao.insertOneUserData(new UserData("ADDEDU100"));
            adao.insertOneUserData(new UserData("ADDEDU200"));
            adao.insertOneUserData(new UserData("ADDEDU300"));
        }
        appDB.logDBInfo();
    }
}

When run (after changing the relevant code for the new schema and increasing the version) the result in the log is :-

2019-11-30 10:56:38.768 12944-12944/a.roommigrationwithassets D/MIGRATE_1_2: Database Version when called is 1
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/MIGRATE_1_2: Checking Context
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/APPLYASSETDB: Attempting application of asset data to database.
        Actual Database = app_database.db
        Asset Database will be app_database.db_asset
        Schema for attached database will be  asset_schema
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/COPYDBFROMASSET: Attempting opening asset database/QuotesDB.db as an InputFileStream.
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/COPYDBFROMASSET: Attempting opening FileOutputStream /data/user/0/a.roommigrationwithassets/databases/app_database.db_asset
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/COPYDBFROMASSET: Initiating copy.
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/COPYDBFROMASSET: Read 12288bytes; Wrote 12288 bytes; in 3
2019-11-30 10:56:38.771 12944-12944/a.roommigrationwithassets D/COPYDBFROMASSET: Finalising (Flush and Close output and close input)
2019-11-30 10:56:38.772 12944-12944/a.roommigrationwithassets D/COPYDBFROMASSET: Finalised
2019-11-30 10:56:38.780 12944-12944/a.roommigrationwithassets D/APPLYASSETDB: Inserted 3 from the Asset Database
2019-11-30 10:56:38.780 12944-12944/a.roommigrationwithassets D/APPLYASSETDB: Deleting /data/user/0/a.roommigrationwithassets/databases/app_database.db_asset
2019-11-30 10:56:38.780 12944-12944/a.roommigrationwithassets D/APPLYASSETDB: Copied AssetDatabase successfully deleted.
2019-11-30 10:56:38.780 12944-12944/a.roommigrationwithassets D/APPLYASSETDB: Finished
2019-11-30 10:56:38.815 12944-12944/a.roommigrationwithassets D/ONOPEN: Database Version when called is 2
2019-11-30 10:56:38.816 12944-12944/a.roommigrationwithassets D/ONOPEN: Database Version after Super call is 2
2019-11-30 10:56:38.819 12944-12944/a.roommigrationwithassets D/DBINFO: UserData rowcount = 6
        ID = 1 NAME = OU1
        ID = 2 NAME = OU2
        ID = 3 NAME = OU3
        ID = 4 NAME = ADDEDU100
        ID = 5 NAME = ADDEDU200
        ID = 6 NAME = ADDEDU300

    OtherData rowcount = 3
        ID = 1Column1 = OD1
        ID = 2Column1 = OD2
        ID = 3Column1 = OD3
2019-11-30 10:56:38.821 12944-12944/a.roommigrationwithassets D/DBINFO: UserData rowcount = 6
        ID = 1 NAME = OU1
        ID = 2 NAME = OU2
        ID = 3 NAME = OU3
        ID = 4 NAME = ADDEDU100
        ID = 5 NAME = ADDEDU200
        ID = 6 NAME = ADDEDU300

    OtherData rowcount = 3
        ID = 1Column1 = OD1
        ID = 2Column1 = OD2
        ID = 3Column1 = OD3

The complete code for the AppDatabase class (noting that this includes some redundant code) is :-

@Database(version = MainActivity.DBVERSION, exportSchema = false,entities = {UserData.class,OtherData.class})
abstract class AppDatabase extends RoomDatabase {

    abstract AllDao allDao();
    static Context sContext;

    static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(SupportSQLiteDatabase db) {
            final String TAG = "MIGRATE_1_2";
            Log.d(TAG,"Database Version when called is " + db.getVersion());

            // I need to tell Room that it should use the data
            // from version 1 ( with the user's favorites ) to version 2.

            // "CREATE TABLE IF NOT EXISTS `userdata` (`userId` INTEGER DEFAULT uid, `name` TEXT, PRIMARY KEY(`userId`))"
            //db.execSQL("CREATE TABLE IF NOT EXISTS `userdata_saveuserdata` (`userId` INTEGER, `name` TEXT, PRIMARY KEY(`userId`))");
            //db.execSQL("INSERT INTO `userdata_saveuserdata` SELECT * FROM `userdata`");
            db.execSQL("ALTER TABLE `otherdata` ADD COLUMN `column2` TEXT");
            Log.d(TAG,"Checking Context");
            if (sContext != null) {
                applyAssetDB(db);
            } else {
                Log.d(TAG,"Context is null!!!!");
            }
        }
    };

    static final RoomDatabase.Callback CALLBACK = new RoomDatabase.Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            Log.d("ONCREATE","Database Version when called is " + db.getVersion());
            super.onCreate(db);
            Log.d("ONCREATE","Database Version after Super call is " + db.getVersion());
        }

        @Override
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
            Log.d("ONOPEN","Database Version when called is " + db.getVersion());
            super.onOpen(db);
            Log.d("ONOPEN","Database Version after Super call is " + db.getVersion());
        }

        @Override
        public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
            Log.d("ONDESTRMIG","Database Version when called is " + db.getVersion());
            super.onDestructiveMigration(db);
            Log.d("ONDESTRMIG","Database Version after Super call is " + db.getVersion());
        }
    };

    public void logDBInfo() {
        AllDao adao = this.allDao();
        List<UserData> allUserDataRows = adao.getAllUserDataRows();
        StringBuilder sb = new StringBuilder().append("UserData rowcount = ").append(allUserDataRows.size());
        for (UserData u: allUserDataRows) {
            sb.append("\n\tID = ").append(u.getId()).append(" NAME = " + u.getName());
        }
        List<OtherData> allOtherDataRows = adao.getAllOtherDataRows();
        sb.append("\n\nOtherData rowcount = ").append(allOtherDataRows.size());
        for (OtherData o: allOtherDataRows) {
            sb.append("\n\tID = ").append(o.getOtherDataId()).append("Column1 = ").append(o.getColumn1());
        }
        Log.d("DBINFO",sb.toString());
    }

    static  void setContext(Context context) {
        sContext = context;
    }

    private static void applyAssetDB(SupportSQLiteDatabase sdb) {
        String TAG = "APPLYASSETDB";
        String mainDatabaseName = (new File(sdb.getPath()).getName());
        String assetDatabaseName = mainDatabaseName + "_asset";
        String asset_schema = "asset_schema";
        Log.d(TAG,"Attempting application of asset data to database."
                        + "\n\tActual Database = " + mainDatabaseName
                        + "\n\tAsset Database will be " + assetDatabaseName
                        + "\n\tSchema for attached database will be  " + asset_schema
                );
        copyDatabaseFromAssets(AppDatabase.sContext,MainActivity.ASSETNAME,assetDatabaseName);
        /*
        if (sdb.isWriteAheadLoggingEnabled()) {
            setAssetDBToWALMode(sContext.getDatabasePath(assetDatabaseName).getPath());
        }
        Log.d(TAG,"Attempting to ATTACH  asset database " + sContext.getDatabasePath(assetDatabaseName).getPath() + "." + asset_schema);
        sdb.execSQL("ATTACH DATABASE '" + sContext.getDatabasePath(assetDatabaseName).getPath() + "' AS " + asset_schema);
        Log.d(TAG,"Attempting INSERTING NEW DATA using\n\t" + "INSERT OR IGNORE INTO `otherdata` SELECT * FROM `otherdata`." + asset_schema);
        sdb.execSQL("INSERT OR IGNORE INTO `otherdata` SELECT * FROM `otherdata`." + asset_schema);
        Log.d(TAG,"Attempting to DETACH " + sContext.getDatabasePath(assetDatabaseName).getPath() + "." + asset_schema);
        sdb.execSQL("DETACH DATABASE '" + sContext.getDatabasePath(assetDatabaseName).getPath() + "." + asset_schema);
        */

        int insertRows = 0;
        SQLiteDatabase assetdb = SQLiteDatabase.openDatabase(sContext.getDatabasePath(assetDatabaseName).getPath(),null,SQLiteDatabase.OPEN_READONLY);
        Cursor assetCursor = assetdb.query("`otherdata`",null,null,null,null,null,null);
        ContentValues cv = new ContentValues();
        while (assetCursor.moveToNext()) {
            cv.clear();
            for (String c: assetCursor.getColumnNames()) {
                if (assetCursor.getType(assetCursor.getColumnIndex(c)) == Cursor.FIELD_TYPE_BLOB) {
                    cv.put(c,assetCursor.getBlob(assetCursor.getColumnIndex(c)));
                } else {
                    cv.put(c,assetCursor.getString(assetCursor.getColumnIndex(c)));
                }
            }
            if (sdb.insert("`otherdata`", OnConflictStrategy.IGNORE,cv) > 0 ) insertRows++;
        }
        Log.d(TAG,"Inserted " + insertRows + " from the Asset Database");
        assetCursor.close();

        Log.d(TAG,"Deleting " + sContext.getDatabasePath(assetDatabaseName).getPath());
        if ((new File(sContext.getDatabasePath(assetDatabaseName).getPath())).delete()) {
            Log.d(TAG,"Copied AssetDatabase successfully deleted.");
        } else {
            Log.d(TAG,"Copied Asset Database file not deleted????");
        }
        Log.d(TAG,"Finished");
    }

    private static void copyDatabaseFromAssets(Context context, String assetName, String databaseName) {
        String TAG = "COPYDBFROMASSET";
        int bufferSize = 1024 * 4, length = 0, read = 0, written = 0, chunks = 0;
        byte[] buffer = new byte[bufferSize];
        try {
            Log.d(TAG,"Attempting opening asset " + assetName + " as an InputFileStream.");
            InputStream is = context.getAssets().open(assetName);
            Log.d(TAG,"Attempting opening FileOutputStream " + context.getDatabasePath(databaseName).getPath());
            OutputStream os = new FileOutputStream(context.getDatabasePath(databaseName));
            Log.d(TAG,"Initiating copy.");
            while((length = is.read(buffer)) > 0) {
                read += length;
                os.write(buffer,0,length);
                written += length;
                chunks++;
            }
            Log.d(TAG,"Read " + read + "bytes; Wrote " + written + " bytes; in " + chunks);
            Log.d(TAG,"Finalising (Flush and Close output and close input)");
            os.flush();
            os.close();
            is.close();
            Log.d(TAG,"Finalised");

        } catch (IOException e) {
            throw new RuntimeException("Error copying Database from Asset " + e.getMessage());
        }
    }

    private static void setAssetDBToWALMode(String assetDBPath) {
        SQLiteDatabase db = SQLiteDatabase.openDatabase(assetDBPath,null,SQLiteDatabase.OPEN_READWRITE);
        db.enableWriteAheadLogging();
        db.close();
    }
}