bbsmax.sql

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

SET  ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, QUOTED_IDENTIFIER ON 
SET  NUMERIC_ROUNDABORT OFF
GO
:setvar DatabaseName "bbsMax"
:setvar PrimaryFilePhysicalName "E:\SQL Server 2005 Data\MSSQL.1\MSSQL\DATA\bbsMax.mdf"
:setvar PrimaryLogFilePhysicalName "E:\SQL Server 2005 Data\MSSQL.1\MSSQL\DATA\bbsMax_log.ldf"

USE [master]

GO

:on error exit

IF  (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'目标数据库 %s 的状态未设置为 ONLINE。要部署此数据库，数据库状态必须设置为 ONLINE。', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END
GO

:on error exit

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END
GO
CREATE DATABASE [$(DatabaseName)] ON ( NAME = N'PrimaryFileName', FILENAME = N'$(PrimaryFilePhysicalName)') LOG ON ( NAME = N'PrimaryLogFileName', FILENAME = N'$(PrimaryLogFilePhysicalName)') COLLATE Chinese_PRC_CI_AS 

GO

:on error resume
     
EXEC sp_dbcmptlevel N'$(DatabaseName)', 80

GO

IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	MULTI_USER,
	CURSOR_CLOSE_ON_COMMIT OFF,
	CURSOR_DEFAULT LOCAL,
	AUTO_CLOSE OFF,
	AUTO_CREATE_STATISTICS ON,
	AUTO_SHRINK OFF,
	AUTO_UPDATE_STATISTICS ON,
	ANSI_NULL_DEFAULT ON,
	ANSI_NULLS ON,
	ANSI_PADDING ON,
	ANSI_WARNINGS ON,
	ARITHABORT ON,
	CONCAT_NULL_YIELDS_NULL ON,
	NUMERIC_ROUNDABORT OFF,
	QUOTED_IDENTIFIER ON,
	RECURSIVE_TRIGGERS OFF,
	RECOVERY SIMPLE,
	TORN_PAGE_DETECTION OFF
	WITH ROLLBACK IMMEDIATE
GO

IF (IS_SRVROLEMEMBER(N'sysadmin') = 1)
BEGIN
    EXEC sp_dboption N'$(DatabaseName)', 'db chaining', 'OFF'
END
ELSE
BEGIN
    RAISERROR(N'无法为 DB_CHAINING 或 TRUSTWORTHY 修改数据库设置。您必须是 SysAdmin 才能应用这些设置。',0,1)
END


GO

USE [$(DatabaseName)]

GO
-- 预先部署脚本模板							
----------------------------------------------------------------------------------------
-- 此文件包含将在生成脚本前执行的 SQL 语句	
-- 使用 SQLCMD 语法将文件包含在预先部署脚本中			
-- 示例:      :r .\filename.sql								
-- 使用 SQLCMD 语法引用预先部署脚本中的变量		
-- 示例:      :setvar $TableName MyTable							
--               SELECT * FROM [$(TableName)]					
----------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[bbsMax_Identities_4000]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[bbsMax_Identities_4000]
GO

SELECT TOP 4000 ID = IDENTITY(int,1,1) INTO [dbo].[bbsMax_Identities_4000]
FROM syscolumns a, syscolumns b
ALTER TABLE [dbo].[bbsMax_Identities_4000] ADD CONSTRAINT PK_bbsMax_Identities_4000 PRIMARY KEY(ID)
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[bbsMax_Identities_8000]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[bbsMax_Identities_8000]
GO

SELECT TOP 8000 ID = IDENTITY(int,1,1) INTO [dbo].[bbsMax_Identities_8000]
FROM syscolumns a, syscolumns b
ALTER TABLE [dbo].[bbsMax_Identities_8000] ADD CONSTRAINT PK_bbsMax_Identities_8000 PRIMARY KEY(ID)
GO
GO








GO

:on error exit

:on error resume
GO
PRINT N'正在添加全文索引'
GO
EXEC sp_fulltext_database N'enable'
GO
PRINT N'正在创建全文目录'
GO
Exec sp_fulltext_catalog 'FTCatalog_bbsMax_Posts','create'
GO
Exec sp_fulltext_catalog 'FTCatalog_bbsMax_Threads','create'
GO
PRINT N'正在创建 [dbo].[bbsMax_Announcements]'
GO
CREATE TABLE [dbo].[bbsMax_Announcements]
(
[AnnouncementID] [int] NOT NULL IDENTITY(1, 1),
[AnnouncementType] [tinyint] NOT NULL,
[PostUserID] [int] NOT NULL,
[PostUserName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Subject] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Content] [ntext] COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[SortOrder] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_Announcements] 上创建主键 [PK_bbsMax_Announcements]'
GO
ALTER TABLE [dbo].[bbsMax_Announcements] ADD CONSTRAINT [PK_bbsMax_Announcements] PRIMARY KEY NONCLUSTERED  ([AnnouncementID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_Announcements] 的索引 [IX_bbsMax_Announcements]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_bbsMax_Announcements] ON [dbo].[bbsMax_Announcements] ([SortOrder]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAnnouncement]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2007/1/16>
-- Description:	<获取单个公告>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetAnnouncement]
	@AnnouncementID int
