SQL Story (11)--Tree table game

The storage and management of tree structure is the problem that every programmer who works on the relational database platform will have to meet with each other sooner or later. Say Big little, how all can solve, say small, handle bad, have trouble waiting for you. The common sense of the people, the public said the right, the woman said the woman rational (who use the chassis to hit me?) The chassis is a good thing, throw will break the hard drive, you see my words did not finish you again lost the monitor ... Cough, well, gossip less, let's talk about the best way to deal with the style. Most of this is not my original, and from a long time ago, database programmers have done so.

The structural expression of tree-like table

Before we begin, we have reached a consensus on how to express the tree table. In relational databases, of course we have no way to represent a tree directly:

A

b C

D E F G

Accordingly, we will transform it into a planar table, which reminds me of topological geometry:

R N1 N2

A b D

A b E

A C F

A C G

Storage structure

A friend with a little experience probably won't try to put a tree-like structure in a row. The problem with this is obvious: unlike the information structure stored in the table, the structure of the table should be relatively fixed and not easily altered. And for ordinary tree structures that cannot be fixed by layers, this is inconceivable. There is no need to discuss too many errors, we choose a relatively correct way-to abstract the tree structure into a form suitable for relational databases.

If only one node is considered, the information associated with this node is its unique identity, parent node, child node, and data information. Only the number of child nodes is indeterminate. However, if each node determines its own parent node, it is obvious that the child nodes can be omitted. As a result, a node needs to store three pieces of information-its unique identity, its parent node, and its data information. An ideal TreeView table requires only three nodes, which is expressed in SQL statements.

CREATE TABLE [dbo]. [TreeView] (

[ID] [char] (PRIMARY KEY),

[PID] [Char] (Ten) FOREIGN KEY REFERENCES [dbo]. [TreeView],

[DATA] [Char] (10)

)

The ID is the unique identification number of the current node and it should obviously be the primary key; we build a closed storage structure, each node's parent node should also be from the node stored in the table, so the PID column reference ID as a foreign key, as for data, it is the information in the node, usually with the structure of the tree has no absolute relationship. I have all three columns set to char (10), is for the later to do the demo more convenient, of course, some people like to use Automatic identification column to the key, in this case, also has its own advantages. In order to maintain the integrity of the data or storage, retrieval and other aspects of consideration, practical we may use more complex structure, but the backbone is like this. The structure is mathematically concise and self consistent. If a node does not have a parent node, its PID is equal to its own ID. This does not violate our definition of a primary foreign key.

Management and use of information

After the structure of the tree is determined, the problem is focused on how to read and write the data.

Add nodes: Adding a leaf node is simple, just specify the node's parent node and "Hook" it to the TreeView. From a recursive point of view, we can repeat this step, really to insert a complete subtree. Relatively troublesome, however, is how to insert a child node into the middle of an existing tree, rather than the end. For example, there is a node n, its root is R, now to insert a new node n ' between R and N, we can do this: put N ' under R, as its child node, and then assign n ' parent node as n '.

Modify a node: This refers to modifying the tree structure, changing the parent node of a node, in which the modification is convenient, as long as the standard update is invoked.

Delete node: When deleting a node, be aware that there are no child nodes under this node, if there are, we usually do in two ways, one is to delete all the relevant child nodes, if the MS SQL Server 2000 such a system, you can be very simple in the table when the foreign key constraints designated to support cascading deletion, Writing a cascading deletion is cumbersome, but it's not impossible, and the point is to build recursion for the process. A brief example is as follows:

--Defining stored procedures

CREATE PROCEDURE Deletenode

@NodeID Char (10)

As

BEGIN

--Creates a cursor as a recordset with the child nodes of the current node

DECLARE childnodes CURSOR

Read_Only

For the SELECT ID from TreeView WHERE PID = @NodeID

DECLARE @ChildNode VARCHAR (40)

OPEN childnodes

FETCH NEXT from ChildNodes into @ChildNode

while (@ @fetch_status <>-1)--determine if the recordset opened successfully

BEGIN

IF (@ @fetch_status <>-2)

BEGIN

--Recursive call

EXEC Deletenode @ChildNode

End

FETCH NEXT from ChildNodes into @ChildNode

End

Close ChildNodes

Deallocate childnodes

--Code execution here, you can determine that @nodeid no longer have child nodes, and now we delete it

DELETE from TreeView WHERE ID = @NodeID

End;

Of course, this is a relatively inefficient design, each of the nodes to be deleted to perform a delete, the more efficient way is to go deeper one layer, operation of the current node's child nodes. Interested readers can have a try.

Query: A tree-like query is one of the most interesting things. Of course, just to find out what the information of a node doesn't mean much, we want to get a complete subtree from the current node down (usually to the bottom). This also requires a recursive. Let's start with an inductive game, in advance, we first insert the following data in the TreeView:

Id

Pid

DATA

----------

----------

----------

ROOT

ROOT

ROOT

N11

ROOT

Node1-1

N12

ROOT

Node1-2

N13

ROOT

Node1-3

N21

N11

Node2-1

N22

N11

Node2-2

N23

N12

Node2-3

N24

N13

Node2-4

N31

N21

Node3-1

N32

N21

Node3-2

N33

N21

Node3-3

N34

N22

Node3-4



The first step of induction, of course, is to construct the initial value, the root node of the query subtree is the standard Sql,select ID, the DATA form TreeView where id = ..., here is a detail that looks up all the root nodes in the table (the attentive reader may have long discovered that The TreeView we designed can store as many trees as possible by select R.id, R.data FORM TreeView R WHERE r.id = r.pid. For simplicity's sake, let's start here.

The second step, it is not difficult to choose the first two layers,

SELECT r.id, N1.id, N1. DATA

From TreeView R

Left JOIN TreeView N1

On r.id = N1. Pid

and R.id <> n1.id

WHERE r.id = R.pid

What we need to be aware of is the Blue section, which is the part of adding a layer after the change.

It's easy, we get the first three floors,

SELECT r.id, N1.id, N2.id, N2. DATA

From TreeView R

Left JOIN TreeView N1

On r.id = N1. Pid

and R.id <> n1.id

Left JOIN TreeView N2

On n1.id = N2. Pid

and N1.id <> n2.id

WHERE r.id = R.pid

In the same way, we focus on the addition of the blue part. Believe that after these two steps, you will find the law. Now we can automate this process ...

......

I do not know whether you remember the last few episodes of the four-color problem of jokes, I once again made such a mistake. I've been stuck here for one months. Obviously, I underestimated the difficulty of the problem. This problem does not seem to translate into a simple expression that can only be achieved through a recursive process. And the process of the program is not a SQL director. There are all kinds of troubles in it. I believe the friends who have tried have experience. The architectural problem I'm using now is obvious, and it needs to know exactly how many layers are down from the top of the subtree. So the number of layers on the tree is first raised.

But, but ... I can't find a simple and graceful way to do it. One months is the time to pass. I have to give up my principles a little bit (this is the beginning of the Fall of Life). Finally, I have to admit that it is still more difficult for me to write an elegant tree-form choice. So, in this article, let's start with a discussion of the choice of the tree--in fact, the tree-view construct, or leave it to the next discussion.