Hooking into GORM events from plugin in Grails

we are working on Grails plugin that caches Domain objects in Node.js app. The Grails plugin purpose is to keep the cache current. In this plugin, we listen GORM events, so that once insert or update happens, we can tag the particular instance dirty in the cache. There are two ways to accomplish this that i am aware:

1. Via Groovy metaClass method

In the plugin configuration file plugin/nameOfThePluginGrailsPlugin.groovy we overload the GORM events methods – afterUPdate and afterInsert as follows:

import org.codehaus.groovy.grails.commons.GrailsClassUtils as GCU
...
 def doWithDynamicMethods = { ctx ->
        def service = ctx.nodeDriverProxyService
        application.domainClasses.each{ cClass ->
          def isCached = GCU.getStaticPropertyValue(cClass.clazz, "isCached")
          if(isCached){
            cClass.metaClass.afterUpdate = { ->
                service.registerUpdate(delegate.id, delegate.version)
            }
          }
        }
...

This goes throw each of the domain classes in the application and finds those that has property ‘isCached’ set to true. Afterwards, using groovy metaclass overloads the ‘afterUpdate’ method called each time the domain instance updated

Here we also call the service that does the work. Since ‘ctx’ is Spring ApplicationContext, we are able directly access the instance of our service – NodeDriverProxyService

This is probably not recommended approach, because once someone adds the method ‘afterUpdate’ to the Domain class itself, it overwrites our dynamic method. This is reasons why its good to use Custom Event Listeners instead.

2. Custom Event Listener

Instead attaching dynamic method to overwrite Domain callback methods ‘afterUpdate’ and ‘afterInsert’, we create custom event listener in src/Groovy:

class CacheListener extends AbstractPersistenceEventListener{
    def nodeDriverProxyService

    public CacheListener(final Datastore datastore) {
        super (datastore)
    }

    @Override
    protected void onPersistenceEvent(final AbstractPersistenceEvent event) {
        switch(event.eventType) {
            case PostInsert:
                if(event.entityObject?.isCached){
                    nodeDriverProxyService.registerInsert(event.entityObject.id, event.entityObject.version)
                }
                break
            case PostUpdate:
                if(event.entityObject?.isCached){
                    nodeDriverProxyService.registerUpdate(event.entityObject.id, event.entityObject.version)
                }
                break;
        }
    }

    @Override
    public boolean supportsEventType(Class eventType) {
        return true
    }

}

In our custom listener, the method ‘onPersistenceEvent’ is called on all GORM events that we filter to what we interested – PostInsert, PostUpdate
Afterwards, we register the listener to ApplicationContext in pluginNameGrailsPlugin.groovy configuration file as follows:

...
    def doWithApplicationContext = { applicationContext ->
        application.mainContext.eventTriggeringInterceptor.datastores.each { k, datastore ->
            def cacheListener = new CacheListener(datastore)
            cacheListener.nodeDriverProxyService = applicationContext.nodeDriverProxyService 
            applicationContext.addApplicationListener(cacheListener)
        }
    }
...

This registers our listener. Here we also manually inject our custom service NodeDriverProxyService to make it available for our listener to do some work

Summary

Whichever way it was implemented, the new Grails plugin can be installed to any of our Grails applications to cache any domain. Grails make it easy again!

Useful links:

  • http://stackoverflow.com/questions/1956115/hooking-into-grails-domain-object-save
  • http://hartsock.blogspot.com/2008/04/inside-hibernate-events-and-audit.html
  • http://grails.org/doc/latest/guide/GORM.html
  • http://grails.1312388.n4.nabble.com/How-to-enable-dynamic-methods-logging-by-interception-ClassCastException-on-PojoWrapper-td3165109.html

Log and Debug GORM

Sometimes its necessary to see the sql queries executed by GORM, so its possible to troubleshoot an issue. In this post, we cover different ways doing just that

1. One way is to enable SQL logging by adding following line in conf/DataSource.groovy:

...
   development {
        dataSource {
            dbCreate = "" // one of 'create', 'create-drop', 'update', 'validate', ''
            ...
            logSql = true
        }
 ...

This enables SQL logging to stdout

2. Second way, is to to enable Hibernate’s logging to log the SQL along with bind variables (so you can see the values passed into your calls, and easily replicate the SQL in your editor or otherwise). Add the following lines conf/Config.groovy:

// log4j configuration
log4j = {
...
    // Enable Hibernate SQL logging with param values
    trace 'org.hibernate.type'
    debug 'org.hibernate.SQL'
...

3. Third way, is to profile GORM with some utility such as P6Spy
…coming in future

Domain Object Many-To-One relationship Grails GORM and SQL Relationship

This is following post series from ‘Domain Object Relationships in Grails GORM and SQL Relational Databases‘ where we look at the details how the relationships are build in GORM with how physically structrured in DB while cascading and direction addressed.

From Grails Documentation, it appears that the relationship One-To-One is the same as Many-to-One in Grails GORM. In this post, we look at One-To-One relationship used as Many-to-One one and whether it is true that One-to-One equals Many-To-One relationship?

In this post, we use the same example – Book<->Author as in the post on Domain Object One-to-One relationship. In fact, this post builds on it since we are not  going to cover db structure, coding or direction/cascading of the One-to-One relationship. We are building Many-To-One relationship with One-to-One relationship implementation:

 def authorToShare = Author.build().save(flush: true)
 def book1 = Book.build(author: authorToShare).save(flush: true)
 def book2 = Book.build(author: authorToShare).save(flush: true)

As you see, a single Author object – authorToShare is shared  by adding to two different Book objects – book1 & book2 turning the Book and Author  relationship from One-to-One to Many-to-One relationship

Many-To-One with Unidirectional One-To-One Relationship

First, we consider unidirectional One-to-One Book to Author relationship as described in the post ‘Domain Object One-to-One relationship Grails GORM and SQL Relationship‘ to accomplish Many-to-One book and author relationship. If we adding shared author  to two different book objects, it indirectly builds Many-to-One Relationship. To validate, lets have test:

def "one-to-one relationship between Book and Author"() {
 given:
 def authorToShare = Author.build().save(flush: true)

when:
 def book1 = Book.build(author: authorToShare).save(flush: true)
 def book2 = Book.build(author: authorToShare).save(flush: true)
 book1.refresh()

then:
 1 == Author.count()
 book1.author.id == book2.author.id

This test pass, so GORM permits adding more books(i.e. book1 & book2) with the same author(i.e.authorToShare) making it also Many-to-One relationship. From ER diagram of One-To-One relationship, it is possible to add more books that contains the same id for author(i.e. ‘author_id’ column) . In another words, in the ‘book’ table there is entry for each book that contains author_id column holding the same ‘id’ of Author.

Many-To-One with Bidirectional Relationship

The same test as above is run, however, the relationship between Book and Author this time is bidirectional instead of unidirectional one. When running test, the following exceptions is rised:

 More than one row with the given identifier was found: 1, for class: Book; nested exception is org.hibernate.HibernateException:

To better understand what is going on we update the test as following:

def "one-to-one relationship between Book and Author"() {
 given:
 def authorToShare = Author.build().save(flush: true)

when:
 def book1 = Book.build(author: authorToShare).save(flush: true)
 def book2 = Book.build(author: authorToShare).save(flush: true)
 book1.author = null
 book1.refresh()

then:
 1 == Author.count()
 book1.author.id == book2.author.id

By adding ‘book1.author=null'(highlighted line), the test passes. This is because, we adjust GORM session, so that there is no two objects(book1 & book2 in our case) pointing to the same object(authorToShare). Apparently, while it is fine with Database table structure (as we see with Unidirection relationship above), the GORM session mechanism prohibit a shared entity (i.e. author) to be reference more than one object (i.e. books). In short, with bidirectional One-to-One relationship, we are unable to turn it into Many-to-One relationship

To summarize, it is true that GORM One-To-One relationship is equal to Many-To-One but ONLY if Unidirectional One.  For Bidirectional One-to-One relationship, while db structure permits Many-to-One relationship, the GORM session it does not. Thus, if you need true one-to-one relationship, make sure it is bidirectional instead unidirectional. Otherwise, by accident its is possible to create Many-to-one that may cause strange behavior when deleting due to extra references present.

Domain Object One-To-Many Relationship in Grails GORM and SQL Relational DB

This is following post series about ‘Domain Object Relationships in Grails GORM and SQL Relational Databases‘ where we look at the details how the relationships build in GORM with how physically structrured in DB while cascading and direction addressed.

We are going to use RetnalUnit – Review example where one RentalUnit may have multiple reviews and each review can be assigned to only one rental unit. In another words RentalUnit and Review has One-To-Many relationship .

This post is divided into four sections as following:

In each section, there is ER diagram, Class diagram and code snippet to better cover the details

Database Structure for Unidirectional One-To-Many Relationship of GORM

To better see the structure of the relationship in Database, the ER Diagram and a view of tables is presented here:

From the diagram, the unidirectional one-to-many relationship is constructed by having addition table (i.e. Retnal_Unit_Review) containing two columns each Id fields from tables of the entities forming the relationship(i.e. RentalUnit and Review). The new added table is child to parent tables – Review and RentalUnit.

Section 1 – Unidirectional Non-Cascading One-To-Many

(a)Class Diagram

(b)Code Snippet

class Review {
    String submittedBy
    String content
    String dateReceived
    boolean isApproved

    static constraints = {
        submittedBy blank: false, size: 3..50
        content blank: false, size: 5..2500
    ....
    }
...
}
class RentalUnit {
    String name
    String nickname
    Address address
    static hasMany = [reviews:Review]

    static constraints = {
        name blank: false, unique: true, size: 4..10
        nickname blank: false, size: 5..60
    }
}

No association defined  in the Review class which makes it unidirectional. There is association ‘reviews’ defined in the RentalUnit class(line 5 highlighted).

Section 2 – Unidirectional Cascading One-to-Many Relationship

(a)Class Diagram

(b)Code Snippet

class Review {
    String submittedBy
    String content
    String dateReceived
    boolean isApproved

    static belongsTo = RentalUnit

    static constraints = {
        submittedBy blank: false, size: 3..50
        content blank: false, size: 5..2500
    ....
    }
...
}

By adding ‘belongTo’ (highlighted line) in the Review class, makes this unidirectional relationship cascade (you inform GORM that the Review belongs to Rental Unit, so it knows when Rental Unit is deleted to cascade onto particular Review). No change needed in Rental Unit

But who cares for orphans!?

The cascading will work for all those reviews pointed by rentalUnit you are deleting:

given:
   def rev1 = Review.build().save(flush:true)
   def rev2 = Review.build().save(flush:true)
   def rentalUnit = RentalUnit.build().addToReviews(rev1).addToReviews(rev2).save(flush:true)
when:
   assert 2 == Reviews.count()
   rentalUnit.delete()
then:
   0 == Reviews.count()

In line 7, the rental unit is deleted and it deletes  all reviews associated with this rental unit from database(validated on line 9), however. It will not cascade on delete if the review is orphan:

given:
   def rev1 = Review.build().save(flush:true)
   def rev2 = Review.build().save(flush:true)
   def rentalUnit = RentalUnit.build().addToReviews(rev1).addToReviews(rev2).save(flush:true)
when:
   assert 2 == Reviews.count()
   rentalUnit.removeFromReviews(rev1)
then:
   2 == Reviews.count()

In line 7, the review – rev1 becomes an orphan since removing it from rental unit, the review is no more referencing any rental unit (the entry in review table  pointing to none or rental_unit_in is null). The line 9 validates that cascading on delete has not applied to orphans( the removed review ‘rev1’ is still in the database). If you wish to apply cascading also to orphans then you have to add following lines:

class RentalUnit {
    String name
    String nickname
    Address address
    static hasMany = [reviews:Review]

    static mapping = {
        reviews cascade: "all-delete-orphan"
    }
...
}

By specifying ‘all-delete-orphan’, the cascade on delete is applied to orphans as well. In our situation, the review – rev1, thus, will be deleted from database when removed from Rental Unit (i.e. rentalUnit.removeFromReviews(rev1))

Don’t be Tricked

For a while, i was puzzled because i saw in my follow developers code that  there was missing line ‘static belongtsTo = SOMEClass’ (line 7 in our Review class) on the right side of the association(i.e. Review) that  enables cascading but it was still cascading fine on deletes. It turns out that the cascading can be enabled from the left side of the association(i.e. Renta Unit)  with the following:

static mapping = {
        reviews cascade: "all-delete-orphan"
}

So, please, don’t be puzzled if cascading works if you don’t see it enabled from the right side of the association. Check the left side of the association. The same applies vice versa. In short, its enough enable cascading on only one side to make it work

Database Structure for Bidirectional One-To-Many Relationship of GORM

To better see the structure of the relationship in Database, the ER Diagram and  view of tables is presented here:

From the diagram, the bidirectional one-to-many relationship is constructed by having addition field ‘rental_unit_it’ in the Review table that is also a foreign key. This makes a child-parent relationship between rental unit and review in  bidirectional one-to-many relationship.

Section 3 – Bidirectional Cascading One-to-Many Relationship

(a)Class Diagram

(b)Code Snippet

class Review {
    String submittedBy
    String content
    String dateReceived
    boolean isApproved

    static belongsTo = [rentalUnit: RentalUnit]

    static constraints = {
        submittedBy blank: false, size: 3..50
        content blank: false, size: 5..2500
    ....
    }
...
}
class RentalUnit {
    String name
    String nickname
    Address address
    static hasMany = [reviews:Review]

    static mapping = {
        reviews cascade: "all-delete-orphan"
    }

    static constraints = {
        name blank: false, unique: true, size: 4..10
        nickname blank: false, size: 5..60
    }
}

The highlighted line in Review sets the association to Rental Unit. It also enables cascading on delete (save/update is by default). The highlighted line 5 in the Rental Unit sets the associations to many Reviews.

Section 4 – Bidirectional Non-Cascading One-to-Many

(a)Class Diagram

(b)Code Snippet

class Review {
    String submittedBy
    String content
    String dateReceived
    boolean isApproved

    static belongsTo = [rentalUnit: RentalUnit]

    static constraints = {
        submittedBy blank: false, size: 3..50
        content blank: false, size: 5..2500
    ....
    }
...
}
class RentalUnit {
    String name
    String nickname
    Address address
    static hasMany = [reviews:Review]

    static mapping = {
       reviews cascade: "none"
    }

    static constraints = {
        name blank: false, unique: true, size: 4..10
        nickname blank: false, size: 5..60
    }
}

The highlighted lines remove cascading for association between rental unit and review. In another words, if you, for example, removing some review in rentalUnit1.reviews collection, it does not physically remove the review from the Review table in database

Step Further

If you remove the left side association (the reference into Review from the Rental Unit class)  from the Bidirectional Rental Unit to Review relationship described above, guess what it will become? Let’s look at Class Diagram:

From the class diagram, we see it is now unidirectional relationship, however different than one above(make sure you compare diagrams). This Unidirectional One-To-Many relationship  is pointing oposite direction. The database structure is the same as the Bidirectional one-to-many relationship described above. In another words, you have two versions of Unidirectional one-to-many relationship separated from each other by database structure as well as the direction of the single association.

The cascading is inherited unless you explicitly disable with ‘static mapping’ statement as described  above(highlighted lines). With that, i would stay away from this relationship because it quickly becomes impossible to delete  either some instance of ‘review’ or ‘rentalUnit’ with associations present and no ‘removeTo’ grails method available to help.

Other

java.util.NoSuchElementException – Challenging One-To-Many

How would GORM handle situation where we pass the same Review to two different Rental Units making it not true One-To-Many relationship?

In another words, the relationship One-to-Many from RentalUnit to Review == Many-to-One from Review to RentalUnit. Now, if we are able to add the same shared Review to two different RentalUnits, then the Review, instead of one, is pointing to more than one RentalUnit.  Thus, this is no more Many-to-one from Review to RentalUnit. We run following Spock test to analyze ( with bidirectional cascading one-to-many implementation as described above):

when:"making true one-to-many"
        def review3 = Review.build().save(flush: true)
        def rentalUnit1 = RentalUnit.build().addToReviews(review3).save(flush: true)
        def rentalUnit2 = RentalUnit.build().addToReviews(review3).save(flush: true)
        review3.submittedBy = "ziggy"
        review3.save(flush: true)

then:
        rentalUnit1.reviews.first().id == rentalUnit2.reviews.first().id
        review3.rentalUnit.id == rentalUnit2.id
        rentalUnit1.reviews.first().submittedBy == "ziggy"
        rentalUnit2.reviews.first().submittedBy == "ziggy"

For our surprise, this test pass making it look like the review ‘review3’ is pointing to two rental units ‘rentalUnit1’ and ‘rentalUnit2’, however. From database table structure of one-to-many described above, this is not possible.

In another words, in order this test pass, the GORM would have to clone review3 in the Review table referencing it to the another rental_unit-id, but then still line 9 (highlighted) would fail since all IDs are unique in the Review table. So, how is it possible this test pass? Thank you Victor Sergienko at stackflow.com for being patient helping me understand. The test will fail if adding one line as follows:

when:"making true one-to-many"
        def review3 = Review.build().save(flush: true)
        def rentalUnit1 = RentalUnit.build().addToReviews(review3).save(flush: true)
        def rentalUnit2 = RentalUnit.build().addToReviews(review3).save(flush: true)
        review3.submittedBy = "ziggy"
        review3.save(flush: true)
        rentalUnit1.refresh()

then:
        rentalUnit1.reviews.first().id == rentalUnit2.reviews.first().id
        review3.rentalUnit.id == rentalUnit2.id
        rentalUnit1.reviews.first().submittedBy == "ziggy"
        rentalUnit2.reviews.first().submittedBy == "ziggy"

By adding line to refresh() of rentalUnit1, the test fails with exception – ‘java.util.NoSuchElementException’.

In line 4, when adding ‘review3’ to the ‘rentalUnit2’, two things happen. First, the review3 entry in the database is updated with different rental_unit_id to point to rentalUnit2. Second, at the same time, the session for rentalUnit2 is updated to point to review3, however!!!! The session is not updated in respect to rentalUnit1. In the session, the rentalUnit1 still pointing to review3 even thou in database it is not. This was why our test was passing. By refreshing the rentalUnit1, the session was reloaded with what is actually in database making it in sync. Then test was failing as expected.

Important to note, that refreshing either the ‘review3’ or the ‘rentalUnit2’ would not make the test fail. It has to be the rentalUnit1 since it is the one out of sync.

In summary, updating associations for domain objects in GORM, may cause session be out of sync with database. This is because the association overwritten by new association, does not get update in session making out of sync till reloaded from database.

Things Different For Unidirectional

For the Unidirectional One-To-Many relationship the above ‘refresh()’ doesn’t remove the reference to the shared Reveiw. As result the test fails in Unidirectional but not if Bidirectional relationship:

def "one-to-many relationship between RentalUnit and Review"() {
        when:"adding shared review will result referenced by last rental unit"
        def review3 = Review.build().save(flush: true)
        def rentalUnit1 = RentalUnit.build().addToReviews(review3).save(flush: true)
        def rentalUnit2 = RentalUnit.build().addToReviews(review3).save(flush: true)
        rentalUnit1.refresh()

        then:
        !rentalUnit1.reviews.contains(review3)
    }

This test fails, perhaps, because the data structure is different. As you saw from the post, there is extra table in Unidirectional relationship. And, I am guessing, that the ‘addToReviews’ method does not update that extra table(line 5), so the reloading session (highlighted line) still contains reference to the shared review. This is different in Bidirectional relationship where ‘addToReviews’ handles the reference in dB and then by reloading session the reference to shared Review was cleared/sync with database accordingly.

Domain Object Many-To-Many Relationship in Grails GORM and SQL Relational DB

This is following post series about ‘Domain Object Relationships in Grails GORM and SQL Relational Databases‘ where we look at the details how the relationships build in GORM with how physically structrured in DB while cascading and direction addressed.

In this post, i have a sample of Many-To-Many relationship between entities ‘RentalUnit’ and ‘Review’. A particular RentalUnit may have many Reviews. One guest my have stayed in multiple RentalUnits, so a review written by this guest is linked to many RentalUnits

In our example, there is ‘Save, Update and Delete’ cascading from RentalUnit to Review and ‘none’ cascading from Review to RentalUnit

Class Diagram

From class diagram you can see there is ‘has to’ relationship between RentalUnit and Review entities

Code Snippet

In the above code snippet, the red colored code sets the relationship Many-To-Many. The green colored code sets the cascading

Database Structure

From the ER Diagram above, we see that GORM creates a separated table – RentalUnit_Review that is a child to parent ‘RentalUnit’ and another parent ‘Review’ tables. The IDs of entities ‘Review’ and ‘RentalUnit’ combines the primary key for the new table

Summarized

Our conclusion that GORM creates additional table every single time there are Many-To-Many or One-To-Many relationship.

Other

1. addTo* And removeTo* methods.

Whenever there are Many-To-Many or One-To-Many relationship, GORM provides you with methods – addTo* and removeTo*. These methods does important part – it sets the relationships in both ways. Consider the following:

def review2 = new Review(rentalUnits: [rentalUnit], ...., isApproved: false).save(flush: true) 
review2.addToRentalUnits(rentalUnit2)

In the above code we initialize object ‘review2’ that points to object ‘rentalUnit’ and ‘rentalUnit2’, however.  While object ‘rentalUnit2’ is pointing back to object ‘review2’, the object ‘rentalUnit’ does not. This is because adding object ‘rentalUnit2’ we used method ‘addTo*’ where as for object ‘rentalUnit’ we did not. As result we have incomplete session.

2. “deleted object would be re-saved by cascade”

Entities like ‘Review’ that have references from multiple ‘RentalUnit’ objects while no cascading applied, will cause errors if not handled with care. For example, if you have to delete a review object R1 that is contained by two rentalUnit objects A and B, then you have to first delete the references from A->R1 and B->R1 before deleting the review – R1 itself. Here is example

def r = []
 r += review1.rentalUnits
 r.each {RentalUnit renUnit ->
     renUnit.removeFromReviews(review1)
 }
 review1.delete(flush: true)

In another words, we are doing cascading manually and should not be needed if we have specified cascading from Review to RentalUnit.

If we don’t handle these RentalUnit references as shown above before deleting the review R1, we will receive an error – deleted object would be re-saved by cascade – and the review R1 is not removed. This because GORM deletes the review R1 in session and before flushing, it detects that there exists other references to this review R1, thus , it cannot be remove, instead restores (re-saved) the review R1 back in session.

3. Cascading gets tricky in Many-To-Many relationship

Consider a RentalUnits U1 and U2 with reviews R1 and R2 with associations as following:

U1 -> R1,R2
U2 ->R2

Now, if you wish to remove rentalUnit U1, it will fail even with cascading on ‘delete’ present from RentalUnit->Review as described above. It fails because GORM is executing cascading on all children of rentalUnit that includes all reviews, so it attempts to delete the review R2, however. The review R2 is also referenced by another rentalUnit U2 resulting GORM unable to remove R2, and also U1 as the result.

Grails Database Migration Plugin

Grails Database Migration Plugin is very useful for managing SQL database for your grails application. The most useful aspect of it is that it can generate SQL  scripts from current GORM. In my previous job, we used MyBatis to manage Database, however. With MyBatis we had to create SQL scripts manually and to make it according to GORM was very time consuming, error prone and not efficient process.

In this space, i will cover the installation, setup and everyday use of Grails Database Migration Plugin.

Pre-Condition: SQL Db setup and installed

Install & Setup DataBase Migration Plugin

1. Installation.

To install Grails Database Migration Plugin, run the following command:

grails install-plugin database-migration
-Or-

Add the line ‘runtime “:database-migration:1.1″‘ in the ‘plugin’ code section in grails-app/conf/BuildConfig.groovy just like this:

To verify, open Dependency report (‘grails open dep-report’) and see if there DB migration plugin listed just like this:

2. Generate Initial Script

Next, we are going to generate intial migration script:

grails dbm-generate-changelog changelog.groovy

This will generate ‘changelog.groovy’ containing SQL scripts to create current GORM state and save it in ‘grails-app/migrations’ folder

Note:You may choose to run ‘grails dbm-changelog-sync’ that will record that the changes have already been applied if you don’t wish to dump tables and test this script.

3. Update Datasource

Last, we need to change the app DataSource to inform GORM that it does not need to ‘create’, ‘update’, or do anything with the Database at the application startup since we are going to use DB migration plugin for ‘create’, ‘update’ the app database. So, update variable ‘dbCreate’ in ‘grails-app/conf/DataSource.groovy’ to nothing just like the following:

Using Database Migration Plugin

Test Run

At any given time, to test Database migration plugin and scripts do the following:

1. Drop all tables in the Database.

You can do that manually or utilize Migration plugin by running the following command

grails dbm-rollback-to-date  1900-01-01

2. Roll back the record. ONLY If you drop tables manually you need to update migration record log, so that it appears no migrations scripts have run so far. To do that run the following command:

grails dbm-drop-all

If you have run ‘dbm-rollback-to-date’ as specified above, it would have done this step for you already

3. Run the Scripts and Check Database

To run all of the migration scripts call the following command:

grails dbm-update

This will run any script that is registered in the migration log as hasn’t been run. In our case, we drop all the records, so it will run all migration scripts

Note: At any time, you can check the migration plugin status of what migrations scripts have and have not been run by calling ‘grails dbm-status’. It will display only scripts that haven’t been run

Generating Migrations Scripts

After making changes in GORM all tested then run the following command:

grails dbm-gorm-diff --add filename.groovy

This will generation and register the script as well as include for the filename in the main changelog for you. You are done. Other developer will get your new script and run ‘dbm-update’ to update their database accordingly

Other Useful Migration Commands

1. dbm-clear-checksum.  You can change any Migrations scripts manually but if you do, then don’t forget to run ‘grails dbm-clear-checksum’ afterwards to make the changes valid

2.  dbm-gorm-diff without ‘–add’. If you run only ‘grails dbm-gorm-diff’ without the ‘–add’ tag, it will display what are the current difference between GORM and your database

Run Migration Scripts at Start

Its all good running ‘dbm-update’ every single time you receive new migration script in your workspace , however. You are able to make it automatic at next time your grails application start by adding  the following in the conf/Config.groovy file:

grails.plugin.databasemigration.updateOnStart = true
grails.plugin.databasemigration.updateOnStartFileNames = ["changelog.groovy"]
grails.plugin.databasemigration.changelogLocation = 'grails-app/migrations'

This will detect what changes hasn’t been run from the change log and run appropriate migration scripts all automatically at grails app startup.

Running the migration script at startup including times you making changes in GORM (domain classes) will cause conflicts between GORM and database. Learn, how to solve the problem in post ‘Grails Environment Aware Framework

Issues Encountered

1. ‘Change Set changelog.groovy::[…] (generated) failed.  Error: Error executing SQL ALTER TABLE[…]

We got this error after cloning the project and running the migrations scripts.

It turned out that this error was caused because only on Windows when you generate initial migration script it includes references to specific database name.

To solve it, we manually deleted these references in the initial migration script. Afterwards don’t forget to run ‘dbm-clear-checksum’ to make changes valid

2. ‘Error: Error executing SQL CREATE INDEX `[…]: Incorrect index name’

This error seems to be specific to Database installation,setup or version. Either way, the solution was to manually updated migration script so that this index is created after the foreign key is added

As you can see it is the same index name as marked in above screenshot. So moving the changeSet 5 in front of the changeSet 4 and then updating the numbering(red color) solved the problem for us

Afterwards, don’t forget to run ‘dbm-clear-checksum’ to make the changes valid

3. Grails Migration plugin fails silently.

Most of the time this happens when there is syntax error in one of the migration scripts. I find myself often times deleting a migrations script and then forgetting to update the initial changelog. groovy. At the bottom, the changelog.groovy contains ‘include’ for each migrations script. If you delete some migration script but fail to update (remove the includes) the changelog.groovy, then migration plugin fails silently for ‘update’, ‘status’ and other cmd.

Debugging Grails Database Migration plugin

1. Enable logging. It may help troubleshoot your issue if you enable the Database Migration Plugin by adding following line in Config.groovy:

log4j = {
           ....
           debug 'grails.plugin.databasemigration', 'liquibase'
    }

Useful Sources:

COUNTDOWN TO GRAILS 2.0: DATABASE MIGRATIONS

Working with the Grails Database Migration Plugin

Database Migration Plugin

Domain Object One-To-One Relationship In Grails GORM and SQL Relational Database

This is following post series about ‘Domain Object Relationships in Grails GORM and SQL Relational Databases‘ where we look at the details how the relationships are build in GORM with how physically structrured in DB while cascading and direction addressed. This post is divided into 4 section as following:

Each section contains the following:

  • Class Diagram
  • Code Snippet
  • ER Diagram with Tables snippet
Summary: There is the same parent-child database structure with foreign key constrain for all 4 different versions of One-to-One relationship. We learn that cascading and direction is enforced solely by GORM without no changes in tables structure.

1. One-To-One Unidirectional Relationship with No-Cascading

(a) Class Diagram

In our example, we have object Book and Author and the one-to-one relationship is where Book has one Author as displayed in the following class diagram:

(b) Code Snippet

So, to have One-To-One unidirectional relationship without cascading for GORM the code looks as following:

By declaring Author in the Book class it becomes unidirectional no-cascading one-to-one relationship.

(c) ER Diagram with Tables snippet

Here is how it physically structured in SQL Rational DB:

As we can see ‘Author’ becomes a child for entity -‘ Book’ by having foreign key as ‘author_id’ part of Book table. Interesting to note that foreign key name is created by concatenation of  name of the Entity +  ‘__’ +  primary key(i.e. author_id)

2. One-To-One Unidirectional With Cascading

(a) Class Diagram

(b) Code Snippet

In order to have cascading enforced by GORM, it needs to be told GORM that ‘Author’ is a child of ‘Book’. Here is how its done:

So,  adding “static belongsTo = Book” lets GORM know that ‘Author’ is a child and needs to be cascaded whenever parent – ‘Book’ deleted, saved.

(c) ER Diagram with Tables snippet

There are no changes in the physical table structure. GORM handles cascading. In short, to have unidirectional and cascading relationship you have two things – declaring child relationship as is  ‘Author’ in the Book entity(green) and letting GORM know its a child in the ‘Author’ entity(red)

3. One-To-One Bidirectional Cascading

(a) Class Diagram

Here is our updated class diagram showing bidirectional relationship:

(b) Code Snippet

Here is the code to have One-to-One bidirectional cascading relationship

The green  code from the code snippet above is what was for making relationship unidirectional. The red code is what changed and turned it into bidirectional while keeping cascading. In short, by making variable ‘belongsTo’  turns the relationship bidirectional

(c) ER Diagram and Tables snippet

There are no change in DB structure. This is telling that just like cascading as well as direcretion –  bidirection and unidirection is solely enorced by GORM and there is no specific changes in SQL DB.

4. One-to-One Bidirectional No Cascading

To enable no cascading, the following code need to be added in Book class:

...
static mapping = {
        author cascade: "none"
    }
...

This will disable any cascading. Some times you may need disable cascade on Deletes only or some other way. These are some alternative cascading options –

  • none
  • save
  • update
  • save-update
  • delete
  • all
  • all-delete-orphan

Interesting to note,that on the right side(Author) of the Book-Author relationship there is cascading enabled while on the left(Book) there is none. It is apparent, that the cascading setting on left(Book) specified with ‘static mapping’ supersedes.

Domain Object Relationships in Grails GORM And SQL Relational Databases

When doing re-factoring  for applications with SQL Databases, it may be helpful to know the details how GORM creates different relationships and how those relationships are physically structured in the DB. This post starts the post series to cover – One-To-One, One-To-Many, Many-To-Many and Inheritance for Domain objects relationships. we hope to answer following questions

  • How these relationships are physically structured in DB?
  • How these relationships  are implemented in respect to cascading and direction(bidirectional vs unidirectional) and whether it changes DB structure?

Here is summary:

Summarized – Quick Reference

Relationship Unidirectional Bidirectional
One-To-One(book*-to-author)
Many-To-One(book*-to-author)   Not Possible (see post)
One-To-Many(RentalUnit*-to-review)
Many-To-Many(RentalUnit*-to-review)
Inheritance img