Feature Suggestion - Audit Fields

In my project, we have audit fields in most of the tables, to include CreateDate, CreateUser, UpdateDate, UpdateUser.  With my VB6 version, I had the data layer automatically recognize them and populate them at the appropriate time when inserting/updating.  How about a similar feature in Paladin?  You could create a new attribute:

Public Enum AuditType
    None = 0
    CreateDate
    CreateUser
    UpdateDate
    UpdateUser
End Enum

<AttributeUsage(AttributeTargets.Field, AllowMultiple:=False)> _
Public NotInheritable Class AuditFieldAttribute
    Inherits Attribute

    Private _Type As AuditType
    Public ReadOnly Property Type() As AuditType
        Get            
	   Return _Type
        End Get
    End Property

    Public Sub New(ByVal Type As AuditType)
        _Type = Type
    End Sub
End Class

Logic for setting:
Action = Create :  All fields set
Action = Update :  Update fields set


This attribute could be applied to the corresponding fields.  In the BEWizard, you could have options for the naming convention so it would know how to apply the attribute.  Finally, you would need a way around the auditing, so an Auditing Boolean property on the Entity base (defaults to True) would turn the feature on/off.  I use this for our users table, since I have a LastLogin field, I want to be able to just update that field w/o triggering the Update audit fields to be changed.

What do you think?  (oh, and excuse my vb... i know you're a c#'er)

-Mike

Hi Mike!

I think I have similar audit requirements like you too - I have CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, IsDeleted, DeletedBy and DeletedDate fields in my application design.

I like your suggestion but one thing though, the dates can be stamped in but how are we going to feed in the value for CreatedBy, ModifiedBy and DeletedBy since they can be user names or user Ids?

I think this looks very much like a 'business requirement' rather than a 'data requirement'. Perhaps it can be coded as a type of BusinessComponent (BC) i.e. AuditedBusinessComponent or maybe, just a feature in the BC i.e. bc.Audit = true;

What do you guys think? Separate component or integrated to BusinessComponent?

Why not use the built-in .NET authentication?  I use a business component to authenticate users, and after I have authenticated the credentials with the DB, I create a generic principal object (from a previous project):

' return an IPrincipal to the caller.  A windows app would then 
' assign it to System.Threading.Thread.CurrentPrincipal and a web
' app would assign it to Context.User
Public Function Authenticate(Credential as LoginCredential) As IPrincipal

    ' snip -- logic to authenticate user & load the user's roles

    If bSuccess Then
        ' Login was successful & roles were loaded for the user
        Dim oIdentity As New GenericIdentity(Credential.Username)
        Dim oPrinciple As New GenericPrincipal(oIdentity, rolesList)

        ' let the data provider know who's logged in
        DataProvider.Instance.UserContext = oIdentity

        Return oPrincipal
    Else
        Return Nothing
    End If
End Function

Now you have a username to insert.  As to those who want to store UserIDs (int) rather than UserNames (varchar), I couldn't tell you.  I'm always torn between the two.  The DB in me wants everything to be normalized while the developer in me wants to easily have access to and display of the values w/o needless joins.  The developer always wins

For the BEWizard, you can enforce this by checking the type of the field.  At runtime you can throw an authentication exception if the UserContext (or similar property) is not set when attempting an operation on an audited field.

Just my 2 cents...
Mike

All these good suggestions will require more thought on design and implementation. I think we are dealing with separate infrastructures here i.e. audit and security. Both are separate framework.

Will think of the implementation starting with the audit first.

Firedancer wrote:

I think this looks very much like a 'business requirement' rather than a 'data requirement'. Perhaps it can be coded as a type of BusinessComponent (BC) i.e. AuditedBusinessComponent or maybe, just a feature in the BC i.e. bc.Audit = true; What do you guys think? Separate component or integrated to BusinessComponent?

