MENU

【代码札记】为Ktorm框架拓展PostgreSQL方言进行Json访问

August 30, 2020 • 瞎折腾

最近从滴滴辞职,为期5天的暑假正式开始了,寻思着做一点有意义的事情提升一下自己。遂决定自己写一套专门用于复杂查询的通联日志管理系统,数据库选用了PostgreSQL,该数据库可以直接对Json类型的数据进行高级查询,然而Ktorm框架并不支持此功能,因此本文将记述为该框架进行拓展的过程。

需求

假设数据库中有一表qso_infos用于存储通联日志,其中qsl_info字段表示QSL相关事宜的记录,该字段为jsonb类型,样例如下:

{
    "lotw": {
        "uploaded": true,
        "uploadDate": "2020-06-20",
        "comfirmed": true,
        "comfireDate": "2020-07-21"
    },
    "card": {
        "sent": {
            "sent": false,
            "required": true,
            "sentDate": null,
            "requiredDate": "2020-06-21",
            "via": "bureau"
        },
        "received": {
            "received": false,
            "required": true,
            "receivedDate": null,
            "requiredDate": "2020-06-22",
            "via": "direct"
        }
    },
    "comment": "因为COVID-19推迟寄送QSL卡片"
}

现在需要查询所有LoTW未上传的记录,应当如何利用Ktorm在数据库端完成?

如果需要查询所有comment为空的记录,应当如何利用Ktorm在数据库端完成?

分析

如果直接编写SQL的话,应当按如下编写:

SELECT * FROM qso_infos WHERE qsl_info->'lotw'->>'uploaded' = 'false';
SELECT * FROM qso_infos WHERE qsl_info->>'comment' = '';

其中第一句还可以这样写:

SELECT * FROM qso_infos WHERE (qsl_info->>'lotw')::json->>'uploaded' = 'false';

第一种写法用到了两个不同的运算符:->是作为Json取出,而->>则是作为字符串取出。需要注意的是对于Json Object这两个运算符是根据输入的字符串作为键去取值,而对于Json Array则是按照输入的整形作为从0开始的索引去取值。为了最大兼容性的考虑,本文将同时实现每个运算符的两个形式。

对于第二种写法,虽然只需要自定义一个运算符即可,但还需要将转换为Json作为一个额外的运算符实现。本文也将实现该运算符。

实现

SQL类型扩展

为了实现对Json的访问,首先应当定义jsonjsonb两个SQL数据类型。这里我没有使用ktorm的模块,而是基于Gson写了一个:

package info.skyblond.jinn.extension

import com.google.gson.Gson
import me.liuwj.ktorm.schema.BaseTable
import me.liuwj.ktorm.schema.Column
import me.liuwj.ktorm.schema.SqlType
import me.liuwj.ktorm.schema.TypeReference
import java.lang.reflect.Type
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Types

class JsonbSqlType<T : Any>(private val type: Type) : SqlType<T>(Types.JAVA_OBJECT, typeName = "jsonb") {
    private val gson = Gson()

    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: T) {
        ps.setString(index, gson.toJson(parameter))
    }

    override fun doGetResult(rs: ResultSet, index: Int): T? {
        val json = rs.getString(index)
        return if (json.isNullOrBlank()) {
            null
        } else {
            gson.fromJson(json, type)
        }
    }
}

fun <C : Any> BaseTable<*>.jsonb(
    name: String,
    typeReference: TypeReference<C>
): Column<C> {
    return registerColumn(name, JsonbSqlType(typeReference.referencedType))
}

internal class JsonSqlType<T : Any>(private val type: Type) : SqlType<T>(Types.JAVA_OBJECT, typeName = "json") {
    private val gson = Gson()

    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: T) {
        ps.setString(index, gson.toJson(parameter))
    }

    override fun doGetResult(rs: ResultSet, index: Int): T? {
        val json = rs.getString(index)
        return if (json.isNullOrBlank()) {
            null
        } else {
            gson.fromJson(json, type)
        }
    }
}

