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 > >>>>> 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 outside of 1.2 since this project was never > >>>>> upgraded, but here it is in case you want to try this approach and > >>>>> adapt it to a more recent version of Cayenne. > >>>>> setForeignKeyRepresentation(Map pkAttributes, DbEntity dbEntity, Map > >>>>> auditRecordMap) and the code that calls it after setting pkAttributes > >>>>> would likely be what you want to reference for fetching the primary > >>>>> key dynamically. > >>>>> > >>>>> > >>>>> import java.io.Serializable; > >>>>> import java.util.ArrayList; > >>>>> import java.util.Collections; > >>>>> import java.util.Date; > >>>>> import java.util.HashMap; > >>>>> import java.util.Iterator; > >>>>> import java.util.List; > >>>>> import java.util.Map; > >>>>> > >>>>> import org.objectstyle.cayenne.CayenneRuntimeException; > >>>>> import org.objectstyle.cayenne.ObjectId; > >>>>> import org.objectstyle.cayenne.access.DataContext; > >>>>> import org.objectstyle.cayenne.access.DataDomainFlushObserver; > >>>>> import org.objectstyle.cayenne.access.DataNode; > >>>>> import org.objectstyle.cayenne.access.DefaultDataContextDelegate; > >>>>> import org.objectstyle.cayenne.dba.PkGenerator; > >>>>> import org.objectstyle.cayenne.map.DataMap; > >>>>> import org.objectstyle.cayenne.map.DbAttribute; > >>>>> import org.objectstyle.cayenne.map.DbEntity; > >>>>> import org.objectstyle.cayenne.map.ObjEntity; > >>>>> import org.objectstyle.cayenne.query.BatchQuery; > >>>>> import org.objectstyle.cayenne.query.DeleteBatchQuery; > >>>>> import org.objectstyle.cayenne.query.InsertBatchQuery; > >>>>> import org.objectstyle.cayenne.query.UpdateBatchQuery; > >>>>> > >>>>> public class AuditLoggingDataContextDelegate extends > >>>>> DefaultDataContextDelegate implements Serializable > >>>>> { > >>>>> public static final String MOD_TYPE_INSERT = "I"; > >>>>> public static final String MOD_TYPE_UPDATE = "U"; > >>>>> public static final String MOD_TYPE_DELETE = "D"; > >>>>> > >>>>> public void finishedRunQueries(DataContext dataContext, List > queryList) { > >>>>> super.finishedRunQueries(dataContext, queryList); > >>>>> > >>>>> Date modificationDate = new Date(); > >>>>> > >>>>> List auditRecordMapList = new ArrayList(); > >>>>> > >>>>> Iterator queryIterator = queryList.iterator(); > >>>>> while (queryIterator.hasNext()) { > >>>>> BatchQuery batchQuery = (BatchQuery) queryIterator.next(); > >>>>> > >>>>> if (batchQuery instanceof InsertBatchQuery) > >>>>> { > >>>>> InsertBatchQuery insertBatchQuery = > >>>>> (InsertBatchQuery)batchQuery; > >>>>> insertBatchQuery.reset(); > >>>>> > >>>>> List dbAttributes = insertBatchQuery.getDbAttributes(); > >>>>> while(insertBatchQuery.next()) { > >>>>> for(int i = 0; i < dbAttributes.size(); i++) { > >>>>> Map auditRecordMap = new HashMap(); > >>>>> > >>>>> DbAttribute dbAttribute = (DbAttribute) > >>>>> dbAttributes.get(i); > >>>>> Object value = insertBatchQuery.getValue(i); > >>>>> > >>>>> DbEntity dbEntity = > (DbEntity)dbAttribute.getEntity(); > >>>>> auditRecordMap.put("MOD_TIME", modificationDate); > >>>>> auditRecordMap.put("SCHEMA_NAME", > dbEntity.getSchema()); > >>>>> auditRecordMap.put("TBL_NAME", > dbEntity.getName()); > >>>>> auditRecordMap.put("COL_NAME", > dbAttribute.getName()); > >>>>> auditRecordMap.put("MOD_TYPE", MOD_TYPE_INSERT); > >>>>> if (null != value) > >>>>> { > >>>>> auditRecordMap.put("NEW_VALUE", > value.toString()); > >>>>> } > >>>>> > >>>>> Map pkAttributes; > >>>>> ObjectId objectId = > insertBatchQuery.getObjectId(); > >>>>> if (null != objectId) > >>>>> { > >>>>> pkAttributes = objectId.getIdSnapshot(); > >>>>> } > >>>>> else > >>>>> { > >>>>> pkAttributes = > >>>>> insertBatchQuery.getCurrentObjectSnapshot(); > >>>>> } > >>>>> setForeignKeyRepresentation(pkAttributes, > >>>>> dbEntity, auditRecordMap); > >>>>> > >>>>> auditRecordMapList.add(auditRecordMap); > >>>>> } > >>>>> } > >>>>> } > >>>>> else if (batchQuery instanceof DeleteBatchQuery) > >>>>> { > >>>>> DeleteBatchQuery deleteBatchQuery = > >>>>> (DeleteBatchQuery)batchQuery; > >>>>> deleteBatchQuery.reset(); > >>>>> > >>>>> List dbAttributes = deleteBatchQuery.getDbAttributes(); > >>>>> while(deleteBatchQuery.next()) { > >>>>> for(int i = 0; i < dbAttributes.size(); i++) { > >>>>> Map auditRecordMap = new HashMap(); > >>>>> > >>>>> DbAttribute dbAttribute = (DbAttribute) > >>>>> dbAttributes.get(i); > >>>>> > >>>>> DbEntity dbEntity = > (DbEntity)dbAttribute.getEntity(); > >>>>> auditRecordMap.put("MOD_TIME", > modificationDate); > >>>>> auditRecordMap.put("SCHEMA_NAME", > dbEntity.getSchema()); > >>>>> auditRecordMap.put("TBL_NAME", > dbEntity.getName()); > >>>>> auditRecordMap.put("COL_NAME", > dbAttribute.getName()); > >>>>> auditRecordMap.put("MOD_TYPE", MOD_TYPE_DELETE); > >>>>> > >>>>> > >>>>> setForeignKeyRepresentation(deleteBatchQuery.getCurrentQualifier(), > >>>>> dbEntity, auditRecordMap); > >>>>> > >>>>> auditRecordMapList.add(auditRecordMap); > >>>>> } > >>>>> } > >>>>> } > >>>>> else if (batchQuery instanceof UpdateBatchQuery) > >>>>> { > >>>>> UpdateBatchQuery updateBatchQuery = > >>>>> (UpdateBatchQuery)batchQuery; > >>>>> updateBatchQuery.reset(); > >>>>> > >>>>> List dbAttributeList = > updateBatchQuery.getUpdatedAttributes(); > >>>>> while(updateBatchQuery.next()) { > >>>>> for(int i = 0; i < dbAttributeList.size(); i++) { > >>>>> Map auditRecordMap = new HashMap(); > >>>>> > >>>>> DbAttribute dbAttribute = (DbAttribute) > >>>>> dbAttributeList.get(i); > >>>>> Object newValue = updateBatchQuery.getValue(i); > >>>>> > >>>>> Object oldValue = > updateBatchQuery.getOldValue(i); > >>>>> > >>>>> DbEntity dbEntity = > (DbEntity)dbAttribute.getEntity(); > >>>>> auditRecordMap.put("MOD_TIME", modificationDate); > >>>>> auditRecordMap.put("SCHEMA_NAME", > dbEntity.getSchema()); > >>>>> auditRecordMap.put("TBL_NAME", > dbEntity.getName()); > >>>>> auditRecordMap.put("COL_NAME", > dbAttribute.getName()); > >>>>> auditRecordMap.put("MOD_TYPE", MOD_TYPE_UPDATE); > >>>>> if (null != oldValue) > >>>>> { > >>>>> auditRecordMap.put("OLD_VALUE", > oldValue.toString()); > >>>>> } > >>>>> if (null != newValue) > >>>>> { > >>>>> auditRecordMap.put("NEW_VALUE", > newValue.toString()); > >>>>> } > >>>>> > >>>>> > >>>>> setForeignKeyRepresentation(batchQuery.getObjectId().getIdSnapshot(), > >>>>> dbEntity, auditRecordMap); > >>>>> > >>>>> auditRecordMapList.add(auditRecordMap); > >>>>> } > >>>>> } > >>>>> } > >>>>> } > >>>>> > >>>>> processAuditRecordMapList(dataContext, auditRecordMapList); > >>>>> } > >>>>> > >>>>> protected void processAuditRecordMapList(DataContext dataContext, > >>>>> List auditRecordMapList) > >>>>> { > >>>>> SecIndividual secIndividual = > >>>>> (SecIndividual)dataContext.getUserProperty("secIndividual"); > >>>>> SecSystem secSystem = > >>>>> (SecSystem)dataContext.getUserProperty("secSystem"); > >>>>> > >>>>> // Sort into ChangeLog records > >>>>> Map changeLogMap = new HashMap(); > >>>>> Iterator auditRecordMapIterator = auditRecordMapList.iterator(); > >>>>> while (auditRecordMapIterator.hasNext()) { > >>>>> Map auditRecordMap = (Map) auditRecordMapIterator.next(); > >>>>> > >>>>> auditRecordMap.put("SYSTEM_ID", secSystem.getPrimaryKey()); > >>>>> auditRecordMap.put("REAL_USER_ID", > secIndividual.getPrimaryKey()); > >>>>> auditRecordMap.put("EFFECTIVE_USER_ID", > >>>>> secIndividual.getPrimaryKey()); > >>>>> > >>>>> String tableName = (String)auditRecordMap.get("TBL_NAME"); > >>>>> DbEntity dbEntity = > >>>>> dataContext.getEntityResolver().getDbEntity(tableName); > >>>>> DataMap dataMap = dbEntity.getDataMap(); > >>>>> String changeLogObjEntityName = "ChangeLog" + > dataMap.getName(); > >>>>> ObjEntity changeLogObjEntity = > >>>>> dataMap.getObjEntity(changeLogObjEntityName); > >>>>> DbEntity changeLogDbEntity = > changeLogObjEntity.getDbEntity(); > >>>>> > >>>>> List changeLogList = > (List)changeLogMap.get(changeLogDbEntity); > >>>>> if (null == changeLogList) > >>>>> { > >>>>> changeLogList = new ArrayList(); > >>>>> changeLogMap.put(changeLogDbEntity, changeLogList); > >>>>> } > >>>>> > >>>>> changeLogList.add(auditRecordMap); > >>>>> } > >>>>> > >>>>> Iterator changeLogMapEntryIterator = > changeLogMap.entrySet().iterator(); > >>>>> while (changeLogMapEntryIterator.hasNext()) { > >>>>> Map.Entry changeLogEntry = (Map.Entry) > >>>>> changeLogMapEntryIterator.next(); > >>>>> DbEntity changeLogDbEntity = > (DbEntity)changeLogEntry.getKey(); > >>>>> List changeLogList = (List)changeLogEntry.getValue(); > >>>>> > >>>>> InsertBatchQuery batch = new > >>>>> InsertBatchQuery(changeLogDbEntity, changeLogList.size()); > >>>>> > >>>>> DataNode node = > >>>>> > dataContext.getParentDataDomain().lookupDataNode(changeLogDbEntity.getDataMap()); > >>>>> PkGenerator pkGenerator = > node.getAdapter().getPkGenerator(); > >>>>> List dbAttributeList = changeLogDbEntity.getPrimaryKey(); > >>>>> if (1 != dbAttributeList.size()) > >>>>> { > >>>>> throw new CayenneRuntimeException("Compound primary > key"); > >>>>> } > >>>>> DbAttribute keyAttribute = > (DbAttribute)dbAttributeList.get(0); > >>>>> String key = keyAttribute.getName(); > >>>>> Iterator changeLogIterator = changeLogList.iterator(); > >>>>> while (changeLogIterator.hasNext()) { > >>>>> Map auditRecordMap = (Map) changeLogIterator.next(); > >>>>> ObjectId id = > >>>>> createObjectIdForAuditLog(changeLogDbEntity, node, pkGenerator, key); > >>>>> auditRecordMap.put(key, id.getIdSnapshot().get(key)); > >>>>> batch.add(auditRecordMap, id); > >>>>> } > >>>>> > >>>>> DataDomainFlushObserver observer = new > DataDomainFlushObserver(); > >>>>> node.performQueries(Collections.singletonList(batch), > observer); > >>>>> } > >>>>> > >>>>> } > >>>>> > >>>>> private ObjectId createObjectIdForAuditLog(DbEntity > >>>>> changeLogDbEntity, DataNode node, PkGenerator pkGenerator, String > key) > >>>>> throws CayenneRuntimeException { > >>>>> Object pkValue; > >>>>> try { > >>>>> pkValue = pkGenerator.generatePkForDbEntity(node, > >>>>> changeLogDbEntity); > >>>>> } catch (Exception e) { > >>>>> throw new CayenneRuntimeException("Error generating audit > >>>>> log primary keys", e); > >>>>> } > >>>>> ObjectId id = new ObjectId(changeLogDbEntity.getName(), key, > pkValue); > >>>>> return id; > >>>>> } > >>>>> > >>>>> private void setForeignKeyRepresentation(Map pkAttributes, > >>>>> DbEntity dbEntity, Map auditRecordMap) { > >>>>> Integer primaryKeyOfRecord = null; > >>>>> String primaryKeysString = null; > >>>>> > >>>>> // References to the record that was changed (FK_C is for > >>>>> compound keys, FK is for a single integer key). > >>>>> > >>>>> if (1 == pkAttributes.size()) > >>>>> { > >>>>> Iterator pkIterator = pkAttributes.keySet().iterator(); > >>>>> String primaryKeyName = (String) pkIterator.next(); > >>>>> Object pkObject = pkAttributes.get(primaryKeyName); > >>>>> if (pkObject instanceof Integer) > >>>>> { > >>>>> primaryKeyOfRecord = (Integer)pkObject; > >>>>> } > >>>>> } > >>>>> > >>>>> if (null == primaryKeyOfRecord) > >>>>> { > >>>>> Iterator pkIterator = pkAttributes.keySet().iterator(); > >>>>> while (pkIterator.hasNext()) > >>>>> { > >>>>> String primaryKeyName = (String) pkIterator.next(); > >>>>> Object primaryKeyValue = > pkAttributes.get(primaryKeyName); > >>>>> > >>>>> if (null == primaryKeysString) > >>>>> { > >>>>> primaryKeysString = primaryKeyName + "=" + > primaryKeyValue; > >>>>> } > >>>>> else > >>>>> { > >>>>> primaryKeysString = primaryKeysString + "," + > >>>>> primaryKeyName + "=" + primaryKeyValue; > >>>>> } > >>>>> } > >>>>> } > >>>>> > >>>>> if (null != primaryKeyOfRecord) > >>>>> { > >>>>> auditRecordMap.put("FOREIGN_KEY", primaryKeyOfRecord); > >>>>> } > >>>>> if (null != primaryKeysString) > >>>>> { > >>>>> auditRecordMap.put("FKEY_CONDITION", primaryKeysString); > >>>>> } > >>>>> } > >>>>> } > >>>>> > >>>>> > >>>>> > >>>>> On Mon, Aug 10, 2015 at 7:27 AM, Aristedes Maniatis < > a...@maniatis.org> wrote: > >>>>>> On 10/08/2015 8:31pm, Hugi Thordarson wrote: > >>>>>>> Is it possible for me to obtain the primary key for a Cayenne > DataObject before committing changes? I’m writing an audit log and I need > the key for the object during PrePersist (where I’m constructing the log > object). > >>>>>> > >>>>>> How will it have a primary key before the record is written to the > database? Or do you want to hang onto the temporary ObjectId and then > replace it with the real PK after the commit? > >>>>>> > >>>>>> Ari > >>>>>> > >>>>>> > >>>>>> -- > >>>>>> --------------------------> > >>>>>> Aristedes Maniatis > >>>>>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A > >>>> > >> > >> > > > > -- > > --------------------------> > > Aristedes Maniatis > > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A > >