AS
	SET NOCOUNT ON
	SELECT * FROM [bbsMax_Announcements] WHERE AnnouncementID=@AnnouncementID
	RETURN
GO
PRINT N'正在创建 [dbo].[System_bbsMax_FaqCatalogs]'
GO
CREATE TABLE [dbo].[System_bbsMax_FaqCatalogs]
(
[FaqCatalogID] [int] NOT NULL IDENTITY(1, 1),
[FaqCatalogName] [nvarchar] (50) NOT NULL,
[SortOrder] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[System_bbsMax_FaqCatalogs] 上创建主键 [PK_System_bbsMax_FaqCatalogs]'
GO
ALTER TABLE [dbo].[System_bbsMax_FaqCatalogs] ADD CONSTRAINT [PK_System_bbsMax_FaqCatalogs] PRIMARY KEY CLUSTERED  ([FaqCatalogID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[System_bbsMax_FaqCatalogs] 的索引 [IX_System_bbsMax_FaqCatalogs]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_System_bbsMax_FaqCatalogs] ON [dbo].[System_bbsMax_FaqCatalogs] ([SortOrder]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[System_bbsMax_Faqs]'
GO
CREATE TABLE [dbo].[System_bbsMax_Faqs]
(
[FaqID] [int] NOT NULL IDENTITY(1, 1),
[FaqCatalogID] [int] NOT NULL,
[FaqTitle] [nvarchar] (500) NOT NULL,
[FaqContent] [ntext] NOT NULL,
[SortOrder] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在 [dbo].[System_bbsMax_Faqs] 上创建主键 [PK_System_bbsMax_Faqs]'
GO
ALTER TABLE [dbo].[System_bbsMax_Faqs] ADD CONSTRAINT [PK_System_bbsMax_Faqs] PRIMARY KEY CLUSTERED  ([FaqID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[System_bbsMax_Faqs] 的索引 [IX_System_bbsMax_Faqs]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_System_bbsMax_Faqs] ON [dbo].[System_bbsMax_Faqs] ([FaqCatalogID], [SortOrder]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_Common_GetRecordsByPageSQLString]'
GO
CREATE PROCEDURE [dbo].[bbsMax_Common_GetRecordsByPageSQLString]
	@PageIndex int,
	@PageSize int,
	@TableName varchar(256),
	@SelectFields varchar(512) = '*', --查询字段，默认为 *
	@Condition nvarchar(256) = N'',     --条件例如"DirectoryID=4"
	@SortField varchar(256) = '[SortOrder]',
	@IsDesc bit = 1, --是否倒序
	@TotalRecords int = -1,
	@ResetOrder bit output,----------- 1表示读取数据的时候 排序要反过来
	@SQLString  nvarchar(4000) output
AS
BEGIN
	SET NOCOUNT ON;

	--DECLARE @SQLString nvarchar(4000);
	DECLARE @WhereString1 nvarchar(800);
	DECLARE @WhereString2 nvarchar(800);
	--DECLARE @ResetOrder bit----------- 1表示读取数据的时候 排序要反过来
	IF @Condition IS NULL OR @Condition = N'' BEGIN
		SELECT @WhereString1 = N'';
		SELECT @WhereString2 = N'WHERE ';
	END
	ELSE BEGIN
		SELECT @WhereString1 = N'WHERE ' + @Condition;
		SELECT @WhereString2 = N'WHERE ' + @Condition + N' AND ';
	END

	IF @PageIndex = 0 BEGIN
		SELECT @SQLString = N'SELECT TOP ' + STR(@PageSize)
			+ N' ' + @SelectFields
			+ N' FROM ' + @TableName + ' WITH (NOLOCK)
			' + @WhereString1 + '
			ORDER BY ' + @SortField;
		IF @IsDesc = 1
			SELECT @SQLString = @SQLString + ' DESC';
		SET @ResetOrder=0	
	END
	ELSE BEGIN
	-----------------------------------------------
		SET @SQLString='';
		DECLARE @GetFromLast BIT
		IF @TotalRecords=-1
			SET @GetFromLast=0
		ELSE BEGIN
			DECLARE @TotalPage INT,@ResidualCount INT
			SET @ResidualCount=@TotalRecords%@PageSize
			IF @ResidualCount=0
				SET @TotalPage=@TotalRecords/@PageSize
			ELSE
				SET @TotalPage=@TotalRecords/@PageSize+1
			IF @PageIndex>@TotalPage/2 --从最后页算上来
				SET @GetFromLast=1
			ELSE
				SET @GetFromLast=0
	-------------------------------------------------
	-------------------------------------------------
			IF @GetFromLast=1 BEGIN
				IF @PageIndex=@TotalPage-1 BEGIN
					IF @ResidualCount=0
						SET @ResidualCount=@PageSize;
					SELECT @SQLString = N'SELECT top ' + STR(@ResidualCount)
						+ N' ' + @SelectFields
						+ N' FROM ' + @TableName + ' WITH (NOLOCK)
						' + @WhereString1 + '
						ORDER BY ' + @SortField;
					IF @IsDesc = 0--反过来
						SELECT @SQLString = @SQLString + ' DESC';
					--EXEC (@SQLString);
					SET @ResetOrder=1
				END 
				ELSE IF  @PageIndex>@TotalPage-1 BEGIN --已经超过最大页数
					SELECT @SQLString = N'SELECT ' + @SelectFields
						+ N' FROM ' + @TableName + ' WITH (NOLOCK) WHERE 1=2'
					--EXEC (@SQLString);
					SET @ResetOrder=0
				END
				ELSE BEGIN
					SET @PageIndex=@TotalPage-(@PageIndex+1)---
					IF @IsDesc=1
						SET @IsDesc=0
					ELSE
						SET @IsDesc=1
					SET @ResetOrder=1
				END  
			END
			ELSE 
				SET @ResetOrder=0
		END
		
		IF @SQLString='' BEGIN
			DECLARE @TopCount INT
			IF @GetFromLast=1
				SET @TopCount=@PageSize * (@PageIndex-1)+@ResidualCount
			ELSE
				SET @TopCount=@PageSize * @PageIndex
		-------------------------------------------------
			IF @IsDesc = 1
				SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
				+ N' ' + @SelectFields
				+ N' FROM ' + @TableName+' WITH (NOLOCK) 
				' + @WhereString2 + @SortField + ' <
					(SELECT Min(' + @SortField + ') FROM 
						(SELECT TOP ' + STR(@TopCount) + ' ' + @SortField + ' FROM ' + @TableName+' WITH (NOLOCK)
							' + @WhereString1 + '
								ORDER BY ' + @SortField + ' DESC) AS IDX)
				ORDER BY ' + @SortField + ' DESC';
			ELSE
				SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
				+ N' ' + @SelectFields
				+ N' FROM ' + @TableName+' WITH (NOLOCK) 
				' + @WhereString2 + @SortField + ' >
					(SELECT Max(' + @SortField + ') FROM 
						(SELECT TOP ' + STR(@TopCount) + ' ' + @SortField + ' FROM ' + @TableName+' WITH (NOLOCK)
							' + @WhereString1 + '
								ORDER BY ' + @SortField + ' ASC) AS IDX)
				ORDER BY ' + @SortField + ' ASC';
		END
	END
	--EXEC (@SQLString);
	--RETURN @SQLString

END
GO
PRINT N'正在创建 [dbo].[bbsMax_PostIcons]'
GO
CREATE TABLE [dbo].[bbsMax_PostIcons]
(
[IconID] [int] NOT NULL IDENTITY(1, 1),
[IconUrl] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[SortOrder] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_PostIcons] 上创建主键 [PK_bbsMax_PostIcons]'
GO
ALTER TABLE [dbo].[bbsMax_PostIcons] ADD CONSTRAINT [PK_bbsMax_PostIcons] PRIMARY KEY CLUSTERED  ([IconID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_PostIcons] 的索引 [IX_bbsMax_PostIcons]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_bbsMax_PostIcons] ON [dbo].[bbsMax_PostIcons] ([SortOrder]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_DeletePostIcon]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2006/12/27>
-- Description:	<删除>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_DeletePostIcon]
	@IconID int
AS
	SET NOCOUNT ON