Inside The Room- DCBln21 revised edition

Transcript

1. Inside the room Effie Barak (@CodingChick)

2. None

3. Talk Outline: 1. Intro 2. Threading in Room 3. Triggers

   4. Transactions 5. Large queries
4. None

5. Intro to SQLite

6. Embedded SQL database engine

7. None
8. None
9. None
10. None
11. None

12. Intro to Android and Android x APIs

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

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

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 {

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

17. /** * Delegates all calls to an implementation of {@link

    SQLiteDatabase}. */ class FrameworkSQLiteDatabase implements SupportSQLiteDatabase {

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

    } @Override public void beginTransactionNonExclusive() { mDelegate.beginTransactionNonExclusive(); }

19. Intro to Room Architecture

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

21. Threads, processes etc

22. Locking in SQLite

23. None

24. Types of locks

25. Types of locks Unlocked

26. Types of locks Unlocked Shared

27. Types of locks Unlocked Shared Reserved

28. Types of locks Unlocked Shared Reserved Pending

29. Types of locks Unlocked Shared Reserved Pending Exclusive

30. Reading from the database

31. Writing to the database 1. Get share lock 2. Get

    reserved lock 3. Write to memory 4. Write to rollback journal

32. The rollback journal

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

34. Write-Ahead Log (WAL)

35. WAL continued

36. Writes with WAL 1. Append to WAL 2. COMMIT 3.

    Continue writing 4. Checkpoint- write to file

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

38. Database connections

39. None

40. SQLite connections

41. Android APIs

42. Only one write connection and multiple reads

43. Get a connection to SQLite

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); } } }

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; }

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); } ... } }

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; } } }

48. public final class SQLiteConnection ... { private void setWalModeFromConfiguration() {

    ... setJournalMode("WAL"); ... } } }

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; } ... }

50. Room with threads

51. Queries (reads) in Room

52. RxJava public static Flowable<Object> createFlowable(final RoomDatabase database, final String... tableNames)

    { return Flowable.create(new FlowableOnSubscribe<Object>() { @Override public void subscribe(final FlowableEmitter<Object> emitter) throws Exception { ...

53. Coroutines @JvmStatic public fun <R> createFlow( db: RoomDatabase, inTransaction: Boolean,

    tableNames: Array<String>, callable: Callable<R> ): Flow<@JvmSuppressWildcards R> = flow { coroutineScope { val resultChannel = Channel<R>() launch(queryContext) { ... emitAll(resultChannel)

54. Writes in Room?

55. Transactions

56. Transactions in SQLite

57. SQLite BEGIN TRANSACTION

58. SQLite COMMIT

59. Transaction on journal vs WAL

60. Transactions / writes with Android API

61. Room wraps a lot of things in transactions

62. Multiple write statements - same transaction val preparedQuery = prepareQuery("INSERT...")

    beginTransaction input.forEach { bind(preparedQuery, it) } endTransaction

63. Triggers

64. Triggers in SQLite

65. Triggers for RxJava and LiveData (& coroutines flows!) public static

    Flowable<Object> createFlowable(final RoomDatabase database, final String... tableNames) { return Flowable.create(new FlowableOnSubscribe<Object>() { @Override public void subscribe(final FlowableEmitter<Object> emitter) throws Exception { ... };

66. public static Flowable<Object> createFlowable(final RoomDatabase database, final String... tableNames) {

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

67. static class WeakObserver extends Observer { final InvalidationTracker mTracker; final

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

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

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; } } }

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()); } }

71. private Set<Integer> checkUpdatedTable() { HashSet<Integer> 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; }

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

73. if (invalidatedTableIds != null && !invalidatedTableIds.isEmpty()) { synchronized (mObserverMap) {

    for (Map.Entry<Observer, ObserverWrapper> entry : mObserverMap) { entry.getValue().notifyByTableInvalidStatus(invalidatedTableIds); } } }

74. AFTER INSERT/UPDATE/DELETE ON <table> BEGIN UPDATE SET invalidated = 1

    WHERE tableId = <id> AND invalidated = 0; END

75. Transactions with Room

76. TransactionExecutor

77. class TransactionExecutor implements Executor { private final Executor mExecutor; private

    final ArrayDeque<Runnable> 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); } } }

78. In RxJava public class RxRoom { private static Executor getExecutor(RoomDatabase

    database, boolean inTransaction) { if (inTransaction) { return database.getTransactionExecutor(); } else { return database.getQueryExecutor(); } } }

79. In Coroutines class CoroutinesRoom private constructor() { companion object {

    @JvmStatic suspend fun <R> execute( db: RoomDatabase, inTransaction: Boolean, callable: Callable<R> ): 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() } } }

80. Transaction APIs in Room public void runInTransaction(@NonNull Runnable body) {

    beginTransaction(); try { body.run(); setTransactionSuccessful(); } finally { endTransaction(); } }

81. What can developers Do?

82. Conclusions

83. Large queries and paging in Room

84. Cursor - Android API package android.database.sqlite; public class SQLiteCursor extends

    AbstractWindowedCursor { private void fillWindow(int requiredPos) { ... } }

85. So large queries are...

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

    transaction

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

    transaction 2. SQLiteCursor cursorPickFillWindowStartPosition

88. public static int cursorPickFillWindowStartPosition( int cursorPosition, int cursorWindowCapacity) { return

    Math.max(cursorPosition - cursorWindowCapacity / 3, 0); }

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

90. Solution #2: Write smaller queries!

91. Solution #3: Integrate with paging library package androidx.room.paging; public abstract

    class LimitOffsetDataSource<T> extends PositionalDataSource<T> { public List<T> 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<T> 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(); } } }

92. mLimitOffsetQuery = "SELECT * FROM ( " + mSourceQuery.getSql() +

    " ) LIMIT ? OFFSET ?";

93. Paging + LiveData + Room support LiveData<PagedList<User>> users = new

    LivePagedListBuilder<>( userDao.loadUsersByAgeDesc(), /*page size*/ 20).build(); https://github.com/googlesamples/android-architecture- components/tree/master/PagingSample

94. Thanks! Effie Barak (@CodingChick)