Re: Obtain primary key for DataObject before commitChanges
On Tue, Aug 11, 2015 at 8:49 PM, Aristedes Maniatis a...@maniatis.org wrote: I'm interested in a different part of the problem you are solving. Other than the relationship data discuss here, how are you storing the actual changes in your audit table? Does your problem just require Bob changed record 23 or are you keeping a complete diff of the changes? If the latter, how are you serialising those changes into the audit log? I've been involved with auditing in at least three projects over the last 15 years, and have handled them similarly each time, although we also have used record-level logging (adding date/time of last change to each record) in addition in one project. This is a combined summary of what I've used in the past and what I'd probably use in the next project, along with any particular application-specific fields, such as REAL_USER_ID and EFFECTIVE_USER_ID. db-entity name=LOG db-attribute name=ID type=INTEGER isPrimaryKey=true isMandatory=true length=22/ db-attribute name=MODIFICATION_DATE type=TIMESTAMP isMandatory=true/ db-attribute name=MODIFICATION_TYPE type=VARCHAR length=1/ db-attribute name=TABLE_NAME type=VARCHAR isMandatory=true length=32/ db-attribute name=COLUMN_NAME type=VARCHAR isMandatory=true length=64/ db-attribute name=FOREIGN_RECORD_KEY type=INTEGER length=22/ db-attribute name=FKEY_CONDITION type=VARCHAR length=200/ db-attribute name=OLD_VALUE type=VARCHAR length=4000/ db-attribute name=NEW_VALUE type=VARCHAR length=4000/ db-attribute name=EFFECTIVE_USER_ID type=INTEGER length=22/ db-attribute name=REAL_USER_ID type=INTEGER length=22/ /db-entity It's a complete diff of any database change, when it was made, and who made it. If necessary, it can be used to reverse a change or replay a change back.Not only does it fulfil general auditing purposes, but it's been very helpful in debugging what an end-user really did as opposed to what they claim they did. It could potentially be hard to determine what data was committed together since it's at such a fine-grained level and the application is multi-threaded. Perhaps adding a commit id would be a slight improvement, but the MODIFICATION_DATE timestamp has been sufficient so far. For a schema where every record has a single primary key of a specific type (such as integer), you only need FOREIGN_RECORD_KEY. If you have compound primary keys, then you need to also use FKEY_CONDITION which is a string-representation of your primary key.
Re: Expressions involving dates can't be converted to EJBQL?
Hi Andrew, thanks for that. I see what the problem is. But if I can’t use EJBQL when generating queries that use dates, can I go down any different paths or do I have to revert to writing plain old SQL? Cheers, - hugi On 12. ágú. 2015, at 05:36, Andrew Lindesay a...@lindesay.co.nz wrote: Hi Hugi; I think this was me; see CAY-1932 for some details on what I did there. cheers. On 12/08/15 10:09, Hugi Thordarson wrote: Hi all. I have a utility method that selects a row count given a DataObject class and an Expression, as seen here: https://gist.github.com/hugith/d2cdaf60a6ee12aa0d17 https://gist.github.com/hugith/d2cdaf60a6ee12aa0d17 I was using this today when I suddenly got the error message the scalar type 'Date' is not supported as a scalar type in EJBQL”. Turns out that if I use a date in an Expression, it can’t be converted to EJBQL. Can anyone suggest a workaround? Also, should I be creating utility methods like this in a different way than by using EJBQL? I have quite a few utility methods that all construct queries using EJBQL, but they become rather useless if I can’t use dates in them. Cheers and thanks, - hugi -- Andrew Lindesay
Re: Obtain primary key for DataObject before commitChanges
For the last few years while using EOF, I’ve been storing changes in the audit log as a map serialized to a String NSPropertyList (the NS* world’s equivalent of JSON). It works fine, but once the log starts to grow, there are performance implications. This time around I’m going for a three table approach that looks something like: - TransactionObject (reference to object) - TransactionObjectLifecycleEvent (user, date and type of action; Insert, Update or Delete) - TransactionObjectFieldValue (name of changed attribute along with new value, serialized to a string) These relate to each other: TransactionObject — TransactionObjectLifecycleEvent — TransactionObjectFieldValue I’m currently planning on ignoring relationships in the object graph and just storing actual values of attributes and foreign keys. We’ll see how that works out. Criticism and ideas for a better implementation are very welcome :). Cheers, - hugi On 12. ágú. 2015, at 00:49, Aristedes Maniatis a...@maniatis.org wrote: I'm interested in a different part of the problem you are solving. Other than the relationship data discuss here, how are you storing the actual changes in your audit table? Does your problem just require Bob changed record 23 or are you keeping a complete diff of the changes? If the latter, how are you serialising those changes into the audit log? Ari On 12/08/2015 8:13am, Hugi Thordarson wrote: Thanks for the ideas Mike. After a little mulling, I think your idea of dynamically modeling relationships to the audit table at application startup is the best one I’ve heard so far. I’m going to take a peek down that road tomorrow :) Cheers, - hugi On 10. ágú. 2015, at 14:24, Mike Kienenberger mkien...@gmail.com wrote: Not sure if it's clear, but there are two different approaches to your problem described. 1) You can set a foreign relationship to your audit table and let Cayenne assign the key at commit. This is probably the easiest especially if you dynamically create the relationships in java code. Except for the initialization of these relationships at some point (startup or first time you hit the audit code), nothing else has to be done and that's the only cayenne-internals code you have to deal with. 2) You can use something like the example code to grab the primary key values after cayenne generates them but before the actual database query executes. I doubt that prePersist is early enough, but it might be. Otherwise you will have to go looking for a different hook. This requires a lot more knowledge of cayenne internals in my opinion. This is also a much harder problem if you're deferring the key generation to the database rather than using sequences or some similar approach, but Cayenne obviously has a way to handle that now or regular foreign key setup for relationships wouldn't work. Your third idea of using postPersist has its own problems. If you decide to use a postPersist and set this information in a separate commit, then you run the risk of your audit log not being created and losing your audit information. For my environment, that wasn't acceptable. Maybe you could wrap both commits in a single transaction -- I'm not certain, but maybe that could work. Transaction support either wasn't there back when I was doing this or I didn't understand that it could solve my problem back then. On Mon, Aug 10, 2015 at 10:13 AM, Hugi Thordarson h...@karlmenn.is wrote: Thanks Mike! Although the approach I’m working on is a little different (and meant to be reusable with any Cayenne installation so can’t depend on superclass template modifications), it’s very helpful to see code from other Cayenne folks. Cheers, - hugi // Hugi Thordarson // http://www.loftfar.is/ http://www.loftfar.is/ // s. 895-6688 On 10. ágú. 2015, at 12:53, Mike Kienenberger mkien...@gmail.com wrote: I set up auditing using a different approach in one project many years ago back in Cayenne 1.1, and I've continued using it up to this point in 3.x. I generated special setter, addTo, and removeFrom methods as well as a create method which created the logger object at that point. To get the primary key, I set up one-way object relationships between the primary key of the logged object and the foreign key storage field in the audit log, one for each object entity. When the commit happened, the relationships automatically populated the audit log fields. You probably can do the same thing. There's probably a better way to set up the object relationships than manually defining them in your model these days. In a different Cayenne 1.2 project, I used something similar to your pre-persist hook, although we didn't have prePersist yet. For this one, I had two foreign record key fields, one used for a single column primary key and one used for compound primary keys. I'm not sure if the code will still work
Re: Expressions involving dates can't be converted to EJBQL?
Hi Hugi; You can use EJBQL queries with timestamps, but there is presently no supported _literal_ representation for a timestamp inside the query string so you have to use parameters instead. Here is an example; EJBQLQuery q = new EJBQLQuery(String.format( DELETE FROM %s r WHERE r.createTimestamp :expiryTimestamp, Response.class.getSimpleName())); q.setParameter(expiryTimestamp, new Timestamp(System.currentTimeMillis() - TimeUnit.SECONDS.toMillis(expirySeconds))); getServerRuntime().getContext().performQuery(q); To help with getting Expression objects to over to EJBQL with timestamp literals; Expression#toEJBQL(ListObject parameterAccumulator, String rootId) So you call that and as it creates the EJBQL fragment, it will insert any necessary parameters into the parameterAccumulator for you all set to go into the EJBQLQuery. Hopefully that helps? cheers. [1] https://github.com/aplgithub/haikudepotserver/blob/master/haikudepotserver-webapp/src/main/java/org/haikuos/haikudepotserver/captcha/DatabaseCaptchaRepository.java For example; SELECT f FROM Foo f WHERE f.createTimestamp ? Then you just need to use the On 12/08/15 20:35, Hugi Thordarson wrote: Hi Andrew, thanks for that. I see what the problem is. But if I can’t use EJBQL when generating queries that use dates, can I go down any different paths or do I have to revert to writing plain old SQL? -- Andrew Lindesay
Re: Obtain primary key for DataObject before commitChanges
Interesting. Questions: * What is the purpose of TransactionObject? * Is there a performance advantage to TransactionObjectFieldValue with multiple rows per change event rather than storing larger json serialised set of changes in one record? I mean, I understand that it allows you to ask what changes were made to this specific field over time without loading and parsing lots of json. But is that how the audit will be used? Will creating 20 times as many rows scale better than storing larger text CLOBs? * How are you grouping changes (eg. if in a single commit a user changes two records at once)? * How do you handle changes to the schema after version data is created? Ari On 12/08/2015 6:18pm, Hugi Thordarson wrote: For the last few years while using EOF, I’ve been storing changes in the audit log as a map serialized to a String NSPropertyList (the NS* world’s equivalent of JSON). It works fine, but once the log starts to grow, there are performance implications. This time around I’m going for a three table approach that looks something like: - TransactionObject (reference to object) - TransactionObjectLifecycleEvent (user, date and type of action; Insert, Update or Delete) - TransactionObjectFieldValue (name of changed attribute along with new value, serialized to a string) These relate to each other: TransactionObject — TransactionObjectLifecycleEvent — TransactionObjectFieldValue I’m currently planning on ignoring relationships in the object graph and just storing actual values of attributes and foreign keys. We’ll see how that works out. Criticism and ideas for a better implementation are very welcome :). Cheers, - hugi On 12. ágú. 2015, at 00:49, Aristedes Maniatis a...@maniatis.org wrote: I'm interested in a different part of the problem you are solving. Other than the relationship data discuss here, how are you storing the actual changes in your audit table? Does your problem just require Bob changed record 23 or are you keeping a complete diff of the changes? If the latter, how are you serialising those changes into the audit log? Ari On 12/08/2015 8:13am, Hugi Thordarson wrote: Thanks for the ideas Mike. After a little mulling, I think your idea of dynamically modeling relationships to the audit table at application startup is the best one I’ve heard so far. I’m going to take a peek down that road tomorrow :) Cheers, - hugi On 10. ágú. 2015, at 14:24, Mike Kienenberger mkien...@gmail.com wrote: Not sure if it's clear, but there are two different approaches to your problem described. 1) You can set a foreign relationship to your audit table and let Cayenne assign the key at commit. This is probably the easiest especially if you dynamically create the relationships in java code. Except for the initialization of these relationships at some point (startup or first time you hit the audit code), nothing else has to be done and that's the only cayenne-internals code you have to deal with. 2) You can use something like the example code to grab the primary key values after cayenne generates them but before the actual database query executes. I doubt that prePersist is early enough, but it might be. Otherwise you will have to go looking for a different hook. This requires a lot more knowledge of cayenne internals in my opinion. This is also a much harder problem if you're deferring the key generation to the database rather than using sequences or some similar approach, but Cayenne obviously has a way to handle that now or regular foreign key setup for relationships wouldn't work. Your third idea of using postPersist has its own problems. If you decide to use a postPersist and set this information in a separate commit, then you run the risk of your audit log not being created and losing your audit information. For my environment, that wasn't acceptable. Maybe you could wrap both commits in a single transaction -- I'm not certain, but maybe that could work. Transaction support either wasn't there back when I was doing this or I didn't understand that it could solve my problem back then. On Mon, Aug 10, 2015 at 10:13 AM, Hugi Thordarson h...@karlmenn.is wrote: Thanks Mike! Although the approach I’m working on is a little different (and meant to be reusable with any Cayenne installation so can’t depend on superclass template modifications), it’s very helpful to see code from other Cayenne folks. Cheers, - hugi // Hugi Thordarson // http://www.loftfar.is/ http://www.loftfar.is/ // s. 895-6688 On 10. ágú. 2015, at 12:53, Mike Kienenberger mkien...@gmail.com wrote: I set up auditing using a different approach in one project many years ago back in Cayenne 1.1, and I've continued using it up to this point in 3.x. I generated special setter, addTo, and removeFrom methods as well as a create method which created the logger object at that point. To get the primary
Re: Expressions involving dates can't be converted to EJBQL?
That’s an awesome solution you created there Andrew, works like a charm. Thanks yet again! Happy little counting method - https://gist.github.com/hugith/8a21aa4247385732a4ce Cheers, - hugi On 12. ágú. 2015, at 09:20, Andrew Lindesay a...@lindesay.co.nz wrote: Hi Hugi; You can use EJBQL queries with timestamps, but there is presently no supported _literal_ representation for a timestamp inside the query string so you have to use parameters instead. Here is an example; EJBQLQuery q = new EJBQLQuery(String.format( DELETE FROM %s r WHERE r.createTimestamp :expiryTimestamp, Response.class.getSimpleName())); q.setParameter(expiryTimestamp, new Timestamp(System.currentTimeMillis() - TimeUnit.SECONDS.toMillis(expirySeconds))); getServerRuntime().getContext().performQuery(q); To help with getting Expression objects to over to EJBQL with timestamp literals; Expression#toEJBQL(ListObject parameterAccumulator, String rootId) So you call that and as it creates the EJBQL fragment, it will insert any necessary parameters into the parameterAccumulator for you all set to go into the EJBQLQuery. Hopefully that helps? cheers. [1] https://github.com/aplgithub/haikudepotserver/blob/master/haikudepotserver-webapp/src/main/java/org/haikuos/haikudepotserver/captcha/DatabaseCaptchaRepository.java For example; SELECT f FROM Foo f WHERE f.createTimestamp ? Then you just need to use the On 12/08/15 20:35, Hugi Thordarson wrote: Hi Andrew, thanks for that. I see what the problem is. But if I can’t use EJBQL when generating queries that use dates, can I go down any different paths or do I have to revert to writing plain old SQL? -- Andrew Lindesay
Re: Obtain primary key for DataObject before commitChanges
For INSERTs, are you planning on storing all of the original values in TransactionObjectFieldValue rows? On Wed, Aug 12, 2015 at 4:18 AM, Hugi Thordarson h...@karlmenn.is wrote: For the last few years while using EOF, I’ve been storing changes in the audit log as a map serialized to a String NSPropertyList (the NS* world’s equivalent of JSON). It works fine, but once the log starts to grow, there are performance implications. This time around I’m going for a three table approach that looks something like: - TransactionObject (reference to object) - TransactionObjectLifecycleEvent (user, date and type of action; Insert, Update or Delete) - TransactionObjectFieldValue (name of changed attribute along with new value, serialized to a string) These relate to each other: TransactionObject — TransactionObjectLifecycleEvent — TransactionObjectFieldValue I’m currently planning on ignoring relationships in the object graph and just storing actual values of attributes and foreign keys. We’ll see how that works out. Criticism and ideas for a better implementation are very welcome :). Cheers, - hugi On 12. ágú. 2015, at 00:49, Aristedes Maniatis a...@maniatis.org wrote: I'm interested in a different part of the problem you are solving. Other than the relationship data discuss here, how are you storing the actual changes in your audit table? Does your problem just require Bob changed record 23 or are you keeping a complete diff of the changes? If the latter, how are you serialising those changes into the audit log? Ari On 12/08/2015 8:13am, Hugi Thordarson wrote: Thanks for the ideas Mike. After a little mulling, I think your idea of dynamically modeling relationships to the audit table at application startup is the best one I’ve heard so far. I’m going to take a peek down that road tomorrow :) Cheers, - hugi On 10. ágú. 2015, at 14:24, Mike Kienenberger mkien...@gmail.com wrote: Not sure if it's clear, but there are two different approaches to your problem described. 1) You can set a foreign relationship to your audit table and let Cayenne assign the key at commit. This is probably the easiest especially if you dynamically create the relationships in java code. Except for the initialization of these relationships at some point (startup or first time you hit the audit code), nothing else has to be done and that's the only cayenne-internals code you have to deal with. 2) You can use something like the example code to grab the primary key values after cayenne generates them but before the actual database query executes. I doubt that prePersist is early enough, but it might be. Otherwise you will have to go looking for a different hook. This requires a lot more knowledge of cayenne internals in my opinion. This is also a much harder problem if you're deferring the key generation to the database rather than using sequences or some similar approach, but Cayenne obviously has a way to handle that now or regular foreign key setup for relationships wouldn't work. Your third idea of using postPersist has its own problems. If you decide to use a postPersist and set this information in a separate commit, then you run the risk of your audit log not being created and losing your audit information. For my environment, that wasn't acceptable. Maybe you could wrap both commits in a single transaction -- I'm not certain, but maybe that could work. Transaction support either wasn't there back when I was doing this or I didn't understand that it could solve my problem back then. On Mon, Aug 10, 2015 at 10:13 AM, Hugi Thordarson h...@karlmenn.is wrote: Thanks Mike! Although the approach I’m working on is a little different (and meant to be reusable with any Cayenne installation so can’t depend on superclass template modifications), it’s very helpful to see code from other Cayenne folks. Cheers, - hugi // Hugi Thordarson // http://www.loftfar.is/ http://www.loftfar.is/ // s. 895-6688 On 10. ágú. 2015, at 12:53, Mike Kienenberger mkien...@gmail.com wrote: I set up auditing using a different approach in one project many years ago back in Cayenne 1.1, and I've continued using it up to this point in 3.x. I generated special setter, addTo, and removeFrom methods as well as a create method which created the logger object at that point. To get the primary key, I set up one-way object relationships between the primary key of the logged object and the foreign key storage field in the audit log, one for each object entity. When the commit happened, the relationships automatically populated the audit log fields. You probably can do the same thing. There's probably a better way to set up the object relationships than manually defining them in your model these days. In a different Cayenne 1.2 project, I used something similar to your
