I have been analysing a work-related thread related to how to integrate database “events” – i.e. any information that reflects interesting actions or changes and that are currently in a RDBMS – with the IBM IOC. There are many ways to do it but each has different requirements and benefits and this is important when, say, in a situation where the database access is somewhat restricted, the ability to deploy specific software solutions limited (e.g. replication software like IIDR) or there are other more mundane constraints (like time and availability of resources).
Enough of this though. In the particular case of Oracle databases there is a feature called Oracle Database Change Notification (aka “Oracle Continuous Query Notification” in more recent releases) that initially seemed helpful to me. The documentation seemed reasonably simple, even if I do not touch database stuff in ages and was never particularly good with it in terms of programming. Examples abound on the net, but mainly (as expected) in Java, which is quite expected since the features is expose through the JDBC driver.
Regardless of the actual applicability to any real project at hand I became curious about it since it seemed a generally useful feature. Since it uses JDBC I decided to use Clojure to keep it Lisp-y and cut down development time to a minimum. I will likely later on try the same with ABCL, which I really like, but for now Clojure seems to be easier to use when doing translations from Java, and in this case it worked quite well.
The result is in my Gitbhub repository oracledcn-clojure and is merely a small command-line tool that connects to an Oracle database via JDBC, registers for notification on whatever tables are affected by a query and then sends a notification to standard output each time there is a change. Note that this is client-side only so any actual changes (updates, inserts, etc.) will have to be done “manually”. This is trivial to add to the code but I wanted to focus on the notification aspect more than anything else. Do read the documentation since there is some small but fundamental setup needed because of the ojdbc6.jar file. The way that it is set up is likely to change since I’m using a feature that is not to be used in Real Projects (TM), but since this isn’t one I’m not terribly worried. I’m not particularly good with Clojure but this demo is to provide some quick information for those who are looking around for implementations and want to have an idea on how it works.
The demo itself is simple but could be easily extended to do more complex event transformation and routing: instead of printing the event it could analyse it, detect rows and tables, trigger additional queries and even transform the result into XML and submit it to a message queue, all within Clojure. This depends a lot of the business goal and specific technical environment: my business goal for this demo was to build wee paper puppets, write the event notification results on them and watch them do a Dance Macabre. I can imagine that there are other, less obvious applications for this technology and your business goals can be wildly different.
Overall notes and observations:
- Make sure that there are no network obstacles: I spend most of the time trying to “fix” a problem that didn’t exist: I wasn’t getting any notification back, but a “select * from USER_CHANGE_NOTIFICATION_REGS;” was returning a correct entry. I changed the code, updated JDBC drivers, went back and forth and in the end I should have just began with “telnet myhost myport” from the database server. I was bitten by this because I assumed that a KVM image using NAT in my laptop would not have a problem communicating, but since this is is based on callbacks (and not in an established connection) firewall rules in both sides were blocking it.
- The “select * from USER_CHANGE_NOTIFICATION_REGS;” query is a good way to see if things are working, the code itself checks for the registration ID but this query can be used directly on the database
- I have found conflicting requirements for use of this feature; most documentation only mentions “grant change notification to foo;”, other sources indicate “GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO foo;” as also being required. YMMV.
- The ojdc6.jar should, from what I’ve read, ideally be the same version, mainly because there are sometimes bugs in the authentication component. That being said this feature should work (as per the documentation at least) using a thin JDBC driver from 11g and an Oracle Database 10gR2.
- DBeaver seems to be a good database manager if you’re not sure on what to use.
I like how Clojure feels but I can’t seem to wrap my mind around it; for a Lisp it has significant differences in terms of “paradigm” but also command names and standard libraries that leave me fumbling around. The easy interop with Java also has the side-effect of making one “lean back” into Java methods, which is most of the time not the most elegant approach. That being said leiningen works very well, it’s easy to get a lot of packages and it’s also very easy to distribute the final work: I can send a überjar for someone to click in and all they need as dependency ia a JVM.
Some important lessons in developing the code (which is extremely simple and only took about 2 hours of my own time because I’m not a programmer and I needed to get back to speed with some infrastructure details) are actually more related to the “social” aspect than to any technical details. A good example is how my lack of knowledge of the Clojure development toolkit was quickly surpassed by going to #clojure (NB: this is not an “hashtag” or whatever people call it, it’s a real IRC channel) and getting help directly from technomancy. This seemed something common to me but then I stopped and analysed it a bit more: leiningen is not exactly a small project, it’s *the* Clojure de facto’s configuration and dependency manager and used throughout the world in hundreds of projects. That some small doubts related to “how to add odbc6.jar to the classpath” could be dealt with the author is something that people coming from more “Enterprise” backgrounds would find unexpected to say the least – I know, because I talk to them.
This, in turn, reminded me something that Nic Ferrier (another #emacs habitué, of elnode and Teamchat fame to name a few) referred some time ago: using software that is maintained and developed by those on your close “social vicinity” is not only emotionally logical but also a practical benefit, and especially so when we are talking about communities which are IMO less artificial and more organic. Also, #clojure was very helpful, got some good answers to obvious questions, which is not always the case in programming communities.
Finally, the whole process just made me more sure that IRC continues to be one of the best “social networks” for people to actually build relationships from scratch. I never met the people in #emacs but I know several of them better that many people I work with. Other networks are fine for announcements and other more unidirectional patterns, but to this day I still think that IRC could be use to build quite an interesting social network, with links from the /whois referring back to source repositories, etc.
Well, I like the idea at least.