Hi,

Enviroment: * Adapter templates v2.6 * SQL-server 2008 64bit Enterprise * Client <- WCF -> Application layer <- ADO.Net/LLBLGenPro -> Database

If a client changes some entity, say Person, what is the best technique to audit:

First of all, I don't want to audit trough triggers - I don't like SQL servers trigger model, you can't pass easily context to triggers, you must know who was responsible for the audit (the client credentials), and I hate those stupid tricks as adding UserModified on each table or using a temp. user table to store the current user. And I read about using the applicationName in the connection string to pass this info, this technique is even worser, a connection pool is created based on a connection-string, so if you change the connection string for each client then again what is the purpose of having connection pooling!

So, I wanted to implemented as much generic as possible with the help of the LLBLGenPro framework. Suggestions?

I did it already for a pure client / server application,

    [DependencyInjectionInfo(
        typeof(CommonEntityBase), 
        "AuditorToUse")]
    [Serializable]
    public class Audit : AuditorBase
    {
        private static readonly string UnkownUser = "(Unknown)";
        private static string m_userName = null;
        private List<AuditEntity> m_auditInfoEntities;

        public Audit()
        {
            IIdentity identity = WindowsIdentity.GetCurrent();
            if (identity != null)
            {
                m_userName = string.IsNullOrEmpty(identity.Name) ? UnkownUser : identity.Name;
            }
            m_auditInfoEntities = new List<AuditEntity>();
        }


        private void AddAuditEntryToList(AuditEntity auditInfo)
        {
            m_auditInfoEntities.Add(auditInfo);
        }

        public override System.Collections.IList GetAuditEntitiesToSave()
        {
            return m_auditInfoEntities;
        }

        public override void TransactionCommitted()
        {
            m_auditInfoEntities.Clear();
        }

        public override void AuditEntityFieldSet(IEntityCore entity, int fieldIndex, object originalValue)
        {
            if (entity.IsNew || entity is AuditEntity)
            {
                return;
            }

            EntityFieldCore field = entity.Fields[fieldIndex] as EntityFieldCore;
            if (field != null && entity.PrimaryKeyFields.Count == 1)
            {
                AuditEntity auditInfo = new AuditEntity();
                EntityFieldCore fieldPK = entity.PrimaryKeyFields[0] as EntityFieldCore;

                if (field.ContainingObjectName.EndsWith("Entity"))
                {
                    auditInfo.TableName = field.ContainingObjectName.Substring(0, field.ContainingObjectName.Length - 6);
                }
                else
                {
                    auditInfo.TableName = field.ContainingObjectName;
                }

                auditInfo.ColumnName = field.Name;
                auditInfo.ValuePK = (fieldPK.CurrentValue as long?).GetValueOrDefault();
                auditInfo.NewValue = ""+field.CurrentValue;
                auditInfo.OldValue = ""+field.DbValue;
                auditInfo.UserCreated = m_userName;
                auditInfo.DateCreated = DateTime.Now;
                AddAuditEntryToList(auditInfo);
            }
        }
    }

Questions about using this code again voor n-tiers? So, if this codes runs on the client layer, there will be AuditEntities created in memory, but because they are sent back to the backend, they are never committed on the client side, this means also that the audit collection will grow and grow.

• If the entity is sent back to the backend, are the audit records automatically included and is a saveEntity call enough then?

Thanx in advance for the answers, Danny

Setting an Entity's Auditor, whether by DI, or manually by setting the AuditorToUse property, associates the Auditor to the Entity. And then when you send the Entity over the wire, the auditor is sent inside the entity. Since it's a property of the entity.

Then upon saving the frameowrk checks if an Auditor is present, and if so, it calls its GetAuditEntitiesToSave() method, to retrieve the audit records to be saved. And thus they are saved too.

Walaa,

And what is happening with the auditor records on the client side?

Scenario: 1. Fetch collection from back-end 2. Change an attribute in some entity in the collection 3. Send collection back to back-end to process 4. Change again an attribute in some entity in the collection 5. Send collection back to back-end

After step 3, are all audit records removed or must I do this manually?

Danny

You should do this manually, unless you have returned back the saved entity. And this case you should have implemented TransactionCommitted() method in the auditor. Which you should use to clear any audit data in this auditor as it's called when all audit information is persisted successfully.

Walaa,

Are the list of audit-entries through a public method/property available in the client?

Thanx in advance Danny

Danny

