beta1to1.2.sql

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

/*
表结构的更改
*/
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_UserAvatar
	DROP CONSTRAINT FK_forums_UserAvatar_forums_Images
GO
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
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_UserAvatar ADD
	FileName nvarchar(64) NOT NULL CONSTRAINT DF_forums_UserAvatar_AvatarExtension DEFAULT ''
GO
COMMIT

print '在线信息'
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_UsersOnline ADD
	OnlineStatus smallint NOT NULL CONSTRAINT DF_forums_UsersOnline_OnlineStatus DEFAULT 0,
	NewMessage bit NOT NULL CONSTRAINT DF_forums_UsersOnline_NewMessage DEFAULT 0,
	NewMail bit NOT NULL CONSTRAINT DF_forums_UsersOnline_NewMail DEFAULT 0,
	NewPost bit NOT NULL CONSTRAINT DF_forums_UsersOnline_NewPost DEFAULT 0,
	NewThread bit NOT NULL CONSTRAINT DF_forums_UsersOnline_NewThread DEFAULT 0
GO
COMMIT


print '生日'
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_UserProfile ADD
	Birthday datetime NULL
GO
COMMIT

/*
新表
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[forums_AvatarGroups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[forums_AvatarGroups]
GO

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

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

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

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

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

CREATE TABLE [dbo].[forums_AvatarGroups] (
	[AvatarGroupID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FolderName] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[forums_Avatars] (
	[AvatarID] [int] IDENTITY (1, 1) NOT NULL ,
	[AvatarGroupID] [int] NOT NULL ,
	[Name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FileName] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[forums_BuddyGroups] (
	[BuddyGroupID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UserID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[forums_BuddyList] (
	[UserID] [int] NOT NULL ,
	[BuddyGroupID] [int] NOT NULL ,
	[BuddyID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[forums_MailBoxs] (
	[MailBoxID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UserID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[forums_Mails] (
	[From] [int] NOT NULL ,
	[To] [int] NOT NULL ,
	[PostID] [int] NOT NULL ,
	[MailBoxID] [int] NOT NULL ,
	[Priority] [int] NOT NULL ,
	[Status] [smallint] NOT NULL ,
	[HasRead] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[forums_AvatarGroups] WITH NOCHECK ADD 
	CONSTRAINT [PK_forums_AvatarGroups] PRIMARY KEY  CLUSTERED 
	(
		[AvatarGroupID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[forums_Avatars] WITH NOCHECK ADD 
	CONSTRAINT [PK_forums_Avatars] PRIMARY KEY  CLUSTERED 
	(
		[AvatarID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[forums_BuddyGroups] WITH NOCHECK ADD 
	CONSTRAINT [PK_forums_BuddyGroups] PRIMARY KEY  CLUSTERED 
	(
		[BuddyGroupID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[forums_MailBoxs] WITH NOCHECK ADD 
	CONSTRAINT [PK_forums_MailBox] PRIMARY KEY  CLUSTERED 
	(
		[MailBoxID]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [IX_forums_BuddyList] ON [dbo].[forums_BuddyList]([UserID], [BuddyGroupID], [BuddyID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[forums_AvatarGroups] ADD 
	CONSTRAINT [DF_forums_AvatarGroups_Name] DEFAULT ('') FOR [Name],
	CONSTRAINT [DF_forums_AvatarGroups_FolderName] DEFAULT ('') FOR [FolderName]
GO

ALTER TABLE [dbo].[forums_Avatars] ADD 
	CONSTRAINT [DF_forums_Avatars_AvatarGroupID_1] DEFAULT (0) FOR [AvatarGroupID],
	CONSTRAINT [DF_forums_Avatars_Name] DEFAULT ('') FOR [Name],
	CONSTRAINT [DF_forums_Avatars_FileName] DEFAULT ('') FOR [FileName]
GO

ALTER TABLE [dbo].[forums_BuddyGroups] ADD 
	CONSTRAINT [DF_forums_BuddyGroups_Name] DEFAULT ('') FOR [Name],
	CONSTRAINT [DF_forums_BuddyGroups_UserID] DEFAULT (0) FOR [UserID]
GO

ALTER TABLE [dbo].[forums_BuddyList] ADD 
	CONSTRAINT [DF_forums_Buddies_BuddyGroupID] DEFAULT (0) FOR [BuddyGroupID]
GO

ALTER TABLE [dbo].[forums_MailBoxs] ADD 
	CONSTRAINT [DF_forums_MailBox_Name] DEFAULT ('') FOR [Name]
GO

ALTER TABLE [dbo].[forums_Mails] ADD 
	CONSTRAINT [DF_forums_Mail_Status] DEFAULT (0) FOR [Status],
	CONSTRAINT [DF_forums_Mail_Read] DEFAULT (0) FOR [HasRead]
GO


/*
数据
*/
delete from forums_UsersInRoles where userid = 0 and roleid > 0

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

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

-- 私人留言重新导入
INSERT INTO forums_Mails
select
	PM1.UserID, PM2.UserID,P.PostID,1,2,1,1
from 
	forums_posts P,
	forums_PrivateMessages PM1,
	forums_PrivateMessages PM2
where
	P.ThreadID = PM1.ThreadID AND
	P.ThreadID = PM2.ThreadID AND
	P.UserID = PM1.UserID AND
	P.UserID <> PM2.UserID