fun <C : Any> BaseTable<*>.json(
    name: String,
    typeReference: TypeReference<C>
): Column<C> {
    return registerColumn(name, JsonSqlType(typeReference.referencedType))
}

关于代码就不多说了,具体可以参考Ktorm文档 - 定义表结构 - 扩展更多的类型。这里最重要的一点就是要使用限制比较宽松的框架进行Json转换,一开始我试图使用kotlinx的serialization进行,于是进行Json转换的时候就需要一个KSerializer<T>对象才能工作,而该对象的获取渠道是SomeClass.serializer(),其中SomeClass需要被@kotlinx.serialization.Serializable注解。之后进行Json操作时为了最大的兼容性,通常都是认为操作的结果是Any而非特定一个类,那么问题就来了:Any类似Java的Object,是万物之父,而所有被@Serializable注解的类,可没有一个统一的父类。因此这样的架构在后续实现运算符的时候就会非常难受。最起码也是要实现了Java的Serializable接口(或其他框架的统一的接口)。

现在有了Json数据类型,接下来我们就可以实现运算符了。

自定义运算符

关于自定义运算符,这里同样不多赘述,详细指导可以参考Ktorm文档 - 运算符 - 自定义运算符。拓展的代码如下:

package info.skyblond.jinn.extension

import me.liuwj.ktorm.expression.ArgumentExpression
import me.liuwj.ktorm.expression.ScalarExpression
import me.liuwj.ktorm.schema.ColumnDeclaring
import me.liuwj.ktorm.schema.IntSqlType
import me.liuwj.ktorm.schema.SqlType
import me.liuwj.ktorm.schema.VarcharSqlType

data class AsJsonExpression(
    val left: ScalarExpression<*>,
    override val sqlType: SqlType<Any> = JsonSqlType(Any::class.java),
    override val isLeafNode: Boolean = false,
    override val extraProperties: Map<String, Any> = mapOf(),
    val alreadyJson: Boolean = false
) : ScalarExpression<Any>()

/**
 * This function make a AsJsonExpression.
 * When alreadyJson is true, the expression does nothing, just to make compiler happy.
 */
fun ColumnDeclaring<*>.asJson(alreadyJson: Boolean = false): AsJsonExpression {
    return AsJsonExpression(asExpression(), alreadyJson = alreadyJson)
}

// Get as Json

data class JsonAccessExpression<T : Any>(
    val left: AsJsonExpression,
    val right: ArgumentExpression<T>,
    override val sqlType: SqlType<Any> = JsonSqlType(Any::class.java),
    override val isLeafNode: Boolean = false,
    override val extraProperties: Map<String, Any> = mapOf()
) : ScalarExpression<Any>()

operator fun AsJsonExpression.get(param: String): AsJsonExpression {
    return JsonAccessExpression(this, ArgumentExpression(param, VarcharSqlType)).asJson(true)
}

operator fun AsJsonExpression.get(param: Int): AsJsonExpression {
    return JsonAccessExpression(this, ArgumentExpression(param, IntSqlType)).asJson(true)
}

// Get as Text

data class JsonAccessAsTextExpression<T : Any>(
    val left: AsJsonExpression,
    val right: ArgumentExpression<T>,
    override val sqlType: SqlType<String> = VarcharSqlType,
    override val isLeafNode: Boolean = false,
    override val extraProperties: Map<String, Any> = mapOf()
) : ScalarExpression<String>()

fun AsJsonExpression.getAsString(param: String): JsonAccessAsTextExpression<String> {
    return JsonAccessAsTextExpression(this, ArgumentExpression(param, VarcharSqlType))
}

fun AsJsonExpression.getAsString(param: Int): JsonAccessAsTextExpression<Int> {
    return JsonAccessAsTextExpression(this, ArgumentExpression(param, IntSqlType))
}

