Category Archives: Code

Tracking database changes using triggers

Posted by Ben on May 13, 2008 None comments

Tracking changes in database tables is an incredibly useful feature–especially for operational data that can change often. Having recently had to implement this feature, I thought I’d share some of the techniques I learned.

Sample Database

First, let’s conceptualize a very simple database consisting of user information (name, date of birth), and e-mails. A user can have more than one e-mail.

 

Table: UserData

Field Type
ID int (PK, identitiy)
FirstName varchar
LastName varchar
birthdate date

Table: UserEmails

Field Type
UserID int (FK)
email varchar

 

 

We want to track all changes to the FirstName, LastName, and birthdate fields. In addition we want to track when e-mails are added or removed from a user. As we’ll see, these aims are accomplished using two different methods.

My implementation is done in SQL Server 2000 and C#, but any database that supports triggers can be used.

Changes in a Single Table

With this method we want to track the changes to all fields of a table. In our example, we want to know when FirstName, LastName, and birthdate change values in the UserData table.

To accomplish this we need another table to track the history. This table is going to have the exact same fields as UserData, plus a few extra for the change tracking.

Table: UserDataChanges

Field Type
ChangeID int (PK, identity)
ChangeTime datetime
ChangeUser varchar
ID int (FK)
FirstName varchar
LastName varchar
birthdate date

Now the automated part–adding a trigger to populate this automatically:

CREATE TRIGGER UserDataChangeTrigger ON UserData FOR UPDATE, INSERT
AS    
    IF (UPDATE (FirstName) OR UPDATE(LastName) OR UPDATE(birthdate))    
    BEGIN     
        INSERT UserDataChanges 
            (ChangeTime, ChangeUser, ID, FirstName, LastName, birthdate)
            (SELECT GetUtcDate(), user, ID, FirstName,LastName,birthdate 
                FROM inserted)     
    END

This trigger will insert a new row into the UserDataChanges table whenever a row in the UserData table is updated or inserted. The IF (UPDATE(FirstName)…. ) is not strictly required in this scenario, but in other cases I did not want a change recorded when certain fields were updated (i.e., you have a field that tracks the last change time of that row, or the number of orders, or any other field that can change frequently and isn’t important to track–you don’t want to create too much noise in this or it will not be useful). The GetUtcDate() and user are SQL Server functions that retrieve the current UTC time and the username of the process that caused the change–very useful for tracking responsibility. The inserted table is created by the server for use by the trigger and contains all the new values.

Changes in a Foreign Key Table

The UserEmails has to be handled differently because there can be multiple e-mails for each user and we can assume they can be added, or removed at will (Remove + Add = Update, so I won’t consider direct updates here).

The solution I landed on was to have a generic event log table that stores manual log entries as well as “special” entries denoting adding or removing e-mails.

Table: UserEventLog

Field Type
EventID int (PK, identity)
ID int (FK)
EventTime datetime
EventType int
ChangeUser varchar
Notes varchar

This table can be used for both adding text notes to a user and, by using the EventType field, special events. In our example, we have two events we need to track:

 

Event Value
EmailAdded 1
EmailRemoved 2

(In code, I’ve made these enumerations)

Next we add a trigger on the UserEmails table:

CREATE TRIGGER UserEmails_EmailAddedTrigger
ON UserEmails
FOR INSERT
AS
 BEGIN
     INSERT UserEventLog(ID, EventTime, EventType, ChangeUser, Notes)
        (SELECT ID, GetUtcDate(), 1, user, '{'+email+'}' FROM inserted)
 END

The value 1 stands for EmailAdded. I’ve added braces around the actual e-mail address to set it apart from regular notes (we’ll see how to integrate everything later).

To handle the deletion of e-mails add another trigger:

CREATE TRIGGER UserEmails_EmailRemovedTrigger
ON UserEmails
FOR DELETE
AS
 BEGIN
     INSERT UserEventLog(ID, EventTime, EventType, ChangeUser, Notes )
        (SELECT ID, GetUtcDate(), 2, user, '{'+email+'}' FROM deleted)
 END

The only things different: FOR DELETE (instead of INSERT), changed the EventType to 2 (EmailRemoved), and the values are taken from the SQL Server-supplied deleted table.

