| Author |
 Topic  |
|
Spooky
Development Team Member
New Zealand
320 Posts |
 Posted - 08 January 2002 : 04:12:43
|
This is what I am considering.
I want to keep well away from rs if I can, and just getrows it into an array. No sorting, nothing :)
This parts running quite happily --->
CREATE PROCEDURE spPost (@ReplyToID int, @Subject char(20), @ReplyType int )
AS
DECLARE @ThreadID varchar(255)
DECLARE @NewPostID int
BEGIN TRAN
IF @ReplyToID = 1 -- New Topic
BEGIN
INSERT FORUM_TOPICS (ThreadID, T_Subject, T_Date ) VALUES (0, @Subject, getdate())
SELECT @NewPostID = @@IDENTITY
UPDATE FORUM_TOPICS
SET ThreadID = RIGHT('00000' + convert(varchar, @NewPostID), 6)
WHERE Topic_ID = @NewPostID
END
ELSE -- @ReplyToID = 0 means reply to an existing post
BEGIN
IF @ReplyType = 1 -- Replying to original post - get the ThreadID
SELECT @ThreadID = ThreadID
FROM FORUM_TOPICS
WHERE TOPIC_ID = @ReplyToID
ELSE -- Replying to Child post
SELECT @ThreadID = convert(varchar,ThreadID)
FROM FORUM_REPLY
WHERE REPLY_ID = @ReplyToID
INSERT FORUM_REPLY (R_Subject, R_Date )
VALUES (@Subject, getdate() )
SELECT @NewPostID = @@IDENTITY
UPDATE FORUM_REPLY
SET ThreadID = @ThreadID + RIGHT('00000' + convert(varchar,@NewPostID),6)
WHERE REPLY_ID = @NewPostID
END
COMMIT TRAN
Calling the sp for all posts will dump the correct threadID
Now, the same theory will apply for Access (and I assume mySQL?) as the query would need to retrieve the Autonumber.
The way I look at it, its best to do the slow query when posting rather than fetching.
I guess time will tell whether its faster, as theres still much work to do :)
It gives a result :
ThreadID
===========
000014000099
000014000099000101
000014000099000102
000014000099000103
000014000100
000014000100000104
000014000100000104000109
000014000100000104000110
000014000100000104000110000111
000014000100000105
000014000100000106
000014000100000106000107
000014000100000108
000015000112
===============
www.outfront.net
Edited by - Spooky on 08 January 2002 05:16:54 |
 |
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 08 January 2002 : 09:13:17
|
You guys are jumping into a topic that's hard to do. Storing nested threads in SQL Server is a pain! Here's all the articles I've found on the subject: http://www.sqlteam.com/SearchResults.asp?SearchTerms=tree+hierarch.
I'd especially look at one by Joe Celko (http://www.intelligententerprise.com/001020/celko1_1.shtml).
If you go search the SQLTeam forums on tree or hierarchy you will find previous discussions on this. Also, you may want to post in the Developer forum and ask if anyone else has written something like this that runs in production.
I think the key is to store the information "pre-sorted". Trying to format it as it comes out is a pain. There are many more views than posts in most forums so spending extra time to format at the point that it's posted seems to make sense.
I've always liked the approach of storing the tree view (12345-0001-00001-etc). I wouldn't put the top level field into this structure though. I'd pull the 12345 out into an int field and just store the tree for child threads. Second, rather than using 0-9 for each digit I'd use 0-Z. That will give you 36 values for each digit or 1,296 values for a two "digit" field or 46,656 values for a three "digit" field. I wrote an article on incrementing a number inside a character value here (http://www.sqlteam.com/item.asp?ItemID=1417) but you'd probably be best to do that in ASP.
Another approach I've always considered which I think is what Celko suggests is building a b-tree as you query the records. Basically doing a bubble sort as the records come out.
Google turns up some interesting articles on the subject also (http://www.google.com/search?q=expanding+hierarchies+in+SQL+Server).
SQLTeam.com - For SQL Server Developers and Administrators |
|
|
|
xstream
Junior Member
242 Posts |
Posted - 08 January 2002 : 11:08:40
|
I haven't thought this through much, but I think I saw it somewhere else. Wouldn't this whole process be simpler by just using a threadID field and a replyTO field. If the post is a new topic the replyTO field could just = 0 . If it is a reply to a post, it would just have that posts threadID number in the field. Would that make sense?
Sorry if I sound really newbish. I am. Please be gentle with me. =)
X
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 08 January 2002 : 11:41:05
|
That is what exactly I am doing. If the reply is to topic then thread id is set to 0 (Default for thread id is 0).
If it is reply to a Reply (i.e. Reply with quote) then I store the reply ID as thread ID.
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
|
xstream
Junior Member
242 Posts |
Posted - 08 January 2002 : 12:31:10
|
Do you have the complete code that you are using for that page?
I would like to test it out on mine. And what have you added to the database?
X
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 08 January 2002 : 13:01:21
|
It is base on version 3.2 with lot of modifications. If you are interested I can post a link. But threading is still not complete and accurate.
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
|
xstream
Junior Member
242 Posts |
Posted - 08 January 2002 : 22:13:15
|
I'm interested. =)
X
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 08 January 2002 : 23:04:01
|
Okay Get it Here
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
|
xstream
Junior Member
242 Posts |
Posted - 09 January 2002 : 07:17:02
|
Were any fields added to the database?
I also need the inc_paging.asp page.....
X
Edited by - xstream on 09 January 2002 07:27:03 |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 09 January 2002 : 11:34:09
|
Inc_paging will not be used for threaded display. You can take out that statement. Added THREAD_ID to the table FORUM_REPLY
www.forumSquare.com - GauravBhabu - It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 19 January 2002 : 01:34:46
|
Oh, couldn't resist getting into this topic either. 
Back in June 2001 I was working on something like this.
Example of Threaded Snitz
I did get it working (granted there are a few minor bugs), had some tweaking to do and that's where I left off. At the time, I was expecting another version of the forum to be released so I never picked it back up.
The above example will not be around too much longer 'cause I canceled the account, but while it's still active, this instance it's working out of an Access Database. No SQL Server, no stored procs, no temporary tables, no fancy queries. There were a couple of fields I had to add and when a reply was posted, there was an additional query I had to make.
After briefly looking through the code and thinking 'bout what I've learned, I could probably optimize what was written and dump the main list of replies directly to an array.
|
|
|
|
Spooky
Development Team Member
New Zealand
320 Posts |
Posted - 19 January 2002 : 06:24:58
|
Im just messing with the tree structure now.
Man theres a lot of work to get all the tree images in the right place - but its smokin :)
===============
www.frontpagewebmaster.com |
|
|
|
xstream
Junior Member
242 Posts |
Posted - 19 January 2002 : 09:41:07
|
Work mule and Spooky. Are you guys going to share with us what you have? I'm new here so I don't know how much you guys share...hehe... I would like to begin taking a look also.
X
|
|
|
|
Spooky
Development Team Member
New Zealand
320 Posts |
Posted - 20 January 2002 : 07:14:47
|
xstream - will do (If I get that far!)
Theres a few other posted examples here too that are working.
Im just taking a different (hopefully faster!) approach.
But, Im just doing it for the hell of it, so it might die quietly if I get bored :)
===============
www.frontpagewebmaster.com |
|
|
|
Spooky
Development Team Member
New Zealand
320 Posts |
Posted - 20 January 2002 : 07:20:17
|
HAs anyone got a link to a nice collapse / expand script?
Ive got one I normally use thats only IE compatible, what others are out there that may be of use. Image swap onclick will be nice too :)
===============
www.frontpagewebmaster.com |
|
|
|
Topic |
|
Topic Locked
