本文转自 https://skyblond.info/archives/751.html

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

需求

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{
    "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 的话，应当按如下编写：

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

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

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

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

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

实现

SQL 类型扩展

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package info.skyblond.jinn.extension

import com.google.gson.Gson
import org.ktorm.schema.BaseTable
import org.ktorm.schema.Column
import org.ktorm.schema.SqlType
import org.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 对象才能工作，而该对象的获取渠道是 SomeClass.serializer()，其中 SomeClass 需要被 @kotlinx.serialization.Serializable 注解。之后进行 Json 操作时为了最大的兼容性，通常都是认为操作的结果是 Any 而非特定一个类，那么问题就来了：Any 类似 Java 的 Object，是万物之父，而所有被 @Serializable 注解的类，可没有一个统一的父类。因此这样的架构在后续实现运算符的时候就会非常难受。最起码也是要实现了 Java 的 Serializable 接口（或其他框架的统一的接口）。

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

自定义运算符

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
package info.skyblond.jinn.extension

import org.ktorm.expression.ArgumentExpression
import org.ktorm.expression.ScalarExpression
import org.ktorm.schema.ColumnDeclaring
import org.ktorm.schema.IntSqlType
import org.ktorm.schema.SqlType
import org.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，然后 right 作为一个 SqlType 来实现更通用的功能的，但是这样一来后面的实现将无法保证只对 Json 类型的语句进行访问：JsonSqlType 要能够适配所有情况，其类型必定是 SqlType，这样一来将无法区分哪些表达式是 Json，哪些不是。当然你也可以额外在加一个类型：AsDataTypeExpression，T 作为 SqlType，而 U 直接存储 JsonSqlType，但是就本文而言，还是单独搞一个 AsJsonExpression 来的最实在。

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

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

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

扩展方言

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package info.skyblond.jinn.extension

import org.ktorm.database.Database
import org.ktorm.expression.SqlExpression
import org.ktorm.expression.SqlFormatter
import org.ktorm.support.postgresql.PostgreSqlDialect
import org.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，而右值则一定是数字或字符串类型的参数，因此左右皆无需增加括号。

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

结果

首先连接数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
val pgDataSource = PGDataSource()
pgDataSource.serverName = "localhost"
pgDataSource.portNumber = 5432
pgDataSource.user = "logbook"
pgDataSource.password = "***"
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())

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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：

1
2
3
4
5
6
7
8
9
10
11
DEBUG org.ktorm.database - SQL: select ... from qso_infos where ((qso_infos.qsl_info -> ?) ->> ?) = ? 
DEBUG org.ktorm.database - Parameters: [lotw(varchar), uploaded(varchar), true(varchar)]

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

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

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

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

- 全文完 -