Integrity Check for a Nested Set

[marone42]

Hello guys,

 

I have here a Database with a Nested Set (Nested Tree) Structure, with id, lft, rgt, root_id and parent_id. I think that the structure is not ok.I found some duplicate nodes and other inconsistencies.

 

So my question: does anyone of you guys have a hint (or maybe some test algos) for doing an integrity check of the tree? Maybe using the parent_ids (which are assigned correct) or so.

 

Thanks!

[bubblegum.anarchy]

for every record the parent_id must match an id in another record.

for every record the parent_id must not match the id.

for every record the root_id must match an id in another record.

 

Are those the type of hints you are looking for?

[marone42]

Hello,

 

i'm sorry, i wasn't precise enough. I want to check the integrity of the Nested Set structure, which has nothing to do with the parent_ids. Its structure consists only of the left and right nodes.

 

I know that the parent_id-Structure is ok, but this system works mainly with the nodes, i think the parent_id and root_id structure is only in the db for "backup".

 

I'm looking for a script that checks the integrity of the left and right nodes, if all trees are correct, maybe if the tree structure matches the parent_id-structure.

 

Regards