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 around.

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 -&gt;
     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 also at the time you making changes in GORM (domain classes) q

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 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

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:

Inheritance Relationship

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