forums0804tobeta1.sql

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

sp_rename forums_Vote, forums_Vote_Create
go
sp_rename Vote, forums_Vote
go


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_AnonymousUsers ADD
	IPAddress nvarchar(32) NOT NULL CONSTRAINT DF_forums_AnonymousUsers_IPAddress 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_Forums ADD
	ForumLogo nvarchar(512) NOT NULL CONSTRAINT DF_forums_Forums_ForumLogo DEFAULT ''
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 ADD
	TotalDownloads int NOT NULL CONSTRAINT DF_forums_PostAttachments_TotalDownloads DEFAULT 0,
	DiskFileName nvarchar(256) NOT NULL CONSTRAINT DF_forums_PostAttachments_DiskFileName DEFAULT ''
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__4DD47EBD
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__4DD47EBD 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






INSERT INTO   forums_UsersInRoles(userid,roleid)
SELECT userid,7 FROM forums_Users where userid > 0

INSERT INTO   forums_UsersInRoles(userid,roleid)
SELECT userid,8 FROM forums_Users where userid > 0

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