The tree structure of BBS has always been a topic discussed. I used to use naming rules to achieve, this kind of benefits are less redundant character, clear, easy to understand, but their limitations are also obvious. Thanks to Liao Jiayuan to provide algorithm (in truth, the algorithm did not learn), I decided to use this algorithm to achieve the tree structure of BBS. Basic ideas are as follows: BBS article table has such a field: rootid: root ID, new post and all its sub-stickers are the same. FatherID: Father ID, Father ID Layer: The number of layers, the depth of the post in the tree. ORDERNUM: Sorting Base, the key, sort it according to it.
Basic algorithms are as follows:
Root 16 (take a small example) ID ORDERNUM LAYER 1 16 02 16 16/2 1 Reply to the first post 3 16 16 / (2 ^ 2) 1 Reply to the first post 4 16 16/2 16 / 2 ^ 3) 2 Reply to the second post 5 16 16 / (2 ^ 2) 16 / (2 ^ 4) 2 Reply to the third post
Then, according to the result of the sort is (plus the depth of the reply), a tree structure) ID ORDERNUM depth 1 16 03 16 16 / (2 ^ 2) 15 16 16 / (2 ^ 2) 16 / ( 2 ^ 4) 22 16 16/2 14 16 16/2 16 / (2 ^ 3) 2
It became such a tree: 1 3 5 2 4
According to the above ideas, our design is as follows:
/ * BBS article table * / if EXISTS (SELECT * from sysobjects where id = Object_id ("bbs")) Drop Table BBSGO
create table BBS (ID int primary key identity not null, RootID int default 0 not null, FatherID int default 0 not null, Layer tinyint default 0 not null, ForumID int default 0 not null, UserID int default 0 not null, Title varchar ( 255) default "" not null, Content text default "", PostTime datetime default getdate () not null, FaceID tinyint default 0 not null, TotalChilds int default 0 not null, OrderNum float default power (2,30) not null, Hits Int default 0 Not null, SELECTED BIT Default 0 Not Null, Closed bit default 0 not null, IfEmail bit default 0 not null, IfSignature bit default 0 not null) go / * BBS registered user table * / if exists (select * from sysobjects where ID = object_id ( "BBSUser")) drop table BBSUsergo
Create Table Bbsuser (ID INT Primary Key Identity Not Null, Username VARCHAR (20) Default "" NOT NULL, Password Varchar (10) default "" "NOT NULL, UserType Tinyint Default 0 Not Null, - User Type, 1 is Bamboo Email VARCHAR (100) Default "" NOT NULL, HOMEPAGE VARCHAR (100) Default "" NOT NULL, ICQ VARCHAR (20) Default "" NOT NULL, SIGNATURE VARCHAR (255) Default "" NOT NULL, - Signed Point Int Default 0 NOT NULL, - User Points) The GO table structure is set, and the rest is how to implement it. I integrate all related functions in a stored procedure, including the storage itself, the key is the generation of the basement of the base; the update of the parent's related fields; the root stickers related fields are updated, these are put in a transaction To maintain the consistency of data, if the parent post requires a reply with an email notification, the function that is replying to Email is implemented during the stored procedure without having to use any ASP or other components. This makes all tasks in a stored procedure.
-------------------------------------------------- ------------------------------
Below is the stored procedure mentioned by the last article, and its role has been said, and it will not be described here. Please see the code yourself. This stored procedure is just a process of storing data. If you have time, I will read data.
/ ************************************************** ********************* / / * * // * Stored Procudure: Up_posttopic * // * * // * Description: Post Storage and Reply Email * / / * * // * Author: BiGeagle * // * * // * Date: 2000/7/25 early morning * // * * // * History: Version 1.0 by Bigeagle, 2000/7/25 * // * * // ******************************************************** ********************** / IF EXISTS (SELECT * from sysobjects where id = Object_id ("UP_Posttopic")) drop proc up_PostTopicgocreate proc up_PostTopic @a_intID int OUTPUT, @a_intFatherID int, @a_intForumID int, @a_intUserID int, @a_strTitle varchar (255), @a_strContent text, @a_intFaceID tinyint, @a_bIfEmail bit, @a_bIfSignature bit as declare @m_intTopicID int declare @m_intlayer tinyint declare @m_introotid int declare @m_fordernum float
SELECT @m_fordernum = power (2, 30) - Initialization Sort Base
/ * First, it is first judge whether there is this forum, no, exit * / if not exists (select * from bbscategory where categoryid = @a_intforumid) begin select @a_intid = 0 return (0) end / * Judgment is a new post or response theme * / If @A_INTFATHERID = 0 - No father, explanation is new post select @m_intlayer = 1, @M_INTROOTID = 0 else begin if not exists (SELECT * from bbs where id = @a_intfatherid) - if not Discover parent post becom select 'Topicid' = 0 return (0) end else - Remove the number of layers and root ID SELECT @m_intlayer = layer 1, @ m_fordernum = Ordernum from BBS WHERE ID = @A_INTFATHERID END / * Update Table, Because you want to operate multiple tables, put it in transaction * / begin transaction / * Insert Table BBS * / Insert Into BBS (FatherId, Layer, ForumID, UserId, Title,Content, PostTime, FaceID, Hits, selected, closed, IfEmail, IfSignature, OrderNum) values (@a_intFatherID, @m_intLayer, @a_intForumID, @a_intUserID, @a_strTitle, @a_strContent, getdate (), @a_intFaceID, 0, 0, 0 , @a_bifemail, @a_bifsignature, default) IF (@@ error <> 0) goto on_ERROR - If the error turning error handling section select @m_inttopicid = @@ identity - Remove the id just inserted records
/ * If it is a new post, take the ID to rootid * /
IF @m_introotid = 0 - New Post Begin Select @m_introotid = @m_inttopicid endelse - Update the root record TOTALCOUNTALCHILDS = TOTALCHLDS 1 - Update the root number WHERE ID = @M_INTROOTID IF (@@ error <> 0) goto on_ERROR - Turn to Error handling section end if the update fails
SELECT @m_fordernum = @m_fordernum power (2, 30) / Power (2, Totalchilds) from bbs where id = @m_introotid select @m_fordernum
/ * Update rootid, ordernum * /
Update bbs set ordernum = @m_fordernum, rootid = @m_introotid where id = @m_inttopiciD if (@@ Error <> 0) GOTO ON_ERROR - Turn to Error handling section if the update fails
/ * Update bbscategory table * / update bbscategory set topiccounts = topiccounts 1, lastreplytime = getdate () where categoryid = @a_intforumid = @a_intforumidiff = @a_intforumid = @@ @@> 0) GOTO ON_ERROR - Turning to Error handling section if the update fails
/ * Update the BBSUser table, add the user fraction 1 * / update bbsuser set point = point 1 where id = @a_intuserid
/ * Complete transaction if all succeeds * / Commit Transaction
