Hello! I'm trying to migrate to Exposed 0.54 but I...

# exposed

[Rafał Kuźmiński]

Hello! I'm trying to migrate to Exposed 0.54 but I could not figure out few things. I checked readme and docs from git but still I encountered obstacles.
First:

class Replace<T : String?>(
    val expr: Expression<T>,
    val firstValue: String,
    val secondValue: String
) : Function<T>(TextColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
        append("replace(")
        append(expr)
        append(",'$firstValue','$secondValue'")
        append(")")
    }
}

There new version fails on "TextColumnType()" with error:
IColumnType<T & Any>

We have many "custom" functions and all of them fails with "IColumnType<T & Any>" error

Second:

fun <T : Any> ExpressionWithColumnType<T>.jsonArrayAgg(serializer: KSerializer<T>) =
    CustomFunction<PgArray>(
        "ARRAY_AGG",
        JsonColumnType(
            { Serialization.commonJson.encodeToString(serializer, it) },
            { Serialization.commonJson.decodeFromString(serializer, it) }),
        this
    )

I have custom expression for jsonArrayAggregation but it fails with "Type mismatch. Required: PgArray Found:T"
I am not sure what to do because I want this to be used on column with type "T" but it always will return PgArray because of aggregation

Third:
Custom column type now requires implementation for "valueFromDB". I check earlier version and before it was just returning value. If it was working, then should I just cast value to PGobject?

class GeometryColumnType : ColumnType<PGobject>() {
    override fun sqlType(): String {
        return "geometry"
    }

    override fun valueFromDB(value: Any): PGobject? {
        TODO("Not yet implemented")
    }
}

[Chantal Loncle]

Hi @Rafał Kuźmiński These changes are following refactoring towards a stricter column type safety.

Any function, operator, etc must now clearly define a type parameter that is representative of the value type returned by the database. The latter must match the associated ColumnType<?> that should be used by Exposed to process values sent to and returned from the db and in loggers etc.

For the first issue, any custom Function<?> must match the ColumnType<?> passed:

class Replace<T : String?>(
    val expr: Expression<T>,
    val firstValue: String,
    val secondValue: String
) : Function<String?>(TextColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
        append("replace(")
        append(expr)
        append(",'$firstValue','$secondValue'")
        append(")")
    }
}

For the second issue, it's the same thing: CustomFunction<?> must have a type that can be associated with and processed appropriately by a ColumnType<?>. I'm not sure how your function was making JsonColumnType handle `PgArray`s, but if the goal is to purposefully return an unprocessed PgArray so you can perform some business logic to it outside the column type, then there would need to be some custom ColumnType<PgArray>. If that is not the specific goal, I've shared some examples below using built-in column types:

// assuming the basic table and data class
@Serializable
data class Item(val name: String, val amount: Int)

object Users : Table("users") {
    val age = integer("age")
    val item = json<Item>("item", Json.Default)
}

// and 2 options for ARRAY_AGG plus 1 for JSON_AGG
fun <T : Any> ExpressionWithColumnType<T>.arrayAgg(delegate: ColumnType<T>) =
    CustomFunction<List<T>>(
        "ARRAY_AGG",
        ArrayColumnType(delegate),
        this
    )

fun <T : Any> ExpressionWithColumnType<T>.jsonArrayAgg(serializer: KSerializer<T>) =
    CustomFunction<T>(
        "ARRAY_AGG",
        JsonColumnType(
            { Json.Default.encodeToString(serializer, it) },
            { Json.Default.decodeFromString(serializer, it) }
        ),
        this
    )

fun <T : Any> ExpressionWithColumnType<T>.jsonAgg(serializer: KSerializer<Array<T>>) =
    CustomFunction<Array<T>>(
        "JSON_AGG",
        JsonColumnType(
            { Json.Default.encodeToString(serializer, it) },
            { Json.Default.decodeFromString(serializer, it) }
        ),
        this
    )

// example usage
val ageAgg = Users.age.arrayAgg(IntegerColumnType())
Users.select(ageAgg).toList()
// [30, 40, 50]