That’s enough to get a pretty good change-tracking system in place, but you’ll still have to build a UI to display it effectively.

Displaying the Changes in the UI

With the above work done, you end up with two types of entities: changes and events. While it would be possible to integrate all functionality into a single event/change table using a lot more logic in the SQL Trigger code, I’m personally more comfortable with the change logic being in my application code. I think this way the database is kept more “pure” and open to changes down the line.

That means we will need to integrate these two types of entities into a single list, ordered by date/time. I’m going to assume the existence of two classes or structs that represent each of these entities. They’ll be called UserChange and UserEvent. I’ll also assume that the lists of each of these are already sorted by time, since that’s trivial to do in a SQL query.

Given that, we need a function that takes both of these lists and produces a sorted, combined list with an easy-to-understand list.

How the function works:

  1. Go through both lists, and pick whichever one is next, time-wise.
  2. Translate the object into a string/list-view representation of that object.
  3. If it’s a UserChange object, compare it to the previous one to figure out what changed.
  4. Sort the list in reverse order to put newer items at the top.

Here’s the C# code which I’ve adapted from our production system. Don’t get hung up on the details:

 

private void FillLog(IList<UserEvent> events, IList<UserChange> changes)
{
    List<ListViewItem> tempItems = new List<ListViewItem>();
 
    int currentEventIdx = 0;
    int currentChangeIdx = 0;
    eventLogListView1.Items.Clear();
 
    while (currentEventIdx < events.Count
    || currentChangeIdx < changes.Count)
    {
    UserChange currentChange = null;
    UserChange prevChange = null;
    UserEvent currentEvent = null;
 
    DateTime changeTime = DateTime.MaxValue;
    DateTime eventTime = DateTime.MaxValue;
 
    if (currentChangeIdx < changes.Count)
    {
        currentChange = changes[currentChangeIdx];
        changeTime = currentChange.ChangeDate;
        if (currentChangeIdx > 0)
        {
        prevChange = changes[currentChangeIdx - 1];
        }
 
    }
 
    if (currentEventIdx < events.Count)
    {
        currentEvent = events[currentEventIdx];
        eventTime = currentEvent.EventDate;
    }
    string dateStr;
    string userStr;
    string eventTypeStr="";
    string notesStr;
 
    if (changeTime < eventTime)
    {
        dateStr = Utils.FormatDateTime(changeTime);
        userStr = currentChange.UserName;
        notesStr = GetChangeString(currentChange, prevChange);
        currentChangeIdx++;
    }
    else
    {
        dateStr = Utils.FormatDate(eventTime);
        userStr = currentEvent.UserName;
        notesStr = currentEvent.Notes;
        eventTypeStr = currentEvent.EventType.ToString();
        currentEventIdx++;
    }
 
    if (notesStr.Length > 0)
    {
        ListViewItem item = new ListViewItem(dateStr);
        item.SubItems.Add(userStr);
        item.SubItems.Add(eventTypeStr);
        item.SubItems.Add(notesStr);
        item.ToolTipText = notesStr;
        item.BackColor = (tempItems.Count % 2 == 0) ? 
            Color.Wheat : Color.White;
        tempItems.Add(item);
 
    }
 
    }//end while
    eventLogListView1.BeginUpdate();
    for (int i = tempItems.Count - 1; i >= 0; i--)
    {
    eventLogListView1.Items.Add(tempItems[i]);
    }
 
    eventLogListView1.AutoResizeColumn(0, 
        ColumnHeaderAutoResizeStyle.ColumnContent);
    eventLogListView1.AutoResizeColumn(1, 
        ColumnHeaderAutoResizeStyle.ColumnContent);
    eventLogListView1.AutoResizeColumn(2, 
        ColumnHeaderAutoResizeStyle.ColumnContent);
    eventLogListView1.Columns[3].Width = eventLogListView1.Width - 
    (eventLogListView1.Columns[0].Width +
    eventLogListView1.Columns[1].Width +
    eventLogListView1.Columns[2].Width +10);
 
    eventLogListView1.EndUpdate();
}

Now we need to define GetChangeString, which figures out the differences in successive UserChange objects and displays only pertinent information.

 

