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