Stuff I've learned recently…

After watching Jason Rudolph build a Grails application around an existing, legacy database, I decided to see if I could do the same. My Google Maps application (see my earlier posts on the subject) gave me a reason to finally put my courses taught data into a database. I still need a way to insert new data into that database, in a more user-friendly way than typing raw SQL at the MySQL command line.

Of course, that’s an ideal application for Grails. The Grails scaffolding builds controllers and views that can display domain objects that map to database tables, update existing objects, and even insert new ones. At my stage of learning Grails, it’s very useful for me to let the automatic generators build the basic application and then gradually replace the scaffolding with code I like.

This is also a nice app to experiment with, because at the moment it’s got a whopping three tables in it. There are three obvious domain objects, too: Course, Client, and Location. I’ve also got two one-to-many relationships between them, in that a client has many courses, and a location also has many courses. Nice and simple, but at least more than just a Grails version of Hello, World.

In Grails, it’s easiest to develop if you don’t already have a database. In a way, it’s ideal for domain-driven design, because you start with your domain objects and their relationships, and everything flows from there.

If I was starting from scratch, my domain classes (in Groovy) would look like:

class Client {
    int id
    String name

    static hasMany = [courses:Course]

    String toString() {
        return name
    }
}

for the Client, where the toString() override is so the relationship drop-down lists will be populated with just the name. Also, the hasMany closure establishes the relationship between Client and Course, at least on this end.

The Location class is

class Location {
    int id
    String city
    String state
    double latitude
    double longtitude

    static hasMany = [courses:Course]

    static constraints = {
        city(blank:false)
        state(blank:false)
        latitude()
        longitude()
    }

    String toString() {
        return "${city}, ${state}"
    }
}

The latitude and longitude fields are for the Google Maps application. The hasMany closure establishes the relationship between Location and Course. The constraints closure puts the various fields in the order listed, and ensures that neither city nor state can ever be empty. The resulting validation and highlighted error messages are part of the coolness Grails brings to the table.

Finally, the Course class is:

class Course {
    int id
    Date startDate
    Date endDate
    String title

    Location location
    Client client

    static belongsTo = [Client, Location]

    String toString() {
        return title
    }

    static constraints = {
        title(blank:false)
        startDate()
        endDate()
    }
}

The Course uses the belongsTo closure to establish the relationships in the other direction. The constraints closure is still mostly just for ordering; I imagine I’ll expand it later.

Now, if I didn’t have an existing database, I’d start with this, generate all the scaffolding, and let Grails create the database schema. The thing is, though, I already have the database schema, and some data to go in it.

As Jason showed, I could just let the Hibernate Tools reverse engineer mapping files from my schema. Then I could edit the mapping files to make the existing schema fit my domain classes.

Still, I thought I’d just edit the database to fit the Grails conventions (cue ominous music in the background). I’m doing this application as much to learn Grails as to solve my problem. Even better, I already have a Groovy script that can repopulate the tables if necessary using my existing DAO layer, currently written in Java with Spring and JPA.

The first change I made from my earlier schema was to make the table names singular instead of plural. The rest was fine (or so I thought). Each table had a primary key called id of type int. The course table has two foreign keys, each of which follows the naming convention (location_id and client_id). All of the column names map to the field names in the classes above, with camel case replaced by snake case (i.e., the startDate field maps to the start_date column).

Everything looked good, so I called the various Grails generators (grails create-app, grails create-domain-class, grails create-controller, and finally grails generate-scaffold) to see what happened.

As an aside, I also edited my DataSource.groovy file to point to my MySQL installation and to get rid of the dangerous “create-drop” capability. I didn’t want to overwrite the data by accident.

Unfortunately, I missed a detail. Grails doesn’t just add in a primary key field called id. It also expects a column called “version” in the database, of some integer type. This column is used for optimistic locking, so that if two people are editing a column at the same time, the one to commit first gets their changes written. The person who commits afterwards gets an error telling them that their data is stale, so they can’t commit until they refresh their data to the current values.

I don’t have version columns in my tables. Or, rather, I didn’t. I figured rather than drop everything, regenerate the tables and repopulate them from scratch, I could just execute “alter table” statements to add the new column to each of the three tables. I went ahead and did that.

The application then came up just as it should. I could see all the data, insert new rows, and delete the ones I inserted. Kewl. I spent some time editing the displays to order the data, make the views more visually appealing, and add functionality to search the courses by title and/or year (thanks to Jason’s great Getting Started With Grails book for all that).

What I couldn’t do, no matter what I tried, was to edit an existing object and have it successfully commit the changes. The web pages said that the changes were successful, so the save() method was supposedly returning true. The console didn’t show any errors, either. But nothing was changing in the database.

I really had to dig to figure that one out, but the Red Sox were losing and the Yankees were winning, so I kind of needed something to distract me anyway.

As it turned out the “grails shell” was the key. The Grails shell is a command-line script that allows you to write Groovy code that works with the existing domain classes. I loaded a course, modified its title, and tried to save it.

The resulting several hundred thousands of lines of the exception stack trace (Grails really can generate reams of lines of exceptions) somewhere had the word “version” in it.

I opened up a MySQL shell and looked at my data. Lo and behold, the version columns I added were there, and the data type was int, but of course all of the entries were null. Not zero, but null.

The optimistic lock tries to increment the value in that column. As it turns out, it’s hard to increment null.

The answer was to put zeros in all the version values. Everything worked just fine after that.

There’s a lot more I can and will do with this, but I have to admit, it was an excellent learning experience. It’s a bit daunting to know, however, that not having a version column meant that I almost had to switch to mapping files.