hidotnet101tocnforumsbeta1.sql

cnForums是功能强大的论坛。开发语言c#,三层结构。模块设计很值得学习

	(
	SiteID,
	IsActive
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ForumID_Active ON dbo.forums_Forums
	(
	SiteID,
	ForumID
	) ON [PRIMARY]
GO
CREATE TRIGGER forums_Forum_Delete ON dbo.forums_Forums 
FOR DELETE 
AS
BEGIN
	DELETE forums_ForumPermissions WHERE ForumID IN (SELECT ForumID FROM DELETED)
	DELETE forums_Threads WHERE ForumID IN (SELECT ForumID FROM DELETED)
END
GO
COMMIT







BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.forums_PostAttachments
	DROP CONSTRAINT DF_forums_PostAttachments_UserID
GO
ALTER TABLE dbo.forums_PostAttachments
	DROP CONSTRAINT DF_forums_PostAttachments_Created
GO
ALTER TABLE dbo.forums_PostAttachments
	DROP CONSTRAINT DF_forums_PostAttachments_TotalDownloads
GO
CREATE TABLE dbo.Tmp_forums_PostAttachments
	(
	PostAttachmentID int NOT NULL IDENTITY (1, 1),
	PostID int NOT NULL,
	ForumID int NOT NULL,
	UserID int NOT NULL,
	Created datetime NOT NULL,
	FileName nvarchar(256) NOT NULL,
	DiskFileName nvarchar(256) NOT NULL,
	Content image NULL,
	ContentType nvarchar(50) NOT NULL,
	ContentSize int NOT NULL,
	Exif nvarchar(200) NULL,
	CheckGuid nvarchar(50) NULL,
	TotalDownloads int NOT NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_forums_PostAttachments ADD CONSTRAINT
	DF_forums_PostAttachments_UserID DEFAULT (0) FOR UserID
GO
ALTER TABLE dbo.Tmp_forums_PostAttachments ADD CONSTRAINT
	DF_forums_PostAttachments_Created DEFAULT (getdate()) FOR Created
GO
ALTER TABLE dbo.Tmp_forums_PostAttachments ADD CONSTRAINT
	DF_forums_PostAttachments_FileName DEFAULT '' FOR FileName
GO
ALTER TABLE dbo.Tmp_forums_PostAttachments ADD CONSTRAINT
	DF_forums_PostAttachments_DiskFileName DEFAULT '' FOR DiskFileName
GO
ALTER TABLE dbo.Tmp_forums_PostAttachments ADD CONSTRAINT
	DF_forums_PostAttachments_ContentSize DEFAULT 0 FOR ContentSize
GO
ALTER TABLE dbo.Tmp_forums_PostAttachments ADD CONSTRAINT
	DF_forums_PostAttachments_TotalDownloads DEFAULT (0) FOR TotalDownloads
GO
SET IDENTITY_INSERT dbo.Tmp_forums_PostAttachments ON
GO
IF EXISTS(SELECT * FROM dbo.forums_PostAttachments)
	 EXEC('INSERT INTO dbo.Tmp_forums_PostAttachments (PostAttachmentID, PostID, ForumID, UserID, Created, FileName, DiskFileName, Content, ContentType, ContentSize, Exif, CheckGuid, TotalDownloads)
		SELECT PostAttachmentID, PostID, ForumID, UserID, Created, FileName, DiskFileName, Content, ContentType, ContentSize, Exif, CheckGuid, TotalDownloads FROM dbo.forums_PostAttachments TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_forums_PostAttachments OFF
GO
DROP TABLE dbo.forums_PostAttachments
GO
EXECUTE sp_rename N'dbo.Tmp_forums_PostAttachments', N'forums_PostAttachments', 'OBJECT'
GO
CREATE NONCLUSTERED INDEX IX_forums_PostAttachments ON dbo.forums_PostAttachments
	(
	PostID
	) ON [PRIMARY]
GO
COMMIT





BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.forums_Posts ADD
	ValuableLevel int NOT NULL CONSTRAINT DF_forums_Posts_ValuableLevel DEFAULT 0,
	ValueGroupID int NOT NULL CONSTRAINT DF_forums_Posts_ValueGroupID DEFAULT 0
GO
COMMIT


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.forums_Threads ADD
	IsValued bit NOT NULL CONSTRAINT DF_forums_Threads_IsValued DEFAULT 0
GO
COMMIT


BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.forums_Users ADD
	Nickname nvarchar(64) NOT NULL CONSTRAINT DF_forums_Users_Nickname DEFAULT '',
	IPCreated nvarchar(32) NOT NULL CONSTRAINT DF_forums_Users_IPCreated DEFAULT N'000.000.000.000',
	IPLastActivity nvarchar(32) NOT NULL CONSTRAINT DF_forums_Users_IPLastActivity DEFAULT N'000.000.000.000',
	IPLastLogin nvarchar(32) NOT NULL CONSTRAINT DF_forums_Users_IPLastLogin DEFAULT N'000.000.000.000'
GO
COMMIT



BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.forums_Vote
	DROP CONSTRAINT DF__Vote__UserID__0ADD8CFD
GO
CREATE TABLE dbo.Tmp_forums_Vote
	(
	PostID int NOT NULL,
	Vote nvarchar(4) NOT NULL,
	UserID int NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_forums_Vote ADD CONSTRAINT
	DF__Vote__UserID__0ADD8CFD DEFAULT (0) FOR UserID
GO
IF EXISTS(SELECT * FROM dbo.forums_Vote)
	 EXEC('INSERT INTO dbo.Tmp_forums_Vote (PostID, Vote, UserID)
		SELECT PostID, Vote, UserID FROM dbo.forums_Vote TABLOCKX')
GO
DROP TABLE dbo.forums_Vote
GO
EXECUTE sp_rename N'dbo.Tmp_forums_Vote', N'forums_Vote', 'OBJECT'
GO
COMMIT



CREATE TABLE [forums_ValueGroups] (
	[ValueGroupID] [int] IDENTITY (1, 1) NOT NULL ,
	[ForumID] [int] NOT NULL CONSTRAINT [DF_forums_ValueGroup_ForumID] DEFAULT (0),
	[ParentID] [int] NOT NULL CONSTRAINT [DF_forums_ValueGroup_ParentID] DEFAULT (0),
	[Name] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_forums_ValueGroup_Name] DEFAULT (''),
	[SortOrder] [int] NOT NULL CONSTRAINT [DF_forums_ValueGroup_SortOrder] DEFAULT (0),
	CONSTRAINT [PK_forums_ValueGroup] PRIMARY KEY  CLUSTERED 
	(
		[ValueGroupID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [forums_VoteOptions] (
	[PostID] [int] NOT NULL ,
	[VoteOptions] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FormattedVoteOptions] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_forums_VoteOptions] PRIMARY KEY  CLUSTERED 
	(
		[PostID]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO





IF NOT EXISTS (SELECT ModerationAction FROM forums_ModerationAction WHERE ModerationAction= 19)
  INSERT INTO forums_ModerationAction (ModerationAction, [Description], TotalActions) VALUES (19, 'UnDeletePost', 0)

IF NOT EXISTS (SELECT ModerationAction FROM forums_ModerationAction WHERE ModerationAction= 20)
  INSERT INTO forums_ModerationAction (ModerationAction, [Description], TotalActions) VALUES (20, 'ValuePost', 0)

IF NOT EXISTS (SELECT ModerationAction FROM forums_ModerationAction WHERE ModerationAction= 21)
  INSERT INTO forums_ModerationAction (ModerationAction, [Description], TotalActions) VALUES (21, 'UnValuePost', 0)


update forums_Users
SET Nickname = Username



insert into forums_VoteOptions
	select PostID, Body, FormattedBody FROM forums_Posts WHERE POSTTYPE = 2


insert into forums_UsersInRoles
select DISTINCT UserID,3 from forums_Moderators

insert into forums_UsersInRoles
select DISTINCT UserID,4 from forums_Moderators