Re: How to get notified about Database-changes (insert, update, delete - events)?

[Lukas Eder]

Hi Dennis,

This topic has been discussed before, here on the user group:

https://groups.google.com/forum/?fromgroups=#!searchin/jooq-user/notify/jooq-user/SgcSM1EgQsA/bRxtsk_VPAQJ

In general, I'm a bit reluctant of adding support for triggers written in Java, as there are too many ways to bypass them. You have mentioned plain SQL, but there is also batch SQL, direct JDBC access, other applications, stored procedures, and many other means to access the database. If you're lucky and you're using Postgres or Oracle, you can profit from their notification mechanisms:

http://stackoverflow.com/a/12619031/521799

Some other, interesting thoughts were written by Adam Gent in the same Stack Overflow question:

http://stackoverflow.com/a/13240582/521799

Other than that, your safest bet right now is to implement ExecutionListeners and be sure that all SQL is always generated by jOOQ through one of the various org.jooq.Query subtypes.

Cheers

Lukas

2013/3/11 <chaosk...@gmail.com>

Hello,

I've taken a look at jooq now and it looks like what I need.
However - my application has a UI and needs to get notified about database changes: insert, update and delete queries.
I didn't find anything documented to this  - the only listener I've found was the so called ExcutionListener - which seems not to be what I'm looking for.
Is this "feature" even supported directly in jooq?

Otherwise - I'll probably extend the generated DAOs and add my custom "Event-Callbacks" in there. The big downside which comes to my mind:
I can't react on plain SQL queries and I can't react on delete by ID calls. Additionally I'd have to add this event features to all DAOs which would result in redundant code.

Anything I'm overlooking?

Regards,
Dennis Fischer

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Lukas Eder]

Hi Dennis,

Yes, in that case, triggers are clearly your best choice. As your database is still in a Java context (maybe even in the same process), it will be easy to wire back signals to the UI through some asynchronous messaging service - as simple as a ConcurrentLinkedDeque

Cheers

Lukas

2013/3/11 Dennis Fischer <chaosk...@gmail.com>

Hi Lukas,

thanks for that response.
I'm using H2 embedded database in this application, which seems to support Java triggers (http://www.h2database.com/html/features.html#triggers)
It's probably best to use these instead of writing some custom ExecutionListeners or something else - then I can get all events regardless if jooq is used to submit these queries or if any other connection possibility is used.

Regards,
Dennis

[Dennis Fischer]

            if (newRow != null && newRow.next()) {
                final Result<Record> result = create.fetch(newRow);
                EventBusUtil.getInstance().post(new ModelPostSavedEvent(result.into(Account.class)));
            } else {
                final Result<Record> result = create.fetch(oldRow);
                EventBusUtil.getInstance().post(new ModelPostRemovedEvent(result.into(Account.class)));
            }Hi Lukas,

I've came to the point adding these triggers now. Well, the H2 API offers me 2 possibilities - I extended the TriggerAdapter.
    public void fire(final Connection conn, final ResultSet oldRow, final ResultSet newRow) throws SQLException {
}

There's no information about the statement type - but we can get it by checking oldRow and newRow against null.
My idea was now to fetch this resultSet and trigger the event with the resulting pojo.

So first of all - this is requried to get the executor:

    final Settings settings = new Settings();
    settings.setExecuteLogging(false);
    final Executor create = new Executor(conn, SQLDialect.H2, settings);

I have to disable default logging due to my posted issue request to use jooq without writing my own listener.
Then this code is used to to fetch the row - please note - this code might contain some mistakes inside the if condition:

            if (newRow != null && newRow.next()) {
                final Result<Record> result = create.fetch(newRow);
                EventBusUtil.getInstance().post(new ModelPostSavedEvent(result.into(Account.class)));
            } else {
                final Result<Record> result = create.fetch(oldRow);
                EventBusUtil.getInstance().post(new ModelPostRemovedEvent(result.into(Account.class)));
            }

Once executing this trigger, I get a huge memory leak (it will "consume" all my memory if not stopped) and a huge cpu usage (up to 100%). I tested the code further and came to the result - this leaks occur once calling:
               
final Result<Record> result = create.fetch(oldRow);

At least - that's the preferred way outlined at http://www.jooq.org/doc/2.6/manual/getting-started/use-cases/jooq-as-a-sql-executor/

After seeing this - I thought about a deadlock situation that prevents h2 to finish the intial (for example) INSERT statement - this then prevents my trigger from fetching the actual resultset. But this can't be true. First of all there's the API allowing me to use this resultSet, secondly that wouldn't explain that huge memory and cpu usages.

Can you explain what's going on?

[Dennis Fischer]

Update:
I wrote a workaround for this now as I was able to understand what was going on:
jooq seems to work as expected - however - h2 is returning an infinite ResultSet (dunno why - but that's the fact).
I changed fetch to fetchLazy and did a check on my identity field - the cursor will then stop once we're having a cycle detected.

[Lukas Eder]

Hi Dennis,

Thanks for investigating this. I'm not sure if you should really "consume" the newRow ResultSet. Instead, you should set values into it, using ResultSet.updateObject() and similar methods. Consider H2's Javadoc on TriggerAdapter.fire(Connection, ResultSet, ResultSet):

http://www.h2database.com/javadoc/org/h2/tools/TriggerAdapter.html#fire_Connection_ResultSet_ResultSet

It says:

"ResultSet.next does not need to be called (and calling it has no effect; it will always return true)."

This is quite astonishing, as H2 returns an infinite ResultSet for a single row. Hence, your fetchLazy() attempt is indeed the best approach to solve this problem, if you want to use H2's TriggerAdapter...

Cheers

Lukas

2013/3/14 Dennis Fischer <chaosk...@gmail.com>

[Lukas Eder]

This is quite astonishing, as H2 returns an infinite ResultSet for a single row. Hence, your fetchLazy() attempt is indeed the best approach to solve this problem, if you want to use H2's TriggerAdapter...

I've sent a mail to the H2 user group, referencing your request, here. Feel free to participate in that discussion:

https://groups.google.com/d/msg/h2-database/rsxhDvdtFmU/sQpgGso1J2sJ

[Ryan How]

I have the exact same setup as you in a project with a H2 embedded database.

The situation with these triggers gets more complicated when you start considering transactions.

What we've done is implement a SQL parser, which parses the SQL statments in the JOOQ execute listener.

In short it does something like this:

If an insert:. It queries the data after inserted and puts it into a JOOQ record

If an update: It gets the where clause & then queries the database, getting the results and putting into JOOQ records.

If an delete: Before executing, it queries using the where clause and gets the results, and puts into JOOQ records.

Then it fires an event before and after the transaction is committed containing the records inserted, updated (before and after the update) and deleted. The system can listen to these events and react accordingly.

We use this mechanism for writing audit data, refreshing the UI, performing some more advanced validation, and triggering other tasks in the system.

It seems to be working well. The limitation is that we are restricted in the type of statements we can execute, because the parser is not very smart. But generally we are only running very simple update, delete and insert statements, so it isn't a big limitation for us.

I guess if you hook it into the H2 trigger system you could skip the query parser and handle any kind of query. But I wanted to keep it database neutral.

Ryan