I am trying to add a column of type array to my Postgres table using exposed.The goal is to have a statement like:
UPDATE posts
SET like_user_id = like_user_id || $1, likes = likes + 1
WHERE NOT (like_user_id @> $1)
AND pid = ($2)
posts table:
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 framework does not have support for array of column type natively, you need to implement it yourself. Here's a generic version I found while trying to do the same thing 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)
}
}
}