Support for computed columns?

[carbotaniuman]

I tried hacking the code to do a computed column, but I didn't figure out how yet.

[vincentlauvlwj]

How do you think about this solution?

interface Dept : Entity<Dept> {
    val id: Int
    val name: String
    val upperName get() = name.toUpperCase()
}

[carbotaniuman]

I'm mainly thinking about it in the context of writing SQL queries, having a variable that only allows for query (SQL) operations would be nice, but I haven't looked into it.

[efenderbosch]

Came to ask for the same thing. I can almost do it using a custom FunctionExpression, but it would be much easier if BaseTable's column attribute (val columns: List<Column<*>> get() = _columns.values.toList()) was open.

Our basic problem is that writing a LocalTime to MySQL and then reading it are not equal. Millisecond precision is dropped. I can get around it by writing usingPreparedStatement::setString and reading using ResultSet::getString inside a custom SqlType and selecting TIME_FORMAT(column_name, '%H:%i:%s.%f') as column_name instead of the actual column. Unfortunately, I need this modified "column" in several places. It could look like a standard column if BaseTable's column attribute was open.

I want columns to return a custom list like: MyTable.columns - MyTable.realColumn + somefunction(MyTable.realColumn).aliased(MyTable.realColumn.label)

And my concrete example: MyTable.columns - MyTable.startTime + timeFormat(MyTable.startTime).aliased(MyTable.startTime.label)

This changes my projection from: select * to: select <all columns other than start_time>, TIME_FORMAT(start_time, '%H:%i:%s.%f') as start_time

For reference, here's the custom SqlType for LocalTime :

val localTimeFormatter: DateTimeFormatter by lazy {
    DateTimeFormatterBuilder()
        .appendPattern("HH:mm:ss")
        // JavaScript will likely only send milliseconds, not microseconds
        .appendFraction(ChronoField.NANO_OF_SECOND, 3, 6, true)
        .toFormatter()
}

fun <E : Any> BaseTable<E>.localtime(name: String): BaseTable<E>.ColumnRegistration<LocalTime> =
    registerColumn(name, LocalTimeSqlType)

object LocalTimeSqlType : SqlType<LocalTime>(Types.TIME, typeName = "localtime") {
    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: LocalTime) {
        val time = localTimeFormatter.format(parameter)
        ps.setString(index, time)
    }

    override fun doGetResult(rs: ResultSet, index: Int): LocalTime? {
        val time = rs.getString(index)
        return if (rs.wasNull()) {
            null
        } else {
            localTimeFormatter.parse(time, LocalTime::from)
        }
    }
}

And the custom TIME_FORMAT function:

fun timeFormat(
    column: ColumnDeclaring<LocalTime>,
    format: String = "'%H:%i:%s.%f'"
): FunctionExpression<LocalTime> = FunctionExpression(
    functionName = "TIME_FORMAT",
    arguments = listOf(column.asExpression(), SqlFragmentExpression(format)),
    sqlType = LocalTimeSqlType
)

data class SqlFragmentExpression(val sqlFragment: String): ScalarExpression<String>() {
    override val extraProperties: Map<String, Any>
        get() = emptyMap()
    override val isLeafNode: Boolean
        get() = true
    override val sqlType: SqlType<String>
        get() = VarcharSqlType
}

[efenderbosch]

Alternatively, some sort of "selectAs" function might work.

val startTime by localtime("start_time").selectAs { timeFormat(it.name) }

Then BaseTable::columns could remain closed, but have the correct representation of the computed column everywhere automatically. Note that having a table with any column using a "selectAs" would mean that the * projection can not be used.

[carbotaniuman]

@vincentlauvlwj I looked at the code, and refactoring Ktorm to have a ReadOnlySqlType and a SqlType looks like it would work. Would you take that PR?