RoomDB: Migration without schema changes, I just want to drop the old Database and create from asset

Question

My app doesn't need to keep user's custom data yet, all Data in Database is prepopulated via createFromAsset(). Now I want to make a version 2 that, when installed in device that already runs version 1, just drop all the data and feed it with createFromAsset() just like it already works for new installations. The point is version 2 has more data than version 1, but also some old data has been removed and replaced. Remember that user doesn't insert any data at all.

I tried combining createFromAsset() with fallbackToDestructiveMigration() to no avail, even incrementing the schema version parameter in @Database annotation.

db = Room.databaseBuilder(mCtx, AppDatabase.class, "AppDatabase")
            .fallbackToDestructiveMigration()
            .createFromAsset("db/INIT_DB.sqlite3")
            .build();

This would just delete the data when updating the app, but not repopulating from asset as I expected. What is the most simple way to just drop the old database and repopulate it? Must I use migrations for this, even though the DB schema is still the same?

---

EDIT: After trying the Callbacks provided by @MikeT, if I install and run (by pressing Play on AndroidStudio) the "version2" of the App over an already installed "version1" (from the Google Play Store), the logs I obtain are as follows:

First, with fallbackToDestructiveMigration():

2022-06-13 XX:XX:XX.XXX 5859-5907/com.mydomain.myapp I/MYAPP_INFO: CALLBACK -> OnDestructiveMigration Called
2022-06-13 XX:XX:XX.XXY 5859-5907/com.mydomain.myapp I/MYAPP_INFO: CALLBACK -> OnOpen Called

...and now, providing the "empty migration" instead:

2022-06-13 XX:XX:XX.XXX 5859-5907/com.mydomain.myapp I/MYAPP_INFO: MIGRATION! Invoked Migration from V1 to V2
2022-06-13 XX:XX:XX.XXY 5859-5907/com.mydomain.myapp I/MYAPP_INFO: CALLBACK -> OnOpen Called

• The .fallbackToDestructiveMigration() approach just leads to the database getting empty when installing new version over the older, and the asset with the new DB data to be ignored.
• The approach in which I implement the empty migration (as well as the approach in which I try to check the "new version of the asset" and call delete() database from ctx), when installing new version over the older, just keeps using the old data, ignoring (once again) all the new data from the asset. And here comes the funny part: if, after launching the version2, I just hold my finger over my app's icon (OS level), "click" on "clean data" and then "delete all data" (emptying therefore the database itself), and then open the app again, the createFromAsset() kicks in!! The whole point is I'd like to spare final users the nuisance of deleting the old database themselves!

In both cases, if the installation is done without previous version on the device, the .createFromAsset works as expected.

FOR THE RECORD

