Change History Information

[sepp2...@gmail.com]

Good afternoon,

there are two features I need to implement in the software which I'm currently developing:

1. creation date, last change date, creator-user, last modified user as columns in each table for basic tracking of that information
   
2. complete change history of each table-row (in each table)
   

I'm coming from C# world, where I worked a few years with a modified Subsonic ORM Layer. Now I have to develop this new software in Java, and due to some recent projects where I already used jOOQ I'm also using jOOQ right now.

The first feature could be done on every saving operation, just upfront setting the values. However, 2 fields are current date, and the other two the current user. It would be cool e.g. to just use:

dbRecord.store(userId)

and that the "magic" is out of sight of the developer.

The second feature is a whole different story. Currently I'm thinking of creating triggers that will do the work in the DBMS (postgreSQL). But maybe there are other solutions too? 

Sepp

[Lukas Eder]

Hi Sepp,

Thank you very much for your enquiry. Currently, jOOQ doesn't support any audit functionality or one/two dimensional historisation functionality yet. Both features are on the roadmap, though:

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

- https://github.com/jOOQ/jOOQ/issues/2605

Already now, you can implement a fair share of auditing directly using the RecordListener SPI, which is documented here:

http://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/crud-record-listener

With this, you can intercept store calls on record. Do note, however, that all other means of storing data in the database will bypass this mechanism. You could also intercept those calls by implementing the VisitListener SPI and listening to all sorts of DML events, but that's a lot of work:

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

And still, you can bypass this mechanism, e.g. by using JDBC or plain SQL directly, or via stored procedures or other ways to insert/update your data.

The most robust way forward is to use database triggers for both your bullet points.

Hope this helps,
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.

[sepp2...@gmail.com]

Thank you Lukas,

 

now you have confirmed that I did not miss that feature in the docs. However, the RecordListener can only be used for setting the date columns (which can really easy be set in the trigger). However, typically the user of the application (this is a web application) is logged in, the request is identified and bound to that user account, and in that place I have to set some information to the store-method. 

However, your link also showed a custom extension:

https://github.com/jOOQ/jOOQ/issues/1592#issuecomment-28075494

I will give this a try, seems an appropriate way for me.

Sepp

[Lukas Eder]

Hi Sepp,

I forgot about that custom extension. Indeed, that could work, although, you will have to remember to call persist() and not store()...

Another solution is to put some context information in the Configuration.data() map, which you can then access from the RecordListener's RecordContext.data() map...

--

[sepp2...@gmail.com]

Hi Lukas,

I found another way, which seems to me pretty easy to add: 

http://www.jooq.org/doc/3.8/manual/code-generation/codegen-custom-code/

However, I didn't find how to use the extended class?  The codegen is called on the cmd-prompt with java -classpath... org.jooq.util.GenerationTool /config.xml

If I add my created .jar (which contains the extended class ExtendedJooqGenerator (which extends the JavaGenerator) is is still not used. :) So there has to be some glue?

Sepp

[sepp2...@gmail.com]

found it for myself. :) Had to put it in the name element of the generator element in the config.xml 

[Lukas Eder]

Exactly! :)

2016-05-28 8:45 GMT+02:00 <sepp2...@gmail.com>:

found it for myself. :) Had to put it in the name element of the generator element in the config.xml 

--