Advanced Room

# Advanced Room

## GDG DevFest Tokyo 2018

2018/09/01

荒木佑一

---

# Room とは

![Jetpack](jetpack-hero.svg)

Android の SQLite データベースを使いやすくするライブラリ

アノテーション プロセッサーを利用することで冗長な処理を自動生成

SQL をビルド時に検証

LiveData によるテーブルの監視

マイグレーション

RxJava サポート

---

# Entity

```kotlin
@Entity
data class Cheese(
    @PrimaryKey
    val id: Long,
    val name: String,
    val favorite: Boolean)
```

Room が自動的に対応するスキーマのテーブルを作成

```sql
CREATE TABLE IF NOT EXISTS Cheese (
  id INTEGER NOT NULL,
  name TEXT NOT NULL,
  favorite INTEGER NOT NULL,
  PRIMARY KEY(id))
```

---

# DAO

SQL と Java/Kotlin の世界の橋渡し

```kotlin
@Dao
interface CheeseDao {
 @Query("SELECT * FROM Cheese")
 fun all(): LiveData<List<Cheese>>

@Query("SELECT * FROM Cheese WHERE id = :id")
 fun byId(id: Long): LiveData<Cheese?>

@Query("SELECT COUNT(*) FROM Cheese")
    fun count(): Int

@Insert
 fun insertAll(cheeses: List<Cheese>)
}
```

---

# Room の基本を学ぶには

