The Resurgence of SQL (Droidcon NYC 2017)

E68309f117985270285ade8082f4877d?s=47 Jake Wharton
September 25, 2017

The Resurgence of SQL (Droidcon NYC 2017)

SQL may have fallen out of fashion over the last decade, but libraries like SQL Delight and Room are now placing it at the forefront of their use. As a language, SQL is more declarative and expressive than any ORM could hope to be. With the support of strong tooling, database interactions not only become easier but you're able to leverage more of its power. This talk will be a re-introduction to the SQL language for those that have seen it before. We will focus on specific examples where you can offload work that would have otherwise been done in code into the database. Finally, we'll compare and contrast the SQL Delight and Room libraries.

Video: https://youtu.be/4eUuD7LsqMs

E68309f117985270285ade8082f4877d?s=128

Jake Wharton

September 25, 2017
Tweet

Transcript

  1. 9.

    Flat Files sharedPreferences.edit() .putString("title", "Shrek") .putInt("year", 2001) .putFloat("rating", 10.0f) .apply()

    <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> </map>
  2. 10.

    Flat Files sharedPreferences.edit() .putString("title", "Shrek") .putInt("year", 2001) .putFloat("rating", 10.0f) .apply()

    sharedPreferences.edit() .putInt("volume", 8) .apply() <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> </map>
  3. 11.

    Flat Files sharedPreferences.edit() .putString("title", "Shrek") .putInt("year", 2001) .putFloat("rating", 10.0f) .apply()

    sharedPreferences.edit() .putInt("volume", 8) .apply() <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> <int name="volume" value="8" /> </map>
  4. 12.

    Flat Files sharedPreferences.edit() .putString("title", "Shrek") .putInt("year", 2001) .putFloat("rating", 10.0f) .apply()

    sharedPreferences.edit() .putInt("volume", 8) .apply() <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> <int name="volume" value="8" /> </map>
  5. 13.

    Flat Files val sharedPreferences = context.getSharedPreferences("user123", MODE_PRIVATE) sharedPreferences.edit() .putString("title", "Shrek")

    .putInt("year", 2001) .putFloat("rating", 10.0f) .apply() sharedPreferences.edit() .putInt("volume", 8) .apply() <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> <int name="volume" value="8" /> </map>
  6. 14.

    Flat Files val sharedPreferences = context.getSharedPreferences("user123", MODE_PRIVATE) sharedPreferences.edit() .putString("title", "Shrek")

    .putInt("year", 2001) .putFloat("rating", 10.0f) .apply() sharedPreferences.edit() .putInt("volume", 8) .apply() <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> <int name="volume" value="8" /> </map>
  7. 15.

    Flat Files val sharedPreferences = context.getSharedPreferences("user123", MODE_PRIVATE) sharedPreferences.edit() .putString("title", "Shrek")

    .putInt("year", 2001) .putFloat("rating", 10.0f) .apply() sharedPreferences.edit() .putInt("volume", 8) .apply() <map> <string name="title">Shrek</string> <int name="year" value="2001" /> <float name="rating" value="10.0" /> <int name="volume" value="8" /> </map>
  8. 17.

    Flat Files data class User( val name: String, val age:

    Int, val email: String ) val bob = User("Bob", 20, "bob@bob.bob") buffer(sink(file)).use { adapter.toJson(it, bob) }
  9. 18.

    Flat Files data class User( val name: String, val age:

    Int, val email: String ) val bob = User("Bob", 20, "bob@bob.bob") buffer(sink(file)).use { adapter.toJson(it, bob) } {"name":"Bob","age":20,"email":"bob@bob.bob"}
  10. 19.

    Flat Files data class User( val name: String, val age:

    Int, val email: String )X val bob = User("Bob", 20, "bob@bob.bob") buffer(sink(file)).use { adapter.toJson(it, bob) }Y {"name":"Bob","age":20,"email":"bob@bob.bob"}Z
  11. 20.

    Flat Files data class User( val name: String, val age:

    Int, val email: String, val friends: List<User> = emptyList() )X val alice = User("Alice", 20, "alice@alice.alice") val bob = User("Bob", 20, "bob@bob.bob", alice) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {"name":"Bob","age":20,"email":"bob@bob.bob","friends":[{"name":"Alice",
 "age":20,"email":"alice@alice.alice"}]}Z
  12. 21.

    data class User( val name: String, val age: Int, val

    email: String, val friends: List<User> = emptyList() )X val alice = User("Alice", 20, "alice@alice.alice") val bob = User("Bob", 20, "bob@bob.bob", alice) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {“name":"Bob","age":20,"email":"bob@bob.bob","friends":[{"name":"Alice",
 “age":20,"email":"alice@alice.alice"}]}Z Flat Files
  13. 22.

    data class User( val name: String, val age: Int, val

    email: String, val friends: List<User> = emptyList() )X val alice = User("Alice", 20, "alice@alice.alice") val bob = User("Bob", 20, "bob@bob.bob", listOf(alice)) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {“name":"Bob","age":20,"email":"bob@bob.bob","friends":[{"name":"Alice",
 “age":20,"email":"alice@alice.alice"}]}Z Flat Files
  14. 23.

    Flat Files data class User( val name: String, val age:

    Int, val email: String, val friends: List<User> = emptyList() )X val alice = User("Alice", 20, "alice@alice.alice") val bob = User("Bob", 20, "bob@bob.bob", listOf(alice)) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {“name”:”Bob","age":20,"email":"bob@bob.bob","friends":[{"name":"Alice",
 “age":20,"email":"alice@alice.alice"}]}Z
  15. 24.

    data class User( val name: String, val age: Int, val

    email: String, val friends: List<User> = emptyList() )X val alice = User("Alice", 20, "alice@alice.alice") val bob = User("Bob", 20, "bob@bob.bob", listOf(alice)) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {“name”:”Bob","age":20,"email":"bob@bob.bob","friends":[{"name":"Alice",
 “age":20,"email":"alice@alice.alice"}]}ZZ Flat Files
  16. 25.

    Object DBs data class User( val name: String, val age:

    Int, val email: String, val friends: List<User> = emptyList() )
  17. 26.

    data class User( val name: String, val age: Int, val

    email: String, val friends: List<User> = emptyList() ) : MagicObject() Object DBs
  18. 27.

    data class User( var name: String, var age: Int, var

    email: String, var friends: List<User> = emptyList() ) : MagicObject() Object DBs
  19. 28.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject()
  20. 29.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject()
 .observeOn(mainThread())
  21. 30.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject()
 .observeOn(mainThread())
  22. 31.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject() // Users who are 20 or older: db.where(User::class.java).greaterThanEqualTo("age", 20).findList()
  23. 32.

    data class User( var name: String, var age: Int, var

    email: String, var friends: List<User> = emptyList() ) : MagicObject() // Users who are 20 or older: db.where(User::class.java).greaterThanEqualTo("age", 20).findList() Object DBs
  24. 33.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject() // Users who are 20 or older: db.where(User::class.java).greaterThanEqualTo("age", 20).findList()
  25. 34.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject() // Users who are 20 or older: db.where(User::class.java).greaterThanEqualTo("age", 20).findList() // Users with 3 or more friends: // Query all users, count and filter in code :(
  26. 35.

    Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject() // Users who are 20 or older: db.where(User::class.java).greaterThanEqualTo("age", 20).findList() // Users with 3 or more friends: // Query all users, count and filter in code :( // Users friends by Bob (transitively) // Query friends, friends of friends and combine in code :(
  27. 36.

    data class User( var name: String, var age: Int, var

    email: String, var friends: List<User> = emptyList() ) : MagicObject() Object DBs
  28. 37.

    ORMs data class User( val id: Long, val name: String,

    val friends: Set<User> )X data class Checkin( val location: String, val time: OffsetDateTime, val users: Set<User> )Y
  29. 38.

    ORMs @Entity data class User( @Id @GeneratedValue(strategy = AUTO) val

    id: Long, val name: String, @ManyToMany val friends: Set<User> )X @Entity data class Checkin( val location: String, val time: OffsetDateTime, @ManyToMany val users: Set<User> )Y
  30. 39.

    ORMs @Entity data class User( @Id @GeneratedValue(strategy = AUTO) val

    id: Long, val name: String, @ManyToMany val friends: Set<User> )X @Entity data class Checkin( val location: String, val time: OffsetDateTime, @ManyToMany val users: Set<User> )Y
  31. 40.

    ORMs // Find your friend’s checkins val me = session.createCritera(User::class.java)

    .add(eq("id", MY_ID)).list().first() val checkins = session.createCritera(Checkin::class.java) .add(eq("users.name", me.friends))
  32. 41.

    ORMs // Find your friend’s checkins val me = session.createCritera(User::class.java)

    .add(eq("id", MY_ID)).list().first() val checkins = session.createCritera(Checkin::class.java) .add(eq("users.name", me.friends))
  33. 42.

    ORMs // Find your friend’s checkins val me = session.createCritera(User::class.java)

    .add(eq("id", MY_ID)).list().first() val checkins = session.createCritera(Checkin::class.java) .add(eq("users.name", me.friends)) @Entity data class User( @Id @GeneratedValue(strategy = AUTO) val id: Long, val name: String, @ManyToMany val friends: Set<User> )X
  34. 45.

    • Data Definition Language (DDL) • Data Manipulation Language (DML)

    • Data Control Language (DCL) • (Not a thing in SQLite) SQL
  35. 46.

    CREATE TABLE user ( _id INTEGER NOT NULL PRIMARY KEY

    AUTOINCREMENT, name TEXT NOT NULL ); Data Definition CREATE TABLE friendship ( friend1 INTEGER NOT NULL REFERENCES user, friend2 INTEGER NOT NULL REFERENCES user, became_friends INTEGER NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (friend1, friend2) );
  36. 47.

    CREATE TABLE checkin ( _id INTEGER NOT NULL PRIMARY KEY

    AUTOINCREMENT, name TEXT NOT NULL, time INTEGER NOT NULL ); Data Definition CREATE TABLE user_checkin ( checkin_id INTEGER NOT NULL REFERENCES checkin, user_id INTEGER NOT NULL REFERENCES user, PRIMARY KEY(checkin_id, user_id) );
  37. 48.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() } Data Manipulation
  38. 51.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() } Data Manipulation
  39. 52.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation friendship
  40. 53.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation friendship FROM
  41. 54.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM friendship friend1 = MY_ID
  42. 55.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM friendship friend1 = MY_ID WHERE
  43. 56.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM friendship friend1 = :my_id WHERE
  44. 57.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM friendship WHERE friend1 = :my_id friend2
  45. 58.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation friend2 FROM friendship WHERE friend1 = :my_id SELECT
  46. 59.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id
  47. 60.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id friendship friend2 = MY_ID friend1 :my_id
  48. 61.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id friendship FROM friend2 = MY_ID friend1 :my_id
  49. 62.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id FROM friendship friend2 = :my_id WHERE friend1 MY_ID
  50. 63.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id friend1 FROM friendship WHERE friend2 = :my_id SELECT
  51. 64.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id SELECT friend1 FROM friendship WHERE friend2 = :my_id +
  52. 65.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id + SELECT friend1 FROM friendship WHERE friend2 = :my_id
  53. 66.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long { var

    friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id
  54. 67.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long

    { var friends = friendships .filter { it.friend1 == MY_ID } .map { it.friend2 } friends += friendships .filter { it.friend2 == MY_ID } .map { it.friend1 } return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id Data Manipulation
  55. 68.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id Data Manipulation
  56. 69.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friendships: Collection<Friendship> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id Data Manipulation
  57. 70.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id Data Manipulation F r i e n d s h i p
  58. 71.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id Data Manipulation
  59. 73.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id Data Manipulation
  60. 74.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation user_checkin SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id
  61. 75.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM user_checkin SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id
  62. 76.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM user_checkin SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id IN user_id in friends
  63. 77.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in f r i e n d s
  64. 78.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in checkin_id
  65. 79.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation checkin_id FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in SELECT
  66. 80.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT checkin_id FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in distinct DISTINCTP
  67. 81.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT DISTINCTPcheckin_id FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in distinct
  68. 82.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT DISTINCTPcheckin_id FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in size count
  69. 83.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT count(DISTINCTPcheckin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in size
  70. 84.

    my_id my_id fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long

    { return checkins .filter { it.user_id in friends } .map { it.checkin_id } .distinct() .size() }X Data Manipulation SELECT count(DISTINCTPcheckin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in size
  71. 85.

    fun friendsCheckins( checkins: Collection<UserCheckin>, friends: Collection<Long> ): Long { }X

    Data Manipulation SELECT count(DISTINCTPcheckin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in size
  72. 86.

    Data Manipulation in SQL! SELECT count(DISTINCTPcheckin_id) FROM user_checkin WHERE user_id

    IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y in size
  73. 87.

    SQL

  74. 88.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y SQL
  75. 89.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id JOIN friendship ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y SQL
  76. 90.

    SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship ON ( (user_id

    = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y SQL
  77. 91.

    SQL

  78. 93.

    • Save DB file to external storage and pull with

    adb • Stetho • View DB file with a SQLite browser (sqlitebrowser) Debugging
  79. 94.

    • Save DB file to external storage and pull with

    adb • Stetho • View DB file with a SQLite browser (sqlitebrowser) • Run queries to learn more Debugging
  80. 95.

    • Save DB file to external storage and pull with

    adb • Stetho • View DB file with a SQLite browser (sqlitebrowser) • Run queries to learn more • EXPLAIN QUERY PLAN Debugging
  81. 96.

    SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship ON ( (user_id

    = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y EXPLAIN QUERY PLAN
  82. 97.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y EXPLAIN QUERY PLAN
  83. 98.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
  84. 99.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) ) selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" Where is this instruction in the FROM clause
  85. 100.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) ) Full table scan
  86. 101.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) ) Full table scan Search a subset using
 an index
  87. 102.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) ) selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" The nesting order of this instruction
  88. 103.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
  89. 104.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) ) selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
  90. 105.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) ) selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
  91. 106.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
  92. 107.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)" "0" "1" "1" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
  93. 108.

    EXPLAIN QUERY PLAN SELECT count(DISTINCT checkin_id) FROM user_checkin JOIN friendship

    ON ( (user_id = friend1 AND friend2 = :my_id) OR (user_id = friend2 AND friend1 = :my_id) )Y EXPLAIN QUERY PLAN
  94. 109.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y EXPLAIN QUERY PLAN
  95. 110.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  96. 111.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id ) The subquery ID for the instruction
  97. 112.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y
  98. 113.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y Can either be EXECUTE or
 EXECUTE CORRELATED
  99. 114.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  100. 115.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  101. 116.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  102. 117.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  103. 118.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y
  104. 119.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  105. 120.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  106. 121.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y EXPLAIN QUERY PLAN
  107. 123.

    • Subquery is stored, not correlated • No order of

    depth, there is only a single scan through the checkin table EXPLAIN QUERY PLAN
  108. 124.

    • Subquery is stored, not correlated • No order of

    depth, there is only a single scan through the checkin table • Scans could be searches if we created an index manually EXPLAIN QUERY PLAN
  109. 125.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y EXPLAIN QUERY PLAN
  110. 126.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
  111. 127.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y user_id
  112. 128.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y user_id CREATE INDEX userIdIndex ON user_checkin(user_id);
  113. 129.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" userIdIndex SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y CREATE INDEX userIdIndex ON user_checkin(user_id); friend2
  114. 130.

    selectid order from detail "0" "0" "0" "SCAN TABLE user_checkin"

    "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SCAN TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" 
 friend2Index userIdIndex SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y CREATE INDEX userIdIndex ON user_checkin(user_id); friend2 
 CREATE INDEX friend2Index ON friendship(friend2);
  115. 131.

    SELECT count(DISTINCT checkin_id) FROM user_checkin WHERE user_id IN ( SELECT

    friend2 FROM friendship WHERE friend1 = :my_id UNION SELECT friend1 FROM friendship WHERE friend2 = :my_id )Y selectid order from detail "0" "0" "0" "SEARCH TABLE user_checkin USING INDEX (user_id=?)" "0" "0" "0" "EXECUTE LIST SUBQUERY 1" "2" "0" "0" "SEARCH TABLE friendship USING COVERING INDEX sqlite_autoindex_friendship_1 (friend1=?)" "3" "0" "0" "SEARCH TABLE friendship USING INDEX (friend2=?)" "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)" CREATE INDEX userIdIndex ON user_checkin(user_id); CREATE INDEX friend2Index ON friendship(friend2); 
 friend2Index userIdIndex
  116. 132.

    SQL

  117. 133.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { companion object { private const val VERSION = 1 }Y }Z
  118. 134.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z
  119. 135.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z
  120. 136.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE user ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z
  121. 137.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE user (Z _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A
  122. 138.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} (Z ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A user _id name
  123. 139.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} (Z ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A object FriendshipColumns { const val TABLE_NAME = "friendship" const val FRIEND_1 = "friend1" const val FRIEND_2 = "friend2" const val BECAME_FRIENDS = "became_friends" }B
  124. 140.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} (Z ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${FriendshipColumns.TABLE_NAME} ( ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2}) ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A object FriendshipColumns { const val TABLE_NAME = "friendship" const val FRIEND_1 = "friend1" const val FRIEND_2 = "friend2" const val BECAME_FRIENDS = "became_friends" }B
  125. 141.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} (Z ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${FriendshipColumns.TABLE_NAME} ( ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2}) ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A object FriendshipColumns { const val TABLE_NAME = "friendship" const val FRIEND_1 = "friend1" const val FRIEND_2 = "friend2" const val BECAME_FRIENDS = "became_friends" }B 
 object CheckinColumns { const val TABLE_NAME = "checkin" const val ID = "_id" const val NAME = "name" const val TIME = "time" }C
  126. 142.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} (Z ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${FriendshipColumns.TABLE_NAME} ( ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2}) ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${CheckinColumns.TABLE_NAME} ( ${CheckinColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${CheckinColumns.NAME} TEXT NOT NULL, ${CheckinColumns.TIME} INTEGER NOT NULL ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A object FriendshipColumns { const val TABLE_NAME = "friendship" const val FRIEND_1 = "friend1" const val FRIEND_2 = "friend2" const val BECAME_FRIENDS = "became_friends" }B 
 object CheckinColumns { const val TABLE_NAME = "checkin" const val ID = "_id" const val NAME = "name" const val TIME = "time" }C
  127. 143.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} (Z ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${FriendshipColumns.TABLE_NAME} ( ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2}) ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${CheckinColumns.TABLE_NAME} ( ${CheckinColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${CheckinColumns.NAME} TEXT NOT NULL, ${CheckinColumns.TIME} INTEGER NOT NULL ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A object FriendshipColumns { const val TABLE_NAME = "friendship" const val FRIEND_1 = "friend1" const val FRIEND_2 = "friend2" const val BECAME_FRIENDS = "became_friends" }B 
 object CheckinColumns { const val TABLE_NAME = "checkin" const val ID = "_id" const val NAME = "name" const val TIME = "time" }C object UserCheckinColumns { const val TABLE_NAME = "user_checkin" const val CHECKIN_ID = "checkin_id" const val USER_ID = "user_id" }D
  128. 144.

    SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context,

    name, null, VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(""" CREATE TABLE ${UserColumns.TABLE_NAME} ( ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${UserColumns.NAME} TEXT NOT NULL ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${FriendshipColumns.TABLE_NAME} ( ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME, PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2}) ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${CheckinColumns.TABLE_NAME} ( ${CheckinColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ${CheckinColumns.NAME} TEXT NOT NULL, ${CheckinColumns.TIME} INTEGER NOT NULL ); """.trimIndent()) db.execSQL(""" CREATE TABLE ${UserCheckinColumns.TABLE_NAME} ( ${UserCheckinColumns.CHECKIN_ID} INTEGER NOT NULL REFERENCES ${CheckinColumns.TABLE_NAME}, ${UserCheckinColumns.USER_ID} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME}, PRIMARY KEY(${UserCheckinColumns.CHECKIN_ID}, ${UserCheckinColumns.USER_ID}) ); """.trimIndent()) }W override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) { }X companion object { private const val VERSION = 1 }Y }Z object UserColumns { const val TABLE_NAME = "user" const val ID = "_id" const val NAME = "name" }A object FriendshipColumns { const val TABLE_NAME = "friendship" const val FRIEND_1 = "friend1" const val FRIEND_2 = "friend2" const val BECAME_FRIENDS = "became_friends" }B 
 object CheckinColumns { const val TABLE_NAME = "checkin" const val ID = "_id" const val NAME = "name" const val TIME = "time" }C object UserCheckinColumns { const val TABLE_NAME = "user_checkin" const val CHECKIN_ID = "checkin_id" const val USER_ID = "user_id" }D
  129. 145.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Cursor? {

    return db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 ) """.trimIndent(), arrayOf(myId.toString())) }Z
  130. 146.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Cursor? {

    return db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 ) """.trimIndent(), arrayOf(myId.toString())) }Z
  131. 147.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Cursor? {A

    return db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 )B """.trimIndent(), arrayOf(myId.toString())) }Z
  132. 148.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A

    return db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 )B """.trimIndent(), arrayOf(myId.toString())) }Z
  133. 149.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A

    val cursor = db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 )B """.trimIndent(), arrayOf(myId.toString())) cursor.use { if (it.moveToNext()) { return it.getInt(0)G }J throw IllegalStateException("Query returned zero rows") }K }Z
  134. 150.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A

    val count = "checkin_count" val cursor = db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) AS $count FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 )B """.trimIndent(), arrayOf(myId.toString())) cursor.use { if (it.moveToNext()) { return it.getInt(it.getColumnIndex(count))G }J throw IllegalStateException("Query returned zero rows") }K }Z
  135. 151.

    SQLite and Android fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A

    val count = "checkin_count" val cursor = db.rawQuery(""" SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) AS $count FROM ${UserCheckinColumns.TABLE_NAME} WHERE ${UserCheckinColumns.USER_ID} IN ( SELECT ${FriendshipColumns.FRIEND_2} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_1} = ?1 UNION SELECT ${FriendshipColumns.FRIEND_1} FROM ${FriendshipColumns.TABLE_NAME} WHERE ${FriendshipColumns.FRIEND_2} = ?1 )B """.trimIndent(), arrayOf(myId.toString())) cursor.use { if (it.moveToNext()) { return it.getInt(it.getColumnIndex(count))G }J throw IllegalStateException("Query returned zero rows") }K }Z
  136. 154.

    SQLite and Android • Strings... Strings everywhere... • No query

    or type safety • Prohibitive level of verbosity
  137. 155.

    SQLite and Android • Strings... Strings everywhere... • No query

    or type safety • Prohibitive level of verbosity • Reactive updates only achievable through a ContentProvider • Another level of hell, omitted for sanity
  138. 158.

    SQLDelight CREATE TABLE todo_list ( _id INTEGER NOT NULL PRIMARY

    KEY AUTOINCREMENT, name TEXT NOT NULL, archived INTEGER AS Boolean NOT NULL DEFAULT 0 ); @AutoValue public abstract class TodoList implements Parcelable, TodoListModel { public static final Factory<TodoList> FACTORY = new TodoListModel.Factory<>(AutoValue_TodoList::new); }
  139. 159.

    @Entity(tableName = "todo_list") data class TodoList( @PrimaryKey(autoGenerate = true) @ColumnInfo(name

    = "_id") val id: Int = 0, val name: String, val archived: Boolean = false ) Room
  140. 161.

    SQLDelight Room • No restriction on Java or SQL type

    • No restriction on Java type (@Ignore)
  141. 162.

    SQLDelight Room • No restriction on Java or SQL type

    • No inheritance or other OOP • No restriction on Java type (@Ignore)
  142. 163.

    SQLDelight Room • No restriction on Java or SQL type

    • No inheritance or other OOP • No restriction on Java type (@Ignore) • Subset of SQLite supported • UNIQUE, CHECK, DEFAULT
  143. 164.

    SQLDelight Room • No restriction on Java or SQL type

    • No inheritance or other OOP • Doesn't play well with Kotlin data classes • No restriction on Java type (@Ignore) • Subset of SQLite supported • UNIQUE, CHECK, DEFAULT
  144. 165.

    SQLDelight Room • No restriction on Java or SQL type

    • No inheritance or other OOP • Doesn't play well with Kotlin data classes • No restriction on Java type (@Ignore) • Subset of SQLite supported • UNIQUE, CHECK, DEFAULT • Doesn't work with AutoValue
  145. 166.

    SQLDelight CREATE TABLE todo_item ( _id INTEGER NOT NULL PRIMARY

    KEY AUTOINCREMENT, todo_list_id INTEGER NOT NULL REFERENCES todo_list, description TEXT NOT NULL, complete INTEGER AS Boolean NOT NULL DEFAULT 0 ); createListIdIndex: CREATE INDEX item_list_id ON todo_item(todo_list_id); @AutoValue public abstract class TodoItem implements TodoItemModel, Parcelable { public static final Factory<TodoItem> FACTORY = new Factory<>(AutoValue_TodoItem::new); }
  146. 167.

    @Entity( tableName = "todo_item", foreignKeys = arrayOf(ForeignKey( entity = TodoItem::class,

    parentColumns = arrayOf("_id"), childColumns = arrayOf("todo_list_id") )) ) data class TodoItem( @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "_id") val id: Long, @ColumnInfo(name = "todo_list_id", index = true) val todoListId: Long, val description: String, val complete: Boolean = false ) Room
  147. 168.

    SQLDelight insertList: INSERT INTO todo_list (name) VALUES (?); private val

    insertList: InsertList by lazy { InsertList(db.writableDatabase) } ... db.bindAndExecute(insertList) { bind(name) }
  148. 171.

    SQLDelight Room • Can’t insert an object • Verbose -

    requires you maintain the cache of mutator queries • Can only insert objects
  149. 172.

    SQLDelight Room • Can’t insert an object • Verbose -

    requires you maintain the cache of mutator queries • Can only insert objects • Can’t use DAO’s during creation
  150. 173.

    SQLDelight titleAndCount: SELECT name, count(todo_item._id) FROM todo_list LEFT JOIN todo_item

    ON (todo_list._id = todo_list_id) WHERE todo_list._id = ? AND complete = 0 GROUP BY todo_list._id; @AutoValue public abstract class TitleAndCount implements TitleAndCountModel { public static final TitleAndCountCreator CREATOR = AutoValue_TitleAndCount::new; }
  151. 174.

    @Query("" + "SELECT name, count(*) AS count\n" + "FROM todo_list\n"

    + "LEFT JOIN todo_item ON (todo_list._id = todo_list_id)\n" + "WHERE todo_list._id = :todoListId AND complete = 0\n" + "GROUP BY todo_list._id" ) fun titleAndCount(todoListId: Long): Flowable<TitleAndCount> Room data class TitleAndCount( val name: String, val count: Int )
  152. 175.
  153. 176.

    SQLDelight Room • “Not sure how to convert a Cursor

    to this method's return type” • Not type safe
  154. 177.

    Not type safe data class TitleAndCount( val name: String, val

    count: Int )Y println(name) // Grocery List println(count) // 4
  155. 179.

    Not type safe data class TitleAndCount( val names: String, val

    count: Int )Y java.lang.IllegalArgumentException: Parameter specified as non-null is null Int
  156. 181.

    Not type safe data class TitleAndCount( val name: Int, val

    count: Int )Y println(name) // 0 println(count) // 4
  157. 186.

    SQLDelight Room • “Not sure how to convert a Cursor

    to this method's return type” • Not type safe
  158. 187.

    SQLDelight Room • Verbose calling code • “Not sure how

    to convert a Cursor to this method's return type” • Not type safe
  159. 188.

    SQLDelight Room • Verbose calling code • SQLBrite - SQLDelight

    bridge not type safe • “Not sure how to convert a Cursor to this method's return type” • Not type safe
  160. 191.

    SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

    • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete
  161. 192.

    SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

    • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete • Migration testing utilities
  162. 193.

    SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

    • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete • Migration testing utilities • Embedded object types
  163. 194.

    SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

    • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete • Migration testing utilities • Embedded object types • Better support/documentation
  164. 196.

    SQLDelight Room • Associate a java type to a column

    definition and receive type safe projections and mutation apis. • Define a table in java and serialize java objects to and from a query
  165. 198.

    Room • Unless you have a reason to otherwise, use

    Room • Support and documentation is way better
  166. 199.

    Room • Unless you have a reason to otherwise, use

    Room • Support and documentation is way better • More than enough sqlite support to get the benefits you need
  167. 200.

    Room • Unless you have a reason to otherwise, use

    Room • Support and documentation is way better • More than enough sqlite support to get the benefits you need • API feels familiar and simple — akin to Retrofit
  168. 202.

    SQLDelight • Spending a lot of time in SQLite →

    Better tooling • Complicated models → Type Safety
  169. 203.

    SQLDelight • Spending a lot of time in SQLite →

    Better tooling • Complicated models → Type Safety • Complicated client backend → Views, Triggers, Virtual Tables, Inserts
  170. 205.

    SQLDelight pre-1.0 CREATE TABLE todo_list ( _id INTEGER NOT NULL

    PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, archived INTEGER AS Boolean NOT NULL DEFAULT 0 ); @AutoValue public abstract class TodoList implements Parcelable, TodoListModel { public static final Factory<TodoList> FACTORY = new TodoListModel.Factory<>(AutoValue_TodoList::new); }
  171. 206.

    SQLDelight 1.0 CREATE TABLE todo_list ( _id INTEGER NOT NULL

    PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, archived INTEGER AS Boolean NOT NULL DEFAULT 0 );
  172. 207.

    insertList: INSERT INTO todo_list (name) VALUES (?); private val insertList:

    InsertList by lazy { InsertList(db.writableDatabase) } ... db.bindAndExecute(insertList) { bind(name) } SQLDelight pre-1.0
  173. 210.
  174. 211.
  175. 213.

    SQLDelight 1.0 • working-kotlin branch on GitHub • Increase precision

    of observable emissions • Only possible because SQLDelight is a compiler
  176. 214.

    SQLDelight 1.0 • working-kotlin branch on GitHub • Increase precision

    of observable emissions • Only possible because SQLDelight is a compiler • Embrace Kotlin as the future
  177. 223.

    Paging • Enables efficient paging of large data sources •

    Not tied to SQL, Room, or RecyclerView
  178. 224.

    Paging • Enables efficient paging of large data sources •

    Not tied to SQL, Room, or RecyclerView • Seamless Room support
 
 @Query("select * from users WHERE age > :age order by name DESC")
 fun usersOlderThan(age: Int): TiledDataSource<User>
  179. 225.

    https://goo.gl/aYQnfh @Strongolopolis & @JakeWharton The Resurgence of SQL SELECT *

    FROM persistence_solution WHERE type != 'flat' AND type != 'ORM' AND type != 'ObjectDB'