Know when you have a lot of objects relying on some database information, but there’s no way to tell whether that information has been changed? I’m not talking about the majority of data in a database, just the little bits that help the application start itself. As an example, a distributed tax calculation application, where each accountant uses it to manage his clients. However, when it loads, it reads some critical information – tax rates, for example. These don’t change often, but when they do, you wouldn’t want to have to tell everyone to restart the application, would you?

So what are the options we’re left with?

Have a big button saying “Refresh Configuration” and have the users click it whenever an email to do so is distributed to them.

Have a function that does the checking periodically for them, basically “pushing the refresh button” every now and then.

Register for database notifications, and be alerted when a change in a table you’re interested in has occured.

The first two options are okay-to-have, but aren’t useful when talking about applications which have no daily human-interaction or that cannot afford to check a database for changes constantly (and even if so, it is critical for them to be aware of change in certain tables as soon as they happen), such as distributed research applications being configured remotely using a single database.

So, I set off in an attempt to create a database which alerts of changes done internally. I’m not a DBA, but I’ve had my share of database-dealings in my past, and have seen a few solutions to this problem. The earliest one was using Oracle’s DBMS_ALERT package to lock an application thread until an alert is released through a trigger, then checking a notifications table for new notifications. This was later superseded by a newer feature of Oracle’s databases called Advanced Queues, which can be used to do exactly what I wanted.

For some time I was struggling with this option, as I didn’t know nor wanted to learn how to set up the required hardware and software for this to actually work, and the IT department was too busy with other (more important) things than to help me with this experiment. I don’t know how I overlooked this for such a long time, but eventually I remembered Java DB, Sun’s distribution of Apache’s Derby Database. This database comes bundled with Sun’s JDK 6, is a breeze to install, and allows for Java code to be used for procedures.

(Notice though that Derby doesn’t come with Apple’s distribution of JDK 6 – You’ll need to download it from Derby’s download page).

After starting the database, the next step was to make it “observable”: The Java procedures do that trick. A procedure could be attached to a public static Java method, and that method will be called whenever the procedure is called with the parameters passed to the procedure translated to the bound Java types (String for VARCHAR, int for INTEGER, etc). It will even check for appropriate conversions between primitives and class types. After the procedure is set up, a trigger can be made on each table to call that procedure, which will in turn alert the application that a change has been made. In my case, the procedure took as parameters the table’s name, the type of update (insert, update or delete) and the value of the primary key.

This worked well, but I didn’t want to couple this observable database with any single application. For that task of de-coupling, I used JMX, the Java Management Extensions library, also bundled with JDK 6 (even with Apple’s). My Java procedure then alerted a JMX component, which in turn went on to select the entire row changed, and sent it as a JMX notification to all registered components. Since JMX also allows for remove registrations using the JMX Remote API, it also allows for distributed applications to register for database events.

I’ve made this project into a wrapper project for any Derby database – just start the observable DB with the database’s URL and viola! It’s observable, complete with a JMX management beans server. Stop the observable DB application – and all the triggers and procedures added will be automagically removed – this is done using the Runtime.addShutdownHook method. If, for example, the application is killed using kill -9 or a power loss, obviously these triggers and procedures will remain. Starting the observable DB again will remove them, though, so no fear – nothing is permanent if it isn’t really wanted.

There are obviously other ways to attack this problem: There could be a management application which would alert all applications of a change when it’s made – since it’s made through the management application. However, this lacks in transparency and could lead to errors later along the way.

Maybe this isn’t as “out-of-the-box” as some of you hoped, but most of it is – it’s just a bit of rewiring from already-existing components in the JDK.