GORM read only columns

Question

Most of our tables have one or more columns which are set by the database, either by a trigger, or we want to use the database default value (which requires not sending the field at all in the insert or update)

This includes transaction dates set in the dB (so all the times are times stamped very accurately by a single source, not relying on the accuracy of the time on an arbitrary server or pc.)

The second VERY common use case is say if a customer record has his address and a last logged in field. the last logged in field (and number of failed logins) is set by another part of the system (e.g. by a web site server). The current overly simplistic CRUD system which GORM provides would overwrite such a field when an operator or customer edits their address for example. This is because GORM includes in its update and insert statements every field, even if it's null, or if it has not been changed.

We need a way to scrub the field from inserts and updates, but still have it used in the read calls. I.e. a true "read only" attribute.

We tried this:

    failedLogins editable: false, attributes: [readonly:true]

Which has no effect on the SQL generated (and doesn't even affect the scaffolded UIs - its still editable in create and edit, in grails 2.4.4 at least, but thats another story)

When we do want to explicitly write one of these fields, such as number of failed logins, we would resort to using embedded SQL.

I saw this post: Read-Only columns

Which asks exactly the same question, but only gives one solution, which is this plugin:

extended GORM mappings

Unfortunately, this plugin has not been updated since 2010, and only works with 1.3. We need something which works with 2.4.4.

Any grails app which has multiple systems which edits independent fields needs something like this, or to do extensive locking (Which is usually out of the question).

E.g. an operator opens the customer details for editing, edits something editable (e.g. address), then the operator fails a login on the website (a different grails or non-grails app), then the operator saves the player details. If the saving included the numberOfFailedLogins field, the system would fail. If opening the player details for editing locked the player, then the player would not be able to login, as updating the "lastLoggedIn" or "numFailedLogins" would fail to be able to write due to the lock. The solution is VERY simple - read only columns. Another way would be to put each read only type field in their own tables, but this would be untenable (and result in hundreds of one field tables)

Or we go back to using MyBatis, which has no such issues, and full control. Sadly, there is no good mybatis plugin for grails.

Answer 1

You can use derived properties for string and number properties:

class Batch {
    String name
    Integer timesRun

    static mapping = {
        timesRun formula: 'times_run' //times_run is a column in the "batch" table
    }
}

In the code above, timesRun would be read in from the database but ignored in inserts and updates as Hibernate considers the column a calculated one.

Updated the example because the original one may have been misleading