bbsmax.sql

这是一个关于论坛的一点小的源代码

		SET @GroupCondition=''
		
	IF @Condition<>''
		SET @GroupCondition=@GroupCondition+' AND '+@Condition
		
	DECLARE @e1 int,@e2 int,@e3 int,@e4 int
	BEGIN TRANSACTION 
	SET @SQLString=N'SELECT @TempOrder=ISNULL(Max(SortOrder),0) FROM '+@TableName+' WITH (NOLOCK) where 1=1'+@GroupCondition
	EXECUTE sp_executesql @SQLString,N'@TempOrder int output',@TempOrder output
	SELECT @e1=@@error
	SET @TempOrder=@TempOrder+1--获取最大的SortOrder加1作为临时值
	
	SET @SQLString='UPDATE '+@TableName+' SET SortOrder='+str(@TempOrder)+' WHERE SortOrder='+STR(@SortOrder2)+@GroupCondition
	EXEC(@SQLString)--把SortOrder为@SortOrder2的值改为@TempOrder
	SELECT @e2=@@error
	
	SET @SQLString='UPDATE '+@TableName+' SET SortOrder=SortOrder+1 WHERE SortOrder>'+STR(@SortOrder1)+'AND SortOrder<'+STR(@SortOrder2)+@GroupCondition
	EXEC(@SQLString)--把大于@SortOrder1小于@SortOrder2的值全部加1
	SELECT @e3=@@error
	
	SET @SQLString='UPDATE '+@TableName+' SET SortOrder='+STR(@SortOrder1)+'+1 WHERE SortOrder='+str(@TempOrder)+@GroupCondition
	EXEC(@SQLString)--把原来值为@SortOrder2的值改为@SortOrder1加1
	SELECT @e4=@@error
	
	IF(@e1=0 AND @e2=0 AND @e3=0 AND @e4=0)
		COMMIT TRANSACTION
	ELSE
		ROLLBACK TRANSACTION
GO
PRINT N'正在创建 [dbo].[bbsMax_GetNotExpiresAnnouncements]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2007/1/16>
-- Description:	<获取没有过期公告>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetNotExpiresAnnouncements] 
AS
	SET NOCOUNT ON
	SELECT * FROM [bbsMax_Announcements] WHERE EndDate>getdate() ORDER BY SortOrder DESC
	RETURN
GO
PRINT N'正在创建 [dbo].[bbsMax_CreateOrUpdatePostIndexAlias]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/2/6>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_CreateOrUpdatePostIndexAlias]
	@PostIndex int,
	@Alias nvarchar(128)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM [bbsMax_PostIndexAlias] WITH(NOLOCK) WHERE PostIndex=@PostIndex)
		UPDATE [bbsMax_PostIndexAlias] SET Alias=@Alias WHERE PostIndex=@PostIndex
	ELSE
		INSERT INTO [bbsMax_PostIndexAlias](PostIndex,Alias) VALUES(@PostIndex,@Alias)
	RETURN
GO
PRINT N'正在创建 [dbo].[bbsMax_QuestionRewards]'
GO
CREATE TABLE [dbo].[bbsMax_QuestionRewards]
(
[ThreadID] [int] NOT NULL,
[PostID] [int] NOT NULL,
[Reward] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_QuestionRewards] 上创建主键 [PK_bbsMax_QuestionRewards]'
GO
ALTER TABLE [dbo].[bbsMax_QuestionRewards] ADD CONSTRAINT [PK_bbsMax_QuestionRewards] PRIMARY KEY CLUSTERED  ([ThreadID], [PostID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_QuestionRewards] 的索引 [IX_bbsMax_QuestionRewards_List]'
GO
CREATE NONCLUSTERED INDEX [IX_bbsMax_QuestionRewards_List] ON [dbo].[bbsMax_QuestionRewards] ([ThreadID], [Reward]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_AnnouncementDoSortOrder]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/1/11>
-- Description:	<公告排序>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_AnnouncementDoSortOrder]
	@SortOrder1 int,
	@SortOrder2 int--要移动的记录（SortOrder为@SortOrder2的记录排在@SortOrder1的后面，@SortOrder1为-1时排在最前面）
AS
	SET NOCOUNT ON 
	IF(@SortOrder1=@SortOrder2)
		RETURN (-1)
	IF ((@SortOrder1=-1 OR
		EXISTS (SELECT * FROM [bbsMax_Announcements] WITH (NOLOCK)  where SortOrder=@SortOrder1))AND
		EXISTS (SELECT * FROM [bbsMax_Announcements] WITH (NOLOCK)  where SortOrder=@SortOrder2))
		BEGIN
			EXECUTE bbsMax_Common_DoSortOrder @SortOrder1,@SortOrder2,'',0,'bbsMax_Announcements'
			RETURN (0)
		END
	ELSE
		RETURN (-1)
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAllAnnouncements]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/1/11>
-- Description:	<修改公告>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetAllAnnouncements]