private string GetChangeString(
    BuoyDataChange currentChange, 
    BuoyDataChange prevChange)
{
    StringBuilder sb = new StringBuilder();
 
    if (prevChange == null)
    {
        CompareAndAdd(sb, "First Name", 
            null, currentChange.FirstName);
        CompareAndAdd(sb, "Last Name", 
            null, currentChange.LastName);
        CompareAndAdd(sb, "Birth Date", 
            null, currentChange.BirthDate);
    }
    else
    {
        CompareAndAdd(sb, "First Name", 
            prevChange.FirstName, currentChange.FirstName);
        CompareAndAdd(sb, "Last Name", 
            prevChange.LastName, currentChange.LastName);
        CompareAndAdd(sb, "Birth Date", 
            prevChange.BirthDate, currentChange.BirthDate);
    }
    return sb.ToString();
}

And one last helper function which compares two objects and if different appends the change to a StringBuilder object.

 

private void CompareAndAdd(StringBuilder sb, string field, 
    object oldVal, object newVal)
   {
       if (oldVal == null && newVal == null)
           return;
 
       if (oldVal == null || !oldVal.Equals(newVal))
       {
           if (sb.Length > 0)
           {
               sb.Append(", ");
           }
           sb.AppendFormat("{0}:{1} -> {2}", field, oldVal, newVal);
       }
   }

In this way you can end up with an automated system that displays all changes in an easy-to-understand format.

Here’s a sample of what our system looks like (click to enlarge):

Change log screenshot

Other ways to accomplish this? Better ways? Please leave a comment!

kick it on DotNetKicks.com

Popularity: 8% [?]

In this universe we obey the law of commutativity

Posted by Ben on April 17, 2008 None comments

This kind of thing has happened to be a few times now, so I thought I’d share the fun.

In one of our pieces of software we have a process that looks like this:

void MyThread()
{
    while (true)
    {
        DoFunctionA();
        DoFunctionB();
        SleepFor10Seconds();
    }
    
}

While FunctionA and FunctionB are conceptually similar, they interact with completely different systems.

We had a problem with FunctionA the other day–it was taking 120 seconds to do its thing instead of the normal 10 (or less) because a remote server was down. This caused problems for FunctionB because it wasn’t running as often as it should have so things were getting backed up in the system. Oops.

Now, the solution is to split these two functions into two independent threads so they don’t interfere with each other, and I’ve been meaning to do this for a while, so that’s what I proposed.

Response back: “That’s a good idea, but before we do something complicated like that, can we just put FunctionB first?“

Um, no.

The time we want to minimize is the time between running FunctionB, which is TimeSleep + TimeA. Putting FunctionB first makes it TimeA + TimeSleep. Last I checked, those were actually equivalent.

Popularity: 4% [?]

GetTextExtent vs. DrawText (with DT_CALCRECT)

Posted by Ben on April 10, 2008 1 comments

Working on an MFC app that has just been converted to Unicode (finally!), I noticed that one button (which is created dynamically) is too small to fit the text in Korean (and Russian and a few other languages).

The code was calling something like:

CSize sz = m_btAdjustColors.GetDC()->GetTextExtent(sCaption);

It seems correct, but these script languages are throwing it for a loop–the measured size is much smaller than it should be. The GetTextExtent documentation doesn’t shed any explicit light on this subject, but may hint at it.

The solution? DrawText. There is a flag you can pass to tell it to calculate the rectangle needed instead of drawing.

CRect rect(0,0,0,0);
m_btAdjustColors.GetDC()->DrawText(sCaption, &rect, DT_CALCRECT);

It’s important to initialize the rectangle to zeroes because, as the docs say, it only modifies the right and bottom members of the rectangle.

Popularity: 7% [?]

How to file good bug reports (from Frank Kelly)

Posted by Ben on March 19, 2008 None comments

This is an issue I run into constantly at my job.

Frank Kelly wrote up a good summary of some items. They’re simple, easy to understand, easy to follow, even for non-programmers. ;)

In fact, I’m sending this link out to everyone in my group here at work.

Technorati Tags: ,,

Popularity: 4% [?]

Never make assumptions about performance

Posted by Ben on March 17, 2008 None comments

The importance of measuring performance changes is a topic that has been covered by others smarter and more experienced than me, but I have a recent simple tale.

