Inside The Room- DCBln21 revised edition

Transcript

1. Inside the room
   Effie Barak
   (@CodingChick)
   

   View Slide

2. View Slide

3. Talk Outline:
   1. Intro
   2. Threading in Room
   3. Triggers
   4. Transactions
   5. Large queries
   

   View Slide

4. View Slide

5. Intro to SQLite
   

   View Slide

6. Embedded SQL database engine
   

   View Slide

7. View Slide

8. View Slide

9. View Slide

10. View Slide

11. View Slide

12. Intro to Android and Android x APIs
    

    View Slide

13. Android package android.database.sqlite
    SQLiteStatement
    
    SQLiteCursor
    
    SQLiteDatabase
    
    SQLiteOpenHelper
    
    SQLiteQuery
    
    

    View Slide

14. Android X androidx.sqlite.db
    SupportSQLiteDatabase
    
    SupportSQLiteOpenHelper
    
    SupportSQLiteQuery
    
    

    View Slide

15. /**
    
    * A database abstraction which removes the framework dependency and allows swapping underlying
    
    * sql versions. It mimics the behavior of {@link android.database.sqlite.SQLiteDatabase}
    
    */
    
    public interface SupportSQLiteDatabase extends Closeable {
    
    

    View Slide

16. Android X androidx.sqlite.db.framework
    FrameworkSQLiteDatabase
    
    FrameworkSQLiteOpenHelper
    
    FrameworkSQLiteStatement
    
    

    View Slide

17. /**
    
    * Delegates all calls to an implementation of {@link SQLiteDatabase}.
    
    */
    
    class FrameworkSQLiteDatabase implements SupportSQLiteDatabase {
    
    

    View Slide

18. private final SQLiteDatabase mDelegate;
    
    @Override
    
    public void beginTransaction() {
    
    mDelegate.beginTransaction();
    
    }
    
    @Override
    
    public void beginTransactionNonExclusive() {
    
    mDelegate.beginTransactionNonExclusive();
    
    }
    
    

    View Slide

19. Intro to Room Architecture
    

    View Slide

20. Isolated modules
    room-common
    room-compiler
    room-runtime
    room-ktx
    room-rxjava2
    

    View Slide

21. Threads, processes etc
    

    View Slide

22. Locking in SQLite
    

    View Slide

23. View Slide

24. Types of locks
    

    View Slide

25. Types of locks
    Unlocked
    

    View Slide

26. Types of locks
    Unlocked
    Shared
    

    View Slide

27. Types of locks
    Unlocked
    Shared
    Reserved
    

    View Slide

28. Types of locks
    Unlocked
    Shared
    Reserved
    Pending
    

    View Slide

29. Types of locks
    Unlocked
    Shared
    Reserved
    Pending
    Exclusive
    

    View Slide

30. Reading from the database
    

    View Slide

31. Writing to the database
    1. Get share lock
    2. Get reserved lock
    3. Write to memory
    4. Write to rollback journal
    

    View Slide

32. The rollback journal
    

    View Slide

33. Writing to the database
    5. Obtain a PENDING lock
    6. Obtain an EXCLUSIVE lock
    7. Wait for SHARED locks to be done
    8. Write to database
    

    View Slide

34. Write-Ahead Log (WAL)
    

    View Slide

35. WAL continued
    

    View Slide

36. Writes with WAL
    1. Append to WAL
    2. COMMIT
    3. Continue writing
    4. Checkpoint- write to file
    

    View Slide

37. Reads with WAL
    1. Check WAL
    2. Check DB
    

    View Slide

38. Database connections
    

    View Slide

39. View Slide

40. SQLite connections
    

    View Slide

41. Android APIs
    

    View Slide

42. Only one write connection and multiple
    reads
    

    View Slide

43. Get a connection to SQLite
    

    View Slide

44. package android.database.sqlite;
    
    public abstract class SQLiteOpenHelper {
    
    public SQLiteDatabase getWritableDatabase() {
    
    synchronized (this) {
    
    return getDatabaseLocked(true);
    
    }
    
    }
    
    public SQLiteDatabase getReadableDatabase() {
    
    synchronized (this) {
    
    return getDatabaseLocked(false);
    
    }
    
    }
    
    }
    
    

    View Slide

45. public final class SQLiteDatabase extends SQLiteClosable {
    
    // The connection pool for the database, null when closed.
    
    // The pool itself is thread-safe, but the reference to it can only be acquired
    
    // when the lock is held.
    
    // INVARIANT: Guarded by mLock.
    
    private SQLiteConnectionPool mConnectionPoolLocked;
    
    }
    
    

    View Slide

46. Where Room sets up WAL
    public abstract class RoomDatabase {
    
    public void init(...) {
    
    boolean wal = false;
    
    if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
    
    wal = configuration.journalMode == JournalMode.WRITE_AHEAD_LOGGING;
    
    mOpenHelper.setWriteAheadLoggingEnabled(wal);
    
    }
    
    ...
    
    }
    
    }
    
    

    View Slide

47. Change SQLite settings
    public final class SQLiteDatabase extends SQLiteClosable {
    
    public boolean enableWriteAheadLogging() {
    ...
    
    mConfigurationLocked.openFlags |= ENABLE_WRITE_AHEAD_LOGGING;
    
    try {
    
    mConnectionPoolLocked.reconfigure(mConfigurationLocked);
    
    } catch (RuntimeException ex) {
    
    mConfigurationLocked.openFlags &= ~ENABLE_WRITE_AHEAD_LOGGING;
    
    throw ex;
    
    }
    
    }
    
    }
    
    

    View Slide

48. public final class SQLiteConnection ... {
    
    private void setWalModeFromConfiguration() {
    
    ...
    
    setJournalMode("WAL");
    
    ...
    
    }
    
    }
    
    }
    
    

    View Slide

