https://goo.gl/aYQnfh Alec Strong & Jake Wharton The Resurgence of SQL
A presentation at Droidcon NYC in September 2017 in New York, NY, USA by Jake Wharton
https://goo.gl/aYQnfh Alec Strong & Jake Wharton The Resurgence of SQL
Why Persistence?
Why Persistence?
Why Persistence? https://goo.gl/1rjsN9
Why Persistence? https://goo.gl/1rjsN9
Why Persistence?
Flat Files
Flat Files sharedPreferences.edit () .putString( "title" , "Shrek" ) .putInt( "year" , 2001 ) .putFloat( "rating" , 10.0f ) .apply()
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
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
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
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
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
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
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
Flat Files data class User(
val name : String,
val age : Int,
val email : String )
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 ) }
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" }
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
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
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
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
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
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
Object DBs data class User(
val name : String,
val age : Int,
val email : String ,
val friends : List<User> = emptyList()
)
data class User(
val name : String,
val age : Int,
val email : String ,
val friends : List<User> = emptyList()
) : MagicObject() Object DBs
data class User(
var name : String,
var age : Int,
var email : String ,
var friends : List<User> = emptyList()
) : MagicObject() Object DBs
Object DBs data class User(
var name : String,
var age : Int,
var email : String ,
var friends : List<User> = emptyList()
) : MagicObject()
Object DBs data class User(
var name : String,
var age : Int,
var email : String ,
var friends : List<User> = emptyList()
) : MagicObject() .observeOn(mainThread())
Object DBs data class User(
var name : String,
var age : Int,
var email : String ,
var friends : List<User> = emptyList()
) : MagicObject() .observeOn(mainThread())
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()
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
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()
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 :(
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 :(
data class User(
var name : String,
var age : Int,
var email : String ,
var friends : List<User> = emptyList()
) : MagicObject() Object DBs
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
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
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
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 ))
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 ))
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
• Data Definition Language (DDL) SQL
• Data Definition Language (DDL) • Data Manipulation Language (DML) SQL
• Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • (Not a thing in SQLite) SQL
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) );
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) );
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { it . friend2 }
MY_ID }
. map { it . friend1 }
return checkins . filter { it . user_id in friends }
. map { it . checkin_id }
. distinct ()
.size()
} Data Manipulation
Data Manipulation
Data Manipulation
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { it . friend2 }
MY_ID }
. map { it . friend1 }
return checkins . filter { it . user_id in friends }
. map { it . checkin_id }
. distinct ()
.size()
} Data Manipulation
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
MY_ID
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
MY_ID
friend1
:my_id
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
MY_ID
friend1
:my_id
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
my_id
my_id
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friendships: Collection<Friendship>
): Long {
MY_ID }
. map { 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
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
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
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
Friendship
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
Data Manipulation
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
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
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
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
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
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
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
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
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 DISTINCT P 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
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 DISTINCT P 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 size
count
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( DISTINCT P 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 size
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( DISTINCT P 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 size
fun friendsCheckins(
checkins: Collection<UserCheckin>,
friends: Collection<Long
): Long {
} X Data Manipulation SELECT
count( DISTINCT P 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 size
Data Manipulation in SQL! SELECT count( DISTINCT P 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 size
SQL
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
SELECT count( DISTINCT checkin_ id) FROM user_c heckin 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
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
SQL
• Save DB file to external storage and pull with adb • Stetho Debugging
• Save DB file to external storage and pull with adb • Stetho • View DB file with a SQLite browser (sqlitebrowser) Debugging
• 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
• 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
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
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
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=?)" EXPLAIN QUERY PLAN
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
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
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
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
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=?)"
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=?)"
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=?)"
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=?)"
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=?)" EXPLAIN QUERY PLAN
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
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
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)" EXPLAIN QUERY PLAN
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
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
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
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)"
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)"
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)"
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)"
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
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)"
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)" EXPLAIN QUERY PLAN
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
• Subquery is stored, not correlated EXPLAIN QUERY PLAN
• Subquery is stored, not correlated • No order of depth, there is only a single scan through the checkin table EXPLAIN QUERY PLAN
• 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
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
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)" EXPLAIN QUERY PLAN
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)" user_id 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)" user_id CREATE INDEX userIdIndex
ON user_checkin(user_id); 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)"
userIdIndex CREATE INDEX userIdIndex
ON user_checkin(user_id); friend 2 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)"
friend2Index
userIdIndex CREATE INDEX userIdIndex
ON user_checkin(user_id); friend 2 CREATE INDEX friend2Index
ON friendship(friend2); 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
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
SQL
SQLite and Android class MyDatabase(context: Context, name: String?)
: SQLiteOpenHelper(context, name,
null , VERSION ) {
companion object {
1
} Y
} Z
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 {
1
} Y
} Z
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 {
1
} Y
} Z
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 {
1
} Y
} Z
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A user
_id
name
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A object FriendshipColumns {
"friendship"
"friend1"
"friend2"
"became_friends" } B
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A object FriendshipColumns {
"friendship"
"friend1"
"friend2"
"became_friends" } B
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A object FriendshipColumns {
"friendship"
"friend1"
"friend2"
"became_friends" } B object CheckinColumns {
"checkin"
"_id"
"name"
"time" } C
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A object FriendshipColumns {
"friendship"
"friend1"
"friend2"
"became_friends" } B object CheckinColumns {
"checkin"
"_id"
"name"
"time" } C
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A object FriendshipColumns {
"friendship"
"friend1"
"friend2"
"became_friends" } B object CheckinColumns {
"checkin"
"_id"
"name"
"time" } C object UserCheckinColumns {
"user_checkin"
"checkin_id"
"user_id" } D
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 {
1
} Y
} Z object UserColumns {
"user"
"_id"
"name" } A object FriendshipColumns {
"friendship"
"friend1"
"friend2"
"became_friends" } B object CheckinColumns {
"checkin"
"_id"
"name"
"time" } C object UserCheckinColumns {
"user_checkin"
"checkin_id"
"user_id" } D
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
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
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
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
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
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
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
SQLite and Android • Strings... Strings everywhere...
SQLite and Android • Strings... Strings everywhere... • No query or type safety
SQLite and Android • Strings... Strings everywhere... • No query or type safety • Prohibitive level of verbosity
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
SQLDelight & Room
SQLDelight Room
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 {
new TodoListModel.Factory<>(AutoValue_TodoList:: new ); }
@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
SQLDelight Room • No restriction on Java or SQL type
SQLDelight Room • No restriction on Java or SQL type • No restriction on Java type ( @Ignore )
SQLDelight Room • No restriction on Java or SQL type • No inheritance or other OOP • No restriction on Java type ( @Ignore )
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
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
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
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 {
new Factory<>(AutoValue_TodoItem:: new ); }
@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
SQLDelight insertList: INSERT INTO todo_list (name) VALUES (?); private val insertList : InsertList by lazy {
InsertList( db . writableDatabase ) }
... db . bindAndExecute ( insertList ) { bind(name) }
@Insert fun insert(list: TodoList) Room listDao .insert(TodoList( name = name))
SQLDelight Room • Can’t insert an object • Can only insert objects
SQLDelight Room • Can’t insert an object • Verbose - requires you maintain the cache of mutator queries • Can only insert objects
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
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 ; }
@Query ( "" +
"SELECT name, count(*) AS count
" +
"FROM todo_list
" +
"LEFT JOIN todo_item ON (todo_list._id = todo_list_id)
" +
"WHERE todo_list._id = :todoListId AND complete = 0
" +
"GROUP BY todo_list._id" ) fun titleAndCount(todoListId: Long): Flowable<TitleAndCount> Room data class TitleAndCount(
val name : String,
val count : Int )
SQLDelight Room • “Not sure how to convert a Cursor to this method's return type ”
SQLDelight Room • “Not sure how to convert a Cursor to this method's return type ” • Not
type safe
Not
type safe data class TitleAndCount(
val name : String,
val
count
: Int
)
Y
println
(
name
)
// Grocery List
println
(
count
)
// 4
Not
type safe data class TitleAndCount(
val name : String,
val count : Int ) Y
Not type safe data class TitleAndCount(
val names : String,
val count : Int ) Y java.lang.IllegalArgumentException: Parameter specified as non-null is null
Int
Not
type safe data class TitleAndCount(
val name : String,
val count : Int ) Y
Not type safe data class TitleAndCount(
val name : Int,
val
count
: Int
)
Y
println
(
name
)
// 0
println
(
count
)
// 4
Room itemDao .titleAndCount( listId ) .observeOn(AndroidSchedulers.mainThread()) .subscribe { titleAndCount ->
TODO ()
}
SQLDelight db . createQuery (TodoItem. FACTORY .titleAndCount( listId )) .mapToOne(TitleAndCount. MAPPER ::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe {
TODO ()
} X
TodoItem.
FACTORY .titleAndCountMapper( CREATOR );
SQLDelight db . createQuery (TodoItem. FACTORY .titleAndCount( listId )) .mapToOne(TitleAndCount. MAPPER ::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe {
TODO ()
} X
Not type safe db . createQuery (TodoItem. FACTORY .titleAndCount( listId ))
// Wreak havoc
.mapToOne(TitleAndCount.
MAPPER ::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe {
TODO ()
} X
SQLDelight
SQLDelight Room • “Not sure how to convert a Cursor to this method's return type ” • Not type safe
SQLDelight Room • Verbose calling code • “Not sure how to convert a Cursor to this method's return type ” • Not type safe
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
SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes
SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes • Compiler error == IDE error
SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete
SQLDelight Room • Full DDL/DML support • Views, Triggers, Indexes • Compiler error == IDE error • Better tooling • Find usages, refactoring, auto complete • Migration testing utilities
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
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
SQLDelight Room
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
Room • Unless you have a reason to otherwise, use Room
Room • Unless you have a reason to otherwise, use Room • Support and documentation is way better
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
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
SQLDelight • Spending a lot of time in SQLite → Better tooling
SQLDelight • Spending a lot of time in SQLite → Better tooling • Complicated models → Type Safety
SQLDelight • Spending a lot of time in SQLite → Better tooling • Complicated models → Type Safety • Complicated client backend → Views, Triggers, Virtual Tables, Inserts
SQLDelight 1.0 • working-kotlin branch on GitHub
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 {
new TodoListModel.Factory<>(AutoValue_TodoList:: new ); }
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 );
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
SQLDelight 1.0 insertList: INSERT INTO todo_list (name) VALUES (?); db .insertList( name)
db . createQuery (TodoItem. FACTORY .titleAndCount( listId )) .mapToOne(TitleAndCount. MAPPER ::map) .observeOn(AndroidSchedulers.mainThread()) .subscribe {
TODO ()
} X
TodoItem.
FACTORY .titleAndCountMapper( CREATOR ); SQLDelight pre-1.0 .observe()
SQLDelight 1.0 db .titleAndCount( listId ).observe() .mapToOne()
.observeOn(AndroidSchedulers.mainThread())
.subscribe
{
TODO ()
} X
public static final RowMapper<TodoItem> MAPPER = TodoItem. FACTORY .titleAndCountMapper( CREATOR );
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>
SQLDelight 1.0 • working-kotlin branch on GitHub
SQLDelight 1.0 • working-kotlin branch on GitHub • Increase precision of observable emissions • Only possible because SQLDelight is a compiler
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
Future of SQLite on Android • SupportSQLite
SupportSQLite SQLiteDatabase SQLiteOpenHelper SQLiteProgram SQLiteStatement
SupportSQLite SupportSQLiteDatabase SupportSQLiteOpenHelper SupportSQLiteProgram SupportSQLiteStatement
SupportSQLite SupportSQLiteDatabase SupportSQLiteOpenHelper SupportSQLiteProgram SupportSQLiteStatement SupportSQLiteQuery
SupportSQLiteDatabase.query(supportQuery)
SupportSQLite api 'android.arch.persistence:db:1.0.0-beta1'
SupportSQLite api 'android.arch.persistence:db:1.0.0-beta1'
implementation 'android.arch.persistence:db-framework :1.0.0-beta1'
Future of SQLite on Android • SupportSQLite • Paging
Paging • Enables efficient paging of large data sources
Paging • Enables efficient paging of large data sources • Not tied to SQL, Room, or RecyclerView
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>
SELECT * FROM persistence_solution WHERE type != 'flat'
AND type != 'ORM'
AND type != 'ObjectDB'
https://goo.gl/aYQnfh @Strongolopolis & @JakeWharton
The Resurgence of SQL
https://goo.gl/aYQnfh @Strongolopolis & @JakeWharton The Resurgence of SQL
