Add value change listeners to the Record Object

[Ryan How]

Hi Lukas,

What do you think about bloating the UpdatableRecord object a bit more and adding value change listeners to the record?.

My use case is that I am using the UpdatableRecord for data binding purposes. It would be great to listen to value change events in the record so I can then sync back to the UI.

This UpdatableRecord object is like a swiss army knife. I love it :)

Thanks, Ryan

[Lukas Eder]

Hi Ryan

> What do you think about bloating the UpdatableRecord object a bit more and
> adding value change listeners to the record?.

Hehe, I see. Well, I will register the idea as a feature request #2010:
https://github.com/jOOQ/jOOQ/issues/2010

I have plans of adding something like an org.jooq.Trigger, which can
be registered to listen to all sorts of events that go through jOOQ
(i.e. more sophisticated events than the ones received by
org.jooq.ExecuteListener). Maybe, there can be
UpdatableRecord-specific trigger actions, too:
https://github.com/jOOQ/jOOQ/issues/1691

> My use case is that I am using the UpdatableRecord for data binding
> purposes. It would be great to listen to value change events in the record
> so I can then sync back to the UI.

Is this mostly about value change events originating from calls like
record.refresh()? Could you elaborate a bit more on what you'd like to
do?

> This UpdatableRecord object is like a swiss army knife. I love it :)

Yes, well, there is always some risk with swiss army knife tools. If
things aren't implemented in a very general way, they add too much
complexity for users that don't need the extra feature. In this case,
improved listener support will align well with what is currently
called an ExecuteListener, though.

[Ryan How]

Hi,

> Is this mostly about value change events originating from calls like
> record.refresh()? Could you elaborate a bit more on what you'd like to
> do?
>

That would be one use case.

Or just a general property change listener like in a JavaBean. That way
I can bind it to a user interface field and if the record is updated
from somewhere other than the bound UI field, then the UI can be updated.

Something like

addValueChangeListener(ValueChangeListener)
fireValueChangeEvent(ValueChangeListener)
ValueChangeListener.valueChanged(Record, Field, OldValue, NewValue)

I'm sure you get the idea.

Or it could just use standard Javabean propertyChangeEvent


I think this would need to be implemented in the Record. But the trigger
idea sounds interesting. I'm all for hooks into Jooq. The more the
merrier :). What kind of events do you think could be hooked into using
the trigger?

Thanks, Ryan

[Lukas Eder]

Hello,

> Or just a general property change listener like in a JavaBean. That way I
> can bind it to a user interface field and if the record is updated from
> somewhere other than the bound UI field, then the UI can be updated.

Hmm, maybe this is indeed an option. After all, jOOQ-generated records
and JavaBeans have some things in common (getters, setters, etc.)

Maybe, there is a way to implement this at the client side, using
jooq-codegen's generated interfaces (which are implemented by the
records)? Although those interfaces wouldn't be able to capture all
possible events.

> I think this would need to be implemented in the Record. But the trigger
> idea sounds interesting. I'm all for hooks into Jooq. The more the merrier
> :).

I thought you'd think so :-)
If there are triggers, records could have hooks that notify all
applicable triggers

> What kind of events do you think could be hooked into using the trigger?

The general idea was to have jOOQ triggers work much like SQL
triggers. I.e. provide actions before/after/instead of
insert/update/delete, etc. I.e. a more high-level ExecuteListener. But
I haven't finished my thinking about this feature yet. On a second
thought, it doesn't seem to be an applicable way to implement
listeners on records... I'd just like to avoid adding too many
different ways of hooking callbacks into jOOQ. Having too many options
would be:

- hard to document
- hard to understand
- hard to maintain

Cheers
Lukas

[Ryan How]

On 14/12/2012 8:46 PM, Lukas Eder wrote:
> Hello,
>
>> Or just a general property change listener like in a JavaBean. That way I
>> can bind it to a user interface field and if the record is updated from
>> somewhere other than the bound UI field, then the UI can be updated.
> Hmm, maybe this is indeed an option. After all, jOOQ-generated records
> and JavaBeans have some things in common (getters, setters, etc.)
>
> Maybe, there is a way to implement this at the client side, using
> jooq-codegen's generated interfaces (which are implemented by the
> records)? Although those interfaces wouldn't be able to capture all
> possible events.

I'm not quite sure what you mean here? Do you mean having a setting in
the jooq.xml which says "generate ValueChangeEvents" or something along
those lines?

