Advantage Replication, Triggers, and Notifications

Most of my posts here are associated with Delphi, but I am also a big fan of the Advantage Database Server, an ISAM (indexed sequential access method) remote database server marketed by Sybase, an SAP company. As I write this I am on a plane returning from an extended onsite visit to one of my clients. During this visit we implemented a number of interesting features in their database application that made extensive use of three of Advantage’s more sophisticated features – replication, triggers, and notifications.

This is not the first time that I’ve used these Advantage features. At the same time, I haven’t written a whole lot about the real world application of these capabilities, and this seems like a good time to share, particularly because I feel a genuine excitement about how nicely these facilities came together to form a meaningful and maintainable solution to a complex problem.

I’ll begin by describing the basic problem that we were faced with, after which I’ll describe how Advantage replication, triggers, and notifications provided a convenient and minimally invasive solution. And for the record, the development environments employed are a mixture of Delphi (native and .NET), C#, SQL, JavaScript/jQuery, Web services, console applications, and Windows services, which is pretty typical of an enterprise-level application.

This particular client operates a number of business interests that interact with each other, and they develop a lot of custom software to support the business processes that distinguish them from their competitors. To say that they are technologically savvy is an understatement.

On my recent visit they determined that they needed to be able to extend communication between two separate database applications that were built independently for very different purposes. Specifically, they needed to make data managed by one application available to another, more or less in real time. That data, in turn, needed to be used to send text messages to specific users of the second database about specific changes occurring in the data on the first database.

These database applications were developed by different teams, and are running on completely different servers. The first application is a native client/server application, and the second is a browser-based application. In addition, the first application is employing Advantage 9.1, the previous version of Advantage, while the second is using the latest version, which is Advantage 10.1. To make things more difficult, this new capability needs to be implemented with no modification to the source application on the first database, if at all possible.

The solution to sharing the data without modifying the source application was to use Advantage's replication feature. Replication, which requires a special replication license on each replicating database, permitted us to transfer changes made to several of the database tables of interest between the two databases. When a table is being replicated, records that are inserted into that table on the source database are also inserted into a corresponding table on the destination database. Similarly, a record that is inserted or update on the source database causes that same change to be performed on the corresponding record in the destination database.

Our solution involved replication of two tables, which I will call events and notifications. The events table represents upcoming events and the current status of that event (schedule, in-process, or complete). This table is a key table of the first application, and represents potential events of interest to users of the second application. The events table is replicated using one-way replication, in that the second database will never make changes to the upcoming events. In other words, even if a user on the second database were to make a change to the events table (which we do not permit), that change would not be sent back to the first database.

The notifications table identifies those events that are of interest to users of the second application, and this table is replicated bi-directionally. Bi-directional replication permits users of the second database to create and remove events about which they want to be notified, as well as to permit the first database to update the notifications table once a notification has been performed. (How this is achieve is discussed later in this post). Through bi-directional notification, both databases can edit the notifications table and see changes made by the other database.

We also leveraged a feature of replication introduced in Advantage 9; the ability to replicate some, but not all of the fields of a source table. The events table contains a lot of fields that are not of interest to users who need notification. Therefore we replicated only the selected fields of interest from the first database to the second. As a result, the inter-database communication used only the bandwidth necessary to transfer the data we needed.

One of the nice characteristics of Advantage replication is that the source database queues replications if a connection to the destination database is lost. So, for example, if we need to reboot the server on which the destination database is running, the source database will hold those changes that need to be replicated until the destination database comes back online. As a result, data is never lost, though sometimes it will be delayed a little, but that is better than losing the data altogether.

Since we were trying to implement these features with no changes to the source application, our solution to managing the notifications employed two additional Advantage features: Triggers and notifications.

Advantage triggers are typically SQL statements that are executed by Advantage in response to record-level events on a table, such as record insertions, deletions, and updates. In this case, we placed a trigger on the events table on the source database. Each time the events table is changed, this trigger determines if the event is associated with an active notification in the notifications table. If so, it calls a stored procedure that inserts a records into a special table used for scheduling messages. It also then sets the timestamp field in the notifications table for that notification, making that particular notification inactive (no more notifications will be sent about that particular event).

