I’ve been using Grails/Groovy for all my projects (mostly bio-medical informatics) since early 2010.
…
For now, I am supposed to implement a web survey application which should be just a bit long web form. I ended up creating a Grails/Groovy Domain Class which has more than 500 variables. (Maybe … close to 1000s. Yeah… it’s stupid to design that many variables in a single domain. Not a good excuse though – Original Questions/Answers sheet is just not good for design. I had to put all unorganized questions one by one very quickly. I can say… it’s very similar to online registration form with so much different kinds of questions I am not familiar with).
The default datasource for Grails, HSQL (in-memory and file-based) works without an issue. I deployed after changing to production setting with MySQL. Soon I got the startup failure. MySQL have an issue with numbers of columns (which is same as number of variables). So, if MySQL is the final, I have to divide a big domain into smaller ones. I am not sure the maximum number of columns in MySQL. Here is the clue: (http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html).
Next, I decided to try Oracle to solve it. but Oracle has different issue with my application – a single column name’s length should not exceed 30 characters.(http://www.dba-oracle.com/sf_ora_00972_identifier_is_too_long.htm)
I turned to Postgres which I have never used in my former projects. It took couple of hours to understand how it works – I just needed to figure out how to configure remote hosts to access the db on Postgres. I works finally.
If I had another database issue, I would try H2. H2 is built by the developer of HSQL too. It seems better – small footprint, fast, pure-java, and feature-rich, etc. Here is the comparisons (copied from http://www.h2database.com).
H2 | Derby | HSQLDB | MySQL | PostgreSQL | |
---|---|---|---|---|---|
Pure Java | Yes | Yes | Yes | No | No |
Memory Mode | Yes | Yes | Yes | No | No |
Encrypted Database | Yes | Yes | Yes | No | No |
ODBC Driver | Yes | No | No | Yes | Yes |
Fulltext Search | Yes | No | No | Yes | Yes |
Multi Version Concurrency | Yes | No | Yes | Yes | Yes |
Footprint (jar/dll size) | ~1 MB | ~2 MB | ~1 MB | ~4 MB | ~6 MB |
See also the detailed comparison.