其他
开源 | 携程机票跨端 Kotlin DSL 数据库框架 SQLlin
作者简介
禹昂,携程机票移动端资深工程师,专注于 Kotlin 移动端跨平台领域,Kotlin 中文社区核心成员,图书《Kotlin 编程实践》译者。
2.1 为什么要使用 SQLite 框架?
2.2 开源方案调研
2.2.1 Jetpack Room
@Entity
data class User(
@PrimaryKey val uid: Int,
@ColumnInfo(name = "first_name") val firstName: String?,
@ColumnInfo(name = "last_name") val lastName: String?
)
@Dao
interface UserDao {
@Query("SELECT * FROM user")
fun getAll(): List<User>
@Query("SELECT * FROM user WHERE uid IN (:userIds)")
fun loadAllByIds(userIds: IntArray): List<User>
@Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
"last_name LIKE :last LIMIT 1")
fun findByName(first: String, last: String): User
@Insert
fun insertAll(vararg users: User)
@Delete
fun delete(user: User)
}
@Database(entities = [User::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
}
2.2.2 Exposed
object Users : Table() {
val id = varchar("id", 10) // Column<String>
val name = varchar("name", length = 50) // Column<String>
val cityId = (integer("city_id") references Cities.id).nullable() // Column<Int?>
override val primaryKey = PrimaryKey(id, name = "PK_User_ID") // name is optional here
}
fun main() {
Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver", user = "root", password = "")
transaction {
addLogger(StdOutSqlLogger)
Users.insert {
it[id] = "andrey"
it[name] = "Andrey"
it[Users.cityId] = saintPetersburgId
}
Users.update({ Users.id eq "alex"}) {
it[name] = "Alexey"
}
Users.deleteWhere{ Users.name like "%thing"}
for (city in Cities.selectAll()) {
println("${city[Cities.id]}: ${city[Cities.name]}")
}
}
2.2.3 SQLDelight
2.3 需求确定
三、 基本设计与实现
3.1 架构设计与 module 划分
fun openDatabase() = memScoped {
val dbPtr = alloc<CPointerVar<sqlite3>>()
val openResult = sqlite3_open_v2(path, dbPtr.ptr, sqliteFlags, null)
if (openResult != SQLITE_OK) {
throw IllegalStateExcepetion(sqlite3_errmsg(dbPtr.value)?.toKString() ?: “”)
}
}
public expect fun openDatabase(config: DatabaseConfiguration): DatabaseConnection
import android.database.sqlite.SQLiteDatabase
public actual fun openDatabase(config: DatabaseConfiguration): DatabaseConnection {
SQLiteDatabase.openDatabase()
//......
}
import co.touchlab.sqliter.createDatabaseManager
public actual fun openDatabase(config: DatabaseConfiguration): DatabaseConnection {
createDatabaseManager(configNative).createMultiThreadedConnection()
//......
}
3.3 DSL 设计与实现
3.3.1 基本设计
fun sample() {
lateinit var statement: SelectStatement<Person>
database {
val table = Table<Person>(“person”)
table INSERT listOf(tom, jerry, nick)
table DELETE WHERE (name == “Jerry”)
table UPDATE SET (age = 27) WHERE (name == “Nick”)
statement = table SELECT WHERE (name == “Tom”) ORDER_BY (age to DESC)
}
val result: List<Person> = statement.getResult()
}
3.3.2 DSL 类型关系
Table + Operation + Clause -> Statement
Statement + Clause -> Statement
ClauseElement + String/Number -> ClauseCondition
ClauseCondition + ClauseCondition -> ClauseCondition
3.3.3 使用 Kotlin Symbol Processor 实现表与列元素生成
@DBRow("person")
data class Person(
val age: Int,
val name: String,
)
// KSP generated:
object PersonTable : Table<Person>("person") {
inline operator fun <R> invoke(block: PersonTable.(table: PersonTable) -> R): R = this.block(this)
val name: ClauseString get() {…}
val age: ClauseNumber get() {…}
var SetClause<Person>.name: String set(value) {…}
var SetClause<Person>.age: Int set(value) {…}
}
3.3.4 如何实现查询结果的反序列化
interface CommonCursor {
fun getInt(columnIndex: Int): Int
fun getString(columnIndex: Int): String?
//……
fun getColumnIndex(columnName: String): Int
fun forEachRow(block: (Int) -> Unit)
fun close()
}
@OptIn(ExperimentalSerializationApi::class)
internal class QueryDecoder(
private val cursor: CommonCursor
) : AbstractDecoder() {
private var elementIndex = 0
private var elementName = ""
override val serializersModule: SerializersModule = EmptySerializersModule()
override tailrec fun decodeElementIndex(descriptor: SerialDescriptor): Int =
if (elementIndex == descriptor.elementsCount)
CompositeDecoder.DECODE_DONE
else {
elementName = descriptor.getElementName(elementIndex)
val resultIndex = elementIndex++
if (cursorColumnIndex >= 0)
resultIndex
else
decodeElementIndex(descriptor)
}
override fun beginStructure(descriptor: SerialDescriptor): CompositeDecoder = QueryDecoder(cursor)
private inline val cursorColumnIndex
get() = cursor.getColumnIndex(elementName)
private inline fun <T> deserialize(block: (Int) -> T): T = cursorColumnIndex.let {
if (it >= 0) block(it) else throw SerializationException("The Cursor doesn't have this column")
}
override fun decodeBoolean(): Boolean = deserialize { cursor.getInt(it) > 0 }
override fun decodeInt(): Int = deserialize { cursor.getInt(it) }
override fun decodeString(): String = deserialize { cursor.getString(it) ?: "" }
override fun decodeDouble(): Double = deserialize { cursor.getDouble(it) }
//......
}
3.3.5 最终效果
fun sample() {
lateinit var statement: SelectStatement<Person>
database {
PersonTable { table ->
table INSERT listOf(tom, jerry, nick)
table DELETE WHERE (name EQ "Jerry")
table UPDATE SET {age = 27} WHERE (name NEQ "Nick")
statement = table SELECT WHERE (name EQ "Tom") ORDER_BY (age to DESC)
}
}
val result: List<Person> = statement.getResult()
}
Android iOS (x64, arm32, arm64, simulatorArm64) macOS (x64, arm64) watchOS (x86, x64, arm32, arm64, simulatorArm64) tvOS (x64, arm64, simulatorArm64) Linux (x64)
四、未来计划
五. 参考链接
文章《携程机票 App KMM 跨端生产实践》 MMKV-Kotlin Github 主页:https://github.com/ctripcorp/mmkv-kotlin 文章《携程机票 App KMM 跨端 KV 存储库 MMKV-Kotlin》 Jetpack Room:https://developer.android.com/training/data-storage/room Exposed:https://github.com/JetBrains/Exposed SQLDelight:https://cashapp.github.io/sqldelight/ 文章《KMM 求生日记四:使用 kotlinx.serialization 对 SQLite 数据库反序列化》 kotlinx.serialization 关于自定义 Decoder 的官方文档:https://github.com/Kotlin/kotlinx.serialization/blob/master/docs/formats.md#basic-decoder SQLlin Github 主页:https://github.com/ctripcorp/SQLlin