LaB
2009-03-26T06:23:09Z
Hello all,

Using 1.9.3 RC2, I noticed today, that the Last Post column doesn't update when posts are made in subforums.

I looked in the Triggers.sql install file, and noticed that the trigger was commented out. Is there a new way of updating the database, or am I missing something?

/*
** Triggers
*/

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Active_insert]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Active_insert]
go

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Forum_update]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Forum_update]
go

/*
CREATE TRIGGER [{databaseOwner}].[{objectQualifier}Forum_update] ON [{databaseOwner}].[{objectQualifier}Forum] FOR UPDATE AS
BEGIN
	IF UPDATE(LastTopicID) OR UPDATE(LastMessageID)
	BEGIN	
		-- recursively update the forum
		DECLARE @ParentID int		

		SET @ParentID = (SELECT TOP 1 ParentID FROM inserted)
		
		WHILE (@ParentID IS NOT NULL)
		BEGIN
			UPDATE a SET
				a.LastPosted = b.LastPosted,
				a.LastTopicID = b.LastTopicID,
				a.LastMessageID = b.LastMessageID,
				a.LastUserID = b.LastUserID,
				a.LastUserName = b.LastUserName
			FROM
				[{databaseOwner}].[{objectQualifier}Forum]] a, inserted b
			WHERE
				a.ForumID = @ParentID AND ((a.LastPosted < b.LastPosted) OR a.LastPosted IS NULL);
			
			SET @ParentID = (SELECT ParentID FROM [{databaseOwner}].[{objectQualifier}Forum] WHERE ForumID = @ParentID)
		END
	END
END
*/
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Group_update]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Group_update]
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Group_insert]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Group_insert]
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}UserGroup_insert]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}UserGroup_insert]
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}UserGroup_delete]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}UserGroup_delete]
GO


Sponsor
LaB
2009-03-27T10:01:21Z
Am I seriously the only one having this issue?

How are updates handled, if not through the triggers that apparantly don't exist anymore?

the yaf_message_approve SP contains the code to update the forum the message is directly posted to, but not the parent forums:

-- update Forum table with last topic/post info
	update [dbo].[yaf_Forum] set
		LastPosted = @Posted,
		LastTopicID = @TopicID,
		LastMessageID = @MessageID,
		LastUserID = @UserID,
		LastUserName = @UserName
	where ForumID = @ForumID



2009-04-08T08:53:48Z
You can add me to the list. We just went 'live' with 1.9.3 RC2 and that was one of the first questions asked of me.

Any workaround or fix?
2009-04-08T11:21:11Z
I think I may have some direction on this. It appears to be working properly in my development environment, but not my production environment. I'm trying to find the difference between the two.

Development is Win2k3 with SQL 2000 SP4 sitting on one machine.

Production is (2) Win2k3 machines, one with the app, the other hosting the DB, which also is SQL 2000 SP4.
2009-04-08T11:50:13Z
OK.. Here is what I have found out so far. If the parent forum doesn't have any posts and the following columns have <NULL> values, then it won't show the most recent post of a sub-forum: LastPosted, LastTopicID, and LastMessageID. These are all in the yaf_Forum table.
2009-04-08T12:14:09Z
This has been confirmed. If the Parent forum doesn't have any posts in it, then the code jumps out and doesn't look for child forum posts.

See /controls/ForumLastPost.ascx.cs beginning at Line 52.

LaB, can you confirm that this is the same reason you are not seeing the Last Post?

Unfortunately, I'm great at tracking down problems, but not so great at actually fixing them.
LaB
2009-04-08T17:43:05Z
chriscoe71 wrote:

This has been confirmed. If the Parent forum doesn't have any posts in it, then the code jumps out and doesn't look for child forum posts.

See /controls/ForumLastPost.ascx.cs beginning at Line 52.

LaB, can you confirm that this is the same reason you are not seeing the Last Post?

Unfortunately, I'm great at tracking down problems, but not so great at actually fixing them.




Yup - same scenario here ... parent forum has no posts. There doesn't seem to be anything wrong with the code logic though,as the trigger updating the parent forums has been commented out of the install scripts (I'm assuming it wasn't finished, as if I uncomment it and fix the syntax errors, the database goes into an infinite loop), and thus there never will be anything other than null values in the LastPosted column for that forum.
2009-04-08T18:12:09Z
I could be totally wrong here, but I don't think that adding a post to a sub-forum is supposed to update the 'LastPosted' column in the database for the parent forum.

I believe that it checks the LastPosted column of all parent forums first and if they have a NULL value in that column, then it doesn't check the child forums.

Here's why I believe that. For all of my forums that have sub-forums with posts, I put in a dummy date on the 'LastPosted' column for the parent forum. The dummy date was way in the past, something like 1/1/2005 12:00:00 PM.

Once I put that into the database, the site started displaying the sub-forum posts properly on the main page. There are no posts in the parent forum and it appears to be working properly.
LaB
2009-04-09T03:29:59Z
You are correct - I just verified that behaviour on my forum as well:

