MySQL columns with "ON UPDATE" should be treated as Generated

[poirazis]

When a column in MySQL has the "ON UPDATE" expression, it should be treated as an auto generated column and BB should not submit a value.

Example :
Table in MySQL has a "updated_on" column, defined as
"updated_on datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),".

Columns of this type autogenerate their values ON UPDATE so BB should treat them as Auto / Generated

Ideas ?

[melohagan]

What happens if you assign a value to updated_on ?

E.g. UPDATE Table SET updated_on = <some_timestamp>

Will it keep the change you made or overwrite it with current_timestamp() ?

[poirazis]

If you explicitly set a value, it will take the value if no other fields are changed.

from the documentation

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to [CURRENT_TIMESTAMP]

(https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_current-timestamp)).

[melohagan]

I think that would be a potential problem then as Auto-generated columns in Budibase are read only, but there may be cases when a user does want to explicitly set a MySQL field that has an 'ON UPDATE'.

[poirazis]

Then lets look at it another way.
In the "Save Row" Action, you can set additional fields, but can you prevent a field from being submitted ? If there is a way to tell BB NOT to submit a value for a specific field then this enable us to utilize the "on update" functionality.

[melohagan]

I gotcha. What we could maybe do is have a Budibase level constraint on columns - a toggle for read only.
That way you could switch that on for on update fields, and if you tried to save a value to that column it would throw an error message "Cannot write to read-only column"

[poirazis]

Sounds like a fine solution to me @melohagan !