table.sql

聊天室简单实用美观的小型语音视频聊天室程序

/******
本文件包括三类脚本：
	１、iChat 用户资料库建表脚本
	２、管理入口的管理帐号建表需要的脚本
	３、头像商店2.0版建表需要的脚本
******/

/****** iChat 用户资料建表脚本　　建议在SQL 2000下使用　不推荐使用SQL 7******/
CREATE TABLE [dbo].[UserInfo] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [varchar] (20) COLLATE Chinese_PRC_BIN NOT NULL ,
	[UserPassword] [varchar] (20) NOT NULL ,
	[UserSex] [int] NOT NULL ,
	[UserCredit] [int] NOT NULL ,
	[UserLevel] [int] NOT NULL ,
	[UserEnable] [int] NOT NULL ,
	[UserIcon] [varchar] (255) NULL ,
	[UserArming] [varchar] (255) NULL ,
	[UserEmail] [varchar] (50) NULL ,
	[UserOicq] [varchar] (12) NULL ,
	[UserPhoto] [varchar] (255) NULL ,
	[UserResume] [varchar] (255) NULL ,
	[UserQuestion] [varchar] (50) NULL ,
	[UserAnswer] [varchar] (50) NULL ,
	[UserLastIP] [varchar] (15) NULL ,
	[UserMsgCount] [int] NULL ,
	[CreateDate] [datetime] NULL ,
	[ModifyDate] [datetime] NULL ,
	[MemberLevel] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserInfo] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserInfo] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO


ALTER TABLE [dbo].[UserInfo] WITH NOCHECK ADD 
	CONSTRAINT [DF_IChatUserInfo_UserSex] DEFAULT (2) FOR [UserSex],
	CONSTRAINT [DF_IChatUserInfo_UserCredit] DEFAULT (1) FOR [UserCredit],
	CONSTRAINT [DF_IChatUserInfo_UserLevel] DEFAULT (1) FOR [UserLevel],
	CONSTRAINT [DF_IChatUserInfo_UserEnable] DEFAULT (1) FOR [UserEnable],
	CONSTRAINT [DF_UserInfo_UserMsgCount] DEFAULT (0) FOR [UserMsgCount],
	CONSTRAINT [DF_IChatUserInfo_CreateDate] DEFAULT (getdate()) FOR [CreateDate],
	CONSTRAINT [DF_IChatUserInfo_ModifyDate] DEFAULT (getdate()) FOR [ModifyDate],
	CONSTRAINT [DF_UserInfo_MemberLevel] DEFAULT (1) FOR [MemberLevel],
	CONSTRAINT [IX_UserInfo] UNIQUE  NONCLUSTERED 
	(
		[UserName]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_UserInfo_1] ON [dbo].[UserInfo]([UserCredit]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_UserInfo_2] ON [dbo].[UserInfo]([UserLevel]) ON [PRIMARY]
GO

exec sp_addextendedproperty N'MS_Description', N'记录创建日期', N'user', N'dbo', N'table', N'UserInfo', N'column', N'CreateDate'
GO
exec sp_addextendedproperty N'MS_Description', N'会员级别', N'user', N'dbo', N'table', N'UserInfo', N'column', N'MemberLevel'
GO
exec sp_addextendedproperty N'MS_Description', N'最后访问日期', N'user', N'dbo', N'table', N'UserInfo', N'column', N'ModifyDate'
GO
exec sp_addextendedproperty N'MS_Description', N'提示问题的答案', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserAnswer'
GO
exec sp_addextendedproperty N'MS_Description', N'用户徽章', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserArming'
GO
exec sp_addextendedproperty N'MS_Description', N'用户积分', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserCredit'
GO
exec sp_addextendedproperty N'MS_Description', N'用户EMAIL地址', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserEmail'
GO
exec sp_addextendedproperty N'MS_Description', N'是否允许登录', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserEnable'
GO
exec sp_addextendedproperty N'MS_Description', N'用户头像', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserIcon'
GO
exec sp_addextendedproperty N'MS_Description', N'User ID 此值在ICHAT主程序中不使用', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserID'
GO
exec sp_addextendedproperty N'MS_Description', N'最后一次访问时的IP', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserLastIP'
GO
exec sp_addextendedproperty N'MS_Description', N'用户级别', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserLevel'
GO
exec sp_addextendedproperty N'MS_Description', N'用户短消息记数器(备用字段)', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserMsgCount'
GO
exec sp_addextendedproperty N'MS_Description', N'姓名', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserName'
GO
exec sp_addextendedproperty N'MS_Description', N'密码', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserPassword'
GO
exec sp_addextendedproperty N'MS_Description', N'照片路径', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserPhoto'
GO
exec sp_addextendedproperty N'MS_Description', N'提示问题', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserQuestion'
GO
exec sp_addextendedproperty N'MS_Description', N'用户自我介绍', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserResume'
GO
exec sp_addextendedproperty N'MS_Description', N'性别   2=保密', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserSex'


GO



/* 管理入口的管理帐号建表需要的脚本 */
CREATE TABLE [dbo].[admin] (
	[username] [varchar] (50) NULL ,
	[pass] [varchar] (50) NULL 
) ON [PRIMARY]
GO
/* 缺省的管理帐号: admin 密码：ichat　系统安装成功后请注意立即修改*/
INSERT INTO admin VALUES('admin','ichat')


/* 以下为头像商店2.0版建表需要的脚本 */
CREATE TABLE [dbo].[Products] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductName] [varchar] (50) NOT NULL ,
	[ProductHtml] [varchar] (255) NOT NULL ,
	[Facies] [varchar] (255) NOT NULL ,
	[Type] [int] NOT NULL ,
	[Quantity] [int] NULL ,
	[Price] [int] NULL ,
	[Sells] [int] NULL ,
	[ProductLevel] [int] NULL ,
	[Memo] [varchar] (255) NULL ,
	[CreateDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserBox] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[ProductName] [varchar] (50) NOT NULL ,
	[ProductHtml] [varchar] (255) NOT NULL ,
	[Facies] [varchar] (255) NOT NULL ,
	[Type] [int] NOT NULL ,
	[Quantity] [int] NULL ,
	[Price] [int] NULL ,
	[UserLevel] [int] NULL ,
	[Memo] [varchar] (255) NULL ,
	[CreateDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[message] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[sender] [varchar] (50) NULL ,
	[incept] [varchar] (50) NULL ,
	[title] [varchar] (100) NULL ,
	[content] [varchar] (1000) NULL ,
	[flag] [int] NULL ,
	[sendtime] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] WITH NOCHECK ADD 
	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserBox] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserBox] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[message] WITH NOCHECK ADD 
	CONSTRAINT [PK_message] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Products] WITH NOCHECK ADD 
	CONSTRAINT [DF_Products_Sells] DEFAULT (0) FOR [Sells],
	CONSTRAINT [DF_Products_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[UserBox] WITH NOCHECK ADD 
	CONSTRAINT [DF_UserBox_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[message] WITH NOCHECK ADD 
	CONSTRAINT [DF_message_flag] DEFAULT (1) FOR [flag],
	CONSTRAINT [DF_message_sendtime] DEFAULT (getdate()) FOR [sendtime]
GO