Sorry, I'm not sure I understand your question. Could you elablorate a bit ?

Thanks

Matt

dvdwouwe wrote:

Walaa,

Are the list of audit-entries through a public method/property available in the client?

Thanx in advance Danny

The auditor tracks changes in the entities it is injected in, so if you want the entities tracked, ask the auditor. Auditing is implemented as a 'recorder' so it records actions on the entity it is part of. Built-in are mechanisms to move the recorded data across a wire, so you can audit actions on the client, serialize it over the wire and get the audit data back on the server.

But I'm not sure what you want to achieve. The point of using an auditor is that you don't want to mess with it manually, otherwise what's the point?

Otis,

I only want to track changes into Audit records on the client and sent it back to the back-end services. I want this so generic as possible.

I attached a solution. I don't have a clue what I'm doing wrong here.

To use the solution, create a small database on sql-server using the script "Tables.sql". Run the WPFHost, run the ConsoleAuditTest program in debug (that triggers the asserts).

Thx in advance for your answer, Danny Peopleware NV

    protected override void InitClassEmpty(IValidator validator, IEntityFields2 fields)
    {
        base.InitClassEmpty(validator, fields);
        ConcurrencyPredicateFactoryToUse = GenericConcurrency.Instance;
        AuditorToUse = new GenericAudit();
    }

You shouldn't set the Auditor in the InitClassEmpty() override, but rather you should have used CreateAuditor()

The following is quoted from the manual:

Setting an entity's Auditor If you've decided to use Auditor classes to place your auditing logic in, you'll be confronted with the question: how to set the right Auditor in every entity object? You've three options:

1- Setting the AuditorToUse property of an entity object manually. This is straight forward, but error prone: if you forget to set an auditor, auditing isn't performed. Also, entities fetched in bulk into a collection are created using the factories so you have to alter these as well. You could opt for overriding OnInitializing in an entity to add the creation of the Auditor class. **2- By overriding the Entity method CreateAuditor. This is a protected virtual (Protected Overridable) method which by default returns null / Nothing. You can override this method in a partial class or user code region of the Entity class to create the Auditor to use for the entity. The LLBLGen Pro runtime framework will take care of calling the method. One way to create an override for most entities is by using a template. Please see the LLBLGen Pro SDK documentation for details about how to write templates to generate additional code into entities and in separate files. Also please see Adding Adding your own code to the generated classes for details. ** 3- By using Dependency Injection. Using the Dependency Injection mechanism build into LLBLGen Pro, the defined Auditors are injected into the entity objects for you. This option is the least amount of work.

Walaa,

I did follow your guidance:

replaced:

        protected override void InitClassEmpty(IValidator validator, IEntityFields2 fields)
        {
            base.InitClassEmpty(validator, fields);
            ConcurrencyPredicateFactoryToUse = GenericConcurrency.Instance;
            AuditorToUse = new GenericAudit();
        }

with:

        protected override IAuditor CreateAuditor()
        {
            return new GenericAudit();
        }

        protected override IConcurrencyPredicateFactory CreateConcurrencyPredicateFactory()
        {
            return GenericConcurrency.Instance;
        }

But still this Debug.Assert failed in the project I send

                    Debug.Assert(CheckAuditDBStore(new List<AuditDB>() { a }), "Auditing of action: Append 2 to Person.FirstName failed.");

This means that the audit record isn't created on our back-end. So the changes didn't solve my problem. The audit-records are still not serialized to the back-end ! I can see them in VS (using the debugger) on the client side, but on the back-end they aren't received.

Danny

You are right, I guess I have missed this already. You have to implement the serialization/deserialization yourself.

ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13609

Walaa,

I can't get it to work, I followed the guidelines but with no success.

I added following code to the GenericAudit class

        public override bool HasDataToXmlSerialize
        {
            get
            {
                return m_AuditInfoEntities.Count > 0;
            }
        }

        public override void ReadXml(System.Xml.XmlReader reader)
        {
            string startElementName = reader.LocalName;
            while (reader.Read() && !((reader.LocalName == startElementName) && (reader.NodeType == XmlNodeType.EndElement)))
            {
                AuditEntity toDeserialize = new AuditEntity();
                toDeserialize.ReadXml(reader);
                m_AuditInfoEntities.Add(toDeserialize);
            }
        }

        public override void WriteXml(System.Xml.XmlWriter writer, XmlFormatAspect aspects, Dictionary<Guid, IEntity2> processedObjectIDs)
        {
            writer.WriteStartElement("Audits"); // <Entities>
            foreach (AuditEntity auditDB in m_AuditInfoEntities)
            {
                auditDB.WriteXml(writer);
            }
            writer.WriteEndElement();
        }

