Glorp and 1:n relationships: a word of advice


Regular readers may remember a post of mine about an issue with Glorp’s commitUnitOfWorkAndContinue method. When you delete objects that refer other objects in an exclusive 1:n relationship, Glorp currently re-inserts those deleted objects in a later transaction. In the process of building a small test application that makes the error transparent, I stumbled upon some strange problems that seemed to be caused by problem with auto-increment (or sequence) columns.

But let’s start at the beginning. The idea was that I’d build a tiny application with one model class called TreeNode. Such a Node can have a parent and zero or more children, besides a title. Should be easy, right?

Wrong.

First things first:

Here’s what the descriptor for the TreeNode looks like:

descriptorForTreeNode: aDescriptor
| table |
table := self tableNamed: 'NODE'.
   aDescriptor table: table.
   (aDescriptor newMapping: DirectMapping) from: #id to: (table fieldNamed: 'id').
   (aDescriptor newMapping: DirectMapping) from: #title to: (table fieldNamed: 'title').
  "Hierarchy"
   (aDescriptor newMapping: OneToManyMapping)
   attributeName: #children;
   beExclusive;
   mappingCriteria: (Join from: (table fieldNamed: 'parent_id') to: (table fieldNamed: 'id')).
  "Backpointer for the hierarchy"
   (aDescriptor newMapping: OneToOneMapping)
   attributeName: #parent;
   referenceClass: TreeNode;
   mappingCriteria: (
    Join
      from: (table fieldNamed: 'parent_id')
      to: ((self tableNamed: 'NODE') fieldNamed: 'id')).


So nothing special here: A Tree Node has a OneToMany relationship for its children and a OneToOne relationship to its parent. I have lots of classes like this in my new Application (which I’ll sure tell you more about later this year) and never had any problems with them – apart from the above-mentioned deletion and re-insertion phenomenon.

So I started building a tree of TreeNodes, because I wanted to show that deleting a child with subchildren would lead to re-insertions in the next transaction. The code to do that is pretty straightforward:

root := JtTreeNode new title: 'Car'.
dbSession register: root.

root addChild: (chassis := JtTreeNode new title: 'chassis').
root addChild: (body := JtTreeNode new title: 'body').

body addChild: (JtTreeNode new title: 'windshield').
body addChild: (JtTreeNode new title: 'steering wheel').
body addChild: (JtTreeNode new title: 'dashboard').

chassis addChild: (JtTreeNode new title: 'front axle').
chassis addChild: (JtTreeNode new title: 'rear axle').
dbSession commitUnitOfWorkAndContinue

So on to testing this first and then writing my test code for the deletion of the object called chassis.

You can probably guess how surprised I was to find that this simple code didn’t work at all. I got duplicate keys on inserts. Duplicate keys when the Database assigns the id’s??? So I fired off my debugger and saw strange things. The rowMap of the current Unit Of Work was so completely screwed and the objects indeed had duplicate IDs!

I played a bit (maybe ten hours or so) with this and tried several things and even re-wrote my test case to another model just to run into another strange problem: Glorp would try to insert multiple root (Car) objects, because the registration of transitive closures of children of Car would point to Car’s without IDs. So there were three Car’s in the rowMap. One had an ID of 1, the other two had an ID of nil, and their title row would contain “anObject” —- strange, isn’t it?

So after a while of playing and trying I was out of ideas. Until I took a coffee break (or was it a bio break?). Maybe something’s is wrong with the mappings? But What could it possibly be?

So the first thing I tried was changing the order of the Join arguments for the OneToManyMapping.

 "Hierarchy"
   (aDescriptor newMapping: OneToManyMapping)
   attributeName: #children;
   beExclusive;
   mappingCriteria: (Join from: (table fieldNamed: 'parent_id') to: (table fieldNamed: 'id')).


became

"Hierarchy"
   (aDescriptor newMapping: OneToManyMapping) 
   attributeName: #children; 
   beExclusive; 
   mappingCriteria: (Join from:  (table fieldNamed: 'id') to: (table fieldNamed: 'parent_id')).

And believe it or not, the duplicate key and multiple root inserts were gone! The initial deletion problem is still there and I could finally prepare a test application that can be looked at in detail now and reliable produces the problem, but these problems I had in the meantime were gone.

So what do we learn from that?
In a 1:n relationship, the order of the Join criteria for the 1-side (root) is important: first define the attributes in the table of the object on the 1-side, and hand the foreign key for the n-side (children) to the to: paramater of the Join.
It should always read:

Join from: myColumnOnThisSide to: theForeignKeyOnTheNSide

I never knew that and can’t remember reading that anywhere, so I thought this is probably worth mentioning here.