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

Have you ever heard the story of SQL the Delight?

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

0991ca4bc363163337f7b02b98af538a?s=128

Aung Kyaw Paing

July 09, 2019
Tweet

More Decks by Aung Kyaw Paing

Other Decks in Technology

Transcript

  1. Have you ever heard the story of SQL the Delight?

    Photo by Alexander Andrews on Unsplash
  2. None
  3. History of SQLite in Android - Content Provider + Raw

    SQL - Object-Relational Mapper (ORMs) - Room
  4. Raw SQL

  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 ) } }
  6. Raw SQL ✅ - POJO not required - Flexibility -

    High level of abstraction
  7. Raw SQL ❌ - Not time efficient - Hard to

    understand - I/O controls
  8. Object-Relational Mapper (ORM) - ORMlite - SQLiteMagic - GreenDAO -

    Active Android
  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 )
  10. ORM override fun getValidBallots(): List<Ballot> { return ballotDao .queryForEq("isValid'", true)

    .map { Ballot( id = it.id!!, image = it.image!!, isValid = it.isValid!! ) } }
  11. ORM ✅ - Fast implementation - Reduce cost

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

    Abstraction
  13. Object Database - Realm - Objectbox

  14. Object Database open class BallotRealmEntity : RealmObject() { @PrimaryKey var

    id: Long = 0 var image: String = "" var isValidBallot: Boolean = true }
  15. Object Database override fun getValidBallots(): List<Ballot> { return realm.where(BallotRealmEntity::class.java) .equalTo("isValidBallot",

    true) .findAll() .map { Ballot( id = it.id, image = it.image, isValid = it.isValidBallot ) } }
  16. Room - Announced in 2017 Google I/O - 2.0.0 in

    2018 - Officially recommended approach
  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<BallotRoomEntity> }
  18. SQLDelight

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

  20. SQLDelight - Write sql(.sq) file - Generate Kotlin code -

    Reverse of ORM - Two parts - Gradle plugin - Drivers
  21. SQLDelight buildscript { repositories { google() mavenCentral() } dependencies {

    classpath 'com.squareup.sqldelight:gradle-plugin:1.1.3' } } apply plugin: 'com.squareup.sqldelight'
  22. SQLDelight sqldelight { ExampleDb { packageName = "com.example.sqldelight" sourceFolders =

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

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

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

    ["sqldelight"] schemaOutputDirectory = file("build/sqldelight") } }
  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)} }
  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)} }
  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)} }
  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)} }
  30. SQLDelight package com.example.sqldelight.sqldelight import ... internal val KClass<ExampleDb>.schema: SqlDriver.Schema get()

    = ExampleDbImpl.Schema internal fun KClass<ExampleDb>.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 ) { } } }
  31. SQLDelight package com.example.sqldelight.sqldelight import ... internal val KClass<ExampleDb>.schema: SqlDriver.Schema get()

    = ExampleDbImpl.Schema internal fun KClass<ExampleDb>.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 ) { } } }
  32. SQLDelight package com.example.sqldelight.sqldelight import ... internal val KClass<ExampleDb>.schema: SqlDriver.Schema get()

    = ExampleDbImpl.Schema internal fun KClass<ExampleDb>.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 ) { } } }
  33. Schema Definition

  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
  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
  36. package com.aungkyawpaing.droidyangonsql.data.sqldelight import ... interface BallotQueries : Transacter { }

    Schema Definition
  37. private class BallotQueriesImpl( private val database: ExampleDbImpl, private val driver:

    SqlDriver ) : TransacterImpl(driver), BallotQueries { } Schema Definition
  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
  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
  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
  41. package com.example.sqldelight.sqldelight import ... internal val KClass<ExampleDb>.schema: SqlDriver.Schema get() =

    ExampleDbImpl.Schema internal fun KClass<ExampleDb>.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
  42. package com.example.sqldelight.sqldelight import ... internal val KClass<ExampleDb>.schema: SqlDriver.Schema get() =

    ExampleDbImpl.Schema internal fun KClass<ExampleDb>.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
  43. package com.example.sqldelight.sqldelight import ... internal val KClass<ExampleDb>.schema: SqlDriver.Schema get() =

    ExampleDbImpl.Schema internal fun KClass<ExampleDb>.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
  44. override fun create(driver: SqlDriver) { } Schema Definition

  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
  46. Queries

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

  48. interface BallotQueries : Transacter { } Query

  49. interface BallotQueries : Transacter { fun <T : Any> select_valid_only(mapper:

    ( id: Long, image: String, is_valid: Boolean ) -> T): Query<T> fun select_valid_only(): Query<Ballot> } Query
  50. private class BallotQueriesImpl( private val database: ExampleDbImpl, private val driver:

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

    SqlDriver ) : TransacterImpl(driver), BallotQueries { internal val select_valid_only: MutableList<Query<*>> = copyOnWriteList() override fun <T : Any> select_valid_only(mapper: ( id: Long, image: String, is_valid: Boolean ) -> T): Query<T> = 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<Ballot> = select_valid_only(Ballot::Impl) } Query
  52. private class BallotQueriesImpl( private val database: ExampleDbImpl, private val driver:

    SqlDriver ) : TransacterImpl(driver), BallotQueries { internal val select_valid_only: MutableList<Query<*>> = copyOnWriteList() override fun <T : Any> select_valid_only(mapper: ( id: Long, image: String, is_valid: Boolean ) -> T): Query<T> = 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<Ballot> = select_valid_only(Ballot::Impl) } Query
  53. private class BallotQueriesImpl( private val database: ExampleDbImpl, private val driver:

    SqlDriver ) : TransacterImpl(driver), BallotQueries { internal val select_valid_only: MutableList<Query<*>> = copyOnWriteList() override fun <T : Any> select_valid_only(mapper: ( id: Long, image: String, is_valid: Boolean ) -> T): Query<T> = 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<Ballot> = select_valid_only(Ballot::Impl) } Query
  54. Drivers - Android - iOS (Kotlin/Native) - JVM

  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"
  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
  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
  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
  59. //Create Database val exampleDb = ExampleDb(driver) Query

  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
  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
  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<ExampleDb>.newInstance(driver: SqlDriver): ExampleDb = ExampleDbImpl(driver) Query
  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
  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
  65. //Get Queries from Database val ballotQueries = exampleDb.ballotQueries internal fun

    KClass<ExampleDb>.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
  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
  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
  68. override fun <T : Any> select_valid_only(mapper: ( id: Long, name:

    String, is_valid: Boolean ) -> T): Query<T> = 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
  69. Query

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

    here }
  71. Query override fun getValidBallots(): List<Ballot> { return ballotQueries.select_valid_only { id,

    image, is_valid -> Ballot( id, image, is_valid ) }.executeAsList() }
  72. Custom Type

  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 );
  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 );
  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 );
  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 );
  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 );
  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() } }
  79. Custom Type interface Ballot { ... class Adapter(val added_dateAdapter: ColumnAdapter<Instant,

    Long>) ... }
  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)} }
  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)} }
  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)} }
  83. Custom Type private val instantColumnAdapter = object : ColumnAdapter<Instant, Long>

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

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

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

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

    { 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 )
  88. Custom Type private val instantColumnAdapter = object : ColumnAdapter<Instant, Long>

    { 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)
  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 );
  90. Migration

  91. Migration by script - Put into src/main/sqldelight - File naming:

    <version to upgrade from>.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
  92. Migration by db - Put .db into src/main/sqldelight - File

    naming: <version number>.db
  93. Migration

  94. Migration

  95. Migration ALTER TABLE Ballot ADD COLUMN added_date INTEGER NOT NULL;

    UPDATE Ballot SET added_date = 0;
  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 ) { } } }
  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 ) { } } }
  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 ) { } } }
  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) } } } }
  100. - POJO not required - Flexibility - High level of

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

    abstraction SQLDelight ✅
  102. - Not time efficient - Hard to understand - I/O

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

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

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

    controls - POJO not required - Flexibility - High level of abstraction SQLDelight ✅ Raw SQL ❌
  106. - Not officially recommended way SQLDelight ❌

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

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

    be unnatural.”