It's time for RDBMS Change Notification Services
It is a safe bet that relational databases will be around for a very long time. The relational model is well past middle age (it was introduced by E.F. Codd in 1970), but it exhibits no loss of vigor despite repeated challenges. Upstarts like Object and XML databases have garnered some support, but they havent yet made a dent in the preeminence of the RDBMS. Layers may be erected between the RDBMS and the application logic, but the roots of most applications will still be in systems that execute SQL commands.
Jonathan Bruce points out in his recent blog, the Java community's interest in SQL (via JDBC) is far from stagnant.
Records managed by an RDBMS are often long lived and are seldom accessed by a single application. Applications written in different languages often manipulate the same records, and this shared access leads to a fundamental problem: How do you know that your data is in sync with the RDBMS?
There is a tendency to deal with shared data problems by building layers on top of the RDBMS. In the Java world, several very good object caches exist and many of these provide replication across clusters to insure that applications will operate on identical data (like JBoss Cache and Turbine JCS).
The problem with these solutions is fundamental. Applications can bypass these mechanisms and go directly to the RDBMS. If a change is made directly to the database, the caching mechanism will be ignorant until a change is attempted or the cache is refreshed.
I think that the fix belongs in the RDBMS itself. Change notification needs to become a standard RDBMS feature. The RDBMS should publish changes to registered subscribers.
Most popular RDBMS offerings already provide for update triggers that execute commands when changes occur. If your application needs to be aware of specific changes, then you can add a trigger to the RDBMS. The downside of this approach is that many triggers must be generated to deal with very similar concerns (and improperly written triggers can severely impact DB performance).
RDBMS authors should build on the trigger technology to implement publish and subscribe change notification services. Applications (and object caches) would subscribe to change notifications by issuing statements very similar to update triggers with the addition of a callback. When the trigger is fired, the callback is invoked.
The advantage to this approach is that it cannot be bypassed. A change notification service that is integral to the RDBMS will catch any changes to the data (including those caused by stored procedures). Applications will still have to deal with the changes, but at least they will know about them.
The APIs for the RDBMS Change Notification Services should be standardized, and the callback mechanisms should be flexible to support multiple languages and protocols (like XML over HTTP). I'm probably naive, but like SQL itself, agreement on a common standard will benefit all RDBMS vendors. There should be little incentive to implement proprietary APIs.
Thanks to a reader for a link to Oracle Streams. If Oracle integrates this functionality with
Toplink, then Java developers will have something very close to what I envision.
Oracle's efforts are a very good start, but we need standard APIs supported by many RDBMS offerings.
Update (20May2005): Looks like Microsoft's SQL server does pretty much what I want. Check out the devx article: Letting Java in on SQL Server Notifications. Unfortunately, there's still not really a standard.