Switching between database files using Room's createFromAsset() returns empty database

Question

I have a spinner that I use to switch between SQLite database files. Upon the spinner selection listener, I pass the relevant database file name to the Room's Database class.

Before switching between databases by calling Room's createFromAsset(), I delete the Room's database file to avoid data caches from the previous database.

My problem is that whenever I switch to another spinner value, the database returns nothing. Upon reading the database file from the app's data on my phone, the database file is there, but the table has no entries.

This is spinner callback:

spinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
    @Override
    public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
        if (mActivateSpinner) {
            
            String newDatabaseName;
            
            switch (position) {
                case 0:
                    newDatabaseName = "database1.db";
                    break;
                case 1:
                    newDatabaseName = "database2.db";
                    break;

                default:
                    newDatabaseName = "database1.db";
            }

            mViewModel.deleteDatabase(newDatabaseName, () -> runOnUiThread(() -> {                  
                
                //
                // Reading database here returns empty data
                //
                
            }));
        }
    }

    @Override
    public void onNothingSelected(AdapterView<?> parent) {
    }
});

ViewModel relevant methods

public class MyViewModel extends AndroidViewModel {

    ....
    
    MyRepository mRepository;
    
    public void deleteDatabase(String newDatabaseName, OnCompletionListener listener) {
        MyRepository.resetInstance();
        MyDataBase.resetInstance();
        
        // delete current Room database file
        deleteDatabaseFile(getApplication().getApplicationContext(), "MyDataBaseFile.db", () -> initRepository(newDatabaseName, listener));
    }


    public void initRepository(String newDatabaseName, OnCompletionListener listener) {
        mRepository = MyRepository.getInstance(getApplication(), newDatabaseName, listener);
    }
    
    
    
    public static void deleteDatabaseFile(Context context, String fileName, OnCompletionListener deleteListener) {
        new Thread(() -> {
            File parent = new File(context.getApplicationInfo().dataDir + "/databases");
            File db = new File(parent, fileName);

            if (db.delete()) {
                deleteListener.onComplete();
                Log.d("TAG", "Database deleted");
            } else 
                Log.d("TAG", "Failed to delete database");
            
        }).start();

    }
    
}

Repository relevant methods

public class MyRepository {

    ...
    
    private MyDataBaseDao mDao;
    private static MyRepository INSTANCE;
        
    public static MyRepository getInstance(Application application, String databaseName, OnCompletionListener listener) {
        if (INSTANCE == null) {
            INSTANCE = new MyRepository(application, databaseName, listener);
        }
        return INSTANCE;
    }
    
    private MyRepository(Application application, String databaseName, OnCompletionListener listener) {
        mDao = MyDataBase.getInstance(application.getApplicationContext(), databaseName, listener).getDao();
    }
    
}

Room database

@Database(entities = {MyTable.class}, version = 1, exportSchema = false)
public abstract class MyDataBase extends RoomDatabase {

    public static final String DATABASE_NAME = "MyDataBaseFile.db";

    private static volatile MyDataBase INSTANCE;

    private static final Object LOCK = new Object();

    public abstract MyDataBaseDao getDao();

    public static void resetInstance() {
        INSTANCE = null;
    }

    static public MyDataBase getInstance(final Context context, String databaseName, OnCompletionListener listener) {
        if (INSTANCE == null) {
            synchronized (LOCK) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                            MyDataBase.class, DATABASE_NAME)
                            .createFromAsset("database/" + databaseName)
                            .build();
                            
                    if (listener != null)
                        listener.onComplete();

                }
            }
        }
        return INSTANCE;
    }

}

The listener.onComplete() gets called, and the database file is there with the expected size, but the table has no data.

Answer 1

Room creates 3 files for each database, as in my case I name the database MyDataBaseFile.db, then Room creates 3 files and name them:

• MyDataBaseFile.db
• MyDataBaseFile.db-wal
• MyDataBaseFile.db-shm

I was just deleting MyDataBaseFile.db and leaving the other two files when I switch the spinner value, and deleting the other two files did really shows the new data in the table

Updated the delete method with

public static void deleteDatabase(Context context, String databaseName, OnCompletionListener deleteListener) {
    new Thread(() -> {
        File parent = new File(context.getApplicationInfo().dataDir + "/databases");
        if (deleteFile(parent, databaseName + "-wal")
                && deleteFile(parent, databaseName + "-shm")
                && deleteFile(parent, databaseName)) {
            deleteListener.onComplete();
            Log.d(TAG, "Database deleted");
        } else
            Log.d(TAG, "Failed to delete database");

    }).start();

}

/*
 * Returns:
 * ****   true: if the file doesn't exist or successfully deleted
 * ****   false: if the file can't be deleted
 * */
private static boolean deleteFile(File parent, String child) {
    File file;

    if (parent != null)
        file = new File(parent, child);
    else
        file = new File(child);

    if (file.exists())
        return file.delete();
    else
        return true;
}

EDIT:

Referring to this answer, instead of deleting the three files, there is a neat solution by just closing the database with close() method, and then just delete the database file.