I throws an exception on the 5th line of ReadXml

System.NullReferenceException was unhandled by user code
  Message="Object reference not set to an instance of an object."
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.Xml2Entity(XmlNode node, Dictionary`2 processedObjectIDs, List`1 nodeEntityReferences)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.ReadXml(XmlNode node, XmlFormatAspect format)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.ReadXml(String xmlData)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.ReadXml(XmlNode node)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.ReadXml(XmlReader reader, XmlFormatAspect format)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.ReadXml(XmlReader reader)
       at PensioB.People.Semantics_I.Domain.GenericAudit.ReadXml(XmlReader reader) in C:\Development\PensioB\Test\PensioB.People\Semantics_I\Domain\GenericAudit.cs:line 92
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.Xml2Entity(XmlReader reader, Dictionary`2 processedObjectIDs, List`1 nodeEntityReferences)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.ReadXml(XmlReader reader, XmlFormatAspect format)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase2.System.Xml.Serialization.IXmlSerializable.ReadXml(XmlReader reader)
       at System.Runtime.Serialization.XmlObjectSerializerReadContext.ReadIXmlSerializable(XmlSerializableReader xmlSerializableReader, XmlReaderDelegator xmlReader, XmlDataContract xmlDataContract, Boolean isMemberType)
       at System.Runtime.Serialization.XmlObjectSerializerReadContext.ReadIXmlSerializable(XmlReaderDelegator xmlReader, XmlDataContract xmlDataContract, Boolean isMemberType)
       at System.Runtime.Serialization.XmlDataContract.ReadXmlValue(XmlReaderDelegator xmlReader, XmlObjectSerializerReadContext context)
       at System.Runtime.Serialization.XmlObjectSerializerReadContext.ReadDataContractValue(DataContract dataContract, XmlReaderDelegator reader)
       at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator reader, String name, String ns, DataContract& dataContract)
       at System.Runtime.Serialization.XmlObjectSerializerReadContext.InternalDeserialize(XmlReaderDelegator xmlReader, Type declaredType, DataContract dataContract, String name, String ns)
       at System.Runtime.Serialization.DataContractSerializer.InternalReadObject(XmlReaderDelegator xmlReader, Boolean verifyObjectName)
       at System.Runtime.Serialization.XmlObjectSerializer.ReadObjectHandleExceptions(XmlReaderDelegator reader, Boolean verifyObjectName)
       at System.Runtime.Serialization.DataContractSerializer.ReadObject(XmlDictionaryReader reader, Boolean verifyObjectName)
       at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameterPart(XmlDictionaryReader reader, PartInfo part, Boolean isRequest)
       at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameter(XmlDictionaryReader reader, PartInfo part, Boolean isRequest)
       at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameters(XmlDictionaryReader reader, PartInfo[] parts, Object[] parameters, Boolean isRequest)
       at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeBody(XmlDictionaryReader reader, MessageVersion version, String action, MessageDescription messageDescription, Object[] parameters, Boolean isRequest)
       at System.ServiceModel.Dispatcher.OperationFormatter.DeserializeBodyContents(Message message, Object[] parameters, Boolean isRequest)
       at System.ServiceModel.Dispatcher.OperationFormatter.DeserializeRequest(Message message, Object[] parameters)
       at System.ServiceModel.Dispatcher.DispatchOperationRuntime.DeserializeInputs(MessageRpc& rpc)
  InnerException: 

I attached the test solution

Thx for answers Danny

I think you should check for the node type, please try the following code.

        public override void ReadXml(System.Xml.XmlReader reader)
        {
            string startElementName = reader.LocalName;
            while (reader.Read() && !((reader.LocalName == startElementName) && (reader.NodeType == XmlNodeType.EndElement)))
            {
                if(reader.NodeType == XmlNodeType.Element)
                {
                   AuditEntity toDeserialize = new AuditEntity();
                   toDeserialize.ReadXml(reader);
                   m_AuditInfoEntities.Add(toDeserialize);
                }
            }
        }

Walaa,

Same exception with this code?

Did you test the code with the solution I attached?

Danny

