The Resurgence of SQL (Droidcon NYC 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. https://goo.gl/aYQnfh Alec Strong & Jake Wharton The Resurgence of SQL

  2. Why Persistence?

  3. Why Persistence?

  4. Why Persistence? https://goo.gl/1rjsN9

  5. Why Persistence? https://goo.gl/1rjsN9

  6. Why Persistence?

  7. Flat Files

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

  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>
  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>
  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>
  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>
  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>
  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>
  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>
  16. Flat Files data class User( val name: String, val age:

    Int, val email: String )
  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) }
  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"}
  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
  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
  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
  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
  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
  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
  25. Object DBs data class User( val name: String, val age:

    Int, val email: String, val friends: List<User> = emptyList() )
  26. data class User( val name: String, val age: Int, val

    email: String, val friends: List<User> = emptyList() ) : MagicObject() Object DBs
  27. data class User( var name: String, var age: Int, var

    email: String, var friends: List<User> = emptyList() ) : MagicObject() Object DBs
  28. Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject()
  29. Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject()
 .observeOn(mainThread())
  30. Object DBs data class User( var name: String, var age:

    Int, var email: String, var friends: List<User> = emptyList() ) : MagicObject()
 .observeOn(mainThread())
  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()
  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
  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()
  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 :(
  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 :(
  36. data class User( var name: String, var age: Int, var

    email: String, var friends: List<User> = emptyList() ) : MagicObject() Object DBs
  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
  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
  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
  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))
  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))
  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
  43. • Data Definition Language (DDL) SQL

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

    SQL
  45. • Data Definition Language (DDL) • Data Manipulation Language (DML)

    • Data Control Language (DCL) • (Not a thing in SQLite) SQL
  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) );
  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) );
  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
  49. Data Manipulation

  50. Data Manipulation

  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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 +
  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
  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
  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
  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
  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
  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
  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
  72. Data Manipulation

  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  87. SQL

  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
  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
  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
  91. SQL

  92. • Save DB file to external storage and pull with

    adb • Stetho Debugging
  93. • Save DB file to external storage and pull with

    adb • Stetho • View DB file with a SQLite browser (sqlitebrowser) Debugging
  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
  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
  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
  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
  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=?)"
  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
  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
  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
  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
  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=?)"
  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=?)"
  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=?)"
  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=?)"
  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=?)"
  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
  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
  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)"
  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
  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
  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
  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)"
  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)"
  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)"
  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)"
  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
  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)"
  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)"
  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
  122. • Subquery is stored, not correlated EXPLAIN QUERY PLAN

  123. • Subquery is stored, not correlated • No order of

    depth, there is only a single scan through the checkin table EXPLAIN QUERY PLAN
  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
  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
  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)"
  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
  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);
  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
  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);
  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
  132. SQL

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

    name, null, VERSION) { companion object { private const val VERSION = 1 }Y }Z
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  152. SQLite and Android • Strings... Strings everywhere...

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

    or type safety
  154. SQLite and Android • Strings... Strings everywhere... • No query

    or type safety • Prohibitive level of verbosity
  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
  156. SQLDelight & Room

  157. SQLDelight Room

  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); }
  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
  160. SQLDelight Room • No restriction on Java or SQL type

  161. SQLDelight Room • No restriction on Java or SQL type

    • No restriction on Java type (@Ignore)
  162. SQLDelight Room • No restriction on Java or SQL type

    • No inheritance or other OOP • No restriction on Java type (@Ignore)
  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
  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
  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
  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); }
  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
  168. SQLDelight insertList: INSERT INTO todo_list (name) VALUES (?); private val

    insertList: InsertList by lazy { InsertList(db.writableDatabase) } ... db.bindAndExecute(insertList) { bind(name) }
  169. @Insert fun insert(list: TodoList) Room listDao.insert(TodoList(name = name))

  170. SQLDelight Room • Can’t insert an object • Can only

    insert objects
  171. SQLDelight Room • Can’t insert an object • Verbose -

    requires you maintain the cache of mutator queries • Can only insert objects
  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
  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; }
  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 )
  175. SQLDelight Room • “Not sure how to convert a Cursor

    to this method's return type”
  176. SQLDelight Room • “Not sure how to convert a Cursor

    to this method's return type” • Not type safe
  177. Not type safe data class TitleAndCount( val name: String, val

    count: Int )Y println(name) // Grocery List println(count) // 4
  178. Not type safe data class TitleAndCount( val name: String, val

    count: Int )Y
  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
  180. Not type safe data class TitleAndCount( val name: String, val

    count: Int )Y
  181. Not type safe data class TitleAndCount( val name: Int, val

    count: Int )Y println(name) // 0 println(count) // 4
  182. Room itemDao.titleAndCount(listId) .observeOn(AndroidSchedulers.mainThread()) .subscribe { titleAndCount -> TODO() }

  183. SQLDelight db.createQuery(TodoItem.FACTORY.titleAndCount(listId)) .mapToOne(TitleAndCount.MAPPER::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X public static

    final RowMapper<TodoItem> MAPPER = TodoItem.FACTORY.titleAndCountMapper(CREATOR);
  184. SQLDelight db.createQuery(TodoItem.FACTORY.titleAndCount(listId)) .mapToOne(TitleAndCount.MAPPER::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X

  185. Not type safe db.createQuery(TodoItem.FACTORY.titleAndCount(listId)) // Wreak havoc .mapToOne(TitleAndCount.MAPPER::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe

    { TODO() }X SQLDelight
  186. SQLDelight Room • “Not sure how to convert a Cursor

    to this method's return type” • Not type safe
  187. SQLDelight Room • Verbose calling code • “Not sure how

    to convert a Cursor to this method's return type” • Not type safe
  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
  189. SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

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

    • Compiler error == IDE error
  191. SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

    • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete
  192. SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes

    • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete • Migration testing utilities
  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
  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
  195. SQLDelight Room

  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
  197. Room • Unless you have a reason to otherwise, use

    Room
  198. Room • Unless you have a reason to otherwise, use

    Room • Support and documentation is way better
  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
  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
  201. SQLDelight • Spending a lot of time in SQLite →

    Better tooling
  202. SQLDelight • Spending a lot of time in SQLite →

    Better tooling • Complicated models → Type Safety
  203. SQLDelight • Spending a lot of time in SQLite →

    Better tooling • Complicated models → Type Safety • Complicated client backend → Views, Triggers, Virtual Tables, Inserts
  204. SQLDelight 1.0 • working-kotlin branch on GitHub

  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); }
  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 );
  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
  208. SQLDelight 1.0 insertList: INSERT INTO todo_list (name) VALUES (?); db.insertList(name)

  209. db.createQuery(TodoItem.FACTORY.titleAndCount(listId)) .mapToOne(TitleAndCount.MAPPER::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X public static final

    RowMapper<TodoItem> MAPPER = TodoItem.FACTORY.titleAndCountMapper(CREATOR); SQLDelight pre-1.0 .observe()
  210. SQLDelight 1.0 db.titleAndCount(listId).observe()
 .mapToOne() .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X public

    static final RowMapper<TodoItem> MAPPER = TodoItem.FACTORY.titleAndCountMapper(CREATOR);
  211. SQLDelight 1.0 db.titleAndCount(::CustomType, listId).observe()
 .mapToOne() .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X

    fun <T> titleAndCount( mapper: (title: String, count: Int) -> T, listId: Long ): Query<T>
  212. SQLDelight 1.0 • working-kotlin branch on GitHub

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

    of observable emissions • Only possible because SQLDelight is a compiler
  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
  215. Future of SQLite on Android • SupportSQLite

  216. SupportSQLite SQLiteDatabase SQLiteOpenHelper SQLiteProgram SQLiteStatement

  217. SupportSQLite SupportSQLiteDatabase SupportSQLiteOpenHelper SupportSQLiteProgram SupportSQLiteStatement

  218. SupportSQLite SupportSQLiteDatabase SupportSQLiteOpenHelper SupportSQLiteProgram SupportSQLiteStatement SupportSQLiteQuery SupportSQLiteDatabase.query(supportQuery)

  219. SupportSQLite api 'android.arch.persistence:db:1.0.0-beta1'

  220. SupportSQLite api 'android.arch.persistence:db:1.0.0-beta1' implementation 'android.arch.persistence:db-framework:1.0.0-beta1'

  221. Future of SQLite on Android • SupportSQLite • Paging

  222. Paging • Enables efficient paging of large data sources

  223. Paging • Enables efficient paging of large data sources •

    Not tied to SQL, Room, or RecyclerView
  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>
  225. https://goo.gl/aYQnfh @Strongolopolis & @JakeWharton The Resurgence of SQL SELECT *

    FROM persistence_solution WHERE type != 'flat' AND type != 'ORM' AND type != 'ObjectDB'
  226. https://goo.gl/aYQnfh @Strongolopolis & @JakeWharton The Resurgence of SQL