Wednesday, May 5, 2010

Grails, GORM database migration

Yesterday I ran into an interesting problem on a Grails application deploy.

I am writing a financial system in grails. It's a work in progress and so far it's been ticking along ok. Early on, I defined my amounts as 'float' in the domains.

A few days ago we started testing huge amounts for transactions, and realised that they get rounded past 6 digits. So, 1234567.89 would turn out as 1234570.00

The problem: floats in java are not very large, and doubles would have been a better choice. Fair enough, I'm not a java person, so I dont even feel bad about this. underpinning all this is that Grails then magically creates a database table using the mysql float type to match. It has the same limitation.

So, change all the domains where this is of importance and redeploy right ? Wrong. Grails is to addled to tell that I have changed my type from float to double, and does nothing to automagically update my table definition.

So, I ran a schema-only dump in my fresh (correct) dev database, and a data-only dump in my production environment. I then recreated the database with the new schema and overlaid the old data onto it. Dumb luck is that the change was just from float to double and the data could insert fine.

This all got me looking into decent db migration tools for grails. Of which there are none. Liquibase and associated Autobase for grails are just silly with a cludgy DSL. Grails simply has nothing that compares to the ease and elegance of Rails migrations.

When I started this project, the hair on the backk of my neck went up because DB control was left with the framework... seems me foreboding was not misplaced.

2 comments:

  1. You shouldn't use floating point data types for financial calculations (either float or double), as the link I attach says. Take a look at it or google for "representing money in java". Float or doubles always carry small rounding errors, even for small numbers.
    The article is this: http://www.javapractices.com/topic/TopicAction.do?Id=13

    ReplyDelete
    Replies
    1. I fail to understand how this day and age, anyone takes a language seriously with such glaring problems in it. Why do we put up with it ?

      Can the JVM team not just do a bit of work and sort this out? What is the benefit of having rounding errors on numerical types, or is the cost of fixing it so very high ?

      Delete