SQLite Datetime column class cast exception about ktorm HOT 6 CLOSED

Most SQL database engines uses static, rigid typing. But SQLite uses a more general dynamic type system.

SQLite only supports five data types, they are NULL, INTEGER, REAL, TEXT, BLOB. There is not a data type set aside for storing dates and/or times. Dates and times are stored stored as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

More details can be found at: https://www.sqlite.org/datatype3.html

from ktorm.

In your case, you should not use the datetime function to store datetimes to SQLite. If you want to save datetimes as integers, you can write a custom implementation of SqlType:

fun <E : Any> BaseTable<E>.datetimeAsInteger(name: String): BaseTable<E>.ColumnRegistration<LocalDateTime> {
    return registerColumn(name, DateTimeAsIntegerSqlType)
}

object DateTimeAsIntegerSqlType : SqlType<LocalDateTime>(Types.INTEGER, "integer") {
    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: LocalDateTime) {
        val seconds = parameter.atZone(ZoneId.systemDefault()).toEpochSecond()
        ps.setLong(index, seconds)
    }

    override fun doGetResult(rs: ResultSet, index: Int): LocalDateTime? {
        val seconds = rs.getLong(index)
        return Instant.ofEpochSecond(seconds).atZone(ZoneId.systemDefault()).toLocalDateTime()
    }
}

Then define the hireAt column using this datetimeAsInteger function:

object Employees : Table<Employee>("employees") {
    val id by int("id").primaryKey().bindTo { it.id }
    val name by varchar("name").bindTo { it.name }
    val hiredAt by datetimeAsInteger("hired_at").bindTo { it.hiredAt }
}

from ktorm.

I didn't know that. Thanks.

Any suggestion on a good way to make the conversion "automatic" without forcing users to change the column type when they switch from, say, MySQL, to SQLite?

from ktorm.

Just saw your last reply. That works but then I have to force users to change their column type everytime they switch from one db provider to SQLite.

You might be wondering what is my use case. Well, I want to run unit tests on a sqlite database and real app with a full MySQL server. Ideally with other ORMs I do with a in-memory sqlite database. (On a side note, I could never get sqlite in-memory database to work with Ktorm.)

from ktorm.

Every database engine has its special features. It is difficult to find a perfect way to handle their differences. In you case, for compatibility reasons, you can always define your datetime columns using datetimeAsInteger, that works both in SQLite and MySQL.

BTW, SQLiteTest is an example using SQLite in memory mode with Ktorm.

from ktorm.

And if you are using SQLite just for running unit tests, testcontainers might be a better choice to do that.

from ktorm.