Subforum posts are only updated on the main page if the parent forum has posts or you manually put a value in the "LastPosted" column of that parent forum
2009-04-11T11:24:15Z
LaB wrote:

You are correct - I just verified that behaviour on my forum as well:

Subforum posts are only updated on the main page if the parent forum has posts or you manually put a value in the "LastPosted" column of that parent forum



I think that you were closer than I was. I finally had some time to dig into this and may have a solution.

I'm definitely NOT an SQL guru, so hopefully someone will check this over.

I'm using SQL Express 2005 and here is what I changed:

In the Database, under Programmability / Functions / Scalar-valued Functions, I altered the yaf_forum_lasttopic function.

Here is the new version:
-- if subforum has newer topic/message, make it last for parent forum
if (@TopicID is not null and @Posted is not null and (@LastPosted < @Posted OR (@LastPosted is null and @Posted is not null ) )) begin

Basically, it adds another qualifier onto the expression, just in case the parent is NULL, but the child is not.. I don't know for sure, but I think it may have had a hard time comparing a NULL value to a valid date..

That appears to have fixed it for me, but I would have an expert check it out before throwing it up in production..

Here is the full function
USE [yourforumdb]
GO
/****** Object:  UserDefinedFunction [dbo].[yaf_forum_lasttopic]    Script Date: 04/11/2009 14:38:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[yaf_forum_lasttopic] 

(	
	@ForumID int,
	@UserID int = null,
	@LastTopicID int = null,
	@LastPosted datetime = null
) RETURNS int AS
BEGIN
	-- local variables for temporary values
	declare @SubforumID int
	declare @TopicID int
	declare @Posted datetime

	-- try to retrieve last direct topic posed in forums if not supplied as argument 
	if (@LastTopicID is null or @LastPosted is null) begin
		SELECT 
			@LastTopicID=a.LastTopicID,
			@LastPosted=a.LastPosted
		FROM
			[dbo].[yaf_Forum] a
			JOIN [dbo].[yaf_vaccess] x ON a.ForumID=x.ForumID
		WHERE
			a.ForumID=@ForumID and
			(
				(@UserID is null and (a.Flags & 2)=0) or 
				(x.UserID=@UserID and ((a.Flags & 2)=0 or x.ReadAccess<>0))
			)
	end

	-- look for newer topic/message in subforums
	if exists(select 1 from [dbo].[yaf_Forum] where ParentID=@ForumID)

	begin
		declare c cursor for
			SELECT 
				a.ForumID,
				a.LastTopicID,
				a.LastPosted
			FROM
				[dbo].[yaf_Forum] a
				JOIN [dbo].[yaf_vaccess] x ON a.ForumID=x.ForumID
			WHERE
				a.ParentID=@ForumID and
				(
					(@UserID is null and (a.Flags & 2)=0) or 
					(x.UserID=@UserID and ((a.Flags & 2)=0 or x.ReadAccess<>0))
				)
			
		open c
		
		-- cycle through subforums
		fetch next from c into @SubforumID, @TopicID, @Posted
		while @@FETCH_STATUS = 0
		begin
			-- get last topic/message info for subforum
			SELECT 
				@TopicID = LastTopicID,
				@Posted = LastPosted
			FROM
				[dbo].[yaf_forum_lastposted](@SubforumID, @UserID, @TopicID, @Posted)


			-- if subforum has newer topic/message, make it last for parent forum
			if (@TopicID is not null and @Posted is not null and (@LastPosted < @Posted OR (@LastPosted is null and @Posted is not null ) )) begin
				SET @LastTopicID = @TopicID
				SET @LastPosted = @Posted
			end

			fetch next from c into @SubforumID, @TopicID, @Posted
		end
		close c
		deallocate c
	end

	-- return id of topic with last message in this forum or its subforums
	RETURN @LastTopicID
END

2009-04-11T17:12:49Z
I had the chance to do some testing and the code changes I presented will only work one level deep. If you have a subforum of a subforum, the post is not reflected at the top level. Back to the drawing board.. :?
2009-04-11T18:03:17Z
Thanks for all your work guys... I'll take a look at a solution.
2009-05-06T07:07:21Z
Jaben wrote:

Thanks for all your work guys... I'll take a look at a solution.



Hi Jaben,
I'm still having the same problem with YAF1.9.3 production version. What should I do?
2009-05-06T07:47:18Z
Wait for a released solution 😉 Or code one yourself and post ;-)

FP:Mek.

UserPostedImage 

"It's a case of RTFM.. the only problem being we don't have a manual!"

When I post FP:Mek in a topic, I'm leaving my footprint there so I can track it once I get into coding/supporting. (Yes I stole this off Ederon 🙂 )
2009-05-07T03:36:53Z
Mek wrote:

Wait for a released solution 😉 Or code one yourself and post ;-)

FP:Mek.



You must be kidding, he he. Unfortunately I'm not a coder. I should be patient.
I appreciate your work, guys!

About Us

The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

Powered by Resharper Donate with PayPal button

Project Twitter Updates

Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved