db.sql

是一个简易的聊天系统

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

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

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

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

CREATE TABLE [dbo].[ChatClass] (
	[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomService] (
	[CustomServiceID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [char] (32) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[TrueName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Title] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ClassID] [int] NOT NULL ,
	[LastActiveTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CustomServiceOnline] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[SessionID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CurrentIP] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CustomServiceID] [int] NOT NULL ,
	[CustomServiceTitle] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[LastActiveTime] [datetime] NOT NULL ,
	[Status] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GuestOnline] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ClientIP] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ClientSessionID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ClientName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ClassID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChatClass] ADD 
	CONSTRAINT [DF_ChatClass_Name] DEFAULT ('') FOR [Name]
GO

ALTER TABLE [dbo].[CustomService] ADD 
	CONSTRAINT [DF_CustomService_TrueName] DEFAULT ('') FOR [TrueName],
	CONSTRAINT [DF_CustomService_Title] DEFAULT ('') FOR [Title],
	CONSTRAINT [DF_CustomService_ClassID] DEFAULT (0) FOR [ClassID],
	CONSTRAINT [DF_CustomService_LastActiveTime] DEFAULT (getdate()) FOR [LastActiveTime]
GO

ALTER TABLE [dbo].[CustomServiceOnline] ADD 
	CONSTRAINT [DF_CustomServiceOnline_SessionID] DEFAULT ('') FOR [SessionID],
	CONSTRAINT [DF_CustomServiceOnline_CurrentIP] DEFAULT ('') FOR [CurrentIP],
	CONSTRAINT [DF_CustomServiceOnline_CustomServiceTitle] DEFAULT ('') FOR [CustomServiceTitle],
	CONSTRAINT [DF_CustomServiceOnline_LastActiveTime] DEFAULT (getdate()) FOR [LastActiveTime],
	CONSTRAINT [DF_CustomServiceOnline_Status] DEFAULT (0) FOR [Status]
GO

ALTER TABLE [dbo].[GuestOnline] ADD 
	CONSTRAINT [DF_GuestOnline_ClientName] DEFAULT ('') FOR [ClientName]
GO


exec sp_addextendedproperty N'MS_Description', N'分类名', N'user', N'dbo', N'table', N'ChatClass', N'column', N'Name'


GO


exec sp_addextendedproperty N'MS_Description', N'处理对应问题分类id', N'user', N'dbo', N'table', N'CustomService', N'column', N'ClassID'
GO
exec sp_addextendedproperty N'MS_Description', N'帐号名', N'user', N'dbo', N'table', N'CustomService', N'column', N'Name'
GO
exec sp_addextendedproperty N'MS_Description', N'密码', N'user', N'dbo', N'table', N'CustomService', N'column', N'Password'
GO
exec sp_addextendedproperty N'MS_Description', N'匿称.与客户交谈时可见. 或者客服编号', N'user', N'dbo', N'table', N'CustomService', N'column', N'Title'
GO
exec sp_addextendedproperty N'MS_Description', N'客服人员真名. 后台可见,与客户交流时不显示', N'user', N'dbo', N'table', N'CustomService', N'column', N'TrueName'


GO


exec sp_addextendedproperty N'MS_Description', N'客服当前ip', N'user', N'dbo', N'table', N'CustomServiceOnline', N'column', N'CurrentIP'
GO
exec sp_addextendedproperty N'MS_Description', N'客服id', N'user', N'dbo', N'table', N'CustomServiceOnline', N'column', N'CustomServiceID'
GO
exec sp_addextendedproperty N'MS_Description', N'客服匿称或编号', N'user', N'dbo', N'table', N'CustomServiceOnline', N'column', N'CustomServiceTitle'
GO
exec sp_addextendedproperty N'MS_Description', N'最后活动时间', N'user', N'dbo', N'table', N'CustomServiceOnline', N'column', N'LastActiveTime'
GO
exec sp_addextendedproperty N'MS_Description', N'客服当前sessionid', N'user', N'dbo', N'table', N'CustomServiceOnline', N'column', N'SessionID'
GO
exec sp_addextendedproperty N'MS_Description', N'状态.0 空闲,1,忙', N'user', N'dbo', N'table', N'CustomServiceOnline', N'column', N'Status'


GO


exec sp_addextendedproperty N'MS_Description', N'客户匿称.若已login则mailaccount,若未login则提示一个input框输入,可输也可不输,若不输入则随机给名为 guestN,随机给的名不在此表记录中重复出现', N'user', N'dbo', N'table', N'GuestOnline', N'column', N'ClientName'


GO