RE: [xml-dev] hierarchical XML in a relational DB structure - moving nodes

["Erik Wright" <erik.wright@radialpoint.com>]

Robert, I have had success with something similar in the past.

To create a space in a parent node you would do something like the following:

#> update T_NODE set F_INDEX=F_INDEX+1 where F_PARENT_NODE_PK=[dest_parent] and F_INDEX>=[dest_index]

To move an existing node into the created space you would do this:

#> update T_NODE set F_INDEX=[dest_index], F_PARENT_NODE_PK=[dest_parent] where F_NODE_PK=[node_to_move]

After removing a node from a parent you could collapse the indexes in the parent using something like:

#> update T_NODE set F_INDEX=F_INDEX-1 where F_PARENT_NODE_PK=[source_parent] and F_INDEX>[source_index]

To move a node within a parent, the best to do is:

#> update T_NODE set F_INDEX=[current_greatest_index+1] where F_NODE_PK=[node_to_move]
#> -- do the following when moving to a greater index
#> update T_NODE set F_INDEX=F_INDEX-1 where F_PARENT_NODE_PK=[parent] and F_INDEX>[source_index] and F_INDEX<=[dest_index]
#> -- do the following when moving to a lesser index
#> update T_NODE set F_INDEX=F_INDEX+1 where F_PARENT_NODE_PK=[parent] and F_INDEX<[source_index] and F_INDEX>=[dest_index]
#> -- always do this
#> update T_NODE set F_INDEX=[dest_index] where F_NODE_PK=[node_to_move]

Clearly you need to take some precautions with regards to ACIDity. If someone else grabs an index before your move and tries to use it afterwards, they will be in trouble. You will either need to only use indexes within transactions or use some sort of optimistic-locking strategy for validating state before using them again - for example, when inserting a node between two others, making sure that the preceding and following node PKs are the ones that you expect.

Cheers,

Erik

> -----Original Message-----
> From: Robert Koberg [mailto:rob@koberg.com] 
> Sent: May 24, 2006 3:53 PM
> To: xml-dev
> Subject: [xml-dev] hierarchical XML in a relational DB 
> structure - moving nodes
> 
> Hi,
> 
> I want to use a relational database to store data that is 
> nested and where the order is preserved. I am having a hard 
> time wrapping my head around how to do the following mostly 
> because I don't have much experience with relational databases.
> 
> I am looking at mapping a structure like so:
> 
> <node id="1">
>    <node id="11">
>      <node id="111">
>        <node id="1111"/>
>        <node id="1112"/>
>        <node id="1113"/>
>      </node>
>      <node id="112"/>
>      <node id="113"/>
>      <node id="114"/>
>    </node>
>    <node id="12">
>      <node id="121"/>
>      <node id="122"/>
>      <node id="123"/>
>    </node>
> </node>
> 
> To a relational database that might have a table representing it like:
> 
> Node
> -------
> id
> parentId
> parentIndexPosition
> 
> First question: Is using an index position the best way to 
> maintain the position? (or is preceding and/or following 
> sibling better? or something
> else?)
> 
> OK, lets say I have imported some data/XML into the above 
> table, giving appropriate parents and index positions.
> 
> Next, say I want to move /node/node[2]/node[@id=121] to the 
> position /node/node[1]/node[1].
> 
> How do you handle updating the index position of other nodes? 
> All children of /node/node[@id=12] will need their 
> parentIndexPosition moved down one. And all children of 
> /node/node[@id=11] will need their parentIndexPosition moved 
> up one. (the ID attributes where abritrarily created and 
> should not indicate position)
> 
> Is there a built in mechanism to do this type of thing in 
> relational databases? If not how would an UPDATE look?
> 
> Are there any java libraries or articles out there that 
> handle this type of thing? (I am having a hard time finding 
> anything with google)
> 
> thanks,
> -Rob
> 
> 
> -----------------------------------------------------------------
> The xml-dev list is sponsored by XML.org 
> <http://www.xml.org>, an initiative of OASIS 
> <http://www.oasis-open.org>
> 
> The list archives are at http://lists.xml.org/archives/xml-dev/
> 
> To subscribe or unsubscribe from this list use the subscription
> manager: <http://www.oasis-open.org/mlmanage/index.php>
> 
> 
>