aspnetforums.sql

微软的.NET论坛的源代码（COOL!!!）

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetThreadByParentID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetThreadByParentID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetThreadByPostID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetThreadByPostID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetThreadByPostIDPaged]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetThreadByPostIDPaged]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTimezoneByUsername]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTimezoneByUsername]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTop25NewPosts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTop25NewPosts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTopicsUserIsTracking]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTopicsUserIsTracking]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTopicsUserMostRecentlyParticipatedIn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTopicsUserMostRecentlyParticipatedIn]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTotalNumberOfForums]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTotalNumberOfForums]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTotalPostCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTotalPostCount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTotalPostsForThread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTotalPostsForThread]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTotalUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTotalUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetTrackingEmailsForThread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetTrackingEmailsForThread]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserGroups]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserGroups]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserNameFromPostID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserNameFromPostID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUsernameByEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUsernameByEmail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUsersByFirstCharacter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUsersByFirstCharacter]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUsersOnline]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUsersOnline]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_IsDuplicatePost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_IsDuplicatePost]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarkAllTopicsRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MarkAllTopicsRead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MarkPostAsRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MarkPostAsRead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MovePost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MovePost]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RemoveModeratedForumForUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RemoveModeratedForumForUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ReverseTrackingOption]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ReverseTrackingOption]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ToggleOptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ToggleOptions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_TopicCountForForum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_TopicCountForForum]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_TrackAnonymousUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_TrackAnonymousUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UnbanUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UnbanUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateEmailTemplate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateEmailTemplate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateForum]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateForum]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateForumGroup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateForumGroup]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateMessageTemplateList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateMessageTemplateList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdatePost]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdatePost]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateUserFromAdminPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateUserFromAdminPage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateUserInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateUserInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserHasPostsAwaitingModeration]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UserHasPostsAwaitingModeration]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Maintenance_CleanForumsRead
(
	@ForumID int
)
AS
BEGIN
	DELETE
		ForumsRead
	WHERE
		MarkReadAfter = 0 AND
		ForumID = @ForumID
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Maintenance_ResetForumGroupsForInsert
AS
BEGIN
DECLARE @SortOrderCount int
DECLARE @ForumGroupID int
DECLARE @SortOrder int

-- Set our seed value
SET @SortOrderCount = 1

-- Use a temp table so we don't get duplicate values
create table #SortForumGroups (
	ForumGroupID int,
	SortOrder int
)

-- Push data into temp table
INSERT INTO #SortForumGroups
SELECT ForumGroupID, SortOrder FROM ForumGroups ORDER BY sortorder

-- Get the lowest value
SELECT TOP 1 @SortOrder = SortOrder, @ForumGroupID = ForumGroupID FROM #SortForumGroups WHERE SortOrder >= 0 ORDER BY SortOrder

WHILE @SortOrderCount < (SELECT count(*) FROM ForumGroups)
BEGIN

  -- Update the forum groups table
  UPDATE ForumGroups SET SortOrder = @SortOrderCount WHERE ForumGroupID = @ForumGroupID

  -- increment our count
  SET @SortOrderCount = @SortOrderCount + 1

  -- Get the next forumgroupid to modify
  SELECT TOP 1 @SortOrder = SortOrder, @ForumGroupID = ForumGroupID FROM #SortForumGroups WHERE SortOrder > @SortOrder ORDER BY SortOrder
END

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE  procedure Reports_UserVisitsByDay
(
	@DaysBack int,
	@SumDayCount bit
)
AS
DECLARE @UserCount int
DECLARE @PostCount int
DECLARE @AspNetTeamPostCount int
DECLARE @AveragePostPerUser decimal(5,2)
DECLARE @PercentagePostsAspNetTeam decimal(5,2)
DECLARE @ForumStartDate datetime

IF (@DaysBack > 0)
BEGIN
	SELECT TOP 1 @ForumStartDate = PostDate FROM Posts ORDER BY PostDate

	SET @DaysBack = DATEDIFF(d, GetDate(), @ForumStartDate)
END

-- Create a temporary table to insert results into
CREATE Table #UserVisitsByDay (
	StatDate datetime,
	UserCount int,
	PostCount int,
	AvgPostPerUser decimal(5,2),
	PostCountAspNetTeam int,
	PercentagePostsAspNetTeam decimal(5,2)
)


