Have you ever heard the story of SQL the Delight?

Talk about SQLDelight from DroidYangon 2019

Check source code at https://github.com/vincent-paing/DroidYangonSQL

Transcript

1. Have you ever heard the
   story of SQL the Delight?
   Photo by Alexander Andrews on Unsplash
   

   View Slide

2. View Slide

3. History of SQLite in Android
   - Content Provider + Raw SQL
   - Object-Relational Mapper (ORMs)
   - Room
   

   View Slide

4. Raw SQL
   

   View Slide

5. Raw SQL
   class BallotMapper {
   fun toContentValues(ballot: Ballot): ContentValues {
   val contentValues = ContentValues()
   contentValues.put("id", ballot.id)
   contentValues.put("image", ballot.image)
   if (ballot.isValid) {
   contentValues.put("isValid", 1)
   } else {
   contentValues.put("isValid", 0)
   }
   return contentValues
   }
   fun fromCursor(cursor: Cursor): Ballot {
   val id = cursor.getLong(cursor.getColumnIndex("id"))
   val imageUrl = cursor.getString(cursor.getColumnIndex("image"))
   val isValid = cursor.getInt(cursor.getColumnIndex("isValid")) == 1
   return Ballot(
   id = id,
   image = imageUrl,
   isValid = isValid
   )
   }
   }
   

   View Slide

6. Raw SQL ✅
   - POJO not required
   - Flexibility
   - High level of abstraction
   

   View Slide

7. Raw SQL ❌
   - Not time efficient
   - Hard to understand
   - I/O controls
   

   View Slide

8. Object-Relational Mapper (ORM)
   - ORMlite
   - SQLiteMagic
   - GreenDAO
   - Active Android
   

   View Slide

9. ORM
   @DatabaseTable(tableName = "ballot")
   data class BallotOrmLiteEntity(
   @DatabaseField(generatedId = false, columnName = "id")
   var id: Long? = null,
   @DatabaseField(columnName = "image")
   var image: String? = null,
   @DatabaseField(columnName = "isValid")
   var isValid: Boolean? = null
   )
   

   View Slide

10. ORM
    override fun getValidBallots(): List {
    return ballotDao
    .queryForEq("isValid'", true)
    .map {
    Ballot(
    id = it.id!!,
    image = it.image!!,
    isValid = it.isValid!!
    )
    }
    }
    

    View Slide

11. ORM ✅
    - Fast implementation
    - Reduce cost
    

    View Slide

12. ORM ❌
    - POJO
    - Hard to debug
    - No Abstraction
    

    View Slide

13. Object Database
    - Realm
    - Objectbox
    

    View Slide

14. Object Database
    open class BallotRealmEntity : RealmObject() {
    @PrimaryKey var id: Long = 0
    var image: String = ""
    var isValidBallot: Boolean = true
    }
    

    View Slide

15. Object Database
    override fun getValidBallots(): List {
    return realm.where(BallotRealmEntity::class.java)
    .equalTo("isValidBallot", true)
    .findAll()
    .map {
    Ballot(
    id = it.id,
    image = it.image,
    isValid = it.isValidBallot
    )
    }
    }
    

    View Slide

16. Room
    - Announced in 2017 Google I/O
    - 2.0.0 in 2018
    - Officially recommended approach
    

    View Slide

17. Room
    @Entity(tableName = "ballots")
    data class BallotRoomEntity(
    @PrimaryKey val id: Long,
    @ColumnInfo(name = "name") val image: String,
    @ColumnInfo(name = "is_valid") val isValid: Boolean
    )
    @Dao
    interface BallotDao {
    @Query("SELECT * FROM ballots WHERE is_valid = :isValid")
    fun getValidBallots(isValid: Boolean): List
    }
    

    View Slide

18. SQLDelight
    

    View Slide

19. SQLDelight
    - Since 2016
    - Version 1.0.0 in Jan-2019
    

    View Slide

20. SQLDelight
    - Write sql(.sq) file
    - Generate Kotlin code
    - Reverse of ORM
    - Two parts
    - Gradle plugin
    - Drivers
    

    View Slide

