Array column type support

[lirispp]

Do you have any plan to support array column support?

[Tapac]

Very small batch of databases support array data type and provide related functions. So the only way to support it from Exposed side is through serialization/deserialization, which means that it wont be possible to use it in any "WHERE" predicates or check it with functions.

We don't see many benefits from adding such support, btw you can easily make it by your own with help of transform function:

object FooTable : IntIdTable() {
    val bar = text("bar")
}

class Foo(id: EntityID<Int>) : IntEntity(id) {
    var bar by FooTable.bar.transform(
            { a -> a.joinToString(SEPARATOR) }, 
            { str -> str.split(SEPARATOR).map { it.toIntOrNull() }.toTypedArray() }
    )
    
    companion object : IntEntityClass<Foo>(FooTable, Foo::class.java) {
        const val SEPARATOR = ":"
    }
}

fun main(args: Array<String>) {
    val new = Foo.new {
        bar = arrayOf(1, 3, 5)
    }
    
    new.bar += 5
}

[lirispp]

This would work for some situations. But major databases like MySQL / Postgres / Oracle support array and have very useful functions for array.

[Tapac]

@lirispp , I can't find info about Array Type in MySQL, also SQLite doesn't support it. But we can think how to add that type support in future.

[lirispp]

Sorry. MySQL doesn't have array type. But we can use json type instead of array type.

[Tapac]

json type only available since MySQL 5.7 version.

[lirispp]

java.sql.ResultSet supports Array type. I think it is reasonable to support array type.

[phase]

PostgreSQL supports array types, and they massively come in handy.

[swistaczek]

Hey, thanks for contributing Exposed to opensource. Is there any chance for pgsql Array Types support for Exposed?

[riyadparvez]

We cannot enroll our own array column type because of this line https://github.com/JetBrains/Exposed/blob/master/src/main/kotlin/org/jetbrains/exposed/sql/ColumnType.kt#L38, which unpacks the array and breaks the code.

[doxavore]

@riyadparvez Are you creating your own class that implements IColumnType, as in @lirispp's example linked above? Could you override valueToString with your own custom Iterable<*> handling before handing off other branches to IColumnType's default implementation?

[riyadparvez]

Yes I am doing more or less similar to @lirispp example. There's no valueToString to override.

fun Table.uuidArray(name: String): Column<List<UUID>>
        = registerColumn(name, UUIDArrayColumnType())

private class UUIDArrayColumnType : ColumnType() {
    override fun sqlType() = "uuid[]"
    override fun valueFromDB(value: Any): List<UUID> {
        return if (value is PgArray) {
            val array = value.array
            if (array is Array<*>) {
                array.map {
                    it as UUID
                }
            }
            else {
                throw Exception("Values returned from database if not of type kotlin Array<*> ")
            }
        }
        else throw Exception("Values returned from database if not of type PgArray")
    }
    
    override fun notNullValueToDB(value: Any): Any {
        throw java.lang.UnsupportedOperationException("Please use the helper method to insert in db")
    }

    override fun nonNullValueToString(value: Any): String {
        return "'${notNullValueToDB(value)}'"
    }
}

By the time notNullValueToDB is called the Iterable is already unpacked by exposed and it breaks the code.

[doxavore]

Interesting - ColumnType is a pretty light abstract class implementing IColumnType, and I'm able to override fun valueToString(value: Any?): String on the latest released version of 0.12.2.

[riyadparvez]

My bad. Yes, @lirispp solution works.

[wangbo223]

Kotlin please officially support array type, it's very unwiedy since very large community is using Postgres and it does have array types

[vyasenenko]

Hi! I made a implementation array column type for postgres..

fun Table.arrayOfLong(name: String): Column<List<Long>> =
    registerColumn(name, LongArrayColumnType())

internal class LongArrayColumnType : ColumnType() {
    override fun sqlType() = "BIGINT[]"
    override fun valueFromDB(value: Any): List<Long> = when (value) {
        is Iterable<*> -> value.map { it.toString().toLong() }
        is PgArray -> {
            val array = value.array
            if (array is Array<*>) {
                array.map {
                    when (it) {
                        is Long -> it
                        is Number -> it.toLong()
                        null -> error("Unexpected value of type Long but value is $it")
                        else -> error("Unexpected value of type Long: $it of ${it::class.qualifiedName}")
                    }
                }
            } else {
                throw Exception("Values returned from database if not of type kotlin Array<*>")
            }
        }
        else -> throw Exception("Values returned from database if not of type PgArray")
    }

    override fun valueToString(value: Any?): String = when (value) {
        null -> {
            if (!nullable) error("NULL in non-nullable column")
            "NULL"
        }

        is Iterable<*> -> {
            "'{${value.joinToString()}}'"
        }

        else -> {
            nonNullValueToString(value)
        }
    }

    override fun setParameter(stmt: PreparedStatement, index: Int, value: Any?) {
        if (value is List<*>) {
            stmt.setArray(index, stmt.connection.createArrayOf("bigint", value.toTypedArray()))
        } else {
            super.setParameter(stmt, index, value)
        }
    }
}

[MrPowerGamerBR]

This is my version, based on @lirispp's implementation.

This version works with Exposed 0.24.1 while previous versions in this thread doesn't due to Exposed's internal changes.

This is also a "generic" version, so you don't need to create a different array column type every time you want to use a different type.

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.statements.jdbc.JdbcConnectionImpl
import org.jetbrains.exposed.sql.transactions.TransactionManager

fun <T> Table.array(name: String, columnType: ColumnType): Column<Array<T>> = registerColumn(name, ArrayColumnType(columnType))

