
-------------------------------------------------------------------------------------------------------------
---- Listing 6.1
-------------------------------------------------------------------------------------------------------------
--------------------------- Rozdział 6. Przykładowy kod -----------------------------------------------------


-- Włączanie Service Brokera, aby reszta kodu zadziałała
--------------------------------------------------------------
USE Master
GO

ALTER DATABASE sample_database
SET ENABLE_BROKER
GO

-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
------------- Tworzenie obiektów Service Brokera
-------------------------------------------------------------------------------------------------------------
USE Sample_Database
GO



-- Tworzenie dwóch obiektów typów komunikatów
-----------------------------------------
CREATE MESSAGE TYPE YourMessageType
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE AnotherMessageType
AUTHORIZATION dbo
VALIDATION = NONE


-- Tworzenie kontraktów definiujących konwersacje
--------------------------------------------------
CREATE CONTRACT MyContract1
AUTHORIZATION dbo
(YourMessageType SENT BY ANY)

CREATE CONTRACT MyHighPriority
AUTHORIZATION dbo
(YourMessageType SENT BY ANY)

CREATE CONTRACT MyContract2
AUTHORIZATION dbo
(YourMessageType SENT BY INITIATOR,
AnotherMessageType SENT BY TARGET)
GO

-- Fikcyjna procedura pozwalająca uruchomić przykład
-------------------------------------------
CREATE PROCEDURE dbo.MySourceActivationProcedure AS RETURN 0;
GO
-- Tworzenie kolejki na komunikaty. Komunikaty są zapisywane w kolejce, dlatego 
-- upewnij się, że dostępna jest wystarczająca ilość miejsca
------------------------------------------------------------------------------------------------------
CREATE QUEUE YourQueue_Source
WITH STATUS=ON,
     RETENTION=OFF,
     ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME=dbo.MySourceActivationProcedure,
          MAX_QUEUE_READERS=30,
          EXECUTE AS OWNER),
     POISON_MESSAGE_HANDLING (STATUS = ON);


	 CREATE QUEUE MyDestinationQueue
WITH STATUS=ON,
     RETENTION=OFF,
     ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME=dbo.MySourceActivationProcedure,
          MAX_QUEUE_READERS=30,
          EXECUTE AS OWNER),
     POISON_MESSAGE_HANDLING (STATUS = ON);


-- Tworzenie obiektu Service definiującego usługę Service Brokera. Przy korzystaniu z tej usługi
-- należy podać jej nazwę
-------------------------------------------------------------------------------------------------------
CREATE SERVICE YourService_Source
AUTHORIZATION dbo
ON QUEUE dbo.YourQueue_Source
(MyContract1)
GO

CREATE SERVICE MyDestinationService
AUTHORIZATION dbo
ON QUEUE dbo.MyDestinationQueue
(MyContract1)
GO

-- Tworzenie obiektu Route używanego do kierowania komunikatów ze zdalnej bazy do danej usługi. Adresy są 
-- tu zmyślone - musisz zastąpić je rzeczywistymi adresami
----------------------------------------------------------------------------------------------------------
CREATE ROUTE ExpenseRoute
    WITH SERVICE_NAME = 'MyService',
    BROKER_INSTANCE = '53FA2363-BF93-4EB6-A32D-F672339E08ED',
    ADDRESS = 'TCP://sql2:1234',
    MIRROR_ADDRESS = 'TCP://sql4:4567' ;


-- Tworzenie obiektu Priority. Dla każdego priorytetu trzeba utworzyć odrębny obiekt tego rodzaju
------------------------------------------------------------------------------------------
CREATE BROKER PRIORITY HighPriority
FOR CONVERSATION
SET ( CONTRACT_NAME = MyHighPriority ,
      LOCAL_SERVICE_NAME = ANY ,
      REMOTE_SERVICE_NAME = N'ANY' ,
      PRIORITY_LEVEL = 8
)

-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------


-- Pobieranie następnej grupy konserwacji, dla której w kolejce dostępne są komunikaty
-----------------------------------------------------------------------------
DECLARE @conversation_group_id AS UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversation_group_id
FROM YourQueue_Source;


-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-- Wysyłanie komunikatów za pomocą Service Brokera


