SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL for Threaded Discussion Forums
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/14/2000 :  20:18:18  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
One of questions we get on a regular basis involves threaded discussion forums. Everyone wants to know an easy way to do this. Everyone is also trying to do recursive SQL or self joins to make this work. That's hard. Here's an easy way to do this. (UPDATE: Fixed a problem and thought I'd repost for everyone to see the changes)

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 11/15/2000 :  21:55:17  Show Profile  Reply with Quote
Another way to do this...

Can you think of another way to do something similar to this? (That is, display a threaded forum with a non-recursive call to the db?) It invovles using an alphanumeric primary key... I'll letcha work on it... :-)

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 11/16/2000 :  00:24:46  Show Profile  Visit graz's Homepage  Reply with Quote
Sure

You append to the right of the key with each succeeding level. First post is 01, it's child is 0101, the next level is 010101 and so on. SQL Server sorts them perfectly by default You have to hard code the number of digits that are important though. Ninety-nine is probably enough in most cases. Why don't you write it up as an alternative and I'll post it :)

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/18/2000 :  02:50:08  Show Profile  Reply with Quote
Bah

99 is appropriate if you have a wussy forum. Why not use something like ten digits, and allow A-Z as the unique characters... then you can have 26^10 unique posts per level... much better. :-)

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/20/2000 :  14:58:32  Show Profile  Reply with Quote
implementing a forum with zero query time

You wrote that query time has to be optimized, but this isn't necessarily crucial. If you're willing to dynamically generate the pages using the sp_makewebtask function when a new posting is made then requesting those pages involves no database queries at all.

The downside is that you need separate physical html/asp pages to store the threads, but it can be generated from a template so it's not too bad.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/20/2000 :  16:21:18  Show Profile  Reply with Quote
Response to BAH

Rather than using A-Z, choose a set of 64 characters and use base 64 encoding. More efficient than 26 on two levels: If you are using some external language to generate the IDs, you can do it with bit-shifting instead of integer divide and modulo. And, of course, you need even fewer "digits" to accomplish the same result.

However...

Isn't it true that most DBs aren't going to let you create an index on a text field longer than 255 characters? (And you certainly want this field to be indexed, don't you? For the ORDER BY to be reasonably efficient?) If so, then the limit you run into is the number of "levels" of response allowed. Assume we use 4 base-64 "digits" for a message number. Then the limit becomes 63 response levels, right? Of course, even 3 base-64 digits allows message numbers up to 262143, which should be enough for most any message board, and bumps the number of response levels up to 85.

Ehhh...if you can't get along with a message board that allows up to 85 levels of response and 256K messages, then maybe you need to find yet another scheme?

I'm using this scheme in an ASP-based board, and even using Access (for prototyping! don't jump on me!) it works really well.

