Slide 1

Slide 1 text

https://goo.gl/aYQnfh Alec Strong & Jake Wharton The Resurgence of SQL

Slide 2

Slide 2 text

Why Persistence?

Slide 3

Slide 3 text

Why Persistence?

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

Why Persistence?

Slide 7

Slide 7 text

Flat Files

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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() Shrek

Slide 14

Slide 14 text

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() Shrek

Slide 15

Slide 15 text

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() Shrek

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Flat Files data class User( val name: String, val age: Int, val email: String ) val bob = User("Bob", 20, "[email protected]") buffer(sink(file)).use { adapter.toJson(it, bob) }

Slide 18

Slide 18 text

Flat Files data class User( val name: String, val age: Int, val email: String ) val bob = User("Bob", 20, "[email protected]") buffer(sink(file)).use { adapter.toJson(it, bob) } {"name":"Bob","age":20,"email":"[email protected]"}

Slide 19

Slide 19 text

Flat Files data class User( val name: String, val age: Int, val email: String )X val bob = User("Bob", 20, "[email protected]") buffer(sink(file)).use { adapter.toJson(it, bob) }Y {"name":"Bob","age":20,"email":"[email protected]"}Z

Slide 20

Slide 20 text

Flat Files data class User( val name: String, val age: Int, val email: String, val friends: List = emptyList() )X val alice = User("Alice", 20, "[email protected]") val bob = User("Bob", 20, "[email protected]", alice) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {"name":"Bob","age":20,"email":"[email protected]","friends":[{"name":"Alice",
 "age":20,"email":"[email protected]"}]}Z

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

Flat Files data class User( val name: String, val age: Int, val email: String, val friends: List = emptyList() )X val alice = User("Alice", 20, "[email protected]") val bob = User("Bob", 20, "[email protected]", listOf(alice)) buffer(sink(file)).use { adapter.toJson(it, bob) }Y {“name”:”Bob","age":20,"email":"[email protected]","friends":[{"name":"Alice",
 “age":20,"email":"[email protected]"}]}Z

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Object DBs data class User( val name: String, val age: Int, val email: String, val friends: List = emptyList() )

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

Object DBs data class User( var name: String, var age: Int, var email: String, var friends: List = 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 :(

Slide 35

Slide 35 text

Object DBs data class User( var name: String, var age: Int, var email: String, var friends: List = 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 :(

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

ORMs data class User( val id: Long, val name: String, val friends: Set )X data class Checkin( val location: String, val time: OffsetDateTime, val users: Set )Y

Slide 38

Slide 38 text

ORMs @Entity data class User( @Id @GeneratedValue(strategy = AUTO) val id: Long, val name: String, @ManyToMany val friends: Set )X @Entity data class Checkin( val location: String, val time: OffsetDateTime, @ManyToMany val users: Set )Y

Slide 39

Slide 39 text

ORMs @Entity data class User( @Id @GeneratedValue(strategy = AUTO) val id: Long, val name: String, @ManyToMany val friends: Set )X @Entity data class Checkin( val location: String, val time: OffsetDateTime, @ManyToMany val users: Set )Y

Slide 40

Slide 40 text

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))

Slide 41

Slide 41 text

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))

Slide 42

Slide 42 text

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 )X

Slide 43

Slide 43 text

• Data Definition Language (DDL) SQL

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

• Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • (Not a thing in SQLite) SQL

Slide 46

Slide 46 text

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) );

Slide 47

Slide 47 text

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) );

Slide 48

Slide 48 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 49

Slide 49 text

Data Manipulation

Slide 50

Slide 50 text

Data Manipulation

Slide 51

Slide 51 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 52

Slide 52 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 53

Slide 53 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 54

Slide 54 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 55

Slide 55 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 56

Slide 56 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 57

Slide 57 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 58

Slide 58 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 59

Slide 59 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 60

Slide 60 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 61

Slide 61 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 62

Slide 62 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 63

Slide 63 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 64

Slide 64 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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 +

Slide 65

Slide 65 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 66

Slide 66 text

fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 67

Slide 67 text

my_id my_id fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 68

Slide 68 text

my_id my_id fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 69

Slide 69 text

my_id my_id fun friendsCheckins( checkins: Collection, friendships: Collection ): 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

Slide 70

Slide 70 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 71

Slide 71 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 72

Slide 72 text

Data Manipulation