And here is where Advantage notifications made their presence felt. Actually, we had been meaning to implement a generic email/texting solution in this database for some time, and we used this opportunity to do just that. Here is how we did it. The messages table, which is located in the source database, includes fields for a subject line, a message body, an attachment, as well as additional fields that could hold one or more email addresses to which the email or text message should be sent. (A future update will include a child table where zero, one, or more attachments can be defined, but we'll save that one for later.)

The messages table also includes a timestamp field that is populated when the emails and text messages are sent, and this field is used to distinguish between messages sent and those still pending. This table also includes an after insert trigger that generates a notification when a record is inserted.

Due to the way that the messages table and its trigger are designed, any application (console, standalone, Web-based, or mobile) can send an message simply by inserting a record into the messages table. We simplified things a bit further by creating an easy to use stored procedure that performs the insertion into messages, along with some additional validation and error handling.

Advantage notifications are created by calling a system stored procedure named sp_SignalEvent. Any application that wants to be notified about a particular notification calls the sp_CreateEvent system stored procedure, after which it can call the sp_WaitForEvent or sp_WaitForAnyEvent system stored procedure to be blocked until a notification of interest is received. As you might imagine, a call to sp_WaitForEvent or sp_WaitForAnyEvent should typically be called from a worker thread, and that's just what we did.

We built a separate Windows service using Delphi to generate the emails and text messages. Each time the Windows service starts, it spawns a thread that begins by calling sp_CreateEvent for two different Advantage notifications. One of these is the notification generated by the after insert trigger on the messages table. The second is a notification generated by a stored procedure that the Windows service itself executes when it receives an instruction to shut down. This notification is used to wake up the worker thread when the Windows service is being terminated.

Next, the worker thread queries the messages table, looking for records where the sent timestamp field is null. If one or more unsent messages are found, the thread generates the necessary emails and text messages for each message, using Delphi's Internet Direct (Indy) TIdSMTP component, after which it sets the messages timestamp field to the time the message was sent.

After sending any pending emails, the Windows service calls sp_WaitForAnyEvent, which blocks the thread until it is receives one of the two notifications of interested.

When the thread in the Windows service receives one of the notifications, it first checks to see if it has been terminated (the Windows service is shutting down). If not, it processes any unsent messages from the messages table, after which it calls sp_WaitForAnyEvent again. This process repeats as long as the Windows service continues to run.

Importantly, all emails are sent, even if the Windows service needs to be shutdown now and then, similar to how replication restarts if a connection to the destination database is temporarily lost.

This set of solutions provided us with the seamless complement of features that we needed, with minimal complexity and modest modifications to the databases. It also permitted us to implement a messaging system that can be used to send emails or text messages by any applications that can talk to the database. Yes, these features could have been implemented in a number of different ways, but this set of solutions provided us with a minimally invasive modification to our databases while providing a reliable and maintainable answer to our needs.

In closing, I've got one more bit of good news. As many of you know, Loy Anderson and I have written three books (to date) about the Advantage Database Server. Our latest book, Advantage Database Server: A Developer's Guide, 2nd Edition, is available on Amazon, CreateSpace (Amazon's publishing company), and Fastspring. This book includes chapters on replication, triggers, stored procedures, SQL, and notifications. See http://www.jensendatasystems.com/ADSBook10 for details. More importantly, it recently became available on Amazon's European and Japanese sites.

Amazon is weird this way, and I hope they change (since we are just finishing our next book on Delphi ClientDataSets, which we will be publishing on Amazon this spring). Our Advantage book has been available on Amazon in the US since late August, but only became available in Europe, the UK, and Japan in the past month. Heck, we're happy that the book is now being sold at the various Amazons, but are still a little annoyed at the delay between its initial publication and its wider availability.