(Okay, I admit to using 4 base-64 digits...don't ask me why.)

Incidentally, didn't mean to be anonymous. Bill Wilkinson of ChiliSoft...errr...of Cobalt Networks....err, of Sun, I guess, next month. billw@chilisoft.com for now, at least.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/20/2000 :  17:47:23  Show Profile  Reply with Quote
Minor correction...

graz wrote:

"You append to the right of the key with each succeeding level. First post is 01, it's child is 0101, the next level is 010101 and so on."

Almost. But what you append is the message number, not the level.

Much more efficient. So message 33 replying to message 17 replying to message 4 becomes:

0004
0004-0017
0004-0017-0033

(Hyphens not needed...I use them for clarity while developing and remove them in final code.)

And, as noted, that sorts perfectly.

Bill Wilkinson
billw@chilisoft.com



Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/21/2000 :  19:00:02  Show Profile  Reply with Quote
I think you have a bug...

I would replace:

SELECT @NextSortOrder = SortOrder
FROM Posts
WHERE PostLevel <= @ParentLevel
   AND SortOrder > @ParentSortOrder

...with...
SELECT @NextSortOrder = Min(SortOrder)
FROM Posts
WHERE PostLevel <= @ParentLevel
   AND SortOrder > @ParentSortOrder

Here's my results as in article:
new thread 1 : 1 1 1 1 1 2000-11-21 18:14:45
follow up to 1: 2 1 1 2 2 2000-11-21 18:15:08
follow up to 2: 4 1 2 3 3 2000-11-21 18:15:46
follow up to 2: 5 1 2 3 4 2000-11-21 18:16:10
follow up to 1: 3 1 1 2 5 2000-11-21 18:15:32
follow up to 2: 7 1 2 3 6 2000-11-21 18:16:48
follow up to 1: 6 1 1 2 7 2000-11-21 18:16:27
Here's my results with Min():
new thread 1 : 1 1 1 1 1 2000-11-21 18:50:31
follow up to 1: 2 1 1 2 2 2000-11-21 18:50:50
follow up to 2: 4 1 2 3 3 2000-11-21 18:51:04
follow up to 2: 5 1 2 3 4 2000-11-21 18:51:06
follow up to 2: 7 1 2 3 5 2000-11-21 18:51:24
follow up to 1: 3 1 1 2 6 2000-11-21 18:50:56
follow up to 1: 6 1 1 2 7 2000-11-21 18:51:15
Thanks for the ideas... I'll start using them soon!
- wtkwest
Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/22/2000 :  14:04:11  Show Profile  Reply with Quote
Idiot Question: Transactions & Locking

Please tell me if I understand this...

In a multi-threaded environment where several connections might be calling the spPost proc, I am concerned about the results of:
SELECT @NewPostID = @@IDENTITY
...and...
UPDATE Posts
SET SortOrder = SortOrder + 1
WHERE ThreadID = @ThreadID
AND SortOrder >= @NextSortOrder

Am I correct in assuming that the transaction will lock the other connection out, so that @@IDENTITY and the @NextSortOrder values will _not_ be updated by the other connection/thread?

Alternately, if the transaction is not explicitly specified, can I assume there would be no locking, and the two threads would be simulatneously executing and corrupting the data... ie, @@IDENTITY in conn1 _could_ be the @@IDENTITY from conn2... and @NextSortOrder _could_ contain the same value in both connections...?

So, the rule might be: Anytime a proc needs exclusive data, wrap it in an explicit transaction...
Did I get it?
- wtkwest

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 11/27/2000 :  13:39:26  Show Profile  Reply with Quote
Posttime filtering?

Can this system be used if you need to be able to view threads that began inside a certain date range?

A recursive system lets the first call dictate the post time filtering reguardless of when replies where made. I don't see how the pre-sorting system can accomplish this.

-Kevin

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 12/03/2000 :  17:26:42  Show Profile  Reply with Quote
number formatting?


Much more efficient. So message 33 replying to message 17 replying to message 4 becomes:

0004
0004-0017
0004-0017-0033

(Hyphens not needed...I use them for clarity while developing and remove them in final code.)


is there a way to format a number to be exactly 4 characters long? say my ID value is 32, i want it to read 0032.

ayan

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 12/04/2000 :  00:38:08  Show Profile  Visit graz's Homepage  Reply with Quote
Incrementing a number

You could do something like this

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 12/05/2000 :  15:39:25  Show Profile  Reply with Quote
Using the 0004-0007-0012 scheme and doing time filtering...

Answering Kevin's question re post-time filtering.

In my design, I *also* kept a table of threads. Any posting to a thread updated the "last posted to" date in that table. Presto, searches by date are easy. A simple join and you're done.

The separate table for threads has several other uses and makes a lot of other things easier to do.

I guess you could do this with a single table. Just a really messy query involving joining the main table to itself. Hmmm...actually, it wouldn't be all that messy if you added a "threadID" field (which could actually just be the same as the message ID of the first message in the thread).

SELECT m1.* FROM messages AS m1
WHERE m1.threadID IN (
      SELECT DISTINCT m2.threadID FROM messages AS m2 WHERE postingTime BETWEEN xxx AND yyy
) ORDER BY messageID

Doesn't that do it? Still, that's almost surely slower than maintaining the separate threads table, isn't it? Just because of the need for the DISTINCT because of the multiple hits?

Bill Wilkinson



Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/13/2001 :  07:34:04  Show Profile  Reply with Quote
How to display number of sub messages for each message?

Is there a way to display the number of submessages for each thread?

// Mikael E.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/23/2001 :  09:46:49  Show Profile  Reply with Quote
Without SP

Hi

Is there any good way of doing this without SP ? (Due to cranky ISP)



Flea

Go to Top of Page

german
Starting Member

0 Posts

Posted - 04/27/2001 :  03:56:07  Show Profile  Reply with Quote
Why?

Why do u use stored procedures when isnt necessary???
I think u can do the same without the fields of order and level.
With that stored procedure u are making many consults to de database and i think isnt necessary, but maybe u have a good reason to do it and i want to know it.
Thanks

Go to Top of Page

EvilSyn
Starting Member

1 Posts

Posted - 02/18/2002 :  15:36:39  Show Profile  Send EvilSyn an AOL message  Reply with Quote
Hi. I recently wrote a message board using the code from this... (http://www.sqlteam.com/item.asp?ItemID=1353) and i had to write in an admin for the client to be able to edit and delete the posts... Editing was no problem, but I had a heck of a time trying to figure out how to delete a post *and* it's children. I spent the better part of all day coding this and I finally finished... So in case anyone ever needs to know how to remove children posts, here's how.

Also, I'm sure i overlooked something and there's probably a *much* simpler way to do so. If this is the case, please let me know!!! Thanks

CODE
***********************************************************

set rs = server.createobject("adodb.recordset")
sql = "SELECT ListServ_Members.Name, ListServ_Members.Email, ListServ_Posts.MemberID, ListServ_Posts.TopicID, ListServ_Posts.Subject, ListServ_Posts.PostID, ListServ_Posts.ThreadID, ListServ_Posts.ParentID, ListServ_Posts.PostLevel, ListServ_Posts.SortOrder, ListServ_Posts.PostDate from ListServ_Posts inner join ListServ_Members on ListServ_Members.id = ListServ_Posts.MemberID where ListServ_Posts.ThreadID = " & request("ThreadID") & " ORDER BY ListServ_Posts.SortOrder"

FieldList = "PostID,ParentID"
FieldsArray = split(FieldList, ",")

rs.open sql, strconn, 1, 2, 1
ThreadArray = rs.GetRows(-1, 0, FieldsArray)
rs.close
set rs = nothing

PostIDList = ""
Level = 1
PostID = cint(request("PostID"))
GetRecords ThreadArray, PostID, Level
PostIDList = PostIDList & PostID

response.write("<br><br>" & PostIDList)

function GetRecords(RecordArray, LookingFor, Level)

if level = 20 then
exit function
end if


for z = 0 to ubound(RecordArray, 2)


CurrentID = RecordArray(1, z)
CurrentParent = RecordArray(0, z)

if CurrentID = LookingFor then

'uncomment for nice debugging output
'response.write("<br>" & replace( space(Level), " ", " " ) & "Pass: " & Level & " - " & z & ", FOUND - " & CurrentID & " | GO FIND: " & CurrentParent)
PostIDList = PostIDList & CurrentParent & ","

if CurrentID <> CurrentParent then
GetRecords RecordArray, CurrentParent, Level + 1
end if

'uncomment for nice debugging output
'else

'uncomment for nice debugging output
'response.write("<br>" & replace( space(Level), " ", " " ) & "Pass: " & Level & " - " & z & ", NOT FOUND")

end if

next

end function


Go to Top of Page

xstream
Starting Member

11 Posts

Posted - 04/19/2002 :  10:15:39  Show Profile  Reply with Quote
My feeble attempt at getting this to work.

http://www.piestactics.com/about/xstream/dev/

X

Go to Top of Page

xstream
Starting Member

11 Posts

Posted - 08/30/2002 :  21:07:17  Show Profile  Reply with Quote
In the stored procedure where it says:

IF EXISTS (SELECT *
FROM Posts
WHERE PostLevel <= @ParentLevel
AND SortOrder > @ParentSortOrder
AND ThreadID = @ThreadID )
BEGIN


Shouldn't it be:

WHERE PostLevel >= @ParentLevel

If you selected all the posts in the thread that were less than the parent post level but larger than the parent sort order - wouldn't that be none of the posts?

X





Go to Top of Page

Jo-E
Starting Member

Israel
1 Posts

Posted - 03/15/2003 :  20:49:37  Show Profile  Reply with Quote
I got this working on a production site that serves many users, and reached the conclusion that there's one thing that was overlooked and has a lot to do with performance.
If you only display X root posts on every page of your discussion forum, you need to make sure the SP doesn't run on all root posts in the requested forum but only on the X posts relevant to that page, otherwise the temporary table will always contain the entire forum even though you only need several posts, which means a waste of server resources.
Another thing is if you want to avoid many deadlocks make sure you create the cursor as READ_ONLY!
This is how it looks now:

CREATE PROC GetChildren
(@ParentID int, @forumID int, @PostLevel int, @ParentPostKey varchar(200), @forumRequestedPage int, @forumMessagesPerPage int) AS

SET NOCOUNT ON

DECLARE @NextLevel int, @Counter int, @PostKey varchar(200), @jumpRows int

SET @Counter = 1

-- Build a cursor to loop through all the kids of this post based on the fact if it's a root post or not
IF @ParentID = 0 BEGIN
DECLARE c1 CURSOR LOCAL SCROLL READ_ONLY FOR
SELECT messageID
FROM messages
WHERE messageParent = @ParentID and messageForum = @forumID and deleted=0 and authorized=1
ORDER BY messageTime DESC

-- since this is a root posts query, gets only the rows that are relevant to the requested page
OPEN c1
IF @forumRequestedPage > 1
BEGIN
SET @jumpRows = ((@forumRequestedPage - 1) * @forumMessagesPerPage) + 1
FETCH ABSOLUTE @jumpRows FROM c1
into @ParentID
END
ELSE BEGIN
FETCH NEXT FROM c1
INTO @ParentID
END

-- this loop runs on all parents until there are no more on this page or at all
WHILE @@FETCH_STATUS = 0 AND @Counter <= @forumMessagesPerPage
BEGIN

-- Put this record in the temp table
INSERT #NestedPosts (PostID, PostKey, messageLevel) VALUES (@ParentID, @PostKey, @PostLevel)

SET @NextLevel = @PostLevel + 1

-- Process all the children for this post
EXEC GetChildren @ParentID, @forumID, @NextLevel, @PostKey, @forumRequestedPage, @forumMessagesPerPage

SET @Counter = @Counter + 1

-- And get the next record at this level
FETCH NEXT FROM c1
INTO @ParentID
END
END
ELSE BEGIN
DECLARE c1 CURSOR LOCAL SCROLL READ_ONLY FOR
SELECT messageID
FROM messages
WHERE messageParent = @ParentID and messageForum = @forumID and deleted=0 and authorized=1
-- Here we can put ASC so that the inner thread will be ordered by the order of insertion and not new comes first
ORDER BY messageTime DESC

-- gets all the kids for this post since it's not a root one, no need for jumping
OPEN c1
FETCH NEXT FROM c1
INTO @ParentID

WHILE @@FETCH_STATUS = 0
BEGIN

-- Put this record in the temp table
INSERT #NestedPosts (PostID, PostKey, messageLevel) VALUES (@ParentID, @PostKey, @PostLevel)

SET @NextLevel = @PostLevel + 1

-- Process all the children for this post
EXEC GetChildren @ParentID, @forumID, @NextLevel, @PostKey, @forumRequestedPage, @forumMessagesPerPage

SET @Counter = @Counter + 1

-- And get the next record at this level
FETCH NEXT FROM c1
INTO @ParentID
END
END

CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF
GO


Edited by - Jo-E on 03/15/2003 20:51:02
Go to Top of Page

famdylan
Starting Member

1 Posts

Posted - 05/17/2005 :  11:31:11  Show Profile  Reply with Quote
Okay on the same type of question say I wanted to move one of the lower replyies up to a upper level or move it to another thread how would I update all the children? Can I do this in the database or do I need to do some code in my application?
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000