Tutorial – Działający przykład Service Brokera (MS SQL 2008)

19-lut-2011

Serwis broker pozwala na pewne przesyłanie danych między różnymi bazami danych, instancjami, czy nawet serwerami. Poszerza on więc możliwości serwera SQL o możliwość niezawodnego przekazywania informacji poza serwer. Samo przekazywanie danych opiera się o odpowiednie protokoły sieciowe i dostęp uzyskany do ENDPOINT-ów na serwerach.

W poniższym przykładzie zademonstruję działanie Service Brokera w obrębie jednej tylko bazy danych, co zazwyczaj w praktyce się nie zdarza. Można by go rozbudowywać dalej, ale… i bez tego sprawa wydaje się już dosyć skomplikowana.

Poniższy skrypt uruchamiany krok po kroku utworzy działający serwis broker. Można go ściągnąć stad. Więc do dzieła!

USE master;
GO

Serwis Broker zostanie utworzony w nowej bazie danych, więc jeżeli już taką bazę masz, to usuń ją:

DROP DATABASE ServiceBrokerTest;
GO

A potem utwórz na nowo:

CREATE DATABASE ServiceBrokerTest;
GO

Żeby określona baza danych mogła używać serwis brokera, trzeba go w tej bazie danych włączyć:

ALTER DATABASE ServiceBrokerTest
 SET ENABLE_BROKER;
GO

Teraz można już tworzyć obiekty:

USE ServiceBrokerTest;
GO

Chcę zaimplementować rozwiązanie, gdzie klient będzie mógł złożyć zamówienie i wysłać do Brokera zapytanie o dostępność towaru. Jeżeli towar będzie na stanie, to zostanie wydane potwierdzenie zamówienia. Jeżeli zaś nie będzie tyle w magazynie, to zostanie zwrócona informacja o błędzie. Komunikacja będzie się odbywać za pośrednictwem Service Brokera, czyli niekoniecznie natychmiast i od razu po zapytaniu będzie udzielona odpowiedź. Można to sobie raczej odnieść do modelu w którym sklepy składają zamówienia na towary w hurtowni. Stąd też utworzę Schemat:

CREATE SCHEMA Magazyn;
GO

W tym schemacie zakładamy tabelę z produktami:

CREATE TABLE Magazyn.Zamowienia
(
IdZamowienia INT NOT NULL PRIMARY KEY IDENTITY(1,1),
IdProduktu INT,
Ilosc INT,
Wynik NVARCHAR(100));
GO

Druga tabela to tabela z zamówieniami. Każde zamówienie ma przyznawany numer zamówienia i zakładam (dla uproszczenia, że w ramach jednego zamówienia będzie można starać się tylko o jeden produkt). W kolumnie wynik będzie zapamiętywane, czy produkt występuje w takiej ilości, by zaspokoić zamówienie, czy też nie. Ta kolumna będzie uzupełniana dopiero po potwierdzeniu zamówienia:

CREATE TABLE Magazyn.Zamowienia
(
IdZamowienia INT NOT NULL PRIMARY KEY IDENTITY(1,1),
IdProduktu INT,
Wynik NVARCHAR(100));
GO

Wypadało by mieć jakieś produkty w magazynie, więc:

 INSERT INTO Magazyn.Produkty
 VALUES
 (’Hoop Cola’,300),
 (’Rybnicki Full’,150);
 GO

Wysyłając zapytanie do serwisu zamówień, trzeba mówić zrozumiałym dla tego serwisu językiem. Dlatego definiujemy pierwszy możliwy do przesyłania komunikat. Ten komunikat to MESSAGE TYPE. Każdy typ wiadomości musi mieć nazwę i może zawierać jakąś dodatkową treść. W tym przypadku ta dodatkowa treść ma mieć poprawną formę XML.  Czy możliwe jest aby wiadomość nie miała dodatkowej treści!? Oczywiście! Jeżeli będąc w odwiedzinach u znajomych Twój partner lub partnerka kopnie Cię pod stołem to masz właśnie wiadomość bez treści, która coś tam oznacza!

