aspnetforums.sql

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

	CONSTRAINT [DF_Users_Trusted] DEFAULT (0) FOR [Trusted],
	CONSTRAINT [DF_Users_FakeEmail] DEFAULT ('') FOR [FakeEmail],
	CONSTRAINT [DF_Users_URL] DEFAULT ('') FOR [URL],
	CONSTRAINT [DF_Users_Signature] DEFAULT ('') FOR [Signature],
	CONSTRAINT [DF_Users_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
	CONSTRAINT [DF_Users_TrackYourPosts] DEFAULT (0) FOR [TrackYourPosts],
	CONSTRAINT [DF_Users_LastLogin] DEFAULT (getdate()) FOR [LastLogin],
	CONSTRAINT [DF_Users_LastActivity] DEFAULT (getdate()) FOR [LastActivity],
	CONSTRAINT [DF_Users_TimeZone] DEFAULT ((-5)) FOR [TimeZone],
	CONSTRAINT [DF_Users_Location] DEFAULT ('') FOR [Location],
	CONSTRAINT [DF_Users_Occupation] DEFAULT ('') FOR [Occupation],
	CONSTRAINT [DF_Users_Interests] DEFAULT ('') FOR [Interests],
	CONSTRAINT [DF_Users_MSN] DEFAULT ('') FOR [MSN],
	CONSTRAINT [DF_Users_Yahoo] DEFAULT ('') FOR [Yahoo],
	CONSTRAINT [DF_Users_AIM] DEFAULT ('') FOR [AIM],
	CONSTRAINT [DF_Users_ICQ] DEFAULT ('') FOR [ICQ],
	CONSTRAINT [DF_Users_TotalPosts] DEFAULT (0) FOR [TotalPosts],
	CONSTRAINT [DF_Users_HasIcon] DEFAULT (0) FOR [HasIcon],
	CONSTRAINT [DF_Users_ShowUnreadTopicsOnly] DEFAULT (0) FOR [ShowUnreadTopicsOnly],
	CONSTRAINT [DF_Users_Style_1] DEFAULT (N'default') FOR [Style],
	CONSTRAINT [DF_Users_ImageType] DEFAULT (N'gif') FOR [ImageType],
	CONSTRAINT [DF_Users_ShowIcon] DEFAULT (0) FOR [ShowIcon],
	CONSTRAINT [DF_Users_DateFormat] DEFAULT (N'MM-dd-yyyy') FOR [DateFormat],
	CONSTRAINT [DF_Users_PostViewOrder] DEFAULT (0) FOR [PostViewOrder],
	CONSTRAINT [DF_Users_FlatView] DEFAULT (1) FOR [FlatView],
	CONSTRAINT [DF_Users_DisplayInMemberList] DEFAULT (1) FOR [DisplayInMemberList],
	CONSTRAINT [IX_Users_UniqueEmail] UNIQUE  NONCLUSTERED 
	(
		[Email]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Forums_Active] ON [dbo].[Forums]([Active]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_ForumsReadByDate] ON [dbo].[ForumsRead]([ForumId]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_ForumsReadByDate_1] ON [dbo].[ForumsRead]([Username]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_ParentID] ON [dbo].[Posts]([ParentID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_ThreadID] ON [dbo].[Posts]([ThreadID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_SortOrder] ON [dbo].[Posts]([SortOrder]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_PostLevel] ON [dbo].[Posts]([PostLevel]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_Approved] ON [dbo].[Posts]([Approved]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_ForumID] ON [dbo].[Posts]([ForumID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Posts_Username] ON [dbo].[Posts]([UserName]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PostsRead] ON [dbo].[PostsRead]([PostId]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PostsRead_1] ON [dbo].[PostsRead]([Username]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_PrivateForums] ON [dbo].[PrivateForums]([ForumId]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_UsersInRoles] ON [dbo].[UsersInRoles]([Username]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_UsersInRoles_1] ON [dbo].[UsersInRoles]([Rolename]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Moderators] ADD 
	CONSTRAINT [FK_Moderators_Users] FOREIGN KEY 
	(
		[UserName]
	) REFERENCES [dbo].[Users] (
		[UserName]
	)
GO

ALTER TABLE [dbo].[Posts] ADD 
	CONSTRAINT [FK_Posts_Forums] FOREIGN KEY 
	(
		[ForumID]
	) REFERENCES [dbo].[Forums] (
		[ForumID]
	),
	CONSTRAINT [FK_Posts_Users] FOREIGN KEY 
	(
		[UserName]
	) REFERENCES [dbo].[Users] (
		[UserName]
	)
GO

ALTER TABLE [dbo].[PrivateForums] ADD 
	CONSTRAINT [FK_PrivateForums_UserRoles] FOREIGN KEY 
	(
		[RoleName]
	) REFERENCES [dbo].[UserRoles] (
		[RoleName]
	)
GO

ALTER TABLE [dbo].[ThreadTrackings] ADD 
	CONSTRAINT [FK_ThreadTrackings_Users] FOREIGN KEY 
	(
		[UserName]
	) REFERENCES [dbo].[Users] (
		[UserName]
	)
GO

ALTER TABLE [dbo].[UsersInRoles] ADD 
	CONSTRAINT [FK_UsersInRoles_UserRoles] FOREIGN KEY 
	(
		[Rolename]
	) REFERENCES [dbo].[UserRoles] (
		[RoleName]
	),
	CONSTRAINT [FK_UsersInRoles_Users] FOREIGN KEY 
	(
		[Username]
	) REFERENCES [dbo].[Users] (
		[UserName]
	)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HasReadPost]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HasReadPost]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





CREATE  function HasReadPost(@UserName nvarchar(50), @PostID int, @ForumID int)
RETURNS bit
AS
BEGIN
DECLARE @HasRead bit
DECLARE @ReadAfter int
SET @HasRead = 0


	-- Do we have topics marked as read?
	SELECT @ReadAfter = MarkReadAfter FROM ForumsRead WHERE Username = @UserName AND ForumID = @ForumID

	IF @ReadAfter IS NOT NULL
	BEGIN
		IF @ReadAfter > @PostID
			RETURN 1
	END
	
	
	IF EXISTS (SELECT HasRead FROM PostsRead WHERE Username = @UserName AND PostID = @PostID)
	  SET @HasRead = 1

RETURN @HasRead
END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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