Brief review: Room Migration guide for Complex SQLite databases by Google Engineers.

The Room Persistence Library is an official data Persistence Library provided by Google that provides SQLite’s abstraction layer. The official documentation strongly recommends using Room instead of using SQLite directly. If you decide to use Room, but the database is large or you have complex queries, the migration process can be time-consuming and cumbersome.

In this article, a Google engineer disassembled the steps for migrating SQLite to Room into PR.

Project scene

Imagine we have a project that looks like this:

  • Our database has 10 tables, one for each Model class. For example, for the Users table, there is a corresponding User class.
  • A CustomDbHelper inherited from SQLiteOpenHelper.
  • The database is accessed in the LocalDataSource class using the CustomDbHelper.
  • Some tests for LocalDataSource.

First PR

Our first PR will include minimal changes to enable Room.

To achieve the entity class

Add @entity, @primaryKey, and @ColumnInfo annotations to the Model class corresponding to each table.

+ @Entity(tableName = "users") public class User { + @PrimaryKey + @ColumnInfo(name = "userid") private int mId; + @ColumnInfo(name = "username") private String mUserName; public User(int id, String userName) { this.mId = id; this.mUserName = userName; } public int getId() { return mId; } public String getUserName() { return mUserName; }}Copy the code

Create Room database

Create an abstract class that inherits RoomDatabase. List all created Entity classes in the @Database annotation. Add the database version number and implement a Migration:

@Database(entities = {<all entity classes>}, version = <incremented_sqlite_version>) public abstract class AppDatabase extends RoomDatabase { private static UsersDatabase INSTANCE; static final Migration MIGRATION_<sqlite_version>_<incremented_sqlite_version> = new Migration(<sqlite_version>, <incremented_sqlite_version>) {@override public void migrate(SupportSQLiteDatabase database) {// Since we didn't Alter the table, there's nothing else // to do here.Copy the code

Update SQLiteOpenHelper to SupportSQLiteOpenHelper

Initially, we used our own CustomOpenHelper implementation in LocalDataSource. Now we’re going to switch to SupportSQLiteOpenHelper, which provides a much cleaner API.

public class LocalUserDataSource {
    private SupportSQLiteOpenHelper mDbHelper;

    LocalUserDataSource(@NonNull SupportSQLiteOpenHelper helper) {
       mDbHelper = helper;
    }
Copy the code

For inserts:

@Override
public void insertOrUpdateUser(User user) {
    SupportSQLiteDatabase db = mDbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME_ENTRY_ID, user.getId());
    values.put(COLUMN_NAME_USERNAME, user.getUserName());

    - db.insertWithOnConflict(TABLE_NAME, null, values,
    -        SQLiteDatabase.CONFLICT_REPLACE);
    + db.insert(TABLE_NAME, SQLiteDatabase.CONFLICT_REPLACE, values);
    db.close();
}
Copy the code

For queries, SupportSQLiteDatabase provides four methods:

Cursor query(String query);
Cursor query(String query, Object[] bindArgs);
Cursor query(SupportSQLiteQuery query);
Cursor query(SupportSQLiteQuery query, CancellationSignal cancellationSignal);
Copy the code

If the original query operation is simple, you can use the first two methods directly. And if more complex, it recommends a SupportSQLiteQuery SupportSQLiteQueryBuilder structure to help query.

For example, if we want to get the first user sorted by user name in the Users table, let’s see the implementation difference between SQLiteDatabase and SupportSQLiteDatabase:

public User getFirstUserAlphabetically() { User user = null; SupportSQLiteDatabase db = mDbHelper.getReadableDatabase(); String[] projection = { COLUMN_NAME_ENTRY_ID, COLUMN_NAME_USERNAME }; // Get the first user from the table ordered alphabetically - Cursor cursor = db.query(TABLE_NAME, projection, null, - null, null, null, COLUMN_NAME_USERNAME + "ASC", "1"); + SupportSQLiteQuery query = + SupportSQLiteQueryBuilder.builder(TABLE_NAME) + .columns(projection) + .orderby (COLUMN_NAME_USERNAME) +.limit(" 1 ") +.create(); + Cursor cursor = db.query(query); if (c ! =null && c.getCount() > 0){ // read data from cursor ... } if (c ! =null){ cursor.close(); } db.close(); return user; }Copy the code

Next PRs

After completing the above steps our data layer implementation becomes Room, we can step by step to create daOs (including tests) and replace Cursor and ContentValue code with DAOs.

The first user method we implemented above to get sorted by user name from the Users table should be defined in the UserDao interface:

@dao public interface UserDao {@query (" SELECT * FROM Users ORDERED BY name ASC LIMIT 1 ") User getFirstUserAlphabetically(); }Copy the code

And is used in the LocalDataSource class:

public class LocalDataSource { private UserDao mUserDao; public User getFirstUserAlphabetically() { return mUserDao.getFirstUserAlphabetically(); }}Copy the code

The above. If you want to learn more about Room, you can read the article in “Extended Reading” at 👇 (science online).

The original:
Incrementally migrate from SQLite to Room

Read more:

  • 7 Pro-tips for Room
  • Understanding migrations with Room
  • Testing Room migrations