Resetting Record original values

[Ryan How]

Hi Lukas,

Just a follow on from this discussion.

https://groups.google.com/d/msg/jooq-user/0F1YSZB2xDk/BDrz9cBxnngJ

Is there a way to set the records "original" values after a store()?. We can set the changed flag, and reset the value to match the original value, but I couldn't see a way to set the original value itself? I might just be missing some obvious.

The reason being that the original values are used for the optimistic locking check. After a store, then subsequent fail and transaction rollback, I need to manually roll back the original values, or the optimistic lock check will fail next attempt. I hope that makes sense.

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

2013/7/31 Ryan How <rh...@exemail.com.au>

Hi Lukas,

Just a follow on from this discussion.

https://groups.google.com/d/msg/jooq-user/0F1YSZB2xDk/BDrz9cBxnngJ

Is there a way to set the records "original" values after a store()?. We can set the changed flag, and reset the value to match the original value, but I couldn't see a way to set the original value itself? I might just be missing some obvious.

There's currently no obvious way to do that. If you look at jOOQ's internals, you can modify "original" values as such:

    record.setValue(field, originalValue);

    record.changed(field, false); // Sets originalValue onto "original"

Or, if you want to retain distinct "original" and "value" values:

    T value = record.getValue(field);

    record.setValue(field, originalValue);

    record.changed(field, false);

    record.setValue(field, value);

This has a tweaky touch to it, though, use at your own risk :-)

The reason being that the original values are used for the optimistic locking check. After a store, then subsequent fail and transaction rollback, I need to manually roll back the original values, or the optimistic lock check will fail next attempt. I hope that makes sense.

I remember the discussion. Can you shed some light on the projected algorithm? Are you using ExecuteListeners to inject some transaction management into jOOQ? Maybe, after all, a "magic", long-term solution will yet unveil...

Cheers

Lukas

[Ryan How]

Hi Lukas,

That did the trick! Thanks! :)

In the very simplistic form:

Load in any record objects as required, these are usually bound to forms, tables, etc. This can happen in multiple transactions, in several stages as the user needs, etc

User does any changes as needed to the records (inclusing adding records and deleting records)

On save

try {

open connection & transaction

storeRecordOriginalState

record.store() on each changed record (Or delete if deleting)

commit transaction

} catch (Optimistic Lock Exception) {

Special handling involving the user to resolve issues

} catch (Any other Exception) {

rollback transaction

restore records back to their original state

} finally () {
close connection

}

So it is important to be able to roll back database changes and not lose information in the records or the optimistic locking will not work afterwards.

It's working well now :)

UI <-> JOOQ Records <-> Database.

It would be fantastic if it could all happen automatically just by calling .store(). That would be "magic". I don't think that should be integrated into JOOQ and the architecture is very application dependent, but it would be great if JOOQ could provide some hooks so I could hook into .store() to do all the additional stuff. Thinking about it... it is sounding a lot like hibernates flush(). Load a bunch of records, do whatever changes, on calling flush() it persists it to database, with any checks... But I never really liked how hibernate did that :). It would be better to accumulate all the records that you want only in a set, then just attach that set to a connection and .store(). I guess a set <-> transaction auto populating could happen too. We also do a few things like linking related records before storing them (because they don't have primary keys yet), then it stores them in the correct order and populates the foreign keys.

Anyway, I'm happy with how it is working at the moment, it isn't worth the extra effort for me to try and support this flexibility unless I start another project and want to get it in from the start. JOOQ does a great job of what it does. It seems like it would be expanding the scope of jooq to start getting into this other stuff, could almost be a separate project.

[Lukas Eder]

Hi Ryan,

2013/8/2 Ryan How <rh...@exemail.com.au>

Hi Lukas,

That did the trick! Thanks! :)

In the very simplistic form:

Load in any record objects as required, these are usually bound to forms, tables, etc. This can happen in multiple transactions, in several stages as the user needs, etc

User does any changes as needed to the records (inclusing adding records and deleting records)

