postgresqlkotlinkotlin-exposed

How to add array Column type in Jetbrains Exposed


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
);

Solution

  • 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)
            }
        }
    }