2005_4_2.sql

解压即可使用

/***********************************************
* Generated at 4/2/2005 7:36:27 AM
***********************************************/


/***********************************************
* SP: cs_Audit_Post
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_Audit_Post'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE proc [dbo].cs_Audit_Post
(
	@PostID		int,
	@SettingsID 	int
)
AS

	-- Get the Post
	exec cs_forums_Post @PostID, null, 0, @SettingsID

	SELECT
		B.Description,
		U.Username,
		A.*
	FROM
		cs_ModerationAudit A,
		cs_ModerationAction B,
		cs_vw_Users_FullUser U
	WHERE
		A.ModerationAction = B.ModerationAction AND
		A.SettingsID = B.SettingsID AND
		U.cs_UserID = A.ModeratorID AND
		A.PostID = @PostID AND
		A.SettingsID = @SettingsID
	ORDER BY
		ModeratedOn

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on dbo.cs_Audit_Post to public
GO
/***********************************************
* SP: cs_Censorships_Get
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_Censorships_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE proc [dbo].cs_Censorships_Get
(
	@Word	nvarchar(40) = '',
	@SettingsID int
)
as
	select
		*
	from
		cs_Censorship
	WHERE
		SettingsID = @SettingsID and (Word = @Word or (@Word = '' AND 1=1))


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Censorships_Get to public
go

/***********************************************
* SP: cs_Censorship_CreateUpdateDelete
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_Censorship_CreateUpdateDelete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE proc [dbo].cs_Censorship_CreateUpdateDelete
(
	  @Word			nvarchar(40)
	, @DeleteWord 	bit = 0
	, @Replacement	nvarchar(40)
	, @SettingsID int
)
as
SET NOCOUNT ON

if( @DeleteWord > 0 )
BEGIN
	DELETE FROM
		cs_Censorship
	WHERE
		Word = @Word and SettingsID = @SettingsID
	RETURN
END
ELSE
BEGIN
	UPDATE cs_Censorship SET
		Replacement	= @Replacement
	WHERE
		Word	= @Word and SettingsID = @SettingsID

	IF( @@rowcount = 0 )
	BEGIN
	INSERT INTO cs_Censorship (
		Word, Replacement, SettingsID
	) VALUES (
		@Word, @Replacement, @SettingsID
	)
	END
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].cs_Censorship_CreateUpdateDelete to public
GO
/***********************************************
* SP: cs_DisallowedNames_Get
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_DisallowedNames_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].cs_DisallowedNames_Get
(
	@SettingsID int
)
AS 

	SELECT 
		DisallowedName 
	FROM 
		cs_DisallowedNames
	WHERE
		SettingsID = @SettingsID 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].cs_DisallowedNames_Get to public
go
/***********************************************
* SP: cs_DisallowedName_CreateUpdateDelete
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_DisallowedName_CreateUpdateDelete'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].cs_DisallowedName_CreateUpdateDelete
(
	@Name		nvarchar(64),
	@Replacement 	nvarchar(64),
	@DeleteName	bit = 0,
	@SettingsID int
)
AS

SET NOCOUNT ON

if( @DeleteName > 0 )
BEGIN
	DELETE FROM
		cs_DisallowedNames
	WHERE
		DisallowedName = @Name and SettingsID = @SettingsID
END
ELSE 
BEGIN
		UPDATE cs_DisallowedNames SET
			DisallowedName = @Replacement
		WHERE
			DisallowedName = @Name and SettingsID = @SettingsID

	if( @@rowcount = 0 )
	BEGIN
		INSERT INTO cs_DisallowedNames (
			DisallowedName, SettingsID
		) VALUES (
			@Name, @SettingsID
		)
		
	END
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].cs_DisallowedName_CreateUpdateDelete to public
go
/***********************************************
* SP: cs_EmailQueue_Delete
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_EmailQueue_Delete'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


create proc dbo.cs_EmailQueue_Delete
	@EmailID uniqueidentifier 
as
	delete from cs_EmailQueue where EmailID = @EmailID




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON dbo.cs_EmailQueue_Delete TO public
GO
/***********************************************
* SP: cs_EmailQueue_Failure
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_EmailQueue_Failure'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


create proc dbo.cs_EmailQueue_Failure
	@EmailID uniqueidentifier,
	@FailureInterval int,
	@MaxNumberOfTries int
as

declare @NumberOfTries int
select @NumberOfTries = NumberOfTries + 1 from cs_EmailQueue where EmailID = @EmailID

if @NumberOfTries <= @MaxNumberOfTries
begin
	update cs_EmailQueue set
		NumberOfTries = @NumberOfTries,
		NextTryTime = dateadd(minute, @NumberOfTries * @FailureInterval, getdate())
	where EmailID = @EmailID
end
else
begin
	delete from cs_EmailQueue where EmailID = @EmailID
end
	
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on dbo.cs_EmailQueue_Failure TO public
GO
/***********************************************
* SP: cs_Emails_Dequeue
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_Emails_Dequeue'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    PROCEDURE [dbo].cs_Emails_Dequeue
(
	@SettingsID int
)
AS
BEGIN

	SELECT * FROM cs_EmailQueue Where SettingsID = @SettingsID and NextTryTime < getdate()
	
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].cs_Emails_Dequeue to public
go
/***********************************************
* SP: cs_Emails_Enqueue
* File Date: 2/23/2005 12:22:17 AM
***********************************************/
Print 'Creating...cs_Emails_Enqueue'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE  PROCEDURE [dbo].cs_Emails_Enqueue
(
	@EmailTo	nvarchar(2000),
	@EmailCc	ntext,
	@EmailBcc	ntext,
	@EmailFrom	nvarchar(256),
	@EmailSubject	nvarchar(1024),
	@EmailBody	ntext,
	@EmailPriority	int,
	@EmailBodyFormat int,
	@SettingsID int
)
AS
BEGIN

	INSERT INTO
		cs_EmailQueue
		(
			emailTo,
			emailCc,
			emailBcc,
			EmailFrom,
			EmailSubject,
			EmailBody,
			emailPriority,
			emailBodyFormat,
			SettingsID
		)
	VALUES
		(
			@EmailTo,
			@EmailCc,
			@EmailBcc,
			@EmailFrom,
			@EmailSubject,
			@EmailBody,
			@EmailPriority,
			@EmailBodyFormat,
			@SettingsID
		)		
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].cs_Emails_Enqueue to public
go
/***********************************************
* SP: cs_Emails_TrackingSection
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_Emails_TrackingSection'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

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


CREATE PROCEDURE [dbo].cs_Emails_TrackingSection
(
	@PostID    INT,
	@SettingsID int
)
AS

DECLARE @SectionID INT
DECLARE @UserID INT
DECLARE @PostLevel INT
DECLARE @ThreadID INT

-- First get the post info
SELECT 
	@SectionID = SectionID, 
	@UserID = UserID,
	@PostLevel = PostLevel,
	@ThreadID = ThreadID
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID

-- Check if its a new thread or not
IF (@PostLevel = 1)
BEGIN
	-- this is a new thread (1 & 2)
	
	-- Check if this is a PM message
	IF (@SectionID = 0)
	BEGIN
		
		-- we have to bind to the PM users for this ThreadID
		SELECT
			U.Email,
			U.EnableHtmlEmail
		FROM
			cs_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID
			JOIN cs_PrivateMessages PM ON PM.UserID = F.UserID AND PM.ThreadID = @ThreadID
		WHERE
			F.SectionID IN (-1, 0) AND F.SettingsID = @SettingsID and U.SettingsID = @SettingsID and 
			F.SubscriptionType <> 0

	END
	ELSE BEGIN

		SELECT
			U.Email, 
			U.EnableHtmlEmail
		FROM 
			cs_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID
		WHERE
			F.SectionID = @SectionID AND F.SettingsID = @SettingsID and U.SettingsID = @SettingsID
			and F.SubscriptionType <> 0
	END
END
ELSE BEGIN
	-- this is a reply to an existing post (2)

	-- Check if this is a PM message
	IF (@SectionID = 0)
	BEGIN
		
		-- we have to bind to the PM users for this ThreadID
		SELECT
			U.Email,
			U.EnableHtmlEmail
		FROM
			cs_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID
			JOIN cs_PrivateMessages PM ON PM.UserID = F.UserID AND PM.ThreadID = @ThreadID
		WHERE
			F.SectionID IN (-1, 0) AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and
			F.SubscriptionType = 2

	END
	ELSE BEGIN

		SELECT
			U.Email, 
			U.EnableHtmlEmail
		FROM 
			cs_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID			
		WHERE
			F.SectionID = @SectionID AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and 
			F.SubscriptionType = 2
	END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Emails_TrackingSection to public
go
/***********************************************
* SP: cs_Emails_TrackingThread
* File Date: 2/23/2005 12:22:15 AM
***********************************************/