When the entity is serialized to XML, it checks whether it has an auditor. if so, it asks if the auditor has xml to deserialize by reading HasDataToXmlSerialize. If that returns to true, it will write the start element <Auditor> and then call the auditor's WriteXml method. In that method, you have to start writing elements yourself. You write the element <Audits> and then start writing entity xml again.

When the auditor is done, the entity calling the auditor's WriteXml emits the end element for <Auditor> and continues with the rest of the data.

The xml is then transfered to the other side and of the wire and ReadXml is reading it. When ReadXml on an entity (in the method Xml2Entity() encounters the element <Auditor> it checks whether the entity instance has an Auditor set, and if so, it will call the ReadXml method by passing the reader. This thus means that the reader is still on the <Auditor> element! So the startElementName is 'Auditor' in ReadXml of your GeneralAudit class.

The while loop you have indeed reads along while it doesn't run into the matching end element, but the start element has the wrong value, it should have the value 'Audits', so you first have to read away 'Auditor', then set the start element name, then go into the while loop. You can see that when you place a breakpoint in the ReadXml method. So inside your while loop the reader is at the 'Audits' element, which causes a problem as the while loop never stops properly. So as you introduce both a new start element (Audits) and a new End element (/Audits) you have to read them first and after your readxml routine. This has to be done because the ReadXml method of the entity stops at the end element of the entity. If you wouldn't introduce the <Audits> element, the reader would be located at the </Auditor> element when ReadXml stops which is read away in the next iteration of the main loop.

So do in your ReadXml method: public override void ReadXml(XmlReader reader) { reader.Read(); // read away <Audits> string startElementName = reader.LocalName; while(reader.Read() && !((reader.LocalName == startElementName) && (reader.NodeType == XmlNodeType.EndElement))) { AuditEntity toDeserialize = new AuditEntity(); toDeserialize.ReadXml(reader); _m_AuditInfoEntities.Add(toDeserialize); } reader.Read(); // read away </Audits> }

(I modified my testmethod to mimic your code base).

You can verify this in the debugger in this method.

Otis,

Thx it worked !!

Following query can be useful for your product, it determine the relation between a table and a generator (or sequence) in firebird. It can be that you got multiple results (more then one sequence is being used in the before insert triggers) for one table, for example a version_id field that is being used that is also being populated through triggers.

select t.rdb$relation_name as table_name,
       d.rdb$depended_on_name
  from rdb$dependencies d
       join rdb$triggers t on d.rdb$dependent_name = t.rdb$trigger_name
 where d.rdb$dependent_type = 2 /* GENERATOR, see RDB$TYPE, 'RDB$OBJECT_TYPE'    */
   and d.rdb$depended_on_type = 14 /* TRIGGER, see RDB$TYPE, 'RDB$OBJECT_TYPE'     */
   and t.rdb$trigger_type = 1     /* BEFORE INSERT, see RDB$TYPE, 'RDB$TRIGGER_TYPE'*/

This query will work for 1.5.x, 2.0.x, 2.1.x and upcoming 2.5.x

Result for the employee database:

TABLE_NAME RDB$DEPENDED_ON_NAME EMPLOYEE EMP_NO_GEN CUSTOMER CUST_NO_GEN

Danny

About the sequence/generator <-> table relationship: llblgen pro v3 works with two different kind of sequences: system sequences (like sqlserver has with identity fields) and schema sequences, like the ones in firebird, oracle, postgresql etc.

It doesn't determine the relationship between sequence and table, as in almost all cases for schema sequences this relationship isn't stored in the db schema so the designer doesn't support it internally in v3.0. In v2 and earlier, a sequence was a sequence, so it always stored the sequence found with the table field, if the sequence was determinable (which was only done for system sequences in v2.x), which was then shown as the sequence to use on the entity field.

This was of course a bit dirty, as it should be stored on the entity field, or better: the mapping. In v3, we therefore decoupled that, and stored the sequence on the mapping. When the target field has its identity flag set, the preferred system sequence is assigned (which one is preferred is determined by the driver). If that's not the case, the user has to manually assign a sequence. So we don't have the information which sequence to use in the table field anymore, it's either identity (so use the preferred system sequence) or manually set the sequence, like in v2.6.

So although the info could be helpful, we don't have support for it internally and won't make these major changes to the object model at this point in the release cycle anymore. I'll store your query for a future 3.x version of the designer to add this feature. (which is firebird specific, as no other db stores schema sequence<-> table relationships, hence we don't have support for it internally).

Thanks for the query