DSLContext.insertInto() is not invoking RecordListener

[jamesl...@gmail.com]

We have set a DefaultRecordListener in our jOOQ configuration and it works when calling things like DSLContext.newRecord(). However, when doing an insert using DSLContext.insertInto() our custom RecordListener is not getting called. Is that expected or a bug?

Thanks,

James Lorenzen

[Lukas Eder]

Hi James,

Your observation is correct and it's the expected behaviour. The RecordListener SPI is intended to intercept only calls on the TableRecord / UpdatableRecord APIs' store(), insert(), update(), delete(), refresh() calls. It cannot intercept arbitrary SQL statements the way SQL triggers can. An example: If you run a bulk UPDATE statement, jOOQ will not be able to intercept individual row updates as this knowledge is available only to the database. Even worse: jOOQ has no idea what will happen behind the scenes of a SQL MERGE statement.

Perhaps, we can find a missing feature or a more subtle flaw though. Would you mind explaining your use-case a little more, so we can see what would be the best way for you to go forward?

Looking forward to hearing from you again,
Lukas

--
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/d/optout.

[jamesl...@gmail.com]

Ok thanks for the explanation.

Our use case is we have a DefaultRecordListener that overrides the storeStart and insertStart methods to populate some created_at and modified_at fields that all our tables have.

However, in one area we are performing a bulk insert using the DSLContext.insertInto(Table, Fields) syntax.

I tried registering a DefaultExecuteListener but all that gives me access to is an ExecuteContext which doesn't seem to provide any methods to modify the statement. To me that would seem the most logical place to provide some hooks to modify the query before being executed.

Thanks again,

James

[Lukas Eder]

Thank you for your additional info.

Just to be sure: Is using a trigger an option? This will be the best way to guarantee the integrity of these columns, regardless who is inserting / updating data, and how they're doing it.

2015-11-04 14:56 GMT+01:00 <jamesl...@gmail.com>:

Our use case is we have a DefaultRecordListener that overrides the storeStart and insertStart methods to populate some created_at and modified_at fields that all our tables have.

However, in one area we are performing a bulk insert using the DSLContext.insertInto(Table, Fields) syntax.$

I can see where the confusion originates, especially if you're passing the Record as a whole to the INSERT statement via InsertSetStep.set(Record). I'll investigate if that method qualifies for the RecordListener lifecycle. If it does, then we'd have a bug:

https://github.com/jOOQ/jOOQ/issues/4706

In any case, the DSLContext.batchInsert(TableRecord...) method will generate the expected events. Perhaps, that's an option? It's really a batch insert, though, not a bulk insert.

I tried registering a DefaultExecuteListener but all that gives me access to is an ExecuteContext which doesn't seem to provide any methods to modify the statement. To me that would seem the most logical place to provide some hooks to modify the query before being executed.

The ExecuteListener is there to listen to JDBC statement lifecycle events. It doesn't have access to the SQL AST transformation and SQL string generation. If you want to get into the generation of the SQL statement, you'll need to implement a VisitListener:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation

With a VisitListener, you can guarantee that all SQL INSERT / UPDATE statements generated by jOOQ will yield the correct additional clauses for CREATED_AT and MODIFIED_AT

Hmm, I've just also found an old feature request for such audit fields:

https://github.com/jOOQ/jOOQ/issues/1592

I think we should be able to implement that for jOOQ 3.8. The idea has popped up a couple of times. Also, it fits the idea of supporting SQL:2011 temporal validity in jOOQ 3.8

[Lukas Eder]

Coincidentally, there is this related Stack Overflow question that was asked just now:

http://stackoverflow.com/q/33534872/521799

[jamesl...@gmail.com]

Yeah I think we are wanting to avoid database triggers for right now.

I'll take a look at the VisitListener approach. That sounds promising.

Thanks again Lukas. Great support.

[Lukas Eder]

Hi James,

2015-11-05 16:37 GMT+01:00 <jamesl...@gmail.com>:

Yeah I think we are wanting to avoid database triggers for right now.

May I ask why? I think they're often shunned for the "wrong" reasons (just like stored procedures)

 

I'll take a look at the VisitListener approach. That sounds promising.

Great. If you have time, I'd love to hear feedback about this approach. Perhaps, this is worthy of a small tutorial in the manual or on the jOOQ blog.

Cheers,
Lukas

[jamesl...@gmail.com]

Lukas,

So I poked around some in `VisitContext` and I'm not seeing much that will allow me to set or add additional fields and values to an existing insert.

`VisitContext.queryPart` returns a `QueryPart` and in the Idea debugger it looks like it has the data I'm looking for, but no public methods I can use to manipulate the SQL. Docs say I can safely cast to `QueryPartInternal` but that doesn't seem to have any modifying methods either.

Would you be able to point in the right direction? I guess I'm looking to do something like: `VisitContext.queryPart().addField(TableField, value)` or something similar.

[Lukas Eder]

Sure,

The idea is not for you to modify QueryPart objects that you are visiting while traversing the AST. You should look at them as being immutable. However, you can replace them for the given AST traversal, and / or generate additional SQL strings.

The idea is that you do something like

visitContext

    .context()

    .sql(", ")
    .visit(MY_TABLE.MODIFIED_AT)
    .sql(" = ")
    .visit(DSL.val(new Timestamp(System.currentTimeMillis())));

You'll have to do the above in the right "moment", of course, e.g. when reaching the end of a Clause.UPDATE_SET

Here are some more elaborate examples:

- http://blog.jooq.org/2015/06/17/implementing-client-side-row-level-security-with-jooq

- http://aakashjapi.com/caching-with-jooq-and-redis

It is a very low level API, but it's extremely powerful.

--

[Lukas Eder]

For the record, after some additional consideration (and after this feature has been requested 2-3 more times), I have now rejected issue #4706:

https://github.com/jOOQ/jOOQ/issues/4706

This is the reason:

We shouldn't trigger RecordListener. The listener is only triggered on actual UpdatableRecord.store()methods, and similar. In this case, the record is "abused" as a Map, whose contents is simply transferred to some other data structure. We don't know:

• If that insert statement is really executed
• If it works (in the DB)
• If it actually runs an insert (or perhaps an update, in the case of INSERT .. ON DUPLICATE KEY UPDATE)

It would be unwise to tie the RecordListener lifecycle to this API usage.

In particular when using INSERT .. ON DUPLICATE KEY UPDATE, then the semantics of a RecordListener isn't really clear anymore. Sure, we could shoe-horn this record state transfer somehow into listener events, but the behaviour would be rather unexpected.

Hope this helps,
Lukas

[Lukas Eder]

jOOQ 3.17 will support client side computed columns, which will probably cover most of these use-cases: https://github.com/jOOQ/jOOQ/issues/9879