21. SQLDelight
    buildscript {
    repositories {
    google()
    mavenCentral()
    }
    dependencies {
    classpath 'com.squareup.sqldelight:gradle-plugin:1.1.3'
    }
    }
    apply plugin: 'com.squareup.sqldelight'
    

    View Slide

22. SQLDelight
    sqldelight {
    ExampleDb {
    packageName = "com.example.sqldelight"
    sourceFolders = ["sqldelight"]
    schemaOutputDirectory = file("build/sqldelight")
    }
    }
    

    View Slide

23. SQLDelight
    sqldelight {
    ExampleDb {
    packageName = "com.example.sqldelight"
    sourceFolders = ["sqldelight"]
    schemaOutputDirectory = file("build/sqldelight")
    }
    }
    

    View Slide

24. SQLDelight
    sqldelight {
    ExampleDb {
    packageName = "com.example.sqldelight"
    sourceFolders = ["sqldelight"]
    schemaOutputDirectory = file("build/sqldelight")
    }
    }
    

    View Slide

25. SQLDelight
    sqldelight {
    ExampleDb {
    packageName = "com.example.sqldelight"
    sourceFolders = ["sqldelight"]
    schemaOutputDirectory = file("build/sqldelight")
    }
    }
    

    View Slide

26. SQLDelight
    package com.example.sqldelight
    ...
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    

    View Slide

27. SQLDelight
    package com.example.sqldelight
    ...
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    

    View Slide

28. SQLDelight
    package com.example.sqldelight
    ...
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    

    View Slide

29. SQLDelight
    package com.example.sqldelight
    ...
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    

    View Slide

30. SQLDelight
    package com.example.sqldelight.sqldelight
    import ...
    internal val KClass.schema: SqlDriver.Schema
    get() = ExampleDbImpl.Schema
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    

    View Slide

31. SQLDelight
    package com.example.sqldelight.sqldelight
    import ...
    internal val KClass.schema: SqlDriver.Schema
    get() = ExampleDbImpl.Schema
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    

    View Slide

32. SQLDelight
    package com.example.sqldelight.sqldelight
    import ...
    internal val KClass.schema: SqlDriver.Schema
    get() = ExampleDbImpl.Schema
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    

    View Slide

33. Schema Definition
    

    View Slide