Slide 73

Slide 73 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 74

Slide 74 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 75

Slide 75 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 76

Slide 76 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 77

Slide 77 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 78

Slide 78 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 79

Slide 79 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 80

Slide 80 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 81

Slide 81 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 82

Slide 82 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 83

Slide 83 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 84

Slide 84 text

my_id my_id fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 85

Slide 85 text

fun friendsCheckins( checkins: Collection, friends: Collection ): 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

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

SQL

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

SQL

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

• Save DB file to external storage and pull with adb • Stetho • View DB file with a SQLite browser (sqlitebrowser) Debugging

Slide 94

Slide 94 text

• 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

Slide 95

Slide 95 text

• 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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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=?)"

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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=?)"

Slide 104

Slide 104 text

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=?)"

Slide 105

Slide 105 text

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=?)"

Slide 106

Slide 106 text

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=?)"

Slide 107

Slide 107 text

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=?)"

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

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)"

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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)"

Slide 115

Slide 115 text

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)"

Slide 116

Slide 116 text

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)"

Slide 117

Slide 117 text

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)"

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

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)"

Slide 120

Slide 120 text

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)"

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

• Subquery is stored, not correlated EXPLAIN QUERY PLAN

Slide 123

Slide 123 text

• Subquery is stored, not correlated • No order of depth, there is only a single scan through the checkin table EXPLAIN QUERY PLAN

Slide 124

Slide 124 text

• 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

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

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)"

Slide 127

Slide 127 text

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

Slide 128

Slide 128 text

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);

Slide 129

Slide 129 text

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

Slide 130

Slide 130 text

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);

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

SQL

Slide 133

Slide 133 text

SQLite and Android class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context, name, null, VERSION) { companion object { private const val VERSION = 1 }Y }Z

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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

Slide 136

Slide 136 text

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

Slide 137

Slide 137 text

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

Slide 138

Slide 138 text

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

Slide 139

Slide 139 text

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

Slide 140

Slide 140 text

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

Slide 141

Slide 141 text

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

Slide 142

Slide 142 text

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

Slide 143

Slide 143 text

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

Slide 144

Slide 144 text

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

Slide 145

Slide 145 text

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

Slide 146

Slide 146 text

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

Slide 147

Slide 147 text

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

Slide 148

Slide 148 text

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

Slide 149

Slide 149 text

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

Slide 150

Slide 150 text

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

Slide 151

Slide 151 text

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

Slide 152

Slide 152 text

SQLite and Android • Strings... Strings everywhere...

Slide 153

Slide 153 text

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

Slide 154

Slide 154 text

SQLite and Android • Strings... Strings everywhere... • No query or type safety • Prohibitive level of verbosity

Slide 155

Slide 155 text

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

Slide 156

Slide 156 text

SQLDelight & Room

Slide 157

Slide 157 text

SQLDelight Room

Slide 158

Slide 158 text

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 FACTORY = new TodoListModel.Factory<>(AutoValue_TodoList::new); }

Slide 159

Slide 159 text

@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

Slide 160

Slide 160 text

SQLDelight Room • No restriction on Java or SQL type

Slide 161

Slide 161 text

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

Slide 162

Slide 162 text

SQLDelight Room • No restriction on Java or SQL type • No inheritance or other OOP • No restriction on Java type (@Ignore)

Slide 163

Slide 163 text

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

Slide 164

Slide 164 text

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

Slide 165

Slide 165 text

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

Slide 166

Slide 166 text

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 FACTORY = new Factory<>(AutoValue_TodoItem::new); }

Slide 167

Slide 167 text

@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

Slide 168

Slide 168 text

SQLDelight insertList: INSERT INTO todo_list (name) VALUES (?); private val insertList: InsertList by lazy { InsertList(db.writableDatabase) } ... db.bindAndExecute(insertList) { bind(name) }

Slide 169

Slide 169 text

@Insert fun insert(list: TodoList) Room listDao.insert(TodoList(name = name))

Slide 170

Slide 170 text

SQLDelight Room • Can’t insert an object • Can only insert objects

Slide 171

Slide 171 text

SQLDelight Room • Can’t insert an object • Verbose - requires you maintain the cache of mutator queries • Can only insert objects

Slide 172

Slide 172 text

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

Slide 173

Slide 173 text

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; }

Slide 174

Slide 174 text

@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 Room data class TitleAndCount( val name: String, val count: Int )

