1.24 Oracle Data Change Notification in Clojure
===============================================
2013-05-15
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
(
https://docs.oracle.com/cd/E11882_01/java.112/e16548/dbchgnf.htm) (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
(
https://www.oracle.com/database/technologies/).
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 (
https://clojure.org/) to
keep it Lisp-y and cut down development time to a minimum. I will
likely later on try the same with ABCL
(
https://armedbear.common-lisp.dev/), 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 GitHub repository oracledcn-clojure
(
https://github.com/fsmunoz/oradcn-demo) 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. 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.
$ lein run -- --user fsmunoz --password p4ss --db XE --host aixdev \
--local-port 7777 -q "select ENAME from EMP"
> Connecting... connected.
> Registered Tables: [FSMUNOZ.EMP]
> DCN Registration ID: 701
> Waiting for DCN events...
Connection information : local=192.168.122.1/192.168.122.1:7777,
remote=aixdev/192.168.122.179:54157
Registration ID : 701
Notification version : 1
Event type : QUERYCHANGE
Database name : XE
Query Change Description (length=1)
query ID=82, query change event type=QUERYCHANGE
Table Change Description (length=1): operation=[UPDATE],
tableName=FSMUNOZ.EMP, objectNumber=20043
^C
$
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
(
https://github.com/technomancy/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.