> The general idea was to have jOOQ triggers work much like SQL
> triggers. I.e. provide actions before/after/instead of
> insert/update/delete, etc. I.e. a more high-level ExecuteListener. But
> I haven't finished my thinking about this feature yet. On a second
> thought, it doesn't seem to be an applicable way to implement
> listeners on records... I'd just like to avoid adding too many
> different ways of hooking callbacks into jOOQ. Having too many options
> would be:
>
> - hard to document
> - hard to understand
> - hard to maintain

The idea sounds good. I would love this kind of functionality. Even to
implement say an audit trail. But as you have mentioned in the past it
is hard to capture these events, for instance for a merge statement it
is unknown which records would be updated / inserted and also in more
complicated updates. It would be fine for using the
UpdatableRecord.store() because it only works on a single record at at time.

I wonder if you could make an interface that plugs into different
databases capabilities to provide this functionality. For instance it
may need to create a database trigger which somehow communicates back to
JOOQ. I haven't used enough databases to know if this is even generally
possible. I guess at the least it would kill performance.

I don't think a global event would be suitable for what I am looking
for. For many other cases, yes, but in this case it would complicate
things (eg. I would have to remember to remove listeners from UI fields
when they are disposed, where usually the record would be disposed at
the same time as the UI, or beforehand)

Thanks, Ryan

[Lukas Eder]

>> Maybe, there is a way to implement this at the client side, using
>> jooq-codegen's generated interfaces (which are implemented by the
>> records)? Although those interfaces wouldn't be able to capture all
>> possible events.
>
> I'm not quite sure what you mean here? Do you mean having a setting in the
> jooq.xml which says "generate ValueChangeEvents" or something along those
> lines?

No, but you could generate interfaces and implement those interfaces
using java.lang.reflect.Proxy, hiding the actual record behind such a
proxy. Within the proxy, parts of this listener API can be
implemented, maybe.

But it would probably be a clumsy solution, compared to actual
listener support by jOOQ's core

> The idea sounds good. I would love this kind of functionality. Even to
> implement say an audit trail. But as you have mentioned in the past it is
> hard to capture these events, for instance for a merge statement it is
> unknown which records would be updated / inserted and also in more
> complicated updates. It would be fine for using the UpdatableRecord.store()
> because it only works on a single record at at time.

Yes, it is very hard to actually foresee what the database does. It is
probably even a bad idea to rely on anything, as the database can
implement a trigger that prevents an insert from being executed (and
executes an update instead). jOOQ would have no way to foresee that.

> I wonder if you could make an interface that plugs into different databases
> capabilities to provide this functionality. For instance it may need to
> create a database trigger which somehow communicates back to JOOQ. I haven't
> used enough databases to know if this is even generally possible. I guess at
> the least it would kill performance.

I guess that would be very unreliable...

> I don't think a global event would be suitable for what I am looking for.
> For many other cases, yes, but in this case it would complicate things (eg.
> I would have to remember to remove listeners from UI fields when they are
> disposed, where usually the record would be disposed at the same time as the
> UI, or beforehand)

Yes. Global event handlers are useful for other use-cases than yours.

Cheers
Lukas

[Ryan How]

> No, but you could generate interfaces and implement those interfaces
> using java.lang.reflect.Proxy, hiding the actual record behind such a
> proxy. Within the proxy, parts of this listener API can be
> implemented, maybe.
>
> But it would probably be a clumsy solution, compared to actual
> listener support by jOOQ's core

I thought about doing it a similar way. Very complicated for what would
seem like adding a few lines of code into the UpdatableRecord? Wouldn't
it just need to maintain a list of listeners and fire an event on
setValue? Or is it more complicated than I am imagining?

Thanks, Ryan

[Lukas Eder]

Hello Ryan

>> But it would probably be a clumsy solution, compared to actual
>> listener support by jOOQ's core
>
> I thought about doing it a similar way. Very complicated for what would seem
> like adding a few lines of code into the UpdatableRecord?

Yes it's a very complicated alternative.

> Wouldn't it just
> need to maintain a list of listeners and fire an event on setValue? Or is it
> more complicated than I am imagining?

OK, let's think this through