Room Persistence Library 
[http://d.android.com/topic/libraries/architecture/room](https://developer.android.com/topic/libraries/architecture/room)

7 Steps To Room 
[https://medium.com/androiddevelopers/7-steps-to-room-27a5fe5f99b2](https://medium.com/androiddevelopers/7-steps-to-room-27a5fe5f99b2)

Room - Architecture Components 勉強会 #4 
[http://yaraki.github.io/slides/aac-room/index.html](http://yaraki.github.io/slides/aac-room/index.html)

---

# 書き込み

```kotlin
@Entity
data class Author(@PrimaryKey val id: Long, val name: String)
```

```kotlin
@Dao
interface AuthorDao {
    @Insert
    fun insert(author: Author)
}
```

```kotlin
@Database(entities = [Author::class], version = 1)
abstract class LiteratureDatabase : RoomDatabase() {
    abstract fun author(): AuthorDao
}
```

```kotlin
db.author().insert(Author(1L, "芥川龍之介"))
```

---

# 複数 書き込み

配列で

```kotlin
 @Insert
 fun insertArray(authors: Array<Author>)

@Insert
    fun insertVararg(vararg authors: Author)
```

リスト、セットで

```kotlin
 @Insert
 fun insertList(authors: List<Author>)

@Insert
 fun insertSet(authors: Set<Author>)
```

???

- どんなコレクションが使える？

---

# 複数 書き込み

`Iterable` なコレクションであれば使える

```kotlin
 @Insert
 fun insertAll(authors: Iterable<Author>)
```

@Update および @Delete でも同様

自動的にトランザクションで囲われる 
= 主キーが重複すればエラーでロールバック

複数種類でも大丈夫

```kotlin
    @Insert
    fun insertAuthorAndBook(author: Author, book: Book)
```

---

# 上書き

主キーが重複するときは上書き

```kotlin
 @Insert(onConflict = OnConflictStrategy.REPLACE)
 fun insertAll(authors: List<Author>)
```

```kotlin
    db.author().insertAll(listOf(
            Author(1L, "夏目"),
            Author(2L, "森鴎外")))
    // db には 2 件

db.author().insertAll(listOf(
            Author(1L, "夏目漱石"),
            Author(3L, "芥川龍之介")))
    // db には 3 件
```

---

# 更新、削除

```kotlin
    @Update
    fun update(author: Author)

@Delete
    fun delete(author: Author)
```

引数の配列やリストの扱いは @Insert と同様

@Query でも UPDATE/DELETE はできる

```kotlin
    @Query("UPDATE Author SET name = :name WHERE id = :id")
    fun rename(id: Long, name: String)

@Query("DELETE FROM Author WHERE id = :id")
    fun delete(id: Long)
```

---

# @Query で複数削除

```kotlin
 @Query("DELETE FROM Author WHERE id IN (:ids)")
 fun deleteAll(ids: List<Long>)
```

Room が自動的に複数パラメーターを展開

ただし 999 個まで

---

# 外部キー制約

```kotlin
@Entity
data class Author(@PrimaryKey val id: Long, val name: String)

@Entity(
        foreignKeys = [
            ForeignKey(
                    entity = Author::class,
                    childColumns = ["authorId"],
                    parentColumns = ["id"])],
        indices = [Index("authorId")])
data class Book(
        @PrimaryKey
        val id: Long,
        val title: String,
        val authorId: Long
)
```

---

# 外部キー制約エラー

```kotlin
    db.author().insertAll(listOf(
            Author(id = 1L, name = "芥川")))

db.book().insertAll(listOf(
            Book(id = 1L, title = "芋粥", authorId = 1L),
            Book(id = 2L, title = "お伽草紙", authorId = 2L)))
```

SQLiteConstraintException で トランザクションはロールバックされる

順番を守って挿入しないといけない

---

# 外部キー制約エラー？

```kotlin
    db.runInTransaction {

db.book().insertAll(listOf(
                Book(id = 1L, title = "芋粥", authorId = 1L),
                Book(id = 2L, title = "お伽草紙", authorId = 2L)))

db.author().insertAll(listOf(
                Author(id = 1L, name = "芥川"),
                Author(id = 2L, name = "太宰")))

}
```

---

# 外部キー制約を一時的に保留

```kotlin
    val sqlite = db.openHelper.writableDatabase

db.runInTransaction {

`sqlite.execSQL("PRAGMA defer_foreign_keys = TRUE")`

db.book().insertAll(listOf(
                Book(id = 1L, title = "芋粥", authorId = 1L),
                Book(id = 2L, title = "お伽草紙", authorId = 2L)))

db.author().insertAll(listOf(
                Author(id = 1L, name = "芥川"),
                Author(id = 2L, name = "太宰")))

}
```

???

- ただし defer_foreign_keys は Lollipop 以降

---

# 抽象クラス @Dao の活用

インターフェイス

```kotlin
@Dao
interface BookDao {
    @Insert
    fun insert(book: Book)
}
```

抽象クラス

```kotlin
@Dao
abstract class AuthorDao {
    @Insert
    abstract fun insert(author: Author)
}
```

具象メソッドを作ることができる

---

# 具象メソッドの活用

```kotlin
@Entity
data class Author(
        @PrimaryKey
        val id: Long,
        val name: String,
        val money: Long
)
```

```
    db.author().insertAll(listOf(
            Author(1L, "井伏", money = 1000L),
            Author(2L, "太宰", money = 0L))

db.author().sendMoney(1L, 2L, 200L)
```

---

# 他のメソッドを組み合わせる

```kotlin
    @Query("UPDATE Author SET money = money + :delta WHERE id = :id")
    abstract fun adjustMoney(id: Long, delta: Long): Int

@Transaction
    open fun sendMoney(from: Long, to: Long, amount: Long) {
        val decrease = adjustMoney(from, -amount)
        if (decrease != 1)
            throw IllegalArgumentException("No author: $from")

val increase = adjustMoney(to, amount)
        if (increase != 1)
            throw IllegalArgumentException("No author: $to")
    }
```

???

- @Transaction: sendMoney をトランザクションで囲った実装が生成される
  - そのために open が必要

---

# 必要な情報だけ取ってくる

```kotlin
@Entity
data class Author(
        @PrimaryKey
        val id: Long,
        val name: String,
        val dateOfBirth: Date,
        val dateOfDeath: Date,
        val placeOfBirth: String,
        val description: String,
        /* まだまだ続く */
)
```

```kotlin
 @Query("SELECT * FROM Author")
 fun liveAll(): LiveData<List<Author>>
```

一覧画面で必要なのは ID と名前だけ

---

# POJO

```kotlin
// @Entity ではない
data class AuthorSummary(
        val id: Long,
        val name: String
)
```

```kotlin
 @Query("SELECT id, name FROM Author")
 fun liveAll(): LiveData<List<`AuthorSummary`>>
```

テーブル監視も正しく動く

カラムの名前と型が一致していれば、返す値は Entity でなくて良い

---

# 1:N の N 側から

```kotlin
data class BookWithAuthorName(
        val id: Long,
        val title: String,
        val authorId: Long,
        val authorName: String
)
```

```kotlin
 @Query("""
 SELECT
 Book.id
 , Book.title
 , Book.authorId
 , Author.name AS authorName
 FROM Book
 INNER JOIN Author ON Book.authorId = Author.id
 """)
 fun liveAllWithAuthor(): LiveData<List<BookWithAuthorName>>
```

---

# 1:N の 1 側から

```kotlin
data class AuthorWithBooks(
 @Embedded
 val author: Author) {
 @Relation(entity = Book::class,
 parentColumn = "id",
 entityColumn = "authorId")
 lateinit var books: List<Book>
}
```

```kotlin
    @Transaction
    @Query("SELECT * FROM Author WHERE id = :id")
    fun byIdWithBooks(id: Long): AuthorWithBooks?
```

以下の SQL が @Relation の中に入る

```sql
SELECT * FROM Book WHERE authorId IN (:id)
```

---

# @Relation

```kotlin
data class AuthorWithBooks(
 @Embedded
 val author: Author) {
 @Relation(entity = Book::class,
 parentColumn = "id",
 entityColumn = "authorId")
 `lateinit var` books: List<Book>
}
```

```kotlin
data class AuthorWithBooks(
 @Embedded
 val author: Author,
 @Relation(entity = Book::class,
 parentColumn = "id",
 entityColumn = "authorId")
 val books: List<Book>
)
```

???

- 現在は @Relation の付くフィールドは var でないといけない
- バージョン 2.1 から val でも大丈夫になる

---

# テスト

```kotlin
@RunWith(AndroidJUnit4::class)
@SmallTest
class AuthorDaoTest {
    private lateinit var db: LiteratureDatabase

@Before
    fun openDatabase() {
        db = Room.`inMemoryDatabaseBuilder`(
                        InstrumentationRegistry.getTargetContext()
                        LiteratureDatabase::class.java)
                 .build()
    }

@Test
    fun author() {
        db.author().insert(Author(1L, "芥川"))
        assertThat(db.author().byId(1L)!!, \`is`(equalTo(Author(1L, "芥川"))))
    }
}
```

---

# LiveData のテスト

```kotlin
    @Test
    fun liveAuthor() {
        db.author().insert(Author(1L, "芥川"))
        assertThat(db.author().`liveById(1L).value`,
                   \`is`(equalTo(Author(1L, "芥川"))))
    }
