Upgrade to Pro — share decks privately, control downloads, hide ads and more …

The Resurgence of SQL (Droidcon NYC 2017)

The Resurgence of SQL (Droidcon NYC 2017)

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

Video: https://youtu.be/4eUuD7LsqMs

Jake Wharton
PRO

September 25, 2017
Tweet

More Decks by Jake Wharton

Other Decks in Programming

Transcript

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

    View Slide

  2. Why Persistence?

    View Slide

  3. Why Persistence?

    View Slide

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

    View Slide

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

    View Slide

  6. Why Persistence?

    View Slide

  7. Flat Files

    View Slide

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

    View Slide

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

    Shrek



    View Slide

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

    Shrek



    View Slide

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

    Shrek




    View Slide

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

    Shrek




    View Slide

  13. Flat Files
    val sharedPreferences =
    context.getSharedPreferences("user123", MODE_PRIVATE)
    sharedPreferences.edit()
    .putString("title", "Shrek")
    .putInt("year", 2001)
    .putFloat("rating", 10.0f)
    .apply()
    sharedPreferences.edit()
    .putInt("volume", 8)
    .apply()

    Shrek




    View Slide

  14. Flat Files
    val sharedPreferences =
    context.getSharedPreferences("user123", MODE_PRIVATE)
    sharedPreferences.edit()
    .putString("title", "Shrek")
    .putInt("year", 2001)
    .putFloat("rating", 10.0f)
    .apply()
    sharedPreferences.edit()
    .putInt("volume", 8)
    .apply()

    Shrek




    View Slide

  15. Flat Files
    val sharedPreferences =
    context.getSharedPreferences("user123", MODE_PRIVATE)
    sharedPreferences.edit()
    .putString("title", "Shrek")
    .putInt("year", 2001)
    .putFloat("rating", 10.0f)
    .apply()
    sharedPreferences.edit()
    .putInt("volume", 8)
    .apply()

    Shrek




    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    "age":20,"email":"[email protected]"}]}Z

    View Slide

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

    “age":20,"email":"[email protected]"}]}Z
    Flat Files

    View Slide

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

    “age":20,"email":"[email protected]"}]}Z
    Flat Files

    View Slide

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

    “age":20,"email":"[email protected]"}]}Z

    View Slide

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

    “age":20,"email":"[email protected]"}]}ZZ
    Flat Files

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    .observeOn(mainThread())

    View Slide

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

    .observeOn(mainThread())

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  34. Object DBs
    data class User(
    var name: String,
    var age: Int,
    var email: String,
    var friends: List = emptyList()
    ) : MagicObject()
    // Users who are 20 or older:
    db.where(User::class.java).greaterThanEqualTo("age", 20).findList()
    // Users with 3 or more friends:
    // Query all users, count and filter in code :(

    View Slide

  35. Object DBs
    data class User(
    var name: String,
    var age: Int,
    var email: String,
    var friends: List = emptyList()
    ) : MagicObject()
    // Users who are 20 or older:
    db.where(User::class.java).greaterThanEqualTo("age", 20).findList()
    // Users with 3 or more friends:
    // Query all users, count and filter in code :(
    // Users friends by Bob (transitively)
    // Query friends, friends of friends and combine in code :(

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  40. ORMs
    // Find your friend’s checkins
    val me = session.createCritera(User::class.java)
    .add(eq("id", MY_ID)).list().first()
    val checkins = session.createCritera(Checkin::class.java)
    .add(eq("users.name", me.friends))

    View Slide

  41. ORMs
    // Find your friend’s checkins
    val me = session.createCritera(User::class.java)
    .add(eq("id", MY_ID)).list().first()
    val checkins = session.createCritera(Checkin::class.java)
    .add(eq("users.name", me.friends))

    View Slide

  42. ORMs
    // Find your friend’s checkins
    val me = session.createCritera(User::class.java)
    .add(eq("id", MY_ID)).list().first()
    val checkins = session.createCritera(Checkin::class.java)
    .add(eq("users.name", me.friends))
    @Entity
    data class User(
    @Id @GeneratedValue(strategy = AUTO)
    val id: Long,
    val name: String,
    @ManyToMany
    val friends: Set
    )X

    View Slide

  43. • Data Definition Language (DDL)
    SQL

    View Slide

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

    View Slide

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

    View Slide

  46. CREATE TABLE user (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
    );
    Data Definition
    CREATE TABLE friendship (
    friend1 INTEGER NOT NULL REFERENCES user,
    friend2 INTEGER NOT NULL REFERENCES user,
    became_friends INTEGER NOT NULL DEFAULT CURRENT_TIME,
    PRIMARY KEY (friend1, friend2)
    );

    View Slide

  47. CREATE TABLE checkin (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    time INTEGER NOT NULL
    );
    Data Definition
    CREATE TABLE user_checkin (
    checkin_id INTEGER NOT NULL REFERENCES checkin,
    user_id INTEGER NOT NULL REFERENCES user,
    PRIMARY KEY(checkin_id, user_id)
    );

    View Slide

  48. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }
    Data Manipulation

    View Slide

  49. Data Manipulation

    View Slide

  50. Data Manipulation

    View Slide

  51. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }
    Data Manipulation

    View Slide

  52. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    friendship

    View Slide

  53. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    friendship
    FROM

    View Slide

  54. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM friendship
    friend1 = MY_ID

    View Slide

  55. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM friendship
    friend1 = MY_ID
    WHERE

    View Slide

  56. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM friendship
    friend1 = :my_id
    WHERE

    View Slide

  57. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM friendship
    WHERE friend1 = :my_id
    friend2

    View Slide

  58. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    friend2
    FROM friendship
    WHERE friend1 = :my_id
    SELECT

    View Slide

  59. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id

    View Slide

  60. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    friendship
    friend2 = MY_ID
    friend1
    :my_id

    View Slide

  61. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    friendship
    FROM
    friend2 = MY_ID
    friend1
    :my_id

    View Slide

  62. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    FROM friendship
    friend2 = :my_id
    WHERE
    friend1 MY_ID

    View Slide

  63. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    friend1
    FROM friendship
    WHERE friend2 = :my_id
    SELECT

    View Slide

  64. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    +

    View Slide

  65. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    +
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id

    View Slide

  66. fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id

    View Slide

  67. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    var friends = friendships
    .filter { it.friend1 == MY_ID }
    .map { it.friend2 }
    friends += friendships
    .filter { it.friend2 == MY_ID }
    .map { it.friend1 }
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    Data Manipulation

    View Slide

  68. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    Data Manipulation

    View Slide

  69. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friendships: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    Data Manipulation

    View Slide

  70. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    Data Manipulation
    F
    r
    i
    e
    n
    d
    s
    h
    i
    p

    View Slide

  71. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    Data Manipulation

    View Slide

  72. Data Manipulation

    View Slide

  73. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    Data Manipulation

    View Slide

  74. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    user_checkin
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id

    View Slide

  75. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM user_checkin
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id

    View Slide

  76. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM user_checkin
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    IN
    user_id in friends

    View Slide

  77. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    f
    r
    i
    e
    n
    d
    s

    View Slide

  78. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    checkin_id

    View Slide

  79. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    checkin_id
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    SELECT

    View Slide

  80. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT checkin_id
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    distinct
    DISTINCTP

    View Slide

  81. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT DISTINCTPcheckin_id
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    distinct

    View Slide

  82. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT DISTINCTPcheckin_id
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    size
    count

    View Slide

  83. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT
    count(DISTINCTPcheckin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    size

    View Slide

  84. my_id
    my_id
    fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    return checkins
    .filter { it.user_id in friends }
    .map { it.checkin_id }
    .distinct()
    .size()
    }X
    Data Manipulation
    SELECT
    count(DISTINCTPcheckin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    size

    View Slide

  85. fun friendsCheckins(
    checkins: Collection,
    friends: Collection
    ): Long {
    }X
    Data Manipulation
    SELECT
    count(DISTINCTPcheckin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    size

    View Slide

  86. Data Manipulation in SQL!
    SELECT count(DISTINCTPcheckin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    in
    size

    View Slide

  87. SQL

    View Slide

  88. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    SQL

    View Slide

  89. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    SQL

    View Slide

  90. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    SQL

    View Slide

  91. SQL

    View Slide

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

    View Slide

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

    View Slide

  94. • Save DB file to external storage and pull with adb
    • Stetho
    • View DB file with a SQLite browser (sqlitebrowser)
    • Run queries to learn more
    Debugging

    View Slide

  95. • Save DB file to external storage and pull with adb
    • Stetho
    • View DB file with a SQLite browser (sqlitebrowser)
    • Run queries to learn more
    • EXPLAIN QUERY PLAN
    Debugging

    View Slide

  96. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    EXPLAIN QUERY PLAN

    View Slide

  97. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    EXPLAIN QUERY PLAN

    View Slide

  98. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"

    View Slide

  99. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    Where is this instruction in the FROM clause

    View Slide

  100. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )
    Full table scan

    View Slide

  101. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )
    Full table scan
    Search a subset using

    an index

    View Slide

  102. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    The nesting order of this instruction

    View Slide

  103. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"

    View Slide

  104. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"

    View Slide

  105. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"

    View Slide

  106. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"

    View Slide

  107. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"
    "0" "1" "1"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=? AND friend2=?)"

    View Slide

  108. EXPLAIN QUERY PLAN
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    JOIN friendship ON (
    (user_id = friend1 AND friend2 = :my_id) OR
    (user_id = friend2 AND friend1 = :my_id)
    )Y
    EXPLAIN QUERY PLAN

    View Slide

  109. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    EXPLAIN QUERY PLAN

    View Slide

  110. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  111. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )
    The subquery ID for the instruction

    View Slide

  112. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y

    View Slide

  113. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    Can either be EXECUTE or

    EXECUTE CORRELATED

    View Slide

  114. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  115. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  116. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  117. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  118. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y

    View Slide

  119. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  120. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  121. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    EXPLAIN QUERY PLAN

    View Slide

  122. • Subquery is stored, not correlated
    EXPLAIN QUERY PLAN

    View Slide

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

    View Slide

  124. • Subquery is stored, not correlated
    • No order of depth, there is only a single scan through the checkin table
    • Scans could be searches if we created an index manually
    EXPLAIN QUERY PLAN

    View Slide

  125. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    EXPLAIN QUERY PLAN

    View Slide

  126. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    View Slide

  127. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    user_id

    View Slide

  128. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    user_id
    CREATE INDEX userIdIndex
    ON user_checkin(user_id);

    View Slide

  129. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    userIdIndex
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    CREATE INDEX userIdIndex
    ON user_checkin(user_id);
    friend2

    View Slide

  130. selectid order from detail
    "0" "0" "0" "SCAN TABLE user_checkin"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SCAN TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"

    friend2Index
    userIdIndex
    SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    CREATE INDEX userIdIndex
    ON user_checkin(user_id);
    friend2

    CREATE INDEX friend2Index
    ON friendship(friend2);

    View Slide

  131. SELECT count(DISTINCT checkin_id)
    FROM user_checkin
    WHERE user_id IN (
    SELECT friend2
    FROM friendship
    WHERE friend1 = :my_id
    UNION
    SELECT friend1
    FROM friendship
    WHERE friend2 = :my_id
    )Y
    selectid order from detail
    "0" "0" "0"
    "SEARCH TABLE user_checkin USING INDEX
    (user_id=?)"
    "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
    "2" "0" "0"
    "SEARCH TABLE friendship USING COVERING INDEX
    sqlite_autoindex_friendship_1 (friend1=?)"
    "3" "0" "0"
    "SEARCH TABLE friendship USING INDEX
    (friend2=?)"
    "1" "0" "0" "COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)"
    CREATE INDEX userIdIndex
    ON user_checkin(user_id);
    CREATE INDEX friend2Index
    ON friendship(friend2);

    friend2Index
    userIdIndex

    View Slide

  132. SQL

    View Slide

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

    View Slide

  134. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z

    View Slide

  135. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z

    View Slide

  136. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE user (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z

    View Slide

  137. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE user (Z
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A

    View Slide

  138. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (Z
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    user
    _id
    name

    View Slide

  139. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (Z
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    object FriendshipColumns {
    const val TABLE_NAME = "friendship"
    const val FRIEND_1 = "friend1"
    const val FRIEND_2 = "friend2"
    const val BECAME_FRIENDS = "became_friends"
    }B

    View Slide

  140. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (Z
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${FriendshipColumns.TABLE_NAME} (
    ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME,
    PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2})
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    object FriendshipColumns {
    const val TABLE_NAME = "friendship"
    const val FRIEND_1 = "friend1"
    const val FRIEND_2 = "friend2"
    const val BECAME_FRIENDS = "became_friends"
    }B

    View Slide

  141. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (Z
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${FriendshipColumns.TABLE_NAME} (
    ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME,
    PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2})
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    object FriendshipColumns {
    const val TABLE_NAME = "friendship"
    const val FRIEND_1 = "friend1"
    const val FRIEND_2 = "friend2"
    const val BECAME_FRIENDS = "became_friends"
    }B

    object CheckinColumns {
    const val TABLE_NAME = "checkin"
    const val ID = "_id"
    const val NAME = "name"
    const val TIME = "time"
    }C

    View Slide

  142. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (Z
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${FriendshipColumns.TABLE_NAME} (
    ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME,
    PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2})
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${CheckinColumns.TABLE_NAME} (
    ${CheckinColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${CheckinColumns.NAME} TEXT NOT NULL,
    ${CheckinColumns.TIME} INTEGER NOT NULL
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    object FriendshipColumns {
    const val TABLE_NAME = "friendship"
    const val FRIEND_1 = "friend1"
    const val FRIEND_2 = "friend2"
    const val BECAME_FRIENDS = "became_friends"
    }B

    object CheckinColumns {
    const val TABLE_NAME = "checkin"
    const val ID = "_id"
    const val NAME = "name"
    const val TIME = "time"
    }C

    View Slide

  143. SQLite and Android
    class MyDatabase(context: Context, name: String?)
    : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (Z
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${FriendshipColumns.TABLE_NAME} (
    ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME,
    PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2})
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${CheckinColumns.TABLE_NAME} (
    ${CheckinColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${CheckinColumns.NAME} TEXT NOT NULL,
    ${CheckinColumns.TIME} INTEGER NOT NULL
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    object FriendshipColumns {
    const val TABLE_NAME = "friendship"
    const val FRIEND_1 = "friend1"
    const val FRIEND_2 = "friend2"
    const val BECAME_FRIENDS = "became_friends"
    }B

    object CheckinColumns {
    const val TABLE_NAME = "checkin"
    const val ID = "_id"
    const val NAME = "name"
    const val TIME = "time"
    }C
    object UserCheckinColumns {
    const val TABLE_NAME = "user_checkin"
    const val CHECKIN_ID = "checkin_id"
    const val USER_ID = "user_id"
    }D

    View Slide

  144. SQLite and Android
    class MyDatabase(context: Context, name: String?) : SQLiteOpenHelper(context, name, null, VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("""
    CREATE TABLE ${UserColumns.TABLE_NAME} (
    ${UserColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${UserColumns.NAME} TEXT NOT NULL
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${FriendshipColumns.TABLE_NAME} (
    ${FriendshipColumns.FRIEND_1} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.FRIEND_2} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    ${FriendshipColumns.BECAME_FRIENDS} INTEGER NOT NULL DEFAULT CURRENT_TIME,
    PRIMARY KEY (${FriendshipColumns.FRIEND_1}, ${FriendshipColumns.FRIEND_2})
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${CheckinColumns.TABLE_NAME} (
    ${CheckinColumns.ID} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    ${CheckinColumns.NAME} TEXT NOT NULL,
    ${CheckinColumns.TIME} INTEGER NOT NULL
    );
    """.trimIndent())
    db.execSQL("""
    CREATE TABLE ${UserCheckinColumns.TABLE_NAME} (
    ${UserCheckinColumns.CHECKIN_ID} INTEGER NOT NULL REFERENCES ${CheckinColumns.TABLE_NAME},
    ${UserCheckinColumns.USER_ID} INTEGER NOT NULL REFERENCES ${UserColumns.TABLE_NAME},
    PRIMARY KEY(${UserCheckinColumns.CHECKIN_ID}, ${UserCheckinColumns.USER_ID})
    );
    """.trimIndent())
    }W
    override fun onUpgrade(db: SQLiteDatabase, old: Int, new: Int) {
    }X
    companion object {
    private const val VERSION = 1
    }Y
    }Z
    object UserColumns {
    const val TABLE_NAME = "user"
    const val ID = "_id"
    const val NAME = "name"
    }A
    object FriendshipColumns {
    const val TABLE_NAME = "friendship"
    const val FRIEND_1 = "friend1"
    const val FRIEND_2 = "friend2"
    const val BECAME_FRIENDS = "became_friends"
    }B

    object CheckinColumns {
    const val TABLE_NAME = "checkin"
    const val ID = "_id"
    const val NAME = "name"
    const val TIME = "time"
    }C
    object UserCheckinColumns {
    const val TABLE_NAME = "user_checkin"
    const val CHECKIN_ID = "checkin_id"
    const val USER_ID = "user_id"
    }D

    View Slide

  145. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Cursor? {
    return db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID})
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )
    """.trimIndent(), arrayOf(myId.toString()))
    }Z

    View Slide

  146. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Cursor? {
    return db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID})
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )
    """.trimIndent(), arrayOf(myId.toString()))
    }Z

    View Slide

  147. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Cursor? {A
    return db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID})
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )B
    """.trimIndent(), arrayOf(myId.toString()))
    }Z

    View Slide

  148. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A
    return db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID})
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )B
    """.trimIndent(), arrayOf(myId.toString()))
    }Z

    View Slide

  149. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A
    val cursor = db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID})
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )B
    """.trimIndent(), arrayOf(myId.toString()))
    cursor.use {
    if (it.moveToNext()) {
    return it.getInt(0)G
    }J
    throw IllegalStateException("Query returned zero rows")
    }K
    }Z

    View Slide

  150. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A
    val count = "checkin_count"
    val cursor = db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) AS $count
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )B
    """.trimIndent(), arrayOf(myId.toString()))
    cursor.use {
    if (it.moveToNext()) {
    return it.getInt(it.getColumnIndex(count))G
    }J
    throw IllegalStateException("Query returned zero rows")
    }K
    }Z

    View Slide

  151. SQLite and Android
    fun friendsCheckins(db: SQLiteDatabase, myId: Int): Int {A
    val count = "checkin_count"
    val cursor = db.rawQuery("""
    SELECT count(DISTINCT ${UserCheckinColumns.CHECKIN_ID}) AS $count
    FROM ${UserCheckinColumns.TABLE_NAME}
    WHERE ${UserCheckinColumns.USER_ID} IN (
    SELECT ${FriendshipColumns.FRIEND_2}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_1} = ?1
    UNION
    SELECT ${FriendshipColumns.FRIEND_1}
    FROM ${FriendshipColumns.TABLE_NAME}
    WHERE ${FriendshipColumns.FRIEND_2} = ?1
    )B
    """.trimIndent(), arrayOf(myId.toString()))
    cursor.use {
    if (it.moveToNext()) {
    return it.getInt(it.getColumnIndex(count))G
    }J
    throw IllegalStateException("Query returned zero rows")
    }K
    }Z

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  155. SQLite and Android
    • Strings... Strings everywhere...
    • No query or type safety
    • Prohibitive level of verbosity
    • Reactive updates only achievable through a ContentProvider
    • Another level of hell, omitted for sanity

    View Slide

  156. SQLDelight & Room

    View Slide

  157. SQLDelight Room

    View Slide

  158. SQLDelight
    CREATE TABLE todo_list (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    archived INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    @AutoValue
    public abstract class TodoList implements Parcelable, TodoListModel {
    public static final Factory FACTORY =
    new TodoListModel.Factory<>(AutoValue_TodoList::new);
    }

    View Slide

  159. @Entity(tableName = "todo_list")
    data class TodoList(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    val id: Int = 0,
    val name: String,
    val archived: Boolean = false
    )
    Room

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  163. SQLDelight Room
    • No restriction on Java or SQL type
    • No inheritance or other OOP
    • No restriction on Java type (@Ignore)
    • Subset of SQLite supported
    • UNIQUE, CHECK, DEFAULT

    View Slide

  164. SQLDelight Room
    • No restriction on Java or SQL type
    • No inheritance or other OOP
    • Doesn't play well with Kotlin data
    classes
    • No restriction on Java type (@Ignore)
    • Subset of SQLite supported
    • UNIQUE, CHECK, DEFAULT

    View Slide

  165. SQLDelight Room
    • No restriction on Java or SQL type
    • No inheritance or other OOP
    • Doesn't play well with Kotlin data
    classes
    • No restriction on Java type (@Ignore)
    • Subset of SQLite supported
    • UNIQUE, CHECK, DEFAULT
    • Doesn't work with AutoValue

    View Slide

  166. SQLDelight
    CREATE TABLE todo_item (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    todo_list_id INTEGER NOT NULL REFERENCES todo_list,
    description TEXT NOT NULL,
    complete INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    createListIdIndex:
    CREATE INDEX item_list_id ON todo_item(todo_list_id);
    @AutoValue
    public abstract class TodoItem implements TodoItemModel, Parcelable {
    public static final Factory FACTORY =
    new Factory<>(AutoValue_TodoItem::new);
    }

    View Slide

  167. @Entity(
    tableName = "todo_item",
    foreignKeys = arrayOf(ForeignKey(
    entity = TodoItem::class,
    parentColumns = arrayOf("_id"),
    childColumns = arrayOf("todo_list_id")
    ))
    )
    data class TodoItem(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    val id: Long,
    @ColumnInfo(name = "todo_list_id", index = true)
    val todoListId: Long,
    val description: String,
    val complete: Boolean = false
    )
    Room

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  172. SQLDelight Room
    • Can’t insert an object
    • Verbose - requires you maintain the
    cache of mutator queries
    • Can only insert objects
    • Can’t use DAO’s during creation

    View Slide

  173. SQLDelight
    titleAndCount:
    SELECT name, count(todo_item._id)
    FROM todo_list
    LEFT JOIN todo_item ON (todo_list._id = todo_list_id)
    WHERE todo_list._id = ? AND complete = 0
    GROUP BY todo_list._id;
    @AutoValue
    public abstract class TitleAndCount implements TitleAndCountModel {
    public static final TitleAndCountCreator CREATOR
    = AutoValue_TitleAndCount::new;
    }

    View Slide

  174. @Query("" +
    "SELECT name, count(*) AS count\n" +
    "FROM todo_list\n" +
    "LEFT JOIN todo_item ON (todo_list._id = todo_list_id)\n" +
    "WHERE todo_list._id = :todoListId AND complete = 0\n" +
    "GROUP BY todo_list._id"
    )
    fun titleAndCount(todoListId: Long): Flowable
    Room
    data class TitleAndCount(
    val name: String,
    val count: Int
    )

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  188. SQLDelight Room
    • Verbose calling code
    • SQLBrite - SQLDelight bridge not type
    safe
    • “Not sure how to convert a Cursor to
    this method's return type”
    • Not type safe

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  195. SQLDelight Room

    View Slide

  196. SQLDelight Room
    • Associate a java type to a column
    definition and receive type safe
    projections and mutation apis.
    • Define a table in java and serialize
    java objects to and from a query

    View Slide

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

    View Slide

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

    View Slide

  199. Room
    • Unless you have a reason to otherwise, use Room
    • Support and documentation is way better
    • More than enough sqlite support to get the benefits you need

    View Slide

  200. Room
    • Unless you have a reason to otherwise, use Room
    • Support and documentation is way better
    • More than enough sqlite support to get the benefits you need
    • API feels familiar and simple — akin to Retrofit

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  204. SQLDelight 1.0
    • working-kotlin branch on GitHub

    View Slide

  205. SQLDelight pre-1.0
    CREATE TABLE todo_list (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    archived INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    @AutoValue
    public abstract class TodoList implements Parcelable, TodoListModel {
    public static final Factory FACTORY =
    new TodoListModel.Factory<>(AutoValue_TodoList::new);
    }

    View Slide

  206. SQLDelight 1.0
    CREATE TABLE todo_list (
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    archived INTEGER AS Boolean NOT NULL DEFAULT 0
    );

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  210. SQLDelight 1.0
    db.titleAndCount(listId).observe()

    .mapToOne()
    .observeOn(AndroidSchedulers.mainThread())
    .subscribe {
    TODO()
    }X
    public static final RowMapper MAPPER =
    TodoItem.FACTORY.titleAndCountMapper(CREATOR);

    View Slide

  211. SQLDelight 1.0
    db.titleAndCount(::CustomType, listId).observe()

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

    View Slide

  212. SQLDelight 1.0
    • working-kotlin branch on GitHub

    View Slide

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

    View Slide

  214. SQLDelight 1.0
    • working-kotlin branch on GitHub
    • Increase precision of observable emissions
    • Only possible because SQLDelight is a compiler
    • Embrace Kotlin as the future

    View Slide

  215. Future of SQLite on Android
    • SupportSQLite

    View Slide

  216. SupportSQLite
    SQLiteDatabase
    SQLiteOpenHelper
    SQLiteProgram
    SQLiteStatement

    View Slide

  217. SupportSQLite
    SupportSQLiteDatabase
    SupportSQLiteOpenHelper
    SupportSQLiteProgram
    SupportSQLiteStatement

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  221. Future of SQLite on Android
    • SupportSQLite
    • Paging

    View Slide

  222. Paging
    • Enables efficient paging of large data sources

    View Slide

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

    View Slide

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


    @Query("select * from users WHERE age > :age order by name DESC")

    fun usersOlderThan(age: Int): TiledDataSource

    View Slide

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

    View Slide

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

    View Slide