在这里我们实现了AsJsonExpression,该表达式将前面的语句转换成Json类型;JsonAccessExpression,该表达式将对Json类型的数据进行->运算;JsonAccessAsTextExpression,该表达式将对Json类型的数据进行->>运算。

在Ktorm中对于数据的面向对象式筛选,实际上是基于面向对象的写法产生一个表达式树。该树中的每一个Expression表示一个运算或参数,最终将被解析为一条SQL语句。

值得注意的是在AsJsonExpression的实现中只有left,原本是打算将其作为AsDataTypeExpression<T>,然后right作为一个SqlType<T>来实现更通用的功能的,但是这样一来后面的实现将无法保证只对Json类型的语句进行访问:JsonSqlType要能够适配所有情况,其类型必定是SqlType<Any>,这样一来将无法区分哪些表达式是Json,哪些不是。当然你也可以额外在加一个类型:AsDataTypeExpression<T, U>,T作为SqlType<T>,而U直接存储JsonSqlType<Any>,但是就本文而言,还是单独搞一个AsJsonExpression来的最实在。

关于asJson()函数,设置alreadyJson的目的就是后面对于->运算符,其运算结果本身就是Json,而对于编译器来说则是JsonAccessExpression类型,不能应用AsJsonExpression的访问操作。因此这里只是单纯的为了让编译器开心,当进行->访问时,除了产生一个访问表达式之外,还在外面报一个AsJsonExpression,这样对于编译器来说就是合法的Json类型了,而在生成SQL语句时通过判断alreadyJson字段可以跳过本次类型转换。

最后为了写起来更舒爽,对于作为Json Object取出的->运算,我重载了kotlin内置的get方法,这样就可以通过asJson()[keyName]的形式进行访问了。

而只进行到此还是不够全面,目前ktorm还不能正确翻译这些表达式。下一步将进行方言扩展。

扩展方言

既然说扩展方言,就是说我们并不像替代原有的PostgreSQL方言,而Ktorm的作者也贴心的将方言实现类加了open关键字,这样我们就可以自由的进行扩展了。根据Ktorm文档 - 运算符 - 自定义运算符,扩展的方言应当覆盖对未知表达式的处理:

package info.skyblond.jinn.extension

import me.liuwj.ktorm.database.Database
import me.liuwj.ktorm.expression.SqlExpression
import me.liuwj.ktorm.expression.SqlFormatter
import me.liuwj.ktorm.support.postgresql.PostgreSqlDialect
import me.liuwj.ktorm.support.postgresql.PostgreSqlFormatter

open class MyPostgreSqlDialect : PostgreSqlDialect() {

    override fun createSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int): SqlFormatter {
        return MyPostgreSqlFormatter(database, beautifySql, indentSize)
    }
}

class MyPostgreSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int)
    : PostgreSqlFormatter(database, beautifySql, indentSize) {

    override fun visitUnknown(expr: SqlExpression): SqlExpression {
        return when (expr) {
            is AsJsonExpression -> {
                if (expr.left.removeBrackets) {
                    visit(expr.left)
                } else {
                    write("(")
                    visit(expr.left)
                    removeLastBlank()
                    write(") ")
                }

                // if already json, then do nothing, just make compiler happy
                if (!expr.alreadyJson) {
                    removeLastBlank()
                    write("::json ")
                }
                expr
            }
            is JsonAccessAsTextExpression<*> -> {
                // Json only, no need to add brackets
                visit(expr.left)

                write("->> ")

                visit(expr.right)

                expr
            }
            is JsonAccessExpression<*> -> {
                // Json only, no need to add brackets
                visit(expr.left)

                write("-> ")

                visit(expr.right)

                expr
            }
            else -> {
                super.visitUnknown(expr)
            }
        }
    }
}

在遇到没有见过的表达式时,将回调父类的处理函数,而这个函数的默认行为就是抛异常。这里在产生SQL语句的时候有一些细节要注意。

对于转换为Json的语句,需要时应当增加括号来保证运算优先级的正确性。而对于Json的访问,其左值一定是Json,而右值则一定是数字或字符串类型的参数,因此左右皆无需增加括号。