On save

try {

open connection & transaction

storeRecordOriginalState

record.store() on each changed record (Or delete if deleting)

commit transaction

} catch (Optimistic Lock Exception) {

Special handling involving the user to resolve issues

} catch (Any other Exception) {

rollback transaction

restore records back to their original state

} finally () {
close connection

}

Thanks for sharing this! Makes sense to me.

 

So it is important to be able to roll back database changes and not lose information in the records or the optimistic locking will not work afterwards.

It's working well now :)

UI <-> JOOQ Records <-> Database.

It would be fantastic if it could all happen automatically just by calling .store(). That would be "magic". I don't think that should be integrated into JOOQ and the architecture is very application dependent, but it would be great if JOOQ could provide some hooks so I could hook into .store() to do all the additional stuff.

I remember discussing #2010 with you.

https://groups.google.com/forum/#!searchin/jooq-user/2010/jooq-user/tMN1n3HCDz8/CfavBiBWi0UJ

I'm currently implementing this for jOOQ 3.2:

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

This is why I'm reaching out to see what else might be needed in this new SPI to simplify things for people wanting to interact with jOOQ's optimistic locking. After all, I might not remove this feature in 4.0, if it can be solved properly :-)

 

Thinking about it... it is sounding a lot like hibernates flush(). Load a bunch of records, do whatever changes, on calling flush() it persists it to database, with any checks... But I never really liked how hibernate did that :). It would be better to accumulate all the records that you want only in a set, then just attach that set to a connection and .store(). I guess a set <-> transaction auto populating could happen too. We also do a few things like linking related records before storing them (because they don't have primary keys yet), then it stores them in the correct order and populates the foreign keys.

Interesting thoughts. Yes, I don't think that jOOQ should implement a Hibernate-y flush, which does things automatically (i.e. magically). Being able to drive the behaviour using an SPI (with 1-2 default implementations) seems more appropriate.

 

Anyway, I'm happy with how it is working at the moment, it isn't worth the extra effort for me to try and support this flexibility unless I start another project and want to get it in from the start. JOOQ does a great job of what it does. It seems like it would be expanding the scope of jooq to start getting into this other stuff, could almost be a separate project.

Yes it would be a separate project.

Cheers

Lukas

[Venkat Sadasivam]

Ryan - You have discovered a nice problem to be resolved. I cache Record object at web layer to re-use them across multiple http request, if my transaction is rolled back then my cache object at web layer becomes incorrect also its too hard to track and rollback every record updated in the failed transaction.

Lukas - from a framework perspective it would be nice to add rollback() method in UpdatableRecord to  bring it back to original state then its upto the transaction layer to call the rollback() method.

[Lukas Eder]

Hello,

2013/8/6 Venkat Sadasivam <venka...@gmail.com>

Ryan - You have discovered a nice problem to be resolved. I cache Record object at web layer to re-use them across multiple http request, if my transaction is rolled back then my cache object at web layer becomes incorrect also its too hard to track and rollback every record updated in the failed transaction.

Lukas - from a framework perspective it would be nice to add rollback() method in UpdatableRecord to  bring it back to original state then its upto the transaction layer to call the rollback() method.

The problem here is to know what the "original" state really is.

jOOQ already maintains an "original" state through Record.original(). This state corresponds to what was originally loaded from the database. Upon successful store, this "original" state is set to the Record's value.

 

While I agree that this behaviour is cumbersome when rolling back a transaction, I'm not sure if there's an easy solution to this, which suits all use-cases and transaction models.

The silliest solution to this problem might be to add an UpdatableRecord.refreshOriginal() method, to re-read original values from the database, without affecting the other values. Would that make sense?

Cheers

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/groups/opt_out.
 
 

[Ryan How]

On 7/08/2013 3:24 PM, Lukas Eder wrote:

Hello,

2013/8/6 Venkat Sadasivam <venka...@gmail.com>

Ryan - You have discovered a nice problem to be resolved. I cache Record object at web layer to re-use them across multiple http request, if my transaction is rolled back then my cache object at web layer becomes incorrect also its too hard to track and rollback every record updated in the failed transaction.

Lukas - from a framework perspective it would be nice to add rollback() method in UpdatableRecord to  bring it back to original state then its upto the transaction layer to call the rollback() method.

The problem here is to know what the "original" state really is.

jOOQ already maintains an "original" state through Record.original(). This state corresponds to what was originally loaded from the database. Upon successful store, this "original" state is set to the Record's value.

 

While I agree that this behaviour is cumbersome when rolling back a transaction, I'm not sure if there's an easy solution to this, which suits all use-cases and transaction models.

The silliest solution to this problem might be to add an UpdatableRecord.refreshOriginal() method, to re-read original values from the database, without affecting the other values. Would that make sense?

Cheers

Lukas

For that to work wouldn't it need to know the original original values?, or at least the original original primary key value, otherwise after it is stored, the original state will be updated, so refreshing it may not be accurate.

It would almost seem to work with all cases it would need record "versioning". Then it could handle savepoints too and roll back to an arbitrary point. Then it is just up to the end implementation to hook in the records with the transaction layer to keep it all in sync.

I think with "versioning", those listeners you are working on, and record "sets" to "track" all your records, you could implement just about any use case you needed to. It is just up to the end implementation to hook it all together in a way that makes sense for them (eg. hooking up a "set" to a transaction so they can be committed and rolled back as a single unit).

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/0bwAOu2LVjo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2013/8/8 Ryan How <ry...@zbit.net.au>

On 7/08/2013 3:24 PM, Lukas Eder wrote:

Hello,

2013/8/6 Venkat Sadasivam <venka...@gmail.com>

Ryan - You have discovered a nice problem to be resolved. I cache Record object at web layer to re-use them across multiple http request, if my transaction is rolled back then my cache object at web layer becomes incorrect also its too hard to track and rollback every record updated in the failed transaction.

Lukas - from a framework perspective it would be nice to add rollback() method in UpdatableRecord to  bring it back to original state then its upto the transaction layer to call the rollback() method.

The problem here is to know what the "original" state really is.

jOOQ already maintains an "original" state through Record.original(). This state corresponds to what was originally loaded from the database. Upon successful store, this "original" state is set to the Record's value.

 

While I agree that this behaviour is cumbersome when rolling back a transaction, I'm not sure if there's an easy solution to this, which suits all use-cases and transaction models.

The silliest solution to this problem might be to add an UpdatableRecord.refreshOriginal() method, to re-read original values from the database, without affecting the other values. Would that make sense?

Cheers

Lukas

For that to work wouldn't it need to know the original original values?, or at least the original original primary key value, otherwise after it is stored, the original state will be updated, so refreshing it may not be accurate.

I haven't thought this through. It may as well be that refreshOriginal() would just add yet another layer of 90%-completeness to this implementation. Thus, the "silliest solution". But jOOQ's CRUD Javadoc already has a couple of references to it assuming a certain level of relational normalisation. I.e. updating primary key values in a record is not recommended.

But the question remains: how would such a "refreshOriginal()" method behave once a transaction is committed? Will that "original original" need to be adapted then? Sigh...

It would almost seem to work with all cases it would need record "versioning". Then it could handle savepoints too and roll back to an arbitrary point. Then it is just up to the end implementation to hook in the records with the transaction layer to keep it all in sync.

I think with "versioning", those listeners you are working on, and record "sets" to "track" all your records, you could implement just about any use case you needed to. It is just up to the end implementation to hook it all together in a way that makes sense for them (eg. hooking up a "set" to a transaction so they can be committed and rolled back as a single unit).

Yes. In the long run, I'd favour such a solution, myself. What I like about this, in particular, is the fact that an SPI exists for users to implement their own versioning, which is well-aligned to their transaction model (including savepoints, as you've mentioned).

Eventually, we'll get there, I think! :-)