Firebird Database Events and Message-oriented Middleware (part 1)

Firebird developers can use Firebird events to notify clients about changes in database record data. In this article, I will show and discuss some limitations of Firebird events and compare possible workarounds with a solution based on message-oriented middleware.

How should I implement “server push” for new data, instead of “client pull”?

For this article we assume a common business use case:

• a two-tier client-server application
• one client creates new records of type “purchase order”
• other clients need to be notified about new “purchase order” records

In this scenario, repeated pulling data from the “purchase order” table causes network traffic from all client workstations – even if there are no new orders at all, and too many clients would cause network and/or server resource overload. But being a two-tier architecture, there is no service layer or application server between database and the client software which could handle the notifications. Our conclusion is that we need server side ‘push’ notifications, which will only cause network traffic when there is new data.

With Firebird events, the push notification code would be part of a database metadata object (a trigger or a stored procedure). Example:

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

All clients which are interested in receiving this event will use the Firebird API to register (subscribe) to the event by its name. Example code, using ZeosLib TZIBEventAlerter:

var
  EventAlerter: TZIBEventAlerter;
...
  // register (subscribe) to purchase order event
  EventAlerter.Events.Add('purchase_order_table_updated');
  EventAlerter.RegisterEvents;
...

Whenever the server sends a purchase_order_table_updated event, the client event handler method will be invoked in all clients which are currently connected and have subscribed (registered) to this event.

Problem #1: “Can I have the ID of the new order, please?”

There is a problem with this simple solution: Firebird events are not able to carry any further information. Clients only will be notified that there is new data in the order table. Clients which receive this event still have to scan the order table for new records.

Solution: A helper table for database events

Internet resources show workarounds, for example here and here on Stackoverflow. Additional tables can be created in the server to store information about the new order. For every event, the server code (in the stored procedure or trigger) writes a record in the helper table with extra data like purchase order ID and order creation time:

begin
    /* store the order ID */
    insert into purchase_order_changes values (gen_id(some_generator, 1), New.ID, 'now');
    /* finally, post the event */
    POST_EVENT 'purchase_order_table_updated';
  end
end

Problem #2: “Oops, I was offline when the new order arrived!”

Firebird events are sent as “broadcast” messages: the server does not care if clients currently do not listen. All disconnected clients will miss new order events. When the client reconnects, the missed event will not be waiting for them on the server.

Solution: Polling for new data when the client reconnects

As illustrated in the previous case, helper tables can be used to store IDs of new records, and client side polling could retrieve all new orders since the last disconnect. Technically this is a solution, but it is of limited use for clients which reconnect frequently (using the ‘briefcase model’). It would be more efficient it the server stores the IDs per client for all events which occured while the client was offline.

Problem #3: “I only need to be notified about every third order.”

Imagine that there are three clients in the office, processing incoming purchase orders. As soon as a client application receives a new order event, it will use the helper table to retrieve the record ID of the new order and then start the order processing workflow. Obviously it would be very inefficient if all clients start the workflow for the same new order record. Instead, the server should try to distribute the new order records between all connected clients.

Because Firebird events are sent as “broadcast” messages, all connected clients which registered (subscribed) the event id will receive a message. Firebird events can not be configured so that they will be sent in a random or round-robin way to one of the registered client exclusively.

Solution: Additional server-side logic to implement “load balancing” (?)

Implementing such advanced features as load-balancing of Firebird events completely on the Firebird server side (using triggers, stored procedures, helper tables) surely is possible, but left as an exercise to the reader.

Problem #4: “After an update of all records my application is unresponsive”

Consider this notification code on the server side in an UPDATE trigger. It will notify all connected clients with an active subscription when a record has been changed. Now imagine a SQL UPDATE changes a high number of table records: so that all clients with an active subscription will receive a high number of purchase_order_updated events. Does your client application refresh the user interface for every single Database Event? If yes, this can quickly overload clients.

  CREATE TRIGGER PURCHASE_ORDER_INSERT
    FOR PURCHASE_ORDER
    AFTER UPDATE
  AS
  BEGIN
    POST_EVENT 'purchase_order_updated';
  END

Solution: with some extra logic on the client side, messages which come in too quickly can be discarded. But this will not reduce the network bandwith load – it will remain high unless the client decides to unsubscribe the event until the database “bulk operation” has finished. (Note: this problem #4 only occurs when every update is executed in a new transaction).

The next part presents a different solution, based on message-oriented middleware products, which is available as open source and supports cross-language / cross-platform clients.

Advertisement