最后我们需要在连接数据库的时候指定该方言。

结果

首先连接数据库:

val pgDataSource = PGDataSource()
pgDataSource.serverName = "localhost"
pgDataSource.portNumber = 5432
pgDataSource.user = "logbook"
pgDataSource.password = "qOA\$&fHGOceoIqxD"
pgDataSource.databaseName = "logbook"

val config = HikariConfig()
config.driverClassName = "com.impossibl.postgres.jdbc.PGDataSource"
config.dataSource = pgDataSource
config.addDataSourceProperty("cachePrepStmts", "true")
config.addDataSourceProperty("prepStmtCacheSize", "250")
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048")

val hikariDataSource = HikariDataSource(config)

val database = Database.connect(hikariDataSource, dialect = MyPostgreSqlDialect())

注意最后在连接数据库的时候指定了我们自己实现的方言。之后在定义完表和实体类之后进行查询:


database.sequenceOf(QsoInfos).filter {
    it.qslInfo.asJson(true)["lotw"].getAsString("uploaded") eq "true"
}.forEach {
    println(it)
}


database.sequenceOf(QsoInfos).filter {
    it.qslInfo.asJson(true)["card"]["sent"].getAsString("sent") eq "false"
}.forEach {
    println(it)
}


database.sequenceOf(QsoInfos).filter {
    it.qslInfo.asJson(true).getAsString("comment") notEq ""
}.forEach {
    println(it)
}

database.sequenceOf(QsoInfos).filter {
    it.qslInfo.asJson(true).getAsString("card").asJson().getAsString("sent").asJson().getAsString("sent") notEq "true"
}.forEach {
    println(it)
}

对应的分别产生了如下SQL:

DEBUG me.liuwj.ktorm.database - SQL: select ... from qso_infos where ((qso_infos.qsl_info -> ?) ->> ?) = ? 
DEBUG me.liuwj.ktorm.database - Parameters: [lotw(varchar), uploaded(varchar), true(varchar)]

DEBUG me.liuwj.ktorm.database - SQL: select ... from qso_infos where (((qso_infos.qsl_info -> ?) -> ?) ->> ?) = ? 
DEBUG me.liuwj.ktorm.database - Parameters: [card(varchar), sent(varchar), sent(varchar), false(varchar)]

DEBUG me.liuwj.ktorm.database - SQL: select ... from qso_infos where (qso_infos.qsl_info ->> ?) <> ? 
DEBUG me.liuwj.ktorm.database - Parameters: [comment(varchar), (varchar)]

DEBUG me.liuwj.ktorm.database - SQL: select ... from qso_infos where (((qso_infos.qsl_info ->> ?)::json ->> ?)::json ->> ?) <> ? 
DEBUG me.liuwj.ktorm.database - Parameters: [card(varchar), sent(varchar), sent(varchar), true(varchar)]

看起来符合预期,程序也没有报错。至此可以算是完美决绝问题。

-全文完-


知识共享许可协议
【代码札记】为Ktorm框架拓展PostgreSQL方言进行Json访问天空 Blond 采用 知识共享 署名 - 非商业性使用 - 相同方式共享 4.0 国际 许可协议进行许可。
本许可协议授权之外的使用权限可以从 https://skyblond.info/about.html 处获得。

Archives QR Code
QR Code for this page
Tipping QR Code
Leave a Comment

6 Comments
  1. 纯洁 纯洁

    请问能不能分享一下demo给参考

    1. xiaoyv_404 xiaoyv_404

      @纯洁同求qwq

  2. 另外我想在我的博客里面转载这篇文章,请问是否可以?

    1. @刘文俊可以可以,尽管转载/用就好啦

    2. @天空Blond谢谢@(爱心)

  3. 写得真好,我是 Ktorm 的作者,在 Google 搜索进来的,可以把你这篇文章的地址贴到 Ktorm 的文档里面作为如何扩展运算符的示例吗?