val itemAgg1 = Users.item.jsonArrayAgg(Item.serializer())
Users.select(itemAgg1).toList()
// {"{"name":"A","amount":3}","{"name":"B","amount":5}","{"name":"C","amount":9}"}

val itemAgg2 = Users.item.jsonAgg(ArraySerializer(Item.serializer()))
Users.select(itemAgg2).toList()
// [Item(name=A, amount=3), Item(name=B, amount=5), Item(name=C, amount=9)]

For the third issue, we usually process `PGobject`s to another type inside the ColumnType rather than returning them as-is via ColumnType<PGobject>. Please see custom column types documentation for examples of what I mean. If the goal is to return PGobject unchanged then yes, implementation of valueFromDB() just requires that the return type be clearly defined. So you should be able to just cast and return if you're sure that is the value type that will be retrieved from the database and you don't want to process it.

[Rafał Kuźmiński]

Thank you very much! This is very helpful. About second issue, we were using jsonArrayAgg to read aggregated timestamp values for table like this:

object SomeTable : Table() {
    val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())
}

fun main() {

    SomeTable.select(SomeTable.createdAt.jsonArrayAgg(InstantSerializer))
        ...
}

because arrayAgg threw weird exception related to Instant class cast exception and someone in this channel recommended using json serializer 😉 I'm sure it is not best way, but it worked so far. Anyway I'll try to use your guide to update library tomorrow, thanks again!

[Rafał Kuźmiński]

I do wonder why in this example:

fun <T : Any> ExpressionWithColumnType<T>.arrayAgg(delegate: ColumnType<T>) =
    CustomFunction<List<T>>(
        "ARRAY_AGG",
        ArrayColumnType(delegate),
        this
    )

do we have to provide delegate with ColumnType? I understand that aim was to provide clear type for every function, but we have type inside "ExpressionWithColumnType". Using this type is now resulting in error:

fun <T : Any> ExpressionWithColumnType<T>.arrayAgg() = CustomFunction<List<T>>(
    "ARRAY_AGG",
    ArrayColumnType(columnType),
    this
)

Error "Required:
ColumnType<TypeVariable(E) & Any>
Found:
IColumnType<T>"

It is hard for me to understand because both ExpressionWithColumnType and returning value of CustomFunction are built on top of the same type "T"

[Chantal Loncle]

ExpressionWithColumnType<T>.columnType property stores a value of type IColumnType<T & Any> (the base interface).

ArrayColumnType has a more restrictive constructor that requires a delegate property of type ColumnType (any instance of the abstract class).

If you don't want to manually specify the delegate type, but would rather rely on the base type of the invoking expression, you could cast:

fun <T : Any> ExpressionWithColumnType<T>.arrayAgg() =
    CustomFunction<List<T>>(
        "ARRAY_AGG",
        ArrayColumnType(this.columnType as ColumnType<T>),
        this
    )

This should work for any invoking types, except for types that are actually just wrappers for other types, like AutoIncColumnType. Meaning, if the above arrayAgg() is called on the auto-incrementing integer id of a table, the cast will fail because ArrayColumnType needs the actual column type of the integer delegate that is wrapped.
You could also go as far as to create a function that resolves the types in whatever way you see fit, if you need more control:

inline fun <reified T : Any> ExpressionWithColumnType<T>.arrayAgg() =
    CustomFunction<List<T>>(
        "ARRAY_AGG",
        ArrayColumnType(getDelegateType(T::class)),
        this
    )

fun <T : Any> getDelegateType(
    klass: KClass<T>
): ColumnType<T> {
    return when (klass) {
        Instant::class -> KotlinInstantColumnType()
        LocalDateTime::class -> KotlinLocalDateTimeColumnType()
        // ...
        else -> // error handling etc
    } as? ColumnType<T>
}

Please be also aware that, in addition to columns, some functions and operators are also ExpressionWithColumnType, so this could potentially be part of a chained call if you're trying to implicitly resolve the final column type.