cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Gentry <mgen...@masslight.net>
Subject Re: Obtain primary key for DataObject before commitChanges
Date Wed, 12 Aug 2015 12:15:03 GMT
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 <hugi@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 <ari@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 <mkienenb@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 <hugi@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 <mkienenb@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 <
> ari@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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message