How to ensure data integrity when using Table Per Subclass?

Question

I am using the table per subclass strategy in Grails by setting the tablePerHierarchy property of the static mapping field in my superclass to false. This way, Grails creates one table for my superclass and one additional table for each of my subclasses.

However, while the superclass and subclass records share the same ID (primary key), there are no foreign key constraints to keep them consistent, i.e. it is possible to delete the superclass record, leaving the subclass record in an invalid state. I want to know if there is a setting/property to make GORM address this in some way, e.g. through constraints. Or is my only option to add foreign keys manually?

---

For example, given the following domain class as superclass:

class Product {
    String productCode

    static mapping = {
        tablePerHierarchy false
    }
}

And the following domain class as subclass:

class Book extends Product {
    String isbn
}

This results in the creation of two tables, the Product table and the Book table. When creating a Book – through scaffolded pages, for instance – a record is inserted into each table, their only link being the fact that the ID value is the same for each. Specifically, the data might look like this:

PRODUCT
Id      Version     ProductCode
1       1           BLAH-02X1

BOOK
Id      ISBN
1       123-4-56-7891011-1

Because there is no formal relationship defined at the database level for these tables, it is possible to delete one of the records and leave the other, which results in invalid data. Obviously I can use SQL to manually create a foreign key constraint on the two ID fields, but I was hoping to let Grails handle that. Is this possible?

---

Using Grails 2.2.1

Melba

Answer 1

Solved!

The following solution fixed this issue for me. Add the class below to src/java (this class cannot be written in Groovy)

package org.example;

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration;
import org.hibernate.MappingException;
import org.hibernate.mapping.JoinedSubclass;
import org.hibernate.mapping.PersistentClass;
import org.hibernate.mapping.RootClass;

import java.util.Iterator;

public class TablePerSubclassConfiguration extends GrailsAnnotationConfiguration {

    private static final long serialVersionUID = 1;

    private boolean alreadyProcessed = false;

    @Override
    protected void secondPassCompile() throws MappingException {
        super.secondPassCompile();

        if (alreadyProcessed) {
            return;
        }

        for (PersistentClass persistentClass : classes.values()) {
            if (persistentClass instanceof RootClass) {
                RootClass rootClass = (RootClass) persistentClass;

                if (rootClass.hasSubclasses()) {
                    Iterator subclasses = rootClass.getSubclassIterator();

                    while (subclasses.hasNext()) {

                        Object subclass = subclasses.next();

                        // This test ensures that foreign keys will only be created for subclasses that are
                        // mapped using "table per subclass"
                        if (subclass instanceof JoinedSubclass) {
                            JoinedSubclass joinedSubclass = (JoinedSubclass) subclass;
                            joinedSubclass.createForeignKey();
                        }
                    }
                }
            }
        }

        alreadyProcessed = true;
    }
}

Then in DataSource.groovy set this as the configuration class

dataSource {
    configClass = 'org.example.TablePerSubclassConfiguration'
    pooled = true
    driverClassName = "org.h2.Driver"
    username = "sa"
    password = ""
    dbCreate = "update"
    url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
}

Update

I've submitted a pull request to Grails for this issue. The fix was included in Grails 2.3.8 or 2.3.9 (can't remember which).

Engage

Using 2.4.3 i still can reproduce this problem. Should i include configClass = 'org.example.TablePerSubclassConfiguration' in my DataSource or this has become the default behavior? – Stultify

Answer 2

Hibernate ensures the data integrity in case of table per subclass. In case of table per subclass, subclass maintains a primary key association with superclass. Have a look at Hibernate Table per subclass. To validate the fact here is your test case:

---

class Product {
    String productCode

    static mapping = {
        tablePerHierarchy false
    }
}

class Book extends Product{
    String isbn
}

//Test Case
def testTablePerSubclass{
    def product = new Product(productCode: 'XYZ456')
    product.save(flush: true, failOnError: true)

    def book = new Book(isbn: '123456123', productCode: 'ABC123')
    book.save(flush: true, failOnError: true)

    assert Book.list().size() == 1 //One Book
    assert Book.list()*.id == [2] //Book id
    assert Product.list().size() == 2 //One Product, one Book (2 Products)
    assert Product.list()*.id == [1, 2] //Product id, Book Id

    //Grab the product (book) to delete
    def productToDelete = Product.get(book.id)
    productToDelete.delete(flush: true)

    assert Book.list().isEmpty() //Book deleted from Book table as well
    assert Product.list().size() == 1 //One Product remaining in Product table
    assert Product.list()*.id == [1] //Remaining Product Id
}

Keep logSql true in DataSource.groovy to see corresponding sqls getting executed.

---

Log Sql Output:-

Hibernate: insert into product (id, version, product_code) values (null, ?, ?)
Hibernate: insert into product (id, version, product_code) values (null, ?, ?)
Hibernate: insert into book (isbn, id) values (?, ?)
Hibernate: select this_.id as id0_0_, this_1_.version as version0_0_, this_1_.product_code as product3_0_0_, this_.isbn as isbn1_0_ from book this_ inner join product this_1_ on this_.id=this_1_.id
[com.example.Book : 2]
Hibernate: select this_.id as id0_0_, this_.version as version0_0_, this_.product_code as product3_0_0_, this_1_.isbn as isbn1_0_, case when this_1_.id is not null then 1 when this_.id is not null then 0 end as clazz_0_ from product this_ left outer join book this_1_ on this_.id=this_1_.id
[com.example.Product : 1, com.example.Book : 2]
Hibernate: delete from book where id=?
Hibernate: delete from product where id=? and version=?
Hibernate: select this_.id as id0_0_, this_1_.version as version0_0_, this_1_.product_code as product3_0_0_, this_.isbn as isbn1_0_ from book this_ inner join product this_1_ on this_.id=this_1_.id
[]
Hibernate: select this_.id as id0_0_, this_.version as version0_0_, this_.product_code as product3_0_0_, this_1_.isbn as isbn1_0_, case when this_1_.id is not null then 1 when this_.id is not null then 0 end as clazz_0_ from product this_ left outer join book this_1_ on this_.id=this_1_.id
[com.example.Product : 1]

---

Using Grails 2.2.2

Demarche

I have run your test, and though you seem to have switched the product and book IDs (1 and 2), this has proven to me that Hibernate maintains an association between these two domain classes. However, there is no such association on the database level. Is there then no way to make Hibernate define a constraint for this in the DB? – Melba

Switched Product and Book Ids? Did not get it? A added 2 products and 1 book. I deleted one product (a book) and it ultimately deleted the book as well. See my updated answer for logSql output.Try logging the sql to see the changes. And sorry I did not get " to make Hibernate define a constraint for this in the DB"? – Demarche

Like I said in my question, the database still allows a book to exist without a corresponding product record. Your answer demonstrates that Hibernate has some logic that associates these tables in the Java layer. I was hoping for something in the database layer itself, but this does not seem to be supported. – Melba