$30 off During Our Annual Pro Sale. View Details »

Room 2.1

Room 2.1

This talk introduces new features of Room 2.1, FTS, VIEWs and so on.

All the sample code is on https://github.com/hkurokawa/RoomSample

- The Room in the House (Android Dev Summit '18)
https://www.youtube.com/watch?v=sU-ot_Oz3AE
- Architecture Components Release Notes  |  Android Developers
https://developer.android.com/jetpack/docs/release-notes#december_4_2018
- SQLite FTS3 and FTS4 Extensions
https://www.sqlite.org/fts3.html
- sqlite - Android Room database query does not return id column - Stack Overflow
https://stackoverflow.com/questions/53536206/android-room-database-query-does-not-return-id-column
- google/auto: A collection of source code generators for Java.
https://github.com/google/auto/

Hiroshi Kurokawa

December 10, 2018
Tweet

More Decks by Hiroshi Kurokawa

Other Decks in Technology

Transcript

  1. ROOM 2.1 10 DEC 2018, ANDROID DEV SUMMIT 2018 REVIEW

    HIROSHI KUROKAWA MIXI, INC.
  2. ROOM 2.1 ▸ alpha-03 is out! 
 https://developer.android.com/jetpack/docs/release-notes#december_4_2018 !

  3. ROOM SO FAR ▸ 1.0 - compile time verified queries,

    observables, IDE integration ▸ 1.1 - Write-Ahead logging, Paging integration ▸ 2.0 - AndroidX ▸ 2.1 - FTS, VIEWs, Multi-Instance Invalidation, AutoValue, RxRoom Room 2.1 is a pretty big release!
  4. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine https://github.com/hkurokawa/RoomSample
  5. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  6. FTS ▸ Full-Text Search ▸ Similar to SQL LIKE Query

    ▸ LIKE Query is just like grep ▸ FTS provides more powerful query ▸ “title:linux problem” ▸ “dog OR cat” https://www.sqlite.org/fts3.html#full_text_index_queries
  7. SAMPLE ENTITY @Entity data class Song( @PrimaryKey val id: Long,

    val url: String, @Embedded val labels: SongLabels ) data class SongLabels( val songName: String, val albumName: String, val artistName: String )
  8. SEARCH WITH LIKE QUERY @Query(""" SELECT * FROM Song WHERE

    songName LIKE '%' || :query || '%' OR albumName LIKE '%' || :query || '%' OR artistName LIKE '%' || :query || '%' """) fun searchSongs(query: String): List<Song>
  9. @Entity @Fts4 data class Song( @PrimaryKey @ColumnInfo(name = "rowid") val

    id: Long, val url: String, @Embedded val labels: SongLabels ) data class SongLabels( val songName: String, val albumName: String, val artistName: String ) SEARCH WITH FTS ⚠ Error: Song has some fields [rowid] which are not returned by the query
  10. @Entity @Fts4 data class Song( val url: String, @Embedded val

    labels: SongLabels ) data class SongLabels( val songName: String, val albumName: String, val artistName: String ) SEARCH WITH FTS https://stackoverflow.com/q/53536206
  11. SEARCH WITH FTS @Query(""" SELECT * FROM Song WHERE Song

    MATCH :query """) fun searchSongs(query: String): List<Song>
  12. SOME DRAWBACKS ‣ An entity annotated with @Fts4 ‣ cannot

    have foreign keys ‣ cannot have composite primary keys
  13. VIRTUAL/SHADOW TABLE

  14. VIRTUAL/SHADOW TABLE

  15. VIRTUAL/SHADOW TABLE Song Song_content Song_docsize Song_segdir Song_segments Song_stat Virtual Table

    Shadow Table Query
  16. EXTERNAL CONTENT @Entity data class Song( @PrimaryKey val id: Long,

    val url: String, @Embedded val labels: SongLabels ) @Entity @Fts4(contentEntity = Song ::class) data class SongFts( @Embedded val labels: SongLabels )
  17. EXTERNAL CONTENT @Query(""" SELECT Song.* FROM SongFts JOIN Song ON

    SongFts.rowid = id WHERE SongFts MATCH :query """) fun searchSongs(query: String): List<Song>
  18. EXTERNAL CONTENT

  19. EXTERNAL CONTENT SongFts Song_docsize Song_segdir Song_segments Song_stat Virtual Table Shadow

    Table Query Song
  20. INSPECT TABLES $ adb shell generic_x86:/ $ run-as net.hydrakecat.roomsample generic_x86:/

    $ sqlite3 databases/test-database SQLite version 3.18.2 2017-07-21 07:56:09 Enter ".help" for usage hints. sqlite> .schema CREATE VIRTUAL TABLE `SongFts` USING FTS4(`songName` TEXT NOT NULL, `albumName` TEXT NOT NULL, `artistName` TEXT NOT NULL, content=`Song`); CREATE TABLE IF NOT EXISTS 'SongFts_segments'(blockid INTEGER PRIMARY KEY, block BLOB); … CREATE TRIGGER room_fts_content_sync_SongFts_BEFORE_UPDATE BEFORE UPDATE ON `Song` BEGIN DELETE FROM `SongFts` WHERE `docid`=OLD.`rowid`; END; … https://www.sqlite.org/fts3.html#_external_content_fts4_tables_
  21. LANGUAGE SUPPORT @Entity @Fts4(languageId = "lid") data class Product( @ColumnInfo(name

    = "lid") val languageId: Int ) ‣ Language ID is used to determine which tokenizer to use ‣ See https://www.sqlite.org/fts3.html#*fts4languageid
  22. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  23. VIEWS Song song_id name popularity Album album_id title AlbumSong album_id

    song_id N : N
  24. VIEWS @Query(""" SELECT Album.title AS album_title, Song.name AS song_name FROM

    AlbumSong INNER JOIN Album ON Album.album_id = AlbumSong.album_id INNER JOIN Song ON Song.song_id = AlbumSong.song_id """) fun allSongsAndAlbums(): List<Listing>
  25. VIEWS Song song_id name popularity Album album_id title AlbumSong album_id

    song_id N : N
  26. VIEWS AlbumSongFull album_id song_id name title popularity

  27. VIEWS @DatabaseView(""" SELECT Album.*, Song.* FROM AlbumSong INNER JOIN Album

    ON Album.album_id = AlbumSong.album_id INNER JOIN Song ON Song.song_id = AlbumSong.song_id """) data class AlbumSongFull( @Embedded val song: Song, @Embedded val album: Album ) @Query(""" SELECT name AS song_name, title AS album_title FROM AlbumSongFull """) fun allSongsAndAlbums(): List<Listing>
  28. INSPECT TABLES $ adb shell generic_x86:/ $ run-as net.hydrakecat.roomsample generic_x86:/

    $ sqlite3 databases/view-database SQLite version 3.18.2 2017-07-21 07:56:09 Enter ".help" for usage hints. sqlite> .schema … CREATE VIEW `AlbumSongFull` AS SELECT Album.*, Song.* FROM AlbumSong INNER JOIN Album ON Album.album_id = AlbumSong.album_id INNER JOIN Song ON Song.song_id = AlbumSong.song_id; …
  29. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  30. MULTIPLE INSTANCE INVALIDATION ▸ Room detects any update on the

    database only if the update is done on the same process ▸ This feature enables us to update the database on another process and make sure the app is notified for the update Room.databaseBuilder(context, MyDatabase ::class.java, “my-db“) .enableMultiInstanceInvalidation() .build()
  31. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  32. AUTO VALUE ▸ A kind of Kotlin data class in

    Java ▸ https://github.com/google/auto/
  33. AUTO VALUE @Entity @AutoValue public abstract class Person { @PrimaryKey(autoGenerate

    = true) @AutoValue.CopyAnnotations public abstract long getId(); public abstract String getFirstName(); public abstract String getLastName(); public static Person create(long id, String firstName, String lastName) { return new AutoValue_Person(id, firstName, lastName); } } Note: AutoValue does not work with kapt ¯\_(π)_/¯
  34. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  35. COMPLETABLE/SINGLE SUPPORT FOR INSERT/UPDATE/DELETE @Insert fun insert(song: Song): Completable @Update

    fun update(song: Song): Single<Int> @Delete fun delete(song: Song): Completable
  36. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  37. KOTLIN COROUTINE @Insert suspend fun insert(song: Song) @Update suspend fun

    update(song: Song) @Delete suspend fun delete(song: Song) @Query("SELECT * FROM Song") suspend fun findAll(): List<Song>