34. CREATE TABLE Ballot(
    id INTEGER PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    Schema Definition
    

    View Slide

35. package com.aungkyawpaing.droidyangonsql.data.sqldelight
    import ...
    interface Ballot {
    val id: Long
    val name: String
    val is_valid: Boolean
    data class Impl(
    override val id: Long,
    override val image: String,
    override val is_valid: Boolean
    ) : Ballot {
    override fun toString(): String = """
    |Ballot.Impl [
    | id: $id
    | image: $image
    | is_valid: $is_valid
    |]
    """.trimMargin()
    }
    }
    Schema Definition
    

    View Slide

36. package com.aungkyawpaing.droidyangonsql.data.sqldelight
    import ...
    interface BallotQueries : Transacter {
    }
    Schema Definition
    

    View Slide

37. private class BallotQueriesImpl(
    private val database: ExampleDbImpl,
    private val driver: SqlDriver
    ) : TransacterImpl(driver), BallotQueries {
    }
    Schema Definition
    

    View Slide

38. interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    Schema Definition
    

    View Slide

39. interface ExampleDb : Transacter {
    val ballotQueries: BallotQueries
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    Schema Definition
    

    View Slide

40. interface ExampleDb : Transacter {
    val ballotQueries: BallotQueries
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    Schema Definition
    

    View Slide

41. package com.example.sqldelight.sqldelight
    import ...
    internal val KClass.schema: SqlDriver.Schema
    get() = ExampleDbImpl.Schema
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    Schema Definition
    

    View Slide

42. package com.example.sqldelight.sqldelight
    import ...
    internal val KClass.schema: SqlDriver.Schema
    get() = ExampleDbImpl.Schema
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    Schema Definition
    

    View Slide

43. package com.example.sqldelight.sqldelight
    import ...
    internal val KClass.schema: SqlDriver.Schema
    get() = ExampleDbImpl.Schema
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    Schema Definition
    

    View Slide

44. override fun create(driver: SqlDriver) {
    }
    Schema Definition
    

    View Slide

45. override fun create(driver: SqlDriver) {
    driver.execute(null, """
    |CREATE TABLE Ballot(
    | id INTEGER PRIMARY KEY NOT NULL,
    | image TEXT NOT NULL,
    | is_valid INTEGER NOT NULL DEFAULT 0
    |)
    """.trimMargin(), 0)
    }
    Schema Definition
    

    View Slide

46. Queries
    

    View Slide

47. Query
    select_valid_only:
    SELECT * FROM Ballot WHERE is_valid = 1;
    

    View Slide

48. interface BallotQueries : Transacter {
    }
    Query
    

    View Slide

49. interface BallotQueries : Transacter {
    fun select_valid_only(mapper: (
    id: Long,
    image: String,
    is_valid: Boolean
    ) -> T): Query
    fun select_valid_only(): Query
    }
    Query
    

    View Slide

50. private class BallotQueriesImpl(
    private val database: ExampleDbImpl,
    private val driver: SqlDriver
    ) : TransacterImpl(driver), BallotQueries {
    }
    Query
    

    View Slide

51. private class BallotQueriesImpl(
    private val database: ExampleDbImpl,
    private val driver: SqlDriver
    ) : TransacterImpl(driver), BallotQueries {
    internal val select_valid_only: MutableList> = copyOnWriteList()
    override fun select_valid_only(mapper: (
    id: Long,
    image: String,
    is_valid: Boolean
    ) -> T): Query = Query(0, select_valid_only, driver,
    "SELECT * FROM Ballot WHERE is_valid = 1") { cursor ->
    mapper(
    cursor.getLong(0)!!,
    cursor.getString(1)!!,
    cursor.getLong(2)!! == 1L
    )
    }
    override fun select_valid_only(): Query = select_valid_only(Ballot::Impl)
    }
    Query
    

    View Slide

52. private class BallotQueriesImpl(
    private val database: ExampleDbImpl,
    private val driver: SqlDriver
    ) : TransacterImpl(driver), BallotQueries {
    internal val select_valid_only: MutableList> = copyOnWriteList()
    override fun select_valid_only(mapper: (
    id: Long,
    image: String,
    is_valid: Boolean
    ) -> T): Query = Query(0, select_valid_only, driver,
    "SELECT * FROM Ballot WHERE is_valid = 1") { cursor ->
    mapper(
    cursor.getLong(0)!!,
    cursor.getString(1)!!,
    cursor.getLong(2)!! == 1L
    )
    }
    override fun select_valid_only(): Query = select_valid_only(Ballot::Impl)
    }
    Query
    

    View Slide

53. private class BallotQueriesImpl(
    private val database: ExampleDbImpl,
    private val driver: SqlDriver
    ) : TransacterImpl(driver), BallotQueries {
    internal val select_valid_only: MutableList> = copyOnWriteList()
    override fun select_valid_only(mapper: (
    id: Long,
    image: String,
    is_valid: Boolean
    ) -> T): Query = Query(0, select_valid_only, driver,
    "SELECT * FROM Ballot WHERE is_valid = 1") { cursor ->
    mapper(
    cursor.getLong(0)!!,
    cursor.getString(1)!!,
    cursor.getLong(2)!! == 1L
    )
    }
    override fun select_valid_only(): Query = select_valid_only(Ballot::Impl)
    }
    Query
    

    View Slide

54. Drivers
    - Android
    - iOS (Kotlin/Native)
    - JVM
    

    View Slide

55. Drivers
    - Android
    implementation "com.squareup.sqldelight:android-driver:1.1.3"
    - iOS (Kotlin/Native)
    implementation “com.squareup.sqldelight:ios-driver:1.1.3"
    - JVM
    implementation "com.squareup.sqldelight:sqlite-driver:1.1.3"
    

    View Slide

56. //Create a driver
    val driver = AndroidSqliteDriver(ExampleDb.Schema, context, "example.db")
    //Create Database
    val exampleDb = ExampleDb(driver)
    //Get Queries from Database
    val ballotQueries = exampleDb.ballotQueries
    Query
    

    View Slide

57. //Create a driver
    val driver = AndroidSqliteDriver(ExampleDb.Schema, context, "example.db")
    //Create Database
    val exampleDb = ExampleDb(driver)
    //Get Queries from Database
    val ballotQueries = exampleDb.ballotQueries
    Query
    

    View Slide

58. //Create a driver
    val driver = AndroidSqliteDriver(ExampleDb.Schema, context, "example.db")
    //Create Database
    val exampleDb = ExampleDb(driver)
    //Get Queries from Database
    val ballotQueries = exampleDb.ballotQueries
    Query
    

    View Slide

59. //Create Database
    val exampleDb = ExampleDb(driver)
    Query
    

    View Slide

60. //Create Database
    val exampleDb = ExampleDb(driver)
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    Query
    

    View Slide

61. //Create Database
    val exampleDb = ExampleDb(driver)
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    Query
    

    View Slide

62. //Create Database
    val exampleDb = ExampleDb(driver)
    interface ExampleDb : Transacter {
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    Query
    

    View Slide

63. //Create Database
    val exampleDb = ExampleDb(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    ...
    override fun create(driver: SqlDriver) {
    driver.execute(null, """
    |CREATE TABLE Ballot(
    | id INTEGER PRIMARY KEY NOT NULL,
    | name TEXT NOT NULL,
    | is_valid INTEGER NOT NULL DEFAULT 0
    |)
    """.trimMargin(), 0)
    }
    ...
    }
    }
    Query
    

    View Slide

64. //Create Database
    val exampleDb = ExampleDb(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    ...
    override fun create(driver: SqlDriver) {
    driver.execute(null, """
    |CREATE TABLE Ballot(
    | id INTEGER PRIMARY KEY NOT NULL,
    | name TEXT NOT NULL,
    | is_valid INTEGER NOT NULL DEFAULT 0
    |)
    """.trimMargin(), 0)
    }
    ...
    }
    }
    Query
    

    View Slide

65. //Get Queries from Database
    val ballotQueries = exampleDb.ballotQueries
    internal fun KClass.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver)
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    object Schema : SqlDriver.Schema {
    ...
    override fun create(driver: SqlDriver) {
    driver.execute(null, """
    |CREATE TABLE Ballot(
    | id INTEGER PRIMARY KEY NOT NULL,
    | name TEXT NOT NULL,
    | is_valid INTEGER NOT NULL DEFAULT 0
    |)
    """.trimMargin(), 0)
    }
    ...
    }
    }
    Query
    

    View Slide

