ajaxchat sql2005.sql

a project create chat room in asp.net with c sharp and ajax

		INSERT INTO ChatRooms
			(cr_name, cr_topic, cr_password, cr_maxusers, cr_visible)
		VALUES
			(@Name, @Topic, @Password, @MaxUsers, @Visible)

		SELECT @@Identity

	END
	ELSE
	BEGIN
		UPDATE ChatRooms
		SET cr_name = @Name,
			cr_topic = @Topic,
			cr_password = @Password,
			cr_maxusers = @MaxUsers,
			cr_visible = @Visible
		WHERE cr_id = @Id
	
	END
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteChatRoom]    Script Date: 04/18/2008 17:35:44 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[DeleteChatRoom]
(
	@ID	INT,
	@Name NVARCHAR(100)
)
 AS

DELETE FROM ChatRooms 
WHERE 
	(@ID IS NULL OR cr_id = @ID) AND
	(@Name IS NULL OR cr_name = @Name)
GO
/****** Object:  StoredProcedure [dbo].[FetchChatUsers]    Script Date: 04/18/2008 17:35:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatUsers]
	@Id int = null,
	@Username varchar(100) = null,
	@DisplayName nvarchar(200) = null,
	@Ip varchar(15) = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT
		cu_id as Id,
		cu_username as Username,
		cu_displayname as DisplayName,
		cu_password	as Password,
		cu_gender as Gender,
		cu_begintime as BeginTime,
		cu_lastactive as LastActive,
		cu_ip as Ip,
		cu_lastactiveguid as [Guid],
		cu_thumbimage as ThumbImage
	FROM
		ChatUsers
	WHERE
		(@Id is null OR @Id = cu_id)
		AND (@Username is null OR @Username = cu_username)
		AND (@DisplayName is null OR @DisplayName = cu_displayname)
		AND (@Ip is null OR @Ip = cu_ip)
END
GO
/****** Object:  StoredProcedure [dbo].[SaveChatUser]    Script Date: 04/18/2008 17:35:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveChatUser]
	@Id int = null,
	@Username varchar(100),
	@DisplayName nvarchar(200),
	@Password	char(40),
	@Gender		int,
	@BeginTime datetime = getdate,
	@LastActive datetime = getdate,
	@Ip varchar(15),
	@LastActiveGuid	char(36),
	@ThumbImage varchar(500)
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 ChatUsers
			(cu_username, cu_displayname, cu_password, cu_gender, cu_begintime, cu_lastactive, cu_ip, cu_lastactiveguid, cu_thumbimage)
		VALUES
			(@Username, @DisplayName, @Password, @Gender, @BeginTime, @LastActive, @Ip, @LastActiveGuid, @ThumbImage)

		SELECT @@Identity

	END
	ELSE
	BEGIN

		UPDATE ChatUsers
		SET cu_username = @Username, cu_displayname = @DisplayName,
			cu_password = @Password, cu_gender = @Gender, cu_begintime = @BeginTime,
			cu_lastactive = @LastActive, cu_ip = @Ip, cu_lastactiveguid = @LastActiveGuid,
			cu_thumbimage = @ThumbImage
		WHERE cu_id = @Id
	
	END
END
GO
/****** Object:  StoredProcedure [dbo].[IsRoomAdmin]    Script Date: 04/18/2008 17:35:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IsRoomAdmin]
	@UserId int,
	@ChatRoomId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF EXISTS(SELECT cu_id FROM ChatRoomAdmins WHERE cu_id = @UserId AND cr_id = @ChatRoomId)
		SELECT CAST(1 AS BIT)
	ELSE
		SELECT CAST(0 AS BIT)
END
GO
/****** Object:  StoredProcedure [dbo].[CreateChatRoomAdmin]    Script Date: 04/18/2008 17:35:43 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CreateChatRoomAdmin]
(
	@UserId	int,
	@ChatRoomId int
)
AS
BEGIN
	IF NOT EXISTS(SELECT cu_id FROM ChatRoomAdmins WHERE cu_id = @UserId AND cr_id = @ChatRoomId)
		INSERT INTO ChatRoomAdmins
			(cu_id, cr_id)
		VALUES
			(@UserId, @ChatRoomId)
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteChatRoomAdmin]    Script Date: 04/18/2008 17:35:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteChatRoomAdmin] 
(
	@UserId	int,
	@ChatRoomId int
)
AS

DELETE FROM ChatRoomAdmins WHERE cu_id = @UserId AND cr_id = @ChatRoomId
GO
/****** Object:  StoredProcedure [dbo].[FetchChatRoomAdmins]    Script Date: 04/18/2008 17:35:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatRoomAdmins] 
(
	@ChatRoomId int
)
AS

SELECT cu_id as UserId FROM ChatRoomAdmins WHERE cr_id = @ChatRoomId
GO
/****** Object:  StoredProcedure [dbo].[FetchChatMessages]    Script Date: 04/18/2008 17:35:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatMessages] 
(
	@RoomId	int,
	@FromId	int = null,
	@SenderId	int = null,
	@TargetId	int = null,
	@TargetIdCanBeNull	bit = 0,
	@NotIgnoredById	int = null
)
AS
BEGIN
	SET NOCOUNT ON;

    SELECT top 50
		cm_id	as Id,
		cm_type as [Type],
		cm_time	as [Time],
		cr_id	as RoomId,
		cu_id_sender	as SenderId,
		cm_senderdisplayname	as SenderDisplayName,
		cu_id_target	as TargetId,
		cm_text	as [Text],
		cm_texthtml	as TextHtml
	FROM
		ChatMessages
	WHERE (@RoomId IS NULL OR cr_id = @RoomId)
		AND (@FromId IS NULL OR cm_id > @FromId)
		AND (@SenderId IS NULL OR cu_id_sender = @SenderId)
		AND (@TargetId IS NULL OR cu_id_target = @TargetId OR 
				(@TargetIdCanBeNull = 1 AND cu_id_target IS NULL))
		AND (@NotIgnoredById IS NULL OR NOT EXISTS
				(SELECT 1 FROM ChatIgnoredUsers 
				WHERE cu_id = @NotIgnoredById 
					AND ciu_ignoreduserid = cu_id_sender))
	ORDER BY cm_id DESC
END
GO
/****** Object:  StoredProcedure [dbo].[FetchChatIgnores]    Script Date: 04/18/2008 17:35:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatIgnores] 
(
	@UserId	int,
	@IgnoredUserId	int
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
		cu_id as UserId,
		ciu_ignoreduserid as IgnoredUserId,
		ciu_ignoredate	as IgnoreDate
	FROM
		ChatIgnoredUsers
	WHERE
		(@UserId IS NULL OR cu_id = @UserId) AND
		(@IgnoredUserId IS NULL OR ciu_ignoreduserid = @IgnoredUserId)
END
GO
/****** Object:  StoredProcedure [dbo].[SaveChatIgnore]    Script Date: 04/18/2008 17:35:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveChatIgnore] 
(
	@UserId	int,
	@IgnoredUserId	int,
	@IgnoreDate datetime
)
AS
BEGIN
	IF NOT EXISTS (SELECT 1 FROM ChatIgnoredUsers WHERE cu_id = @UserId AND ciu_ignoreduserid = @IgnoredUserId)
		INSERT INTO ChatIgnoredUsers
			(cu_id, ciu_ignoreduserid, ciu_ignoredate)
		VALUES
			(@UserId, @IgnoredUserId, @IgnoreDate)
	ELSE
		UPDATE ChatIgnoredUsers
		SET
			ciu_ignoredate = @IgnoreDate
		WHERE
			cu_id = @UserId AND ciu_ignoreduserid = @IgnoredUserId
END
GO
/****** Object:  StoredProcedure [dbo].[FetchChatTranslation]    Script Date: 04/18/2008 17:35:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatTranslation] 
(
	@LanguageId	INT = 1,
	@Key		NVARCHAR(400)
)
AS

SELECT
	ct_value as Value
FROM
	ChatTranslations
WHERE
	l_id = @LanguageId AND
	ct_key = @Key
GO
/****** Object:  StoredProcedure [dbo].[SaveChatTranslation]    Script Date: 04/18/2008 17:35:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveChatTranslation] 
(
	@LanguageId	INT = 1,
	@Key		NVARCHAR(400),
	@Value		NVARCHAR(400),
	@ClientSide	BIT
)
AS

IF NOT EXISTS (SELECT ct_key FROM ChatTranslations WHERE l_id = @LanguageId AND ct_key = @Key)
BEGIN
	INSERT INTO ChatTranslations
		(l_id, ct_key, ct_value, ct_clientside)
	VALUES
		(@LanguageId, @Key, @Value, @ClientSide)
END
ELSE
BEGIN
	UPDATE ChatTranslations
	SET ct_value = @Value
	WHERE l_id = @LanguageId AND ct_key = @Key
END
GO
/****** Object:  StoredProcedure [dbo].[FetchClientChatTranslations]    Script Date: 04/18/2008 17:35:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchClientChatTranslations] 
(
	@LanguageId	INT = 1
)
AS

SELECT
	ct_key as [Key],
	ct_value as [Value]
FROM
	ChatTranslations
WHERE
	l_id = @LanguageId AND ct_clientside = 1
GO
/****** Object:  StoredProcedure [dbo].[FetchChatTranslationKeys]    Script Date: 04/18/2008 17:35:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FetchChatTranslationKeys] 
(
	@LanguageId	INT = 1
)
AS

SELECT 
	DISTINCT ct_key as [Key]
FROM
	ChatTranslations
GO
/****** Object:  StoredProcedure [dbo].[UpdateLastOnline]    Script Date: 04/18/2008 17:36:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateLastOnline]
	@Username nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM ChatPresence WHERE cp_username = @Username)
		UPDATE ChatPresence SET cp_lastonline = getdate() WHERE cp_username = @Username
	ELSE
		INSERT INTO ChatPresence VALUES (@Username, getdate())

END
GO
/****** Object:  StoredProcedure [dbo].[GetOnlineUsers]    Script Date: 04/18/2008 17:35:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetOnlineUsers]
	@Username nvarchar(100) = NULL,
	@PresenceTimeout int
AS
BEGIN
	SET NOCOUNT ON;

    SELECT cp_username as Username
	FROM ChatPresence
	WHERE (@Username IS NULL OR cp_username = @Username)
		AND DATEADD(s, @PresenceTimeout, cp_lastonline) > getdate()

END
GO
/****** Object:  StoredProcedure [dbo].[SaveChatMessage]    Script Date: 04/18/2008 17:35:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveChatMessage] 
	@Id int = null,
	@Type int = 0,
	@Time datetime = getdate,
	@ChatRoomId	int = null,
	@SenderUserId int = null,
	@SenderDisplayName nvarchar(100),
	@TargetUserId int = null,
	@Text nvarchar(2000),
	@TextHtml nvarchar(2000)
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 ChatMessages
			(cm_time, cm_type, cr_id, cu_id_sender, cm_senderdisplayname, cu_id_target, cm_text, cm_texthtml)
		VALUES
			(@Time, @Type, @ChatRoomId, @SenderUserId, @SenderDisplayName, @TargetUserId, @Text, @TextHtml)

		SELECT @@Identity

	END
	ELSE
	BEGIN

		UPDATE ChatMessages
		SET cm_type = @Type, cm_time = @Time, cr_id = @ChatRoomId, cu_id_sender = @SenderUserId, 
			cm_senderdisplayname = @SenderDisplayName, cu_id_target = @TargetUserId, cm_text = @Text,
			cm_texthtml = @TextHtml
		WHERE cr_id = @Id
	
	END
END
GO
/****** Object:  StoredProcedure [dbo].[GetChatOnlineUsers]    Script Date: 04/18/2008 17:35:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetChatOnlineUsers]
	@ChatRoomId int,
	@OnlineTimeout int
AS
BEGIN
	SET NOCOUNT ON;

    SELECT cra_userid as UserId,
		cra_roomid as RoomId,
		cra_username as Username,
		cra_displayname as DisplayName,
		cra_lastonline as LastOnline
	FROM ChatRoomActivity
	WHERE (@ChatRoomId IS NULL OR cra_roomid = @ChatRoomId)
		AND DATEADD(s, @OnlineTimeout, cra_lastonline) > getdate()

END
GO
/****** Object:  StoredProcedure [dbo].[SetChatOnlineUser]    Script Date: 04/18/2008 17:35:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SetChatOnlineUser]
	@RoomId int,
	@UserId int,
	@Username varchar(100),
	@DisplayName nvarchar(100),
	@Delete bit = 0
AS
BEGIN
	SET NOCOUNT ON;

	IF (@Delete = 1)
		DELETE FROM ChatRoomActivity 
		WHERE cra_roomid = @RoomId 
			AND cra_userid = @UserId
	ELSE
	IF (EXISTS (SELECT 1 FROM ChatRoomActivity 
				WHERE cra_roomid = @RoomId 
				AND cra_userid = @UserId))
		UPDATE ChatRoomActivity
		SET cra_lastonline = getdate()
		WHERE cra_roomid = @RoomId 
			AND cra_userid = @UserId
	ELSE
		INSERT INTO ChatRoomActivity VALUES
		(@UserId, @RoomId, @Username, @DisplayName, getdate())
END
GO
/****** Object:  ForeignKey [FK_ChatIgnoredUsers_ChatIgnoredUsers]    Script Date: 04/18/2008 17:36:08 ******/
ALTER TABLE [dbo].[ChatIgnoredUsers]  WITH CHECK ADD  CONSTRAINT [FK_ChatIgnoredUsers_ChatIgnoredUsers] FOREIGN KEY([cu_id])
REFERENCES [dbo].[ChatUsers] ([cu_id])
GO
ALTER TABLE [dbo].[ChatIgnoredUsers] CHECK CONSTRAINT [FK_ChatIgnoredUsers_ChatIgnoredUsers]
GO
/****** Object:  ForeignKey [FK_ChatIgnoredUsers_ChatUsers]    Script Date: 04/18/2008 17:36:08 ******/
ALTER TABLE [dbo].[ChatIgnoredUsers]  WITH CHECK ADD  CONSTRAINT [FK_ChatIgnoredUsers_ChatUsers] FOREIGN KEY([ciu_ignoreduserid])
REFERENCES [dbo].[ChatUsers] ([cu_id])
GO
ALTER TABLE [dbo].[ChatIgnoredUsers] CHECK CONSTRAINT [FK_ChatIgnoredUsers_ChatUsers]
GO

-- INSERT DEFAULT ADMIN ACCOUNT (admin/admin) into ChatAdmins table 
INSERT INTO ChatAdmins 
(ca_username,ca_password) 
VALUES 
('admin','D033E22AE348AEB5660FC2140AEC35850C4DA997')
GO 

INSERT INTO [ChatRooms] (
	[cr_name],
	[cr_visible]
) VALUES ( N'Main Chat', 1 ) 
GO