Database change notification callback/mechanism

[Marko Frelih]

Dear Sirs and Madams!

Let say I have 5 apps working on same mysql database through class/object DatabaseManager, whose responsibility is to insert/change/delete records from tables in some database (and all other db related operations). This class/object heavly uses jooq for communicating with database. Now, does jooq itself has some sort of mechanismus, so my DatabaseManager gets notified about database change. Let's say, if I add record in some table in some database through this class, does jooq support "callback" from mysql that record has been added? Or this functionality needs to be implemented from scratch in DatabaseManager itself?

Sincerely,
Marko

[Lukas Eder]

Hello Marko,

From what I understand, you would probably want to add some sort of trigger to your database, which invokes a database notification mechanism. I have recently given an answer to this Stack Overflow question, regarding a similar topic:
http://stackoverflow.com/a/12619031/521799

From what I know, these databases support such notification features:

Oracle:

• The DBMS_ALERT is a simple means for such notification
• Oracle AQ / Oracle Streams provide more sophisticated queue mechanisms

Postgres:

• The NOTIFY statement is a simple means for such notification

I doubt that in MySQL, you will be able to implement something similar. Neither does jOOQ have something like that built-in. If you want to heavily rely on such notification / caching mechanisms, you may want to look into EhCache or JBossCache or any similar product, which allows to synchronise caching state among your 5 nodes. But then again, this will have to be done outside of jOOQ.

I'd be curious to hear about your further steps / decisions, as this is indeed an interesting problem.

Cheers

Lukas

[Marko Frelih]

Will report what have we done ... Otherwise, this j00q of yours is nevertheless the far most usable library I've seen ... Congrats for nice work!

[Lukas Eder]

> Will report what have we done ... Otherwise, this j00q of yours is
> nevertheless the far most usable library I've seen ... Congrats for nice
> work!

Thank you! And good luck with the notification mechanism

[Ryan How]

I was going to have a go at doing something similar in JOOQ using the ExecuteListener. On an insert or update I was going to parse the query to get the table and fields, then fire an event to say a table has been updated / inserted and what fields and values. I guess the downside is all data updates need to go through JOOQ and you wouldn't be able to use stored procedures.

I was going to use this to fire before a transaction was committed so extra validation could be done in the application.

[Lukas Eder]

Hi Ryan,

> [...]

> I was going to use this to fire before a transaction was committed so extra
> validation could be done in the application.

Thanks for your input. Yes, that downside is really the actual problem
here. Not only will stored procedure calls bypass your jOOQ-specific
logic, but also plain SQL calls, calls from other (non-Java/jOOQ)
applications and the MERGE / INSERT .. ON DUPLICATE KEY UPDATE
statements are hard to predict, too

In the mean time, there had been some more discussion on this Stack
Overflow question, on which I have put a small bounty:
http://stackoverflow.com/q/12618915/521799

It looks like the most reliable solution is really to handle these
events in the database somehow, using triggers and then preferably a
proprietary notification mechanism, or some sort of sophisticated
polling...