Inside the room

Inside the room

Bcc14b45a86f42cd22d9102a96bc8a5c?s=128

Effie Barak

April 25, 2019
Tweet

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. Intro to SQLite

  5. Embedded SQL database engine

  6. None
  7. Intro to Android and Android x APIs

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

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

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

  11. Intro to Room Architecture

  12. Isolated modules 4 room-common 4 room-compiler 4 room-runtime 4 room-ktx

    4 room-rxjava2
  13. Threads, processes etc

  14. Locking in SQLite

  15. Types of locks

  16. Types of locks 4 Unlocked

  17. Types of locks 4 Unlocked 4 Shared

  18. Types of locks 4 Unlocked 4 Shared 4 Reserved

  19. Types of locks 4 Unlocked 4 Shared 4 Reserved 4

    Pending
  20. Types of locks 4 Unlocked 4 Shared 4 Reserved 4

    Pending 4 Exclusive
  21. Reading from the database

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

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

  24. Writing to the database 1. Obtain a PENDING lock 2.

    Obtain an EXCLUSIVE lock 3. Wait for SHARED lockes to be done 4. Write to database
  25. Write-Ahead Log (WAL)

  26. WAL continued

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

    Continue writing 4. Checkpoint- write to file
  28. Reads with WAL 1. Check WAL 2. Check DB

  29. Database connections

  30. SQLite connections

  31. Android APIs

  32. Only one write connection and multiple reads

  33. Get a connection to SQLite

  34. package android.database.sqlite; public abstract class SQLiteOpenHelper { public SQLiteDatabase getWritableDatabase()

    { synchronized (this) { return getDatabaseLocked(true); } } public SQLiteDatabase getReadableDatabase() { synchronized (this) { return getDatabaseLocked(false); } } }
  35. 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; }
  36. 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; } } }
  37. public final class SQLiteConnection ... { private void setWalModeFromConfiguration() {

    ... setJournalMode("WAL"); ... } } }
  38. 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; } // PRAGMA journal_mode silently fails and returns the original journal // mode in some cases if the journal mode could not be changed. } catch (SQLiteDatabaseLockedException ex) { // This error (SQLITE_BUSY) occurs if one connection has the database // open in WAL mode and another tries to change it to non-WAL. } ... }
  39. Where Room sets up WAL public abstract class RoomDatabase {

    public void init(@NonNull DatabaseConfiguration configuration) { mOpenHelper = createOpenHelper(configuration); boolean wal = false; if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) { wal = configuration.journalMode == JournalMode.WRITE_AHEAD_LOGGING; mOpenHelper.setWriteAheadLoggingEnabled(wal); } ... } }
  40. Room with threads

  41. Queries (reads) in Room

  42. Writes in Room?

  43. Transactions

  44. Transactions in SQLite

  45. SQLite BEGIN TRANSACTION

  46. SQLite COMMIT

  47. Transaction on journal vs WAL

  48. Transactions / writes with Android API

  49. Room wraps a lot of things in transactions

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

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

  52. Triggers in SQLite

  53. Triggers for RxJava and LiveData class RoomTrackingLiveData<T> extends LiveData<T> {

    final Runnable mInvalidationRunnable = new Runnable() { ... public void run() { boolean isActive = hasActiveObservers(); if (mInvalid.compareAndSet(false, true)) { if (isActive) { mDatabase.getQueryExecutor().execute(mRefreshRunnable); } } } }; }
  54. final Runnable mRefreshRunnable = new Runnable() { @WorkerThread @Override public

    void run() { if (mRegisteredObserver.compareAndSet(false, true)) { mDatabase.getInvalidationTracker().addWeakObserver(mObserver); }
  55. Invalidation Tracking public class InvalidationTracker { @RestrictTo(RestrictTo.Scope.LIBRARY_GROUP) public void addWeakObserver(Observer

    observer) { addObserver(new WeakObserver(this, observer)); } }
  56. public class InvalidationTracker { void syncTriggers(SupportSQLiteDatabase database) { } 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()); } }
  57. private boolean checkUpdatedTable() { boolean hasUpdatedTable = false; Cursor cursor

    = mDatabase.query(new SimpleSQLiteQuery(SELECT_UPDATED_TABLES_SQL)); //noinspection TryFinallyCanBeTryWithResources try { while (cursor.moveToNext()) { final int tableId = cursor.getInt(0); mTableInvalidStatus.set(tableId); hasUpdatedTable = true; } } finally { cursor.close(); } if (hasUpdatedTable) { mCleanupStatement.executeUpdateDelete(); } return hasUpdatedTable; }
  58. public void endTransaction() { mOpenHelper.getWritableDatabase().endTransaction(); if (!inTransaction()) { // enqueue

    refresh only if we are NOT in a transaction. Otherwise, wait for the last // endTransaction call to do it. mInvalidationTracker.refreshVersionsAsync(); } }
  59. AFTER INSERT/UPDATE/DELETE ON <table> BEGIN UPDATE SET invalidated = 1

    WHERE tableId = <id> AND invalidated = 0; END
  60. Transactions with Room

  61. TransactionExecutor

  62. 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); } } }
  63. In RxJava public class RxRoom { @RestrictTo(RestrictTo.Scope.LIBRARY_GROUP_PREFIX) public static <T>

    Observable<T> createObservable(final RoomDatabase database, final boolean inTransaction, final String[] tableNames, final Callable<T> callable) { Scheduler scheduler = Schedulers.from(getExecutor(database, inTransaction)); final Maybe<T> maybe = Maybe.fromCallable(callable); return createObservable(database, tableNames) .subscribeOn(scheduler) .unsubscribeOn(scheduler) .observeOn(scheduler) .flatMapMaybe(new Function<Object, MaybeSource<T>>() { @Override public MaybeSource<T> apply(Object o) throws Exception { return maybe; } }); } private static Executor getExecutor(RoomDatabase database, boolean inTransaction) { if (inTransaction) { return database.getTransactionExecutor(); } else { return database.getQueryExecutor(); } } }
  64. 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() } } } }
  65. Transaction APIs in Room public abstract class RoomDatabase { @Deprecated

    public void beginTransaction() { assertNotMainThread(); SupportSQLiteDatabase database = mOpenHelper.getWritableDatabase(); mInvalidationTracker.syncTriggers(database); database.beginTransaction(); } /** * Wrapper for {@link SupportSQLiteDatabase#endTransaction()}. * * @deprecated Use {@link #runInTransaction(Runnable)} */ @Deprecated public void endTransaction() { mOpenHelper.getWritableDatabase().endTransaction(); if (!inTransaction()) { // enqueue refresh only if we are NOT in a transaction. Otherwise, wait for the last // endTransaction call to do it. mInvalidationTracker.refreshVersionsAsync(); } } }
  66. public abstract class RoomDatabase { public void runInTransaction(@NonNull Runnable body)

    { beginTransaction(); try { body.run(); setTransactionSuccessful(); } finally { endTransaction(); } } }
  67. What can developers Do?

  68. Conclusions

  69. Large queries and paging in Room

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

    AbstractWindowedCursor { private void fillWindow(int requiredPos) { ... } }
  71. So large queries are...

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

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

    transaction 2. SQLiteCursor cursorPickFillWindowStartPosition
  74. /** * Picks a start position for {@link Cursor#fillWindow} such

    that the * window will contain the requested row and a useful range of rows * around it. * * When the data set is too large to fit in a cursor window, seeking the * cursor can become a very expensive operation since we have to run the * query again when we move outside the bounds of the current window. * * We try to choose a start position for the cursor window such that * 1/3 of the window's capacity is used to hold rows before the requested * position and 2/3 of the window's capacity is used to hold rows after the * requested position. * * @param cursorPosition The row index of the row we want to get. * @param cursorWindowCapacity The estimated number of rows that can fit in * a cursor window, or 0 if unknown. * @return The recommended start position, always less than or equal to * the requested row. * @hide */ public static int cursorPickFillWindowStartPosition( int cursorPosition, int cursorWindowCapacity) { return Math.max(cursorPosition - cursorWindowCapacity / 3, 0); }
  75. Solution #1: @Transaction https://medium.com/androiddevelopers/7-pro-tips- for-room-fbadea4bfbd1

  76. Solution #2: Write smaller queries!

  77. 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(); } } } }
  78. mLimitOffsetQuery = "SELECT * FROM ( " + mSourceQuery.getSql() +

    " ) LIMIT ? OFFSET ?";
  79. 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
  80. Thanks! Effie Barak (@CodingChick)