CREATE MESSAGE TYPE
       [//Magazyn/Zamowienie]
       VALIDATION = WELL_FORMED_XML;
GO

Drugi rodzaj wiadomości będzie odsyłany do nadawcy zamówienia:

CREATE MESSAGE TYPE
       [//Magazyn/Potwierdzenie]
       VALIDATION = WELL_FORMED_XML;
GO

Dane przesyłane do zaakceptowania będą na bieżąco odkładane do kolejki i tam będą czekać, jak pacjenci w przychodni, aż się nimi ktoś zajmie. Kolejka jest to „prawie tabela”, z tym, że ma ustaloną kolejność. Kto pierwszy wszedł pierwszy też wyjdzie (chyba, że bawisz się jeszcze z priorytetami wiadomości):

CREATE QUEUE Magazyn.KolejkaZamowien;
GO

Podobna kolejka musi istnieć, aby gromadzić odpowiedzi z serwisu zamówień. Tworzymy przecież tak naprawdę parę serwis brokerów „Przyjmujący zlecenia” i „Przyjmujący potwierdzenia”.

CREATE QUEUE Magazyn.KolejkaPotwierdzen;
GO

Przechodzimy do czegoś ciekawszego. Rozmawiając ze sobą serwisy powinny  informować się wiadomościami według ustalonej zasady. Np. kiedy pytam, to potem Ty mi odpowiadasz. Podobnie jest tutaj: Komunikacja odbywa się w oparciu o tzw. Kontrakt. Kontrakt określa jaką wiadomość kto może przesłać. W naszym przypadku kontrakt WysyłkaZamówień dopuszcza, aby Zamowienie było wysyłane przez Inicjatora dialogu, a Potwierdzenie odsyłane przez odbiorcę zlecenia. To trochę jak w szkole. Chcesz coś powiedzieć, to najpierw podnieś rękę:

CREATE CONTRACT [//Magazyn/WysylkaZamowien]
([//Magazyn/Zamowienie] SENT BY INITIATOR,
 [//Magazyn/Potwierdzenie] SENT BY TARGET);
GO

Teraz tworzymy usługi. Najpierw usługa przyjmowania zleceń. Usługa nazywa się SerwisZamowien, odczytuje komunikaty z kolejki KolejkaZamowien i oczekuje dialogu zgodnego z kontraktem WysylkaZamowien

CREATE SERVICE [//Magazyn/SerwisZamowien]
ON QUEUE Magazyn.KolejkaZamowien
([//Magazyn/WysylkaZamowien])
GO

Drugi serwis z kolei czeka na informacje zapisywane do kolejki KolejkaPotwierdzen:

CREATE SERVICE [//Magazyn/SerwisKlienta]
ON QUEUE Magazyn.KolejkaPotwierdzen
GO

Składanie zamówień będzie realizowane poprzez procedurę składowaną, chociaż mógłby to być równie dobrze skrypt, a nie procedura. Ten kawałek kodu będę komentował krok po kroku:

CREATE PROCEDURE Magazyn.SP_ZlozenieZamowienia
@IdZamawianegoProduktu INT,
@ZamawianaIlosc INT
AS
BEGIN

Najpierw zapisujemy do tabeli zamówień informację o tym, że składamy zamówienie. Zapamiętujemy nadany nr zamowienia.

      INSERT INTO Magazyn.Zamowienia VALUES (@IdZamawianegoProduktu,@ZamawianaIlosc,”);
      DECLARE @IdZamowienia INT;
      SET @IdZamowienia = @@IDENTITY;

Deklarujemy zmienne na dialog (tak, dialog jest typu INT) – jest to „uchwyt” (handle) do dialogu:

      DECLARE @IdDialogu UNIQUEIDENTIFIER;
      DECLARE @Wiadomosc NVARCHAR(100);

 I zaczynamy rozmawiać. Rozmawiać będą ze sobą dwa serwisy bazując na konkretnym kontrakcie:

      BEGIN DIALOG @IdDialogu
             FROM SERVICE [//Magazyn/SerwisKlienta]
             TO SERVICE N’//Magazyn/SerwisZamowien’
             ON CONTRACT [//Magazyn/WysylkaZamowien]
             WITH ENCRYPTION = OFF;

Będzie tu wysłana konkretna wiadomość: „Na zamowieniu nr tyle a tyle, chcę zamówić produkt taki a taki w tej ilości”.

      SELECT @Wiadomosc =
N'<Zamowienie><IdZamowienia>’+
CAST(@IdZamowienia AS VARCHAR(10))+
'</IdZamowienia><IdProduktu>’+
CAST(@IdZamawianegoProduktu AS NVARCHAR(10))+
'</IdProduktu><Ilosc>’+
CAST(@ZamawianaIlosc AS NVARCHAR(10))+
'</Ilosc></Zamowienie>’;

Tak przygotowaną wiadomość wysyłamy w ramach naszego dialogu

      SEND ON CONVERSATION @IdDialogu
            MESSAGE TYPE [//Magazyn/Zamowienie]
            (@Wiadomosc);

I to na tyle! Teraz niech ServiceBrokery same się martwią o dalsze przerobienie naszego zlecenia.

END
GO

Możemy teraz złożyć zamówienie:

EXECUTE Magazyn.SP_ZlozenieZamowienia 1,3;
GO

Sprawdźmy co zapisało się w tabelach:

SELECT * FROM Magazyn.Zamowienia;
SELECT * FROM Magazyn.KolejkaZamowien;
SELECT * FROM Magazyn.KolejkaPotwierdzen;

 Efekt powinien być taki, że w kolejce zamówień coś zobaczysz. Kolejka potwierdzeń będzie jeszcze pusta. W tabeli zamówień zobaczysz zamówienie na produkt nr 1 w ilości 3 szt., ale jeszcze bez potwierdzenia. Pora więc, by ktoś łaskawie pobrał dane z kolejki zamówień.

Również i w tym przypadku najpierw przygotujemy procedurę, a dopiero potem ją uruchomimy. Jazda! Procedura nie przyjmuje żadnych parametrów:

 CREATE PROCEDURE Magazyn.SP_ObslugaKolejkiZamowien
AS
BEGIN

Deklarujemy zmienne na:  Identyfikator dialogu (jest obecnie zapisany w kolejce), przekazaną wiadomość (również w kolejce) oraz typ wiadomości (też w kolejce).

      DECLARE @IdDialogu UNIQUEIDENTIFIER;
      DECLARE @Wiadomosc NVARCHAR(100);
      DECLARE @TypWiadomosci sysname;

Pobieramy rekord z kolejki. Jak jest to fajnie, a jak nie ma, to czekamy na to maksymalnie 1 sekundę. Pobrane dane z kolejki zapisujemy w zmiennych:

      WAITFOR
      ( RECEIVE TOP(1)
            @IdDialogu = conversation_handle,
            @Wiadomosc = message_body,
            @TypWiadomosci = message_type_name
        FROM Magazyn.KolejkaZamowien
      ), TIMEOUT 1000;

Jeżeli przekazana do nas wiadomość to zamówienie (już wiesz po co definiowaliśmy różne rodzaje wiadomości), to tą wiadomością się zajmiemy.

      IF @TypWiadomosci = N’//Magazyn/Zamowienie’
      BEGIN

Zaczniemy od wczytania do zadeklarowanych zmiennych tego, co znajduje się w wiadomości. A we wiadomości znajduje się id zamówienia, id produktu oraz zamawiana ilość:

            DECLARE @IdProduktu INT;
            DECLARE @Ilosc INT;
            DECLARE @Dokument INT;

Wiadomość jest „opakowana” w XML-u więc wydobywam te dane za pomocą OPENXML:

            EXECUTE sp_xml_preparedocument @Dokument OUTPUT, @Wiadomosc;
           SELECT @IdProduktu=IdProduktu, @Ilosc=Ilosc
          FROM OPENXML(@Dokument,’Zamowienie’,2)
                        WITH
                        (
                             IdProduktu INT,
                             Ilosc INT
                        );   
           EXECUTE sp_xml_removedocument @Dokument

Zaczniemy od typowych obliczeń w tabeli produkty. Potwierdzę zamówienie tylko jeżeli w magazynie mam odpowiednią ilość:

            DECLARE @StanMagazynu INT
            SELECT @StanMagazynu=Ilosc
            FROM Magazyn.Produkty WHERE IdProduktu=@IdProduktu;

A teraz budujemy odpowiedź. Będzie to oczywiście XML. Zawartość XML zależy od tego, czy wystarczy danego produktu w magazynie, czy nie. Do wiadomości należy włożyć też IdZamowienia, oraz (choć niekoniecznie) IdProduktu i w końcu odpowiedź. Dwie instrukcje poniżej, budujące XML różnią się tylko wynikiem potwierdzenia: jest to albo „OK.” albo „Błąd”:

             –Budowanie odpowiedzi
            DECLARE @Odpowiedz NVARCHAR(100);
         
            IF @StanMagazynu>=@Ilosc
                  BEGIN
                        SET @Odpowiedz = N'<Odpowiedz> <IdZamowienia>’+
                        CAST(@IdZamowienia AS VARCHAR(10))+
                        '</IdZamowienia><IdProduktu>’+
                        CAST(@IdProduktu AS NVARCHAR(10))+
                        '</IdProduktu><Wynik>OK</Wynik></Odpowiedz>’;
                        UPDATE Magazyn.Produkty
                        SET Ilosc=Ilosc-@Ilosc
                        WHERE IdProduktu=@IdProduktu;
                  END
            ELSE
                  BEGIN
                        SET @Odpowiedz = N'<Odpowiedz><IdZamowienia>’+
                        CAST(@IdZamowienia AS VARCHAR(10))+
                        '</IdZamowienia><IdProduktu>’+
                        CAST(@IdProduktu AS NVARCHAR(10))+
                        '</IdProduktu><Wynik>Blad</Wynik></Odpowiedz>’;
                  END;

Teraz pora na wysłanie odpowiedzi:

             SEND ON CONVERSATION @IdDialogu
            MESSAGE TYPE [//Magazyn/Potwierdzenie]
            (@Odpowiedz);
 
            END CONVERSATION @IdDialogu;
      END
END
GO

I to by było na tyle. Teraz tylko pora uruchomić tę procedurę!

EXECUTE Magazyn.SP_ObslugaKolejkiZamowien
GO

Jeżeli przyjrzeć się teraz kolejkom, dostrzeżesz zmianę. Są za pisy w kolejce potwierdzeń, a nie ma w kolejce zamówień.

SELECT * FROM Magazyn.Zamowienia;
SELECT * FROM Magazyn.KolejkaZamowien;
SELECT * FROM Magazyn.KolejkaPotwierdzen;

Teraz napiszemy procedurę pobierającą potwierdzenia. Będzie ona czytać z kolejki potwierdzeń i zapisywać do tabeli Zamowienia fakt potwierdzenia lub błędu.  Procedura nie ma parametrów. Wszystko przeczyta sobie z  kolejki:

CREATE PROCEDURE Magazyn.SP_ObslugaKolejkiPotwierdzen
AS
BEGIN

Deklarujemy zmienne na odczyt danych z kolejki

      DECLARE @IdDialogu UNIQUEIDENTIFIER;
      DECLARE @Wiadomosc NVARCHAR(100);
      DECLARE @TypWiadomosci sysname;

Czytamy jeden zapis z kolejki, albo czekamy przez sekundę i kończymy.

      WAITFOR
      ( RECEIVE TOP(1)
            @IdDialogu = conversation_handle,
            @Wiadomosc = message_body,
            @TypWiadomosci = message_type_name
        FROM Magazyn.KolejkaPotwierdzen
      ), TIMEOUT 1000;

Jeżeli wiadomość jest typu Potwierdzenie, to idziemy dalej:

      IF @TypWiadomosci = N’//Magazyn/Potwierdzenie’
      BEGIN

Pobieramy to co zostało przekazane w wiadomości w postaci XML-a. Tak więc OPENXML idzie w ruch:

            DECLARE @Wynik NVARCHAR(100);
            DECLARE @IdZamowienia INT;
            DECLARE @Id