49. private void setJournalMode(String newValue) {
    
    String value = executeForString("PRAGMA journal_mode", null, null);
    
    if (!value.equalsIgnoreCase(newValue)) {
    
    try {
    
    String result = executeForString("PRAGMA journal_mode=" + newValue, null, null);
    
    if (result.equalsIgnoreCase(newValue)) {
    
    return;
    
    }
    
    ...
    
    }
    
    

    View Slide

50. Room with threads
    

    View Slide

51. Queries (reads) in Room
    

    View Slide

52. RxJava
    public static Flowable createFlowable(final RoomDatabase database,
    
    final String... tableNames) {
    
    return Flowable.create(new FlowableOnSubscribe() {
    
    @Override
    
    public void subscribe(final FlowableEmitter emitter) throws Exception {
    
    ...
    
    

    View Slide

53. Coroutines
    @JvmStatic
    
    public fun createFlow(
    
    db: RoomDatabase,
    
    inTransaction: Boolean,
    
    tableNames: Array,
    
    callable: Callable
    
    ): Flow<@JvmSuppressWildcards R> = flow {
    
    coroutineScope {
    
    val resultChannel = Channel()
    
    launch(queryContext) {
    
    ...
    
    emitAll(resultChannel)
    
    

    View Slide

54. Writes in Room?
    

    View Slide

55. Transactions
    

    View Slide

56. Transactions in SQLite
    

    View Slide

57. SQLite BEGIN TRANSACTION
    

    View Slide

58. SQLite COMMIT
    

    View Slide

59. Transaction on journal vs WAL
    

    View Slide

60. Transactions / writes with Android API
    

    View Slide

61. Room wraps a lot of things in
    transactions
    

    View Slide

62. Multiple write statements - same transaction
    val preparedQuery = prepareQuery("INSERT...")
    
    beginTransaction
    
    input.forEach {
    
    bind(preparedQuery, it)
    
    }
    
    endTransaction
    
    

    View Slide

63. Triggers
    

    View Slide

64. Triggers in SQLite
    

    View Slide

65. Triggers for RxJava and LiveData (& coroutines
    flows!)
    public static Flowable createFlowable(final RoomDatabase database,
    
    final String... tableNames) {
    
    return Flowable.create(new FlowableOnSubscribe() {
    
    @Override
    
    public void subscribe(final FlowableEmitter emitter) throws Exception {
    
    ...
    
    };
    
    

    View Slide

66. public static Flowable createFlowable(final RoomDatabase database,
    
    final String... tableNames) {
    
    return Flowable.create(new FlowableOnSubscribe() {
    
    @Override
    
    public void subscribe(final FlowableEmitter emitter) throws Exception {
    
    final InvalidationTracker.Observer observer = new InvalidationTracker.Observer(
    
    tableNames) {
    
    @Override
    
    public void onInvalidated(@androidx.annotation.NonNull Set tables) {
    
    if (!emitter.isCancelled()) {
    
    emitter.onNext(NOTHING);
    
    }
    
    }
    
    };
    
    

    View Slide

67. static class WeakObserver extends Observer {
    
    final InvalidationTracker mTracker;
    
    final WeakReference mDelegateRef;
    
    WeakObserver(InvalidationTracker tracker, Observer delegate) {
    
    super(delegate.mTables);
    
    mTracker = tracker;
    
    mDelegateRef = new WeakReference<>(delegate);
    }
    
    @Override
    
    public void onInvalidated(@NonNull Set tables) {
    
    final Observer observer = mDelegateRef.get();
    
    if (observer == null) {
    
    mTracker.removeObserver(this);
    
    } else {
    
    observer.onInvalidated(tables);
    }
    
    }
    
    }
    
    

    View Slide

68. Invalidation Tracking
    public class InvalidationTracker {
    
    static class ObservedTableTracker {
    
    static final int NO_OP = 0; // don't change trigger state for this table
    
    static final int ADD = 1; // add triggers for this table
    
    static final int REMOVE = 2; // remove triggers for this table
    
    

    View Slide

69. public class InvalidationTracker {
    
    void syncTriggers(SupportSQLiteDatabase database) {
    
    try {
    
    for (int tableId = 0; tableId < limit; tableId++) {
    
    switch (tablesToSync[tableId]) {
    
    case ObservedTableTracker.ADD:
    
    startTrackingTable(database, tableId);
    
    break;
    
    case ObservedTableTracker.REMOVE:
    
    stopTrackingTable(database, tableId);
    
    break;
    
    }
    
    }
    
    }
    
    

    View Slide

70. private void startTrackingTable(SupportSQLiteDatabase writableDb, int tableId) {
    
    final String tableName = mTableNames[tableId];
    
    StringBuilder stringBuilder = new StringBuilder();
    
    for (String trigger : TRIGGERS) {
    
    stringBuilder.setLength(0);
    
    stringBuilder.append("CREATE TEMP TRIGGER IF NOT EXISTS ");
    
    appendTriggerName(stringBuilder, tableName, trigger);
    
    stringBuilder.append(" AFTER ")
    
    .append(trigger)
    
    .append(" ON `")
    
    .append(tableName)
    
    .append("` BEGIN INSERT OR REPLACE INTO ")
    
    .append(UPDATE_TABLE_NAME)
    
    .append(" VALUES(null, ")
    
    .append(tableId)
    
    .append("); END");
    
    writableDb.execSQL(stringBuilder.toString());
    
    }
    
    }
    
    

    View Slide

71. private Set checkUpdatedTable() {
    
    HashSet invalidatedTableIds = new HashSet<>();
    
    Cursor cursor = mDatabase.query(new SimpleSQLiteQuery(SELECT_UPDATED_TABLES_SQL));
    
    //noinspection TryFinallyCanBeTryWithResources
    
    try {
    
    while (cursor.moveToNext()) {
    
    final int tableId = cursor.getInt(0);
    
    invalidatedTableIds.add(tableId);
    
    }
    
    } finally {
    
    cursor.close();
    
    }
    
    if (!invalidatedTableIds.isEmpty()) {
    
    mCleanupStatement.executeUpdateDelete();
    
    }
    
    return invalidatedTableIds;
    
    }
    
    

    View Slide

72. public void endTransaction() {
    
    mInvalidationTracker.refreshVersionsAsync();
    
    }
    
    

    View Slide

73. if (invalidatedTableIds != null && !invalidatedTableIds.isEmpty()) {
    
    synchronized (mObserverMap) {
    
    for (Map.Entry entry : mObserverMap) {
    
    entry.getValue().notifyByTableInvalidStatus(invalidatedTableIds);
    
    }
    
    }
    
    }
    
    

    View Slide

74. AFTER INSERT/UPDATE/DELETE ON BEGIN UPDATE
    
    SET invalidated = 1 WHERE tableId = AND invalidated = 0;
    
    END
    
    

    View Slide

75. Transactions with Room
    

    View Slide

76. TransactionExecutor
    

    View Slide

77. class TransactionExecutor implements Executor {
    
    private final Executor mExecutor;
    
    private final ArrayDeque mTasks = new ArrayDeque<>();
    
    private Runnable mActive;
    
    TransactionExecutor(@NonNull Executor executor) {
    
    mExecutor = executor;
    
    }
    
    public synchronized void execute(final Runnable command) {
    
    mTasks.offer(new Runnable() {
    
    public void run() {
    
    try {
    
    command.run();
    
    } finally {
    
    scheduleNext();
    
    }
    
    }
    
    });
    
    if (mActive == null) {
    
    scheduleNext();
    
    }
    
    }
    
    @SuppressWarnings("WeakerAccess")
    
    synchronized void scheduleNext() {
    
    if ((mActive = mTasks.poll()) != null) {
    
    mExecutor.execute(mActive);
    
    }
    
    }
    
    }
    
    

    View Slide

78. In RxJava
    public class RxRoom {
    
    private static Executor getExecutor(RoomDatabase database, boolean inTransaction) {
    
    if (inTransaction) {
    
    return database.getTransactionExecutor();
    
    } else {
    
    return database.getQueryExecutor();
    
    }
    
    }
    
    }
    
    

    View Slide

79. In Coroutines
    class CoroutinesRoom private constructor() {
    
    companion object {
    
    @JvmStatic
    
    suspend fun execute(
    
    db: RoomDatabase,
    
    inTransaction: Boolean,
    
    callable: Callable
    
    ): R {
    
    if (db.isOpen && db.inTransaction()) {
    
    return callable.call()
    
    }
    
    // Use the transaction dispatcher if we are on a transaction coroutine, otherwise
    
    // use the database dispatchers.
    
    val context = coroutineContext[TransactionElement]?.transactionDispatcher
    
    ?: if (inTransaction) db.transactionDispatcher else db.queryDispatcher
    
    return withContext(context) {
    
    callable.call()
    
    }
    
    }
    
    }
    
    

    View Slide

80. Transaction APIs in Room
    public void runInTransaction(@NonNull Runnable body) {
    
    beginTransaction();
    
    try {
    
    body.run();
    
    setTransactionSuccessful();
    
    } finally {
    
    endTransaction();
    
    }
    
    }
    
    

    View Slide

81. What can developers Do?
    

    View Slide

82. Conclusions
    

    View Slide

83. Large queries and paging in Room
    

    View Slide

84. Cursor - Android API
    package android.database.sqlite;
    
    public class SQLiteCursor extends AbstractWindowedCursor {
    
    private void fillWindow(int requiredPos) {
    
    ...
    
    }
    
    }
    
    

    View Slide

85. So large queries are...
    

    View Slide

86. Why is this a problem?
    1. SQLiteCursor isn't in a transaction
    

    View Slide

87. Why is this a problem?
    1. SQLiteCursor isn't in a transaction
    2. SQLiteCursor cursorPickFillWindowStartPosition
    

    View Slide

88. public static int cursorPickFillWindowStartPosition(
    
    int cursorPosition, int cursorWindowCapacity) {
    
    return Math.max(cursorPosition - cursorWindowCapacity / 3, 0);
    
    }
    
    

    View Slide

89. Solution #1: @Transaction
    https://medium.com/androiddevelopers/7-pro-tips-for-room-
    fbadea4bfbd1
    

    View Slide

90. Solution #2: Write smaller queries!
    

    View Slide

91. Solution #3: Integrate with paging library
    package androidx.room.paging;
    
    public abstract class LimitOffsetDataSource extends PositionalDataSource {
    
    public List loadRange(int startPosition, int loadCount) {
    
    final RoomSQLiteQuery sqLiteQuery = getSQLiteQuery(startPosition, loadCount);
    
    if (mInTransaction) {
    
    mDb.beginTransaction();
    
    Cursor cursor = null;
    
    //noinspection TryFinallyCanBeTryWithResources
    
    try {
    
    cursor = mDb.query(sqLiteQuery);
    
    List rows = convertRows(cursor);
    
    mDb.setTransactionSuccessful();
    return rows;
    
    } finally {
    
    if (cursor != null) {
    
    cursor.close();
    
    }
    
    mDb.endTransaction();
    
    sqLiteQuery.release();
    
    }
    
    } else {
    
    Cursor cursor = mDb.query(sqLiteQuery);
    
    //noinspection TryFinallyCanBeTryWithResources
    
    try {
    
    return convertRows(cursor);
    
    } finally {
    
    cursor.close();
    
    sqLiteQuery.release();
    
    }
    
    }
    
    }
    
    

    View Slide

92. mLimitOffsetQuery = "SELECT * FROM ( " + mSourceQuery.getSql() + " ) LIMIT ? OFFSET ?";
    
    

    View Slide

93. Paging + LiveData + Room support
    LiveData> users = new LivePagedListBuilder<>(
    
    userDao.loadUsersByAgeDesc(), /*page size*/ 20).build();
    
    https://github.com/googlesamples/android-architecture-
    components/tree/master/PagingSample
    

    View Slide

94. Thanks!
    Effie Barak (@CodingChick)
    

    View Slide