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/

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>