postgresql - 如何在 Jetbrains Exposed 中添加数组列类型
问题描述
我正在尝试使用暴露的将数组类型的列添加到我的 Postgres 表中。目标是有如下语句:
UPDATE posts
SET like_user_id = like_user_id || $1, likes = likes + 1
WHERE NOT (like_user_id @> $1)
AND pid = ($2)
帖子表:
CREATE TABLE posts (
pid SERIAL PRIMARY KEY,
title VARCHAR(255),
body VARCHAR,
user_id INT REFERENCES users(uid),
author VARCHAR REFERENCES users(username),
date_created TIMESTAMP
like_user_id INT[] DEFAULT ARRAY[]::INT[],
likes INT DEFAULT 0
);
解决方案
Kotlin Exposed 框架原生不支持列类型数组,需要自己实现。这是我在尝试做同样的事情时发现的通用版本https://github.com/LorittaBot/Loritta/blob/db577852a76266d207361b7d8257d24b4ee0b947/platforms/discord/legacy/src/main/java/com/mrpowergamerbr/loritta/utils/exposed /array.kt
fun <T> Table.array(name: String, columnType: ColumnType): Column<Array<T>> = registerColumn(name, ArrayColumnType(columnType))
class ArrayColumnType(private val type: ColumnType) : ColumnType() {
private fun supportsArrays() = !loritta.config.database.type.startsWith("SQLite")
override fun sqlType(): String = buildString {
if (!supportsArrays()) {
append("TEXT")
} else {
append(type.sqlType())
append(" ARRAY")
}
}
override fun valueToDB(value: Any?): Any? {
if (!supportsArrays())
return "'NOT SUPPORTED'"
if (value is Array<*>) {
val columnType = type.sqlType().split("(")[0]
val jdbcConnection = (TransactionManager.current().connection as JdbcConnectionImpl).connection
return jdbcConnection.createArrayOf(columnType, value)
} else {
return super.valueToDB(value)
}
}
override fun valueFromDB(value: Any): Any {
if (!supportsArrays()) {
val clazz = type::class
val clazzName = clazz.simpleName
if (clazzName == "LongColumnType")
return arrayOf<Long>()
if (clazzName == "TextColumnType")
return arrayOf<String>()
error("Unsupported Column Type")
}
if (value is java.sql.Array) {
return value.array
}
if (value is Array<*>) {
return value
}
error("Array does not support for this database")
}
override fun notNullValueToDB(value: Any): Any {
if (!supportsArrays())
return "'NOT SUPPORTED'"
if (value is Array<*>) {
if (value.isEmpty())
return "'{}'"
val columnType = type.sqlType().split("(")[0]
val jdbcConnection = (TransactionManager.current().connection as JdbcConnectionImpl).connection
return jdbcConnection.createArrayOf(columnType, value) ?: error("Can't create non null array for $value")
} else {
return super.notNullValueToDB(value)
}
}
}
推荐阅读
- windows - 在四个 3840 x 2160 屏幕上自动打开和定位八个网页 (Windows 10)
- soap - C0000005 ACCESS_VIOLATION 异常与 Progress Openedge 调用 Web 服务时
- python - 通过“文件名”检索 GridFS 文档的文档“_id”
- javascript - 我们如何在打字稿中创建带有前缀/后缀单词的字符串类型?
- excel - 如何在excel中将a+bi表单数据更改为复数?
- mysql - 在远程主机上执行 mysql 查询时转义 mysql 密码中的特殊字符
- forms - 如果未选中复选框(= 请求为空),则不处理 Symfony 表单
- python - 如何获取值具有特定值的行的索引?
- html - 将 img 定位在其父 div 的底部,而不使任何位置成为绝对位置
- amazon-web-services - 为 AWS 成本资源管理器创建 VPC 终端节点