66. private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    ...
    }
    //Get Queries from Database
    val ballotQueries = exampleDb.ballotQueries
    Query
    

    View Slide

67. //Get Queries from Database
    val ballotQueries = exampleDb.ballotQueries
    private class ExampleDbImpl(driver: SqlDriver) : TransacterImpl(driver), ExampleDb {
    override val ballotQueries: BallotQueriesImpl = BallotQueriesImpl(this, driver)
    ...
    }
    private class BallotQueriesImpl(
    private val database: ExampleDbImpl,
    private val driver: SqlDriver
    ) : TransacterImpl(driver), BallotQueries {
    ...
    }
    Query
    

    View Slide

68. override fun select_valid_only(mapper: (
    id: Long,
    name: String,
    is_valid: Boolean
    ) -> T): Query = Query(0, select_valid_only, driver,
    "SELECT * FROM Ballot WHERE is_valid = 1") { cursor ->
    mapper(
    cursor.getLong(0)!!,
    cursor.getString(1)!!,
    cursor.getLong(2)!! == 1L
    )
    }
    Query
    

    View Slide

69. Query
    

    View Slide

70. Query
    return ballotQueries.select_valid_only { id, name, is_valid ->
    //Do Mapping here
    }
    

    View Slide

71. Query
    override fun getValidBallots(): List {
    return ballotQueries.select_valid_only { id, image, is_valid ->
    Ballot(
    id,
    image,
    is_valid
    )
    }.executeAsList()
    }
    

    View Slide