AS
	SET NOCOUNT ON
	SELECT * FROM [bbsMax_Announcements] WITH(NOLOCK) ORDER BY SortOrder
GO
PRINT N'正在创建 [dbo].[bbsMax_CreatePostIcon]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2006/12/27>
-- Description:	<创建>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_CreatePostIcon]
	@IconUrl nvarchar(256)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM [bbsMax_PostIcons] WITH (NOLOCK) WHERE IconUrl=@IconUrl)
		RETURN (3) 
	ELSE 
		BEGIN
		declare @SortOrder int
		EXECUTE bbsMax_Common_GetSortOrder 'bbsMax_PostIcons', '',@SortOrder output
		INSERT INTO [bbsMax_PostIcons] (
		[IconUrl],
		[SortOrder]
		) VALUES (
		@IconUrl,
		@SortOrder
		)
		RETURN (0)
		END
GO
PRINT N'正在创建 [dbo].[bbsMax_Split_ntext_long]'
GO
CREATE FUNCTION [dbo].[bbsMax_Split_ntext_long]
(
	@text ntext,
	@Separator nvarchar(2) = N','
)
RETURNS @ItemTable TABLE 
(
	id int identity(1,1),
	item nvarchar(4000) 
)
AS
BEGIN

	DECLARE @s nvarchar(4000), @i int, @j int   
	SELECT @s = SUBSTRING(@text, 1, 4000), @i=1   
	IF (@s = '')
		INSERT @ItemTable VALUES ('')
	ELSE BEGIN
		WHILE @s <> '' BEGIN

			IF len(@s) = 4000   
				SELECT @j = 4000 - CHARINDEX(@Separator, REVERSE(@s)), @i = @i + @j + 1, @s = LEFT(@s, @j)
			ELSE     
				SELECT @i = @i + 4000, @j = LEN(@s)

			INSERT @ItemTable
			SELECT SUBSTRING(@s, ID ,CHARINDEX(@Separator, @s + @Separator, ID) - ID)   
			FROM bbsMax_Identities_4000 WITH (NOLOCK)
			WHERE ID <= @j + 1 AND CHARINDEX(@Separator, @Separator + @s, ID) - ID = 0   

			SELECT @s = SUBSTRING(@text, @i, 4000)

		END  
	END 
	RETURN;
END
GO
PRINT N'正在创建 [dbo].[bbsMax_CreateAttachmentExchange]'
GO
CREATE PROCEDURE [dbo].[bbsMax_CreateAttachmentExchange]
	@AttachmentID int, 
	@UserID int,
	@Price int
AS
	Insert into bbsMax_AttachmentExchanges(AttachmentID,UserID,Price,CreateDate) values(@AttachmentID,@UserID,@Price,getdate())
	IF(@@ROWCOUNT>0)
		RETURN 0
	ELSE
		RETURN 1
GO
PRINT N'正在创建 [dbo].[bbsMax_PostIconDoSortOrder]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2006/12/27>
-- Description:	<排序>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_PostIconDoSortOrder]
	@SortOrder1 int,
	@SortOrder2 int--要移动的记录（SortOrder为@SortOrder2的记录排在@SortOrder1的后面，@SortOrder1为-1时排在最前面）
AS
	SET NOCOUNT ON
	IF(@SortOrder1=@SortOrder2)
		RETURN (-1)
	IF ((@SortOrder1=-1 OR
		EXISTS (SELECT * FROM [bbsMax_PostIcons] WITH (NOLOCK)  where SortOrder=@SortOrder1))AND
		EXISTS (SELECT * FROM [bbsMax_PostIcons] WITH (NOLOCK)  where SortOrder=@SortOrder2))
		BEGIN
			EXECUTE bbsMax_Common_DoSortOrder @SortOrder1,@SortOrder2,'',0,'bbsMax_PostIcons'
			RETURN (0)
		END
	ELSE
		RETURN (-1)
GO
PRINT N'正在创建 [dbo].[bbsMax_GetDisabledTriggerForumIDs]'
GO
CREATE PROCEDURE [dbo].[bbsMax_GetDisabledTriggerForumIDs]
				@ForumIDs nvarchar(64) output
			AS
			BEGIN
				SET NOCOUNT ON;
				set @ForumIDs='';
			END
GO
PRINT N'正在创建 [dbo].[bbsMax_CreateThreadCatalog]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/22>
-- Description:	<创建>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_CreateThreadCatalog] 
	@ThreadCatalogName nvarchar(64),
	@LogoUrl nvarchar(512)
AS
	SET NOCOUNT ON 
	IF EXISTS (SELECT * FROM [bbsMax_ThreadCatalogs] WITH (NOLOCK) WHERE ThreadCatalogName=@ThreadCatalogName)
			RETURN (3) 
--	ELSE IF EXISTS (SELECT * FROM [bbsMax_ThreadCatalogs] WITH (NOLOCK) WHERE LogoUrl=@LogoUrl)
--			RETURN (6) 
	ELSE
		BEGIN
