Upgrade to Pro — share decks privately, control downloads, hide ads and more …

From the Big Bang to Room @ Londroid

From the Big Bang to Room @ Londroid

Story and experience about the migration of our database @ Busuu from SQLite to Room. Efficiency comparisons, benchmarking and personal views.
By @niksheva and @clau_cookie
Londroid - Android Meetup,
2018

Nikolai Shevchenko

March 20, 2018
Tweet

More Decks by Nikolai Shevchenko

Other Decks in Programming

Transcript

  1. If you look at your old code and it looks

    fine, it means you didn’t learn anything new recently.
  2. busuu 2018 ABSTRACTIONS 2015 SQLite + Pre Bundler 2016 ORMlite

    + Upgrade Resolver 2017 Room + Kotlin t
  3. busuu 2018 TIMELINE 2015 SQLite + Pre Bundler 2016 ORMlite

    + Upgrade Resolver 2017 Room + Kotlin
  4. busuu 2018 SQLite - embedded, open source and lightweight database

    SQLite Zero configuration Serverless Single file database Readable source code Handwritten SQL
  5. busuu 2018 SQLite Queries can get long Lots of Boilerplate

    No compile time checks 1MB BLOB Limitation Zero configuration Serverless Single file database Readable source code Handwritten SQL SQLite - embedded, open source and lightweight database
  6. busuu 2018 SQLite Comes with handy SQLiteOpenHelper public class BusuuDbHelper

    extends SQLiteOpenHelper { public void onCreate(SQLiteDatabase db) { // create db } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // upgrade existing db } }
  7. busuu 2018 SQLite Comes with handy SQLiteOpenHelper public class BusuuDbHelper

    extends SQLiteOpenHelper { public void onCreate(SQLiteDatabase db) { // create db } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // upgrade existing db } }
  8. busuu 2018 public class UserTable { public static final String

    TABLE_NAME = "user"; public static final String COL_ID = "_id"; public static final String COL_PERSONAL_NAME = "name"; public static final String COL_EMAIL = "email"; public static String buildCreateTableQuery() { return "CREATE TABLE " + TABLE_NAME + "("+ COL_ID + " TEXT PRIMARY KEY, " + COL_PERSONAL_NAME + " TEXT, " + COL_EMAIL + " TEXT," + ");"; } } Define models Create db Create tables Copy the db Pre-bundler
  9. busuu 2018 // create database SqliteDatabase db = SQLiteDatabase.openOrCreateDatabase( String

    NAME, CursorFactory factory, DatabaseErrorHandler errorHandler); Define models Create db Create tables Copy the db Pre-bundler
  10. busuu 2018 database.exec(InAppPurchaseTable.buildCreateTableQuery()); database.exec(CommunityExerciseTable.buildCreateTableQuery()); database.exec(CommunityExerciseImageTable.buildCreateTableQuery()); database.exec(CommunityExerciseCommentTable.buildCreateTableQuery()); database.exec(InAppPurchaseTable.buildCreateTableQuery()); // exercises database.exec(CommunityExerciseTable.buildCreateTableQuery());

    database.exec(CommunityExerciseImageTable.buildCreateTableQuery()); database.exec(CommunityExerciseCommentTable.buildCreateTableQuery()); database.exec(CommunityExerciseTable.buildCreateTableQuery()); // user database.exec(CommunityExerciseImageTable.buildCreateTableQuery()); database.exec(CommunityExerciseCommentTable.buildCreateTableQuery()); Define models Create db Create tables Copy the db Pre-bundler
  11. busuu 2018 Define models Create db Create tables Copy the

    db /output db of pre-bundler .../src/main/assets/databases Pre-bundler
  12. busuu 2018 SQLite Usage @Override public void save(User user) {

    final ContentValues values = new ContentValues(); final String userId = user.getId(); if (userId != null) { values.put(UserTable.COL_ID, userId); } final String name = user.getName(); if (name != null) { values.put(UserTable.COL_PERSONAL_NAME, name); } database.insertOrThrow(UserTable.TABLE_NAME, values); } Save data Read data
  13. busuu 2018 SQLite Usage Cursor cursor = null; String[] columns

    = { UserTable.COL_ID, UserTable.COL_PERSONAL_NAME, UserTable.COL_PREMIUM, UserTable.COL_UNSEEN_EXERCISES_COUNT … }; try { cursor = database.query(UserTable.TABLE_NAME, columns); user = mapperUserCursorToUser(cursor); } finally { cursor.close(); } Save data Read data
  14. busuu 2018 1 new column ~ 10 min Cursor to

    read ContentValues to store Update mappers Run pre-bundler SQLite drawbacks
  15. busuu 2018 Time consuming ~ 10 min No offline persistence

    on app update Run-time exceptions SQLite drawbacks
  16. busuu 2018 Time consuming ~ 10 min No offline persistence

    on app update Run-time exceptions Perpetuous conflicts SQLite drawbacks
  17. busuu 2018 TIMELINE 2015 SQLite + Pre Bundler 2016 ORMlite

    + Upgrade Resolver 2017 Room + Kotlin
  18. busuu 2018 ORMLITE @DatabaseTable(tableName = UserEntity.TABLE_NAME) public class UserEntity {

    public static final String TABLE_NAME = "user"; public static final String COL_ID = "_id"; public static final String COL_PERSONAL_NAME = "personal_name"; @DatabaseField(columnName = COL_ID, id = true) public String mId; @DatabaseField(columnName = COL_PERSONAL_NAME) public String mPersonalName; public String getId() { return mId; } public void setId(String id) { mId = id; } } Define models Create tables Use tables Update tables
  19. busuu 2018 ORMLITE private val userDao: RuntimeExceptionDao<UserEntity, String> @Throws(DatabaseException::class) private

    fun loadUserProfile(userId: String): User? { val userEntity = userDao.queryForId(userId) return userMapper.lowerToUpperLayer(userEntity) } Define models Create tables Use tables Update tables
  20. busuu 2018 ORMLITE //UpgradeResolver.kt fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, source:

    ConnectionSource ) { if (oldVersion < NEW_VERSION) { val dao = DaoManager.createDao(...) dao.executeRaw( "ALTER TABLE `" + DbUserEvent.TABLE_NAME + "` ADD COLUMN " + DbUserEvent.COL_ID + " INTEGER PRIMARY KEY;" ) } } Define models Create tables Use tables Update tables
  21. busuu 2018 Time consuming ~ 10 min No offline persistence

    Run-time exceptions Perpetuous conflicts < 1 min Seamless update Clear data Never ORMLITE
  22. busuu 2018 ORMLITE drawbacks Upgrade resolver a bit annoying. No

    control under the hood. Foreign keys = multiple queries.
  23. busuu 2018 ORMLITE drawbacks Upgrade resolver a bit annoying. No

    control under the hood. Foreign keys = multiple queries. A bit slower than SQLite.
  24. busuu 2018 ORMLITE It seemed to be good Why did

    you need to migrate to ROOM then?
  25. busuu 2018 Our Component table Education team grew a lot

    Exercises got bigger and longer Nested complex queries
  26. busuu 2018 Our Component table Education team grew a lot

    Exercises got bigger and longer Nested complex queries Offline Mode
  27. busuu 2018 Speed times BEFORE None 2.9 s (2912 ms)

    3.0 s (3012 ms) 3.1 s (3110 ms) 2 lessons 4.9 s (4915 ms) 5.7 s (9740 ms) 3 lessons 5.5 s (5542 ms) 6.1 s (9124 ms) 8 lessons 8.1 s (8133 ms) 11.2 s (11204 ms) Lower end devices
  28. busuu 2018 Splittin’ like we trippin’ Component Id Name Icon

    ... Lesson Unit Activity Exercise Split content Cheaper than querying a enormous table
  29. busuu 2018 Splittin’ like we trippin’ Component Id Name Icon

    ... Lesson Unit Activity Exercise Load the course
  30. busuu 2018 TIMELINE 2015 SQLite + Pre Bundler 2016 ORMlite

    + Upgrade Resolver 2017 Room + Kotlin
  31. busuu 2018 ROOM Creating tables Simple data class Easy contracts

    declaration @Entity( tableName = LESSONS_DB, indices = [(Index("remoteId", unique = true))] ) data class LessonEntity( @PrimaryKey(autoGenerate = true) val id: Int, val remoteId: String, val type: String, val bucket: Int?, val description: String, val thumbnail: String, val title: String )
  32. busuu 2018 ROOM DAOs Easy to use Simple DAOs //

    CourseRepository.kt @Inject lateinit var CourseDao courseDao fun loadExercises() { courseDao.loadAllExercises() } // CourseDao.kt @Dao abstract class CourseDao { @Query("SELECT * FROM $EXERCISES_DB") abstract fun loadAllExercises(): Single<List<ExerciseEntity>> }
  33. busuu 2018 ROOM DB Easy to read No endless tables

    definition Fast & Easy to setup For us 1 gradle dependency const val DATABASE_VERSION = 220 @Database( version = DATABASE_VERSION, entities = [ LessonEntity::class, UnitEntity::class, ActivityEntity::class, ExerciseEntity::class, LearningEntity::class, TranslationEntity::class ] ) abstract class BusuuDatabase : RoomDatabase() { abstract fun courseDao(): CourseDao }
  34. busuu 2018 ROOM Upgrade @Entity( tableName = LESSONS_DB, indices =

    [(Index("remoteId", unique = true))] ) data class LessonEntity( @PrimaryKey(autoGenerate = true) val id: Int, val remoteId: String, val type: String, val bucket: Int?, val description: String, val thumbnail: String, val title: String val newfield: String, ) Easy to upgrade Add a column in a table and increase database version
  35. busuu 2018 ROOM Upgrade fun init(context: Context): BusuuDatabase { val

    builder = Room.databaseBuilder( context, BusuuDatabase::class.java, ROOM_DATABASE ) builder.fallbackToDestructiveMigration() return builder.build() } Easy to upgrade Fallback destructive migration
  36. busuu 2018 ROOM + RX @Dao abstract class CourseDao {

    @Query("SELECT * FROM $ACTIVITIES_DB WHERE unitId = :unitId") abstract fun loadActivitiesWithUnitId(unitId: String): Maybe<List<ActivityEntity>> @Insert(onConflict = OnConflictStrategy.REPLACE) abstract fun insertExercise(exercise: ExerciseEntity) @Query("SELECT * FROM $GROUP_LEVEL_DB") abstract fun loadGroupLevels(): Flowable<List<GroupLevelEntity>> @Query("SELECT * FROM $EXERCISES_DB") abstract fun loadAllExercises(): Single<List<ExerciseEntity>> }
  37. busuu 2018 ROOM Speed times BEFORE AFTER Constant 696ms None

    2.9 s (2912 ms) 3.0 s (3012 ms) 3.1 s (3110 ms) 2 lessons 4.9 s (4915 ms) 9.7 s (9740 ms) 3 lessons 5.5 s (5542 ms) 9.1 s (9124 ms) 4 lessons 8.1 s (8133 ms) 11.2 s (11204 ms)
  38. busuu 2018 ROOM Advantages Compile time checks FULL SQLite power

    Easy & testable migration No queries on Main Thread No boilerplate
  39. busuu 2018 Conclusion 1/2 Profile your best and worst case

    scenario. Always split your data! Know how the tool you use works under the hood. Think of users.
  40. busuu 2018 Conclusion 1/2 Monitor small issues, they might grow.

    Extra care during data migration. Cache gainz. Dream big, take baby steps