aspnetforums.sql

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

		ModerationAudit
	VALUES
		(GetDate(), @PostID, @ApprovedBy, 1, NULL)

	-- Update the forums statistics
	exec Statistics_ResetForumStatistics @ForumID

	-- Are we updating the status of a user?
 	IF (@Trusted IS NOT NULL)
	BEGIN

		-- Mark the user as trusted
		UPDATE
			Users
		SET
			Trusted = 1
		WHERE
			Username = @Trusted

		-- Update the ModerationAudit table
		INSERT INTO
			ModerationAudit
		VALUES
			(GetDate(), @PostID, @ApprovedBy, 5, NULL)

	END

	-- Send back a success code
	SELECT 1
END








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE     PROCEDURE sp_ApprovePost
(
	@PostID	int
)
 AS
DECLARE @ForumID int
DECLARE @ThreadID int

	-- first make sure that the post is ALREADY non-approved
	IF (SELECT Approved FROM Posts (nolock) WHERE PostID = @PostID) = 1
		-- its already been approved, return 0
		SELECT 0
	ELSE
	  BEGIN
		-- Approve the post
		UPDATE 
			Posts
		SET 
			Approved = 1
		WHERE 
			PostID = @PostID

		-- Get details about the thread and forum this post belongs in
		SELECT
			@ForumID = ForumID,
			@ThreadID = ThreadID
		FROM
			Posts
		WHERE
			PostID = @PostID

		-- Update the thread date
		UPDATE 
			Posts
		SET 
			ThreadDate = getdate()
		WHERE 
			ThreadID = @ThreadID

		-- Update the ModerationAudit table
		INSERT INTO
			ModerationAudit
		VALUES
			(GetDate(), @PostID, 'undone', 1, NULL)

		-- Update the forums statistics
		exec Statistics_ResetForumStatistics @ForumID

		-- Send back a success code
		SELECT 1
	  END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure sp_CanModerate 
(
	@UserName	nvarchar(50)
)
AS
	-- determine whether or not this user can moderate
	IF EXISTS(SELECT NULL FROM Moderators (nolock) WHERE UserName = @UserName)
		SELECT CanModerate = 1
	ELSE
		SELECT CanModerate = 0












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create  PROCEDURE sp_CanModerateForum 
(
	@UserName	nvarchar(50),
	@ForumID	int
)
AS


IF EXISTS(SELECT ForumID FROM Moderators WHERE Username = @UserName AND ForumID = 0)
  SELECT CanModerate = 1
ELSE
  IF EXISTS (SELECT ForumID FROM Moderators WHERE Username = @UserName AND ForumID = @ForumID)
    SELECT CanModerate = 1
  ELSE
    SELECT CanModerate = 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create procedure sp_ChangeForumGroupSortOrder
(
     @forumGroupID int,
     @MoveUp bit
)
AS
BEGIN
DECLARE @currentSortValue int
DECLARE @replaceSortValue int

-- Get the current sort order
SELECT @currentSortValue = SortOrder FROM ForumGroups WHERE ForumGroupID = @forumGroupID

-- Move the item up or down?
IF (@MoveUp = 1)
  BEGIN
    IF (@currentSortValue != 1)
      BEGIN
        SET @replaceSortValue = @currentSortValue - 1

        UPDATE ForumGroups SET SortOrder = @currentSortValue WHERE SortOrder = @replaceSortValue
        UPDATE ForumGroups SET SortOrder = @replaceSortValue WHERE ForumGroupID = @forumGroupID
      END
  END

ELSE
  BEGIN
    IF (@currentSortValue < (SELECT MAX(ForumGroupID) FROM ForumGroups))
    BEGIN
      SET @replaceSortValue = @currentSortValue + 1

      UPDATE ForumGroups SET SortOrder = @currentSortValue WHERE SortOrder = @replaceSortValue
      UPDATE ForumGroups SET SortOrder = @replaceSortValue WHERE ForumGroupID = @forumGroupID
    END
  END
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create  PROCEDURE sp_ChangeUserPassword
(
	@UserName nvarchar(50),
	@NewPassword nvarchar(50)
)
AS

UPDATE
	Users
SET
	Password = @NewPassword
WHERE
	Username = @UserName




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure sp_CheckUserCredentials
(
	@UserName nvarchar(50),
	@Password nvarchar(20)
)
 AS
	IF EXISTS(SELECT UserName FROM Users WHERE UserName = @UserName AND Password = @Password AND Approved=1)
	BEGIN
		-- update the time the user last logged in
		UPDATE Users
			SET LastLogin = getdate()
		WHERE UserName = @UserName
		SELECT 1
	END
	ELSE
		SELECT 0












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure sp_CreateNewUser
(
	@UserName nvarchar(50),
	@Email nvarchar(75),
	@RandomPassword nvarchar(20)
)
 AS
	-- this sproc returns various error/success codes
		-- a return value of 1 means success
		-- a return value of 2 means a dup username
		-- a return value of 3 means a dup email address
	-- first, we need to check if the username is a dup
	IF EXISTS(SELECT Username FROM Users (nolock) WHERE Username = @UserName)
		SELECT 2
	ELSE
		-- we need to check if the email is a dup
		IF EXISTS(SELECT Email FROM Users (nolock) WHERE Email = @Email)
			SELECT 3
		ELSE
			-- everything's peachy if we get this far - insert the user
		  BEGIN
			INSERT INTO Users (UserName, Email, Password)
			VALUES(@UserName, @Email, @RandomPassword)
			SELECT 1	-- return Everything's fine status code
		  END












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure sp_DeleteForum
(
	@ForumID	int
)
 AS
	-- delete the specified forum and all of its posts
	-- first we must remove all the thread tracking rows
	DELETE FROM ThreadTrackings
	WHERE ThreadID IN (SELECT DISTINCT ThreadID FROM Posts WHERE ForumID = @ForumID)
	-- we must remove all of the moderators for this forum
	DELETE FROM Moderators
	WHERE ForumID = @ForumID
	-- now we must remove all of the posts
	DELETE FROM Posts
	WHERE ForumID = @ForumID
	-- finally we can delete the actual forum
	DELETE FROM Forums
	WHERE ForumID = @ForumID












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE     PROCEDURE sp_DeleteModeratedPost
(
	@PostID	int,
	@ApprovedBy nvarchar(50),
	@Reason nvarchar(1024)
) AS
	-- we must delete all of the posts and replies
	-- first things first, determine if this is the parent of the thread
	DECLARE @ThreadID int
	DECLARE @ForumID int
	DECLARE @UserName nvarchar(50)

	SELECT @ThreadID = ThreadID, @ForumID = ForumID, @UserName = Username FROM Posts (nolock) WHERE PostID = @PostID
	IF @ThreadID = @PostID
	  BEGIN
		-- we are dealing with the parent fo the thread
		-- delete all of the thread tracking
		DELETE 
			ThreadTrackings
		WHERE 
			ThreadID = @ThreadID

		-- Delete the entire thread
		DELETE 
			Posts
		WHERE 
			ThreadID = @ThreadID

		-- Clean up the forum statistics
		exec Statistics_ResetForumStatistics @ForumID

		-- Update users table to decrement post count for this user
		UPDATE
			Users
		SET 
			TotalPosts = (TotalPosts - 1)
		WHERE
			Username = @UserName

		-- Record to our moderation audit log
		INSERT INTO
			ModerationAudit
		VALUES
			(GetDate(), @PostID, @ApprovedBy, 4, @Reason)

	  END
	ELSE
		-- we must recursively delete this post and all of its children
		EXEC sp_DeletePostAndChildren @PostID












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NU