--		declare @SortOrder int,@Condition varchar(100)
--		SET @Condition='ForumID='+str(@ForumID)
--		EXECUTE MaxCommon_GetSortOrder 'bbsMax_ThreadCatalogs', @Condition,@SortOrder output
		INSERT INTO [bbsMax_ThreadCatalogs] (
		[ThreadCatalogName],
		[LogoUrl]
		) VALUES (
		@ThreadCatalogName,
		@LogoUrl
		)
		RETURN (0)
		END
GO
PRINT N'正在创建 [dbo].[bbsMax_GetExtendedPointCurrencys]'
GO
CREATE PROC [bbsMax_GetExtendedPointCurrencys]

AS

SELECT * FROM bbsMax_CurrencyExtendedPointRatio
GO
PRINT N'正在创建 [dbo].[System_bbsMax_PermissionSchemes]'
GO
CREATE TABLE [dbo].[System_bbsMax_PermissionSchemes]
(
[SchemeID] [int] NOT NULL IDENTITY(1, 1),
[PermissionType] [varchar] (256) NOT NULL,
[SchemeName] [nvarchar] (256) NOT NULL,
[Description] [ntext] NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_System_bbsMax_Permissions_CreateDate] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[System_bbsMax_PermissionSchemes] 的索引 [IX_System_bbsMax_PermissionSchemes]'
GO
CREATE UNIQUE CLUSTERED INDEX [IX_System_bbsMax_PermissionSchemes] ON [dbo].[System_bbsMax_PermissionSchemes] ([PermissionType], [SchemeID]) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[System_bbsMax_PermissionSchemes] 上创建主键 [PK_System_bbsMax_PermissionSchemes]'
GO
ALTER TABLE [dbo].[System_bbsMax_PermissionSchemes] ADD CONSTRAINT [PK_System_bbsMax_PermissionSchemes] PRIMARY KEY NONCLUSTERED  ([SchemeID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAllPostIcons]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2006/12/27>
-- Description:	<获取所有信息>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetAllPostIcons]

AS
	SET NOCOUNT ON
	SELECT 
	*
	FROM bbsMax_PostIcons with (nolock) order by SortOrder 
	RETURN
GO
PRINT N'正在创建 [dbo].[bbsMax_GetThreadManageLogsBySearch]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2006/12/28>
-- Description:	<获取日志>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetThreadManageLogsBySearch]
	@PageIndex int,
	@PageSize int,
	@Keyword nvarchar(4000),
	@ActionType tinyint
AS
	SET NOCOUNT ON 
	DECLARE @Condition nvarchar(4000)
	-------Modify By 帅帅 2007/3/20----------start
		IF(@Keyword='') BEGIN
				SET @Condition=''
		END
		ELSE BEGIN
			IF(@ActionType=1)--操作者
			SET @Condition='UserID in('+@Keyword+')'
			ELSE IF(@ActionType=2)--IP地址
				SET @Condition='IPAddress LIKE ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=3)--时间
			SET @Condition='CreateDate LIKE ''%'+@Keyword+'%'''
			ELSE IF(@ActionType=4)--版面名称
			SET @Condition='ForumID in(SELECT ForumID FROM bbsMax_Forums WITH(NOLOCK) WHERE ForumName Like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=5)--主题标题
			SET @Condition='ThreadID in(SELECT ThreadID FROM bbsMax_Threads WITH(NOLOCK) WHERE Subject Like ''%'+@Keyword+'%'')'
			ELSE IF(@ActionType=6)--动作
			SET @Condition='ActionType='+@Keyword
			ELSE IF(@ActionType=7)--原因
			SET @Condition='Reason LIKE ''%'+@Keyword+'%'''
		END
	-------Modify By 帅帅 2007/3/20----------end
	EXECUTE bbsMax_Common_GetRecordsByPage_LongCondition @PageIndex,@PageSize,'bbsMax_ThreadManageLogs','*',@Condition,'LogID',1
	DECLARE @S INT
	IF(@Condition<>'')
	EXEC('SELECT COUNT(1) TotalRecord FROM bbsMax_ThreadManageLogs WHERE ' +@Condition) 
	ELSE
	EXEC('SELECT COUNT(1) TotalRecord FROM bbsMax_ThreadManageLogs')
GO
PRINT N'正在创建 [dbo].[System_bbsMax_Permissions]'
GO
CREATE TABLE [dbo].[System_bbsMax_Permissions]
(
[SchemeID] [int] NOT NULL,
[RoleID] [int] NOT NULL,
[PermissionItems] [ntext] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在 [dbo].[System_bbsMax_Permissions] 上创建主键 [PK_System_bbsMax_Permissions]'
GO
ALTER TABLE [dbo].[System_bbsMax_Permissions] ADD CONSTRAINT [PK_System_bbsMax_Permissions] PRIMARY KEY CLUSTERED  ([SchemeID], [RoleID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_Users]'