Replacing GORM with Groovy Sql for Lightwieght Jobs

GORM, the Grails Object-Relational Mapping persistence framework provides a rational way to use Hibernate based on pojo (or groovy pogo) domain classes and basic conventions. Create a domain class, define some constraints, start up grails and viola! tables, columns, indexes are created without coding a line of SQL/DDL or creating annoying XML mappings.

But, what if you have a simple standalone process that requires single or multiple database access? Currently GORM has trouble working outside of grails, and creating a new grails project just to support a simple, no-client script is overkill. For that, I use groovy's database access classes in groovy.sql and a small custom library to easily access multiple data sources and provide simple query tools optimized for batch operations.

Here's an example: Tracking multiple UPS shipments. The application must access existing shipment requests from a local database, then query the UPS tracking remote service to discover shipment status. When a shipment is picked up or delivered, another local database is updated and a third system is updated via XML feed.

To summarize, the requirements are:

• provide simultaneous access to multiple databases
• query UPS tracking via HTTP requests
• create XML responses and write to a messaging system
  

Cron triggers this process multiple times during the working day. It runs on the production machine (1MB slice at slicehost), so should be kind to existing web and database applications serving the user base.

Design Implementation:
As always, I start with the tests. Using groovy test case, the database queries, inserts, and updates tests were created, then the target classes and methods. Queries are always the easiest, and updates are straight forward as well. Inserts with auto generated IDs are a bit tricker, but here is where groovy sql comes to the rescue.

Creating new tables presented a new problem: should I create SQL/DDL scripts? No! For this, it was easy enough to access the current grails application and create the new domain classes, run grails to have Hibernate create the new tables.

The groovy Sql class provides a method called executeInsert(). The magic in this method is that it returns an array of all auto-generated keys. So, if I'm working in MySql or Oracle or whatever, inserts all look the same--no database specific code required.

Database access defaults to the production environment but database tests must operate on the test database (similar to grails). This is easy to accomplish by simply overriding the default behavior in the test scripts.

Conclusions:

• Use groovy Sql for small, non-web applications that require single or multiple database access.
• Use Grails/GORM for all database/table creation and maintenance (no DDL scripts). This also has the additional benefit of enabling access to new tables through grails if required.
  
• Use executeInsert() to return auto-generated keys when inserting new rows.
  

Survey Project--A Grails Implementation

I started small grails application today to enable creating surveys and questions and tracking questions and results. The domain classes are:

• Survey: the primary table that defines a survey project
• SurveyQuestion: object to hold a specific question mapped to a survey
• SurveyResponder: the person that is answering the survey questions
• SurveyResponse: the recorded answers that the reponder enters

And here is the proposed ER diagram, created using dbDesigner.


As the diagram shows, surveys have many questions, questions have many responses. Surveys also have many responders and responders have many responses. You can also see that this is a very simple survey with no branches.

Grails/GORM Implementation:
I began by generating the Survey and Survey question domain classes and basic validation tests. Next was to create the SurveyReponder and SurveyResponse classes. The implementation was straight forward, but I had to fiddle with the domain class definitions to get hibernate to understand my intent. Just a matter of specifically defining class references in dependent models rather than depending on the 'belongs_to' and 'has_many' declarations.

Datasets and Loaders (Fixtures):
Unlike Rails, Grails doesn't use fixtures for test data. Lucky for me I have a Grails enhancement the solves this based on a sandbox proposal from the Grails guys. So I generated the dataset loader classes using: grails create-dataset [domain]. This creates the following:

• a DataLoaderBootStrap class in grails-app/conf/ (unless it exists)
• a DomainDataset class in src/groovy/datasets
• a set of random data to be loaded

An example of what gets created is here:

> grails create-dataset survey
> ...
> file grails-app/conf/DataLoaderBootStrap.groovy exists...
> created file src/groovy/datasets/SurveyDataset.groovy
>

The SurveyDataset class looks like this:

class SurveyDataset {
dataset = {
def set = [
[ name:'879',description:'510',email:'680' ],
]

return set
}

load() {
dataset().each { data ->
obj = new Survey( data )
obj.save()
}
}
}

As you can see, the data is contrived but all of the fields are present and other than the actual data, the script is good to go. I'll show how this data can be loaded from the console, command line and within test scripts in a later post.

10 Reasons to Switch from Rails to Grails

After spending a few years really enjoying Rails it was difficult to bring myself to even try groovy and grails. But my latest contract forced me to look for alternatives, and I'm glad I did. Here are some reasons that you may want to switch...

1. GORM with hibernate/spring and jpa is much better than ActiveRecord
2. No distribution problems; runs in many production ready containers
3. Internationalization out of the box (not specifically ignored as DHH does)
4. Transactions actually work, and they include save-points.
   
5. Not only dynamic finders and counters, but dynamic listOrderBy
   
6. No green threads (although this may be fixed in Ruby 2.0, around 2010?)
   
7. Ability to use pessimistic locking out of the box
   
8. Real-live prepared statements and .withCriteria method
9. Production level test reporting with built in Mocking and Stubbing
10. Search operations are based on Lucene (with a plugin)

All of these don't make sense for a non-java coder. And my startup time for grails would have be much longer without my prior experience with Rails and Ruby.

One thing I thought I might be giving up was the endless list of valuable gems that make developing in Rails a real pleasure. But after re-reviewing the list of open source products from apache, java source, spring, hibernate, and even Sun (glassfish), I can't think of any gems that I will miss.