How to create a notification server which informs Delphi application when database changes?

Question

We need to be able to inform a Delphi application in case there are changes to some of our tables in MySQL.

Delphi clients are in the Internet behind a firewall, and they have to be authenticated before connecting to the notification server we need to implement. The server can be programmed using for example Java, PHP or Python, and it has to support thousands of clients.

Typically one change in the database needs to be informed only to a single client, and I don't believe performance will be a bottleneck. It just has to be possible to inform any of those thousands of clients when a change affecting the specific client occurs.

I have been thinking of a solution where:

1. MySQL trigger would inform to notification server
2. Delphi client connects to a messaging queue and gets the notification using it

My questions:

1. What would be the best to way from the trigger to inform the external server of the change
2. Which message queue solution to pick?

Answer 1

Why not use the XMPP protocol (aka Jabbber) ?

Answer 2

There is apache camel and spring intergration, both provides some ways to send messages across.

Answer 3

Answer to the First Question:

check this question and answers on Stack Overflow:

When a new row in database is added, an external command line program must be invoked

In theory, a simple user-defined function could be used to fire a 'row changed' message to a message broker / queue. But this involves external systems (at least a network subsystem) which can fail - and bad things can happen.

A different solution which does not require dangerous modifications to the database system would be a multi-tiered design for the application. The server application which hosts the business logic then needs to generate 'database content changed' events, post them to a publish and subscribe message channel (aka 'topic') on the message broker so that every client will receive a copy of this message immediately or if the client reconnects (using 'durable subscriptions').

Answer to the Second Question:

The creators of Second Life have evaluated a couple of message brokers and published their results - for some of the products, Delphi client libraries exist or can be implemented using standard protocols: Message Queue Evaluation Notes

Since then, other products have been released, some of them can also be integrated with Delphi clients through non-Java protocols, for example:

• Open Message Queue (OpenMQ) 4.4, which is the default JMS provider broker in the Sun GlassFish v3 application Server
• JBoss HornetQ 2.1 which will be the default JMS provider in JBoss application server 6. HornetQ 2.0.GA obtained scores up to 307% higher than previously published SPECjms2007 benchmark results, on the same server hardware and operating system set-up.

A very popular open source message broker which can be used from Delphi, Java, PHP, C# (and other) clients is

• Apache ActiveMQ 5.3.2 - free introduction chapter of "ActiveMQ in Action" by Bruce Snyder, Dejan Bosanac, and Rob Davies

All brokers are designed for thousands of concurrent clients and tens of thousands of messages per second. They also typically support clustering and failover, though this is not part of the JMS specification.

If speed is not so important, but you need High availability (even if your internal system is down), Amazon Simple Queue Service (Amazon SQS) is a cloud-based service which can be accessed using REST and Soap style interfaces.