Exposed

Exposed copied to clipboard

Adds support for MySQL column DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP syntax.

This default statement is useful when other app updated the row, but without/forget assigning the "update time" column.

Test statements output

MySQL 5.5:

ERROR Test worker Exposed:descriptionDdl:95 - mysql 5.5 doesn't support expression 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' as default value. Column will be created with NULL marker.
SQL: CREATE TABLE IF NOT EXISTS foo (id INT AUTO_INCREMENT PRIMARY KEY, `name` TEXT NOT NULL, defaultDateTimeAutoUpdate DATETIME NULL)
SQL: SELECT CURRENT_TIMESTAMP
SQL: INSERT INTO foo (defaultDateTimeAutoUpdate, `name`) VALUES (CURRENT_TIMESTAMP, 'bar')
SQL: SELECT foo.id, foo.`name`, foo.defaultDateTimeAutoUpdate FROM foo WHERE foo.id = 1
SQL: DROP TABLE IF EXISTS foo

MySQL 8.0:

SQL: CREATE TABLE IF NOT EXISTS foo (id INT AUTO_INCREMENT PRIMARY KEY, `name` TEXT NOT NULL, defaultDateTimeAutoUpdate DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) NOT NULL)
SQL: SELECT CURRENT_TIMESTAMP
SQL: INSERT INTO foo (defaultDateTimeAutoUpdate, `name`) VALUES (CURRENT_TIMESTAMP(6), 'bar')
SQL: SELECT foo.id, foo.`name`, foo.defaultDateTimeAutoUpdate FROM foo WHERE foo.id = 1
SQL: INSERT INTO foo (`name`) VALUES ('baz')
SQL: SELECT foo.id, foo.`name`, foo.defaultDateTimeAutoUpdate FROM foo WHERE foo.id = 2
SQL: UPDATE foo SET `name`='bah' WHERE foo.id = 2
SQL: SELECT foo.id, foo.`name`, foo.defaultDateTimeAutoUpdate FROM foo WHERE foo.id = 2
SQL: DROP TABLE IF EXISTS foo

Please note that this syntax is not support with h2 even in MySQL mode. A real MySQL/MariaDB is needed for testing.

@xJoeWoo , as I can see from MySQL documentation ON UPDATE clause could be used even without DEFAULT part (what could be hard to implement with current Column.defaultExpression() approach. But one could want to default to some constant datetime value and then update with ON UPDATE.

I would prefer some kind of extention on Expression<T : Temporal> to use it like:

datetime("columnName").default(CurrentDateTime().onUpdateCurrentTimestamp())
or
datetime("columnName").default(dateTimeLiteral(LocalDateTime.MIN).onUpdateCurrentTimestamp())

BTW, MySQL 8.0.13+ has normal support for expressions in DEFAULT!

Please note that this syntax is not support with h2 even in MySQL mode. A real MySQL/MariaDB is needed for testing.

https://github.com/h2database/h2database/pull/959

According to this pull, it is supported in all modes with h2 now