chat.sql

来自「简单的聊天程序.」· SQL 代码 · 共 309 行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MESSAGES_ACTIONS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MESSAGES] DROP CONSTRAINT FK_MESSAGES_ACTIONS
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ACTIVITY_HANDLES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ACTIVITY] DROP CONSTRAINT FK_ACTIVITY_HANDLES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MESSAGES_HANDLES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MESSAGES] DROP CONSTRAINT FK_MESSAGES_HANDLES
GO

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[ACTIONS] (
	[action_id] [int] IDENTITY (1, 1) NOT NULL ,
	[action_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[action_descr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACTIVITY] (
	[handle_id] [int] NULL ,
	[action_time] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HANDLES] (
	[handle_id] [int] IDENTITY (1, 1) NOT NULL ,
	[handle] [nvarchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MESSAGES] (
	[msg_id] [int] IDENTITY (1, 1) NOT NULL ,
	[message] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[action_id] [int] NULL ,
	[handle_id] [int] NULL ,
	[action_time] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTIONS] WITH NOCHECK ADD 
	CONSTRAINT [PK_ACTIONS] PRIMARY KEY  CLUSTERED 
	(
		[action_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[HANDLES] WITH NOCHECK ADD 
	CONSTRAINT [PK_HANDLES] PRIMARY KEY  CLUSTERED 
	(
		[handle_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[MESSAGES] WITH NOCHECK ADD 
	CONSTRAINT [PK_MESSAGES] PRIMARY KEY  CLUSTERED 
	(
		[msg_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ACTIVITY] ADD 
	CONSTRAINT [FK_ACTIVITY_HANDLES] FOREIGN KEY 
	(
		[handle_id]
	) REFERENCES [dbo].[HANDLES] (
		[handle_id]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[MESSAGES] ADD 
	CONSTRAINT [FK_MESSAGES_ACTIONS] FOREIGN KEY 
	(
		[action_id]
	) REFERENCES [dbo].[ACTIONS] (
		[action_id]
	) ON DELETE CASCADE  NOT FOR REPLICATION ,
	CONSTRAINT [FK_MESSAGES_HANDLES] FOREIGN KEY 
	(
		[handle_id]
	) REFERENCES [dbo].[HANDLES] (
		[handle_id]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE dbo.do_login (@handle varchar(63)) AS

DECLARE @handle_id int
DECLARE @new_message_xml varchar(2000)

SET NOCOUNT ON
IF EXISTS(
	SELECT *
	FROM HANDLES
	WHERE HANDLES.handle = @handle
)
	SELECT '<error><number>1</number><description>This handle already exists and is in use.</description></error>'
ELSE
BEGIN
	INSERT INTO HANDLES (handle)
	VALUES (@handle)

	SET @handle_id = @@IDENTITY

	INSERT INTO ACTIVITY
	VALUES (
		@handle_id,
		GETDATE()
	)

	SET @new_message_xml = '<MESSAGES><action_id>1</action_id><message>has just logged in.</message><handle_id>' + CAST(@handle_id AS varchar) + '</handle_id></MESSAGES>'
	EXEC new_message @new_message_xml

	SELECT *
	FROM HANDLES
	WHERE HANDLES.handle = @handle
	FOR XML AUTO, ELEMENTS
END
SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE dbo.do_logout (@xml_param varchar(2000)) AS

DECLARE @new_message_xml varchar(2000)
DECLARE @handle_id int
DECLARE @handle varchar(63)
DECLARE @i_xml_param int

SET NOCOUNT ON

EXEC sp_xml_preparedocument @i_xml_param OUTPUT, @xml_param

SELECT @handle_id = handle_id
FROM OPENXML(@i_xml_param, '//MESSAGES', 2)
WITH (	handle_id int	)

EXEC sp_xml_removedocument @i_xml_param

SELECT @handle = handle
FROM HANDLES
WHERE handle_id = @handle_id

DELETE FROM HANDLES
WHERE handle_id = @handle_id

SET @new_message_xml = '<MESSAGES><action_id>4</action_id><message>' + @handle + ' has just logged out.</message><handle_id>1</handle_id></MESSAGES>'
EXEC new_message @new_message_xml
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE dbo.get_handles AS

SELECT HANDLES.handle_id, HANDLES.handle
FROM HANDLES
INNER JOIN ACTIVITY
ON ACTIVITY.handle_id = HANDLES.handle_id
WHERE HANDLES.handle_id > 1
FOR XML AUTO, ELEMENTS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE dbo.get_messages (@handle_id int) AS

DECLARE @action_time datetime

SET NOCOUNT ON

SET @action_time = GETDATE()

SELECT
	MESSAGES.msg_id,
	CASE WHEN MESSAGES.action_id = 2 THEN
		HANDLES.handle + ': ' + MESSAGES.message 
	WHEN MESSAGES.action_id = 1 OR MESSAGES.action_id = 3 THEN
		HANDLES.handle + ' ' + MESSAGES.message
	ELSE
		MESSAGES.message
	END AS message,
	MESSAGES.action_id,
	MESSAGES.handle_id
FROM MESSAGES
INNER JOIN HANDLES
ON HANDLES.handle_id = MESSAGES.handle_id
WHERE MESSAGES.action_time > (
	SELECT action_time
	FROM ACTIVITY
	WHERE handle_id = @handle_id
) AND MESSAGES.action_time <= @action_time
FOR XML AUTO, ELEMENTS

UPDATE ACTIVITY
SET action_time = @action_time
WHERE handle_id = @handle_id

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE dbo.new_message (@xml_param varchar(2000)) AS

DECLARE @i_xml_param int

EXEC sp_xml_preparedocument @i_xml_param OUTPUT, @xml_param

INSERT INTO MESSAGES (
	message,	
	action_id,
	handle_id,
	action_time	)
SELECT
	message,	
	action_id,
	handle_id,
	GETDATE()
FROM OPENXML(@i_xml_param, '//MESSAGES', 2)
WITH (	message varchar(255),
	action_id int,
	handle_id int	)

EXEC sp_xml_removedocument @i_xml_param

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO