Database Change Notification Tools

Database Change Notification Tools

I found very little in the way of existing software that provides a full solution to the database-change-to-RSS-Feed problem. We'll likely have to do some programming to make this happen.

This is a good article summarizing the topic of having databases notify outside clients when changes have occurred:

John Reynolds's Blog - It's time for RDBMS Change Notification Services

Highlights from the article:

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.

Update: Thanks to a reader for a link to Oracle Streams. If Oracle integrates this functionality with Toplink, then Java evelopers 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. ...

Active Databases Look into the area of Active Database Management Systems and you will be surprised how much work has been done in the 90s:

An active database is a database with the event monitoring scheme for detecting when certain data is INSERTED, DELETED, UPDATED, or SELECTED, and automatically executing the actions in response when certain events happen and particular conditions are met.

Mechanisms supported in specific databases

Postgresql

Triggers

The type of triggers useful for monitoring database changes in Postgres are row-level insert, update, and delete statements that occur on a specific table.

Triggers on views are not supported.
Postgres does not support that the trigger be fired only when certain columns in the row have changed.

A sample trigger in Postgres

On the other hand, Postgres Rules can be used with Views.

Rules

The Postgres Rules system is implemented as Query-Rewrite rules.
Rules are useful for detecting UPDATE's, INSERT's and DELETE's performed on views.
I added a rule to view stating that when the contents of a view were 'updated' and the changed values in a view row meet some condition, then the rule should take some action. This rule never fired. In actuality, no 'UPDATE' command is every executed on the view because none of my code issues a statement to 'UPDATE' the view directly. Instead, commands are performed on the view's backing tables. While these commands affect the contents of a view that is 'rerendered' or 'fetched', Postgres doesn't run an 'UPDATE' command on the view, either. So I have to conclude that defining rules on views has very limited utility for the purpose of triggering notifications on database changes.

Documentation

The PostgreSQL Rule System

PostgreSQL supports a powerful rule system for the specification of views and ambiguous view updates. Originally the PostgreSQL rule system consisted of two implementations:

The first one worked using tuple (row) level processing and was implemented deep in the executor. The rule system was called whenever an individual tuple (row) had been accessed. This implementation was removed in 1995 when the last official release of the PostgreSQL project was transformed into Postgres95.

The second implementation of the rule system is a technique called query rewriting. The rewrite system is a module that exists between the parser stage and the planner/optimizer. This technique is still implemented.

The query rewriter is discussed in some detail in Chapter 36, so there is no need to cover it here. We will only point out that both the input and the output of the rewriter are query trees, that is, there is no change in the representation or level of semantic detail in the trees. Rewriting can be thought of as a form of macro expansion.

Page 44 of the book 'Active Database Systems By Jennifer Widom, Stefano Ceri'

POSTGRES is a next generation extensible database management system. Among the main design goals of POSTGRES was to support active rules. The first implementation of an integrated rule system is described in [SHP88]. Since then, based on the initial experience with rules and user feedback [SHP89], a second version of the POSTGRES rule system was developed, sometimes referred to as PRS2. This chapter describes the PRS2 version of POSTGRES rules. There are two alternate implementations of POSGTRES rules. The first implementation, the Tuple Level System (TLS), processes rules on a tuple by tuple basis. This system uses special markers called rule locks. When a rule is defined, those locks are stored in tuples that satisfy the rule's qualification (i.e., its condition part). When an appropriate event (e.g., retrieval of an attribute value, tuple modification, etc.) occurs in a tuple that has the appropriate rule locks, the corresponding rule actions are executed. The second implementation, the Query Rewrite System (QRS), is based on a query rewrite mechanism similar to the view implementation mechanism presented in [Sto75]. Using this approach, a query is combined with all relevant rules, and a set of modified queries is generated. The execution of this new set of queries provides the desired results.

More info on the original rules system implemented in Postgresql that was ripped out for the Postgres 95 release:

The Integration of Rule Systems and Database Systems

by Michael Stonebraker EECS Department. University of California, Berkeley 1993

A Commentary on the Postgres Rules System

Michael Stonebraker, Marti Hearst, and Spyros Potamianos EECS Dept. University of California, Berkeley 1989

More info on the current rules system (Query Rewrite System) implemented in Postgres.

On Rules, Procedures, Caching and Views in Database Systems

Michael Stonebraker, Anant Jhingran, Jeffrey Goh and Spyros Potamianos EECS Dept. University of California, Berkeley International Conference on Management of Data Proceedings of the 1990 ACM SIGMOD international conference on Management of data 1990 , Atlantic City, New Jersey, United States

An Introduction to the PostgreSQL Rule System

OKPUG - August 17, 2008 Presented by Emilie Steele Giustozzi

Chapter 36. The Rule System

Chapter 35. Triggers

Chapter 30. libpq - C Library - 30.7. Asynchronous Notification

SQL NOTIFY Command

MS SQL Server

Microsoft SQL Server Notification Services

Notification on database change? Is it possible to have SQL server notify an application upon a change in a table?

Steve Lasker's Web Log - Notification to Pull

So, as you can see, a Notification to Pull model is very powerful, but quite complex. There are a lot of moving parts to build, configure, maintain. We have a number of the parts including:

* SQL Server - Ability to store all this data on the server * SQL Server Compact - Ability to store all this data easily on the client with minimal deployment issues * Sync Services for ADO.NET - The pull operation to send/receive net changes between the server and the client * SQL Server Service Broker - Ability to queue up operational changes within the data center * WCF - communication stack between the client and the server over various protocols * Query Notifications - Ability for the server to notify a listener service that something has changed * Change Notifications - SQL Server 2008 introduces a more efficient way to track changes that can be used with Query Notifications to provide an efficient way to know when something changed, and what the details of the change are.

SQL Server Notification Services is deprecated for SQL Server 2008 because this feature did not get a lot of use. One reason cited for this lack of use is that it is not very easy to use. Microsoft says:

Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.

Oracle

Python cx_Oracle gets Database Change Notification Support

Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01

13 Developing Applications with Database Change Notification

Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits ...

Compare and Contrast Change Data Capture and Database Change Notification

Oracle has two seemingly competing technologies. CDC and DCN. What are the strengths of each? When would you use one and not the other?

Using Data Change Notification This tutorial shows you how to use Data Change Notification(DCN) using the Java program, QCNSlider.java.

Discussion of sample code that isn't quite working right: http://www.coderanch.com/t/81455/OracleOAS/Data-Change-Notification-Java

Tangently-related

RSS and SourceSafe

November 22nd, 2004 by gregr

I while back, I wrote an application to generate RSS feeds from Visual SourceSafe databases. The code was set to expire a few months later, as we were considering building this capability into a product. A couple more times after that, I posted updated versions with a later and later expiration.

Well, I’m happy to announce that we’ve decided to release the source code for this application. It requires .NET 1.1, and build files are included for Visual Studio .NET 2003.

This really is a cool app…it runs as a Windows service, and generates RSS feeds based on the change logs in VSS. And now it’s free, unlimited, and ready for you to play with. smile The copyright and license are included in the readme.rtf in the package below - basically, it’s an unrestricted modification and redistribution license.

download source: VssRssSvc.zip