Monday, September 12, 2011

ActiveJDBC cascades deep and shallow

Each ORM adds functionality not found in standard DB access layer, and ActiveJDBC is no exception. Lets say delete with cascade. Method:
model.deleteCascade();
has been in existence for a while, but had somewhat limited functionality. It deleted the model and its immediate children in case One to Many and Polymorphic associations. In case of Many to Many, it was merely clearing links in a join table. The main reason for this was performance. In order to implement a true cascade delete, an ORM must follow all relationships until none left, but unfortunately in the process, it has to load every record instance into memory. This process might allocate huge chunks of memory and generate unexpected number of DELETE statements to the database.

So, initially performance considerations stopped me from implementing a true cascade delete. After all, deleting immediate children is very efficient: clean all of them in one SQL, then delete the parent.

However, once people started using ActiveJDBC, many asked a question: "this deleteCascade() is not really cascading, what the heck?" (well, they are all nice people, but I need to add some drama here keep you reading:)).
In any case, they pointed out inconsistencies of a name and the actual semantics. This prompted me to implement delete cascade true to its name. So, a new version just published to Sonatype will cascade like there is no tomorrow. It will navigate all child and many to many relationships of a model being deleted, find their children, grand children, grand-grand kinds, etc. No one walks out alive, if you know what I mean:)

Implications might be strange at first, but logical if you think about it. Imagine you have a relationship where doctors treat patients and patients visit doctors. In other words, this is a many to many relationship. If you delete a doctor, then all patients associated to that doctor are also deleted. But, what if a patient also visits another doctor? Guess what, that doctor is also deleted (because it is a dependency of a patient being deleted) and so are his/her patients, and so on. So, "deleteCascade()" really knows how to cascade!

But, what about the fast and efficient delete if all I want is to delete a model and immediate children (assuming no grand kids)? For that, there is a new method:

model.deleteCascadeShallow();
which retained the same functionality deleteCascade() had before.

So, deleting models in ActiveJDBC is an easy business, with methods:
delete();
deleteCascade();
deleteCascadeShallow();

For more detailed info, see this Wiki page: http://code.google.com/p/activejdbc/wiki/DeleteCascade

cheers..