Grails: named foreign keys

In Grails if we create a domain class with association we will get a foreign key for the association. Let’s see how the foreign key gets named using an example using a many-to-one association.

Here is a simple domain class with an association to itself.

package gorm

class Category {
    Category child
}

The create table for this class will look like this:

CREATE TABLE category (
    id bigint NOT NULL,
    version bigint NOT NULL,
    child_id bigint,
    CONSTRAINT category_pkey PRIMARY KEY (id),
    CONSTRAINT fk302bcfeabee40a7 FOREIGN KEY (child_id)
        REFERENCES category (id)
)

I have removed some noise from the statement, so do not wonder if it looks a little bit different for you :)

We can see that the primary key gets a human readable name category_pkey but the name of the foreign key is fk302bcfeabee40a7. Not too readable. It would be nice if we could give the foreign key a human readable name too. What about <COLUMN>_<TARGET_TABLE>_fkey to make it similar to the name of the primary key?

It looks like Grails does not offer any GORM DSL sugar to customize the name of the foreign key. But naming it manually on each domain class may be a bit cumbersome anyway. It would be nice if we could make it use the <COLUMN>__<TARGET_TABLE>_fkey format automatically.

It turns out we can do this with a few lines of code. The basic idea is described in Customizing GORM with a Configuration Subclass using some Java code and a hardcoded foreign key.

Here is a version in groovy implementing the format given above:

package gorm

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
import org.hibernate.mapping.PersistentClass
import org.hibernate.mapping.ForeignKey


class GormConfiguration extends GrailsAnnotationConfiguration {
    boolean hasRenamedForeignKeys = false

    @Override
    protected void secondPassCompile () {
        super.secondPassCompile ()

        if (renamedForeignKeys) {
            return
        }

        renameForeignKeys ()
        renamedForeignKeys = true
    }

    void renameForeignKeys () {
        classes.values().each { PersistentClass persistentClass ->
            persistentClass.table.foreignKeyIterator.each {ForeignKey key ->
                key.name = createHumanReadableName (key)
            }
        }
    }

    String createHumanReadableName (ForeignKey key) {
        "${key.columns.first().name}__${key.referencedTable.name}_fkey"
    }
}

It is not perfect. We do not check the length of the generated name and there are are probably other details I do not know anything about yet we have to take care of. But it it a start. :-)

The create table now look like this:

CREATE TABLE category (
    id bigint NOT NULL,
    version bigint NOT NULL,
    child_id bigint,
    CONSTRAINT category_pkey PRIMARY KEY (id),
    CONSTRAINT child_id__category_fkey FOREIGN KEY (child_id)
        REFERENCES category (id)
)

With a nicely named foreign key. :-)

What is left is to activate the code which is a one liner, we just have to add a configClass parameter to the datasource configuration:

    dataSource {
        configClass = gorm.GormConfiguration
        driverClassName = "org.postgresql.Driver"
        dialect = org.hibernate.dialect.PostgreSQLDialect
        dbCreate = "create"
        url = "jdbc:postgresql://localhost/gorm"
        username = "gorm"
        password = "gorm"
    }

That’s it.

Happy gorming :-)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s