📄 ajaxchat sql2005.sql

📁 a project create chat room in asp.net with c sharp and ajax
💻 SQL
📖 第 1 页 / 共 2 页
字号:
12 下一页
/****** Object:  Table [dbo].[ChatBans]    Script Date: 04/18/2008 17:36:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChatBans](
	[cb_id] [int] IDENTITY(1,1) NOT NULL,
	[cr_id] [int] NULL,
	[cu_id] [int] NULL,
	[cb_ip] [varchar](15) NULL,
	[cb_date] [datetime] NOT NULL CONSTRAINT [DF_ChatBans_cb_date]  DEFAULT (getdate()),
	[cb_dateexpires] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ChatAdmins]    Script Date: 04/18/2008 17:36:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChatAdmins](
	[ca_username] [varchar](20) NOT NULL,
	[ca_password] [varchar](250) NOT NULL,
	[ca_lastlogin] [datetime] NOT NULL CONSTRAINT [DF_ChatAdmins_a_lastlogin]  DEFAULT (getdate()),
	[ca_lastsessionid] [varchar](32) NOT NULL CONSTRAINT [DF_ChatAdmins_a_lastsessionid]  DEFAULT (''),
 CONSTRAINT [PK__Admins] PRIMARY KEY CLUSTERED 
(
	[ca_username] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ChatRooms]    Script Date: 04/18/2008 17:36:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatRooms](
	[cr_id] [int] IDENTITY(1,1) NOT NULL,
	[cr_name] [nvarchar](100) NOT NULL,
	[cr_topic] [nvarchar](250) NOT NULL CONSTRAINT [DF_ChatRooms_cr_topic]  DEFAULT (''),
	[cr_password] [nvarchar](50) NULL,
	[cr_maxusers] [int] NULL,
	[cr_visible] [bit] NOT NULL CONSTRAINT [DF_ChatRooms_cr_visible]  DEFAULT ((1)),
 CONSTRAINT [PK_ChatRooms] PRIMARY KEY CLUSTERED 
(
	[cr_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_ChatRooms] UNIQUE NONCLUSTERED 
(
	[cr_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[SearchChatUsers]    Script Date: 04/18/2008 17:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchChatUsers]
	@Username varchar(100) = null,
	@DisplayName nvarchar(200) = null,
	@Gender	int = null,
	@BeginTimeFrom	datetime = null,
	@BeginTimeTo	datetime = null,
	@LastActiveFrom datetime = null,
	@LastActiveTo	datetime = null,
	@Ip varchar(15) = null,
	@SortColumn		varchar(50)
AS
BEGIN
    SELECT
		cu_id as Id
	FROM ChatUsers
	WHERE
		(@Username IS NULL OR @Username = cu_username)
		AND (@DisplayName IS NULL OR @DisplayName = cu_displayname)
		AND (@Gender IS NULL OR @Gender = cu_gender)
		AND ((@BeginTimeFrom IS NULL AND @BeginTimeTo IS NULL)
			 OR
			((@BeginTimeFrom IS NULL AND @BeginTimeTo IS NOT NULL AND cu_begintime > '1/1/2000') OR cu_begintime > @BeginTimeFrom) AND
			((@BeginTimeTo IS NULL AND @BeginTimeFrom IS NOT NULL AND cu_begintime < '1/1/2100') OR cu_begintime < @BeginTimeTo))
		AND ((@LastActiveFrom IS NULL AND @LastActiveTo IS NULL) 
			 OR
			((@LastActiveFrom IS NULL AND @LastActiveTo IS NOT NULL AND cu_lastactive > '1/1/2000') OR cu_lastactive > @LastActiveFrom) AND
			((@LastActiveTo IS NULL AND @LastActiveFrom IS NOT NULL AND cu_lastactive < '1/1/2100') OR cu_lastactive < @LastActiveTo))
		AND (@Ip IS NULL OR @Ip = cu_ip)
	ORDER BY
		CASE @SortColumn
			WHEN 'Username' THEN cu_username
			WHEN 'IP' THEN cu_ip
		END,
		CASE @SortColumn
			WHEN 'Gender' THEN cu_gender
		END,
		CASE @SortColumn
			WHEN 'BeginTime' THEN cu_begintime
			WHEN 'LastActive' THEN cu_lastactive
		END
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteChatUser]    Script Date: 04/18/2008 17:35:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[DeleteChatUser]
(
	@UserId	int
)
 AS

DELETE FROM ChatUsers WHERE cu_id = @UserId
GO
/****** Object:  Table [dbo].[ChatUsers]    Script Date: 04/18/2008 17:36:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChatUsers](
	[cu_id] [int] IDENTITY(1,1) NOT NULL,
	[cu_username] [varchar](100) NOT NULL,
	[cu_password] [char](40) NULL,
	[cu_displayname] [nvarchar](200) NULL,
	[cu_gender] [int] NULL,
	[cu_begintime] [datetime] NOT NULL CONSTRAINT [DF_ChatUsers_cu_begintime]  DEFAULT (getdate()),
	[cu_lastactive] [datetime] NOT NULL CONSTRAINT [DF_ChatUsers_cu_activetime]  DEFAULT (getdate()),
	[cu_ip] [varchar](15) NULL,
	[cu_lastactiveguid] [char](36) NULL,
	[cu_thumbimage] [varchar](500) NULL,
 CONSTRAINT [PK_ChatUsers] PRIMARY KEY CLUSTERED 
(
	[cu_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ChatRoomAdmins]    Script Date: 04/18/2008 17:36:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatRoomAdmins](
	[cu_id] [int] NOT NULL,
	[cr_id] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ChatSettings]    Script Date: 04/18/2008 17:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChatSettings](
	[cs_key] [varchar](80) NOT NULL,
	[cs_value] [ntext] NOT NULL,
 CONSTRAINT [PK_ChatSettings] PRIMARY KEY CLUSTERED 
(
	[cs_key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ChatTranslations]    Script Date: 04/18/2008 17:36:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatTranslations](
	[ct_key] [nvarchar](400) NOT NULL,
	[l_id] [int] NOT NULL CONSTRAINT [DF_ChatTranslations_l_id]  DEFAULT ((1)),
	[ct_value] [nvarchar](400) NOT NULL,
	[ct_clientside] [bit] NOT NULL,
 CONSTRAINT [PK_ChatTranslations] PRIMARY KEY CLUSTERED 
(
	[ct_key] ASC,
	[l_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ChatPresence]    Script Date: 04/18/2008 17:36:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatPresence](
	[cp_username] [nvarchar](100) NOT NULL,
	[cp_lastonline] [datetime] NOT NULL CONSTRAINT [DF_ChatPresence_cp_lastonline]  DEFAULT (getdate()),
 CONSTRAINT [PK_ChatPresence] PRIMARY KEY CLUSTERED 
(
	[cp_username] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ChatMessages]    Script Date: 04/18/2008 17:36:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatMessages](
	[cm_id] [int] IDENTITY(1,1) NOT NULL,
	[cm_type] [int] NOT NULL CONSTRAINT [DF_ChatMessages_cm_type]  DEFAULT ((0)),
	[cm_time] [datetime] NOT NULL,
	[cr_id] [int] NULL,
	[cu_id_sender] [int] NULL,
	[cm_senderdisplayname] [nvarchar](100) NOT NULL,
	[cu_id_target] [int] NULL,
	[cm_text] [nvarchar](2000) NOT NULL,
	[cm_texthtml] [nvarchar](2000) NOT NULL,
 CONSTRAINT [PK_ChatMessages] PRIMARY KEY CLUSTERED 
(
	[cm_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ChatRoomActivity]    Script Date: 04/18/2008 17:36:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChatRoomActivity](
	[cra_userid] [int] NOT NULL,
	[cra_roomid] [int] NOT NULL,
	[cra_username] [varchar](100) NOT NULL,
	[cra_displayname] [nvarchar](100) NOT NULL,
	[cra_lastonline] [datetime] NOT NULL CONSTRAINT [DF_ChatRoomActivity_cra_lastonline]  DEFAULT (getdate()),
 CONSTRAINT [PK_ChatRoomActivity] PRIMARY KEY CLUSTERED 
(
	[cra_userid] ASC,
	[cra_roomid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ChatIgnoredUsers]    Script Date: 04/18/2008 17:36:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChatIgnoredUsers](
	[cu_id] [int] NOT NULL,
	[ciu_ignoreduserid] [int] NOT NULL,
	[ciu_ignoredate] [datetime] NOT NULL,
 CONSTRAINT [PK_ChatIgnoredUsers] PRIMARY KEY CLUSTERED 
(
	[cu_id] ASC,
	[ciu_ignoreduserid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[FetchChatBans]    Script Date: 04/18/2008 17:35:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatBans]
	@Id int = null,
	@ChatRoomId int = null,
	@UserId int = null,
	@UserIp varchar(15) = null,
	@ExpiresAfter datetime = getdate
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT
		cb_id as Id,
		cr_id as ChatRoomId,
		cu_id as UserId,
		cb_ip as UserIp,
		cb_date as BanDate,
		cb_dateexpires as BanExpires
	FROM
		ChatBans
	WHERE
		(@Id is null OR @Id = cu_id)
		AND (@ChatRoomId is null or @ChatRoomId = cr_id)
		AND (@UserId is null or @UserId = cu_id)
		AND (@UserIp is null or @UserIp = cb_ip)
		AND (@ExpiresAfter is null or cb_dateexpires is null or @ExpiresAfter < cb_dateexpires)
END
GO
/****** Object:  StoredProcedure [dbo].[SaveChatBan]    Script Date: 04/18/2008 17:35:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveChatBan]
	@Id int = null,
	@ChatRoomId int = null,
	@UserId int = null,
	@UserIp varchar(15) = null,
	@BanDate datetime = getdate,
	@BanExpires datetime = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    IF (@Id IS NULL)
	BEGIN

		INSERT INTO ChatBans
			(cr_id, cu_id, cb_ip, cb_date, cb_dateexpires)
		VALUES
			(@ChatRoomId, @UserId, @UserIp, @BanDate, @BanExpires)

		SELECT @@Identity

	END
	ELSE
	BEGIN

		UPDATE ChatBans
		SET cr_id = @ChatRoomId, cu_id = @UserId, cb_ip = @UserIp, cb_date = @BanDate,
			cb_dateexpires = @BanExpires
		WHERE cb_id = @Id
	
	END
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateChatAdminLastLogin]    Script Date: 04/18/2008 17:35:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateChatAdminLastLogin]
(
	@Username	VARCHAR(20),
	@SessionID	VARCHAR(32)
)
AS
	UPDATE ChatAdmins
	SET
		ca_lastlogin = GETDATE(),
		ca_lastsessionid = @SessionID
	WHERE
		ca_username = @Username
GO
/****** Object:  StoredProcedure [dbo].[CreateChatAdmin]    Script Date: 04/18/2008 17:35:43 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CreateChatAdmin]
(
	@Username	VARCHAR(20),
	@Password	VARCHAR(250)
)
AS
	INSERT INTO ChatAdmins
		(ca_username, ca_password)
	VALUES
		(@Username, @Password)
GO
/****** Object:  StoredProcedure [dbo].[LoadChatAdmin]    Script Date: 04/18/2008 17:35:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadChatAdmin]
(
	@Username varchar(20)
)
AS
	SELECT 
		ca_username as Username,
		ca_password as Password,
		ca_lastlogin as LastLogin,
		ca_lastsessionid as LastSessionID
	FROM
		ChatAdmins
	WHERE
		@Username = ca_username
GO
/****** Object:  StoredProcedure [dbo].[UpdateChatAdmin]    Script Date: 04/18/2008 17:35:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateChatAdmin]
(
	@Username	VARCHAR(20),
	@Password	VARCHAR(250)
)
 AS
	UPDATE ChatAdmins
	SET
		ca_password = @Password,
		ca_lastlogin = getdate()
	WHERE
		ca_username = @Username
GO
/****** Object:  StoredProcedure [dbo].[FetchChatAdmins]    Script Date: 04/18/2008 17:35:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[FetchChatAdmins]
(
	@Username	VARCHAR(20) = null
)
AS
	SELECT
		ca_password as Password,
		ca_username as Username,
		ca_lastlogin as LastLogin
	FROM 
		ChatAdmins
	WHERE
		@Username = null OR @Username = ca_username
GO
/****** Object:  StoredProcedure [dbo].[DeleteChatAdmin]    Script Date: 04/18/2008 17:35:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[DeleteChatAdmin]
(
	@Username	VARCHAR(20)
)
 AS

DELETE FROM ChatAdmins WHERE ca_username = @Username
GO
/****** Object:  StoredProcedure [dbo].[FetchChatSetting]    Script Date: 04/18/2008 17:35:48 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatSetting]
(
	@Key	varchar(80)
)
 AS
	SELECT cs_value FROM ChatSettings WHERE @Key = cs_key
GO
/****** Object:  StoredProcedure [dbo].[UpdateChatSetting]    Script Date: 04/18/2008 17:35:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateChatSetting]
(
	@Key	varchar(80),
	@Value ntext
)
 AS
	IF (EXISTS(SELECT * FROM ChatSettings WHERE @Key = cs_key))
	UPDATE ChatSettings
	SET
		cs_value = @Value
	WHERE
		@Key = cs_key
	ELSE
	INSERT INTO ChatSettings
		(cs_key, cs_value)
	VALUES
		(@Key, @Value)
GO
/****** Object:  StoredProcedure [dbo].[FetchChatRooms]    Script Date: 04/18/2008 17:35:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatRooms] 
(
	@ID	INT,
	@Name	NVARCHAR(100)
)
AS
BEGIN
	SET NOCOUNT ON;

    SELECT 
		cr_id		as Id,
		cr_name		as Name,
		cr_topic	as Topic,
		cr_password as Password,
		cr_maxusers as MaxUsers,
		cr_visible	as Visible
	FROM ChatRooms
	WHERE
		(@ID IS NULL OR @ID = cr_id) AND
		(@Name IS NULL OR @Name = cr_name)
END
GO
/****** Object:  StoredProcedure [dbo].[SaveChatRoom]    Script Date: 04/18/2008 17:35:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveChatRoom]
	@Id int = null,
	@Name nvarchar(100),
	@Topic nvarchar(250),
	@Password nvarchar(50),
	@MaxUsers int,
	@Visible bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    IF (@Id IS NULL)
	BEGIN
12 下一页
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -