Service Broker implements the publish-subscribe framework

Ervice broker implements a complete set of publish-subscribe solutions, in which author sends the service broker message (also known as article) to the publisher (publisher ). The publisher is responsible for distributing messages to different subscribers (subscriber ). Each subscriber accepts specific messages through subscription.

Describes this publish-subscribe solution:
The following describes how to implement this solution through the service broker function. Define the infrastructure to implement author, publisher, and different subscribers as service broker services. Authorservice sends an article message by starting a new session (conversation) with publisherservice. Before authorservice sends an actual article message, it needs to notify publisherservice in advance of the master body (subject) of the article in the session ). A subscriber is also a Service Broker Service, which will session with publisherservice. The subscriber first sends the topic of interest to the request, and then receives all published article messages about the topic. Because of these requirements, the publisherservice interface must support the following two contracts: l Contractl of the article message sent by the author service The related service broker object is defined under the contract of the subscriber subscribe to the topic and receive the published article message. Message Type Create Message Type [http://ssb.csharp.at/ssb_book/c10/publishmessagevalidation = well_formed_xml; gocreate Message Type [workflow = none; gocreate Message Type [workflow = well_formed_xml; go Contract Contract Create contract [comment ([comment sent by initiator, [http://ssb.csharp.at/ssb_book/c10/articlemessage] sent by initiator) gocreate contract [comment ([comment sent by initiator, [comment sent by target) Go Queue And Service Publisher queue and services Create queue [publisherqueue] gocreate service [publisherservice] on queue [publisherqueue] ([login) Go Subscriber queue and its services Create queue subscriberqueue1; gocreate service subscriberservice1 on queue subscriberqueue1; gocreate queue subscriberqueue2; gocreate service subscriberservice2 on queue subscriberqueue2; go Author Queues and services Create queue authorqueue; gocreate service authorservice on queue authorqueue; go creates two tables to store publishing and subscription records after the preceding service broker infrastructure is created. In this example, we create the following two tables: Publications and subscriptions. (Translated by entlib.com: The Publications table needs to be created on the author and publisher sides, and the subscriptions table needs to be created on the publisher and subscriber sides respectively .) Create Table publications (publication uniqueidentifier not null primary key, subject nvarchar (max) not null, originalxml XML not null) gocreate table subscriptions (subscriber uniqueidentifier not null primary key, subject nvarchar (max) not null, originalxml XML not null) Go matches the article message and subscriber from authorservice through these two tables. Use the subject column to connect the two tables. The "publisher logic" is described in detail later. Let's take a look at the services running on the publisherservice end. Program .

 

Application publisher Logic

In the Stored Procedure sp_publisherservice, implement the entry point of the publisherservice service ). When a new message arrives at the publisherqueue queue, the sp_publisherservice stored procedure is automatically activated and starts to process the message. This stored procedure can process the following message types: [http://ssb.csharp.at/ssb_book/c10/publishmessage#: when the stored procedure starts to publish an article message, it needs to receive this message type from authorservice. This message contains the same topic (subject) as the following article message ). [Http://ssb.csharp.at/ssb_book/c10/subscribemessage#: when the stored procedure is about to subscribe to a topic, it needs to receive the message type from the subscriber service provider. The message contains the topic of the subscriber request. [Http://ssb.csharp.at/ssb_book/c10/articlemessage#: when a stored procedure publishes an article message, it receives this message type from the authorservice. [Http://schemas.microsoft.com/ SQL /servicebroker/enddialog.pdf: When the stored procedure is about to close the session opened by publisherservice, the stored procedure will receive this message type from authorservice or the subscriber service. [Http://schemas.microsoft.com/ SQL /servicebroker/errortypes: if the requested topic does not exist, the stored procedure will receive this message type from publisherservice. The following script shows how the sp_publisherservice stored procedure processes these message types. Sp_publisherservice Service handler: Create procedure success @ conversation uniqueidentifier; declare @ message varbinary (max); declare @ messagetypename sysname; begin transaction; waitfor (receive top (1) @ conversation = conversation_handle, @ message = message_body, @ messagetypename = message_type_namefrom publisherqueue), timeout 1000; while (@ conversation is not null) beginif (@ messagetypename = 'HTTP: // SSB. CSHARP. at/ssb_book/C10/publishmessage ') begin Exec sp_processpublicationrequest @ Conversation, @ message; endelse if (@ messagetypename = 'HTTP: // SSB. CSHARP. at/ssb_book/C10/subscribemessage') begin Exec sp_processsubscriptionrequest @ Conversation, @ message; endelse if (@ messagetypename = 'HTTP: // SSB. CSHARP. at/ssb_book/C10/articlemessage') begin Exec sp_sendonpublication @ Conversation, @ message; endelse if (@ messagetypename in ( N 'HTTP: // schemas.microsoft.com/ SQL /servicebroker/error ', N 'HTTP: // schemas.microsoft.com/ SQL /servicebroker/enddialog ')) Beginend conversation @ conversation; If (exists (select * from publications where publication = @ conversation) begin Exec sp_removepublication @ Conversation; endif (exists (select * from subscribers) begin Exec sp_removesubscriber @ Conversation; endendelsebegin -- unexpected messageraiserror (n' encoded unexpected Message Type: % s', 16, 1, @ messagetypename); rollback; return; endcommit; select @ conversation = NULL; begin transaction; waitfor (receive top (1) @ conversation = conversation_handle, @ message = message_body, @ messagetypename = message_type_namefrom publisherqueue), timeout 1000; endcommit; endgo in the above stored procedure, first, receive a new one from the publisherqueue queue. The following message type is [http://ssb.csharp.at/ssb_book/C10/publishmessage], call the sp_processpublicationrequest stored procedure and record the received publishing data to the publications table. If the message type is [http://ssb.csharp.at/SSB_Book/c10/subscribemessage], the sp_processsubscriptionrequest stored procedure is called to record received subscription data to the subscriptions table. Finally, if the message is [http://ssb.csharp.at/ssb_book/c10/articlemessage?news, The sp_publisherservice stored procedure calls sp_sendonpublication to distribute the message to all matched subscribers. Use the sp_processpublicationrequest and sp_processsubscriptionrequest stored procedures to manage publishing and subscription records. These two stored procedures call other stored procedures respectively to insert the received messages to the publications or subscriptions table. The following shows the sp_processpublicationrequest stored procedure. Because the sp_processsubscriptionrequest stored procedure is similar, this stored procedure is ignored here. Sp_processpublicationrequest Stored Procedure script: Create procedure sp_processpublicationrequest @ conversation uniqueidentifier, @ message varbinary (max) asbegindeclare @ request XML; declare @ subject nvarchar (max); select @ request = cast (@ message as XML ); with xmlnamespaces (default 'HTTP: // SSB. CSHARP. at/ssb_book/C10/publishsubscribe ') Select @ subject = @ request. value (n' (// publish/subject) [1] ', n' nvarchar (max )'); If (@ subject is not null) Begin Exec sp_publishpublication @ Conversation, @ subject, @ message; endelsebeginend conversation @ conversationwith error = 1 Description = n' the publication is missing a subject '; Exec comment @ conversation; endendgo explain the stored procedure calls sp_publishpublication, and input three parameters: @ conversation, @ subject, and @ message. The following is the sp_publishpublication stored procedure. Sp_publishpublication Stored Procedure script: Create procedure sp_publishpublication @ publication uniqueidentifier, @ subject nvarchar (max), @ originalxml xmlasbegininsert Publications (Publication, subject, originalxml) values (@ publication, @ subject, @ originalxml) the publication column in The endgo publications table and the subscriptions column in the subscriptions table store the session ID, you need these session IDs to send the article message to the subscriber. The last stored procedure is sp_sendonpublication. The stored procedure is called after a message is received from authorservice at [http://ssb.csharp.at/ssb_book/c10/articlemessage. Sp_sendonpublication Stored Procedure script: Create procedure publish @ publication publish, @ Article varbinary (max) Publish @ subscribe publish; declare @ cursorsubscriptions cursor; Set @ cursorsubscriptions = cursor local scroll forselect subscriberfrom subscriptions sjoin publications P on S. subject = P. subjectwhere p. publication = @ publication; begin transaction; open @ cursorsubscriptions; fetch next from @ Cu Rsorsubscriptionsinto @ subscribe; while (@ fetch_status = 0) begin if (@ Article is not null) beginsend on conversation @ subscriptionmessage type [comment (@ Article ); endelsebeginsend on conversation @ subscriptionmessage type [http://ssb.csharp.at/ssb_book/c10/articlemessage##endfetch next from @ cursorsubscriptions into @ sublimit; endclose @ cursorsubscrip Tions; deallocate @ cursorsubscriptions; commit; endgo sp_sendonpublication the stored procedure uses a cusor to send the article message received from the authorservice to the matched subscriber. Send on conversation @ subscriptionmessage type [partition (@ Article); by connecting the subject fields of the publications and subscriptions tables, Match: Select subscriberfrom subscriptions sjoin publications P on S. subject = P. subjectwhere p. publication = @ publication; when the message type is received from [http://schemas.microsoft.com/ SQL /servicebroker/enddialog] (from authorservice or subscriber service provider), the corresponding publishing or subscription data needs to be obtained from publication Delete the S or subscriptions table. Use the sp_removepublication or sp_removesubscriptions stored procedure. Sp_removepublication Stored Procedure script: Create procedure sp_removepublication @ publication uniqueidentifierasbegindelete from publicationswhere publication = @ publicationend

Go

 

 

Publish information publishing information

This section demonstrates how a subscriber subscribes to receive information from the publisherservice service and how the authorservice has sent a new article message to the publisherservice for distribution. Before a subscriber can receive a new article message from publisherservice, it must request a subscription. You can send a [http://ssb.csharp.at/ssb_book/c10/subscribemessage] message, Code As follows: Subscriber request subscription script: Declare @ ch uniqueidentifier; begin dialog conversation @ chfrom service [subscriberservice1] to service 'herherservice' on contract [disable encryption = off; send on conversation @ ch Message Type [http://ssb.csharp.at/ssb_book/c10/subscribemessage] (N' <? XML version = "1.0"?> <Request xmlns = "http://ssb.csharp.at/SSB_Book/c10/PublishSubscribe"> <subject> subject1 </subject> </request> '); go in the above script, you must specify the message topic in the message to be sent, then, the published data is obtained from publisherservice Based on the topic. Once the subscriber sets the subscription, authorservice can send the article message to publisherservice for distribution. The sample code is as follows: [Authorservice] Send publishmessage Message consumption: Declare @ ch uniqueidentifier; begin dialog conversation @ chfrom service [authorservice] to service 'herherservice' on contract [encryption = off; send on conversation @ ch Message Type [http://ssb.csharp.at/ssb_book/c10/publishmessage] (N' <? XML version = "1.0"?> <Publish xmlns = "http://ssb.csharp.at/SSB_Book/c10/PublishSubscribe"> <subject> subject1 </subject> </publish> '); authorservice must specify which topic the subsequent article message belongs, it notifies publisherservice to record the corresponding Publishing Record by sending the [http://ssb.csharp.at/ssb_book/C10/publishmessage] Message type. Finally, authorservice sends different articles that belong to the previous topic. Send the article of a specific topic Message: Send on conversation @ ch Message Type [http://ssb.csharp.at/ssb_book/c10/articlemessage] (N' this is an article on subject1 '); send on conversation @ ch Message Type [http://ssb.csharp.at/ssb_book/c10/articlemessage] (N' and this is another article on subject1 '); go sends the article message to publisherservice at authorservice 1, which is automatically forwarded to the subscriber through the sp_publisherservice stored procedure. Now, you can query the queue corresponding to the subscriber service. The Zookeeper steals the rticle message and forwards it successfully. Note: Due to the inherent asynchronous processing feature of the service broker, you need to wait several seconds before you view the subscriberqueue1 and subscriberqueue2 queues until the message consumption is completed.