/* 使用本脚本前，首先要将动网的数据库表建好，然后把ICHAT的数据库表也建好*/
/* 然后在SQL查询分析器里执行下面的脚本代码，即可完全数据库自动合并*/

/* 步骤1： 添加ICHAT用户库所需的字段 */
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.[Dv_user] ADD
	UserCredit int NULL,
	UserLevel int NULL,
	UserEnable int NULL,
	UserIcon varchar(50) NULL,
	UserArming varchar(255) NULL,
	UserOicq varchar(12) NULL,
	UserResume varchar(255) NULL,
	UserMsgCount int NULL,
	CreateDate datetime NULL,
	ModifyDate datetime NULL,
	MemberLevel int NULL
GO
DECLARE @v sql_variant 
SET @v = N'用户积分'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Dv_user', N'column', N'UserCredit'
GO
DECLARE @v sql_variant 
SET @v = N'用户级别'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Dv_user', N'column', N'UserLevel'
GO
DECLARE @v sql_variant 
SET @v = N'是否允许登录'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Dv_user', N'column', N'UserEnable'
GO
DECLARE @v sql_variant 
SET @v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Dv_user', N'column', N'UserMsgCount'
GO
DECLARE @v sql_variant 
SET @v = N'最后访问日期'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Dv_user', N'column', N'ModifyDate'
GO
DECLARE @v sql_variant 
SET @v = N'会员级别'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Dv_user', N'column', N'MemberLevel'
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_UserCredit DEFAULT 100 FOR UserCredit
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_UserLevel DEFAULT 1 FOR UserLevel
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_UserEnable DEFAULT 1 FOR UserEnable
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_UserMsgCount DEFAULT 0 FOR UserMsgCount
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_CreateDate DEFAULT getdate() FOR CreateDate
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_ModifyDate DEFAULT getdate() FOR ModifyDate
GO
ALTER TABLE dbo.[Dv_user] ADD CONSTRAINT
	DF_user_MemberLevel DEFAULT 1 FOR MemberLevel
GO
COMMIT


/* 步骤2： 对新添加的字段设初始值 */

BEGIN TRANSACTION
UPDATE [Dv_user]
SET UserCredit = 100, UserLevel = 1, UserEnable = 1, UserMsgCount = 0, 
      CreateDate = GETDATE(), ModifyDate = GETDATE(), MemberLevel = 1
GO
COMMIT

/* 步骤3：删除原ICHAT数据库使用的UserInfo表 */
BEGIN TRANSACTION
Drop Table UserInfo
GO
COMMIT

/* 步骤4：创建ICHAT需要的数据库视图UserInfo */
CREATE VIEW dbo.UserInfo
AS
SELECT * 
FROM dbo.[Dv_user]
GO