View Single Post
  #76 (permalink)  
Old September 20th, 2007, 03:43 AM
jimibt jimibt is offline
Friend of Wrox
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts


i don't think supports generics, so you may not be able to use the little trick that i did above, which is a shame as this gives a lot of possibilities to create functions on the fly (JIT) which would require a lot of logic (and looping) to achieve otherwise.

i did start on the new post / old post idea a while back and got a version 0.5 working failry well. however, more pressing issues (both here and on other things) took over and i never quite finished the idea. basically, what i did was:

1. create a table called UserViewPosts

CREATE TABLE [dbo].[UserViewPosts](
    [UserID] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ParentPostID] [int] NOT NULL,
    [UserID] ASC,
    [ParentPostID] ASC

2. i then amended the tbh_Forums_IncrementViewCount procedure adding @ParentPostID and @UserID paramaters.

3. when the IncrementViewCount sql is run, it also adds a row in the UserViewPosts table.

4. in tbh_Forums_GetForums, add an outer join onto the UserViewPosts table (on ParentPostID) and add a virtual column (isNew) to indicate whether the thread has had any new entires since you last read it. (i.e. if the row doesn't exist and returns a null, then the virtual column would have a value of true. if the row brings back data, then the isNew vlaue would be set to false)

5. when a new post is added or an old one amended, then delete all rows from the UserViewPosts table based on the ParentPostID of the thread.

That was as far as i got. the caveats of this approach being that the cacheing would have to be re-thought to accomodate the user by user 'view' on the forum/thread list. this is why i say i'd got to version 0.5 as this is quite a big area in itself. however, i think a 'generic' discrete user session/cache 'addition' might be quite useful for other things that need to view global data but apply user additions over the top of it.

let me know what you think of the general approach that i had taken.