Here are my 2 cents thought and ideas on business data auditing:
1. Do you want to provide auditing at the business entity level (specific database field or the entire entity relationship?
2. What about storing TransactionType like Create, Update and Delete in the framework for auditing?
3. What about EntityTransactionTypes for storing the set of transaction types to audit for each entity
4. What about logging transaction data for record history and external viewing?  

Thanh wrote:

Here are my 2 cents thought and ideas on business data auditing: 1. Do you want to provide auditing at the business entity level (specific database field or the entire entity relationship? 2. What about storing TransactionType like Create, Update and Delete in the framework for auditing? 3. What about EntityTransactionTypes for storing the set of transaction types to audit for each entity 4. What about logging transaction data for record history and external viewing?

Yeah! Thanh, I think these suggestions are more related to an Audit and Logging framework (or in EntLib terms, an App Block ). They should be developed separate from the internals of the CoreAdapters. Right now, I'm toying with the idea that it should be implemented at the business layer.

Meaning, it should come with its own Business Components and possibly it's own control tables in the database.

If we are doing auditing, it should be more than just stamping date fields. There should be things like before and after update images. Which column has been modified and etc.

What do you guys think?

chid wrote:

Why not use the built-in .NET authentication?  I use a business component to authenticate users, and after I have authenticated the credentials with the DB, I create a generic principal object (from a previous project): Now you have a username to insert.  As to those who want to store UserIDs (int) rather than UserNames (varchar), I couldn't tell you.  I'm always torn between the two.  The DB in me wants everything to be normalized while the developer in me wants to easily have access to and display of the values w/o needless joins.  The developer always wins

I understand where you are coming from. I need a solid and neutral implementation of a security framework. It cannot be bias towards a single implementation. Therefore, more thoughts have to be put on this.

I'm sure there will be implementations where UserIds are preferred over names. Although I personally, prefer names like you.

All these will be thought off in 0.9.1 or maybe in the .net 2.0 version.

Firedancer wrote:

... Right now, I'm toying with the idea that it should be implemented at the business layer. Meaning, it should come with its own Business Components and possibly it's own control tables in the database. If we are doing auditing, it should be more than just stamping date fields. There should be things like before and after update images. Which column has been modified and etc. What do you guys think?

I think it's an excellent idea.

A while ago, I was toying with this code to learn about entity auditing and applying generic in V2 to see if I can simplify the code. It's only a bare bone concept and it certainly requires a lot more work to make it functional.

using System;
using System.Reflection;
using System.Collections.Generic;

namespace Audit
{
  class Audit
  {
    public Audit(string Name, string OriginalValue, string NewValue)
    {
      this._name = Name;
      this._auditdate = DateTime.Now;
      this._originalvalue = OriginalValue;
      this._newvalue = NewValue;   
    }

    private string _name ;
    public string Name
    {
      get {return _name;}
    }

    private DateTime _auditdate;
    public DateTime AuditDate
    {
      get { return _auditdate ;}
    }

    private string _originalvalue;
    public string OriginalValue
    {
      get { return _originalvalue; }
      set { _originalvalue = value; }
    }

    private string _newvalue;
    public string NewValue
    {
      get { return _newvalue; }
      set { _newvalue = value; }
    }
  }
 
  class AuditFactory<BOType>
  {
    public Audit Add(BOType bo, string PropertyName, string NewValue)
    {
      PropertyInfo boPropertyInfo = typeof(BOType).GetProperty(PropertyName);

      string OriginalValue = (string)boPropertyInfo.GetValue(bo, null);
      if (OriginalValue != NewValue)
      {
 Audit ObjectAudit = new Audit("Owner name here", OriginalValue, NewValue);
 return ObjectAudit;
      }
      else
      {
 return null;
      }
    }
  }
 
  public class Invoice
  {
    private List<Audit> ObjectInvoiceAudit = new List<Audit>();
    private AuditFactory<Invoice> ObjectAuditFactory = new AuditFactory<Invoice>();
    private Audit ObjectAudit;

    private string _invoicedescription = "";
    public string InvoiceDescription
    {
      get { return _invoicedescription; }
      set
      {
 ObjectAudit = ObjectAuditFactory.Add(this, "InvoiceDescription", value);
 if (ObjectAudit != null)
 {
   ObjectInvoiceAudit.Add(ObjectAudit);
 }

 _invoicedescription = value;
      }
    }
   
    public void DisplayList()
    {
      foreach(Audit Item in ObjectInvoiceAudit)
      {
        Console.WriteLine("Original value: {0} New value: {1}",
          Item.OriginalValue, Item.NewValue);       
      }
    }
  }
 
  public class Client
  {
    public static void Main(string[] args)
    {
      Invoice inv = new Invoice();
      inv.InvoiceDescription = "First invoice";
      inv.InvoiceDescription = "Second invoice";     
     
      inv.DisplayList();
    }   
  }   
}

FD, give me your feedback   

Thanh wrote:

FD, give me your feedback

Give me sometime to digest. One immediate problem though, the framework is not on .NET 2.0 yet

Oh Dear... I think I shot myself here. During some POC with the audit design, I discovered that it will be a major problem to implement it at business level. This is because, each Entity may have kids (I mean child EntityList). To do it in the business layer, I will have to replicate the code of traversing the kids from the data layer to the business component which is not so desirable.

I guessed I may have to integrate it into the data layer afterall for performance reasons. Will have to go along Mike's recommendations. Anyone can come up with other ideas? My other ideas:

1) AuditComponent that subscribes to the OnSaving event of the Entities (May need traversing - need to recheck).

2) Inheriting from a new EntityBase called AuditedEntityBase that has its Validate method overridden.

