-------------------------------------------------- ------------------------------- BBS tree structure (1)
The tree structure display of BBS can have many ways, which is easier to think of the recursive and sorting string method, but these two methods are not very good, then how do you calculate a relatively reasonable algorithm? Recursive method does not use, everyone knows how to use, first talk about the sorting method, the simplest sort string method can be used, only one ID can complete the tree, to this 1 001 2 001 3 001002001 4 001001001 5 001002001 After this string is sorted, it will become like this: 001 001001 001001001 001002001 002 This method is easy to implement, but the disadvantage is also very obvious, one is limited by the number of replies, the other with the increase in reply will grow, affecting database efficiency .
One of the following methods is Li Long's, which is a variable string method DDL -------------- Create Table dbo.Message (ID Numeric (18, 0) Identity (1000, 1), DateAndTime datetime DEFAULT getdate () NOT NULL, AuthorID numeric (18,0) NOT NULL, Subject nvarchar (250) NOT NULL, Body ntext NULL, linkURL nvarchar (100) NULL, TextForLink nvarchar (50) NULL, ImageURL nvarchar (100) NULL, Class int DEFAULT 0 NOT NULL, ClientInfo nvarchar (250) NULL, RemoteAddr nvarchar (50) NULL, CONSTRAINT PK_BBSMessage PRIMARY KEY NONCLUSTERED (ID, AuthorID)) go CREATE TABLE dbo.MsgRefTab (MsgID numeric (18,0) NOT NULL Parentid Numeric (18, 0) Not Null, Ancestorid Numeric (18, 0) Not Null, ChildNum Numeric (18, 0) Default 0 Not Null, Linkstr Nvarchar (250) Not Null, Constraint PK_BBSREFTAB Primary Key Nonclustered (msgid)) GO ---------- ------- Store procedure: ----------------- Take Create Procedure SP_Summary @haveBody Bit, @From Numeric, @To Numeric As IF (@havebody =
1) SELECT T.ID, T. DateandTime, M. Nickname as Author, M.Email, T. Subject, T.Body, T. LinkURL, T.Textforlink, T.ImageURL, S.ChildNum, S.Parent from Message T, MsgReftab as s, (select msgid from msgreftab where parentid = 0) as f, members as m where t.id = s.msgid and f.msgid = S.Ancestorid and f.msgid betWeen @From and @to and m .Meid = t.authorId Order by S.Ancestorid, S.LinkStr else SELECT T.ID, T.DateAndtime, M. Nickname as Author, M.Email, T.Subject, T.LinkURL, T.Textforlink, T.ImageURL , S.ChildNum, S.PAR
Entid from Message T, Msgreftab As s, (Select Msgid from Msgreftab WHERE PARENTID = 0) AS F, MEMBERS AS M where t.id = s.msgid and f.msgid = S.Ancestorid and f.msgid Between @From and @ To and m.memberid = T.authorid Order by S.Ancestorid, S.LinkStr Go
- Add
CREATE PROCEDURE sp_Add_Message @AuthorID numeric, @Subject nvarchar (250), @Body ntext, @LinkURL nvarchar (100), @TextForLink nvarchar (50), @ImageURL nvarchar (100), @ParentID numeric, @ID numeric OUTPUT, @ChildNum numeric OUTPUT, @LinkStr nvarchar (250) OUTPUT, @AncestorID numeric OUTPUT AS INSERT INTO Message (AuthorID, Subject, Body, linkURL, TextForLink, ImageURL) VALUES (@AuthorID, @Subject, @Body, @LinkURL, @TextForLink, @ ImageURL) SELECT @ID = @@ iDENTITY
Update msgreftab set childnum = childnum 1 where msgid = @parentID
Select @ChildNum = childnum, @LINKSTR = Linkstr, @Ancestorid = Ancestorid from msgreftab where msgid = @parentid Go
--- is based on this idea: posts and followers are placed in the Message table, and the MsgReftab has a description of each message. Pupire Parentid, 0 is not a child, ancestor sticker, the ancestor is directly connected to the CHILDNUM, and the learning is here, all the followers are expressed in a digital string, if it is 1011 ---> Empty 1012 ---> 001 1011's follow-up, parent stickers LINKSTR parent stickers 1 1013 ---> 001001 1012 follow-up, parent stickers LINKSTR parent stickers 1 1018 ---> 001001001 1013 follow-up, Parent Sticker LinkStr Pupid 2 Number 1 1014 ---> 001002 1012 of the follow-up, pupils LINKSTR parent stickers 1 1017 ---> 001002001 1014 follow-up, parent stickers LINKSTR parent stickers Substand 1 Some Demo Data: Msgid Parentid Ancestorid ChildNum Linkstr 1010 0 1010 0 1011 1011 3 001 1014 1011 1011 1 0011014 1012 1011 1 001002 1015 0 1015 0 1017 1014 1011 0 001002001 1018 1013 1011 0 001001001
It is a complicated algorithm, but only SELECT has been a list of correct structural lists. I have seen so many BBS algorithms, or I feel that I have a good way. In the reality, the stored procedure directly generates an XML document, handed over to the client.
-------------------------------------------------- ----------------
Method for implementation of BBS tree structure (2)
Below this method is the method of the big monster and Yihong Bozi
Create Table Forum (ID INT NOT NULL IDENTITY, / * Post serial number * / rootid int not null, / * Root feeder number * / ParentID int not null default = 0, / * Double pro post serial number * / indent tinyint, / * Indentation * / Order tinyint, / * with the subject post sort * / username varchar (40) Not null, / * username * / time daytime not null, / * Posts time * / ip varchar (15) Not null, / * IP * / SUBJECT VARCHAR (60) Not null, / * Post topic * / text text, / * Post body * / bytes int, / * Post word number * / status bit, / * status * / Hits Tinyint , / * hIT number * / primary key (id) / * main keyword * /) Simply says the hierarchy of the hierarchy 1.Rootid 2.indent 3. Under the same root, Order_no
Such as
1 post 2 post 3 post 5 post 4 4 号 6 6
The storage format of this structure is as follows Rootid Indent a root, orde_no 1 1 0 0 2 1 1 3 1 2 2 4 4 0 0 5 1 1 3 6 4 1 1
Press rootid, "a root, order_no" sort, according to Indent to indent the tree to the list of posts
Indent is a 4byte integer. If you start from 0, you support 2147483648 layers. If you set up Numberic, then I can't help support several layers.
BBS tree structure Implementation Method (3) The floating point number of BBS database structure is composed of a series of articles, and each article has some basic properties, such as authors, creation time, articles number, etc. The most important of which is used to indicate the layer and the number of layers. The layer represents the first few layers of the article tree, the highest layer of post layer is equal to 0, and the reply is 1, the reply layer of the reply is 2, and so on. All layers are equal to 0 post-time sequences, their orders are 1, 2, 3 ..... The order of the remaining posts satisfies the following conditions: When all posts are displayed, their orders from large to small arrangements ,with no exceptions.
When the above method specifies the implementation of BBS, various operations are performed as follows: 0, the system maintains a curler, indicating the current number of integers currently used. 1. Display the list of posts: The size of the sequencing value is simply displayed, and the layer of the post can help determine how much. 2, new addition: If it is a post on layer 0, remove an integer order as the number of posts; if it is a layer L1 post, its order is N1, then the new post L = L1 1, then go to the database The next post in the post of the number is N1, take the order, assuming to N2, then the number of new posts n = (N1 N2) / 2.3, delete post: Assume that the delete layer L1, the number of orders is N1 The posts and all of their followers, then take the order N2 of the next post of L1, then delete all orders as posts from N1 to N2 (excluding N2).
The biggest advantage of the above method is that no additional operation is required when the list of posts is displayed, and the speed is abnormal, and no recursive operation is required at all. It is also convenient for paging anomalies. If you secure the topics per page, you can only return only records of the current page in the SQL query, which can reduce the resource of the system to minimize. Even if the total number of posts is fixed according to each page, you can limit the SQL query only return to the record you need.
Below is an intuitive representation of various values (for easy understanding, according to the ascending order of the order, it will be designed in descending order, so as to make the final post display at the forefront):
1.Topic1 (layer = 0, order = 1) 5.re :topic1 (layer = 1, order number = 1.125) 4.re :topic1 (layer = 1, order = 1.25) 3.re :topic1 (layer = 1, Order = 1.5) 6.re: Re :topic1 (layer = 2, order = 1.75) 8.re: Re :topic1 (layer = 3, order = 1.7875) 7.re: RE :topic1 (layer = 2, Order = 1.825) 2.Topic2 (layer = 0, order = 2) 10.re :topic2 (layer = 1, order = 2.5) 9.topic3 (layer = 0, order = 3) 11.re :topic3 (layer = 1, the order = 3.5)
As can be seen from the above example, as the layer increases, and the number of orders, the number of orders is increasing, so a possible problem with this method is that SQL Server cannot indicate enough small enough The number of floating point is the same as the number of the two posts. After experiment, I know that SQL Server has 8 floating point, up to 1023 reposses.
