advanced: tree copy issue

[simpli]

Hi,

I have quite an advance issue in my database and I need your input to find out how I should tackle.

I have a table (table A, auto-increment primary key) that represents tree items.

Scenario_id node_id
1 28
1 29

nodes 28 and 29 are children of the same parent.

there is an accompanying table (table B) that tracks the position of the items in the tree. It's called a closure table in the literature.

Scenario_id 	ancestor_id 	descendant_id
1 	28 	28
1 	29 	29
1 	29 	37

(this above means that node 28 is is own ancestor, node 29 also and 37 is a descendant of 29)

 

I am trying to develop a functionality where I can make a copy a whole parent item. Copying the data from table A poses no problem. It would basically copy the items I had earlier. Because of the auto-increment feature however we would have 1 30 and 1 31. At that point however, the copy is not complete. We still need to reproduce the path that the copied parent had. We cannot however copy the data that is in the closure table as it is refering to the original data. My question is therefore what's the easiest way to recreate the path?

 

I think copying each table A line and the corresponding table B lines would be possible but I am not sure this is the most efficient way of dealing with this. Anyone can chip in and maybe point me to ways of handling this i havent tought of?

Thanks in advance.

[xtopolis]

I may not be understanding, but doesn't your issue relate to this article: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html  ?

[simpli]

yes it does in a sense as it shows one way of organizing the hierarchical tree data. But that's done for me. And it works. Now I'm trying to COPY a node and all its children and it's that operation I'm having trouble conceptualizing.

JR

[dzelenika]

yes it does in a sense as it shows one way of organizing the hierarchical tree data. But that's done for me. And it works. Now I'm trying to COPY a node and all its children and it's that operation I'm having trouble conceptualizing.

JR

I think You are using bad tree logic. It is better to realize tree in one table with fields:

 

node_id (PK)

parent_id (ID of parent node)

order (makes order between siblings from same tree level)

 

Top nodes doesn't have parent_id

Then make a recursive function which inputs top node of tree part which you want to copy.

Function should collect all nodes which have provided parent_id, loop through nodes and call itself again for any node until node has descendants.