Firebird developers can use Firebird events to notify clients about changes in database record data. In the previous post, I showed and discussed some limitations of Firebird events. This post presents a solution based on message-oriented middleware.

The solutions in the first post used server-side logic in the Firebird database metadata, implemented with Firebird’s procedural language (PSQL). Information which can not be passed within Firebird events, such as the record ID of the new purchase order, can be stored in helper tables which then must be read by the database clients. So from a technical view, these solutions only replace the timer-triggered client-pull with event-triggered client-pull. This is a small step forward, at the price of increased complexity of database metadata.

A new strategy: implementing the event notification on the client side

Obviously, the client application which inserts a new purchase order could also send notifications to the other clients. The advantage would be that server-side PSQL code can be replaced by code written in your client-side programming language (Delphi/Free Pascal, C#, …). The PSQL code in the server-side solution looked like this:

CREATE TRIGGER PURCHASE_ORDER_INSERT FOR PURCHASE_ORDER AFTER INSERT
AS
BEGIN
POST_EVENT 'purchase_order_table_updated';
END

If we implement notifications in the client side application, similar code has to be be executed when a new order record has been inserted in the order table, using an inter-process communication method. The event notification code could look like this:

procedure TAppDataModule.PurchaseOrderAfterPost(DataSet: TDataSet);
var
Notification: INotification;
begin
Notification := NotificationService.CreateNotification(PURCHASE_ORDER_TABLE_UPDATED);
Notification.SetIntProperty(PURCHASE_ORDER_ID, PurchaseOrderID.AsInteger);
NotificationService.Send(Notification);
end;

But advanced features like load balancing, or for notifying clients which currently are not listening, would need a high amount of custom code. Another difficult task would be the integration of clients or other systems written in an other programming languages (a PHP based order entry web app for example) – their integration with your application would only be possible after porting the notification system.

Introducing message brokers and message queues

Message brokers and message queues are software solutions which implement a message-oriented middleware (MOM) for message exchange. MOM provides software elements that reside in all communicating components and support asynchronous calls between the applications.

After installing the message broker server software, client applications can register a software routine that “listens” for messages placed onto the queue. For example, a C# application can use the IMessageListener interface in the ActiveMQ NMS client for asynchronous message receive. The IMessageListener interface declares one method, OnMessage, which has one parameter, message. If the ActiveMQ client library receives a new message, it will pass it in the message parameter to the application implementation of OnMessage:

using Apache.NMS;
using Common.Logging;

namespace MyApp
{
public class SimpleMessageListener : IMessageListener
{
public void OnMessage(IMessage message)
{
ITextMessage textMessage = message as ITextMessage;
if (textMessage != null)
{
LOG.Info("Message Text = " + textMessage.Text);
} else
{
LOG.Warn("Can not process message of type " message.GetType());
}
}
}

Receiving messages can be done in a background thread, asynchronously, as shown in the C# example above, or with a synchronous call, for example triggered by pushing a button or choosing menu item.

Text and binary message payload

Messages not only can include user-defined properties (implemented as a key-value list) but also have a text or binary message body. In our example scenario, this message body could be used to transfer the image data of a scanned fax order (or an incoming purchase order EDI document).

How can I use a message queue server as a “load balancer”?

In the last post, the third feature requirement was titled “I only need to be notified about every third order.”. And the conclusion was that

• the server should try to distribute the new order records between all connected clients

If you have configured a message queue for the new purchase order messages, a client application can post new order messages to the message broker, and all applications can listen for messages in this queue. The server will keep messages in the queue until a client asks the server for a new message. The broker will then remove the oldest message from the queue and send it to this client exclusively. The next client asking for a message will receive the next pending message, and so on. Message distribution between clients is a key feature of a message queue, which makes it a perfect fit for load balancing.

What happens if the client is offline?

Unlike Firebird events, which require that the client side is connected and listening (otherwise the events are lost), message queue senders and receivers do not have to be connected to the message broker at the same time. New purchase order messages will stay in the queue, waiting for client applications to connect.

Can I use message brokers for broadcast messages?

Message brokers support the publish/subscribe communication model, where a producer publishes messages to a particular destination (‘message topic’). Subscribers may register interest in receiving messages on a particular message topic. Only the connected clients will receive the message, similar to a a radio receiver.

Delphi/Free Pascal example code (using a Habari Client library), on the side of the sending application:

procedure TAppDataModule.PurchaseOrderAfterPost(DataSet: TDataSet);
var
NewOrderMessage: IMessage;
begin
// create a new message
NewOrderMessage := Session.CreateMessage;

// store the database record id in a message property
NewOrderMessage.SetIntProperty(PURCHASE_ORDER_ID, PurchaseOrderID.AsInteger);

// send it
MessageProducer.Send(NewOrder);
end;

The receiving applications can use asynchronous receive (in a background thread) or code like in the example below, which checks for a new purchase order in a GUI event handler:

procedure TOrderForm.CheckPurchaseOrder(Sender: TObject);
var
NewOrderMessage: IMessage;
OrderID: Integer;
begin
// check for new message (wait 100 milliseconds)
NewOrderMessage := MessageConsumer.Receive(100);

if (NewOrderMessage <> nil) then
begin
// get the database record id
OrderID := NewOrderMessage.GetIntProperty(PURCHASE_ORDER_ID);

// start the order processing workflow
EditOrder(OrderID);
end
else
begin
ShowMessage('No new order available');
end;
end;

Available message broker client libraries for Delphi and Free Pascal

Cross-language client libraries are available for most message queue systems. ActiveMQ supports a variety of Cross Language Clients and Protocols from Java, C, C++, C#, Ruby, Perl, Python, PHP. RabbitMQ can be used with Java, Ruby, Python, .NET, PHP and other development platforms. For Delphi and Free Pascal developers, Habarisoft offers client libraries for the open source message brokers ActiveMQ, Apollo, HornetQ, Open MQ and RabbitMQ. These libraries wrap the broker communication protocol in a high level API, and give access to advanced broker features, such as persistent messages, transacted send, and more.