Re: JTree & database: ID column ?

• Previous message: maRIO5: "JTree & database: ID column ?"
• In reply to: maRIO5: "JTree & database: ID column ?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 23 Dec 2003 01:28:50 +0100

"maRIO5" <mario_zupan@inet.hr> wrote in message
news:bs7tir$lm2$1@sunce.iskon.hr...
> Idevelop the JTree which create,update and delete nodes
> from database and for it I use ID1 column which is unique
> and ID2 which is pointer on the parent node.
> My ID1 is look like this:
> 0.0
> 0.1
> 0.1.0
> 0.2
> 0.2.0
> 0.2.1
> 0.2.1.0
> 0.2.1.0.0
> 0.2.1.0.1
> and every number in a code represents the place in a parent node.
>
> and my ID2 is a pointer on a parent node.
> 0
> 0
> 0.1
> 0
> 0.2
> 0.2
> 0.2.1
> 0.2.1.0
> 0.2.1.0
>
> Does it too complicated and doi you have a simplier solution?
> I have problems with the column sorting because it must be a string
> (because this "." is a separator)

I'm sorry, but what exactly is your question? Do you want a better way of
storing a tree structure in a database? And if you can construct a tree
(create java objects) from the data in the database using the way you
described, how is sorting that tree a problem?

A way of storing a tree in the database, is to use 2 integers. one for the
'depth' and one 'reverse-index'. (I would like to point out that a
reverse-index is NOT an auto-inserted id value, although it has to be unique
for every node)

an example tree would be:

Index | Depth # Tree
6 | 0 # *
5 | 1 # ` *
4 | 2 # ` ` *
3 | 2 # ` ` *
2 | 0 # *
1 | 0 # *
0 | 1 # ` *

Now, when you want to insert a node between '4' and '5', you increase every
(reverse)index greater than 4: "UPDATE table SET Index=Index+1 WHERE Index >
4"

add the node using the same depth as it's parent (the '5' in the tree, now
'6' after the UPDATE) "INSERT INTO table (Index,Depth) VALUES (5,2)".

Removing a node, you will do the reverse. First DELETE the node with a
specific index and then lower all Index's of every node with an Index
greater than the one of the node you wish te delete.

To load the nodes from the database and construct a tree in memory you
"SELECT * FROM table ORDER BY Index DESC"

Now you can do something like this:

rootNode = new Node();
currentDepth = -1;
nodeStack = new Stack();
nodeStack.push(rootNode);
while(more_nodes_in_database()) {
    newNode = get_node_from_database();
    while(currentDepth >= newNode.depth) {
        nodeStack.pop();
        currentDepth--;
     }
     // now the top of the stack represents the parent of the node
     Node parent = (Node) nodeStack.getLast();
     parent.add(newNode);
     nodeStack.push(newNode);
     currentDepth++;
}

I hope my silly pseudo-code is any help.

you can google for 'how to represent a tree in a relational database' for
more information (perhaps better)
http://dbforums.com/arch/121/2002/8/445561
^ is one discussion about this subject.

Regards,
 - Vincent

PS) The reason for the reverse-index is that this algorithm was developed
for a forum and more 'happened' at the 'top' of the threads on the database.
(the higher-index ones) so it makes sense to have the UPDATE's affect as few
elements as possible. If the index was reversed, a bigger portion of the
tree would be affected by the UPDATE. If a tree is affected at the bottom as
much as at the top, it doesn't matter which way you go.

• Previous message: maRIO5: "JTree & database: ID column ?"
• In reply to: maRIO5: "JTree & database: ID column ?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]