```

→ null

どうにかして待ち合わせる必要がある

---

# InstantTaskExecutorRule

```gradle
    androidTestImplementation 'android.arch.core:core-testing:1.1.1'
```

```kotlin
class AuthorDaoTest {
 `@get:Rule val executorRule = InstantTaskExecutorRule()`
 
 @Test
 fun liveAuthor() {
 db.author().insert(Author(1L, "芥川"))
 val liveData = db.author().liveById(1L)
 liveData.observeForever(object : Observer<Author> {
 override fun onChanged(t: Author?) {
 assertThat(t, \`is`(equalTo(Author(1L, "芥川"))))
 liveData.removeObserver(this)
 }
 })
 }
}
```

```kotlin
    db = Room.inMemoryDatabaseBuilder(context, LiteratureDatabase::class.java)
        .`allowMainThreadQueries`().build()
```

---

# Mockito

```kotlin
    @Test
    fun testLiveData() {
        db.author().insert(Author(1L, "芥川"))

@Suppress("UNCHECKED_CAST")
 val observer = mock(Observer::class.java) as Observer<Author>

val liveData = db.author().liveById(1L)
        InstrumentationRegistry.getInstrumentation().runOnMainSync {
            liveData.observeForever(observer)
        }

*       verify(observer, timeout(300L).atLeastOnce())
*           .onChanged(eq(Author(1L, "芥川")))

InstrumentationRegistry.getInstrumentation().runOnMainSync {
            liveData.removeObserver(observer)
        }
    }
```

???

- ちょっと遅い

---

# CountDownLatch

```kotlin
    @Test
    fun testLiveData() {
        db.author().insert(Author(1L, "芥川"))
        assertThat(db.author().liveById(1L).`await`(),
                   \`is`(equalTo(Author(1L, "芥川"))))
    }

private fun <T> LiveData<T>.await(): T {
 val latch = CountDownLatch(1)
 val result = AtomicReference<T>()
 InstrumentationRegistry.getInstrumentation().runOnMainSync {
 observeForever(object : Observer<T> {
 override fun onChanged(t: T?) {
 if (t != null) {
 result.set(t)
 removeObserver(this)
 } } })
 }
 latch.await(300L, TimeUnit.MILLISECONDS)
 return result.get()
 }
