<xml.lt>

One of the outstanding issues in the updated (so far internally) version of the DIY Framework, which now keeps resources in a hierarchy, is how to effectively calculate the full URIs of resources. For this post, it would be Blog/2008/09/16/Computing+path+of+a+node+in+a+hierarchy, where the post resource is a leaf node with a depth of 5. In general terms, the question is how to calculate path of a node.

Propel's nested sets implementation for some reason calculates the path by recursively traversing from the node to its parent and so on until the root is reached, although this can be done using one SQL query. But even one query per resource is not good enough, since usually one page would use tens or even hundreds of resource objects.

In order to improve performance and not to compute the URI on every load, we left the full URI property for resources as it was, in addition to the new relative (to parent resource) URI property, and properties needed to implement the nested sets. So the question is, how to keep the full URI up-to-date all the time with resource's position in the tree? It certainly needs to change when one of the ascendant (parent, parent of the parent etc.) resources, or the resource in question itself, is moved or updated. But that might also involve a chain reaction since full URIs of descendant resources would also need to change, and they might be in numbers of hundreds or thousands. Definitely a performance problem, especially if done on the PHP level. I was thinking about implementing this with SQL triggers somehow, but it doesn't seem to be so trivial. Anyone has ideas?