Tuesday, January 29, 2008

A Database Trigger That Maintains an RSS Feed

Since my opening post was about how much I’m addicted to RSS, I thought it would be fitting if my first Advantage-specific post was related. In this post I will talk a bit about a trigger I wrote that updates an RSS feed automatically. At the end I will include a link to a zip file with the trigger source.


This trigger is used in a bug tracking database. R&D engineers and support engineers subscribe to the feed to stay up to date with known bugs in the product. Readers obviously won’t memorize every entry, but this helps facilitate that, “Hey, I think I remember a bug similar to this.” moment that can often be helpful. It can also be used to replace e-mail notifications when new bugs are entered into a tracking system. RSS is a much friendlier way to deliver these notifications, as it can be read at the consumer’s leisure, as opposed to interrupting them or littering their inbox as e-mail notifications would.


Triggers can be fun to write because they can often add useful functionality to an application with zero changes to the actual application itself. This particular trigger is defined as an AFTER INSERT trigger on the base bug table. It takes a few details from the new row, including the bug ID and description, and writes them to an xml file.


This particular example is a trigger DLL written in Delphi. The first order of business is to extract the information we want to include in the feed from the __new table.
oQuery.SQL.Text := 'SELECT * FROM __new';
oQuery.Open;
strID := oQuery.FieldByName( 'bugid' ).AsString;
strTitle := oQuery.FieldByName( 'title' ).AsString;
strDesc := 'Bug #' + strID + #13 + #10 + #13 + #10 +
      oQuery.FieldByName( 'Description' ).AsString +
      #13 + #10 + #13 + #10;
oQuery.Close;

The next block of code reads a few settings from a settings table in the database. The main purpose of the settings table is to avoid hard coding the values inside the trigger.

The settings table looks something like this:

PropertyValue
rss_feedc:\path\to\myfeed.xml
rss_feed_itemtemplatec:\path\to\myfeedtemplate.xml
rss_feed_linkhttp://myserver/somesite/buginfo.php?BugID=

The first two rows include paths to the feed file (myfeed.xml) and the feed template file (myfeedtemplate.xml). The feed template file contains the xml shell to generate a single new feed item. The final property, rss_feed_link, is used to embed a link readers can click on that will take them to a web page where they can view more details about the particular feed item.


And the code that reads the settings table looks like this:

oQuery.SQL.Text := 'select * from settings';
oQuery.Open;
if not oQuery.Locate( 'property', 'rss_feed', [loCaseInsensitive] ) then
// no feed configured, just exit
exit;
strFeedFile := oQuery.FieldbyName('value').AsString;
if not oQuery.Locate( 'property', 'rss_feed_link', [loCaseInsensitive] ) then
// no feed link configured, just exit
exit;
strFeedLink := oQuery.FieldbyName('value').AsString + strID;
if not oQuery.Locate( 'property', 'rss_feed_item_template', [loCaseInsensitive] ) then
// no feed link configured, just exit
exit;
strFeedItem := oQuery.FieldbyName('value').AsString;
oQuery.Close;


Finally, a utility function is called to actually add a new feed item to the file.

AddFeedItem( strFeedItem,
         strFeedFile,
         strTitle,
         strFeedLink,
         strDesc );

The base feed file includes an identifier that can easily be located, in this case it is the comment -- next item here --. The AddFeedItem function constructs a valid feed item and replaces -- next item here -- with the new feed item template, which also includes the comment at the end, facilitating the next insertion sometime in the future.


Verifying Your Feed

I use the following site to verify a feed after testing. This is particularly important when generating the xml in code, as we aren’t using any third party libraries, and there is certainly the potential of generating some malformed xml:

http://www.feedvalidator.org/


Performance Note

It should be noted that this trigger is not suited for a table that frequently undergoes a lot of concurrent insert operations, or batches of inserts. It streams the feed file in each time it is executed, and the reading and writing of the feed file has to be synchronous. The last thing you would want is 100 users waiting for synchronous access to the feed file.


Exercises Left to the Reader

This trigger was written in haste with a “good enough for government work” mentality. In other words, I was fairly lazy but this gets the job done. As such, there are certainly some issues that would need to be addressed if you wanted it to be bullet proof.

One that comes to mind off the top of my head is if absolutely every new row needs to be in the feed, you would need to address the code that bails out if it can’t get a deny write lock on the feed file. In its current state, the trigger just gives up after a few tries and exits.

You may also want to limit the number if items you put in the feed. When you reach a specific number of feeds you may want to delete an old item for every new item you add.


The Source Code

The trigger source code, a base feed file, and a feed item template can be found here.

3 comments:

Vevici said...

J.D.

Perfect. I was kicking around the idea of writing such a trigger. Thanks for saving me time with this handy example.

It looks like I'm the first commentary, so that would make me your blog's biggest fan!

Cheers,
Carl

J.D. Mullin said...

Thanks Carl, unfortunately Tim beat you to the punch and posted a comment on my first post about his favorite rss reader. :)

Andrew MacNeill said...

Hey JD.

I do something VERY similar to this with both our Customer Call AND bug tracking database but it's not triggered off the database, rather it simply generates the RSS feed on demand (yes, a huge hit if you had hundreds of users but if you have that many developers, I don't imagine that would be your biggest concern) - but the benefit of having issues and calls all accessible from an RSS feed is just wonderful.

A nice feed of all open issues

Post a Comment