class ArrayColumnType(private val type: ColumnType) : ColumnType() {
	override fun sqlType(): String = buildString {
		append(type.sqlType())
		append(" ARRAY")
	}
	override fun valueToDB(value: Any?): Any? {
		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 (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 (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)
		}
	}
}

class AnyOp(val expr1: Expression<*>, val expr2: Expression<*>) : Op<Boolean>() {
	override fun toQueryBuilder(queryBuilder: QueryBuilder) {
		if (expr2 is OrOp) {
			queryBuilder.append("(").append(expr2).append(")")
		} else {
			queryBuilder.append(expr2)
		}
		queryBuilder.append(" = ANY (")
		if (expr1 is OrOp) {
			queryBuilder.append("(").append(expr1).append(")")
		} else {
			queryBuilder.append(expr1)
		}
		queryBuilder.append(")")
	}
}

class ContainsOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "@>")

infix fun<T, S> ExpressionWithColumnType<T>.any(t: S) : Op<Boolean> {
	if (t == null) {
		return IsNullOp(this)
	}
	return AnyOp(this, QueryParameter(t, columnType))
}

infix fun<T, S> ExpressionWithColumnType<T>.contains(arry: Array<in S>) : Op<Boolean> = ContainsOp(this, QueryParameter(arry, columnType))

Source: https://github.com/LorittaBot/Loritta/blob/development/loritta-discord/src/main/java/com/mrpowergamerbr/loritta/utils/exposed/array.kt

[Doomsdayrs]

I believe googles Room persistence library serializes arrays into a string for SQLite, then de serializes them.

For basic types such as Int, Long, String, etc

[lyndsysimon]

Just an anecdote - I'm working on a small service that uses an existing PostgreSQL database. It's the first project in our organization using Kotlin, so will likely serve as a template of sorts for future projects.

I originally chose Exposed for my ORM, but had to switch to ktorm because we already have text[] columns in the database.

So, in my case, Exposed's lack of array and hstore types prevented me from using it.

[lyndsysimon]

Out of curiosity, if someone were to provide a patch for Exposed that supported Array, json, jsonb, and hstore types, would you be willing to include it in the next release?

I've already had to use my nascent Kotlin skills to patch ktorm in a couple of instances, so it's not as intimidating to me now as it was a few weeks ago...

[Tapac]

@lyndsysimon , sure any PR is very welcome.

[sabiou]

This is my version, based on @lirispp's implementation.

This version works with Exposed 0.24.1 while previous versions in this thread doesn't due to Exposed's internal changes.

This is also a "generic" version, so you don't need to create a different array column type every time you want to use a different type.

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.statements.jdbc.JdbcConnectionImpl
import org.jetbrains.exposed.sql.transactions.TransactionManager

fun <T> Table.array(name: String, columnType: ColumnType): Column<Array<T>> = registerColumn(name, ArrayColumnType(columnType))

class ArrayColumnType(private val type: ColumnType) : ColumnType() {
	override fun sqlType(): String = buildString {
		append(type.sqlType())
		append(" ARRAY")
	}
	override fun valueToDB(value: Any?): Any? {
		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 (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 (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)
		}
	}
}

class AnyOp(val expr1: Expression<*>, val expr2: Expression<*>) : Op<Boolean>() {
	override fun toQueryBuilder(queryBuilder: QueryBuilder) {
		if (expr2 is OrOp) {
			queryBuilder.append("(").append(expr2).append(")")
		} else {
			queryBuilder.append(expr2)
		}
		queryBuilder.append(" = ANY (")
		if (expr1 is OrOp) {
			queryBuilder.append("(").append(expr1).append(")")
		} else {
			queryBuilder.append(expr1)
		}
		queryBuilder.append(")")
	}
}

class ContainsOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "@>")

infix fun<T, S> ExpressionWithColumnType<T>.any(t: S) : Op<Boolean> {
	if (t == null) {
		return IsNullOp(this)
	}
	return AnyOp(this, QueryParameter(t, columnType))
}

infix fun<T, S> ExpressionWithColumnType<T>.contains(arry: Array<in S>) : Op<Boolean> = ContainsOp(this, QueryParameter(arry, columnType))

Source: https://github.com/LorittaBot/Loritta/blob/development/loritta-discord/src/main/java/com/mrpowergamerbr/loritta/utils/exposed/array.kt

Hi @MrPowerGamerBR I'm trying to implements this solution for something like: val departments = array("departement", ColumnType ) . What should be my ColumnType in this case ? Given departments is a list of Department.

[TheReprator]

@sabiou use the above in following way, val databaseCompanyOwner = array<Int>("subOwners", IntegerColumnType() )

[sabiou]

@sabiou use the above in following way, val databaseCompanyOwner = array("subOwners", IntegerColumnType() )

Thanks @TheReprator

[Doomsdayrs]

Hi, Any update? Any native implementation of exposed?

This is irrelevant to the issue

[DRSchlaubi]

I have my working solution here: https://gist.github.com/DRSchlaubi/cb146ee2b4d94d1c89b17b358187c612

[frynet]

Based on the above ideas I have something like this: https://gist.github.com/frynet/59b68faee6927bb95302a7d290e1cff8

@Tapac I have no time for PR yet. But you can consider my gist for the basis. It works fine in my case (I needed to save array Enums and use it for filtering query).

Any enthusiasts can finish the code to make it better :smiley:

[williamboxhall]

Thank you so much @bog-walk! When can we expect this to be included in a release?

[bog-walk]

@williamboxhall We aim for monthly releases at the end of every month. When the release is available, it will also be announced on the Slack channel. If you run into any issues with the new column type or if you'd like to share feedback later, please consider opening a ticket on YouTrack.