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

Inside The Room- DCBln21 revised edition

Inside The Room- DCBln21 revised edition

Effie Barak

October 25, 2021
Tweet

More Decks by Effie Barak

Other Decks in Programming

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