Provided Column Type Not Included In Operators For Primitive Value Types

[baswerc]

I'm trying to understand why the provided column type is not included in the wrap function of SQLExpressionBuilder for the primitive data types below?

    fun <T, S : T?> ExpressionWithColumnType<in S>.wrap(value: T): QueryParameter<T> = when (value) {
        is Boolean -> booleanParam(value)
        is Byte -> byteParam(value)
        is UByte -> ubyteParam(value)
        is Short -> shortParam(value)
        is UShort -> ushortParam(value)
        is Int -> intParam(value)
        is UInt -> uintParam(value)
        is Long -> longParam(value)
        is ULong -> ulongParam(value)
        is Float -> floatParam(value)
        is Double -> doubleParam(value)
        is String -> QueryParameter(value, columnType) // String value should inherit from column
        else -> QueryParameter(value, columnType)
    } as QueryParameter<T>

This seems to limit custom column types of any of these primitive types. Is that intended?

[AlexeySoshin]

Hi @baswerc , Could you please elaborate a bit more on what the issue is?

[baswerc]

Sure thing @AlexeySoshin . Let's say I want to create a long column type that actually stores the value as timezoneless timestamp type in the database. Something like this:

fun Table.timestampMilliseconds(name: String): Column<Long> = registerColumn(name, TimestampMilliseconds())

class TimestampMilliseconds() : ColumnType() {
    override fun sqlType(): String = "TIMESTAMP"

    override fun valueFromDB(value: Any): Long {
        return if (value is Timestamp) {
            value.time
        } else {
            throw IllegalArgumentException("Unsupported timestamp milliseconds value of type ${value.javaClass}.")
        }
    }

    override fun valueToDB(value: Any?): Any? {
        return if (value == null) {
            null
        } else if (value is Long) {
            Timestamp(value)
        } else if (value is Timestamp) {
            value
        } else {
            throw IllegalArgumentException("Unsupported timestamp milliseconds value of type ${value.javaClass}.")
        }
    }

    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        if (value == null) {
            super.setParameter(stmt, index, value)
        } else if (value is Long) {
            super.setParameter(stmt, index, Timestamp(value))
        } else if (value is Timestamp) {
            super.setParameter(stmt, index, value)
            value
        } else {
            throw IllegalArgumentException("Unsupported timestamp milliseconds value of type ${value.javaClass}.")
        }
    }
}

This seems to work fine except when I'm doing a query something like this:

MyTable.select { MyTable.createdAt lessEq System.currentTimeMillis() }

In this case the TimestampMilliseconds.setParameters isn't called for the query statement because a LongColumnType is used from the wrap function.

Maybe I'm just going about this the wrong way?

[AlexeySoshin]

I assume that your column is defined as:

object MyTable {
   val createdAt = timestampMilliseconds("created_at")
}

Something like that?

[baswerc]

That's correct. Just like that.

[baswerc]

@AlexeySoshin Anymore thoughts on this? Should I be using a different approach for this?

[joc-a]

Hey @baswerc. Thank you for filing this issue. Just to better understand what you're trying to do, why create a custom type instead of using any of the other available datetime/timestamp types?

[baswerc]

@joc-a We use an Oracle database with some Timestamp columns and we'd like to to work with those as long representing milliseconds from the epoch. We're able to work around it it would just be a little cleaner if we could define our column as type long.

[baswerc]

Thanks for this update @joc-a!