A Handbook about Migration from SQL to Room
Migrate to Room from SQLiteOpenHelper and SQLiteDatabase with Migration Test
After Room stable version released, we decide to migrate our project’s local database from Android SQLiteOpenHelper to Room. We also want to use previous data in our original database (that is, we need migration and should use the same database files between SQLiteOpenHelper and Room.) Thanks my colleagues, Benjamin and Wan-Ching, work together to investigate the Room.
Before all, I strongly suggest to read this blog: Room and Custom Type, it’s awesome and cover all complicated case when using Room with complicated data.
I record the steps and notes about how I migrate our previous database from SQLiteOpenHelper to Room, and how to implement the migration test between 2 different APIs in order to verify these developments.
1. Reuse Previous Database File
To reuse data from previous tables created from SQLiteOpenHelper, I would suggest to use the same database files. So, we can easily create new tables, migrate data, and remove old tables (all operations inside original database) within single Room’s migration.
AppDatabase db = Room.databaseBuilder(getApplicationContext(),
AppDatabase.class, "previous database file name").build();2. Increase Version to Upgrade and Migrate Data
It’s necessary to increase database version. Room_Impl will crash because it cannot find proper schemas to match with Room annotation, so increase database version even no modification for your tables.
How to implement the migration depends on whether you have altered the tables or not.
Non-alter the tables
If you didn’t alter the table (that is, unchanged table name, column names, primary key and etc,) it needn’t to do anything in your migration implementation, but you must implement a Migration class to tell Room that it should use the original data and tables. If no migration is provided, then the tables will be dropped and recreated.
With this solution, you might add lots of @Entity(tableName = “xxx”), @ColumnInfo(name = “xxx”) and etc. into your data class, to make all entities and fields identical.
/**
* Migrate from:
* version 1 - using the SQLiteOpenHelper\SQLiteDatabase * to
* version 2 - using Room
*/
static final Migration MIGRATION_1_2 = new Migration(1, 2) {
@Override
public void migrate(SupportSQLiteDatabase database) {
// There's nothing else to do here
// Since we didn't alter the table
}
};Altered the tables
If table was altered, we have to migrate by ourselves from previous tables to new ones. There would be 3 steps generally in the Migration class:
- Create the new tables
- Clone original data and migrate to new tables
- Delete the old tables
/**
* Migrate from:
* version 1 - using the SQLiteOpenHelper\SQLiteDatabase * to
* version 2 - using Room
*/
static final Migration MIGRATION_1_2 = new Migration(1, 2) {
@Override
public void migrate(SupportSQLiteDatabase database) {
// Create the new table
database.execSQL("CREATE TABLE " + NEW_TABLE_NAME);
// Clone the data
database.execSQL("INSERT INTO " + NEW_TABLE_NAME +
"(column_1, column_2) "+
"SELECT column_a, column_b " +
"FROM " + OLD_TABLE_NAME);
// Delete the old table
database.execSQL("DROP TABLE " + OLD_TABLE_NAME);
}
};3. Migration Test from SQLiteOpenHelper
Room offers migration tests from exported schemas to target version of database. However, since our original database was created by SQLiteOpenHelper, there isn’t any schema from Room migration test.
There are 2 solutions to create initial database, one is to use SQLiteOpenHelper, the other is to create fake schema for MigrationTestHelper.
Use SQLiteOpenHelper to create initial database
We can still use SQLiteOpenHelper as before to create our database in test. The following example displays how to create and run migration validation. Just reminder we cannot use MigrationTestHelper.createDatabase().
@Rule
public MigrationTestHelper migrationHelper;@Test
public void migrate1To2() throws IOException {
// Use TestDbOpenHelper
// to create the database with the initial version 1
TestDbOpenHelper sqliteDbHelper = new TestDbOpenHelper(
InstrumentationRegistry.getTargetContext(),
TEST_DB);
SQLiteDatabase db = sqliteDbHelper.getWritableDatabase();
db.execSQL(CREATE_TABLE");
db.close(); // Re-open the database with version 2 (which Room generated)
// and provide MIGRATION_1_2 as the migration process.
migrationHelper.runMigrationsAndValidate(
TEST_DB, 2, true,
MIGRATION_1_2); }=======================================================/**
* Helper class for creating the test database with SQLite.
*/
public class TestDbOpenHelper extends SQLiteOpenHelper {
public static final int VERSION = 1;
public static final String CREATE_TABLE =
“CREATE TABLE " + OLD_TABLE_NAME + " ("
"colunm_a INTEGER PRIMARY KEY, " +
"colunm_b TEXT )” public TestDbOpenHelper(Context context, String databaseName) {
super(context, databaseName, null, VERSION);
} public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
} public void onUpgrade(SQLiteDatabase db, int old, int new) {
// Not required for test
} public void onDowngrade(SQLiteDatabase db, int old, int new) {
// Not required for test
}
}
Use MigrationTestHelper.createDatabase() to create initial database
But if we would like to use MigrationTestHelper.createDatabase() to create initial database. How? We need to generate schema json file for SQLite database, and it’s easy to create this fake one.
Let’s start for schema json of version 2, which was generated by Room. There are “formatVerison”, “database”, “setupQueries”. And “database” has “identityHash”, “version” and entities information.
To generate schema of version 1, we keep “identityHash”, modify “createSql” and “columnName” to sync with previous database config, set correct “primaryKey”, “indeces” and “foreignKeys” if needed.
Don’t forget to clean out “setupQueries”, we don’t need them.
After generating 1.json, we can use MigrationTestHelper.createDatabase() directly, as following example.
@Rule
public MigrationTestHelper migrationHelper;@Test
public void migrate1To2() throws IOException {
// Create the database in version 1
SupportSQLiteDatabase db = migrationHelper.createDatabase(
TEST_DB, 1); //Prepare for the next version
db.close(); // Re-open the database with version 2
// and provide MIGRATION_1_2 as the migration process.
migrationHelper.runMigrationsAndValidate(
TEST_DB, 2, true,
MIGRATION_1_2);
}
Here are some issues that I faced when adopting Room, I record the solution and reference.
How to Delete All Data
As Google’s Room example, use @Query("DELETE FROM " + TABLE.NAME) to remove all data from the table.
/**
* Delete all users.
*/
@Query(“DELETE FROM Users”)
void deleteAllUsers();Lint Error of RestrictedApi for Code Generated by Room
This issue should be resolved after com.android.tools.build:gradle:3.0.0 by making lint skip generated source folders. If you still need to use gradle v2, I guess the only solution is to disable RestrictedApi in Android gradle.
lintOptions {
disable ‘RestrictedApi’
}