1. It is probably interesting to know WHY a value has been changed. So
we should probably distinguish between
1.1. external setter calls
1.2. calls to fromXXX() methods, loading values into the record
1.3. calls to refresh(), or the new refresh(Field...)
1.4. calls to the new reset() or reset(Field)
1.5. calls to the new changed() methods, which change the internal flags
1.6. calls to store(), which change the internal flags (some special
considerations may apply to batchStore)
1.7. calls to copy() (I'm not so sure what / whom to notify, though...)
The above could be distinguished using a RecordChangeType enum

2. Some change events are interesting on a Record-level (1.2, 1.3,
1.4, 1.5, 1.6, 1.7). Some clients may be interested in listening to
value-level change events, too (1.1, 1.3, 1.4, 1.5). While JavaBeans
mainly knows PropertyChangeEvents (i.e. value-level change events), I
think you may not always want to receive 20 "uncoordinated" events for
a single refresh()

3. Since some of the event sources are not really "vetoable" on a
value-level (1.3, 1.6), I don't think that a JavaBeans
VetoableChangeSupport is generally applicable. PropertyVetoException
being a checked exception, this might be irrelevant anyway. Hence, I'm
guessing that events are fired *after* the change is made.

4. We would have a List<RecordChangeListener> within
org.jooq.impl.AbstractRecord (all Record implementations), as well as
a List<ValueChangeListener> within every org.jooq.impl.Value. A
RecordChangeEvent would then contain a List<ValueChangeEvent>, every
ValueChangeEvent would reference its parent RecordChangeEvent. Events
would be fired as soon as there is at least one listener in the Record
/ in at least one Value

5. Listener types and Event types will have to be implemented in jOOQ.
JavaBeans PropertyChangeEvent is probably too general, and too much
focused on bean property changes in the context of AWT / Swing
applications

Any other points that I may have overlooked?

Cheers
Lukas

[Ryan How]

Hi Lukas,

> OK, let's think this through
>
> 1. It is probably interesting to know WHY a value has been changed. So
> we should probably distinguish between
> 1.1. external setter calls
> 1.2. calls to fromXXX() methods, loading values into the record
> 1.3. calls to refresh(), or the new refresh(Field...)
> 1.4. calls to the new reset() or reset(Field)
> 1.5. calls to the new changed() methods, which change the internal flags
> 1.6. calls to store(), which change the internal flags (some special
> considerations may apply to batchStore)
> 1.7. calls to copy() (I'm not so sure what / whom to notify, though...)
> The above could be distinguished using a RecordChangeType enum
>
> 2. Some change events are interesting on a Record-level (1.2, 1.3,
> 1.4, 1.5, 1.6, 1.7). Some clients may be interested in listening to
> value-level change events, too (1.1, 1.3, 1.4, 1.5). While JavaBeans
> mainly knows PropertyChangeEvents (i.e. value-level change events), I
> think you may not always want to receive 20 "uncoordinated" events for
> a single refresh()

Well you thought about it a lot more than I did :). For my use case I am
happy with a simple value change listener. It can get fired on any
source of the setValue call and can be fired 20 times if there are 20
fields, I don't mind, it is just to keep the UI in sync which would
require updating each field anyway.

But yes it does make sense that if you are going to add this
functionality it would be worth going the entire way and be able to
distinguish why an update occurred and also handle record level and
value level changes.

Perhaps that kind of functionality would be better in the "trigger"
system you were describing. eg. when a store() is issued, then the
trigger could fire and pass the record object, what fields were changed
and if it was being inserted or updated. It would save bloating the
Record object.

I guess for my use case I only need the functionality in the
UpdatableRecord type.

> 3. Since some of the event sources are not really "vetoable" on a
> value-level (1.3, 1.6), I don't think that a JavaBeans
> VetoableChangeSupport is generally applicable. PropertyVetoException
> being a checked exception, this might be irrelevant anyway. Hence, I'm
> guessing that events are fired *after* the change is made.

I guess this would be an interesting way of enforcing some validation /
constraints on a field. But probably not the best way to go around it.
It wouldn't be the place to be defining such constraints anyway. And it
doesn't really appear to anywhere else in the scope of JOOQ?

>
> 4. We would have a List<RecordChangeListener> within
> org.jooq.impl.AbstractRecord (all Record implementations), as well as
> a List<ValueChangeListener> within every org.jooq.impl.Value. A
> RecordChangeEvent would then contain a List<ValueChangeEvent>, every
> ValueChangeEvent would reference its parent RecordChangeEvent. Events
> would be fired as soon as there is at least one listener in the Record
> / in at least one Value

I wonder of the memory and performance overhead that introduces?. I
guess on an initial query there are no listeners registered. And for a
large query you aren't going to register a listener on every record. In
my use case I would only want to register on an UpdatableRecord.

> 5. Listener types and Event types will have to be implemented in jOOQ.
> JavaBeans PropertyChangeEvent is probably too general, and too much
> focused on bean property changes in the context of AWT / Swing
> applications

Yes I agree.

>
> Any other points that I may have overlooked?

You seem to have thought about it pretty thoroughly. It would work for
me. I would be concerned all the extra data would have an impact on
performance and memory usage. If you load a large data set though JOOQ
into memory then all those extra bytes count...

Getting a bit off topic... The more I have been using JOOQ, the more
things I keep wanting to add to it. It is a great project. It has really
changed the architecture of my system. It eliminates a lot of the data
layer and makes it a whole lot more agile. It would be great to be able
to plug into it in all kinds of places. So for me the more extension and
plugin points the better. I still want to implement an audit trail
system, an extensive validation system and a trigger system that all
runs through JOOQ.

You made a suggestion earlier about using jooq-codegen. Maybe there
could be a way to "plug into" JOOQ by having the generated JOOQ record
objects extend from a custom class, or add hooks into a custom class.
Then extra / custom functionality could be inserted in there. I could
then "intercept" all value changes and use my own listener system that I
coded in there. I guess that is really similar to the proxy idea.

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

> Perhaps that kind of functionality would be better in the "trigger" system
> you were describing. eg. when a store() is issued, then the trigger could
> fire and pass the record object, what fields were changed and if it was
> being inserted or updated. It would save bloating the Record object.

The problem with the trigger is the fact that it is quite global. You
will probably want to attach listeners to concrete record instaces...

>> 4. We would have a List<RecordChangeListener> within
>> org.jooq.impl.AbstractRecord (all Record implementations), as well as
>> a List<ValueChangeListener> within every org.jooq.impl.Value. A
>> RecordChangeEvent would then contain a List<ValueChangeEvent>, every
>> ValueChangeEvent would reference its parent RecordChangeEvent. Events
>> would be fired as soon as there is at least one listener in the Record
>> / in at least one Value
>
> I wonder of the memory and performance overhead that introduces?

Yes, that's what I was wondering, too.

>. I guess on
> an initial query there are no listeners registered. And for a large query
> you aren't going to register a listener on every record. In my use case I
> would only want to register on an UpdatableRecord.

Every record would hold one more reference (or null), which is OK. But
I'm not such a big fan of adding one additional reference to every
org.jooq.impl.Value object. Maybe there is a simpler implementation,
that doesn't need one reference per value.

> Getting a bit off topic... The more I have been using JOOQ, the more things
> I keep wanting to add to it. It is a great project. It has really changed
> the architecture of my system. It eliminates a lot of the data layer and
> makes it a whole lot more agile. It would be great to be able to plug into
> it in all kinds of places. So for me the more extension and plugin points
> the better. I still want to implement an audit trail system, an extensive
> validation system and a trigger system that all runs through JOOQ.

Nothing that ExecuteListener could help you with, so far? I'm open to
any more detailed elaboration of your ideas!

> You made a suggestion earlier about using jooq-codegen. Maybe there could be
> a way to "plug into" JOOQ by having the generated JOOQ record objects extend
> from a custom class, or add hooks into a custom class. Then extra / custom
> functionality could be inserted in there. I could then "intercept" all value
> changes and use my own listener system that I coded in there. I guess that
> is really similar to the proxy idea.

Yes, in jOOQ 3.0, I hope a lot more will be possible out of the box,
without rewriting parts of the code generator. Right now, you can only
let your record classes implement custom interfaces, not extend custom
classes.

Cheers
Lukas

[Ryan How]

Hi Lukas,

On 18/12/2012 4:16 AM, Lukas Eder wrote:
> Every record would hold one more reference (or null), which is OK. But
> I'm not such a big fan of adding one additional reference to every
> org.jooq.impl.Value object. Maybe there is a simpler implementation,
> that doesn't need one reference per value.

Do all the "setValue" calls go through the record object? or can it
happen directly on the value object?

Can these listeners go in UpdatableRecordImpl instead of the base Record
class?

>> Getting a bit off topic... The more I have been using JOOQ, the more things
>> I keep wanting to add to it. It is a great project. It has really changed
>> the architecture of my system. It eliminates a lot of the data layer and
>> makes it a whole lot more agile. It would be great to be able to plug into
>> it in all kinds of places. So for me the more extension and plugin points
>> the better. I still want to implement an audit trail system, an extensive
>> validation system and a trigger system that all runs through JOOQ.
> Nothing that ExecuteListener could help you with, so far? I'm open to
> any more detailed elaboration of your ideas!

Yes, my plan is to use the execute listener and parse the SQL statement
to get Tables / Fields updated. It would be great if the update
initiated from an UpdatableRecord to actually pass that record, or if it
was an Update statement then to pass the fields in the statement. It
would save me parsing the SQL :). I know you can write SQL in JOOQ, but
if this additional information available then I could use it in preference.