-- Wysyłanie komunikatu
-----------------------
DECLARE @message_body AS XML, @dialog_handle as UNIQUEIDENTIFIER
SET @message_body = (SELECT * 
     FROM sys.all_objects as object 
     FOR XML AUTO, root('root'))  --- Tworzenie dokumentu XML używanego jako treść komunikatu

BEGIN DIALOG CONVERSATION @dialog_handle
     FROM SERVICE [YourService_Source]
     TO SERVICE 'YourDestinationService' -- W wierszu TO SERVICE trzeba podać łańcuch znaków (wielkość liter ma w nim znaczenie)
     ON CONTRACT [MyContract2];

SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE YourMessageType
(@message_body)
GO



-- Tworzenie tabeli na uchwyty konwersacji dla przykładowego programu
--------------------------------------------------------------------------
CREATE TABLE dbo.SSB_Settings
([Source] sysname NOT NULL,
[Destination] sysname NOT NULL,
[Contract] sysname NOT NULL,
[dialog_handle] uniqueidentifier
CONSTRAINT PK_SSB_Setting PRIMARY KEY ([Source], [Destination], [Contract])
)
GO

CREATE PROCEDURE dbo.CreateConversation
     @Destination sysname,
     @Source sysname,
     @Contract sysname,
     @MessageType sysname,
     @MessageBody XML,
     @dialog_handle uniqueidentifier
AS
/* Pobieranie identyfikatora konwersacji */
SELECT @dialog_handle = dialog_handle
FROM dbo.SSB_Settings 
WHERE [Source] = @Source 
     AND [Destination] = @Destination 
     AND [Contract] = @Contract;

/* Jeśli uchwyt nie istnieje, należy utworzyć nową konwersację */
IF @dialog_handle IS NULL 
BEGIN
     BEGIN TRANSACTION
     /* Jeśli istnieje uchwyt konwersacji, należy zasygnalizować odbiorcy,
        że poprzednia konwersacja została zamknięta */
     IF @dialog_handle IS NOT NULL
     BEGIN
          UPDATE dbo.SSB_Settings 
          SET dialog_handle = NULL
          WHERE [Source] = @Source
               AND [Destination] = @Destination 
               AND [Contract] = @Contract;
          SEND ON CONVERSATION @dialog_handle
          MESSAGE TYPE EndOfConversation;
     END

     /* Konfigurowanie nowej konwersacji */
     BEGIN DIALOG CONVERSATION @dialog_handle
     FROM SERVICE @Source
     TO SERVICE @Destination
     ON CONTRACT @Contract;

     /* Rejestrowanie identyfikatora nowej konwersacji */
     UPDATE dbo.SSB_Settings 
          SET dialog_handle = @dialog_handle 
     WHERE [Source] = @Source
          AND [Destination] = @Destination 
          AND [Contract] = @Contract;

     IF @@ROWCOUNT = 0
          INSERT INTO dbo.SSB_Settings 
           ([Source], [Destination], [Contract], [dialog_handle])
          VALUES
           (@Source, @Destination, @Contract, @dialog_handle);
END;

/* Wysyłanie komunikatu */
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE @MessageType
(@MessageBody);
/* Sprawdzanie, czy dany uchwyt konwersacji to ten sam, który jest zapisany w tabeli. 
   Jeśli nie, należy oznaczyć daną konwersację jako zakończoną */
IF (SELECT dialog_handle 
    FROM dbo.SSB_Settings  
    WHERE [Source] = @Source 
        AND [Destination] = @Destination 
        AND [Contract] = @Contract) <> @dialog_handle 
    SEND ON CONVERSATION @dialog_handle
        MESSAGE TYPE EndOfConversation;
GO


-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-- Odbiór komunikatów
---------------------
DECLARE @dialog_handle UNIQUEIDENTIFIER, @messagetype nvarchar(128), @message_body XML;

BEGIN TRANSACTION;
RECEIVE TOP (1) @dialog_handle = conversation_handle, @messagetype = [message_type_name],
     @message_body = CAST(message_body as XML)
FROM MyDestinationQueue
IF @@ROWCOUNT = 0 
	BEGIN
		COMMIT TRANSACTION;
	END
