Tree structure (2)

Realization: With a routing field (sorted by characters), the actual length of this field is the reply depth (when a character represents a layer depth). Restriction: The reply depth is only limited by the sequencing string defined (a bit like space change depth), and each post-reply number (including root stickers and sub-stickers) is 30 (when SQL Server uses Dictionary Order, Case-Insensitive sorting method, That is, when the letter case is not distinguished), if SQL Server uses a binary order, it is limited to 127 (255?). Improvement method: If you feel that you don't have enough, you can use multiple characters to correspond to a depth (this calculation is a bit trouble), or use several digits represent a depth, such as 3 digits ?? You can 999 posts, but some sort fields The length should be "3 * maximum depth") Advantages: This method is uniform.

Fields related to tree: rootid, Orderstr (VARCHAR type, depth as needed, if you want to use 20 layers of reply, it is routed as varchar (20), after the residual push)

For example: (below the ordering of Order by rootid (1-sign (rootid)) * lybid desc, orderrstr, id dec ___________________3 1 y ?? Reply 1, reduce the last character z of the sorting string of the next to 1, constitute a new sort string.

Sorting results are: ID rootid orderrstr1 0 empty string 3 1 y2 1 z___________________4 1 x ?? Reply 1 post, sorting string begins

The row results: ID rootid orderstr1 0 Empty string 4 1 x3 1 y2 1 z__________________5 1 xz ?? Reply 4 post, check 4 stickers already have a reply, now, the initialization sage second character (add Z after 4 )

Sort result is: ID rootid orderrstr1 0 Empty string 4 1 x5 1 xz3 1 y2 1 z____________________________6 1 XY ?? Reply 4 post, the first character of the first child of 4 stickers is reduced by 1 ASCII code minus 1

Sorting results: id rootid orderrstr1 0 Empty string 4 1 x6 1 xy5 1 xz3 1 y2 1 z

In this way, the tree structure is realized according to the order of ORDERSTR and LEN (ORDERSTR)? ID ORDERSTR1 Empty Strings 4 x 6 XY 5 XZ 3 Y 2 Z

Affixed stored procedure: if exists (select * from sysobjects where id = object_id ( "lybsave")) drop proc lybsaveCREATE PROCEDURE [lybsave] @keyid int = 0, @ guestname varchar (20), @ guestitle varchar (100), @ Guestcomm text, @ Guestemail Varchar (50) = ', @ emailflag bit = 0, @ fromip varchar (15), @ Recimail Varchar (50) OutputasDeclare @ostr varchar (30), @ rootid int, @ @ @ @ @ @ @ @ @ @ ostrs VARCHAR (30), @ @ l tinyint, @ Tdt DateTime, @ Putdate Varchar (10), @ Puttime Varchar (5), @ Eflag Bitslect @ TDT = Getdate () SELECT @ Putdate = Convert (VARCHAR (4), Datepart (YY , @ TDT)) '-' LEFT ('0' Convert (VARCHAR (2), DatePart (mm, @ TDT)), 2) '-' Left ('0' Convert (varchar (2 ), DatePart (DD, @ TDT)), 2) SELECT @ Puttime = Left ('0' Convert (VARCHAR (2), DATEPART (HH, @ TDT)), 2) ':' Left ('0 ' Convert (VARCHAR (2), DATEPART (MI, @ TDT)), 2) SELECT @ostr =', @ rootid = 0, @ @ = 0if (@ Guestemail = '') Select @ emailflag = 0If @ keyid = 0 - new stickers goto newinELSEbegin SELECT @ lybid = lybid, @ rootid = rootid, @ ostr = orderstr, @ recimail = guestemail, @ eflag = emailflag from guestbook where lybid = @ keyid IF @ lybid = 0 - Reply Post is not found When a new posted goto newin else becomi (@ EFLAG = 0 and @guestemail <> 'Swuse@21cn.com abc') Select @ Recimail = '' - If it is a moderator reply and specifies a message to ask questions, Whether the posser requires reply, the back ABC is equivalent to managing password if (@ rootid = 0) select @ rootid = @ ostr '%', @ \bid = 0 Select Top 1 @

lybid = lybid, @ ostrs = orderstr from guestbook where rootid = @ rootid and (orderstr like @ostrs) and lybid <> @ keyid order by orderstr if (@ lybid = 0) select @ ostr = @ ostr char (122) else Begin select @ l = len (@OSSTRS) SELECT @ ostr = left (@ ostrs, @ L-1) char (Substring (@ ostrs, @ L, 1)) - 1) End Goto newin endndnewin: Insert Into guestbook (guestname, guestitle, guestcomm, putdate, puttime, guestemail, emailflag, rootid, fromip, orderstr) values ​​(@ guestname, @ guestitle, @ guestcomm, @ putdate, @ puttime, rtrim (@guestemail), @ emailflag, @ rootid , @ fromip, @ ostr)

Delete the post (twig) stored procedure:

if exists (select * from sysobjects where id = object_id ( "lybdel")) drop proc lybdelCREATE PROCEDURE [lybdel] @keyid intASDECLARE @ostr varchar (30), @ rootid int, @ lybid intselect @ostr = ', @ rootid = 0 , @ lybid = 0SELECT @ ostr = orderstr, @ rootid = rootid, @ lybid = lybid from guestbook where lybid = @ keyidif (@lybid <> 0) BEGIN if (@ rootid = 0) select @ rootid = @ lybid SELECT @ostr @ @ ostr '%' delete from guestbook where orderrstr limited @ostr and rootid = @ rootid or @ rootid end

The following is a SQL statement that establishes the library structure (as an example of a simple tree message):

if exists (select * from sysobjects where ID = object_id ( "guestbook")) drop table guestbookgodrop table guestbookcreate table guestbook (lybid int identity (1,1), guestname varchar (40) NOT NULL, guestitle varchar (100) NOT NULL, GUESTCOMM VARCHAR (8000), Putdate Varchar (20), Puttime Varchar (10), Guestemail Varchar (50), Emailflag Bit, Rootid Int, Fromip Varchar (25), ORDERSTV VARCHAR (3)) Program Name: Savelyb.asp program function : Save the post content

<% postType = request ("posttype") Pageno = Request ("pageno") keyid = request ("keyid" if keyid = "" "" "" 0emailpost = request ( "emailpost") guestitle = trim (request.form ( "guestitle")) guestname = trim (request.form ( "guestname")) guestcomm = rtrim (request.form ( "guestcomm")) guestemail = request .form ("Guestemail" emailflag = request.form ("emailflag") if guest = "" or guestname = "" "or" "" or "" = "" "f" information is incomplete, the message must fill in the name and title

return refill "" then call posttomecall errmessage ( "

Thanks your message!

read a message ") session (" guestitle ") = guestitleguestconn .Closeset GuestConn = NothingResponse.endsub errmessage (message)%>