I'll prevent certain queries from being issued that affect certain
tables. (eg. can't run a merge statement, for some tables only a single
record can be updated at a time). It will impose some restrictions, but
If I did this through a DAO it would impose restrictions anyway. Tables
I am running a MERGE on, I probably wouldn't want to keep an audit trail
for every record anyway...

The execute listener will store all the changes until a transaction is
committed, then before it is committed it can be run through other
systems, eg. validation, generating audit trails. That way it can all
happen as part of the same transaction and I can make sure the database
will be in a "correct" state when the transaction is committed. eg.
Record A requires certain records in Record B to make sense. So on
committing anything that affects record A, it can check the
corresponding records in B are valid in the same transaction. I'm using
MVCC in the database, otherwise this probably wouldn't work
consistently. It also means I can define some hard complex validation
rules and I know they will always be enforced, because all updates run
through the execute listener.

Also this way instead of running everything through a DAO and having
heaps of boilerplate code to maintain, I can pipe it through that
central location and then define elsewhere any constraints, audit trail
generation, etc. I'm also planning on trying to catch updates prior to
JOOQ executing them on the database to do "pre execution validation and
triggers", but this is really outside of the scope of JOOQ. I'm
basically just storing up a bunch of UpdatableRecord objects, then
commit them all at once after running some checks and triggers on the
records as a set. For instance a certain object might require a "reason"
for being changed. So in this check, it will detect that it needs a
"reason", so pop up asking for a reason, then once satisfied it will run
the commit.

