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

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 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!
  2. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

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

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  4. 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
  5. 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 )
  6. 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>
  7. @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
  8. @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
  9. SEARCH WITH FTS @Query(""" SELECT * FROM Song WHERE Song

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

    have foreign keys ‣ cannot have composite primary keys
  11. 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 )
  12. EXTERNAL CONTENT @Query(""" SELECT Song.* FROM SongFts JOIN Song ON

    SongFts.rowid = id WHERE SongFts MATCH :query """) fun searchSongs(query: String): List<Song>
  13. 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_
  14. 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
  15. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  16. 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>
  17. 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>
  18. 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; …
  19. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  20. 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()
  21. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

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

    Java ▸ https://github.com/google/auto/
  23. 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 ¯\_(π)_/¯
  24. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  25. 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
  26. ROOM 2.1 ▸ FTS ▸ VIEWs ▸ Multi-Instance Invalidation ▸

    AutoValue ▸ More Rx ▸ Kotlin Coroutine
  27. 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>