```

---

# ツリー構造

![ツリー](tree.svg)

同一のエンティティーがツリー構造をなす

- 従業員の組織構造
- 掲示板の返信
- TODO 依存関係

---

# テーブルとして表現

```kotlin
@Entity(
        foreignKeys = [
            ForeignKey(
                    entity = Employee::class,
                    childColumns = ["managerId"],
                    parentColumns = ["id"])
        ],
        indices = [Index("managerId")]
)
data class Employee(
        @PrimaryKey
        val id: Long,
        val name: String,
        val managerId: Long?
)
```

---

# テーブルとして表現

![ツリー](tree.svg)

```kotlin
    db.employee().insertAll(listOf(
            Employee(1L, "A", null),
            Employee(2L, "B", 1L),
            Employee(5L, "E", 2L),
            Employee(4L, "D", 2L),
            Employee(3L, "C", 1L),
            Employee(6L, "F", 2L),
            Employee(7L, "G", 3L),
            Employee(8L, "H", 3L),
            Employee(9L, "I", 4L),
            Employee(10L, "J", 7L),
            Employee(11L, "K", 7L),
            Employee(12L, "L", 10L),
            Employee(13L, "M", 10L)
    ))
```

---

# 再帰的なクエリー 1

![ツリー](tree-chain.svg)

```kotlin
    // id: 10L = "J"
    val chain = db.employee().chain(10L)

// J, G, C, A のリスト
```

---

# 再帰的なクエリー 1

```kotlin
 @Query("""
 WITH RECURSIVE r AS (
 SELECT * FROM Employee WHERE id = :id
 UNION ALL
 SELECT Employee.* FROM Employee, r WHERE Employee.id = r.managerId
 )
 SELECT * FROM r
 """)
 fun chain(id: Long): List<Employee>
```

---

# 再帰的なクエリー 2

![ツリー](tree-team.svg)

```kotlin
    // id: 7L = "G"
    val team = db.employee().team(7L)

// G, J, K, L, M のリスト
```

---

# 再帰的なクエリー 2

```kotlin
 @Query("""
 WITH RECURSIVE r AS (
 SELECT * FROM Employee WHERE id = :id
 UNION ALL
 SELECT Employee.* FROM Employee, r WHERE Employee.managerId = r.id
 )
 SELECT * FROM r
 """)
 fun team(id: Long): List<Employee>
```

---

# Fizz Buzz

```kotlin
 @Query("""
 WITH RECURSIVE sequence1(x) AS (
 SELECT 1
 UNION ALL
 SELECT x + 1 FROM sequence1
 LIMIT :max
 )
 SELECT
 CASE
 WHEN x % 15 = 0 THEN 'fizzbuzz'
 WHEN x % 3 = 0 THEN 'fizz'
 WHEN x % 5 = 0 THEN 'buzz'
 ELSE x END AS fizzbuzz
 FROM sequence1;
 """)
 fun fizzbuzz(max: Int): List<String>
```

---

# 予定

※ あくまで予定

---

# [予定] View

```kotlin
@DatabaseView("""
    SELECT
        Book.id
      , Book.name
      , Book.authorId
      , Author.name AS authorName
    FROM
        Book INNER JOIN Author ON Book.authorId = Author.id
""")
data class BookDetail(
        val id: Long,
        val name: String,
        val authorId: Long,
        val authorName: String
)
```

```kotlin
    @Query("SELECT * FROM BookDetail WHERE id = :id")
    fun detailById(id: Long): BookDetail?
```

---

# [予定] その他

- FTS (全文検索)
- @Relation の強化
- マルチ インスタンス 通知

複数のデータベース インスタンス間でテーブル変更を通知し合う

```kotlin
val db = Room.databaseBuilder(context, LiteratureDatabase.class, "l.db")
             .`enableMultiInstanceInvalidation()`
             .build()
```

プロセスをまたいでも OK

※ 予定

---

# まとめ

不具合報告・要望は [issuetracker.google.com](https://issuetracker.google.com/issues/new?component=192731&template=842428) から

[AOSP AndroidX Contribution Guide](https://android.googlesource.com/platform/frameworks/support/+/androidx-master-dev/README.md)

---

# ありがとうございました