-- Do for each day
WHILE (@DaysBack <= 0)
BEGIN
	IF (@SumDayCount = 1) AND (@DaysBack < -1)
	BEGIN
		-- Users visited in last day
		SELECT @UserCount = Count(*) FROM Users WHERE DatePart(dy, LastActivity) >= DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, LastActivity) >= DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))
		
		-- Users posted in last day
		SELECT @PostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) >= DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) >= DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))

                -- ASPNet Team post count
		SELECT @AspNetTeamPostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) >= DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) >= DatePart(yy, DateAdd(dy, @DaysBack, GetDate())) AND Username IN (SELECT Username FROM UsersInRoles WHERE RoleName = 'AspNetTeam')
	END
	ELSE
	BEGIN
		-- Users visited in last day
		SELECT @UserCount = Count(*) FROM Users WHERE DatePart(dy, LastActivity) = DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, LastActivity) = DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))
		
		-- Users posted in last day
		SELECT @PostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) = DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) = DatePart(yy, DateAdd(dy, @DaysBack, GetDate()))

                -- ASPNet Team post count
		SELECT @AspNetTeamPostCount = Count(*) FROM Posts WHERE DatePart(dy, PostDate) = DatePart(dy, DateAdd(dy, @DaysBack, GetDate())) AND DatePart(yy, PostDate) = DatePart(yy, DateAdd(dy, @DaysBack, GetDate())) AND Username IN (SELECT Username FROM UsersInRoles WHERE RoleName = 'AspNetTeam')
	END
	
	-- Calculate avg. post/user
	SET @AveragePostPerUser = CAST(@PostCount as decimal) / CAST(@UserCount as decimal)

	-- Calculate avg. post/user from ASP.NET Team
	SET @PercentagePostsAspNetTeam = CAST(@AspNetTeamPostCount as decimal) / CAST(@PostCount as decimal)

	INSERT INTO 
		#UserVisitsByDay 
	VALUES (
		DATEADD(d, @DaysBack, GetDate()), 
		@UserCount, 
		@PostCount, 
		@AveragePostPerUser,
		@AspNetTeamPostCount,
		@PercentagePostsAspNetTeam
		)

	SET @DaysBack = @DaysBack + 1
END


SELECT 
	StatDate,
	UserCount,
	PostCount,
	AvgPostPerUser,
	PostCountAspNetTeam,
	PercentagePostsAspNetTeam
FROM
	#UserVisitsByDay
ORDER BY
	StatDate DESC





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Statistics_GetModerationActions
AS

SELECT DISTINCT
	Description, TotalActions = (SELECT Count(ModerationAction) FROM ModerationAudit M2 WHERE M2.ModerationAction = M.ModerationAction) 
FROM 
	ModerationAudit M, 
	ModerationAction A 
WHERE 
	M.ModerationAction = A.ModerationAction 
ORDER BY 
	TotalActions DESC 





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Statistics_GetMostActiveModerators
AS

SELECT DISTINCT TOP 10
	Username = ModeratedBy, 
	PostsModerated = (SELECT Count(ModerationAction) FROM ModerationAudit M2 WHERE M2.ModeratedBy = M.ModeratedBy)  
FROM 
	ModerationAudit M 
ORDER BY 
	PostsModerated DESC





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   procedure Statistics_GetMostActiveUsers
AS
SELECT TOP 3
	Username,
	TotalPosts
FROM
	Users
WHERE
	Username NOT IN (SELECT Username FROM UsersInRoles WHERE Rolename = 'AspNetTeam')
ORDER BY
	TotalPosts DESC





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE  procedure Statistics_ResetForumStatistics
(
@ForumID int
)
AS
BEGIN
DECLARE @ForumCount int
DECLARE @ThreadID int
DECLARE @PostID int
set @ForumCount = 1


IF @ForumID = 0
  WHILE @ForumCount < (SELECT Max(ForumID) FROM FORUMS)
  BEGIN


	IF EXISTS(SELECT ForumID FROM Forums WHERE ForumID = @ForumCount)
	BEGIN
		SELECT TOP 1 @ThreadID = ThreadID, @PostID = PostID FROM Posts WHERE ForumID = @ForumCount and Approved = 1 ORDER BY PostID DESC
		IF @ThreadID IS NOT NULL
			exec Statistics_UpdateForumStatistics @ForumCount, @ThreadID, @PostID
	END

	SET @ForumCount = @ForumCount + 1
	SET @ThreadID = NULL


  END
ELSE
	SELECT TOP 1 @ThreadID = ThreadID, @PostID = PostID FROM Posts WHERE ForumID = @ForumID and Approved = 1 ORDER BY PostID DESC
	exec Statistics_UpdateForumStatistics @ForumID, @ThreadID, @PostID
END




GO