Slide 175

Slide 175 text

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

Slide 176

Slide 176 text

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

Slide 177

Slide 177 text

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

Slide 178

Slide 178 text

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

Slide 179

Slide 179 text

Not type safe data class TitleAndCount( val names: String, val count: Int )Y java.lang.IllegalArgumentException: Parameter specified as non-null is null Int

Slide 180

Slide 180 text

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

Slide 181

Slide 181 text

Not type safe data class TitleAndCount( val name: Int, val count: Int )Y println(name) // 0 println(count) // 4

Slide 182

Slide 182 text

Room itemDao.titleAndCount(listId) .observeOn(AndroidSchedulers.mainThread()) .subscribe { titleAndCount -> TODO() }

Slide 183

Slide 183 text

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

Slide 184

Slide 184 text

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

Slide 185

Slide 185 text

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

Slide 186

Slide 186 text

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

Slide 187

Slide 187 text

SQLDelight Room • Verbose calling code • “Not sure how to convert a Cursor to this method's return type” • Not type safe

Slide 188

Slide 188 text

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

Slide 189

Slide 189 text

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

Slide 190

Slide 190 text

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

Slide 191

Slide 191 text

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

Slide 192

Slide 192 text

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

Slide 193

Slide 193 text

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

Slide 194

Slide 194 text

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

Slide 195

Slide 195 text

SQLDelight Room

Slide 196

Slide 196 text

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

Slide 197

Slide 197 text

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

Slide 198

Slide 198 text

Room • Unless you have a reason to otherwise, use Room • Support and documentation is way better

Slide 199

Slide 199 text

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

Slide 200

Slide 200 text

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

Slide 201

Slide 201 text

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

Slide 202

Slide 202 text

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

Slide 203

Slide 203 text

SQLDelight • Spending a lot of time in SQLite → Better tooling • Complicated models → Type Safety • Complicated client backend → Views, Triggers, Virtual Tables, Inserts

Slide 204

Slide 204 text

SQLDelight 1.0 • working-kotlin branch on GitHub

Slide 205

Slide 205 text

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 FACTORY = new TodoListModel.Factory<>(AutoValue_TodoList::new); }

Slide 206

Slide 206 text

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 );

Slide 207

Slide 207 text

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

Slide 208

Slide 208 text

SQLDelight 1.0 insertList: INSERT INTO todo_list (name) VALUES (?); db.insertList(name)

Slide 209

Slide 209 text

db.createQuery(TodoItem.FACTORY.titleAndCount(listId)) .mapToOne(TitleAndCount.MAPPER::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X public static final RowMapper MAPPER = TodoItem.FACTORY.titleAndCountMapper(CREATOR); SQLDelight pre-1.0 .observe()

Slide 210

Slide 210 text

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

Slide 211

Slide 211 text

SQLDelight 1.0 db.titleAndCount(::CustomType, listId).observe()
 .mapToOne() .observeOn(AndroidSchedulers.mainThread()) .subscribe { TODO() }X fun titleAndCount( mapper: (title: String, count: Int) -> T, listId: Long ): Query

Slide 212

Slide 212 text

SQLDelight 1.0 • working-kotlin branch on GitHub

Slide 213

Slide 213 text

SQLDelight 1.0 • working-kotlin branch on GitHub • Increase precision of observable emissions • Only possible because SQLDelight is a compiler

Slide 214

Slide 214 text

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

Slide 215

Slide 215 text

Future of SQLite on Android • SupportSQLite

Slide 216

Slide 216 text

SupportSQLite SQLiteDatabase SQLiteOpenHelper SQLiteProgram SQLiteStatement

Slide 217

Slide 217 text

SupportSQLite SupportSQLiteDatabase SupportSQLiteOpenHelper SupportSQLiteProgram SupportSQLiteStatement

Slide 218

Slide 218 text

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

Slide 219

Slide 219 text

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

Slide 220

Slide 220 text

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

Slide 221

Slide 221 text

Future of SQLite on Android • SupportSQLite • Paging

Slide 222

Slide 222 text

Paging • Enables efficient paging of large data sources

Slide 223

Slide 223 text

Paging • Enables efficient paging of large data sources • Not tied to SQL, Room, or RecyclerView

Slide 224

Slide 224 text

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

Slide 225

Slide 225 text

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

Slide 226

Slide 226 text

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