I am still using a single "asset name" for both versions of the app, and one single asset file: INIT_DB.db (is an SQLITE3 file anyway, but the SDK won't swallow it if I don't use .db extension... yet let's consider the "mock name" INIT_DB.sqlite3 in the code samples to be valid...

Answer 1

I would suggest that the way to go is to detect the change in the asset.

Copying the asset and then comparing rows would be relatively inefficient. A more efficient approach, although a bit more complicated to manage would be to utilise the SQLITE USER_VERSION (or the APPLICATION_ID).

This would entail setting the value in the latest asset to be greater than it was.

A changed (increased) USER_VERSION can be detected by just accessing the files (the current database and the asset) and reading the first 100 bytes and then extracting the 4 bytes at offset 60 (68 for the Application Id).

You can set the values using PRAGMA user_version = n for the USER_VERSION or (PRAGMA application_id = n) for the APPLICATION_ID via whatever tool you are using to maintain the database externally.

You would do this every time you instantiate the Database class BEFORE you build the database. If the asset version is greater (changed) then you delete the database and thus when the build is actioned then createFromAsset will copy the database from the asset.

I would suggest maintaining the database version in-line with the asset (although unlike the SQLite which would thrown an exception on a downgrade API Room doesn't seem to care about downgrade).

Here's a working example.

For the testing two asset databases have been created.

• The first (in the asset folder as V1Base) has rows with id's 1-5 inclusive and the second column name has V1 within the name.
• The second (in the asset folder as V2Base) has id's 2 and 4 from the first (1,3 and 5 have been deleted) and an additional 3 rows, id's 6, 8 and 10 and the name includes V2.

The asset folder (after the 2nd test run):-

• Obviously you would only have the 1 asset. However, for experimentation/testing/debugging the above allows easy manipulation of the files.
  • as the arrows show V1Base was copied to what is now INIT_DB.sqlite3_ORIGINAL (first run it was named INIT_DB.sqlite3) and V2Base was copied (after renaming INIT_DB.sqlite3 to INIT_DB.sqlite3_ORIGINAL) to INIT_DB.sqlite3

Only a single @Entity class was used that being TheTable :-

@Entity
class TheTable {
    @PrimaryKey
    Long id=null;
    String name;
}

An @Dao interface with just a method to extract the data TheTableDao:-

@Dao
interface TheTableDao {
    @Query("SELECT * FROM thetable")
    List<TheTable> getAllTheTableRows();
}

A little more complex than normal @Database annotated abstract class TheDatabase :-

@Database(entities = {TheTable.class}, version = MainActivity.DATABASE_VERSION,exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
    abstract TheTableDao getTheTableDao();

    /* see  https://www.sqlite.org/fileformat.html */
    private static int SQLITE_HEADER_DATA_LENGTH = 100;
    private static int SQLITE_HEADER_USERVERSION_OFFSET = 60;
    private static int SQLITE_HEADER_USERVERSION_LENGTH = 4;


    private volatile static TheDatabase instance=null;
    static TheDatabase getInstance(Context context) {
        if (instance == null) {
            if (isNewAsset(context,MainActivity.ASSET_NAME,MainActivity.DATABASE_NAME)) {
                context.getDatabasePath(MainActivity.DATABASE_NAME).delete();
            }
            instance = Room.databaseBuilder(context,TheDatabase.class,MainActivity.DATABASE_NAME)
                    .allowMainThreadQueries()
                    //.fallbackToDestructiveMigration()
                    .createFromAsset(MainActivity.ASSET_NAME,ppcb)
                    .addMigrations(v1_v2)
                    .addCallback(cb)
                    .build();
        }
        return instance;
    }

    static Migration v1_v2 = new Migration(1,2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            Log.d("MIGV1->V2","Invoked Migration from V1 to V2");
        }
    };

    static Callback cb = new Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);
            Log.d("CALLBACK","OnCreate Called");
        }

        @Override
        public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
            super.onDestructiveMigration(db);
            Log.d("CALLBACK","OnDestructiveMigration Called");
        }

        @Override
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
            super.onOpen(db);
            Log.d("CALLBACK","OnOpen Called");
        }
    };

    static PrepackagedDatabaseCallback ppcb = new PrepackagedDatabaseCallback() {
        @Override
        public void onOpenPrepackagedDatabase(@NonNull SupportSQLiteDatabase db) {
            super.onOpenPrepackagedDatabase(db);
            Log.d("PPCALLBACK","PrepackagedDatabase Called");
        }
    };

    private static boolean isNewAsset(Context context, String asset, String dbname) {
        File current_Db = context.getDatabasePath(dbname);
        if(!current_Db.exists()) return false; /* No Database then nothing to do */
        int current_Db_version = getDBVersion(current_Db);
        Log.d("DBINFO","isNewAsset has determined that the current database version is " + current_Db_version);
        if (current_Db_version < 0) return false; /* No valid version */
        int asssetVersion = getAssetVersion(context,asset);
        Log.d("DBINFO","isNewAsset has determined that the asset version is " + asssetVersion);
        if (asssetVersion > current_Db_version) {
            Log.d("DBINFO","isNewAsset has found that the asset version is greater than the current db version " + current_Db_version);
            return true;
        } else {
            Log.d("DBINFO","isNewAsset has found that the asset version is unchanged " + current_Db_version);
        }
        return false;
    }

    private static int getDBVersion(File f) {
        int rv = -1;
        byte[] buffer = new byte[SQLITE_HEADER_DATA_LENGTH];
        InputStream is;
        try {
            is = new FileInputStream(f);
            is.read(buffer,0,buffer.length);
            is.close();
            rv =  getVersionFromBuffer(buffer);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rv;
    }

    private static int getAssetVersion(Context context,String asset) {
        int rv = -1;
        byte[] buffer = new byte[SQLITE_HEADER_DATA_LENGTH];
        InputStream is;
        try {
            is = context.getAssets().open(asset);
            is.read(buffer,0,buffer.length);
            is.close();
            rv = getVersionFromBuffer(buffer);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rv;
    }


    static int getVersionFromBuffer(byte[] buffer) {
        int rv = -1;
        if (buffer.length == SQLITE_HEADER_DATA_LENGTH) {
            ByteBuffer bb = ByteBuffer.wrap(buffer,SQLITE_HEADER_USERVERSION_OFFSET,SQLITE_HEADER_USERVERSION_LENGTH);
            return  bb.getInt();
        }
        return rv;
    }
}

• Notes
  • Although .allowMainThreadQueries has been used, this is just for convenience, no reliance is made upon it's use.
  • what is important is the placement of the call of isNewAsset, this should be before the build.
  • the code is complicated by callbacks (suggested as it's then easier to understand what is going on; remove them when happy)
  • likewise some Logging
  • see the reference to the SQLite documentation regarding the header.

Last but not least and Activity to demonstrate MainActivity

public class MainActivity extends AppCompatActivity {
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "INIT_DB.sqlite3";
    public static final String ASSET_NAME = "db/" + DATABASE_NAME;

    TheDatabase db;
    TheTableDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getTheTableDao();
        Log.d("DBINFO","Extracting data");
        for (TheTable tt: dao.getAllTheTableRows()) {
            Log.d("DBINFO","ID= " + tt.id + " NAME= " + tt.name);
        }
    }
}

• nothing special. However, constants placed here as it seemed more convenient for testing.

First Run

1. all assets bar V1Base.db and V2Base.db deleted.
2. the App uninstalled if it existed.
3. VB1Base.db copied and pasted as INIT_DB.sqlite3.
4. DATABASE_VERSION set to 1.
5. Run and the log includes

:-

2022-06-12 09:50:37.804 D/DBINFO: Extracting data
2022-06-12 09:50:37.849 D/PPCALLBACK: PrepackagedDatabase Called
2022-06-12 09:50:37.905 D/CALLBACK: OnOpen Called
2022-06-12 09:50:37.910 D/DBINFO: ID= 1 NAME= V1Data001
2022-06-12 09:50:37.910 D/DBINFO: ID= 2 NAME= V1Data002
2022-06-12 09:50:37.911 D/DBINFO: ID= 3 NAME= V1Data003
2022-06-12 09:50:37.911 D/DBINFO: ID= 4 NAME= V1Data004
2022-06-12 09:50:37.911 D/DBINFO: ID= 5 NAME= V1Data005

Second Run

1. Renamed INIT_DB.sqlite3 to INIT_DB.sqlite3_ORIGINAL.
2. Copied and pasted V2Base.db to INIT_DB.sqlite3.
3. DATABASE_VERSION changed to 2.
4. Run and the log includes

:-

2022-06-12 09:58:29.484 D/DBINFO: isNewAsset has determined that the current database version is 1
2022-06-12 09:58:29.484 D/DBINFO: isNewAsset has determined that the asset version is 2
2022-06-12 09:58:29.484 D/DBINFO: isNewAsset has found that the asset version is greater than the current db version 1
2022-06-12 09:58:29.521 D/DBINFO: Extracting data
2022-06-12 09:58:29.560 D/PPCALLBACK: PrepackagedDatabase Called
2022-06-12 09:58:29.571 D/MIGV1->V2: Invoked Migration from V1 to V2
2022-06-12 09:58:29.617 D/CALLBACK: OnOpen Called
2022-06-12 09:58:29.622 D/DBINFO: ID= 2 NAME= V1Data002
2022-06-12 09:58:29.622 D/DBINFO: ID= 4 NAME= V1Data004
2022-06-12 09:58:29.622 D/DBINFO: ID= 6 NAME= V2Data001
2022-06-12 09:58:29.622 D/DBINFO: ID= 8 NAME= V2Data002
2022-06-12 09:58:29.622 D/DBINFO: ID= 10 NAME= V2Data003

As can be seen this time (as the database exists) the versions are obtained, the change has been detected and thus the original database file has been deleted AND the data is now, as expected, from the newer asset.

Third Run

As a test when there is no new/updated asset, the log includes

:-

2022-06-12 11:23:57.249 D/DBINFO: isNewAsset has determined that the current database version is 2
2022-06-12 11:23:57.249 D/DBINFO: isNewAsset has determined that the asset version is 2
2022-06-12 11:23:57.250 D/DBINFO: isNewAsset has found that the asset version is unchanged 2
2022-06-12 11:23:57.287 D/DBINFO: Extracting data
2022-06-12 11:23:57.304 D/CALLBACK: OnOpen Called
2022-06-12 11:23:57.308 D/DBINFO: ID= 2 NAME= V1Data002
2022-06-12 11:23:57.308 D/DBINFO: ID= 4 NAME= V1Data004
2022-06-12 11:23:57.308 D/DBINFO: ID= 6 NAME= V2Data001
2022-06-12 11:23:57.308 D/DBINFO: ID= 8 NAME= V2Data002
2022-06-12 11:23:57.308 D/DBINFO: ID= 10 NAME= V2Data003

i.e. the asset and db are at the same level and the data remains as it was.

Fourth Run

App is uninstalled, so as per a new install, the log includes :-

2022-06-12 11:28:46.025 D/DBINFO: Extracting data
2022-06-12 11:28:46.073 D/PPCALLBACK: PrepackagedDatabase Called
2022-06-12 11:28:46.131 D/CALLBACK: OnOpen Called
2022-06-12 11:28:46.135 D/DBINFO: ID= 2 NAME= V1Data002
2022-06-12 11:28:46.135 D/DBINFO: ID= 4 NAME= V1Data004
2022-06-12 11:28:46.136 D/DBINFO: ID= 6 NAME= V2Data001
2022-06-12 11:28:46.136 D/DBINFO: ID= 8 NAME= V2Data002
2022-06-12 11:28:46.136 D/DBINFO: ID= 10 NAME= V2Data003

i.e. the latest asset is copied.

Additional example of setting the USER_VERSION using DB Brwoser for SQLite

1. Open the database file :-

2. Optional but suggested check the current version (here it is 1):-

3. Change the USER_VERSION to 2:-

• Note that there is no result in the result window but the log window shows that the command was successfully executed.

4. Check the USER_VERSION (strongly suggested) :-

5. SAVE (File/Close Database), quit DB Browser, restart DB Browser, Open the file and check the USER_VERSION again, then File/Close Database.

6. Copy the amended file into the asset replacing (suggest renaming until checked) the previous asset.