cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Aristedes Maniatis <...@maniatis.org>
Subject Re: Obtain primary key for DataObject before commitChanges
Date Wed, 12 Aug 2015 10:05:15 GMT
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 <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
> 

-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

Mime
View raw message