The storage and management of the tree structure is the problem that the programmer working on the relational database platform will encounter. It is big, how can I solve, saying that small is not small, it's not good, some are trouble waiting for you. The benevolent sees the benevolence, the wise is witness, the public is public, the mother said that her wife is reasonable (who uses the chassis? It is a good thing, and the chaos will fall down the hard disk. You see that I didn't finish the display, I lost the display ... ), Cough, ok, gossip less, we talk about the Treatment style of the largest road. Most of this is not my original, from a long time ago, the database programmers did this.
Structured expression of tree table
Before every start, let's reach a consensus on the representation of the tree table. In the relational database, we certainly have no way to directly represent a tree:
a
B C
D e f g
Corresponding, we will deform it into a flat table, which makes me think of topology geometry:
R N1 N2
A B D
A b e
a c f
A c g
Storage structure
A little experienced friend, probably not trying to put a layer of tree structure in the tree. The problem of doing this is obvious: different from the information structure stored in the table, the structure of the table should be relatively fixed, and cannot be changed casually. It is incredible for ordinary tree structures that cannot be fixed for layers. There is no need to discuss excessive mistakes, we choose a relatively correct way - put the tree structure into the form of a relational database.
Just consider a certain node, and the information related to this node is: its unique identifier, parent node, child node, data information. Only the number of only child nodes is uncertain. However, if each node determines its own parent node, it is obvious that the subtocks can be omitted. In this way, a node needs to store three parts information - its unique identity, parent, data information. An ideal TreeView table can only need three nodes, and express it with a SQL statement.
CREATE TABLE [DBO]. [TreeView]
[ID] [char] (10) Primary Key,
[PID] [char] (10) Foreign Key References [DBO]. [TreeView],
[DATA] [char] (10)
)
ID is the unique identification number of the current node, apparently it should be a primary key; we build a self-closed storage structure, and the parent node of each node should also be stored in the table, so the PID column reference ID as a foreign key; As for DATA, it is information in the node, usually there is no absolute relationship with the structure of the tree. I put these three columns into char (10), which is more convenient for later demonstrations, and of course some people like to use automatic identity columns to do primary keys, in this case, they also have their own advantages. In order to maintain the integrity or storage, retrieval of data, we may use more complex structures, but the backbone is like this. This structure is very simple from mathematics, and it is self-contained. If a node does not have a parent node, then its PID is equal to its own ID. This does not violate our definition of the primary key.
Information management and use
After the structure of the tree table is determined, the problem is concentrated on how to read and write the data.
Add a node: Increase a leaf node is simple, as long as the parent node of this node is specified, "Mount" to TreeView. From a recursive point of view, we can repeat this step, which is really inserted into a complete sub-tree. Relatively, it is more trouble that how to insert a child node into the middle of the existing tree, not the end. For example, there is a node n, its root is R, now insert a new node n 'between R and N, we can do this: Hill N' below R, as its child node, then put N The parent node is specified as N '. Modify the node: Here, refer to the modification of the tree structure, change the parent node of a node. In this structure, it is convenient to modify, as long as the standard Update is called.
Delete Node: When deleting a node, pay attention to this node There is no child node. If we have, we usually handle it in two ways, one is to complete the related child nodes, if it is the system of MS SQL Server 2000, you can It is very simple to design the foreign key constraint as the support level to delete, it is not possible to write a level ordered, but it is not impossible. The focus is to establish recursive for this process. The brief example is as follows:
- Define stored procedures
Create Procedure deletenode
@Nodeid char (10)
AS
Begin
- Establish a cursor as a recordset in the child node of the current node
Declare ChildNodes Cursor
Read_only
For select id from treeview where pid = @nodeid
Declare @ChildNode Varchar (40)
Open ChildNodes
Fetch next from childnodNodes Into @ChildNode
While (@@ fetch_status <> -1) - Judgment whether the recordset is successful
Begin
IF (@@ fetch_status <> -2)
Begin
- recursive call
Exec deletenode @ChildNode
End
Fetch next from childnodNodes Into @ChildNode
End
Close ChildNodes
Deallocate ChildNodes
- The code is executed here, you can determine that @NodeId no longer has a subpode, now we delete it
Delete from treeview where id = @nodeid
END;
Of course, this is a relatively inefficient design. Each of the nodes that will be deleted must perform DELETE. The more efficient method is to depth, and the child node of the current node is operated. Interested readers can try it.
Query: The query of the tree table is one of the most interesting content. Of course, only the information of a node is not too great, we want to get a complete sub-tree that has been down from the current node (usually to the bottom). This also requires a recursive. Let us start from an inductive game, beforehand, let's insert the following data in 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 Singapple Method First Step, of course At the beginning, the root node of the query sub-tree is the standard SQL, SELECT ID, DATA FORM TreeView WHERE ID = ..., there is a detail here, find all the root nodes in the table (careful readers may have discovered, we designed This TreeView can store any plurality of trees) can be implemented by Select R.ID, R.Data Form TreeView R where r.ID = R.PID. Simple, let's start here.
In 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 have to pay attention to is a granular part of the blue, which is part of the change in the change.
It is easy, we get the top 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
Similarly, we focus on the part of the blue. I believe that after these two steps, everyone will find the law. Now we can automate this process ...
......
I don't know if you still remember the jokes about the four-color problem in the first few episodes. I made this mistake again. For more than a month, I can't pull here. Obviously, I underestimate the difficulty of the problem. This problem does not seem to be converted into a simple expression and can only be implemented by a recursive process. The process of process-chemical procedures are not SQL. There are all kinds of trouble. I believe that friends trying to have an experience. Now I use the architecture problem is obvious, it needs to know how many layers from the bottom of the subtree. Therefore, the number of layers of the calculation tree is first mentioned.
However, but, but ... (I just got someone) I haven't found a simple and elegant approach. Time for more than a month has passed. I have to give up my principles a little bit (this is the beginning of life). Finally, I have to admit that I want to write an elegant tree table to choose from, it is still more difficult to me. So, in this article, let's discuss it first, the choice of tree table - actually tells tree views, or leaves it next time.