ELSE IF @messagetype = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
	BEGIN
		-- Rejestrowanie błędu w dzienniku aplikacji
		END CONVERSATION @dialog_handle;
		COMMIT TRANSACTION;
	END
ELSE IF @messagetype = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
	BEGIN
		END CONVERSATION @dialog_handle;
		COMMIT TRANSACTION;
	END
ELSE
	BEGIN
/* Wykonywanie potrzebnych operacji na dokumencie XML */
		COMMIT TRANSACTION;
	END
GO

-- Pobieranie kilku komunikatów w każdej instrukcji pozwala poprawić wydajność
------------------------------------------------------------------------
DECLARE @dialog_handle UNIQUEIDENTIFIER, @message_body XML, @message_type nvarchar(128)
DECLARE @Messages TABLE
(conversation_handle uniqueidentifier,
message_type sysname,
message_body VARBINARY(MAX))
BEGIN TRANSACTION
WAITFOR (
	RECEIVE TOP (1000) conversation_handle, message_type_name, message_body
	FROM MyDestinationQueue
	INTO @Messages), TIMEOUT 5000;
	DECLARE cur CURSOR FOR select conversation_handle, message_type, CAST(message_body AS XML) 
                       FROM @Messages 
                       WHERE message_body IS NOT NULL
OPEN cur
FETCH NEXT FROM cur INTO @dialog_handle, @message_type, @message_body
WHILE @@FETCH_STATUS = 0
BEGIN
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
	BEGIN
		-- Rejestrowanie błędu w dzienniku aplikacji
		END CONVERSATION @dialog_handle;
	END
ELSE IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
	BEGIN
		END CONVERSATION @dialog_handle;
	END
ELSE
	BEGIN
/* Wykonywanie potrzebnych operacji na dokumencie XML */
		SELECT @message_body
	END
    FETCH NEXT FROM cur INTO @dialog_handle, @message_body
END
CLOSE cur
DEALLOCATE cur;
IF EXISTS (SELECT * FROM @Messages WHERE message_type = 'EndOfConversation')
     END CONVERSATION @dialog_handle
COMMIT TRANSACTION
GO


-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-- Konfigurowanie Service Brokera pod kątem zdalnego dostępu

USE master
GO
-- Zabezpieczenia Service Brokera wymagają, aby bazy danych miały główny klucz szyfrowania
---------------------------------------------------------------
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourSecurePassword1!'


-- Tworzenie certyfikatu identyfikującego każdy punkt końcowy
-------------------------------------------------
CREATE CERTIFICATE MyServiceBrokerCertificate
WITH SUBJECT = 'Service Broker Certificate', 
     START_DATE = '1/1/2014',
     EXPIRY_DATE = '12/31/2099'

-- Tworzenie kopii zapasowej klucza publicznego z certyfikatu, co pozwala przesłać ten klucz do innego punktu końcowego
BACKUP CERTIFICATE MyServiceBrokerCertificate 
     TO FILE='C:\MyServiceBrokerCertificate.cer'

-- Importowanie certyfikatu do zdalnego serwera
------------------------------------------------
CREATE CERTIFICATE MyServiceBrokerCertificate
FROM FILE='c:\MyServiceBrokerCertificate.cer'


USE master
GO 
-- Tworzenie dla serwera punktu końcowego Service Brokera 
--------------------------------------------------
CREATE ENDPOINT ServiceBrokerEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1234, LISTENER_IP=ALL)
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE MyServiceBrokerCertificate, 
     ENCRYPTION = REQUIRED ALGORITHM AES);
GO



-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-- Tworzenie kolejki i zdarzenia na potrzeby zewnętrznej aktywacji
----------------------------------------------------------
USE Sample_Database

CREATE QUEUE dbo.MyDestinationQueueEA
GO

CREATE SERVICE MyDestinationServiceEA
ON QUEUE dbo.MyDestinationQueueEA
(
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
GO

CREATE EVENT NOTIFICATION MyDestinationNotificationEA
ON QUEUE MyDestinationQueue
FOR QUEUE_ACTIVATION
TO SERVICE 'MyDestinationServiceEA', 'current database'
GO










