cs_procedures.sql

本系统是在asp版《在线文件管理器》的基础上设计制作

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE proc [dbo].cs_Audit_Post
(
	@PostID		int,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
	-- 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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE proc [dbo].cs_Censorship_CreateUpdateDelete
(
	  @Word			nvarchar(40)
	, @DeleteWord 	bit = 0
	, @Replacement	nvarchar(40)
	, @SettingsID int
)
as
SET Transaction Isolation Level Read UNCOMMITTED
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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE proc [dbo].cs_Censorships_Get
(
	@Word	nvarchar(40) = '',
	@SettingsID int
)
as
SET Transaction Isolation Level Read UNCOMMITTED
	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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].cs_DisallowedName_CreateUpdateDelete
(
	@Name		nvarchar(64),
	@Replacement 	nvarchar(64),
	@DeleteName	bit = 0,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].cs_DisallowedNames_Get
(
	@SettingsID int
)
AS 
SET Transaction Isolation Level Read UNCOMMITTED

	SELECT 
		DisallowedName 
	FROM 
		cs_DisallowedNames
	WHERE
		SettingsID = @SettingsID