I think it will work :). I'll see once I've implemented it. I'm just
sick of applications that have DAOs everywhere, then on changing a table
structure slightly I have to update it in 10 places. Then I forget to
update it somewhere and find out a few months later... Also, devs can
really easily circumvent constraints. If it all pipes through though
JOOQ then I can catch everything there. I've done a similar thing using
Hibernate, but it was a bit messy, and hibernate is an ORM.

Also, all write access to the database access goes through the
application, so I don't have to worry about defining too many things at
a database level. Otherwise I'd be using stored procedures for
everything and have most of the logic / validation / triggers / etc in
the database. I'd rather define and maintain it in java.

All part of my quest to try and make the data layer a whole lot easier
to change and maintain. So far going well. I keep restructuring tables
over and over. I have been implementing some basic DAOs where re-using
the same complex query, but they are quite easy to maintain.

Anyway, once I have it working (hopefully!) I'll let you know if it
works, or if it is fundamentally floored and was never going to work.

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

>> Every record would hold one more reference (or null), which is OK. But
>> I'm not such a big fan of adding one additional reference to every
>> org.jooq.impl.Value object. Maybe there is a simpler implementation,
>> that doesn't need one reference per value.
>
> Do all the "setValue" calls go through the record object? or can it happen
> directly on the value object?

In jOOQ, internally, yes it can happen. AbstractRecord exposes its
values for jOOQ's internals.
From the public API, no, it cannot happen.

> Can these listeners go in UpdatableRecordImpl instead of the base Record
> class?

That might be worth exploring. I have thought about this before, as
"base records" (e.g. records fetched from joined relations) might not
really need all the overhead produced from "changed" flags, "original"
values and now, these listeners. That's something to be decided in
jOOQ 3.0.

On the other hand, you can transform record types into other record
types using the into() and from() methods. So maybe, some users would
still like to see these flags, etc on the base records as well...

>> Nothing that ExecuteListener could help you with, so far? I'm open to
>> any more detailed elaboration of your ideas!
>
> Yes, my plan is to use the execute listener and parse the SQL statement to
> get Tables / Fields updated. It would be great if the update initiated from
> an UpdatableRecord to actually pass that record, or if it was an Update
> statement then to pass the fields in the statement. It would save me parsing
> the SQL :). I know you can write SQL in JOOQ, but if this additional

> information available then I could use it in preference. [...]

I see, thanks for sharing! I've noticed that people start to try to
use jOOQ for such elaborate plans. It's a good way to implement
central triggering / validation / audit trails. Currently, what you
need is only possible through SQL parsing or through reflection. The
actual query object is available from ExecuteContext.query():
http://www.jooq.org/javadoc/latest/org/jooq/ExecuteContext.html#query()

However, jOOQ's Query subtypes currently don't expose their internals.
This is a pending feature request on the roadmap for jOOQ 3.0:
https://github.com/jOOQ/jOOQ/issues/1492

I've tried implementing this ticket before, but I'm afraid it won't be
possible sensibly, without breaking (some) API. Hence it cannot be
shipped before the next major release.

> Anyway, once I have it working (hopefully!) I'll let you know if it works,
> or if it is fundamentally floored and was never going to work.

I'd be glad to hear that!

Cheers
Lukas