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

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