I’ve simplified the code quite a bit in order to demonstrate the issue. Suppose I have a wrapper around an image (it has many more attributes):

   1: class Picture
   2: {
   3:     Image _image;
   4:     string _path;
   5: 
   6:     public Image Photo
   7:     {
   8:         get
   9:         {
  10:             if (_image==null && !string.IsNullOrEmpty(_path))
  11:             {
  12:                 _image = Bitmap.FromFile(_path);
  13:             }
  14:             return _image;
  15:         }
  16:     }
  17: 
  18: }

I had this and a view that loading about 2,700 of these into a customized ListView control at program startup. On a cold start (where none of the pictures were in the disk’s cache), it would take 27 seconds. Unacceptable.

What to do?

My first thought was to load the pictures asynchronously. I wrapped Bitmap.FromFile() into a function and called it asynchronously. When it was done, it fired an event that percolated up to the top.

Well, I spent about 30 minutes implementing that and ran it–horrible. The list showed up immediately, but it was unusable. The problem? Dumping 2,700 items into the ThreadPool queue is a problem. It doesn’t create 2,700 threads, but it causes enough problems to not be a viable option.

Asynchronicity is still the answer, though. But it’s at a different level. Instead of loading the individual images asynchronously, I skipped loading the images when creating the list control and instead launched a thread to load all the images and update them when done. The list loads in under a second, and the pictures show up little by little after that.

Measure, measure, measure. And pay attention.

Technorati Tags: ,,

Popularity: 5% [?]

Tip: Easily Automating use of WaitCursor

Posted by Ben on February 22, 2008 1 comments

This is really simple and probably common, but it’s a useful tip anyway.

Say you need to set a form’s cursor to the wait cursor while you accomplish something.

You would do something like this:

this.Cursor = Cursors.WaitCursor;
 
//do something
 
this.Cursor = Cursors.Default;

Of course, what if “do something” throws an exception? Then your cursor won’t be set back to the default.

So let’s wrap it in try-finally.

try
{
    this.Cursor = Cursors.WaitCursor;
    //do something
}
finally
{
    this.Cursor = Cursors.Default;
}

Better, but that’s a lot of wrapping around a simple operation. Notice, that the try-finally is exactly the pattern that using is. Why not wrap this functionality into a simple to use class that handles the cursor reset automatically?

internal class WaitCursor : IDisposable
{
    private Control _control = null;
    public WaitCursor(Control control)
    {
        _control = control;
        _control.Cursor = Cursors.WaitCursor;
    }
    public void Dispose()
    {
        _control.Cursor = Cursors.Default;
    }
}

And to use it in a form is simple:

using (new WaitCursor(this))
{
    //do work
}

Of course, you could easily add functionality like restoring the previous cursor instead of always the default, handling exceptions, make it thread-aware so it uses Invoke if necessary, better error-handling, etc.

Technorati Tags: ,,,

Popularity: 7% [?]

.Net Reflector

Posted by Ben on February 13, 2008 None comments

Lutz Roeder’s .Net Reflector has been discussed on many blogs before, but I want to give it an additional plug. I recently had to emulate some C# serial-port code in our C++ app. The .Net SerialPort class is great, easy-to-use, and works well. Unfortunately, we’re using a C++ serial port library that does not support all the possible features. Fortunately, we have the source code and can easily extend it. Unfortunately, I’m not too familiar with serial port programming, and the .Net functionality does not all obviously map to the Win32 API in every respect.

Enter Reflector. It was trivial to poke into the .Net assemblies and see what the SerialPort class was doing under the covers and then use the correct Win32 functionality in our app.

There are also a ton of plugins available.

Technorati Tags: ,,,

Popularity: 7% [?]

Solving "Unexpected Store Error" in Exchange

Posted by Ben on January 30, 2008 None comments

Getting a weird COM Exception with the cryptic ID 0x8055001E?

We’ve been struggling with this problem for over a year now, and we finally have a solution.

We have some critical code that is contacting Exchange server via COM Interop and CDOEX.DLL to read some inboxes and process e-mails. About once a month or so, we get this error:

System.Runtime.InteropServices.COMException (0x8055001E): Unexpected
store error: %1!d! (0x%1!8.8x!)
   at ADODB.RecordsetClass.Open(Object Source, Object
ActiveConnection, CursorTypeEnum CursorType, LockTypeEnum LockType,
Int32 Options)
   at MessageService.Exchange.ExchangeClient.Connect(String folderUrl,
String userId, String password, Boolean useHttp)