72. Custom Type
    

    View Slide

73. Custom Type
    CREATE TABLE Ballot(
    id INTEGER PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    

    View Slide

74. Custom Type
    CREATE TABLE Ballot(
    id INTEGER PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    

    View Slide

75. Custom Type
    CREATE TABLE Ballot(
    id INTEGER PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    

    View Slide

76. Custom Type
    import org.threeten.bp.Instant;
    CREATE TABLE Ballot(
    id INTEGER PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    added_date INTEGER AS Instant NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    

    View Slide

77. Custom Type
    import org.threeten.bp.Instant;
    CREATE TABLE Ballot(
    id INTEGER PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    added_date INTEGER AS Instant NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    

    View Slide

78. Custom Type
    interface Ballot {
    val id: Long
    val image: String
    val added_date: Instant
    val is_valid: Boolean
    data class Impl(
    override val id: Long,
    override val image: String,
    override val added_date: Instant,
    override val is_valid: Boolean
    ) : Ballot {
    override fun toString(): String = """
    |Ballot.Impl [
    | id: $id
    | image: $image
    | added_date: $added_date
    | is_valid: $is_valid
    |]
    """.trimMargin()
    }
    }
    

    View Slide

79. Custom Type
    interface Ballot {
    ...
    class Adapter(val added_dateAdapter: ColumnAdapter)
    ...
    }
    

    View Slide

80. Custom Type
    interface ExampleDb : Transacter {
    val ballotQueries: BallotQueries
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver): ExampleDb =
    ExampleDb::class.newInstance(driver)}
    }
    

    View Slide

81. Custom Type
    interface ExampleDb : Transacter {
    val ballotQueries: BallotQueries
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver, BallotAdapter:
    Ballot.Adapter): ExampleDb =
    ExampleDb::class.newInstance(driver,
    BallotAdapter)}
    }
    

    View Slide

82. Custom Type
    interface ExampleDb : Transacter {
    val ballotQueries: BallotQueries
    companion object {
    val Schema: SqlDriver.Schema
    get() = ExampleDb::class.schema
    operator fun invoke(driver: SqlDriver, BallotAdapter:
    Ballot.Adapter): ExampleDb =
    ExampleDb::class.newInstance(driver,
    BallotAdapter)}
    }
    

    View Slide

83. Custom Type
    private val instantColumnAdapter = object : ColumnAdapter {
    override fun decode(databaseValue: Long): Instant {
    return Instant.ofEpochMilli(databaseValue)
    }
    override fun encode(value: Instant): Long {
    return value.toEpochMilli()
    }
    }
    

    View Slide

84. Custom Type
    private val instantColumnAdapter = object : ColumnAdapter {
    override fun decode(databaseValue: Long): Instant {
    return Instant.ofEpochMilli(databaseValue)
    }
    override fun encode(value: Instant): Long {
    return value.toEpochMilli()
    }
    }
    

    View Slide

85. Custom Type
    private val instantColumnAdapter = object : ColumnAdapter {
    override fun decode(databaseValue: Long): Instant {
    return Instant.ofEpochMilli(databaseValue)
    }
    override fun encode(value: Instant): Long {
    return value.toEpochMilli()
    }
    }
    

    View Slide

86. Custom Type
    private val instantColumnAdapter = object : ColumnAdapter {
    override fun decode(databaseValue: Long): Instant {
    return Instant.ofEpochMilli(databaseValue)
    }
    override fun encode(value: Instant): Long {
    return value.toEpochMilli()
    }
    }
    

    View Slide

87. Custom Type
    private val instantColumnAdapter = object : ColumnAdapter {
    override fun decode(databaseValue: Long): Instant {
    return Instant.ofEpochMilli(databaseValue)
    }
    override fun encode(value: Instant): Long {
    return value.toEpochMilli()
    }
    }
    private val ballotColumnAdapter = Ballot.Adapter(
    added_dateAdapter = instantColumnAdapter
    )
    

    View Slide

88. Custom Type
    private val instantColumnAdapter = object : ColumnAdapter {
    override fun decode(databaseValue: Long): Instant {
    return Instant.ofEpochMilli(databaseValue)
    }
    override fun encode(value: Instant): Long {
    return value.toEpochMilli()
    }
    }
    private val ballotColumnAdapter = Ballot.Adapter(
    added_dateAdapter = instantColumnAdapter
    )
    private val exampleDb = ExampleDb(driver, ballotColumnAdapter)
    

    View Slide

89. Custom Type
    https://jakewharton.com/inline-classes-make-great-database-ids/
    inline class BallotId(val value: Long)
    CREATE TABLE Ballot(
    ballot_id INTEGER as BallotId PRIMARY KEY NOT NULL,
    image TEXT NOT NULL,
    added_date INTEGER AS Instant NOT NULL,
    is_valid INTEGER AS Boolean NOT NULL DEFAULT 0
    );
    

    View Slide

90. Migration
    

    View Slide

91. Migration by script
    - Put into src/main/sqldelight
    - File naming: .sqm
    - 1 to 2 => 1.sqm
    - 2 to 3 => 2.sqm
    - 7 to 8 => 7.sqm
    - .sq file will have to always contain latest structure
    

    View Slide

92. Migration by db
    - Put .db into src/main/sqldelight
    - File naming: .db
    

    View Slide

93. Migration
    

    View Slide

94. Migration
    

    View Slide

95. Migration
    ALTER TABLE Ballot ADD COLUMN added_date INTEGER NOT NULL;
    UPDATE Ballot SET added_date = 0;
    

    View Slide

96. Migration
    private class ExampleDbImpl(driver: SqlDriver) ...{
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun create(driver: SqlDriver) {
    //...
    }
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    

    View Slide

97. Migration
    private class ExampleDbImpl(driver: SqlDriver) ...{
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 1
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    

    View Slide

98. Migration
    private class ExampleDbImpl(driver: SqlDriver) ...{
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 2
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    }
    }
    }
    

    View Slide

99. Migration
    private class ExampleDbImpl(driver: SqlDriver) ...{
    object Schema : SqlDriver.Schema {
    override val version: Int
    get() = 2
    override fun migrate(
    driver: SqlDriver,
    oldVersion: Int,
    newVersion: Int
    ) {
    if (oldVersion <= 1 && newVersion > 1) {
    driver.execute(null, "ALTER TABLE Ballot ADD COLUMN added_date INTEGER NOT NULL;”, 0)
    driver.execute(null, "UPDATE Ballot SET added_date = 0;", 0)
    }
    }
    }
    }
    

    View Slide

100. - POJO not required
     - Flexibility
     - High level of abstraction
     Raw SQL ✅
     

     View Slide

101. - POJO not required
     - Flexibility
     - High level of abstraction
     SQLDelight ✅
     

     View Slide

102. - Not time efficient
     - Hard to understand
     - I/O controls
     - POJO not required
     - Flexibility
     - High level of abstraction
     SQLDelight ✅ Raw SQL ❌
     

     View Slide

103. - Not time efficient
     - Hard to understand
     - I/O controls
     - POJO not required
     - Flexibility
     - High level of abstraction
     SQLDelight ✅ Raw SQL ❌
     

     View Slide

104. - Not time efficient
     - Hard to understand
     - I/O controls
     - POJO not required
     - Flexibility
     - High level of abstraction
     SQLDelight ✅ Raw SQL ❌
     

     View Slide

105. - Not time efficient
     - Hard to understand
     - I/O controls
     - POJO not required
     - Flexibility
     - High level of abstraction
     SQLDelight ✅ Raw SQL ❌
     

     View Slide

106. - Not officially recommended way
     SQLDelight ❌
     

     View Slide

107. Extensions
     - RxJava support
     - Android Paging support
     - Multiplatform
     

     View Slide

108. “SQL is a pathway to many queries some
     consider to be unnatural.”
     

     View Slide