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
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.”