After this point, restarting our software does not help. The only recourse is to restart the Exchange store completely. Did I mention that our software needs to run 24/7/365 with no downtime (a few minutes here and there are acceptable)?

So about once a month, I get a message on my phone, I log into the server, reboot Exchange, and all is well.

Searching on Google revealed nothing at all. Until recently.

I now believe the problem was we were checking two e-mail accounts back-to-back, in a loop like this (highly simplified):

while (running)
{
    CheckAccount1();
    CheckACcount2();
    Thread.Sleep(60000);
}

Apparently, there is some bug in the CDO COM components’ code that will cause errors if you reconnect too fast. Occasionally, the Exchange code must have completed so quickly that it didn’t provide enough time for the COM components to clean up properly before the next solution attempt. Solution?

while (running)
{
    CheckAccount1();
    Thread.Sleep(5000);
    CheckAccount2();
    Thread.Sleep(60000);
}

We implemented that change on a staging server that was also experiencing this problem and haven’t had a single reoccurrence since. The fix will be going into production very soon. No more 2AM alerts!

Popularity: 7% [?]

Easily Unit Testing Event Handlers

Posted by Ben on December 27, 2007 6 comments

In C#, If you need to unit test a class that fires an event in certain circumstances (perhaps even asynchronously), you need to handle a little more than just running some code and doing the assertion. You have to make sure your unit test waits for the event to be fired. Here’s one naive way of doing it, a WRONG way:

 

   1: private bool statsUpdated = false;
   2: private ManualResetEvent statsUpdatedEvent = new ManualResetEvent(false);
   3:  
   4: [Test]
   5: public void CheckStats()
   6: {
   7:     BrickDatabase db = new BrickDatabase(tempFolder, maxCacheAge);
   8:     
   9:     statsUpdated = false;
  10:     statsUpdatedEvent.Reset();
  11:  
  12:     db.InventoryStatsUpdated += new EventHandler(db_InventoryStatsUpdated);
  13:     db.DoSomethingThatFiresEvent();
  14:  
  15:     statsUpdatedEvent.WaitOne();
  16:  
  17:     Assert.IsTrue(statsUpdated);
  18: }
  19:  
  20: void db_InventoryStatsUpdated(object sender, EventArgs e)
  21: {
  22:     statsUpdated = true;
  23:     statsUpdatedEvent.Set();
  24: }

There are a number of things wrong with this:

  1. The class variables. More complex unit test class. Have to coordinate these variables across multiple functions.
  2. Since they are class variables, you will want to reuse them, but you’d better remember to reset the event and the boolean every time!
  3. Have to have two functions to do something really, really simple.
  4. The WaitOne() does not have a timeout, so if the wait is ever satisfied then statsUpdated is guaranteed to be true.

Here’s a better way of doing it, using anonymous methods in C# 2.0:

 

   1: [Test]
   2: public void CheckStats()
   3: {
   4:     BrickDatabase db = new BrickDatabase(tempFolder, maxCacheAge);
   5:     bool statsUpdated = false;
   6:     ManualResetEvent statsUpdatedEvent = new ManualResetEvent(false);
   7:  
   8:     db.InventoryStatsUpdated += delegate
   9:     {
  10:         statsUpdated = true;
  11:         statsUpdatedEvent.Set();
  12:     };
  13:  
  14:     db.DoSomethingThatFiresEvent();
  15:  
  16:     statsUpdatedEvent.WaitOne(5000,false);
  17:  
  18:     Assert.IsTrue(statsUpdated);
  19: }

Improvements?

  1. The event is just part of the method. Since the event handler is an anonymous delegate, it can access the enclosing method’s local variables.
  2. Added 5,000ms timeout to the WaitOne() function to prevent hanging of unit tests.

Popularity: 9% [?]

DiskSlicer 1.2 Out

Posted by Ben on December 17, 2007 None comments

I made some minor updates to DiskSlicer. Mostly, some minor bug fixes, but also I added the much-requested ability to delete files directly from the program.

Go get it and enjoy!

Popularity: 4% [?]