I've already coded the AuditFieldAttribute. Currently it has these values for AuditFieldType enum - CreatedBy, CreateDate, ModifiedBy, ModifiedDate, IsDeleted, DeletedBy, DeleteDate. Anyone of you want to suggest more?

Should I make it more cheesie like Creator, Editor, EditDate, Deletor?

Firedancer wrote:

I guessed I may have to integrate it into the data layer afterall for performance reasons. Will have to go along Mike's recommendations. Anyone can come up with other ideas?

Humm.... integrate it into the data layer somehow doesn't seem right. Since I don't have a clue how different pieces are glued together, my gut feeling still tells me that it doesn't belong in the data layer    

Thanh wrote:

Humm.... integrate it into the data layer somehow doesn't seem right. Since I don't have a clue how different pieces are glued together, my gut feeling still tells me that it doesn't belong in the data layer

I think so too. How about that AuditedEntityBase idea?

Firedancer wrote:

I think so too. How about that AuditedEntityBase idea?

The idea sounds good. My question is first what are we validating here? As you mention that an entity can contain many kids(child entities). This tree structure can get very deep and complicated if you have to traverse the hierachical structure and compare every single field. Performance is also at the forefront here and should be taken into consideration.

Just thinking of how to stay simple, easy to use, not to complicate to implement... umm not so simple after all.  

Yeah! It is not easy at all.

The AuditedEntityBase idea is to make use of the Validate method that is available in the EntityBase. The method will be overriden to include additional checks on Audit fields. Time-stamping can be done there. Since each entity calls its own instance of the Validate method, there won't be any problems if they are nested.

The person under audit however, will be much trickier. An ID or Name will need to be passed through the BusinessComponent to the Entity. Traversing through the object hierarchy may also be required.

Of course, this approach is slighly better than the data layer approach where auditing is done just before the SQL is executed. AuditedEntityBase still keeps auditing at the Business layer.

Firedancer wrote:

The person under audit however, will be much trickier. An ID or Name will need to be passed through the BusinessComponent to the Entity. Traversing through the object hierarchy may also be required.

What about creating the GUID or something unique in the base component, BusinessComponent, or right in the Entity if it's feasible, or something along the line?