Table – DiM-Messages

Purpose

This table is used to decouple message being passed between the trellispark framework and external systems.

Outbound messages are added to the DiM-Messages table by workflow within the framework. When the external system is available, they are transferred by a decoupled service.

Inbound messages are read by decoupled services from external systems and added to the DiM-Messages table. Messages are picked up as required by framework workflow from the DiM-Messages table.

If the transfer is successful, then the result is written to the DiM-MessageHistory table.

If the transfer fails, then the result is written to the DiM-MessageFailures table.

Definition

CREATE TABLE [dbo].[DiM-Messages](
	[MessageGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[QueuedOn] [DATETIME2](7) NOT NULL,
	[MessageQueueGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[Sender] [VARCHAR](500) NULL,
	[Recipient] [VARCHAR](500) NULL,
	[Subject] [VARCHAR](500) NULL,
	[BodyFormat] [VARCHAR](10) NULL,
	[BodyContent] [VARCHAR](MAX) NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
	[MessageGUID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[DiM-Messages] ADD  CONSTRAINT [DF_Messages_MessageGUID]  DEFAULT (NEWID()) FOR [MessageGUID]
GO

ALTER TABLE [dbo].[DiM-Messages] ADD  CONSTRAINT [DF_Messages_QueuedOn]  DEFAULT (SYSUTCDATETIME()) FOR [QueuedOn]
GO

Column Definition

A unique MessageGUID is assigned to each message when it is added to the table.

QueuedOn records the timestamp when the message was added to the table.

MessageQueueGUID is the InstanceGUID of a Message Queue Instance record which details the configuration of the external system, including direction of message flow. This configuration is part of the Workspace Owner application’s Service configuration.

Sender details who is sending the message (if required).

Recipient details who is the intended recipient of the message (if required).

Subject contains the subject text for the message (if required).

BodyFormat indicates how the BodyContent field should be interpreted.

BodyContent contains the actual body of the message to be transferred.

Key Functionality

Messages are added to the DiM-Messages table using the GI_SendMessage TSQL Stored Procedure.

This procedure uses the @MessageQueueName to determine the @MessageQueueGUID that will be used to transfer the message.

CREATE PROCEDURE [dbo].[GI_SendMessage]
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@MessageGUID AS UNIQUEIDENTIFIER,
	@MessageQueueName AS VARCHAR(500),
	@Sender AS VARCHAR(500),
	@Recipient AS VARCHAR(500),
	@Subject AS VARCHAR(500),
	@BodyFormat AS VARCHAR(10),
	@BodyContent AS VARCHAR(MAX)
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	BEGIN TRY

		DECLARE @ReturnStatus AS INTEGER = 0
		DECLARE @MessageQueueGUID AS UNIQUEIDENTIFIER = 0x0

		-- Try to validate the session data...
		EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
		IF @ReturnStatus > 0
		BEGIN
		
			-- Log and raise the error...
			EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_SendMessage - Invalid session', 1, NULL
			RAISERROR ('DB.GI_SendMessage - Invalid session.', 16, 1)
			RETURN 100 + @ReturnStatus
			
		END

		SELECT @MessageQueueGUID=InstanceGUID FROM vw_ZSO_MessageQueue WITH (NOLOCK) WHERE @MessageQueueName=InstanceName

		IF @MessageQueueGUID = CAST(0x0 AS UNIQUEIDENTIFIER)
			BEGIN
				EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_SendMessage - Invalid message queue name', 1, NULL
				RAISERROR ('DB.GI_SendMessage - Invalid message queue name.', 16, 1)
				RETURN 100 + @ReturnStatus
			END
		ELSE
			BEGIN
				INSERT INTO [dbo].[DiM-Messages] (MessageGUID, MessageQueueGUID, Sender, Recipient, [Subject], BodyFormat, BodyContent)
				VALUES (@MessageGUID, @MessageQueueGUID, @Sender, @Recipient, @Subject, @BodyFormat, @BodyContent)
			END

	END TRY
	BEGIN CATCH
			DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
			EXECUTE dbo.GI_LogEvent @UserGUID, 'DB.GI_SendMessage - Unknown error', 1, @ErrorMessage
			RAISERROR ('DB.GI_SendMessage - Unknown error.', 16, 1)
	END CATCH

END

Messages are read from the top of DiM-Messages table one at a time, in First-In, First-Out sequence using the GI_ReadMessage TSQL Stored Procedure.

This procedure uses the @MessageQueueName to determine the @MessageQueueGUID that will be used to transfer the message.

CREATE PROCEDURE [dbo].[GI_ReadMessage]
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@MessageQueueName AS VARCHAR(500),
	@MessageGUID AS UNIQUEIDENTIFIER OUTPUT,
	@Sender AS VARCHAR(500) OUTPUT,
	@Recipient AS VARCHAR(500) OUTPUT,
	@Subject AS VARCHAR(500) OUTPUT,
	@BodyFormat AS VARCHAR(10) OUTPUT,
	@BodyContent AS VARCHAR(MAX) OUTPUT
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	BEGIN TRY

		DECLARE @ReturnStatus AS INTEGER = 0
		DECLARE @MessageQueueGUID AS UNIQUEIDENTIFIER = 0x0

		-- Try to validate the session data...
		EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
		IF @ReturnStatus > 0
		BEGIN
		
			-- Log and raise the error...
			EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_ReadMessage - Invalid session', 1, NULL
			RAISERROR ('DB.GI_ReadMessage - Invalid session.', 16, 1)
			RETURN 100 + @ReturnStatus
			
		END

		SELECT @MessageQueueGUID=InstanceGUID FROM vw_ZSO_MessageQueue WITH (NOLOCK) WHERE @MessageQueueName=InstanceName

		IF @MessageQueueGUID = CAST(0x0 AS UNIQUEIDENTIFIER)
			BEGIN
				EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_ReadMessage - Invalid message queue name', 1, NULL
				RAISERROR ('DB.GI_ReadMessage - Invalid message queue name.', 16, 1)
				RETURN 100 + @ReturnStatus
			END
		ELSE
			BEGIN
				SELECT TOP 1 @MessageGUID = MessageGUID, @Sender = Sender, @Recipient = Recipient,
								 @Subject = [Subject], @BodyFormat = BodyFormat, @BodyContent = BodyContent
				FROM [dbo].[DiM-Messages] WITH (NOLOCK)
				WHERE MessageQueueGUID = @MessageQueueGUID
				ORDER BY QueuedOn DESC
			END

	END TRY
	BEGIN CATCH
			DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
			EXECUTE dbo.GI_LogEvent @UserGUID, 'DB.GI_ReadMessage - Unknown error', 1, @ErrorMessage
			RAISERROR ('DB.GI_ReadMessage - Unknown error.', 16, 1)
	END CATCH

END

Once a message has been processed it is removed from the DiM-Messages table using the GI_ProcessedMessage TSQL Stored Procedure and transferred to either the DiM_MessageHistory or DiM_MessageFailures table depending upon the result.

CREATE PROCEDURE [dbo].[GI_ProcessedMessage]
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@InstanceGUID AS UNIQUEIDENTIFIER,
	@InputString AS VARCHAR(MAX)
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	BEGIN TRY

		DECLARE @ReturnStatus AS INTEGER = 0
		-- Try to validate the session data...
		EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
		IF @ReturnStatus > 0
		BEGIN
		
			-- Log and raise the error...
			EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_ProcessedMessage - Invalid session', 1, NULL
			RAISERROR ('DB.GI_ProcessedMessage - Invalid session.', 16, 1)
			RETURN 100 + @ReturnStatus
			
		END

		IF UPPER(@InputString) = 'SUCCESS'
			BEGIN
				-- Move the original message into the history table...
				INSERT INTO [dbo].[DiM-MessageHistory] (MessageGUID, QueuedOn, MessageQueueGUID, Sender, Recipient, [Subject], Result)
				SELECT MessageGUID, QueuedOn, MessageQueueGUID, Sender, Recipient, [Subject], @InputString
				FROM [dbo].[DiM-Messages] WITH (NOLOCK)
				WHERE MessageGUID = @InstanceGUID
			END
		ELSE
			BEGIN
			-- Move the original message into the failure table...
				INSERT INTO [dbo].[DiM-MessageFailures] (MessageGUID, QueuedOn, MessageQueueGUID, Sender, Recipient, [Subject], BodyFormat, BodyContent, Result, ProcessedOn)
				SELECT MessageGUID, QueuedOn, MessageQueueGUID, Sender, Recipient, [Subject], BodyFormat, BodyContent, @InputString, SYSUTCDATETIME()
				FROM [dbo].[DiM-Messages] WITH (NOLOCK)
				WHERE MessageGUID = @InstanceGUID
			END

		-- Remove the message from the Messages table...
		DELETE FROM [dbo].[DiM-Messages] WHERE MessageGUID = @InstanceGUID -- Just to make really sure that the message has be removed completely!

	END TRY
	BEGIN CATCH
			DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
			EXECUTE dbo.GI_LogEvent @UserGUID, 'DB.GI_ProcessedMessage - Unknown error', 1, @ErrorMessage
			RAISERROR ('DB.GI_ProcessedMessage - Unknown error.', 16, 1)
	END CATCH

END

Updated on November 24, 2022

Was this article helpful?

Related Articles

Need Support?
Can’t find the answer you’re looking for? Don’t worry we’re here to help!
Contact Support

Leave a Comment