/*

Service Broker

*/

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'R10')
  DROP DATABASE R10;
GO

CREATE DATABASE R10;
GO

ALTER DATABASE R10 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE R10 SET ENABLE_BROKER;
ALTER DATABASE R10 SET MULTI_USER;
GO

ALTER AUTHORIZATION ON DATABASE::R10 TO sa;
GO

USE R10;
GO
CREATE MESSAGE TYPE [http://helion.pl/SQL2008/R10/RequestMessage]
VALIDATION = WELL_FORMED_XML;
GO
CREATE MESSAGE TYPE [http://helion.pl/SQL2008/R10/ResponseMessage]
VALIDATION = WELL_FORMED_XML;
GO

USE R10;
GO
SELECT * FROM sys.service_message_types WHERE name LIKE N'%helion.pl%';
GO

USE R10;
GO
CREATE CONTRACT [http://helion.pl/SQL2008/R10/OrderContract]
(
  [http://helion.pl/SQL2008/R10/RequestMessage] SENT BY INITIATOR,
  [http://helion.pl/SQL2008/R10/ResponseMessage] SENT BY TARGET
);
GO

USE R10;
GO
SELECT 
  c.name AS contract_name, 
  u.message_type_id,
  u.is_sent_by_initiator AS sent_by_initiator,
  u.is_sent_by_target AS sent_by_target  
FROM sys.service_contracts AS c
INNER JOIN sys.service_contract_message_usages AS u
ON c.service_contract_id = u.service_contract_id
WHERE c.name LIKE N'%helion.pl%';
GO

USE R10;
GO
CREATE QUEUE dbo.InitiatorQueue
WITH STATUS = ON;
GO
CREATE QUEUE dbo.TargetQueue
WITH STATUS = ON;
GO

USE R10;
GO
SELECT name, is_enqueue_enabled 
FROM sys.service_queues WHERE is_ms_shipped = 0;
GO

USE R10;
GO
CREATE SERVICE InitiatorService
ON QUEUE dbo.InitiatorQueue(
  [http://helion.pl/SQL2008/R10/OrderContract]
);
GO
CREATE SERVICE TargetService
ON QUEUE dbo.TargetQueue (
  [http://helion.pl/SQL2008/R10/OrderContract]
);
GO

USE R10;
GO
SELECT s.name, q.name
FROM sys.services AS s
INNER JOIN sys.service_queues AS q
ON s.service_queue_id = q.[object_id];
GO

USE R10;
GO
CREATE ROUTE RemoteServiceRoute
  WITH
  SERVICE_NAME = 'RemoteService',
  ADDRESS = 'TCP://RemoteServer:1234';
GO

USE R10;
GO
SELECT name, address FROM sys.routes;
GO

USE R10;
GO
CREATE BROKER PRIORITY HigherPriority
    FOR CONVERSATION
    SET (CONTRACT_NAME = [http://helion.pl/SQL2008/R10/OrderContract],
         LOCAL_SERVICE_NAME = InitiatorService,
         REMOTE_SERVICE_NAME = 'TargetService',
         PRIORITY_LEVEL = 7);
GO

USE R10;
GO
BEGIN TRAN;
	DECLARE @handle UNIQUEIDENTIFIER;
	DECLARE @message NVARCHAR(MAX);

	BEGIN DIALOG CONVERSATION @handle
		FROM SERVICE [InitiatorService]
		TO SERVICE 'TargetService'
		ON CONTRACT [http://helion.pl/SQL2008/R10/OrderContract]
		WITH ENCRYPTION = OFF;

	SET @message = '<OrderRequest>Zamawiam!</OrderRequest>';

	SEND ON CONVERSATION @handle 
	MESSAGE TYPE [http://helion.pl/SQL2008/R10/RequestMessage] (@message);
COMMIT TRAN;
GO

USE R10;
GO
DECLARE @r_handle UNIQUEIDENTIFIER;
DECLARE @r_message NVARCHAR(100);
DECLARE @r_message_type SYSNAME;
BEGIN TRAN;
  WAITFOR
  ( RECEIVE TOP(1)
      @r_handle = conversation_handle,
      @r_message = message_body,
      @r_message_type = message_type_name
    FROM dbo.TargetQueue
  ), TIMEOUT 1000;
  SELECT @r_message, @r_message_type;
	END CONVERSATION @r_handle;
COMMIT TRAN;
GO

USE R10;
GO

CREATE PROC dbo.uspProcessRequestMessages
AS
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @message_type NVARCHAR(256);
DECLARE @message XML;
DECLARE @response XML;

WHILE (1=1)
BEGIN
  BEGIN TRY;
    BEGIN TRANSACTION;
	  
    WAITFOR
    ( RECEIVE TOP(1)
        @handle = conversation_handle,
        @message = message_body,
        @message_type = message_type_name
      FROM dbo.TargetQueue
    ), TIMEOUT 6000;
    
    IF @@ROWCOUNT = 0
    BEGIN
      ROLLBACK TRAN;
      BREAK;
    END;
    
    IF @message_type = N'http://helion.pl/SQL2008/R10/RequestMessage'
    BEGIN
      INSERT INTO dbo.Orders (OrderID, OrderText, ServiceName)
      VALUES (NEWID(), @message, N'TargetService')
      
      SET @response = N'<OrderResponse>Order "' + 
                      @message.value('/OrderRequest[1]', 'NVARCHAR(max)') +
                      '" has been accepted</OrderResponse>';
                      
      SEND ON CONVERSATION @handle
      MESSAGE TYPE [http://helion.pl/SQL2008/R10/ResponseMessage]
      (
        @message
      );
      
      END CONVERSATION @handle;                    
    END;
    
    IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
      END CONVERSATION @handle;
    END
    
    COMMIT TRAN;
  END TRY
  BEGIN CATCH;
    ROLLBACK TRAN;
  END CATCH;
END;
GO

USE R10;
GO
ALTER QUEUE dbo.TargetQueue
WITH ACTIVATION
(
  STATUS = ON,
  PROCEDURE_NAME = dbo.uspProcessRequestMessages,
  MAX_QUEUE_READERS = 1,
  EXECUTE AS SELF
);
GO

USE R10;
GO
CREATE QUEUE dbo.EventQueue;
GO
CREATE SERVICE EventService
ON QUEUE dbo.EventQueue
(
  [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE EVENT NOTIFICATION CreateTableNotification
ON DATABASE
WITH FAN_IN
FOR CREATE_TABLE
TO SERVICE N'EventService', N'2525E77B-8F69-4C67-8CFE-352BDE5CD5A8';
GO

USE R10;
GO
CREATE TABLE dbo.Test (ID int);

SELECT CAST(message_body AS XML) FROM dbo.EventQueue;
GO

USE R10;
GO
DECLARE @r_handle UNIQUEIDENTIFIER;
DECLARE @r_message NVARCHAR(100);
DECLARE @r_message_type SYSNAME;
WHILE(1=1)
BEGIN
  BEGIN TRAN;
    WAITFOR
    ( RECEIVE TOP(1)
        @r_handle = conversation_handle,
        @r_message = message_body,
        @r_message_type = message_type_name
      FROM dbo.TargetQueue
    ), TIMEOUT 2000;

  IF (@@ROWCOUNT = 0)
  BEGIN
    ROLLBACK TRAN;
    BREAK
  END;
  ROLLBACK TRAN;
END;
GO

USE R10;
GO
ALTER QUEUE dbo.TargetQueue WITH STATUS = ON;
GO

USE R10;
GO
SELECT enqueue_time, from_service_name, to_service_name, transmission_status  
FROM sys.transmission_queue;
GO

--C:\ >ssbdiagnose -S KATMAI -E -d R10 CONFIGURATION FROM SERVICE
-- InitiatorService TO SERVICE TargetService

