Inside the room Effie Barak (@CodingChick) 

No content

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

Intro to SQLite 

Embedded SQL database engine 

No content

No content

No content

No content

No content

Intro to Android and Android x APIs 

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

Android X androidx.sqlite.db SupportSQLiteDatabase SupportSQLiteOpenHelper SupportSQLiteQuery 

/** * 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 { 

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

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

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

Intro to Room Architecture 

Isolated modules 4 room-common 4 room-compiler 4 room-runtime 4 room-ktx 4 room-rxjava2 

Threads, processes etc 

Locking in SQLite 

No content

Types of locks 

Types of locks 4 Unlocked 

Types of locks 4 Unlocked 4 Shared 

Types of locks 4 Unlocked 4 Shared 4 Reserved 

Types of locks 4 Unlocked 4 Shared 4 Reserved 4 Pending 

Types of locks 4 Unlocked 4 Shared 4 Reserved 4 Pending 4 Exclusive 

Reading from the database 

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

The rollback journal 

Writing to the database 1. Obtain a PENDING lock 2. Obtain an EXCLUSIVE lock 3. Wait for SHARED locks to be done 4. Write to database 

Write-Ahead Log (WAL) 

WAL continued 

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

Reads with WAL 1. Check WAL 2. Check DB 

Database connections 

No content

SQLite connections 

Android APIs 

Only one write connection and multiple reads 

Get a connection to SQLite 

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

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

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

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

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

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

Room with threads 

Queries (reads) in Room 

Writes in Room? 

Transactions 

Transactions in SQLite 

SQLite BEGIN TRANSACTION 

SQLite COMMIT 

Transaction on journal vs WAL 

Transactions / writes with Android API 

Room wraps a lot of things in transactions 

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

Triggers 

Triggers in SQLite 

Triggers for RxJava and LiveData class RoomTrackingLiveData extends LiveData { final Runnable mInvalidationRunnable = new Runnable() { ... public void run() { boolean isActive = hasActiveObservers(); if (mInvalid.compareAndSet(false, true)) { if (isActive) { mDatabase.getQueryExecutor().execute(mRefreshRunnable); } } } }; } 

class RoomTrackingLiveData extends LiveData { final Runnable mRefreshRunnable = new Runnable() { public void run() { ... mDatabase.getInvalidationTracker().addWeakObserver(mObserver); } 

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 

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

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

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

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

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

Transactions with Room 

TransactionExecutor 

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

In RxJava public class RxRoom { @RestrictTo(RestrictTo.Scope.LIBRARY_GROUP_PREFIX) public static Observable createObservable(final RoomDatabase database, final boolean inTransaction, final String[] tableNames, final Callable callable) { Scheduler scheduler = Schedulers.from(getExecutor(database, inTransaction)); final Maybe maybe = Maybe.fromCallable(callable); return createObservable(database, tableNames) .subscribeOn(scheduler) .unsubscribeOn(scheduler) .observeOn(scheduler) .flatMapMaybe(new Function>() { @Override public MaybeSource apply(Object o) throws Exception { return maybe; } }); } private static Executor getExecutor(RoomDatabase database, boolean inTransaction) { if (inTransaction) { return database.getTransactionExecutor(); } else { return database.getQueryExecutor(); } } } 

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

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

public abstract class RoomDatabase { public void runInTransaction(@NonNull Runnable body) { beginTransaction(); try { body.run(); setTransactionSuccessful(); } finally { endTransaction(); } } } 

What can developers Do? 

Conclusions 

Large queries and paging in Room 

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

So large queries are... 

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

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

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

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

Solution #2: Write smaller queries! 

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

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

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

Thanks! Effie Barak (@CodingChick)